22 高级SQL特性

在对表的定义中，使用了REFERENCES关键字，它表示cust_id中的任何值都必须是customers表中的cust_id中的值。

22.1.3 唯一约束

唯一约束用来保证一列中的数据是唯一的。类似于主键，但存在下列区别：

• 表可包含多个唯一约束，但每个表只能有一个主键；
• 唯一约束列可以包含NULL值；
• 唯一约束列可以修改或者更新；
• 唯一约束列的值可以重复使用
• 与主键不一样，唯一约束不能用来定义外键。

比如我们在注册账号的时候，需要填上用户的邮箱，每个人的邮箱都不一样，但是我们一般不会考虑把邮箱作为主键，而是定义用户ID作为主键。为了保证邮箱唯一，可以在邮箱列上定义UNIQUE约束做到。

22.1.4 检查约束

MySQL不支持这里不做详细介绍。

22.2 索引

索引用来排序数据以加快搜索和排序操作的速度。

在创建索引之前，应该了解：

• 索引改善检索操作的性能，但是降低了数据插入、修改和删除的性能；
• 索引数据可能要占据大量的存储空间
• 并非所有数据都能做索引数据；
• 索引用于数据过滤和数据排序。如果经常以某种特定的顺序排序数据，则该数据可能适合做索引；
• 可以在索引中定义多个列。

索引用CREATE INDEX语句创建。

下面的语句就是在products表的产品名列上创建一个简单的索引。

CREATE INDEX prod_nume_ind
ON products (prod_nume);

索引必须唯一命名。这里的索引名是prod_name_ind 在关键字 CREATE INDEX 之后定义。ON用来指定被索引的表，而索引中包含的列在表名后的圆括号中给出。

22.3 触发器

触发器是特殊的存储过程，在特定的数据库活动发生时自动执行。触发器会在某个表执行以下语句时而自动执行：DELETE、INSERT、UPDATE。

22.3.1 创建触发器

在创建触发器的时候，需要给出4条信息：

• 唯一的触发器名；
• 触发器关联的表；
• 触发器应该响应的活动（DELETE、INSERT、UPDATE）；
• 触发器何时执行（处理之前或之后）

例如：

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT ‘product added‘;

CREATE TRIGGER用来创建名为newproduct的新触发器。触发器可在一个操作发生之前或之后执行，这里给出了AFTER INSERT(还有BEFORE)，所以此触发器将在INSERT语句成功执行后执行。这个触

发器还指定FOREACH ROW，因此代码对每个插入行执行。在这个例子中，文本Product added将对每个插入的行显示一次。 

触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条INSERT、 UPDATE和DELETE的之前和之后）。

22.3.2 删除触发器

为了删除一个触发器，可使用DROP TRIGGER语句，如下所示：

DROP TRIGGER newproduct;

22.3.3 INSERT触发器

INSERT触发器在INSERT语句执行之前或之后执行。

• 在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行 
• 在BEFORE INSERT触发器中， NEW中的值也可以被更新（允许更改被插入的值）
• 对于AUTO_INCREMENT列， NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。
  

CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;

此代码创建一个名为neworder的触发器，它按照AFTER INSERT ON orders执行。在插入一个新订单到orders表时， MySQL生成一个新订单号并保存到order_num中。触发器从NEW. order_num取得这个值

并返回它。对于orders的每次插入使用这个触发器将总是返回新的订单号。 

为测试这个触发器，试着插入一下新行，如下所示：

INSERT INTO orders(order_date, cust_id)
VALUES(Now(), 10001);

22.3.4 DELETE触发器

DELETE触发器在DELETE语句执行之前或之后执行。

• 在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；
• OLD中的值全都是只读的，不能更新。
  

看这样的例子：

CREATE INTO deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO achieve_orders(order_num, order_date, cust_id)
    VALUES(OLD.order_date, OLD.cust_id);
END;

在任意订单被删除前将执行此触发器。它使用一条INSERT语句将OLD中的值（要被删除的订单）保存到一个名为archive_orders的存档表中（为实际使用这个例子，需要用与orders相同的列创建一个名为

archive_orders的表）。

22.3.5 UPDATE触发器

UPDATE触发器在UPDATE语句执行之前或之后执行。

• 在UPDATE触发器代码中，可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值；
• 在BEFORE UPDATE触发器中， NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；
  
• OLD中的值全都是只读的，不能更新。
  

看这样一个例子：

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper( NEW.vend_state );

每次更新一个行时，NEW.vend_state中的值都用Upper(NEW.vend_state)替换。

22 高级SQL特性

标签：响应   使用   因此   2.3   速度   time   需要   活动   after