SQL 必知必会 Chapter 22 —— 高级SQL特性

---

typora-copy-images-to: SQL 必知必会

SQL 必知必会 Chapter 22 高级SQL特性

---

22.1 约束

• 关系数据库存储分解为多个表的数据，每个表存储相应的数据。利用键来建立从一个表到另一个表的引用

• 正确地进行关系数据库设计，需要一种方法保证只在表中插入合法数据

• 所谓约束，是管理如何插入或处理数据库数据的规则

• DBMS 通过在数据库表上施加约束来实施引用完整性

• 大多数约束是在表定义中定义的

• 主键：

  • 用来保证一列中的值是唯一的，永不改动
  • 满足以下条件都可以用于主键
    • 任意两行的主键值都不相同
    • 每行都具有一个主键值，不允许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
);
-- 创建主键 PRIMARY KEY

ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id);
-- 使用CONSTRAINT 语法 在ALTER TABLE中

• 外键
  • 帮助保证引用完整性

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 Orders
ADD CONSTRAINT
FOREIGN KEY (cust_id) REFERENCES Customers (cust_id)

• 外键有助于防止意外删除

  • 例如无法删除关联订单的顾客，需要删除相关的订单后，才能删除相关客户

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

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

• 唯一约束既可以用UNIQUE 关键字在表定义中定义，也可以用单独的CONSTRAINT 定义

• 检查约束，用来保证一列中的数据满足一组指定的条件，常见用途如下

  • 检查最小或最大值
  • 指定范围，如：保证发货日期大于今天的日期
  • 只允许特定的值，如：在性别字段中只允许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
);
-- 保证了 quantity大于0

-- 保证gender 的列只包含 M 或者 F ，可编写如下语句
ADD CONSTRAINT CHECK (gender LIKE '[MF]')

22.2 索引

• 索引是用来排序数据以加快搜索和排序操作的速度
• 可以在一个或多个列上定义索引，使DBMS保存其内容的一个排过序的列表

-- 索引用CREATE INDEX 语句创建
CREATE INDEX prod_name_ind
ON Products (prod_name);

22.3 触发器

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

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

• 触发器的常见用途：

  • 保证数据的一致，如，改变大小写
  • 基于某个表的变动在其他表上执行活动，如，写入日志表
  • 进行额外的验证并根据需要回退数据，如，保证某个顾客的可用资金不超限定

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 版本的触发器

22.4 数据库安全

• 任何安全系统的基础都是用户授权和身份确认
• 一般说来需要保护的操作有：

22.5 小结

• 讲授如何使用SQL 的一些高级特性。约束是实施引用完整性的重要部分，索引可改善数据检索的性能，触发器可以用来执行运行前后的处理，安全选项可用来管理数据访问。不同的DBMS 可能会以不同的形式提供这些特性，更详细的信息请参阅具体的DBMS 文档