This slide is based on Jeffrey D. Ullman's work, which can be download from his website.
References: 刘增杰、张少军 《MySQL 5.5 从零开始学》
在属性类型声明后面加上
CREATE TABLE Beers ( name CHAR(20) UNIQUE, manf CHAR(20) );
CREATE TABLE Sells ( bar CHAR(20), beer VARCHAR(20), price REAL, PRIMARY KEY (bar, beer) );
某个关系中某个属性或属性组上的值, 也必在另一个关系的某个或某些属性上出现.
例如: 在
使用关键词
被引用的另一个关系中的属性必须(在它所在的关系中)被声明为
CREATE TABLE Beers ( name CHAR(20) PRIMARY KEY, manf CHAR(20) ); CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20) REFERENCES Beers(name), price REAL, PRIMARY KEY(bar,beer) );
mysql> alter table Sells -> add foreign key(beer) references Beers(name) -> on delete cascade -> on update cascade;
要删除刚才建立的外键约束, 则使用下面的语句
mysql> alter table Sells drop foreign key Sells_ibfk_1;
这里的
mysql> show create table Sells\G; *************************** 1. row *************************** Table: Sells Create Table: CREATE TABLE `Sells` ( `bar` varchar(20) NOT NULL DEFAULT '', `beer` varchar(20) NOT NULL DEFAULT '', `price` double DEFAULT NULL, PRIMARY KEY (`bar`,`beer`), KEY `beer` (`beer`), CONSTRAINT `Sells_ibfk_1` FOREIGN KEY (`beer`) REFERENCES `Beers` (`name`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
要注意的是, 如果用
mysql> alter table Sells -> add foreign key (beer) references Beers (name) -> on delete set null -> on update cascade; ERROR 1005 (HY000): Can't create table 'test_bar.#sql-35e_2' (errno: 150) mysql> desc Beers;
CREATE TABLE Beers ( name CHAR(20) PRIMARY KEY, manf CHAR(20) ); CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20), price REAL, FOREIGN KEY (beer) REFERENCES Beers(name) );
如果关系
对于第一种, 系统拒绝这种违法修改. 但是, 对于在被引用关系上的修改, 设计者可以有三种选择:
例子: 设
对于
从
将
从
将
当我们声明一个外键时, 对于删除和更新操作可以独立地设置置空(
在外键的声明之后加上
如果不作出上面的选取, 则采用缺省原则(即拒绝更新).
CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20), price REAL, FOREIGN KEY(beer) REFERENCES Beers(name) ON DELETE SET NULL ON UPDATE CASCADE );
试一下下面的语句是否能够创建外键
CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20) REFERENCES Beers(name) ON DELETE SET NULL ON UPDATE CASCADE, price REAL );
事实上, 上面的语句能运行, 但是并没有创建外键.
对特定属性的值作约束, 可以在对该属性声明数据类型时加上
条件
CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20) CHECK ( beer IN (SELECT name FROM Beers)), price REAL CHECK ( price <= 5.00 ) );
在 MySQL5.5 版本中, 上面的语句可以成功创建表
基于属性的检查仅在属性的值被插入或更新时才进行.
这里的
检查仅在元组被插入或更新时进行.
只有
CREATE TABLE Sells ( bar CHAR(20), beer CHAR(20), price REAL, CHECK (bar = ’Joe’’s Bar’ OR price <= 5.00) );
在 MySQL5.5 版本中, 上面的语句可以成功创建表
断言是数据库模式(schema)的一部分, 等同于表(relation)或视图(view).
断言的定义是:
MySQL 目前不支持断言. 但是可以使用触发器来实现断言所述的功能.
在
CREATE ASSERTION NoRipoffBars CHECK( NOT EXISTS ( SELECT bar FROM Sells GROUP BY bar HAVING 5.00 < AVG(price) ));
其中子查询所得的结果关系是那些啤酒平均售价超过 $\$ 5$ 的酒吧.
mysql> CREATE ASSERTION NoRipoffBars CHECK( -> NOT EXISTS ( -> SELECT bar FROM Sells -> GROUP BY bar -> HAVING 5.00 < AVG(price) -> )); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ASSERTION NoRipoffBars CHECK( NOT EXISTS ( SELECT bar FROM Sells ' at line 1 mysql>
对于关系
CREATE ASSERTION FewBar CHECK ( (SELECT COUNT(*) FROM Bars) <= (SELECT COUNT(*) FROM Drinkers) );
原则上, 对于数据库中任何关系的更改, 每个断言都必须执行.
一个聪明的系统可以观察到仅某些更改会导致断言被违反.
断言的确很强大, 但
基于属性和基于元组的检查, 知道何时检查, 但它们不够强大.
触发器允许用户在给定时机检查任何给定的条件.
MySQL 的触发器和存储过程一样, 都是嵌入到 MySQL 的一段程序.
触发器是由事件来触发某个操作, 这些事件包括
外键约束将使得一个在
CREATE TRIGGER BeerTrig BEFORE INSERT ON Sells REFERENCING NEW ROW AS NewTuple FOR EACH ROW WHEN (NewTuple.beer NOT IN (SELECT name FROM Beers)) INSERT INTO Beers(name) VALUES(NewTuple.beer);
MySQL 中上面的代码会出错. MySQL 中是用
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt
触发器程序可以使用
mysql> delimiter // mysql> CREATE TRIGGER ins_check BEFORE INSERT ON Sells -> FOR EACH ROW -> BEGIN -> IF NEW.price < 0 THEN -> SET NEW.price = 0; -> ELSEIF NEW.price > 100 THEN -> SET NEW.price = 100; -> END IF; -> END// mysql> delimiter ;
继续实验, 看看会有什么问题
mysql> delimiter // mysql> CREATE TRIGGER PriceTrig -> AFTER UPDATE ON Sells -> FOR EACH ROW -> BEGIN -> IF NEW.price > OLD.price +1.00 THEN -> SET NEW.price = OLD.price +1.00; -> END IF; -> END// mysql> delimiter ; ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger
mysql> delimiter // mysql> CREATE TRIGGER PriceTrig -> BEFORE UPDATE ON Sells -> FOR EACH ROW -> BEGIN -> IF NEW.price > OLD.price +1.00 THEN -> SET NEW.price = OLD.price +1.00; -> END IF; -> END// mysql> delimiter ; ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
请先删除刚才建立的触发器, 然后完成触发器的创建.
删除触发器的语句是:
mysql> drop trigger ins_check;
例子: 单执行语句的触发器
首先建立一张表
mysql> CREATE TABLE account( -> acct_num INT, -> amount DECIMAL(10,2) -> ); Query OK, 0 rows affected (0.04 sec)
然后针对这张表的插入事件建立名为
mysql> CREATE TRIGGER ins_sum BEFORE INSERT -> ON account -> FOR EACH ROW -> SET @sum=@sum+NEW.amount; Query OK, 0 rows affected (0.03 sec)
最后, 先设置用户变量, 然后执行插入.
mysql> SET @sum=0; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO account -> VALUES (1,1.00), (2,2.00); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0
检查用户变量
mysql> SELECT @sum; +------+ | @sum | +------+ | 3.00 | +------+ 1 row in set (0.00 sec)
或者
CREATE OR REPLACE TRIGGER <name>
触发器或者是 "row-level" 或者是 "statement-level".
Refer to these by
可以是任意返回布尔值的条件.
条件(condition)在触发器事件之前或之后(取决于使用的是
动作中可以有不止一个
但是查询在动作中没有太大的实际意义(尽管是可以的, 可能有的时候也是需要的, 比如自动化显示等等), 因此我们这里只限定为修改.
将关系
CREATE TRIGGER PriceTrig AFTER UPDATE OF price ON Sells REFERENCING OLD ROW AS ooo NEW ROW AS nnn FOR EACH ROW WHEN (nnn.price > ooo.price + 1.00) INSERT INTO RipoffBars VALUES (nnn.bar);
mysql> delimiter // mysql> CREATE TRIGGER PriceTrig -> AFTER UPDATE ON Sells -> FOR EACH ROW -> BEGIN -> IF NEW.price > OLD.price +1.00 THEN -> INSERT INTO RipoffBars VALUES (NEW.bar); -> END IF; -> END// mysql> delimiter ;
如果指明对属性 price 进行修改, 试一下下面的语句
mysql> delimiter // mysql> CREATE TRIGGER PriceTrig -> AFTER UPDATE of Price ON Sells -> FOR EACH ROW -> BEGIN -> IF NEW.price > OLD.price +1.00 THEN -> INSERT INTO RipoffBars VALUES (NEW.bar); -> END IF; -> END// mysql> delimiter ;
会导致语法错误.
另一个要注意的是, 尽管 RipoffBars 这个表没有建立, 但是触发器也是可以建立的.
查看建立的触发器
mysql> SHOW TRIGGERS\G;
创建表
create table t(s1 integer);
创建触发器
delimiter | CREATE TRIGGER t_trigger BEFORE INSERT ON t FOR EACH ROW BEGIN SET @x = "hello trigger"; SET NEW.s1 = 55; END; | delimiter ;
最后一行 delimiter ; 是恢复分号作为语句结束符, 注意有 delimiter 和 ; 之间有空格.
查看触发器
show triggers\G;
删除触发器
drop trigger t_trigger;
参考自 http://oak.cs.ucla.edu/cs143/project/mysql/assertion.html
DELIMITER // -- change the end of statement delimiter from ; to // CREATE TRIGGER CS143Mandatory AFTER INSERT ON Student FOR EACH ROW -- note that "REFERECING NEW ROW AS NEW" is missing BEGIN IF NEW.GPA > 2.0 THEN -- this is equivalent to WHEN (NEW.GPA > 2.0) INSERT INTO Enroll VALUES (NEW.sid, 'CS', 143, 1); END IF; END // -- mark the end of trigger with // DELIMITER ; -- change the delimiter back to ;
我们可以推出表
创建一个单执行语句的触发器. 由于是单条语句, 故不必更换分隔符.
CREATE TABLE account( acct_num INT, amount DECIMAL(10,2) ); CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum=@sum+NEW.amount;
mysql> CREATE TABLE account( -> acct_num INT, -> amount DECIMAL(10,2) -> ); Query OK, 0 rows affected (0.04 sec) mysql> CREATE TRIGGER ins_sum -> BEFORE INSERT ON account -> FOR EACH ROW -> SET @sum=@sum+NEW.amount; Query OK, 0 rows affected (0.03 sec) mysql> SET @sum=0; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO account VALUES(1,1.00),(2,2.00),(3,3.00),(4,4.00),(5,5.00); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT @sum; +-------+ | @sum | +-------+ | 15.00 | +-------+ 1 row in set (0.00 sec)
注意, 当 MySQL 重新启动后, 变量
mysql> select @sum; +------+ | @sum | +------+ | NULL | +------+ 1 row in set (0.00 sec)
此时将执行语句放在
CREATE TABLE test1( a1 INT; ); CREATE TABLE test2( a2 INT; ); CREATE TABLE test3( a3 INT; ); CREATE TABLE test4( a4 INT; b4 INT; );
DELIMITER // CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 VALUES(NEW.a1); DELETE FROM test3 WHERE a3=NEW.a1; UPDATE test4 SET b4=b4+1 WHERE a4=NEW.a1; END // DELIMITER ;
Before MySQL 5.7.2, there cannot be multiple triggers for a given table that have the same trigger event and action time. For example, you cannot have two BEFORE UPDATE triggers for a table.
关于
我们以书上第7章 P.197 图7-5的例子, 将之改写为 MySQL 下可以运行的触发器.
CREATE TRIGGER NetWorthTrigger AFTER UPDATE ON MovieExec FOR EACH ROW BEGIN IF OLD.netWorth > NEW.netWorth THEN UPDATE MovieExec SET netWorth = OLD.netWorth WHERE certNo = NEW.certNo; END IF; END;//
CREATE TRIGGER NetWorthTrigger AFTER UPDATE OF netWorth ON MovieExec REFERENCING OLD ROW AS OldTuple, NEW ROW AS NewTuple FOR EACH ROW WHEN (OldTuple.netWorth > NewTuple.netWorth) UPDATE MovieExec SET netWorth = OldTuple.netWorth WHERE cert# = NewTuple.cert#;