SQL 必知必会·笔记<20>高级SQL特性

1. 约束

约束（constraint）就是管理如何插入或处理数据库数据的规则。DBMS通过在数据库表上施加约束来实施引用完整性。

 

1.1 主键

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

列应用于主键的条件：

• 任意两行的主键值都不相同。
• 每行都具有一个主键值（即列中不允许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);

 

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

 

在创建表之后还可以使用ALTER TABLE语句中用CONSTRAINT语法添加外键：

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

 

提示：外键有助防止意外删除

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

 

1.3 唯一约束

唯一约束用来保证一列（或一组列）中的数据是唯一的。唯一约束类似于主键，但是二者并不相同：

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

 

语法

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

 

1.4 检查约束

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

检测约束的主要作用有：

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

 

下面的例子,通过添加约束，任何插入（或更新）这个表中的行都会被检查，保证quantity大于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
);

 

2. 索引

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

使用索引的注意事项：

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

 

创建索引

索引用CREATE INDEX语句创建（不同DBMS创建索引的语句变化很大）,且索引必须唯一命名。下面的例子是在Products表的pro_name列上创建名为：prod_name_ind的索引：

CREATE INDEX prod_name_ind
ON PRODUCTS (prod_name);

 

提示：检查索引

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

 

3. 触发器

触发器的特点

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

触发器的作用

• 保证数据一致。例如，在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;

 

提示：约束比触发器更快

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

 

4. 数据库安全

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

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

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