SQL必知必会—高级SQL特性_sql文的高级特性-CSDN博客

本文链接：https://blog.csdn.net/u014465934/article/details/80068969

《SQL必知必会》读书笔记

1.约束

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

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

2.主键

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

换句话说，表中的一列（或多个列）的值唯一标识表中的每一行。这方便了直接或交互地处理表中的行。

没有主键，要安全地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
)

使用CONSTRAINT语法，定义主键。

ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id);

说明：SQLite中的主键
SQLite不允许使用ALTER TABLE定义键，要求在初始化的CREATE TABLE语句中定义它们。

附注：SQL中的CONSTRAINT用法总结

CONSTRAINT主要就是增加数据约束的。

约束是通过使用CREATE TABLE或ALTER TABLE语句生成的。

ORACLE支持五种类型的完整性约束：NOT NULL、CHECK、UNIQUE、PRIMARY KEY、POREIGN KEY。

1.主键约束：
因为是主键约束，就是对一个列进行了约束，约束为非空、不重复。

2.check约束：
就是给一列的数据进行了限制，CHECK就是检查在约束中指定的条件是否得到了满足。比方说，年龄列的数据都要大于20的。

3.unique约束：
这样的约束就是给列的数据追加的不重复的约束类型。

4.默认约束：
意思很简单就是让此列的数据默认为一定的数据。

5.外键约束：
外键其实就是引用。外键实现了引用的完整性，应用完整性规定，所引用的数据必须存在。

3.外键

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

CREATE TABLE Orders(
    order_num INTEGER NOT NULL PRIMARY KEY,
    order_date DATETIME NOT NULL,
    cust_id CHAR(10) NOT NULL REFEREMCES
)

相同的工作也可以在ALTER TABLE语句中用CONSTRAINT语法来完成：

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

提示：外键有助防止意外删除
外键保证引用完整性外，还有另一个重要作用。在定义外键后，DBMS不允许删除在另一个表中具有关联行的行。例如，不能删除关联订单的顾客。删除该顾客的唯一方法是首先删除相关的订单（这表示还要删除相关的订单项）。

有的DBMS支持称为级联删除（cascading delete）的特性。如果启用，该特性在从一个表中删除行时删除所有相关的数据。例如，如果启用级联删除并且从Customers表中删除某个顾客，则任何相关的订单行也会被自动删除。

4.唯一约束

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

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

附注：主键在一个表里只能存在一个！因为建立主键后，DBMS会默认把建立主键的列建立聚簇索引。但是一个表允许把几个列设为主键的。还要约束，一个表可用不敢多个唯一约束。

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

5.检查约束

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

检查约束的常见用途有以下几点。

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

换句话说，第1课介绍的数据类型限制了列中可保存的数据的类型。检查约束在数据类型内又做了进一步的限制，这些限制极其重要，可以确保插入数据库的数据正是你想要的数据。

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]')

提示：用户定义数据类型
有的DBMS允许用户定义自己的数据类型。它们是定义检查约束（或其他约束）的基本简单数据类型。例如，你可以定义自己的名为gender的数据类型，它是单字符的文本数据类型，带限制其值为M或F（对于未知值或许还允许NULL）的检查约束。定制数据类型的优点是只需施加约束一次（在数据类型定义中），而每当使用该数据类型时，都会自动应用这些约束。请查阅相应的DBMS文档，看它是否支持自定义数据类型。

6.索引

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

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

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

索引用CREATE INDEX语句创建（不同DBMS创建索引的语句变化很大）。

CREATE INDEX prod_name_ind
ON PRODUCTS (prod_name);

索引必须唯一命名。这里的索引名prod_name_ind在关键字CREATE INDEX之后定义。ON用来指定被索引的表，而索引中包含的列（此例中仅有一列）在表名后的圆括号中给出。

提示：检查索引
检索的效率随表数据的增加或改变而变化。最好定期检查索引，并根据需要对所有进行调整。

7.触发器

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

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

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

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

下面是触发器的一些常见用途。

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

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

-- SQL Server
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;

-- Oracle和PostgreSQL
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;