《SQL必知必会》第二十二课 高级SQL特性 约束、索引、触发器、安全选项

第二十二课 高级SQL特性

SQL所涉及的几个高级数据处理特性：约束、索引和触发器
约束是实施引用完整性的重要部分
索引可改善数据检索的性能
触发器可用来执行运行前后的处理
安全选项可用来管理数据访问
不同的DBMS可能会以不同的形式提供这些特性

一、约束

#SQL强有力的特性给用户提供了高级的数据处理技术，如约束
#关联表和引用完整性
#关系数据库存储分解为多个表的数据，每个表存储相应的数据
#利用键来建立从一个表到另一个表的引用(由此产生了术语引用完整性referential integrity)

正确地进行关系数据库设计，需要一种方法保证只在表中插入合法数据
虽然可以在插入新行时进行检查（在另一个表上执行SELECT，以保证所有值合法并存在），但最好不好这样，原因如下：

• 如果在客户端层面上实施数据库完整性规则，则每个客户观都要被迫实施这些规则，一定会有一些客户端不实施这些规则
• 在执行UPDATE和DELETE操作时，也必须实施这些规则
• 执行客户端检查是非常耗时的，而DBMS执行这些检查会相对高效

约束： 管理如何插入或处理数据库数据的规则

DBMS通过在数据库表上施加约束来实施引用完整性
大多数约束是在表定义中定义的

注意1：
具体DBMS约束：
有几种不同类型的约束，每个DBMS都提供自己的支持
在进行实验前，请参阅具体的DBMS文档

1.1 主键

主键是一种特殊的约束，用来保证一列(或一组列)中的值是唯一的，而且永不改动

表中的一列（或多个列）的主键值唯一标识表中的每一行

没有主键，要安全地UPDATE或DELETE特定行而不影响其他行会非常困难

表中任意列只要满足以下条件，都可以用于主键：

• 任意两行的主键值都不相同
• 每行都具有一个主键值（即列中不允许NULL值）
• 包含主键值的列从不修改或更新
• 主键值不能重用。如果从表中删除某一行，其主键值不分配给新行。

一种定义主键的方法是创建它：

【1】CREATE TABLE Vendors ( vend_id CHAR(10) NOT NULL PRIMARY KEY, vend_name CHAR(50) NOT NULL, vend_address CHAR(50) NULL, vend_city CHAR(50) NULL, vend_state CHAR(5) NULL, vend_zip CHAR(10) NULL, vend_country CHAR(50) NULL );
#给表的vend_id列定义添加关键字PRIMARY KEY，使其成为主键

【2】ALTER TABLE Vendors ADD CONSTRAINT PRIMARY KEY (vend_id);
#在创建表之后在添加主键
#定义相同的列为主键，但使用的是CONSTRAINT语法
#此语法也可以用于CREATE TABLE和ALTER TABLE语句

注意2：
SQLite中的键：
SQLite不允许使用ALTER TABLE定义键，要求在初始的CREATE TABLE语句中定义它

1.2 外键

外键是表中的一列，其值必须列在另一表的主键中

外键是保证引用完整性的极其重要部分

定义外键的方法：
【3】CREATE TABLE Orders ( order_num INTEGER NOT NULL PRIMARY KEY, order_date DATETIME NOT NULL, cust_id CHAR(10) NOT NULL REFERENCES Customers(cust_id) );
#其中的表定义使用 REFERENCES 关键字，它表示cust_id 中任何值都必须是Customers表的cust_id中的值

相同的工作也可以在ALTER TABLE语句中用CONSTRAINT语法来完成:

【4】ALTER TABLE Orders ADD CONSTRAINT FOREIGN KEY (cust_id) REFERENCES Customers (cust_id)
#在创建表之后还可以使用ALTER TABLE语句中用CONSTRAINT语法添加外键

注意3：
外键有助防止意外删除：
[1]除帮助保证引用完整性外，外键还有另一个重要作用。在定义外键后，DBMS不允许删除在另一个表中具有关联行的行

[2]有的DBMS支持级联删除（cascading delete）的特性:
如果启用，该特性在从一个表中删除行时删除所有相关的数据。
例：如果启用级联删除并且从Customers表中删除某个顾客，则任何关联的订单行也会被自动删除

1.3 唯一约束

唯一约束用来保证一列（或一组列）中的数据是唯一的

约束类似于主键，但存在以下重要区别：

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

唯一约束的语法类似于其他约束的语法
唯一约束既可以用UNIQUE关键字在表定义中定义，也可以用单独的CONSTRAINT定义

1.4 检查约束

检查约束用来保证一列(或一组列)中的数据满足一组指定的条件

检查约束的常见用途有以下几点：

• 检查最小或最大值。例如，防止0个物品的订单（即使0是合法的数）
• 指定范围。例如，保证发货日期大于等于今天的日期，但不超过今天起一年后的日期
• 只允许特定的值。例如，在性别字段中只允许M或F

#第1课介绍的数据类型限制了列中可保存的数据的类型。

#检查约束在数据类型内又做了进一步的限制，这些限制极其重要，可以确保插入数据库的数据正是你想要的数据

#不需要依赖于客户端应用程序或用户来保证正确获取它，DBMS本身将会拒绝任何无效的数据

下例对OrderItems表施加检查约束，保证所有物品的数量大于0
【5】CREATE TABLE OrderItems ( order_num INTEGER NOT NULL, order_item INTEGER NOT NULL, prod_id CHAR(10) NOT NULL, quantity INTEGER NOT NULL CHECK (quantity > 0), item_price MONEY NOT NULL );
#利用这个约束，任何插入（或更新）这个表中的行都会被检查，保证quantity大于0

【6】ADD CONSTRAINT CHECK (gender LIKE '[MF]')
#ALTER TABLE语句
#检查名为gender的列只包含M或F

注意4：
用户定义数据类型：
#有的DBMS允许用户定义自己的数据类型
#它们是定义检查约束（或其它约束）的基本简单数据类型
#定义数据类型的优点是只需施加约束一次（在数据类型定义中），而每当使用该数据类型时，都会自动应用这些约束
加粗样式

二、索引

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

[1]使索引有用的因素：恰当的排序

[2]数据库索引的作用也一样，主键数据总是排序的，这是DBMS的工作

[3]按主键检索特定行总是一种快速有效的操作

[4]搜索其他列的值通常效率不高，解决方法是使用索引

[5]可以在一个或多个列上定义索引，使DBMS保存其内容的一个排过序的列表

[6]在定义了索引后，DBMS以使用书的索引类似的方法使用它

[7]DBMS搜索排过序的索引，找出匹配的位置，然后检索这些行

开始创建索引前，应该记住以下内容:

• 索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。在执行这些操作时，DBMS必须动态地更新索引
• 索引数据可能要占用大量的存储空间
• 并非所有数据都适合做索引。取值不多的数据（如州）不如具有更多可能值的数据（如姓或名），能通过索引得到那么多的好处
• 索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引
• 可以在索引中定义多个列（例如，州加上城市）。这样的索引仅在以州加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处

大多数DBMS提供可用来确定索引效率的实用程序，应该经常使用这些实用程序

索引用CREATE INDEX语句创建(不同DBMS创建索引的语句变化很大)
索引必须唯一命名

【7】CREATE INDEX prod_name_ind ON PRODUCTS (prod_name);
#在Products表的产品名pro_name列上创建一个简单的名为prod_name_ind的索引
#索引必须唯一命名
#索引名prod_name_ind在关键字CREATE INDEX之后定义
#ON用来指定被索引的表，而索引中包含的列(此列中仅有一列)在表明后的圆括号中给出

注意5：
检查索引：
#索引的效率随表数据的增加或改变而变化
#许多数据库管理员发现，过去创建的某个理想的索引经过几个月的数据处理后可能变得不再理想
#最好定期检查索引，并根据需要对索引进行调整

三、触发器

触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行

触发器可以与特定表上的INSERT、UPDATE和DELETE操作（或组合）相关联

与存储过程不一样（存储过程只是简单的存储SQL语句），触发器与单个的表相关联

与Orders表上的INSERT操作相关联的触发器只在Orders表中插入行时执行

触发器内的代码具有以下数据的访问权：

• INSERT操作中的所有新数据
• UPDATE操作中的所有新数据和旧数据
• DELETE操作中删除的数据

根据所使用的DBMS的不同，触发器可在特定操作执行之前或之后执行

触发器的一些常见用途：

• 保证数据一致。例如，在INSERT或UPDATE操作中将所有州名转换为大写
• 基于某个表的变动在其他表上执行活动。例如，每当更新或删除一行时将审计跟踪记录写入某个日志表
• 进行额外的验证并根据需要回退数据。例如，保证某个顾客的可用资金不超限定，如果已经超出，则阻塞插入
• 计算计算列的值或更新时间戳

不同的DBMS的触发器创建语法差异很大

创建触发器：

【8】CREATE TRIGGER customer_state ON Customers FOR INSERT, UPDATE AS UPDATE Customers SET cust_state = Upper(cust_state) WHERE Customers.cust_id = inserted.cust_id;
#SQL Server版本
#创建一个触发器，它对所有INSERT和UPDATE操作，将Customers表中的cust_state列转换为大写

【9】CREATE TRIGGER customer_state AFTER INSERT OR UPDATE FOR EACH ROW BEGIN UPDATE Customers SET cust_state = Upper(cust_state) WHERE Customers.cust_id = :OLD.cust_id END;
#上例Oracle、PostgreSQL版本

注意6：
约束比触发器更快：
一般来说，约束的处理比触发器快，在可能的时候，应该尽量使用约束

四、数据库安全

对于组织来说，没什么比数据更重要，应该保护这些数据，使其不被偷窃或任意浏览

数据也必须允许需要访问它的用户访问，因此大多数DBMS都给管理员提供了管理机制，利用管理机制授予或限制对数据的访问

任何安全系统的基础都是用户授权和身份确认
这是一种处理，通过这种处理对用户进行确认，保证他是有权用户，允许执行他要执行的操作

有的DBMS为此结合使用了操作系统的安全措施，而有的维护自己的用户及密码列表，还有一些结合使用外部目录服务服务器

需要保护的操作有：

• 对数据库管理功能（创建表、更改或删除已存在的表等）的访问
• 对特定数据库或表的访问
• 访问的类型（只读、对特定列的访问等）
• 仅通过视图或存储过程对表进行访问
• 创建多层次的安全措施，从而允许多种基于登录的访问和控制
• 限制管理用户账号的能力

安全性使用SQL的GRANT和REVOKE语句来管理
大多数DBMS提供了交互式的管理实用程序，这些实用程序在内部使用GRANT和REVOKE语句

CREATE TABLE Vendors
(
    vend_id         CHAR(10)       NOT NULL PRIMARY KEY,
    vend_name       CHAR(50)       NOT NULL,
    vend_address    CHAR(50)       NULL,
    vend_city       CHAR(50)       NULL,
    vend_state      CHAR(5)        NULL,
    vend_zip        CHAR(10)       NULL,
    vend_country    CHAR(50)       NULL
);


ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id);

CREATE TABLE Orders
(
    order_num     INTEGER     NOT NULL PRIMARY KEY,
    order_date    DATETIME    NOT NULL,
    cust_id       CHAR(10)    NOT NULL REFERENCES Customers(cust_id)
);

ALTER TABLE Orders
ADD CONSTRAINT
FOREIGN KEY (cust_id) REFERENCES Customers (cust_id)

CREATE TABLE OrderItems
(
    order_num     INTEGER     NOT NULL,
    order_item    INTEGER     NOT NULL,
    prod_id       CHAR(10)    NOT NULL,
    quantity      INTEGER     NOT NULL CHECK (quantity >    0),
    item_price    MONEY       NOT NULL
);

ADD CONSTRAINT CHECK (gender LIKE '[MF]')

CREATE INDEX prod_name_ind
ON PRODUCTS (prod_name);


CREATE TRIGGER customer_state
ON Customers
FOR INSERT, UPDATE
AS
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id = inserted.cust_id;
--SQL Server版本

CREATE TRIGGER customer_state
AFTER INSERT OR UPDATE
FOR EACH ROW
BEGIN
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id = :OLD.cust_id
END;
--Oracle、PostgreSQL版本

SQL基础篇完结，算是有始有终。
需要完整代码的小伙伴可关注微信公众号：菜田里守望者

打开微信扫一扫关注吧，你们的支持就是我的动力
后面会做相关的SQL并且分享给大家

参考文献：
【1】《SQL必知必会》第四版 人民邮电出版社 [美] Ben Forta 著 钟鸣 刘晓霞 译