SQL必知必会笔记（下）

第14课 组合查询

组合查询

多数SQL查询只包含从一个或多个表中返回数据的单条 SELECT 语句。但是，SQL 也允许执行多个查询（多条 SELECT 语句），并将结果作为一个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。

主要有两种情况需要使用组合查询：

• 在一个查询中从不同的表返回结构数据
• 对一个表执行多个查询，按一个查询返回数据

创建组合查询

可以用 UNION 操作符来组合数条 SQL 查询。

使用 UNION

使用 UNION 很简单，所要做的只是给出每条 SELECT 语句，在各条语句之间放上关键字 UNION。

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';
复制代码

使用多条WHERE子句而不是UNION的相同查询：

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
OR cust_name = 'Fun4All';
复制代码

对于较复杂的过滤条件，或者从多个表（而不是一个表）中检索数据的情形，使用 UNION 可能会使处理更简单。

UNION 规则

• UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字 UNION 分隔（因此，如果组合四条 SELECT 语句，将要使用三个UNION关键字）。
• UNION 中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同的次序列出）。
• 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含转换的类型（例如，不同的数值类型或不同的日期类型）。

包含或取消重复的行

UNION 从查询结果集中自动去除了重复的行；换句话说，它的行为与一条 SELECT 语句中使用多个 WHERE 子句条件一样。

如果想返回所有的匹配行，可使用 UNION ALL 而不是 UNION。

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION ALL
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';
复制代码

UNION ALL 为 UNION 的一种形式，它完成 WHERE 子句完成不了的工作。

对组合查询结果排序

在用 UNION 组合查询时，只能使用一条 ORDER BY 子句，它必须位于最后一条 SELECT 语句之后。

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All'
ORDER BY cust_name, cust_contact;
复制代码

第15课 插入数据

数据插入

INSERT 用来将行插入（或添加）到数据库表。插入有几种方式：

• 插入完整的行
• 插入行的一部分
• 插入某些查询的结果

插入及系统安全：使用 INSERT 语句可能需要客户端/服务器 DBMS 中的特定安全权限。在你试图使用 INSERT 前，应该保证自己有足够的安全权限。

插入完整的行

把数据插入表中的最简单方法是使用基本的 INSERT 语法，它要求指定表名和插入到新行中的值。

INSERT INTO Customers
VALUES('1000000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL);
复制代码

存储到表中每一列的数据在 VALUES 子句中给出，必须给每一列提供一个值。如果某列没有值，则应该使用 NULL 值（假定表允许对该列指定空值）。各列必须以它们在表定义中出现的次序填充。

虽然这种语法很简单，但并不安全，应该尽量避免使用。上面的 SQL 语句高度依赖于表中列的定义次序，还依赖于其容易获得的次序信息。即使可以得到这种次序信息，也不能保证各列在下一次表结构变动后保持完全相同的次序。因此，编写依赖于特定列次序的 SQL 语句是很不安全的，这样做迟早会出问题。

编写 INSERT 语句的更安全（不过更繁琐）的方法如下：

INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL);
复制代码

因为提供了列名，VALUES 必须以其指定的次序匹配指定的列名，不一定按各列出现在表中的实际次序。其优点是，即使表的结构改变，这条 INSERT 语句仍然能正确工作。

给出列名的情况下，以不同的次序填充仍然正确：

INSERT INTO Customers(cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip)
VALUES('1000000006', NULL, NULL, 'Toy Land', '123 Any Street', 'New York', 'NY', '11111');
复制代码

小心使用 VALUES：不管使用哪种 INSERT 语法，VALUES 的数目都必须正确。如果不提供列名，则必须给每个表列提供一个值；如果提供列名，则必须给列出的每个列一个值。否则，就会产生一条错误消息，相应的行不能成功插入。

插入部分行

使用 INSERT 的推荐方法是明确给出表的列名。使用这种语法，还可以省略列，这表示可以只给某些列提供值，给其他列不提供值。

INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)
VALUES('1000000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA');
复制代码

省略的列必须满足以下条件：

• 该列定义为允许 NULL 值（无值或空值）
• 在表定义中给出默认值。这表示如果不给出值，将使用默认值。

插入检索出的数据

INSERT 还存在另一种形式，可以利用它将 SELECT 语句的结果插入表中，这就是所谓的INSERT SELECT。顾名思义，它是由一条 INSERT语句和一条 SELECT语句组成的。

INSERT INTO Customers(cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)
SELECT cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country
FROM CustNew;
复制代码

如果 CustNew 这个表确实有数据，则所有的数据将被插入到 Customers 表。

从一个表复制到另一个表

有一种数据插入不使用 INSERT 语句。要将一个表的内容复制到一个全新的表（运行中创建的表），可以使用 SELECT INTO 语句。

SELECT *
INTO CustCopy
FROM Customers;
复制代码

要想只复制部分的列，可以明确给出列名，而不是使用 * 通配符。

在使用SELECT INTO时，需要知道一些事情：

• 任何SELECT选项和子句都可以使用，包括 WHERE和 GROUP BY
• 可利用联结从多个表插入数据
• 不管从多少个表中检索数据，数据都只能插入到一个表中

第16课 更新和删除数据

更新数据

更新（修改）表中的数据，可以使用 UPDATE 语句。有两种使用 UPDATE 的方式：

• 更新表中的特定行
• 更新表中的所有行

基本的 UPDATE 语句由三部分组成，分别是：

• 要更新的表
• 列名和它们的新值
• 确定要更新那些行的过滤条件

UPDATE Customers
SET cust_email = 'kim@thetoystore.com
WHERE cust_id = '1000000005';
复制代码

不要省略 WHERE 子句：在使用 UPDATE 时一定要细心。因为稍不注意，就会更新表中的所有行。

更新更多列的语法稍有不同：

UPDATE Customers
SET cust_contact = 'Sam Roberts',
	cust_email = 'sam@toyland.com'
WHERE cust_id = '1000000006';
复制代码

要删除某个列的值，可设置它为 NULL（假如表定义允许 NULL 值）。

UPDATE Customers
SET cust_email = NULL
WHERE cust_id = '1000000005'
复制代码

其中 NULL 用来去除 cust_email 列中的值。这与保存空字符串很不同（空字符串用''表示，是一个值），而 NULL 表示没有值。

删除数据

从一个表中删除（去掉）数据，使用 DELETE 语句。有两种使用 DELETE 的方式：

• 从表中删除特定的行
• 从表中删除所有行

DELETE FROM Customers
WHERE cust_id = '1000000006';
复制代码

不要省略 WHERE 子句：在使用 DELETE 时一定要细心。因为稍不注意，就会错误地删除表中所有行。

友好的外键：使用外键确保引用完整性的一个好处是，DBMS 通常可以防止删除某个关系需要用到的行。例如，要从 Products 表中删除一个产品，而这个产品用在 OrderItems 的已有订单中，那么 DELETE 语句将抛出错误并中止。这是总要定义外键的另一个理由。

更新和删除的指导原则

如果省略了 WHERE 子句，则 UPDATE 或 DELETE 将被应用到表中所有的行。因此许多 SQL 程序员使用 UPDATE 或 DELETE 时需要遵循以下原则：

• 除非确实打算更新和删除每一行，否则绝对不要使用不带 WHERE子句的 UPDATE 或 DELETE 语句。
• 保证每个表都有主键（如果忘记这个内容，请参阅第 12课），尽可能像 WHERE 子句那样使用它（可以指定各主键、多个值或值的范围）。
• 在 UPDATE 或 DELETE 语句使用 WHERE 子句前，应该先用 SELECT 进行测试，保证它过滤的是正确的记录，以防编写的 WHERE 子句不正确。
• 使用强制实施引用完整性的数据库（关于这个内容，请参阅第12课），这样 DBMS 将不允许删除其数据与其他表相关联的行。
• 有的 DBMS 允许数据库管理员施加约束，防止执行不带 WHERE 子句的 UPDATE 或 DELETE 语句。如果所采用的 DBMS 支持这个特性，应该使用它。

第17课 创建和操纵表

创建表

一般有两种创建表的方法：

• 多数 DBMS 都具有交互式创建和管理数据库表的工具
• 表也可以直接用 SQL 语句操纵 用程序创建表，可以使用 SQL 的 CREATE TABLE 语句。需要注意的是，使用交互式工具时实际上就是使用 SQL 语句。这些语句不是用户编写的，界面工具会自动生成并执行相应的 SQL 语句（更改已有的表时也是这样）。

表创建基础

利用 CREATE TABLE 创建表，必须给出下列信息：

• 新表的名字，在关键字 CREATE TABLE 之后给出
• 表列的名字和定义，用逗号分隔
• 有的 DBMS 还要求指定表的位置

CREATE TABLE Products
(
	prod_id		CHAR(10)			NOT NULL,
	vend_id		CHAR(10)			NOT NULL,
	prod_name	CHAR(254)		NOT NULL,
	prod_price	DECIMAL(8, 2)		NOT NULL,
	prod_desc	VARCHAR(1000)	NULL
);
复制代码

替换现有的表：在创建新的表时，指定的表名必须不存在，否则会出错。防止意外覆盖已有的表，SQL 要求首先手工删除该表（请参阅后面的内容），然后再重建它，而不是简单地用创建表语句覆盖它。

使用 NULL 值

在插入或更新行时，该列必须有值。每个表列要么是 NULL列，要么是 NOT NULL 列，这种状态在创建时由表的定义规定。

CREATE TABLE Orders
(
	order_num	INTEGER		NOT NULL,
	order_date	DATETIME	NOT NULL,
	cust_id		CHAR(10)		NOT NULL
);
复制代码

这三列都需要，因此每一列的定义都含有关键字 NOT NULL。这就会阻止插入没有值的列。如果插入没有值的列，将返回错误，且插入失败。

CREATE TABLE Vendors
(
	vend_id			CHAR(10)		NOT NULL,
	vend_name		CHAR(50)	NOT NULL,
	vend_address		CHAR(50)	,
	vend_city			CHAR(50)	,
	vend_state		CHAR(5)		,
	vend_zip			CHAR(10)		,
	vend_country		CHAR(50)
);
复制代码

NULL 为默认设置，如果不指定 NOT NULL，就认为指定的是 NULL。

主键和 NULL 值：主键是其值唯一标识表中每一行的列。只有不允许 NULL 值的列可作为主键，允许 NULL 值的列不能作为唯一标识。

指定默认值

CREATE TABLE OrderItems
(
	order_num		INTEGER			NOT NULL,
	order_item		INTEGER			NOT NULL,
	prod_id			CHAR(10)			NOT NULL,
	quantity			INTEGER			NOT NULL		DEFAULT 1,
	item_price		DECIMAL(8, 2)		NOT NULL
);
复制代码

默认值经常用于日期或时间戳列。

更新表

更新表定义，可以使用 ALTER TABLE 语句。以下是使用 ALTER TABLE 时需要考虑的事情。

• 理想情况下，不要在表中包含数据时对其进行更新。应该在表的设计过程中充分考虑未来可能的需求，避免今后对表的结构做大改动
• 所有的 DBMS 都允许给现有的表增加列，不过对所增加列的数据类型（以及 NULL 和 DEFAULT 的使用）有所限制
• 许多 DBMS 不允许删除或更改表中的列
• 多数 DBMS 允许重新命名表中的列
• 许多 DBMS 限制对已经填有数据的列进行更改，对未填有数据的列几乎没有限制

使用 ALTER TABLE 更改表结构，必须给出下面的信息：

• 在 ALTER TABLE 之后给出要更改的表名（该表必须存在，否则将出错）
• 列出要做哪些更改

ALTER TABLE Vendors
ADD vend_phone CHAR(20);
复制代码

更改或删除列、增加约束或增加键，这些操作也使用类似的语法：

ALTER TABLE Vendors
DROP COLUMN vend_phone;
复制代码

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：

(1) 用新的列布局创建一个新表； (2) 使用 INSERT SELECT 语句（关于这条语句的详细介绍，请参阅第 15课）从旧表复制数据到新表。有必要的话，可以使用转换函数和计算字段； (3) 检验包含所需数据的新表； (4) 重命名旧表（如果确定，可以删除它）； (5) 用旧表原来的名字重命名新表； (6) 根据需要，重新创建触发器、存储过程、索引和外键。

小心使用 ALTER TABLE：使用 ALTER TABLE 要极为小心，应该在进行改动前做完整的备份（表结构和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，也许无法删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。

删除表

DROP TABLE CustCopy;
复制代码

删除表没有确认，也不能撤销，执行这条语句将永久删除该表。

使用关系规则防止意外删除：许多 DBMS 允许强制实施有关规则，防止删除与其他表相关联的表。在实施这些规则时，如果对某个表发布一条 DROP TABLE 语句，且该表是某个关系的组成部分，则DBMS将阻止这条语句执行，直到该关系被删除为止。如果允许，应该启用这些选项，它能防止意外删除有用的表。

重命名表

所有重命名操作的基本语法都要求指定旧表名和新表名。不过，存在 DBMS 实现差异。关于具体的语法，请参阅相应的 DBMS 文档。

第18课 使用视图

视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索 数据的查询。

用下面的 SELECT 语句从三个表中检索数据：

SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customer.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num
AND prod_id = 'RGAN01';
复制代码

现在，假如可以把整个查询包装成一个名为 ProductCustomers 的虚拟表，则可以如下轻松地检索出相同的数据：

SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';
复制代码

这就是视图的作用。ProductCustomers 是一个视图，它不包含任何列或数据，包含的是一个查询（与上面用以正确联结表的查询相同）。

为什么使用视图

下面是视图的一些常见应用：

• 重用 SQL 语句
• 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节。
• 使用表的一部分而不是整个表。
• 保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。
• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

创建视图之后，可以用与表基本相同的方式使用它们。可以对视图执行 SELECT 操作，过滤和排序数据，将视图联结到其他视图或表，甚至添加和更新数据（添加和更新数据存在某些限制，关于这个内容稍后做介绍）。

重要的是，要知道视图仅仅是用来查看存储在别处数据的一种设施。视图本身不包含数据，因此返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。

性能问题：因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时需要的所有检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，性能可能会下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试。

视图的规则和限制

关于视图创建和使用的一些最常见的规则和限制：

• 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）
• 对于可以创建的视图数目没有限制。
• 创建视图，必须具有足够的访问权限。这些权限通常由数据库管理人员授予。
• 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造视图。所允许的嵌套层数在不同的DBMS中有所不同（嵌套视图可能会严重降低查询的性能，因此在产品环境中使用之前，应该对其进行全面测试）。
• 许多 DBMS 禁止在视图查询中使用 ORDER BY 子句。
• 有些 DBMS 要求对返回的所有列进行命名，如果列是计算字段，则需要使用别名（关于列别名的更多信息，请参阅第7课）。
• 视图不能索引，也不能有关联的触发器或默认值。
• 有些 DBMS 把视图作为只读的查询，这表示可以从视图检索数据，但不能将数据写回底层表。详情请参阅具体的 DBMS 文档。
• 有些 DBMS 允许创建这样的视图，它不能进行导致行不再属于视图的插入或更新。例如有一个视图，只检索带有电子邮件地址的顾客。如果更新某个顾客，删除他的电子邮件地址，将使该顾客不再属于视图。这是默认行为，而且是允许的，但有的 DBMS 可能会防止这种情况发生。

创建视图

视图用 CREATE VIEW 语句来创建。删除视图可以用 DROP VIEW 。

利用视图简化复杂的联结

CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;
复制代码

在以上视图中进行检索：

SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';
复制代码

用视图重新格式化检索出的数据

SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' AS vend_title
FROM Vendors
ORDER BY vend_name;
复制代码

把此语句转换为视图：

CREATE VIEW VendorLocations AS
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' AS vend_title
FROM Vendors;
复制代码

再检索数据：

SELECT *
FROM VendorLocations;
复制代码

用视图过滤不想要的数据

CREATE VIEW CustomerEmailList AS
SELECT cust_id, cust_name, cust_email
FROM Customers
WHERE cust_email IS NOT NULL;
复制代码

再检索数据：

SELECT *
FROM CustomerEMailList;
复制代码

使用视图和计算字段

SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;
复制代码

将以上查询转成视图：

CREATE VIEW OrderItemsExpanded AS 
SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM OrderItems;
复制代码

再检索数据：

SELECT *
FROM OrderItemsExpanded
WHERE order_num = 20008;
复制代码

第19课 使用存储过程

存储过程

简单来说，存储过程就是为以后使用而保存的一条或多条 SQL 语句。可将其视为批文件，虽然它们的作用不仅限于批处理。

为什么要使用存储过程

理由很多，下面给出一些主要的：

• 通过把处理封装在一个易用的单元中，可以简化复杂的操作（如前面例子所述）。
• 由于不要求反复建立一系列处理步骤，因而保证了数据的一致性。如果所有开发人员和应用程序都使用同一存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
• 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，那么只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。 这一点的延伸就是安全性。通过存储过程限制对基础数据的访问，减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。
• 因为存储过程通常以编译过的形式存储，所以 DBMS 处理命令所需的工作量少，提高了性能。
• 存在一些只能用在单个请求中的 SQL 元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

换句话说，使用存储过程有三个主要的好处，即简单、安全、高性能。

执行存储过程

存储过程的执行远比编写要频繁得多，因此我们先介绍存储过程的执行。执行存储过程的 SQL 语句很简单，即 EXECUTE。EXECUTE 接受存储过程名和需要传递给它的任何参数。

EXECUTE AddNewProduct('JTS01', 'Stuffed Eiffel Tower', 6.49, 'Plush stuffed toy with the text La Tour Eiffel in red white and blue'0;
复制代码

这里执行一个名为 AddNewProduct 的存储过程，将一个新产品添加到 Products 表中。AddNewProduct 有四个参数，分别是：供应商 ID（Vendors 表的主键）、产品名、价格和描述。这 4个参数匹配存储过程中4个预期变量（定义为存储过程自身的组成部分）。此存储过程将新行添加到 Products 表，并将传入的属性赋给相应的列。

在 Products表中还有另一个需要值的列 prod_id列，它是这个表的主键。为什么这个值不作为属性传递给存储过程？要保证恰当地生成此 ID，最好是使生成此 ID 的过程自动化（而不是依赖于最终用户的输入）。

以下是存储过程所完成的工作：

• 验证传递的数据，保证所有4个参数都有值；
• 生成用作主键的唯一ID；
• 将新产品插入 Products 表，在合适的列中存储生成的主键和传递的数据。

创建存储过程

Oracle 版本：

CREATE PROCEDURE MailingListCount (
	ListCount OUT INTEGER
)
IS
v_rows INTEGER;
BEGIN
	SELECT COUNT(*) INTO v_rows
	FROM Customers
	WHERE NOT cust_email IS NULL;
	ListCount := v_rows;
END;
复制代码

这个存储过程有一个名为 ListCount 的参数。此参数从存储过程返回一个值而不是传递一个值给存储过程。关键字 OUT 用来指示这种行为。Oracle支持 IN（传递值给存储过程）、OUT（从存储过程返回值，如这里）、INOUT （既传递值给存储过程也从存储过程传回值）类型的参数。存储过程的代码括在 BEGIN 和 END 语句中，这里执行一条简单的 SELECT 语句，它检索具有邮件地址的顾客。然后用检索出的行数设置 ListCount（要传递的输出参数）。

第20课 管理事务处理

事务处理

使用事务处理（transaction processing），通过确保成批的 SQL 操作要么完全执行，要么完全不执行，来维护数据库的完整性。

关系数据库把数据存储在多个表中，使数据更容易操纵、维护和重用。不用深究如何以及为什么进行关系数据库设计，在某种程度上说，设计良好的数据库模式都是关联的。

事务处理是一种机制，用来管理必须成批执行的 SQL 操作，保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们要么完全执行，要么完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表；如果发生错误，则进行回退（撤销），将数据库恢复到某个已知且安全的状态。

下面是关于事务处理需要知道的几个术语：

• 事务（transaction）指一组 SQL 语句；
• 回退（rollback）指撤销指定 SQL 语句的过程；
• 提交（commit）指将未存储的 SQL 语句结果写入数据库表；
• 保留点（savepoint）指事务处理中设置的临时占位符（placeholder），可以对它发布回退（与回退整个事务处理不同）。

可以回退哪些语句：事务处理用来管理 INSERT、UPDATE 和 DELETE 语句。不能回退 SELECT 语句（回退 SELECT 语句也没有必要），也不能回退 CREATE 或 DROP 操作。

控制事务处理

管理事务的关键在于将 SQL 语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。

MySQL 中的标识：

START TRANSACTION
...
复制代码

事务一直存在，直到被中断。通常，COMMIT 用于保存更改，ROLLBACK 用于撤销，详述如下。

使用 ROLLBACK

DELETE FROM Orders;
ROLLBACK;
复制代码

在此例子中，执行 DELETE 操作，然后用 ROLLBACK 语句撤销。虽然这不是最有用的例子，但它的确能够说明，在事务处理块中，DELETE 操作（与 INSERT 和 UPDATE 操作一样）并不是最终的结果。

使用 COMMIT

一般的 SQL 语句都是针对数据库表直接执行和编写的。这就是所谓的隐式提交（implicit commit），即提交（写或保存）操作是自动进行的。

在事务处理块中，提交不会隐式进行。不过，不同 DBMS 的做法有所不同。有的 DBMS 按隐式提交处理事务端，有的则不这样。

进行明确的提交，使用COMMIT语句。

Oracle 示例：

SET TRANSACTION
DELETE OrderItems WHERE order_num = 12345;
DELETE OrderItems WHERE order_num = 12345;
COMMIT;
复制代码

使用保留点

使用简单的 ROLLBACK 和 COMMIT 语句，就可以写入或撤销整个事务。但是，只对简单的事务才能这样做，复杂的事务可能需要部分提交或回退。

要支持回退部分事务，必须在事务处理块中的合适位置放置占位符。这样，如果需要回退，可以回退到某个占位符。

MySQL 和 Oracle 示例：

ROLLBACK TO delete1;
复制代码

第21课 使用游标

游标

有时，需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。游标（cursor）是一个存储在 DBMS 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

不同的 DBMS 支持不同的游标选项和特性。常见的一些选项和特性如下：

• 能够标记游标为只读，使数据能读取，但不能更新和删除
• 能控制可以执行的定向操作（向前、向后、第一、最后、绝对位置、相对位置等）
• 能标记某些列为可编辑的，某些列为不可编辑的。
• 规定范围，使游标对创建它的特定请求（如存储过程）或对所有请求可访问。
• 指示 DBMS 对检索出的数据（而不是指出表中活动数据）进行复制，使数据在游标打开和访问期间不变化。

使用游标

使用游标有几个明确的步骤：

• 在使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句和游标选项。
• 一旦声明，就必须打开游标以供使用。这个过程用前面定义的 SELECT 语句把数据实际检索出来。
• 对于填有数据的游标，根据需要取出（检索）各行。
• 在结束游标使用时，必须关闭游标，可能的话，释放游标（有赖于具体的DBMS）。

创建游标

使用 DECLARE 语句创建游标，这条语句在不同的 DBMS 中有所不同。DECLARE 命名游标，并定义相应的 SELECT 语句，根据需要带 WHERE 和其他子句。

DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL;
复制代码

使用游标

OPEN CURSOR CustCursor;
复制代码

现在可以用 FETCH 语句访问游标数据了。FETCH 指出要检索哪些行，从何处检索它们以及将它们放于何处（如变量名）。

使用 Oracle 语法从游标中检索一行：

DECLARE TYPE CustCursor IS REF CURSOR RETURN Customers%ROWTYPE
DECLARE CustRecord Customers%ROWTYPE;
BEGIN
	OPEN CustCursor;
	FETCH CustCursor INTO CustRecord;
	CLOSE CustCursor;
END;
复制代码

关闭游标

CLOSE 语句用来关闭游标。一旦游标关闭，如果不再次打开，将不能使 用。第二次使用它时不需要再声明，只需用 OPEN 打开它即可。

CLOSE CustCursor
复制代码

第22课 高级SQL特性

约束

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

主键

主键是一种特殊的约束，用来 一组列）中的值是唯一的，而且永不改动。换句话说，表中的一列（或 多个列）的值唯一标识表中的每一行。这方便了直接或交互地处理表中的行。没有主键，要安全地 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,
	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);
复制代码

外键

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

定义外键的方法：

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)
);
复制代码

也可以用 CONSTRAINT 来完成：

ALTER TABLE Orders
ADD CONSTRAINT
FOREIGN KEY (cust_id) REFERENCES Customers (cust_id)
复制代码

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

唯一约束

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

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

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

检查约束

检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。检 查约束的常见用途有以下几点：

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

检查 约束在数据类型内又做了进一步的限制，这些限制极其重要，可以确保插 入数据库的数据正是你想要的数据。不需要依赖于客户端应用程序或用户 来保证正确获取它，DBMS 本身将会拒绝任何无效的数据。

施加检查约束：

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
);
复制代码

检查名为 gender 的列只包含 M 或 F，可编写如下的 ALTER TABLE 语句：

ADD CONSTRAINT CHECK (gender LIKE '[MF]')
复制代码

索引

索引用来排序数据以加快搜索和排序操作的速度。想像一本书后的索引 （如本书后的索引），可以帮助你理解数据库的索引。

假如要找出本书中所有的“数据类型”这个词，简单的办法是从第 1 页 开始，浏览每一行。虽然这样做可以完成任务，但显然不是一种好的办法。浏览少数几页文字可能还行，但以这种方式浏览整部书就不可行了。 随着要搜索的页数不断增加，找出所需词汇的时间也会增加。

这就是书籍要有索引的原因。索引按字母顺序列出词汇及其在书中的位 置。为了搜索“数据类型”一词，可在索引中找出该词，确定它出现在 哪些页中。然后再翻到这些页，找出“数据类型”一词。

使索引有用的因素是什么？很简单，就是恰当的排序。找出书中词汇的 困难不在于必须进行多少搜索，而在于书的内容没有按词汇排序。如果 书的内容像字典一样排序，则索引没有必要（因此字典就没有索引）。

数据库索引的作用也一样。主键数据总是排序的，这是 DBMS 的工作。 因此，按主键检索特定行总是一种快速有效的操作。

但是，搜索其他列中的值通常效率不高。例如，如果想搜索住在某个州的客户，怎么办？因为表数据并未按州排序，DBMS 必须读出表中所有行（从第一行开始），看其是否匹配。这就像要从没有索引的书中找出词汇一样。

解决方法是使用索引。可以在一个或多个列上定义索引，使 DBMS 保存 其内容的一个排过序的列表。在定义了索引后，DBMS 以使用书的索引类似的方法使用它。DBMS 搜索排过序的索引，找出匹配的位置，然后检索这些行。

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

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

没有严格的规则要求什么应该索引，何时索引。大多数 DBMS 提供了可 用来确定索引效率的实用程序，应该经常使用这些实用程序。

索引用 CREATE INDEX 语句创建：

CREATE INDEX prod_name_ind
ON Products (prod_name);
复制代码

索引必须唯一命名。

触发器

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

与存储过程不一样（存储过程只是简单的存储 SQL 语句），触发器与单个的表相关联。与 Orders 表上的 INSERT 操作相关联的触发器只在 Orders 表中插入行时执行。 类似地， Customers 表上的 INSERT 和 UPDATE 操作的触发器只在 Customers 表上出现这些操作时执行。

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

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

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

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

不同 DBMS 的触发器创建语法差异很大，更详细的信息请参阅相应的文档。

Oracle 版本：

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;
复制代码

数据库安全

对于组织来说，没有什么比它的数据更重要了，因此应该保护这些数据， 使其不被偷盗或任意浏览。当然，数据也必须允许需要访问它的用户访 问，因此大多数 DBMS 都给管理员提供了管理机制，利用管理机制授予 或限制对数据的访问。

任何安全系统的基础都是用户授权和身份确认。这是一种处理，通过这 种处理对用户进行确认，保证他是有权用户，允许执行他要执行的操作。 有的 DBMS 为此结合使用了操作系统的安全措施，而有的维护自己的用户及密码列表，还有一些结合使用外部目录服务服务器。

一般说来，需要保护的操作有：

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

转载于:https://juejin.im/post/5cdb41ab51882568666dfc80