【SQL 必知必会】- 第二二课 高级SQL 特性

目录

22.1 约束

        约束（constraint）

        22.1.1 主键

        22.1.2 外键

        外键有助防止意外删除

        22.1.3 唯一约束

        22.1.4 检查约束

22.2 索引

        检查索引

22.3 触发器

        约束比触发器更快

22.5 小结

        这一课介绍SQL 所涉及的几个高级数据处理特性：约束、索引和触发器。

        这节课是【SQL必知必会】的最后一课，看完本课之后，恭喜你已经通读了【SQL必知必会】一书，想要更加详细的介绍，请自行寻找电子书目，支持正版。

22.1 约束

        关联表和引用完整性已经在前面讨论过几次。正如所述，关系数据库存储分解为多个表的数据，每个表存储相应的数据。利用键来建立从一个表到另一个表的引用（由此产生了术语引用完整性（referential integrity））。

        这也是关系型数据库的本质，及重要特性。

        虽然可以在插入新行时进行检查（在另一个表上执行 SELECT，以保证所有值合法并存在），但最好不要这样做，原因如下。

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

        约束（constraint）

        管理如何插入或处理数据库数据的规则。

        DBMS 通过在数据库表上施加约束来实施引用完整性。大多数约束是在表定义中定义的，如第17 课所述，用 CREATE TABLE 或 ALTER TABLE语句。

        22.1.1 主键

        主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且永不改动。换句话说，表中的一列（或多个列）的值唯一标识表中的每一行。这方便了直接或交互地处理表中的行。没有主键，要安全地 UPDATE 或 DELETE 特定行而不影响其他行会非常困难。

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

• 任意两行的主键值都不相同。
• 每行都具有一个主键值（即列中不允许 NULL 值）。
• 包含主键值的列从不修改或更新。（大多数 DBMS 不允许这么做，但如果你使用的 DBMS 允许这样做，好吧，千万别！）
• 主键值不能重用。如果从表中删除某一行，其主键值不分配给新行。

        这点十分重要，保证历史唯一性是保护数据的高效方式。

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

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
);

        关键字 PRIMARY KEY，使其成为主键。

ALTER TABLE Vendors ADD CONSTRAINT PRIMARY KEY (vend_id);

        这里定义相同的列为主键，但使用的是 CONSTRAINT 语法。此语法也可以用于 CREATE TABLE 和 ALTER TABLE 语句。

        22.1.2 外键

        外键是表中的一列，其值必须列在另一表的主键中。外键是保证引用完整性的极其重要部分。也就是说一个表的外键，一定会是另一个表的主键。

        下面是定义这个外键的方法：

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 语法来完成：

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

        外键有助防止意外删除

        如第6 课所述，除帮助保证引用完整性外，外键还有另一个重要作用。在定义外键后，DBMS 不允许删除在另一个表中具有关联行的行。

        在实际的工作中，设计表结构的事情一般都由数据库小组的人员负责，我们自己定义的表结构也需要通过数据库小组的审核，非数据库小组的成员，修改 / 创建表的机会很多少。

        22.1.3 唯一约束

        唯一约束用来保证一列（或一组列）中的数据是唯一的。它们类似于主键，但存在以下重要区别。

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

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

        22.1.4 检查约束

        检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。检查约束的常见用途有以下几点。

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

        换句话说，第 1 课介绍的数据类型限制了列中可保存的数据的类型。检查约束在数据类型内又做了进一步的限制，这些限制极其重要，可以确保插入数据库的数据正是你想要的数据。不需要依赖于客户端应用程序或用户来保证正确获取它，DBMS 本身将会拒绝任何无效的数据。

        下面的例子对 OrderItems 表施加了检查约束，它保证所有物品的数量大于 0。

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。检查名为 gender 的列只包含 M 或F，可编写如下的 ALTER TABLE 语句：

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

22.2 索引

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

        使索引有用的因素是什么？很简单，就是恰当的排序。找出书中词汇的困难不在于必须进行多少搜索，而在于书的内容没有按词汇排序。如果书的内容像字典一样排序，则索引没有必要（因此字典就没有索引）。

        数据库索引的作用也一样。主键数据总是排序的，这是 DBMS 的工作。因此，按主键检索特定行总是一种快速有效的操作。

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

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

        没有严格的规则要求什么应该索引，何时索引。大多数 DBMS 提供了可用来确定索引效率的实用程序，应该经常使用这些实用程序。

        索引用 CREATE INDEX 语句创建（不同 DBMS 创建索引的语句变化很大）。下面的语句在 Products 表的产品名列上创建一个简单的索引。

-- 这里需要注意语法
CREATE INDEX prod_name_ind ON Products (prod_name);

        索引必须唯一命名。

        检查索引

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

22.3 触发器

        触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。触发器可以与特定表上的 INSERT、UPDATE 和 DELETE 操作（或组合）相关联。

        与存储过程不一样（存储过程只是简单的存储 SQL 语句），触发器与单个的表相关联。与 Orders 表上的 INSERT 操作相关联的触发器只在 Orders 表中插入行时执行。类似地，Customers 表上的 INSERT 和 UPDATE 操作的触发器只在表上出现这些操作时执行。

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

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

        根据所使用的 DBMS 的不同，触发器可在特定操作执行之前或之后执行。下面是触发器的一些常见用途。

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

        下面的例子创建一个触发器，它对所有 INSERT 和 UPDATE 操作，将 Customers 表中的  cust_state 列转换为大写。

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;

        约束比触发器更快

        一般来说，约束的处理比触发器快，因此在可能的时候，应该尽量使用约束。

22.4 数据库安全

        对于组织来说，没有什么比它的数据更重要了，因此应该保护这些数据，使其不被偷盗或任意浏览。当然，数据也必须允许需要访问它的用户访问，因此大多数 DBMS 都给管理员提供了管理机制，利用管理机制授予或限制对数据的访问。

        任何安全系统的基础都是用户授权和身份确认。这是一种处理，通过这种处理对用户进行确认，保证他是有权用户，允许执行他要执行的操作。有的 DBMS 为此结合使用了操作系统的安全措施，而有的维护自己的用户及密码列表，还有一些结合使用外部目录服务服务器。

        一般说来，需要保护的操作有：

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

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

22.5 小结

        本课讲授如何使用 SQL 的一些高级特性。约束是实施引用完整性的重要部分，索引可改善数据检索的性能，触发器可以用来执行运行前后的处理，安全选项可用来管理数据访问。

        恭喜你，看到这里，你已经结束了【SQL 必知必会】系列，希望能对你有所帮助，想要了解更加高级的特性，需要配合视频课程 / 高级特性书籍进行学习。

        后面还有一个关于【SQL 必知必会】课程的【常用SQL语句速查】博文，请自行查阅。