SQL学习

法则1：col

table表/columns列/rows行

问题：movies表有100万数据？

法则2：select

SELECT col,col,col, * 找什么？
FROM table从哪找？
WHEREcol条件？条件是啥？AND OR

任务

• task1 boss要电影名字清单
  SELECT title
FROM movies
WHERE 1;
• task2 boss要电影名字+年份清单
  SELECT title,year
FROM movies
WHERE 1;
• task3 boss要1995-2000年电影，ID，名字，年份？
  SELECT id,title,year
FROM movies
WHERE year BETWEEN 1995 AND 2000;(也可用WHERE year >= 1995 AND year <= 2000; 或者用WHERE year IN (1995,1996,1997,1998,1999,2000);)
• task4 boss要toy story系列电影，ID，名字，年份？
  SELECT id,title,year
FROM movies
WHERE title LIKE "toy story%";
• task5 boss要John Lasseter导演的电影，按年份降序排列最后三部，要ID，名字，年份？
  SELECT id,title,director, year
FROM movies
WHERE director = "John Lasseter"
ORDER BY year DESC LIMIT 3;
• task6 boss要按导演电影数量推荐排序最高的三个？
  SELECT director ,COUNT(Director) AS count
FROM movies
WHERE 1
GROUP BY director
ORDER BY count DESC, DIRECTOR ASC LIMIT 3;

条件：数字（where）

当查找条件col是数字
select * from table where col = 1;

条件：文本（where）

当查找条件col是文本
select * from table where col like '%jin';

排序（rows）

需要对结果rows排序和筛选部分rows
select * from table where col >1 order by col asc limit 2 offset 2

统计（select）

对查找的rows需要按col分组统计的情况
select count(*),avg(col),col from table where col > 1 group by col

提示

SQL语句不区分大小写；
在处理 SQL语句时，其中所有空格都被忽略。

检索单个列

SELECT prod_name
FROM Products;

检索多个列

SELECT prod_id, prod_name, prod_price
FROM Products;

检索所有列

SELECT *
FROM Products;

检索不同的值

使用 DISTINCT 关键字，它指示数据库只返回不同的值。

SELECT DISTINCT vend_id
FROM Products;

注意：不能部分使用 DISTINCT
DISTINCT 关键字作用于所有的列，不仅仅是跟在其后的那一列。例
如，你指定 SELECT DISTINCT vend_id, prod_price ，除非指定的
两列完全相同，否则所有的行都会被检索出来。

限制结果

在 SQL Server和 Access中使用 SELECT 时，可以使用 TOP 关键字来限制最多返回多少行，如下所示：

SELECT TOP 5 prod_name
FROM Products;

如果使用的是DB2,DBMS特定的SQL语句为：

SELECT prod_name
FROM Products
FETCH FIRST 5 ROWS ONLY;

如果使用的是Oracle，需要基于ROWNUM（行计数器）来计算行：

SELECT prod_name
FROM Products
WHERE ROWNUM <=5;

如果使用MySQL、MariaDB、PostgreSQL或SQLite，需要使用limit子句：

SELECT prod_name
FROM Products
LIMIT 5;

为了得到后面的 5行数据，需要指定从哪儿开始以及检索的行数:

SELECT prod_name
FROM Products
LIMIT 5 OFFSET 5;

所以， LIMIT 指定返回的行数。 LIMIT 带的 OFFSET 指定从哪儿开始。
注意：第 0 行
第一个被检索的行是第0行，而不是第 1行。因此， LIMIT 1 OFFSET
1 会检索第 2行，而不是第 1行。

注释

1、注释使用 -- （两个连字符）嵌在行内。 -- 之后的文本就是注释。
2、在一行的开始处使用 # ，这一整行都将作为注释。
3、注释从 /* 开始，到 / 结束， / 和 */ 之间的任何内容都是注释。

3 排序检索数据

如何使用 SELECT 语句的 ORDER BY 子句，根据需要排序检索
出的数据。
为了明确地排序用 SELECT 语句检索出的数据，可使用 ORDER BY 子句。ORDER BY 子句取一个或多个列的名字，据此对输出以字母顺序排序。

SELECT prod_name
FROM Products
ORDER BY prod_name;

注意： ORDER BY 子句的位置
在指定一条 ORDER BY 子句时，应该保证它是 SELECT 语句中最后一条子句。如果它不是最后的子句，将会出现错误消息。

3.2 按多个列排序

要按多个列排序，简单指定列名，列名之间用逗号分开即可。下面的代码检索 3 个列，并按其中两个列对结果进行排序——首先按价格，然后按名称排序。

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;

3.3 按列位置排序

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY 2, 3;

这里的输出与上面的查询相同，不同之处在于 ORDER BY 子句。SELECT 清单中指定的是选择列的相对位置而不是列名。 ORDER BY 2表示按 SELECT 清单中的第二个列 prod_name 进行排序。 ORDER BY 2，3 表示先按 prod_price ，再按 prod_name 进行排序。

3.4 指定排序方向

数据默认按升序排序（从 A到 Z），还可以使用 ORDER BY 子句进行降序（从 Z 到 A）排序，此时必须指定 DESC 关键字。

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC;

用多个列排序:

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name;

DESC 关键字只应用到直接位于其前面的列名。在上例中，只对prod_price列指定 DESC ，对 prod_name 列不指定。因此， prod_price 列以降序排序，而 prod_name 列（在每个价格内）仍然按标准的升序排序。
注意：在多个列上降序排序
如果想在多个列上进行降序排序，必须对每一列指定 DESC 关键字。

4 过滤数据

如何使用 SELECT 语句的 WHERE 子句指定搜索条件。

4.1 使用WHERE子句

数据库表一般包含大量的数据，很少需要检索表中的所有行。通常只会
根据特定操作或报告的需要提取表数据的子集。只检索所需数据需要指
定搜索条件（search criteria），搜索条件也称为过滤条件（filter condition）。
在 SELECT 语句中，数据根据 WHERE 子句中指定的搜索条件进行过滤。WHERE 子句在表名（ FROM 子句）之后给出，如下所示：

SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49;

注意： WHERE 子句的位置
在同时使用 ORDER BY 和 WHERE 子句时，应该让ORDER BY 位于
WHERE 之后，否则将会产生错误.

4.2 WHERE子句操作符

4.2.1 范围值检查

要检查某个范围的值，可以使用 BETWEEN 操作符。它需要两个值，即范围的开始值和结束值。

SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;

4.2.2 空值检查

在创建表时，表设计人员可以指定其中的列能否不包含值。在一个列不
包含值时，称其包含空值 NULL 。

SELECT cust_name
FROM CUSTOMERS
WHERE cust_email IS NULL;

如果表中没有空行，就没有返回数据，如果有包含具有NULL值的列，返回那几列。

5 高级数据过滤

如何组合 WHERE 子句以建立功能更强、更高级的搜索条件。如何使用 NOT 和 IN 操作符。

5.1 组合WHERE子句

SQL允许给出多个 WHERE 子句。这些子句有两种使用方式，即以 AND 子句或 OR 子句的方式使用。用来联结或改变 WHERE 子句中的子句的关键字，也称为逻辑操作符（logical operator）。

5.1.1 AND操作符

SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4;

可以增加多个过滤条件，每个条件间都要使用 AND 关键字。

5.1.2 OR操作符

许多 DBMS在 OR WHERE 子句的第一个条件得到满足的情况下，就不再计算第二个条件了（在第一个条件满足时，不管第二个条件是否满足，相应的行都将被检索出来）。

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = ‘BRS01’;

5.1.3 求值顺序

WHERE 子句可以包含任意数目的 AND 和 OR 操作符。允许两者结合以进行复杂、高级的过滤。
假如需要列出价格为10美元及以上，且由 DLL01 或 BRS01制造的所有产品。

SELECT prod_name, prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')
AND prod_price >= 10;

注意：
SQL在处理 OR 操作符前，优先处理 AND 操作符。
圆括号具有比 AND 或 OR 操作符更高的求值顺序。

5.2 IN操作符

IN 操作符用来指定条件范围，范围中的每个条件都可以进行匹配。 IN 取
一组由逗号分隔、括在圆括号中的合法值。

SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ( 'DLL01', 'BRS01' )
ORDER BY prod_name;

IN操作符的优点

• 在有很多合法选项时， IN 操作符的语法更清楚，更直观。
• 在与其他 AND 和 OR 操作符组合使用 IN 时，求值顺序更容易管理。
• IN 操作符一般比一组 OR 操作符执行得更快（在上面这个合法选项很
  少的例子中，你看不出性能差异）。
• IN 的最大优点是可以包含其他 SELECT语句，能够更动态地建立WHERE 子句。

5.3 NOT操作符

WHERE 子句中的 NOT 操作符有且只有一个功能，即否定其后所跟的任何条件。 NOT 关键字可以用在要过滤的列前，而不仅是在其后（也可以使用 <> 操作符来完成）。

SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;

6 用通配符进行过滤

介绍什么是通配符、如何使用通配符以及怎样使用 LIKE 操作符进行通配搜索，以便对数据进行复杂过滤。

6.1 LIKE操作符

通配符（wildcard）：用来匹配值的一部分的特殊字符。
搜索模式（search pattern）由字面值、通配符或两者组合构成的搜索条件。
操作符何时不是操作符？答案是，它作为谓词时。从技术上说， LIKE
是谓词而不是操作符。
通配符搜索只能用于文本字段（字符串），非文本数据类型字段不能使用通配符搜索。

6.1.1 百分号（%）通配符

最常使用的通配符是百分号（ % ）。在搜索串中， % 表示任何字符出现任意次数。例如，为了找出所有以词 Fish 起头的产品，可发布以下 SELECT 语句：

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';

注意：

• Access 通配符：如果使用的是 Microsoft Access，需要使用 * 而不是 % 。
• 区分大小写：根据 DBMS的不同及其配置，搜索可以是区分大小写的。如果区分大小写，则 'fish%' 与 Fish bean bag toy 就不匹配。

通配符可在搜索模式中的任意位置使用，并且可以使用多个通配符。

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '%bean bag%';

通配符也可以出现在搜索模式的中间，下面的例子找出以 F 起头、以 y 结尾的所有产品：

SELECT prod_name
FROM Products
WHERE prod_name LIKE 'F%y';

有一种情况下把通配符放在搜索模式中间是很有用的，就是根据邮件地 址 的 一 部 分 来 查 找 电 子 邮 件 ， 例 如 WHERE email LIKE 'b%@forta.com' 。

需要特别注意，除了能匹配一个或多个字符外，% 还能匹配 0 个字符。%代表搜索模式中给定位置的 0个、1个或多个字符。

通配符 % 看起来像是可以匹配任何东西，但有个例外，这就是 NULL 。
子句 WHERE prod_name LIKE '%' 不会匹配产品名称为 NULL 的行。

6.1.2 下划线（_）通配符

下划线的用途与 % 一样，但它只匹配单个字符，而不是多个字符。

• DB2不支持通配符 _ 。
• 如果使用的是 Microsoft Access，需要使用 ? 而不是 _ 。

这个 WHERE 子句中的搜索模式给出了后面跟有文本的两个通配符。

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';

与 % 能匹配 0个字符不同， _ 总是刚好匹配一个字符，不能多也不能少。

6.1.3 方括号（[ ]）通配符

方括号（ [] ）通配符用来指定一个字符集，它必须匹配指定位置（通配
符的位置）的一个字符。
例如，找出所有名字以 J 或 M 起头的联系人，可进行如下查询：

SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

此语句的 WHERE 子句中的模式为 '[JM]%' 。这一搜索模式使用了两个不同的通配符。 [JM] 匹配方括号中任意一个字符，它也只能匹配单个字符。因此，任何多于一个字符的名字都不匹配。 [JM] 之后的 % 通配符匹配第一个字符之后的任意数目的字符，返回所需结果。

## 输出：
cust_contact
-----------------
Jim Jones
John Smith
Michelle Green

此通配符可以用前缀字符 ^ ^ （脱字号）来否定。例如，下面的查询匹配以J 和 M 之外的任意字符起头的任意联系人名（与前一个例子相反）：

SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[ ^JM]%'
ORDER BY cust_contact;

如果使用的是 Microsoft Access，需要用 ! 而不是 ^ 来否定一个集合，因
此，使用的是 [!JM] 而不是 [ ^ JM] 。

7 创建计算字段

介绍什么是计算字段，如何创建计算字段，以及如何从应用程序中使用别名引用它们。

7.1 计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式，例如：

• 需要显示公司名，同时还需要显示公司的地址，但这两个信息存储在不同的表列中。
• 城市、州和邮政编码存储在不同的列中（应该这样），但邮件标签打印程序需要把它们作为一个有恰当格式的字段检索出来。
• 列数据是大小写混合的，但报表程序需要把所有数据按大写表示出来。

计算字段是运行时在 SELECT 语句内创建的。
字段（field）：基本上与列（column）的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常与计算字段一起使用。

7.2 拼接字段

创建由两列组成的标题。Vendors 表包含供应商名和地址信息。假如要生成一个供应商报表，需要在格式化的名称（位置）中列出供应商的位置。此报表需要一个值，而表中数据存储在两个列 vend_name 和 vend_
country 中。此外，需要用括号将 vend_country 括起来，这些东西都
没有存储在数据库表中。

拼接（concatenate）：将值联结到一起（将一个值附加到另一个值）构成单个值。Access和 SQL Server使用 + 号。DB2、Oracle、PostgreSQL、SQLite和Open Office Base 使用 || 。

SELECT vend_name + ' (' + vend_country + ')'
FROM Vendors
ORDER BY vend_name;

## 输出
-----------------------------------------------------------
Bear Emporium (USA )
Bears R Us (USA )
Doll House Inc. (USA )
Fun and Games (England )
Furball Inc. (USA )
Jouets et ours (France )

下面是相同的语句，但使用的是 || 语法：

SELECT vend_name || ' (' || vend_country || ')'
FROM Vendors
ORDER BY vend_name;

下面是使用 MySQL或 MariaDB时需要使用的语句：

SELECT Concat(vend_name, ' (', vend_country, ')')
FROM Vendors
ORDER BY vend_name;

为正确返回格式化的数据，必须去掉这些空格，可使用 SQL的 RTRIM() 函数来完成。

SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
FROM Vendors
ORDER BY vend_name;

## 输出
-----------------------------------------------------------
Bear Emporium (USA)
Bears R Us (USA)
Doll House Inc. (USA)
Fun and Games (England)
Furball Inc. (USA)
Jouets et ours (France)

大多数 DBMS都支持 RTRIM() （正如刚才所见，它去掉字符串右边的空格）、 LTRIM() （去掉字符串左边的空格）以及 TRIM() （去掉字符串左右两边的空格）。

使用别名

别名（alias）是一个字段或值的替换名。别名用 AS 关键字赋予。

SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
               AS vend_title
FROM Vendors
ORDER BY vend_name;

7.3 执行算术计算

计算字段的另一常见用途是对检索出的数据进行算术计算。举个例子，Orders 表包含收到的所有订单， OrderItems 表包含每个订单中的各项物品。下面的 SQL语句检索订单号 20008 中的所有物品：

SELECT prod_id,
               quantity,
               item_price,
               quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

8 使用函数处理数据

什么是函数，DBMS支持何种函数，如何使用这些函数，为什么 SQL函数的使用可能会带来问题？...

8.1 函数

8.2 使用函数

大多数 SQL实现支持以下类型的函数：

• 用于处理文本字符串（如删除或填充值，转换值为大写或小写）的文本函数。
• 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。
• 用于处理日期和时间值并从这些值中提取特定成分（如返回两个日期之差，检查日期有效性）的日期和时间函数。
• 返回 DBMS正使用的特殊信息（如返回用户登录信息）的系统函数。

8.2.1 文本处理函数

下面给出一个使用 SOUNDEX() 函数的例子。 Customers 表中有一个顾客Kids Place ，其联系名为 Michelle Green 。但如果这是错误的输入，此联系名实际上应该是 Michael Green ，该怎么办呢？显然，按正确的联系名搜索不会返回数据，如下所示：

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_contact = 'Michael Green';

现在试一下使用 SOUNDEX() 函数进行搜索，它匹配所有发音类似于Michael Green 的联系名：

SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

8.2.2 日期和时间处理函数

Orders 表中包含的订单都带有订单日期。为在 SQL Server中检索 2012年的所有订单，可如下进行：

SELECT order_num
FROM Orders
WHERE DATEPART(yy, order_date) = 2012;

在 Access中使用如下版本：

SELECT order_num
FROM Orders
WHERE DATEPART('yyyy', order_date) = 2012;

DATEPART()函数返回日期的某一部分。 DATEPART() 函数有两个参数，它们分别是返回的成分和从中返回成分的日期。
下面是使用名为 DATE_PART() 的类似函数的 PostgreSQL版本：

SELECT order_num
FROM Orders
WHERE DATE_PART('year', order_date) = 2012;

Oracle没有 DATEPART() 函数，不过有几个可用来完成相同检索的日期处理函数。例如：

SELECT order_num
FROM Orders
WHERE to_number(to_char(order_date, 'YYYY')) = 2012;

to_char() 函数用来提取日期的成分， to_number() 用来将提取出的成分转换为数值，以便能与 2012 进行比较。
完成相同工作的另一方法是使用 BETWEEN 操作符：

SELECT order_num
FROM Orders
WHERE order_date BETWEEN to_date('01-01-2012')
AND to_date('12-31-2012');

MySQL和 MariaDB用户可使用名为 YEAR() 的函数从日期中提取年份：

SELECT order_num
FROM Orders
WHERE YEAR(order_date) = 2012;

8.2.3 数值处理函数

9 汇总数据

什么是 SQL的聚集函数，如何利用它们汇总表的数据。

9.1 聚集函数

聚集函数（aggregate function）：对某些行运行的函数，计算并返回一个值。

9.1.1 AVG()函数

AVG() 通过对表中行数计数并计算其列值之和，求得该列的平均值。AVG()可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。
下面的例子使用 AVG() 返回 Products 表中所有产品的平均价格：

SELECT AVG(prod_price) AS avg_price
FROM Products;

下面的例子返回特定供应商所提供产品的平均价格：

SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

为了获得多个列的平均值，必须使用多个 AVG() 函数。
AVG() 函数忽略列值为 NULL 的行。

9.1.2 COUNT()函数

COUNT() 函数进行计数。可利用 COUNT() 确定表中行的数目或符合特定条件的行的数目。COUNT() 函数有两种使用方式：

• 使用 COUNT(*) 对表中行的数目进行计数，不管表列中包含的是空值（ NULL ）还是非空值。
• 使用 COUNT(column) 对特定列中具有值的行进行计数，忽略 NULL 值。

下面的例子返回 Customers 表中顾客的总数：

SELECT COUNT(*) AS num_cust
FROM Customers;

在此例子中，利用 COUNT(*) 对所有行计数，不管行中各列有什么值。计数值在 num_cust 中返回。下面的例子只对具有电子邮件地址的客户计数：

SELECT COUNT(cust_email) AS num_cust
FROM Customers;

如果指定列名，则 COUNT() 函数会忽略指定列的值为空的行，但如果COUNT() 函数中用的是星号（ * ），则不忽略。

9.1.3 MAX()函数

MAX() 返回指定列中的最大值。 MAX() 要求指定列名，如下所示：

SELECT MAX(prod_price) AS max_price
FROM Products;

在用于文本数据时， MAX() 返回按该列排序后的最后一行。
MAX() 函数忽略列值为 NULL 的行。

9.1.4 MIN()函数

MIN() 的功能正好与 MAX() 功能相反，它返回指定列的最小值。与MAX()一样， MIN() 要求指定列名，如下所示：

SELECT MIN(prod_price) AS min_price
FROM Products;

在用于文本数据时， MIN() 返回该列排序后最前面的行。
MIN() 函数忽略列值为 NULL 的行。

9.1.5 SUM()函数

SUM() 用来返回指定列值的和（总计）。

SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE order_num = 20005;

SUM() 也可以用来合计计算值。在下面的例子中，合计每项物品的item_price*quantity ，得出总的订单金额：

SELECT SUM(item_price*quantity) AS total_price
FROM OrderItems
WHERE order_num = 20005;

9.2 聚集不同值

以上 5个聚集函数都可以如下使用。

• 对所有行执行计算，指定 ALL 参数或不指定参数（因为 ALL 是默认行为）。
• 只包含不同的值，指定 DISTINCT 参数。

Microsoft Access 在聚集函数中不支持 DISTINCT；

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

在使用了 DISTINCT 后，此例子中的 avg_price 比较高，因为有多个物品具有相同的较低价格。排除它们提升了平均价格。
如果指定列名，则 DISTINCT 只能用于 COUNT() 。 DISTINCT 不能用于 COUNT(*) 。类似地， DISTINCT 必须使用列名，不能用于计算或表达式。

9.3 组合聚集函数

目前为止的所有聚集函数例子都只涉及单个函数。但实际上， SELECT语句可根据需要包含多个聚集函数。

SELECT COUNT(*) AS num_items,
               MIN(prod_price) AS price_min,
               MAX(prod_price) AS price_max,
               AVG(prod_price) AS price_avg
FROM Products;

10 分组数据

如何分组数据，以便汇总表内容的子集。这涉及两个新SELECT 语句子句： GROUP BY 子句和 HAVING 子句。

10.1 创建分组

分组是使用 SELECT 语句的 GROUP BY 子句建立的。

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;

## 输出
vend_id num_prods
------- ---------
BRS01   3
DLL01   4
FNG01   2

上面的 SELECT 语句指定了两个列： vend_id 包含产品供应商的 ID，num_prods 为计算字段（用 COUNT(*) 函数建立）。 GROUP BY 子句指示DBMS按 vend_id 排序并分组数据。这就会对每个 vend_id 而不是整个表计算 num_prods 一次。从输出中可以看到，供应商 BRS01 有 3 个产品，供应商 DLL01 有 4 个产品，而供应商 FNG01 有 2 个产品。

• GROUP BY 子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。
• 如果在 GROUP BY 子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
• GROUP BY 子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在 SELECT 中使用表达式，则必须在 GROUP BY子句中指定相同的表达式。不能使用别名。
• 大多数 SQL实现不允许 GROUP BY 列带有长度可变的数据类型（如文本或备注型字段）。
• 除聚集计算语句外， SELECT 语句中的每一列都必须在 GROUP BY 子句中给出。
• 如果分组列中包含具有 NULL 值的行，则 NULL 将作为一个分组返回。如果列中有多行 NULL 值，它们将分为一组。
• GROUP BY 子句必须出现在 WHERE 子句之后， ORDER BY 子句之前。

10.3 过滤分组

HAVING 非常类似于 WHERE 。事实上，目前为止所学过的所有类型的WHERE 子句都可以用 HAVING 来替代。唯一的差别是， WHERE过滤行，而 HAVING 过滤分组。

SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

列出具有两个以上产品且其价格大于等于 4 的供应商：

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;

10.4 分组和排序

一般在使用 GROUP BY 子句时，应该也给出 ORDER BY 子句。这是保证数据正确排序的唯一方法。千万不要仅依赖 GROUP BY 排序数据。
检索包含三个或更多物品的订单号和订购物品的数目：

SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3;

## 输出
order_num items
--------- -----
20006 3
20007 5
20008 5
20009 3

要按订购物品的数目排序输出，需要添加 ORDER BY 子句，如下所示：

SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;

order_num items
--------- -----
20006 3
20009 3
20007 5
20008 5

使用 GROUP BY 子句按订单号（ order_num 列）分组数据，以便COUNT(*) 函数能够返回每个订单中的物品数目。 HAVING 子句过滤数据，使得只返回包含三个或更多物品的订单。最后，用 ORDER BY子句排序输出。

10.5 SELECT子句顺序

11 使用子查询

什么是子查询，如何使用它们。

11.1 子查询

查询（query）：任何 SQL语句都是查询。但此术语一般指 SELECT语句。
简单查询：即从单个数据库表中检索数据的单条语句。迄今为止所看到的所有 SELECT 语句都是简单查询。
子查询（subquery）：即嵌套在其他查询中的查询。

11.2 利用子查询进行过滤

假如需要列出订购物品 RGAN01 的所有顾客，应该怎样检索？下面列出具体的步骤。
(1) 检索包含物品 RGAN01 的所有订单的编号。
(2) 检索具有前一步骤列出的订单编号的所有顾客的 ID。
(3) 检索前一步骤返回的所有顾客 ID的顾客信息。

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
                  FROM Orders
                  WHERE order_num IN (SELECT order_num
                                      FROM OrderItems
                                      WHERE prod_id = 'RGAN01'));

在 SELECT 语句中，子查询总是从内向外处理。
作为子查询的 SELECT 语句只能查询单个列。企图检索多个列将返回错误。

11.3 作为计算字段使用子查询

使用子查询的另一方法是创建计算字段。假如需要显示 Customers 表中每个顾客的订单总数。订单与相应的顾客 ID存储在 Orders 表中。
执行这个操作，要遵循下面的步骤：
(1) 从 Customers 表中检索顾客列表；
(2) 对于检索出的每个顾客，统计其在 Orders 表中的订单数目。

SELECT cust_name,
       cust_state,
       (SELECT COUNT(*)
        FROM Orders
        WHERE cust_id = cust_id) AS orders
FROM Customers
ORDER BY cust_name;

12 联结表

介绍什么是联结，为什么使用联结，如何编写使用联结的SELECT 语句。

12.1 联结

12.1.1 关系表

关系表的设计就是要把信息分解成多个表，一类数据一个表。各表通过某些共同的值互相关联（所以才叫关系数据库）。

12.1.2 为什么使用联结

联结是一种机制，用来在一条 SELECT 语句中关联表，因此称为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

12.2 创建联结

指定要联结的所有表以及关联它们的方式即可。请看下面的例子：

SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;

在引用的列可能出现歧义时，必须使用完全限定列名（用一个句点分隔表名和列名）。

12.2.1 内联结

目前为止使用的联结称为等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内联结（inner join）。其实，可以对这种联结使用稍微不同的语法，明确指定联结的类型。下面的 SELECT 语句返回与前面例子完全相同的数据：

SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;

12.2.2 联结多个表

SQL 不限制一条 SELECT 语句中可以联结的表的数目。创建联结的基本
规则也相同。首先列出所有表，然后定义表之间的关系。例如：

SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;

13 创建高级联结

讲解另外一些联结（包括它们的含义和使用方法），介绍如何使用表别名，如何对被联结的表使用聚集函数。

13.1 使用表别名

SQL 除了可以对列名和计算字段使用别名，还允许给表名起别名。
请看下面的 SELECT 语句。它与前一课例子中所用的语句基本相同：

SELECT cust_name, cust_contact
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
   AND OI.order_num = O.order_num
   AND prod_id = 'RGAN01';

13.2 使用不同类型的联结

迄今为止，使用的只是内联结或等值联结的简单联结。现在来看三种其他联结：自联结（self-join）、自然联结（natural join）和外联结（outer join）。

13.2.1 自联结

使用表别名的一个主要原因是能在一条 SELECT 语句中不止一次引用相同的表。
假如要给与 Jim Jones同一公司的所有顾客发送一封信件。这个查询要求
首先找出 Jim Jones工作的公司，然后找出在该公司工作的顾客。下面是
解决此问题的一种方法：

SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
                   FROM Customers
                   WHERE cust_contact = 'Jim Jones');

使用了子查询。内部的 SELECT 语句做了一个简单检索，返回 Jim Jones工作公司的 cust_name 。该名字用于外部查询的WHERE 子句中，以检索出为该公司工作的所有雇员
使用联结的相同查询：

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
  AND c2.cust_contact = 'Jim Jones';

自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。

13.2.2 自然联结

无论何时对表进行联结，应该至少有一列不止出现在一个表中（被联结的列）。标准的联结（前一课中介绍的内联结）返回所有数据，相同的列甚至多次出现。自然联结排除多次出现，使每一列只返回一次。
自然联结要求你只能选择那些唯一的列，一般通过对一个表使用通配符
（ SELECT * ），而对其他表的列使用明确的子集来完成。下面举一个例子：

SELECT C.*, O.order_num, O.order_date,
     OI.prod_id, OI.quantity, OI.item_price
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
   AND OI.order_num = O.order_num
   AND prod_id = 'RGAN01';

13.2.3 外联结

许多联结将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行。例如，可能需要使用联结完成以下工作：

• 对每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客；
• 列出所有产品以及订购数量，包括没有人订购的产品；
• 计算平均销售规模，包括那些至今尚未下订单的顾客。

联结包含了那些在相关表中没有关联行的行。这种联结称为外联结。
下面的 SELECT 语句给出了一个简单的内联结。它检索所有顾客及其订单：

SELECT Customers.cust_id, Orders.order_num
FROM Customers INNER JOIN Orders
   ON Customers.cust_id = Orders.cust_id;

外联结语法类似。要检索包括没有订单顾客在内的所有顾客，可如下进行：

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
  ON Customers.cust_id = Orders.cust_id;

这条 SELECT 语句使用了关键字 OUTER JOIN来指定联结类型（而不是在 WHERE 子句中指定）。在使用 OUTER JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表（ RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT 指出的是OUTER JOIN左边的表）。上面的例子使用 LEFT OUTER JOIN 从 FROM 子句左边的表（ Customers 表）中选择所有行。

还存在另一种外联结，就是全外联结（full outer join），它检索两个表中
的所有行并关联那些可以关联的行。与左外联结或右外联结包含一个表
的不关联的行不同，全外联结包含两个表的不关联的行。全外联结的语
法如下：

SELECT Customers.cust_id, Orders.order_num
FROM Orders FULL OUTER JOIN Customers
  ON Orders.cust_id = Customers.cust_id;

13.3 使用带聚集函数的联结

聚集函数用来汇总数据。虽然至今为止我们举的聚集函数的例子都只是从一个表中汇总数据，但这些函数也可以与联结一起使用。
我们来看个例子，要检索所有顾客及每个顾客所下的订单数，下面的代
码使用 COUNT() 函数完成此工作：

SELECT Customers.cust_id,
     COUNT(Orders.order_num) AS num_ord
FROM Customers INNER JOIN Orders
  ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

14 组合查询

如何利用 UNION 操作符将多条 SELECT 语句组合成一个结果集。

14.1 组合查询

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

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

14.2 创建组合查询

可用 UNION 操作符来组合数条 SQL 查询。利用 UNION ，可给出多条SELECT 语句，将它们的结果组合成一个结果集。

14.2.1 使用UNION

给出每条 SELECT 语句，在各条语句之间放上关键字 UNION 。
假如需要 Illinois、Indiana和 Michigan等美国几个州的所有顾客的报表，还想包括不管位于哪个州的所有的 Fun4All 。当然可以利用WHERE 子句来完成此工作，不过这次我们使用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子句的相同查询：

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

使用UNION规则：

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

14.2.3 包含或取消重复的行

UNION 从查询结果集中自动去除了重复的行，这是 UNION 的默认行为，如果想返回所有的匹配行，可使用 UNION ALL 而不是 UNION 。

14.2.4 对组合查询结果排序

SELECT 语句的输出用 ORDER BY 子句排序。在用 UNION 组合查询时，只能使用一条 ORDER BY 子句，它必须位于最后一条 SELECT 语句之后。对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条 ORDER BY 子句。

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;

这条 UNION 在最后一条 SELECT 语句后使用了 ORDER BY 子句。虽然 ORDER BY 子句似乎只是最后一条 SELECT 语句的组成部分，但实际上 DBMS将用它来排序所有 SELECT 语句返回的所有结果。

15 插入数据

介绍如何利用 SQL的 INSERT 语句将数据插入表中。

15.1 数据插入

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

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

15.1.1 插入完整的行

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

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

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

15.1.2 插入部分行

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

没有给 cust_contact 和 cust_email 这两列提供值。这表示没必要在INSERT 语句中包含它们。因此，这里的 INSERT语句省略了这两列及其对应的值。

15.1.3 插入检索出的数据

利用INSERT将 SELECT 语句的结果插入表中，这就是所谓的INSERT SELECT ，它是由一条 INSERT 语句和一条 SELECT语句组成的。
假如想把另一表中的顾客列合并到 Customers 表中。不需要每次读取一
行再将它用 INSERT 插入，可以如下进行：

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;

这个例子使用 INSERT SELECT 从 CustNew 中将所有数据导入Customers 。 SELECT 语句从 CustNew 检索出要插入的值，而不是列出它们。 SELECT 中列出的每一列对应于 Customers 表名后所跟的每一列。这条语句将插入多少行呢？这依赖于 CustNew 表有多少行。如果这个表为空，则没有行被插入（也不产生错误，因为操作仍然是合的）。如果这个表确实有数据，则所有数据将被插入到 Customers 。

15.2 从一个表复制到另一个表

有一种数据插入不使用 INSERT 语句。要将一个表的内容复制到一个全
新的表（运行中创建的表），可以使用 SELECT INTO 语句。（DB2不支持这里描述的 SELECT INTO ）
与 INSERT SELECT 将数据添加到一个已经存在的表不同， SELECT INTO将数据复制到一个新表（有的 DBMS可以覆盖已经存在的表，这依赖于所使用的具体 DBMS）。
** INSERT SELECT 与 SELECT INTO**：它们之间的一个重要差别是前者导出数据，而后者导入数据。
下面的例子说明如何使用 SELECT INTO ：

SELECT *
INTO CustCopy
FROM Customers;

这条 SELECT 语句创建一个名为 CustCopy 的新表，并把 Customers 表
的整个内容复制到新表中。因为这里使用的是 SELECT * ，所以将在
CustCopy 表中创建（并填充）与 Customers 表的每一列相同的列。要
想只复制部分的列，可以明确给出列名，而不是使用 * 通配符。

在使用 SELECT INTO 时，需要知道一些事情：
 任何 SELECT 选项和子句都可以使用，包括 WHERE 和 GROUP BY；
 可利用联结从多个表插入数据；
 不管从多少个表中检索数据，数据都只能插入到一个表中。

16 更新和删除数据

如何利用 UPDATE 和 DELETE 语句进一步操作表数据。

16.1 更新数据

更新（修改）表中的数据，可以使用 UPDATE 语句。有两种使用UPDATE的方式：
 更新表中的特定行；
 更新表中的所有行。
基本的 UPDATE 语句由三部分组成，分别是：
 要更新的表；
 列名和它们的新值；
 确定要更新哪些行的过滤条件。
例如客户 1000000005 现在有了电子邮件地址，因此他的记录需要更新，语句如下：

UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1000000005';

更新多个列的语法:

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

16.2 删除数据

从一个表中删除（去掉）数据，使用 DELETE 语句。有两种用DELETE
的方式：
 从表中删除特定的行；
 从表中删除所有行。
下面的语句从 Customers 表中删除一行：

DELETE FROM Customers
WHERE cust_id = '1000000006';

DELETE 不需要列名或通配符。 DELETE 删除整行而不是删除列。要删除指定的列，请使用 UPDATE 语句。

16.3 更新和删除的指导原则

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

17 创建和操纵表

17.1 创建表

SQL 不仅用于表数据操纵，还用来执行数据库和表的所有操作，包括表
本身的创建和处理。
一般有两种创建表的方法：
 多数 DBMS都具有交互式创建和管理数据库表的工具；
 表也可以直接用 SQL语句操纵。

17.1.1 表创建基础

利用 CREATE TABLE 创建表，必须给出下列信息：
 新表的名字，在关键字 CREATE TABLE 之后给出；
 表列的名字和定义，用逗号分隔；
 有的 DBMS还要求指定表的位置。
下面的 SQL语句创建本书中所用的 Products 表：

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

对于 MySQL， varchar 必须替换为 text ；对于 DB2，必须从最后一列中去掉 NULL 。此即对不同的 DBMS，要编写不同的表创建脚本的原因。

17.1.2 使用NULL值

第 4课提到， NULL 值就是没有值或缺值。允许 NULL 值的列也允许在插入行时不给出该列的值。不允许 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 。这就会阻止插入没有值的列。下一个例子将创建混合了 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 。

17.1.3 指定默认值

SQL 允许指定默认值，在插入行时如果不给出值，DBMS 将自动采用默
认值。默认值在 CREATE TABLE 语句的列定义中用关键字 DEFAULT 指定。

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

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

17.2 更新表

更新表定义，可以使用 ALTER TABLE 语句。
使用 ALTER TABLE 更改表结构，必须给出下面的信息：
 在 ALTER TABLE 之后给出要更改的表名（该表必须存在，否则将出错）；
 列出要做哪些更改。

ALTER TABLE Vendors
ADD vend_phone CHAR(20);

这条语句给 Vendors 表增加一个名为 vend_phone 的列，其数据类型为 CHAR 。
更改或删除列、增加约束或增加键，这些操作也使用类似的语法:

ALTER TABLE Vendors
DROP COLUMN vend_phone;

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

17.3 删除表

删除表（删除整个表而不是其内容）非常简单，使用 DROP TABLE 语句
即可：

DROP TABLE CustCopy;

17.4 重命名表

DB2、MariaDB、MySQL、Oracle和 PostgreSQL用户使用 RENAME
语句，SQL Server用户使用 sp_rename 存储过程，SQLite用户使用 ALTER TABLE 语句。所有重命名操作的基本语法都要求指定旧表名和新表名。

18 使用视图

介绍什么是视图，它们怎样工作，何时使用它们；还将讲述如何利用视图简化前几课中执行的某些 SQL操作。

18.1 视图

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

18.2 创建视图

视图用 CREATE VIEW 语句来创建。与 CREATE TABLE 一样，CREATE VIEW只能用于创建不存在的视图。
说明：视图重命名
删除视图，可以使用 DROP 语句，其语法为 DROP VIEW viewname; 。
覆盖（或更新）视图，必须先删除它，然后再重新创建。

18.2.1 利用视图简化复杂的联结

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;

这条语句创建一个名为 ProductCustomers 的视图，它联结三个表，返
回已订购了任意产品的所有顾客的列表。如果执行 SELECT * FROM
ProductCustomers ，将列出订购了任意产品的顾客。
检索订购了产品 RGAN01 的顾客，可如下进行：

SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

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

视图的另一常见用途是重新格式化检索出的数据。下面的SELECT 语句（来自第 7课）在单个组合计算列中返回供应商名和位置：

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 语句相同的查询创建视图。要检索数据，创建所有的邮件标签，可如下进行：

SELECT *
FROM VendorLocations;

18.2.3 用视图过滤不想要的数据

视图对于应用普通的 WHERE 子句也很有用。例如，可以定义CustomerEMailList 视图，过滤没有电子邮件地址的顾客。为此，可使用下面的语句：

CREATE VIEW CustomerEMailList AS
SELECT cust_id, cust_name, cust_email
FROM Customers
WHERE cust_email IS NOT NULL;

现在，可以像使用其他表一样使用视图 CustomerEMailList 。

SELECT *
FROM CustomerEMailList;

从视图检索数据时如果使用了一条 WHERE 子句，则两组子句（一组在视图中，另一组是传递给视图的）将自动组合。

18.2.4 使用视图与计算字段

在简化计算字段的使用上，视图也特别有用。下面是第 7 课中介绍的一
条 SELECT 语句，它检索某个订单中的物品，计算每种物品的总价格：

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;

检索订单 20008 的详细内容（上面的输出），如下进行：

SELECT *
FROM OrderItemsExpanded
WHERE order_num = 20008;

19 使用存储过程

介绍什么是存储过程，为什么要使用存储过程，如何使用存储过程，以及创建和使用存储过程的基本语法。

19.1 存储过程

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

19.2 执行存储过程

存储过程的执行远比编写要频繁得多，因此先介绍存储过程的执行。执行存储过程的 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' );

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

注意到在 Products 表中还有另一个需要值的列 prod_id 列，它是这个表的主键。为什么这个值不作为属性传递给存储过程？要保证恰当地生成此 ID，最好是使生成此 ID 的过程自动化（而不是依赖于最终用户的输入）。这也是这个例子使用存储过程的原因。以下是存储过程所完成的工作：
 验证传递的数据，保证所有 4个参数都有值；
 生成用作主键的唯一 ID；
 将新产品插入 Products 表，在合适的列中存储生成的主键和传递的
数据。

19.3 创建存储过程

对邮件发送清单中具有邮件地址的顾客进行计数。下面是该过程的 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（要传递的输出参数）。
调用 Oracle例子可以像下面这样：

var ReturnValue NUMBER
EXEC MailingListCount(:ReturnValue);
SELECT ReturnValue;

这段代码声明了一个变量来保存存储过程返回的任何值，然后执行存储过程，再使用 SELECT 语句显示返回的值。
下面是该过程的 SQL Server 版本。

CREATE PROCEDURE MailingListCount
AS
DECLARE @cnt INTEGER
SELECT @cnt = COUNT(*)
FROM Customers
WHERE NOT cust_email IS NULL;
RETURN @cnt;

此存储过程没有参数。调用程序检索 SQL Server的返回代码提供的值。其中用 DECLARE 语句声明了一个名为 @cnt 的局部变量（SQL Server中所有局部变量名都以 @ 起头）；然后在 SELECT 语句中使用这个变量，让它包含 COUNT() 函数返回的值；最后，用 RETURN @cnt 语句将计数返回给调用程序。
调用 SQL Server例子可以像下面这样：

DECLARE @ReturnValue INT
EXECUTE @ReturnValue=MailingListCount;
SELECT @ReturnValue;

这段代码声明了一个变量来保存存储过程返回的任何值，然后执行存储过程，再使用 SELECT 语句显示返回的值。
下面是另一个例子，这次在 Orders 表中插入一个新订单。此程序仅适用于 SQL Server，但它说明了存储过程的某些用途和技术：

CREATE PROCEDURE NewOrder @cust_id CHAR(10)
AS
-- Declare variable for order number
DECLARE @order_num INTEGER
-- Get current highest order number
SELECT @order_num=MAX(order_num)
FROM Orders
-- Determine next order number
SELECT @order_num=@order_num+1
-- Insert new order
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(@order_num, GETDATE(), @cust_id)
-- Return order number
RETURN @order_num;

此存储过程在 Orders 表中创建一个新订单。它只有一个参数，即下订单顾客的 ID。订单号和订单日期这两列在存储过程中自动生成。代码首先声明一个局部变量来存储订单号。接着，检索当前最大订单号（使用MAX() 函数）并增加 1（使用 SELECT 语句）。然后用 INSERT 语句插入由新生成的订单号、当前系统日期（用 GETDATE() 函数检索）和传递的顾客 ID组成的订单。最后，用 RETURN @order_num 返回订单（处理订单物品需要它）。请注意，此代码加了注释，在编写存储过程时应该多加注释。
下面是相同 SQL Server代码的一个很不同的版本：

CREATE PROCEDURE NewOrder @cust_id CHAR(10)
AS
-- Insert new order
INSERT INTO Orders(cust_id)
VALUES(@cust_id)
-- Return order number
SELECT order_num = @@IDENTITY;

此存储过程也在 Orders 表中创建一个新订单。这次由 DBMS生成订单号。大多数 DBMS都支持这种功能；SQL Server中称这些自动增量的列为标识字段（identity field），而其他 DBMS称之为自动编号（auto number）或序列（sequence）。传递给此过程的参数也是一个，即下订单的顾客 ID。订单号和订单日期没有给出，DBMS对日期使用默认值（ GETDATE() 函数），订单号自动生成。怎样才能得到这个自动生成的ID？在 SQL Server上可在全局变量 @@IDENTITY 中得到，它返回到调用程序（这里使用 SELECT 语句）。

20 管理事务处理

介绍什么是事务处理，如何利用 COMMIT 和 ROLLBACK 语句管理事务处理。

20.1 事务处理

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

前面使用的 Orders 表就是一个很好的例子。订单存储在 Orders 和OrderItems 两个表中： Orders 存储实际的订单， OrderItems 存储订购的各项物品。这两个表使用称为主键（参阅第 1课）的唯一ID互相关联，又与包含客户和产品信息的其他表相关联。给系统添加订单的过程如下：
(1) 检查数据库中是否存在相应的顾客，如果不存在，添加他；
(2) 提交顾客信息；
(3) 检索顾客的 ID；
(4) 在 Orders 表中添加一行；
(5) 如果向 Orders 表添加行时出现故障，回退；
(6) 检索 Orders 表中赋予的新订单 ID；
(7) 对于订购的每项物品，添加新行到 OrderItems 表；
(8) 如果向 OrderItems 添加行时出现故障，回退所有添加的 OrderItems
行和 Orders 行。

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

20.2 控制事务处理

管理事务的关键在于将 SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。有的DBMS要求明确标识事务处理块的开始和结束。如在SQL Server中，标识如下：

BEGIN TRANSACTION
...
COMMIT TRANSACTION

在这个例子中， BEGIN TRANSACTION 和 COMMIT TRANSACTION语句之间的 SQL必须完全执行或者完全不执行。
MariaDB和 MySQL中等同的代码为：

START TRANSACTION
...

Oracle使用的语法：

SET TRANSACTION
...

20.2.1 使用 ROLLBACK

SQL的 ROLLBACK 命令用来回退（撤销）SQL语句，请看下面的语句：

DELETE FROM Orders;
ROLLBACK;

20.2.2 使用 COMMIT

一般的 SQL语句都是针对数据库表直接执行和编写的。这就是所谓的隐式提交（implicit commit），即提交（写或保存）操作是自动进行的。
进行明确的提交，使用 COMMIT 语句。下面是一个 SQL Server的例子：

BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT TRANSACTION

在这个 SQL Server例子中，从系统中完全删除订单 12345 。因为涉及更新两个数据库表 Orders 和 OrderItems ，所以使用事务处理块来保证订单不被部分删除。最后的 COMMIT 语句仅在不出错时写出更改。如果第一条 DELETE 起作用，但第二条失败，则 DELETE 不会提交。
为在 Oracle中完成相同的工作，可如下进行：

SET TRANSACTION
DELETE OrderItems WHERE order_num = 12345;
DELETE Orders WHERE order_num = 12345;
COMMIT;

20.2.3 使用保留点

使用简单的 ROLLBACK 和 COMMIT 语句，就可以写入或撤销整个事务。但是，只对简单的事务才能这样做，复杂的事务可能需要部分提交或回退。例如前面描述的添加订单的过程就是一个事务。如果发生错误，只需要返回到添加 Orders 行之前即可。不需要回退到 Customers 表（如果存在的话）。
要支持回退部分事务，必须在事务处理块中的合适位置放置占位符。这样，如果需要回退，可以回退到某个占位符。
在 SQL 中，这些占位符称为保留点。在 MariaDB、MySQL 和 Oracle 中
创建占位符，可使用 SAVEPOINT 语句。

SAVEPOINT delete1;

在 SQL Server 中，如下进行：

SAVE TRANSACTION delete1;

每个保留点都要取能够标识它的唯一名字，以便在回退时，DBMS 知道
回退到何处。要回退到本例给出的保留点，在 SQL Server 中可如下进行。

ROLLBACK TRANSACTION delete1;

在 MariaDB、MySQL和 Oracle中，如下进行：

ROLLBACK TO delete1;

下面是一个完整的 SQL Server例子：

BEGIN TRANSACTION
INSERT INTO Customers(cust_id, cust_name)
VALUES('1000000010', 'Toys Emporium');
SAVE TRANSACTION StartOrder;
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20100,'2001/12/1','1000000010');
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity,
➥item_price)
VALUES(20100, 1, 'BR01', 100, 5.49);
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity,
➥item_price)
VALUES(20100, 2, 'BR03', 100, 10.99);
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
COMMIT TRANSACTION

这里的事务处理块中包含了 4条 INSERT 语句。在第一条 INSERT 语句之后定义了一个保留点，因此，如果后面的任何一个 INSERT 操作失败，事务处理能够回退到这里。在 SQL Server 中，可检查一个名为 @@ERROR的变量，看操作是否成功。（其他 DBMS 使用不同的函数或变量返回此信息。）如果 @@ERROR 返回一个非 0 的值，表示有错误发生，事务处理回退到保留点。如果整个事务处理成功，发布COMMIT 以保留数据。

21 使用游标

21.1 游标

SQL检索操作返回一组称为结果集（SQL查询所检索出的结果。）的行，这组返回的行都是与 SQL语句相匹配的行（零行或多行）。简单地使用 SELECT 语句，没有办法得到第一行、下一行或前 10行。但这是关系 DBMS功能的组成部分。

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

21.2 使用游标

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

21.2.1 创建游标

使用 DECLARE 语句创建游标，这条语句在不同的 DBMS 中有所不同。
DECLARE 命名游标，并定义相应的 SELECT 语句，根据需要带WHERE 和其他子句。为说明，创建一个游标来检索没有电子邮件地址的所有顾客，作为应用程序的组成部分，帮助操作人员找出空缺的电子邮件地址。
下面是创建此游标的 DB2、MariaDB、MySQL和 SQL Server版本。

DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL

DECLARE 语句用来定义和命名游标，这里为CustCursor 。 SELECT 语句定义一个包含没有电子邮件地址（ NULL 值）的所有顾客的游标。
定义游标之后，就可以打开它了。

21.2.2 使用游标

使用 OPEN CURSOR 语句打开游标，这条语句很简单，在大多数DBMS
中的语法相同：

OPEN CURSOR CustCursor

在处理 OPEN CURSOR 语句时，执行查询，存储检索出的数据以供浏览和滚动。
现在可以用 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;

在这个例子中， FETCH 用来检索当前行（自动从第一行开始），放到声明的变量 CustRecord 中。对于检索出来的数据不做任何处理。
下一个例子（也使用 Oracle 语法）中，从第一行到最后一行，对检索出
来的数据进行循环：

DECLARE TYPE CustCursor IS REF CURSOR
       RETURN Customers%ROWTYPE;
DECLARE CustRecord Customers%ROWTYPE
BEGIN
       OPEN CustCursor;
       LOOP
       FETCH CustCursor INTO CustRecord;
       EXIT WHEN CustCursor%NOTFOUND;
       ...
       END LOOP;
       CLOSE CustCursor;
END;

与前一个例子一样，这个例子使用 FETCH 检索当前行，放到一个名为
CustRecord 的变量中。但不一样的是，这里的 FETCH 位于 LOOP 内，因此它反复执行。代码 EXIT WHEN CustCursor%NOTFOUND 使在取不出更多的行时终止处理（退出循环）。这个例子也没有做实际的处理，实际例子中可用具体的处理代码替换占位符 ... 。
下面是另一个例子，这次使用 Microsoft SQL Server语法：

DECLARE @cust_id CHAR(10),
@cust_name CHAR(50),
@cust_address CHAR(50),
@cust_city CHAR(50),
@cust_state CHAR(5),
@cust_zip CHAR(10),
@cust_country CHAR(50),
@cust_contact CHAR(50),
@cust_email CHAR(255)
OPEN CustCursor
FETCH NEXT FROM CustCursor
INTO @cust_id, @cust_name, @cust_address,
@cust_city, @cust_state, @cust_zip,
@cust_country, @cust_contact, @cust_email
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM CustCursor
INTO @cust_id, @cust_name, @cust_address,
@cust_city, @cust_state, @cust_zip,
@cust_country, @cust_contact, @cust_email
END
CLOSE CustCursor

在此例中，为每个检索出的列声明一个变量， FETCH 语句检索一行并保
存值到这些变量中。使用 WHILE 循环处理每一行，条件 WHILE
@@FETCH_STATUS = 0 在取不出更多的行时终止处理（退出循环）。这个例子也不进行具体的处理，实际代码中，应该用具体的处理代码替换其中的 ... 占位符。

21.2.3 关闭游标

游标在使用完毕时需要关闭。此外，SQL Server等 DBMS 要求明确释放游标所占用的资源。下面是 DB2、Oracle 和PostgreSQL的语法。
CLOSE CustCursor
下面是 Microsoft SQL Server 的版本。

CLOSE CustCursor
DEALLOCATE CURSOR CustCursor

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

22 高级 SQL 特性

介绍 SQL所涉及的几个高级数据处理特性：约束、索引和触发器。

22.1 约束

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

22.1.1 主键

主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且永不改动。换句话说，表中的一列（或多个列）的值唯一标识表中的每一行。这方便了直接或交互地处理表中的行。没有主键，要安全地 UPDATE 或 DELETE 特定行而不影响其他行会非常困难。
表中任意列只要满足以下条件，都可以用于主键。
 任意两行的主键值都不相同。
 每行都具有一个主键值（即列中不允许 NULL 值）。
 包含主键值的列从不修改或更新。（大多数 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
);

在此例子中，给表的 vend_id 列定义添加关键字 PRIMARY KEY ，使其成为主键。

ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id);

这里定义相同的列为主键，但使用的是 CONSTRAINT 语法。此语法可以用于 CREATE TABLE 和 ALTER TABLE 语句。

22.1.2 外键

外键是表中的一列，其值必须列在另一表的主键中。外键是保证引用完
整性的极其重要部分。举个例子来理解外键。
Orders 表将录入到系统的每个订单作为一行包含其中。顾客信息存储在
Customers 表中。 Orders 表中的订单通过顾客 ID与 Customers 表中的
特定行相关联。顾客 ID 为 Customers 表的主键，每个顾客都有唯一的
ID。订单号为 Orders 表的主键，每个订单都有唯一的订单号。
Orders 表中顾客 ID列的值不一定是唯一的。如果某个顾客有多个订单，
则有多个行具有相同的顾客 ID（虽然每个订单都有不同的订单号）。同
时， Orders 表中顾客 ID列的合法值为 Customers 表中顾客的 ID。
这就是外键的作用。在这个例子中，在 Orders 的顾客 ID列上定义了一
个外键，因此该列只能接受 Customers 表的主键值。
下面是定义这个外键的方法。

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 语句中用 CONSTRAINT 语法来完成：

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

22.1.3 唯一约束

唯一约束用来保证一列（或一组列）中的数据是唯一的。它们类似于主
键，但存在以下重要区别。
 表可包含多个唯一约束，但每个表只允许一个主键。
 唯一约束列可包含 NULL 值。
 唯一约束列可修改或更新。
 唯一约束列的值可重复使用。
 与主键不一样，唯一约束不能用来定义外键。

22.1.4 检查约束

检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。检
查约束的常见用途有以下几点。
 检查最小或最大值。例如防止 0个物品的订单（即使 0是合法数）。
 指定范围。例如保证发货日期大于等于今天的日期，但不超过今天起一年后的日期。
 只允许特定的值。例如，在性别字段中只允许 M 或 F。
下面的例子对 OrderItems 表施加了检查约束，它保证所有物品的数量大于 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
);

利用这个约束，任何插入（或更新）的行都会被检查，保证 quantity大于 0。
检查名为 gender 的列只包含 M 或 F ，编写如下 ALTER TABLE 语句：

ADD CONSTRAINT CHECK (gender LIKE '[MF]')

22.2 索引

索引用来排序数据以加快搜索和排序操作的速度。索引用 CREATE INDEX 语句创建（不同DBMS创建索引的语句变化很大）。
下面的语句在 Products 表的产品名列上创建一个简单的索引。

CREATE INDEX prod_name_ind
ON Products (prod_name);

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

22.3 触发器

触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。触发器可以与特定表上的 INSERT 、 UPDATE 和 DELETE 操作（或组合）相关联。
触发器内的代码具有以下数据的访问权：
 INSERT 操作中的所有新数据；
 UPDATE 操作中的所有新数据和旧数据；
 DELETE 操作中删除的数据。
下面的例子创建一个触发器，它对所有 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;

22.4 数据库安全

一般说来，需要保护的操作有：
 对数据库管理功能（创建表、更改或删除已存在的表等）的访问；
 对特定数据库或表的访问；
 访问的类型（只读、对特定列的访问等）；
 仅通过视图或存储过程对表进行访问；
 创建多层次的安全措施，从而允许多种基于登录的访问和控制；
 限制管理用户账号的能力。
安全性使用 SQL的 GRANT 和 REVOKE 语句来管理，不过，大多数 DBMS提供了交互式的管理实用程序，这些实用程序在内部使用 GRANT 和REVOKE 语句。