SQL 特性

MySQL►数据库 , , 评论

关于高级 SQL 特性的读书笔记整理

约束

  • 约束(constraint)：管理如何插入或处理数据库数据的规则。

主键

  • 主键是一种特殊的约束，用来保证一列中的值是唯一的，而且永不改动。
  • 主键要满足的条件：
    1. 任意两行的主键值都不同。
    2. 每行都具有一个主键值（即列中不允许 NULL 值）
    3. 包含主键值的列从不修改或更新。
    4. 主键值不能重用。如果表中删除某一行，其主键值不分配给新行。
1
2
3
4
5
6
7
8
9
10
11
-- 在建表时定义主键：
CREATE TABLE tablename
(
    cum1	CHAR(10)	NOT NULL PRIMARY KEY,
	cum2	CHAR(10)	NOT NULL
);

-- 定于主键的第二种方法：
ALTER TABLE tablename 
CONSTRAINT PRIMARY KEY (cum1);
-- 注意：在 SQLite 中不允许使用 ALTER TABLE 定义键，要求在初始的 CREATE TABLE 语句中定义它们。

外键

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

  • 《SQL 必知必会》 中的例子：

    • Orders 表将录入到系统的每个订单作为一行包含其中。顾客信息存储在 Customers 表中。 Orders 表中订单通过顾客 ID 与 Customers 表中的特定行相关联。顾客 ID 为 Customers 表的主键，每个顾客都有唯一的 ID。订单号为 Orders 表的主键，每个订单都有唯一的订单号。
      Order 表中顾客 ID 列的值不一定是唯一的。如果某个顾客有多个订单，则有多个行具有相同的顾客 ID （虽然每个订单都有不同的订单号）。同事，Orders 表中顾客 ID 列的合法值为 Customers 表中顾客的 ID。
1
2
3
4
5
6
7
8
9
10
11
12
-- 在建表语句中定义外键：
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 不允许删除在另一个表中具有关联行的行。例如不能删除关联订单的顾客。删除顾客的唯一方法是首先删除相关订单。由于需要一系列的删除，因而利用外键可以防止意外删除数据。
    • 有的 DBMS 支持成为级联删除的特性。如果启用，该特性在从一个表中删除行时删除所有相关的数据。

唯一约束

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

  • 唯一约束与主键的区别：

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

  • 语法： 同其他约束的语法。关键字 UNIQUE。

检查约束

  • 检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。
  • 检查约束的常见用途：
    • **检查最大或最小值。**例如，防止0个物品的订单。
    • **指定范围。**例如保证发货日期大于等于今天的日期，但不超过今天起一年后的日期。
    • **只允许特定的值。**例如，在性别字段中只允许 M 或 F。
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 在建表语句中定义检查约束：
-- 保证 quantity 大于 0.
CREATE TABLE OrderItems
(
	order_num	INTEGER		NOT NULL,
	order_item	INTEGER		NOT NULL,
	prod_id		INTEGER		NOT NULL,
	quantity	INTEGER		NOT NULL CHECK (quantity > 0),
	item_price	MONEY		NOT NULL
);

-- 检查名为 gender 的列只包含 M 或 F，可编写如下的 ALTER TABLE 语句：
ADD CONSTRAINT CHECK (gender LIKE '[MF]')

索引

  • 索引用来排序数据以加快搜索和排序操作的速度。
  • 可以在一个或多个列上定义索引，使 DBMS 保存期内容的一个排过序的列表。在定义了索引后，DBMS 以使用书的索引类似的方法使用它。DBMS 搜索排过序的索引，找出匹配的位置，然后检索这些行。
  • 创建索引前要注意：
    • **索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。**在执行这些操作时，DBMS 必须动态地更新索引
    • 索引数据可能要占用大量的存储空间。
    • **并非所有数据都适合做索引。**取值不多的数据不如具有更多可能值的数据，能用过索引得到那么多的好处。
    • **索引用于数据过滤和数据排序。**如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。
    • 可以在索引中定义多个列。
1
2
3
4
5
-- 创建索引：
CREATE INDEX prod_name_ind
ON Products (prod_name);

-- 索引必须唯一命名。

触发器

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

  • 与存储过程不一样，触发器与单个的表相关联。与 Orders 表上的 INSERT 操作相关联的触发器只在 Orders 表中插入行时执行。

  • 约束的处理比触发器快，在可能的时候，应该尽量使用约束。

希川Engineer

格物致知。