《SQL必知必会》笔记五：22课
五、高级SQL特性
5.1 约束
关联表和引用完整性已经在前面讨论过几次。正如所述，关系数据库存储分解为多个表的数据，每个表存储相应的数据。
利用键来建立从一个表到另一个表的引用（由此产生了术语引用完整性（referential integrity））。
约束：管理如何插入或处理数据库数据的规则。
DBMS 通过在数据库表上施加约束来实施引用完整性。
大多数约束是在表定义中定义的，用 CREATE TABLE 或 ALTER TABLE语句。
主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且永不改动。
换句话说，表中的一列（或多个列）的值唯一标识表中的每一行。
表中任意列只要满足以下条件，都可以用于主键：
任意两行的主键值都不相同。
每行都具有一个主键值（即列中不允许 NULL 值）。
包含主键值的列从不修改或更新。
主键值不能重用。如果从表中删除某一行，其主键值不分配给新行。
/*定义主键的方法*/ 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);
外键是表中的一列，其值必须列在另一表的主键中。外键是保证引用完整性的极其重要部分。
/*不同数据库有不同的定义方法*/ ALTER TABLE Orders ADD CONSTRAINT FOREIGN KEY (cust_id) REFERENCES Customers (cust_id)
唯一约束用来保证一列（或一组列）中的数据是唯一的。它们类似于主键，但存在以下重要区别：
表可包含多个唯一约束，但每个表只允许一个主键。
唯一约束列可包含 NULL 值。
唯一约束列可修改或更新。
唯一约束列的值可重复使用。
与主键不一样，唯一约束不能用来定义外键。
举个例子：每个雇员都有唯一的社会安全号，但我们并不想用它作主键，因为它太长（而且我们也不想使该信息容易利用）。因此，每个雇员除了其社会安全号外还有唯一的雇员 ID（主键）。
为了保证社会安全号是唯一不会重复的，可以添加唯一约束。
唯一约束既可以用 UNIQUE 关键字在表定义中定义，也可以用单独的 CONSTRAINT 定义。
检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。检查约束的常见用途有以下几点：
检查最小或最大值。例如，防止 0 个物品的订单（即使 0 是合法的数）。
指定范围。例如，保证发货日期大于等于今天的日期，但不超过今天起一年后的日期。
只允许特定的值。例如，在性别字段中只允许 M 或 F。
/*添加检查约束*/ 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]')
5.2 索引
索引用来排序数据以加快搜索和排序操作的速度。想像一本书后的索引（如本书后的索引），可以帮助你理解数据库的索引。
在开始创建索引前，应该记住以下内容：
索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。在执行这些操作时，DBMS 必须动态地更新索引。
索引数据可能要占用大量的存储空间。
并非所有数据都适合做索引。取值不多的数据（如州）不如具有更多可能值的数据（如姓或名），能通过索引得到那么多的好处。
索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。
可以在索引中定义多个列（例如，州加上城市）。这样的索引仅在以州加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处。
索引用CREATE INDEX语句创建（不同DBMS创建索引的语句变化很大）。
/*在 Products 表的产品名列上创建一个简单的索引*/ CREATE INDEX prod_name_ind ON Products (prod_name); /*索引必须唯一命名。这里的索引名 prod_name_ind 在关键字 CREATE INDEX 之后定义。ON 用来指定被索引的表，而索引中包含的列（此例中仅有一列）在表名后的圆括号中给出。*/
5.3 触发器
触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。
触发器可以与特定表上的 INSERT、UPDATE 和 DELETE 操作（或组合）相关联。
触发器内的代码具有以下数据的访问权：
INSERT 操作中的所有新数据；
UPDATE 操作中的所有新数据和旧数据；
DELETE 操作中删除的数据。
下面是触发器的一些常见用途：
保证数据一致。例如，在 INSERT 或 UPDATE 操作中将所有州名转换为大写。
基于某个表的变动在其他表上执行活动。例如，每当更新或删除一行时将审计跟踪记录写入某个日志表。
进行额外的验证并根据需要回退数据。例如，保证某个顾客的可用资金不超限定，如果已经超出，则阻塞插入。
计算计算列的值或更新时间戳。
/*下面创建一个触发器，它对所有 INSERT 和 UPDATE 操作，将Customers 表中的 cust_state 列转换为大写。*/ 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; /*一般来说，约束的处理比触发器快，因此在可能的时候，应该尽量使用约束。*/
5.4 数据库安全
大多数 DBMS 都给管理员提供了管理机制，利用管理机制授予或限制对数据的访问。
任何安全系统的基础都是用户授权和身份确认。这是一种处理，通过这种处理对用户进行确认，保证他是有权用户，允许执行他要执行的操作。
有的 DBMS 为此结合使用了操作系统的安全措施，而有的维护自己的用户及密码列表，还有一些结合使用外部目录服务服务器。
一般说来，需要保护的操作有：
对数据库管理功能（创建表、更改或删除已存在的表等）的访问；
对特定数据库或表的访问；
访问的类型（只读、对特定列的访问等）；
仅通过视图或存储过程对表进行访问；
创建多层次的安全措施，从而允许多种基于登录的访问和控制；
限制管理用户账号的能力。
安全性使用 SQL 的 GRANT 和 REVOKE 语句来管理，不过，大多数 DBMS提供了交互式的管理实用程序，这些实用程序在内部使用 GRANT 和REVOKE 语句。
