SQL必知必会（第4版）读书笔记

第1课 了解SQL

1.1 数据库基础

1.1.1 数据库

数据库是一个以某种有组织的方式存储的数据集合。

数据库（database）
保存有组织的数据的容器（通常是一个文件或一组文件）。

注意：误用导致混淆
数据库软件应称为数据库管理系统（即DBMS）。数据库是通过DBMS创建和操纵的容器，而具体它究竟是什么，形式如何，各种数据库都不一样。

1.1.2 表

表（table）
某种特定类型数据的结构化清单。

数据库中的每个表都有一个名字来标识自己。这个名字是唯一的，即数据库中没有其他表具有相同的名字。

说明：表名
使表名成为唯一的，实际上是数据库名和表名等的组合。有的数据库还使用数据库拥有者的名字作为唯一名的一部分。也就是说，虽然在相同数据库中不能两次使用相同的表名，但在不同的数据库中完全可以使用相同的表名。

表具有一些特性，这些特性定义了数据在表中如何存储，包含存储什么样的数据，数据如何分解，各部分信息如何命名等信息。描述表的这组信息就是所谓的模式（schema），模式可以用来描述数据库中特定的表，也可以用来描述整个数据库（和其中表的关系）。

模式
关于数据库和表的布局及特性的信息。

1.1.3 列和数据类型

列（column）
表中的一个字段。所有表都是由一个或多个列组成的。

提示：分解数据
正确地将数据分解为多个列极为重要。例如，城市、州、邮政编码应该总是彼此独立的列。通过分解这些数据，才有可能利用特定的列对数据进行分类和过滤（如找出特定州或特定城市的所有顾客）。如果城市和州组合在一个列中，则按州进行分类或过滤就会很困难。

数据库中每个列都有相应的数据类型。数据类型（datatype）定义了列可以存储哪些数据种类。

数据类型
所允许的数据的类型。每个表列都有相应的数据类型，它限制（或允许）该列中存储的数据。

1.1.4 行

表中的数据是按行存储的，所保存的每个记录存储在自己的行内。如果将表想象为网格，网格中垂直的列为表列，水平行为表行。

行（row）
表中的一个记录。

1.1.5 主键

主键（primary key）
一列（或一组列），其值能够唯一标识表中每一行。

提示：应该总是定义主键
虽然并不总是需要主键，但多数数据库设计者都会保证他们创建的每个表具有一个主键，以便于以后的数据操作和管理。

表中的任何列都可以作为主键，只要它满足以下条件：

• 任意两行都不具有相同的主键值；
• 每一行都必须具有一个主键值（主键列不允许NULL值）；
• 主键列中的值不允许修改或更新；
• 主键值不能重用（如果某行从表中删除，它的主键不能赋给以后的新行）。

主键通常定义在表的一列上，但并不是必需这么做，也可以一起使用多个列作为主键。在使用多列作为主键时，上述条件必须应用到所有列，所有列值的组合必须是唯一的（但单个列的值可以不唯一）。

1.2 什么是SQL

SQL（发音为字母S-Q-L或sequel）是结构化查询语言（Structured Query Language）的缩写。

SQL有如下的优点。

• SQL不是某个特定数据库供应商专有的语言。几乎所有重要的DBMS都支持SQL，所以学习此语言使你几乎能与所有数据库打交道。
• SQL简单易学。它的语句全都是由有很强描述性的英语单词组成，而且这些单词的数目不多。
• SQL虽然看上去很简单，但实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。

第2课 检索数据

2.1 SELECT语句

SQL语句是由简单的英语单词构成的。这些单词称为关键字，每个SQL语句都是由一个或多个关键字构成的。

SELECT语句的用途是从一个或多个表中检索信息。

关键字（keyword）
作为SQL组成部分的保留字。关键字不能用作表或列的名字。

为了使用SELECT检索表数据，必须至少给出两条信息——想选择什么，以及从什么地方选择。

2.2 检索单个列

SELECT prod_name
FROM Products;

提示：结束SQL语句
多条SQL语句必须以分号（；）分隔。

提示：SQL语句和大小写
请注意，SQL语句不区分大小写，因此SELECT与select是相同的。

提示：使用空格
在处理SQL语句时，其中所有空格都被忽略。SQL语句可以写成长长的一行，也可以分写在多行。

2.3 检索多个列

要想从一个表中检索多个列，仍然使用相同的SELECT语句。唯一的不同是必须在SELECT关键字后给出多个列名，列名之间必须以逗号分隔。

提示：当心逗号
在选择多个列时，一定要在列名之间加上逗号，但最后一个列名后不加。如果在最后一个列名后加了逗号，将出现错误。

SELECT prod_id, prod_name, prod_price
FROM Products;

2.4 检索所有列

除了指定所需的列外，SELECT语句还可以检索所有的列而不必逐个列出它们。在实际列名的位置使用星号（*）通配符可以做到这点，如下所示。

SELECT *
FROM Products;

警告：使用通配符
一般而言，除非你确实需要表中的每一列，否则最好别使用*通配符。虽然使用通配符能让你自己省事，不用明确列出所需列，但检索不需要的列通常会降低检索和应用程序的性能。

2.5 检索不同的值

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

SELECT DISTINCT vend_id
FROM Products;

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

2.6 限制结果

SELECT语句返回指定表中所有匹配的行，很可能是每一行。如果你只想返回第一行或者一定数量的行，该怎么办呢？这是可行的，然而遗憾的是，各种数据库中的这一SQL实现并不相同。

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

# 使用SELECT TOP 5语句，只检索前5行数据。
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指定返回的行数。带OFFSET的LIMIT指定从哪儿开始。

警告：第0行
第一个被检索的行是第0行，而不是第1行。因此，LIMIT 1 OFFSET 1会检索第2行，而不是第1行。

提示：MySQL和MariaDB快捷键
MySQL和MariaDB支持简化版的LIMIT 4 OFFSET 3语句，即LIMIT 3,4。使用这个语法，,之前的值对应OFFSET， ,之后的值对应LIMIT。

2.7 适用注释

• 添加一些描述性的注释，这便于你自己今后参考，或者供项目后续参与人员参考
• 同样适用于SQL文件开始处的内容，它可能包含程序员的联系方式、程序描述以及一些说明。
• 注释的另一个重要应用是暂时停止要执行的SQL代码。如果你碰到一个长SQL语句，而只想测试它的一部分，那么应该注释掉一些代码，以便MariaDB将其视为注释而加以忽略。

SELECT prod_name    -- 这是一条行内注释 
FROM Products;

# 这是一条单行注释
SELECT prod_name 
FROM Products;

/* SELECT prod_name, vend_id 
FROM Products; */ 
SELECT prod_name 
FROM Products;

第3课 排序检索数据

3.1 排序数据

子句（clause）
SQL语句由子句构成，有些子句是必需的，有些则是可选的。一个子句通常由一个关键字加上所提供的数据组成。

为了明确地排序用SELECT语句检索出的数据，可使用ORDER BY子句。ORDER BY子句取一个或多个列的名字，据此对输出进行排序。

SELECT prod_name
FROM Products
ORDER BY prod_name; 

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

通过非选择列进行排序
通常，ORDER BY子句中使用的列将是为显示而选择的列。但是，实际上并不一定要这样，用非检索的列排序数据是完全合法的。

3.2 按多个列排序

要按多个列排序，简单指定列名，列名之间用逗号分开即可（就像选择多个列时那样）。

下面的代码检索3个列，并按其中两个列对结果进行排序——首先按价格，然后按名称排序。

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

3.3 按列位置排序

除了能用列名指出排序顺序外，ORDER BY还支持按相对列位置进行排序。

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

ORDER BY 2表示按SELECT清单中的第二个列prod_name进行排序。ORDER BY 2，3表示先按prod_price，再按prod_name进行排序。

这一技术的主要好处在于不用重新输入列名。但它也有缺点。首先，不明确给出列名有可能造成错用列名排序。其次，在对SELECT清单进行更改时容易错误地对数据进行排序（忘记对ORDER BY子句做相应的改动）。最后，如果进行排序的列不在SELECT清单中，显然不能使用这项技术。

按非选择列排序
显然，当根据不出现在SELECT清单中的列进行排序时，不能采用这项技术。但是，如果有必要，可以混合匹配使用实际列名和相对列位置。

3.4 指定排序方向

数据排序不限于升序排序（从A到Z），这只是默认的排序顺序。还可以使用ORDER BY子句进行降序（从Z到A）排序。为了进行降序排序，必须指定DESC关键字。

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

警告：在多个列上降序排序
如果想在多个列上进行降序排序，必须对每一列指定DESC关键字。

请注意，DESC是DESCENDING的缩写，这两个关键字都可以使用。与DESC相对的是ASC（或ASCENDING），在升序排序时可以指定它。但实际上，ASC没有多大用处，因为升序是默认的（如果既不指定ASC也不指定DESC，则假定为ASC）。

提示：区分大小写和排序顺序
在对文本性数据进行排序时，A与a相同吗？a位于B之前，还是Z之后？这些问题不是理论问题，其答案取决于数据库的设置方式。

在字典（dictionary）排序顺序中，A被视为与a相同，这是大多数数据库管理系统的默认行为。但是，许多DBMS允许数据库管理员在需要时改变这种行为（如果你的数据库包含大量外语字符，可能必须这样做）。

这里的关键问题是，如果确实需要改变这种排序顺序，用简单的ORDER BY子句可能做不到。你必须请求数据库管理员的帮助。

第4课 过滤数据

4.1 使用WHERE子句

在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。WHERE子句在表名（FROM子句）之后给出，如下所示：

SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49; 

提示：SQL过滤与应用过滤
数据也可以在应用层过滤。为此，SQL的SELECT语句为客户端应用检索出超过实际所需的数据，然后客户端代码对返回数据进行循环，提取出需要的行。

通常，这种做法极其不妥。优化数据库后可以更快速有效地对数据进行过滤。而让客户端应用（或开发语言）处理数据库的工作将会极大地影响应用的性能，并且使所创建的应用完全不具备可伸缩性。此外，如果在客户端过滤数据，服务器不得不通过网络发送多余的数据，这将导致网络带宽的浪费。

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

4.2 WHERE字句操作符

我们在做相等检验时看到了第一个WHERE子句，它确定一个列是否包含指定的值。

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
!	不小于
>	大于
>=	大于等于
!>	不大于
BETWEEN	在指定两个值之间
IS NULL	为NULL值

警告： 操作符兼容
表4-1中列出的某些操作符是冗余的（如< >与!=相同，!<相当于>=）。并非所有DBMS都支持这些操作符

4.2.1 检查单个值

第一个例子是列出所有价格小于10美元的产品：

SELECT prod_name, prod_price
FROM Products
WHERE prod_price < 10; 

4.2.2 不匹配检查

列出所有不是供应商DLL01制造的产品：

SELECT vend_id, prod_name
FROM Products
WHERE vend_id <> 'DLL01';

提示：何时使用引号
如果仔细观察上述WHERE子句中的条件，会看到有的值括在单引号内，而有的值未括起来。单引号用来限定字符串。如果将值与字符串类型的列进行比较，就需要限定引号。用来与数值列进行比较的值不用引号。

警告：是!=还是<>？
!=和<>通常可以互换。但是，并非所有DBMS都支持这两种不等于操作符。例如，Microsoft Access支持<>而不支持!=。

4.2.3 范围值检查

要检查某个范围的值，可以使用BETWEEN操作符。其语法与其他WHERE子句的操作符稍有不同，因为它需要两个值，即范围的开始值和结束值。

下面的例子说明如何使用BETWEEN操作符，它检索价格在5美元和10美元之间的所有产品：

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

在使用BETWEEN时，必须指定两个值——所需范围的低端值和高端值。这两个值必须用AND关键字分隔。BETWEEN匹配范围中所有的值，包括指定的开始值和结束值。

4.2.4 空值检查

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

NULL
无值（no value），它与字段包含0、空字符串或仅仅包含空格不同。

确定值是否为NULL，不能简单地检查是否= NULL。SELECT语句有一个特殊的WHERE子句，可用来检查具有NULL值的列。这个WHERE子句就是IS NULL子句。其语法如下：

SELECT prod_name
FROM Products
WHERE prod_price IS NULL;

警告：NULL和非匹配
通过过滤选择不包含指定值的所有行时，你可能希望返回含NULL值的行。但是这做不到。因为未知（unknown）有特殊的含义，数据库不知道它们是否匹配，所以在进行匹配过滤或非匹配过滤时，不会返回这些结果。

过滤数据时，一定要验证被过滤列中含NULL的行确实出现在返回的数据中。

第5课 高级数据过滤

5.1 组合WHERE子句

为了进行更强的过滤控制，SQL允许给出多个WHERE子句。这些子句有两种使用方式，即以AND子句或OR子句的方式使用。

操作符（operator）
用来联结或改变WHERE子句中的子句的关键字，也称为逻辑操作符（logical operator）。

5.1.1 AND操作符

要通过不止一个列进行过滤，可以使用AND操作符给WHERE子句附加条件。下面的代码给出了一个例子：

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

AND
用在WHERE子句中的关键字，用来指示检索满足所有给定条件的行。

这个例子只包含一个AND子句，因此最多有两个过滤条件。可以增加多个过滤条件，每个条件间都要使用AND关键字。

5.1.2 OR操作符

OR操作符与AND操作符正好相反，它指示DBMS检索匹配任一条件的行。事实上，许多DBMS在OR WHERE子句的第一个条件得到满足的情况下，就不再计算第二个条件了（在第一个条件满足时，不管第二个条件是否满足，相应的行都将被检索出来）。

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

OR
WHERE子句中使用的关键字，用来表示检索匹配任一给定条件的行。

5.1.3 求值顺序

WHERE子句可以包含任意数目的AND和OR操作符。允许两者结合以进行复杂、高级的过滤。

SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符。

此问题的解决方法是使用圆括号对操作符进行明确分组。请看下面的SELECT语句及输出：

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

提示：在WHERE子句中使用圆括号
任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认求值顺序，即使它确实如你希望的那样。使用圆括号没有什么坏处，它能消除歧义。

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子句。

IN
WHERE子句中用来指定要匹配值的清单的关键字，功能与OR相当。

5.3 NOT操作符

WHERE子句中的NOT操作符有且只有一个功能，那就是否定其后所跟的任何条件。因为NOT从不单独使用（它总是与其他操作符一起使用），所以它的语法与其他操作符有所不同。NOT关键字可以用在要过滤的列前，而不仅是在其后。

NOT
WHERE子句中用来否定其后条件的关键字。

为了列出除DLL01之外的所有供应商制造的产品，可编写如下的代码：

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

为什么使用NOT？对于这里的这种简单的WHERE子句，使用NOT确实没有什么优势。但在更复杂的子句中，NOT是非常有用的。例如，在与IN操作符联合使用时，NOT可以非常简单地找出与条件列表不匹配的行。

说明：MariaDB中的NOT
MariaDB支持使用NOT否定IN、BETWEEN和EXISTS子句。大多数DBMS允许使用NOT否定任何条件。

第6课 用通配符进行过滤

6.1 LIKE操作符

通配符（wildcard）
用来匹配值的一部分的特殊字符。

搜索模式（search pattern）
由字面值、通配符或两者组合构成的搜索条件。

通配符本身实际上是SQL的WHERE子句中有特殊含义的字符，SQL支持几种通配符。为在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示DBMS，后跟的搜索模式利用通配符匹配而不是简单的相等匹配进行比较。

通配符搜索只能用于文本字段（串），非文本数据类型字段不能使用通配符搜索。

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

6.1.2 下划线（_）通配符

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

说明：DB2通配符
DB2不支持通配符_。

说明：Access通配符
如果使用的是Microsoft Access，需要使用?而不是_。

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

6.1.3 方括号（[]）通配符

方括号（[]）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。

说明：并不总是支持集合
与前面描述的通配符不一样，并不是所有DBMS都支持用来创建集合的[]。只有微软的Access和SQL Server支持集合。为确定你使用的DBMS是否支持集合

此通配符可以用前缀字符^（脱字号）来否定。例如，下面的查询匹配不以J或M起头的任意联系人名：

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

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

当然，也可以使用NOT操作符得出相同的结果。^的唯一优点是在使用多个WHERE子句时可以简化语法：

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

6.2 使用通配符的技巧

• 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
• 在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。
• 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

第7课 创建计算字段

7.1 计算字段

计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的。

字段（field）
基本上与列（column）的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常与计算字段一起使用。

需要特别注意，只有数据库知道SELECT语句中哪些列是实际的表列，哪些列是计算字段。从客户端（如应用程序）来看，计算字段的数据与其他列的数据的返回方式相同。

提示：客户端与服务器的格式
在SQL语句内可完成的许多转换和格式化工作都可以直接在客户端应用程序内完成。但一般来说，在数据库服务器上完成这些操作比在客户端中完成要快得多。

7.2 拼接字段

拼接（concatenate）
将值联结到一起（将一个值附加到另一个值）构成单个值。

在SQL中的SELECT语句中，可使用一个特殊的操作符来拼接两个列。根据你所使用的DBMS，此操作符可用加号（+）或两个竖杠（||）表示。在MySQL和MariaDB中，必须使用特殊的函数。

说明：是+还是||？
Access和SQL Server使用+号。DB2、Oracle、PostgreSQL、SQLite和Open Office Base使用||。

下面是使用加号的例子（多数DBMS使用这种语法）：

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

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

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

RTRIM()函数去掉值右边的所有空格。通过使用RTRIM()，各个列都进行了整理。

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

使用别名

SQL支持列别名。别名（alias）是一个字段或值的替换名。别名用AS关键字赋予。请看下面的SELECT语句：

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

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

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

下面是MySQL和MariaDB中使用的语句：

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

说明：AS通常可选
在很多DBMS中，AS关键字是可选的，不过最好使用它，这被视为一条最佳实践。

提示：别名的其他用途
别名还有其他用途。常见的用途包括在实际的表列名包含不合法的字符（如空格）时重新命名它，在原来的名字含混或容易误解时扩充它。

警告：别名
别名既可以是一个单词也可以是一个字符串。如果是后者，字符串应该括在引号中。虽然这种做法是合法的，但不建议这么去做。多单词的名字可读性高，不过会给客户端应用带来各种问题。因此，别名最常见的使用是将多个单词的列名重命名为一个单词的名字。

说明：导出列
别名有时也称为导出列（derived column），不管怎么叫，它们所代表的是相同的东西。

7.3 执行算术计算

计算字段的另一常见用途是对检索出的数据进行算术计算。

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

输出中显示的expanded_price列是一个计算字段，此计算为quantity*item_price。客户端应用现在可以使用这个新计算列，就像使用其他列一样。

基本算术操作符：+（加）、-（减）、*（乘）、/（除）。此外，圆括号可用来区分优先顺序。

提示：如何测试计算
SELECT语句为测试、检验函数和计算提供了很好的方法。虽然SELECT通常用于从表中检索数据，但是省略了FROM子句后就是简单地访问和处理表达式，例如SELECT 3 * 2;将返回6，SELECT Trim(' abc ');将返回abc，SELECT Now();使用Now()函数返回当前日期和时间。现在你明白了，可以根据需要使用SELECT语句进行检验。

第8课 使用数据处理函数

8.1 函数

与大多数其他计算机语言一样，SQL也可以用函数来处理数据。函数一般是在数据上执行的，为数据的转换和处理提供了方便。

函数带来的问题

与几乎所有DBMS都等同地支持SQL语句（如SELECT）不同，每一个DBMS都有特定的函数。事实上，只有少数几个函数被所有主要的DBMS等同地支持。虽然所有类型的函数一般都可以在每个DBMS中使用，但各个函数的名称和语法可能极其不同。

函数	语法
提取字符串的组成部分	Access使用MID()； DB2、Oracle、PostgreSQL和SQLite使用SUBSTR(); MySQL和SQL Server使用SUBSTRING()
数据类型转换	Access和Oracle使用多个函数，每种类型的转换有一个函数； DB2和PostgreSQL使用CAST()； MariaDB、MySQL和SQL Server使用CONVERT()
取当前时间	Access使用NOW()； DB2和PostgreSQL使用CURRENT_DATE； MariaDB和MySQL使用CURDATE()； Oracle使用SYSDATE； SQL Server使用GETDATE()； SQLite使用DATE()

与SQL语句不一样，SQL函数不是可移植的。这表示为特定SQL实现编写的代码在其他实现中可能不正常。

可移植（portable）
所编写的代码可以在多个系统上运行。

提示：是否应该使用函数？
现在，你面临是否应该使用函数的选择。决定权在你，使用或是不使用也没有对错之分。如果你决定使用函数，应该保证做好代码注释，以便以后你（或其他人）能确切地知道所编写的SQL代码的含义。

8.2 使用函数

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

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

8.2.1 文本处理函数

RTRIM()函数来去除列值右边的空格。

UPPER()将文本转换为大写

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM Vendors
ORDER BY vend_name;

函数	说明
LEFT()（或使用子字符串函数）	返回字符串左边的字符
LENGTH()（也使用DATALENGTH()或LEN()）	返回字符串的长度
LOWER()（Access使用LCASE()）	将字符串转换为小写
LTRIM()	去掉字符串左边的空格
RIGHT()（或使用子字符串函数）	返回字符串右边的字符
RTRIM()	去掉字符串右边的空格
SOUNDEX()	返回字符串的SOUNDEX值
UPPER()（Access使用UCASE()）	将字符串转换为大写

SOUNDEX需要做进一步的解释。SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对字符串进行发音比较而不是字母比较。虽然SOUNDEX不是SQL概念，但多数DBMS都提供对SOUNDEX的支持。

说明：SOUNDEX支持
Microsoft Access和PostgreSQL不支持SOUNDEX()，因此以下的例子不适用于这些DBMS。

另外，如果在创建SQLite时使用了SQLITE_SOUNDEX编译时选项，那么SOUNDEX()在SQLite中就可用。因为SQLITE_SOUNDEX不是默认的编译时选项，所以多数SQLite实现不支持SOUNDEX()。

下面给出一个使用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'); 

在这个例子中，WHERE子句使用SOUNDEX()函数把cust_contact列值和搜索字符串转换为它们的SOUNDEX值。因为Michael Green和Michelle Green发音相似，所以它们的SOUNDEX值匹配，因此WHERE子句正确地过滤出了所需的数据。

8.2.2 日期和时间处理函数

日期和时间采用相应的数据类型存储在表中，每种DBMS都有自己的特殊形式。日期和时间值以特殊的格式存储，以便能快速和有效地排序或过滤，并且节省物理存储空间。

举个简单的例子，来说明日期处理函数的用法。

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;

下面是使用名为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; 

完成相同工作的另一方法是使用BETWEEN操作符：

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

MySQL和MariaDB具有各种日期处理函数，但没有DATEPART()。MySQL和MariaDB用户可使用名为YEAR()的函数从日期中提取年份：

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

在SQLite中有个小技巧：

SELECT order_num
FROM Orders
WHERE strftime('%Y', order_date) = 2012;

这里给出的例子提取和使用日期的成分（年）。按月份过滤，可以进行相同的处理，指定AND操作符以及年和月份的比较。

大多数DBMS具有比较日期、执行基于日期的运算、选择日期格式等的函数。但是，可以看到，不同DBMS的日期-时间处理函数可能不同。

8.2.3 数值处理函数

数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算，因此不像字符串或日期-时间处理函数使用那么频繁。

函数	说明
ABS()	返回一个数的绝对值
COS()	返回一个角度的余弦
EXP()	返回一个数的指数值
PI()	返回圆周率
SIN()	返回一个角度的正弦
SQRT()	返回一个数的平方根
TAN()	返回一个角度的正切

第9课 汇总数据

9.1 聚集函数

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

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某类的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

9.1.1 AVG()函数

AVG()通过对表中行数计数并计算其列值之和，求得该列的平均值。AVG()可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。

下面的例子使用AVG()返回Products表中所有产品的平均价格：

SELECT AVG(prod_price) AS avg_price
FROM Products;

警告：只用于单个列
**AVG()**只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。

说明：NULL值
AVG()函数忽略列值为NULL的行。

9.1.2 COUNT()函数

COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目。

COUNT()函数有两种使用方式：

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

SELECT COUNT(*) AS num_cust
FROM Customers;

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

9.1.3 MAX()函数

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

SELECT MAX(prod_price) AS max_price
FROM Products;

提示：对非数值数据使用MAX()
虽然MAX()一般用来找出最大的数值或日期值，但许多（并非所有）DBMS允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，MAX()返回按该列排序后的最后一行。

说明：NULL值
MAX()函数忽略列值为NULL的行。

9.1.4 MIN()函数

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

SELECT MIN(prod_price) AS min_price
FROM Products;

提示：对非数值数据使用MIN()
虽然MIN()一般用来找出最小的数值或日期值，但许多（并非所有）DBMS允许将它用来返回任意列中的最小值，包括返回文本列中的最小值。在用于文本数据时，MIN()返回该列排序后最前面的行。

说明：NULL值
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;

提示：在多个列上进行计算
如本例所示，利用标准的算术操作符，所有聚集函数都可用来执行多个列上的计算。

说明：NULL值
SUM()函数忽略列值为NULL的行。

9.2 聚集不同值

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

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

提示：ALL为默认
ALL参数不需要指定，因为它是默认行为。如果不指定DISTINCT，则假定为ALL。

说明：不要在Access中使用
Microsoft Access在聚集函数中不支持DISTINCT，因此下面的例子不适合于Access。要在Access得到类似的结果，需要使用子查询把DISTINCT数据返回到外部SELECT COUNT(*)语句。

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products

警告：DISTINCT不能用于COUNT(*)
如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)。类似地，DISTINCT必须使用列名，不能用于计算或表达式。

提示：将DISTINCT用于MIN()和MAX()
虽然DISTINCT从技术上可用于MIN()和MAX()，但这样做实际上没有价值。一个列中的最小值和最大值不管是否只考虑不同值，结果都是相同的。

说明：其他聚集参数
除了这里介绍的DISTINCT和ALL参数，有的DBMS还支持其他参数，如支持对查询结果的子集进行计算的TOP和TOP PERCENT。为了解具体的DBMS支持哪些参数，请参阅相应的文档。

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;

警告：取别名
在指定别名以包含某个聚集函数的结果时，不应该使用表中实际的列名。虽然这样做也算合法，但许多SQL实现不支持，可能会产生模糊的错误消息。

第10课 分组数据

10.1 数据分组

使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算。

10.2 创建分组

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

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

因为使用了GROUP BY，就不必指定要计算和估值的每个组了。系统会自动完成。GROUP BY子句指示DBMS分组数据，然后对每个组而不是整个结果集进行聚集。

在使用GROUP BY子句前，需要知道一些重要的规定。

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

提示：ALL子句
Microsoft SQL Server等有些SQL实现在GROUP BY中支持可选的ALL子句。这个子句可用来返回所有分组，即使是没有匹配行的分组也返回（在此情况下，聚集将返回NULL）。

警告：通过相对位置指定列
有的SQL实现允许根据SELECT列表中的位置指定GROUP BY的列。例如，GROUP BY 2, 1可表示按选择的第二个列分组，然后再按第一个列分组。虽然这种速记语法很方便，但并非所有SQL实现都支持，并且使用它容易在编辑SQL语句时出错。

10.3 过滤分组

除了能用GROUP BY分组数据外，SQL还允许过滤分组，规定包括哪些分组，排除哪些分组。

WHERE过滤指定的是行而不是分组。事实上，WHERE没有分组的概念。

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

提示：HAVING支持所有WHERE操作符
在第4课和第5课中，我们学习了WHERE子句的条件（包括通配符条件和带多个操作符的子句）。学过的这些有关WHERE的所有技术和选项都适用于HAVING。它们的句法是相同的，只是关键字有差别。

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

说明：HAVING和WHERE的差别
这里有另一种理解方法，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

说明：使用HAVING和WHERE
HAVING与WHERE非常类似，如果不指定GROUP BY，则大多数DBMS会同等对待它们。不过，你自己要能区分这一点。使用HAVING时应该结合GROUP BY子句，而WHERE子句用于标准的行级过滤。

10.4 分组和排序

ORDER BY与GROUP BY之间的差别

ORDER BY	GROUP BY
对产生的输出排序	对行分组，但输出可能不是分组的顺序
任意列都可以使用（甚至非选择的列也可以使用）	只可能使用选择列或表达式列，而且必须使用每个选择列表达式
不一定需要	如果与聚集函数一起使用列（或表达式），则必须使用

提示：不要忘记ORDER BY
一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要仅依赖GROUP BY排序数据。

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

说明：Access的不兼容性
Microsoft Access不允许按别名排序，因此这个例子在Access中将失败。解决方法是用实际的计算或字段位置替换items（在ORDER BY子句中），即ORDER BY COUNT(*), order_num或ORDER BY 2, order_num。

10.5 SELECT子句顺序

SELECT子句及其顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否

第11课 使用子查询

11.1 子查询

简单查询：从单个数据库表中检索数据的单条语句。

查询（query）
任何SQL语句都是查询。但此术语一般指SELECT语句。

SQL还允许创建子查询（subquery），即嵌套在其他查询中的查询。

说明：MySQL支持
如果使用MySQL，应该知道对子查询的支持是从4.1版本引入的。MySQL的早期版本不支持子查询。

11.2 利用子查询进行过滤

订单存储在两个表中。每个订单包含订单编号、客户ID、订单日期，在Orders表中存储为一行。各订单的物品存储在相关的OrderItems表中。Orders表不存储顾客信息，只存储顾客ID。顾客的实际信息存储在Customers表中。

现在，假如需要列出订购物品RGAN01的所有顾客，应该怎样检索？下面列出具体的步骤。

1. 检索包含物品RGAN01的所有订单的编号。
2. 检索具有前一步骤列出的订单编号的所有顾客的ID。
3. 检索前一步骤返回的所有顾客ID的顾客信息。

第一条SELECT语句的含义很明确，它对prod_id为RGAN01的所有订单物品，检索其order_num列。输出列出了两个包含此物品的订单：

SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01';

现在，我们知道了哪个订单包含要检索的物品，下一步查询与订单20007和20008相关的顾客ID。利用第5课介绍的IN子句，编写如下的SELECT语句：

SELECT cust_id
FROM Orders
WHERE order_num IN (20007,20008);

现在，结合这两个查询，把第一个查询（返回订单号的那一个）变为子查询。请看下面的SELECT语句：

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
                    FROM OrderItems
                    WHERE prod_id = 'RGAN01');

现在得到了订购物品RGAN01的所有顾客的ID。下一步是检索这些顾客ID的顾客信息。检索两列的SQL语句为：

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN ('1000000004','1000000005');

可以把其中的WHERE子句转换为子查询，而不是硬编码这些顾客ID：

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

警告：只能是单列
作为子查询的SELECT语句只能查询单个列。企图检索多个列将返回错误。

警告：子查询和性能
这里给出的代码有效，并且获得了所需的结果。但是，使用子查询并不总是执行这类数据检索的最有效方法。

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

使用子查询的另一方法是创建计算字段。

假如需要显示Customers表中每个顾客的订单总数。订单与相应的顾客ID存储在Orders表中。

执行这个操作，要遵循下面的步骤：

1. 从Customers表中检索顾客列表；
2. 对于检索出的每个顾客，统计其在Orders表中的订单数目。

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

警告：完全限定列名
你已经看到了为什么要使用完全限定列名，没有具体指定就会返回错误结果，因为DBMS会误解你的意思。有时候，由于出现冲突列名而导致的歧义性，会引起DBMS抛出错误信息。例如，WHERE或ORDER BY子句指定的某个列名可能会出现在多个表中。好的做法是，如果在SELECT语句中操作多个表，就应使用完全限定列名来避免歧义。

第12课 联结表

12.1 联结

SQL最强大的功能之一就是能在数据查询的执行中联结（join）表。联结是利用SQL的SELECT能执行的最重要的操作

12.1.1 关系表

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

有一个包含产品目录的数据库表，其中每类物品占一行。对于每一种物品，要存储的信息包括产品描述、价格，以及生产该产品的供应商。

现在有同一供应商生产的多种物品，那么在何处存储供应商名、地址、联系方法等供应商信息呢？将这些数据与产品信息分开存储的

在这个例子中可建立两个表：一个存储供应商信息，另一个存储产品信息。Vendors表包含所有供应商信息，每个供应商占一行，具有唯一的标识。此标识称为主键（primary key），可以是供应商ID或任何其他唯一值。

Products表只存储产品信息，除了存储供应商ID（Vendors表的主键）外，它不存储其他有关供应商的信息。Vendors表的主键将Vendors表与Products表关联，利用供应商ID能从Vendors表中找出相应供应商的详细信息。

这样做的好处是：

• 供应商信息不重复，不会浪费时间和空间；

• 如果供应商信息变动，可以只更新Vendors表中的单个记录，相关表中的数据不用改动；

• 由于数据不重复，数据显然是一致的，使得处理数据和生成报表更简单。

总之，关系数据可以有效地存储，方便地处理。因此，关系数据库的可伸缩性远比非关系数据库要好。

可伸缩（scale）
能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称为可伸缩性好（scale well）。

12.1.2 为什么使用联结

如果数据存储在多个表中，怎样用一条SELECT语句就检索出数据呢？

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

12.2 创建联结

创建联结非常简单，指定要联结的所有表以及关联它们的方式即可。

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

12.2.1 WHERE子句的重要性

在一条SELECT语句中联结几个表时，相应的关系是在运行中构造的。在数据库表的定义中没有指示DBMS如何对表进行联结的内容。你必须自己做这件事情。在联结两个表时，实际要做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE子句作为过滤条件，只包含那些匹配给定条件（这里是联结条件）的行。没有WHERE子句，第一个表中的每一行将与第二个表中的每一行配对，而不管它们逻辑上是否能配在一起。

笛卡儿积（cartesian product）
由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

SELECT vend_name, prod_name, prod_price
FROM Vendors, Products;

警告：不要忘了WHERE子句
要保证所有联结都有WHERE子句，否则DBMS将返回比想要的数据多得多的数据。同理，要保证WHERE子句的正确性。不正确的过滤条件会导致DBMS返回不正确的数据。

提示：叉联结
有时，返回笛卡儿积的联结，也称叉联结（cross join）。

12.2.2 内联结

目前为止使用的联结称为等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内联结（inner join）。

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

12.2.3 联结多个表

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;

警告：性能考虑
DBMS在运行时关联指定的每个表，以处理联结。这种处理可能非常耗费资源，因此应该注意，不要联结不必要的表。联结的表越多，性能下降越厉害。

警告：联结中表的最大数目
虽然SQL本身不限制每个联结约束中表的数目，但实际上许多DBMS都有限制。

如下的SELECT语句返回订购产品RGAN01的顾客列表：

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 cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
 AND OrderItems.order_num = Orders.order_num
 AND prod_id = 'RGAN01';

提示：多做实验
可以看到，执行任一给定的SQL操作一般不止一种方法。很少有绝对正确或绝对错误的方法。性能可能会受操作类型、所使用的DBMS、表中数据量、是否存在索引或键等条件的影响。因此，有必要试验不同的选择机制，找出最适合具体情况的方法

第13课 创建高级联结

13.1 使用表别名

给列起别名的语法如下：

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

SQL除了可以对列名和计算字段使用别名，还允许给表名起别名。这样做有两个主要理由：

• 缩短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';

警告：Oracle中没有AS
Oracle不支持AS关键字。要在Oracle中使用别名，可以不用AS，简单地指定列名即可（因此，应该是Customers C，而不是Customers AS C）。

需要注意，表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户端。

13.2 使用不同类型的联结

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

13.2.1 自联结

使用表别名的一个主要原因是能在一条SELECT语句中不止一次引用相同的表。下面举一个例子。

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

现在来看使用联结的相同查询：

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

提示：Oracle中没有AS
Oracle用户应该记住去掉AS。

提示：用自联结而不用子查询
自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的，但许多DBMS处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。

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

提示：Oracle中没有AS
Oracle用户应该记住去掉AS。

在这个例子中，通配符只对第一个表使用。所有其他列明确列出，所以没有重复的列被检索出来。

事实上，我们迄今为止建立的每个内联结都是自然联结，很可能永远都不会用到不是自然联结的内联结。

13.2.3 外联结

许多联结将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行。这种联结称为外联结。

警告：语法差别
需要注意，用来创建外联结的语法在不同的SQL实现中可能稍有不同。

要检索包括没有订单顾客在内的所有顾客，可如下进行：

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

在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。

警告：SQLite外联结
SQLite支持LEFT OUTER JOIN，但不支持RIGHT OUTER JOIN。幸好，如果你确实需要在SQLite中使用RIGHT OUTER JOIN，有一种更简单的办法，这将在下面的提示中介绍。

提示：外联结的类型
要记住，总是有两种基本的外联结形式：左外联结和右外联结。它们之间的唯一差别是所关联的表的顺序。换句话说，调整FROM或WHERE子句中表的顺序，左外联结可以转换为右外联结。因此，这两种外联结可以互换使用，哪个方便就用哪个。

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

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

警告：FULL OUTER JOIN的支持
Access、MariaDB、MySQL、Open Office Base或SQLite不支持FULL OUTER JOIN语法。

13.3 使用带聚集函数的联结

聚集函数用来汇总数据。这些函数也可以与联结一起使用。

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

提示：Oracle中没有AS
再次提醒Oracle用户，请记住删除AS。

13.4 使用联结和联结条件

联结及其使用的要点。

• 注意所使用的联结类型。一般我们使用内联结，但使用外联结也有效。
• 关于确切的联结语法，应该查看具体的文档，看相应的DBMS支持何种语法（大多数DBMS使用这两课中描述的某种语法）。
• 保证使用正确的联结条件（不管采用哪种语法），否则会返回不正确的数据。
• 应该总是提供联结条件，否则会得出笛卡儿积。
• 在一个联结中可以包含多个表，甚至可以对每个联结采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前分别测试每个联结。这会使故障排除更为简单。

第14课 组合查询

14.1 组合查询

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

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

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

提示：组合查询和多个WHERE条件
多数情况下，组合相同表的两个查询所完成的工作与具有多个WHERE子句条件的一个查询所完成的工作相同。换句话说，任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询

14.2 创建组合查询

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

14.2.1 使用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组合SELECT语句的数目，SQL没有标准限制。但是，最好是参考一下具体的DBMS文档，了解它是否对UNION能组合的最大语句数目有限制。

警告：性能问题
多数好的DBMS使用内部查询优化程序，在处理各条SELECT语句前组合它们。理论上讲，这意味着从性能上看使用多条WHERE子句条件还是UNION应该没有实际的差别。不过我说的是理论上，实践中多数查询优化程序并不能达到理想状态，所以最好测试一下这两种方法，看哪种工作得更好。

14.2.2 UNION规则

UNION非常容易使用，但在进行组合时需要注意几条规则。

• UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合四条SELECT语句，将要使用三个UNION关键字）。

• UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同的次序列出）。

• 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含转换的类型（例如，不同的数值类型或不同的日期类型）。

14.2.3 包含或取消重复的行

UNION从查询结果集中自动去除了重复的行；使用UNION时，重复的行会被自动取消。

这是UNION的默认行为，如果愿意也可以改变它。事实上，如果想返回所有的匹配行，可使用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，DBMS不取消重复的行。

提示：UNION与WHERE
UNION几乎总是完成与多个WHERE条件相同的工作。UNION ALL为UNION的一种形式，它完成WHERE子句完成不了的工作。如果确实需要每个条件的匹配行全部出现（包括重复行），就必须使用UNION ALL，而不是WHERE。

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
某些DBMS还支持另外两种UNION：EXCEPT（有时称为MINUS）可用来检索只在第一个表中存在而在第二个表中不存在的行；而INTERSECT可用来检索两个表中都存在的行。实际上，这些UNION很少使用，因为相同的结果可利用联结得到。

提示：操作多个表
为了简单，本课中的例子都是使用UNION来组合针对同一表的多个查询。实际上，UNION在需要组合多个表的数据时也很有用，即使是有不匹配列名的表，在这种情况下，可以将UNION与别名组合，检索一个结果集。

第15课 插入数据

15.1 数据插入

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

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

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

15.1.1 插入完整的行

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

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

提示：INTO关键字
在某些SQL实现中，跟在INSERT之后的INTO关键字是可选的。但是，即使不一定需要，最好还是提供这个关键字，这样做将保证SQL代码在DBMS之间可移植。

编写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语句。给出列能使SQL代码继续发挥作用，即使表结构发生了变化。

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

15.1.2 插入部分行

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

警告：省略列
如果表的定义允许，则可以在INSERT操作中省略某些列。省略的列必须满足以下某个条件。

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

如果对表中不允许NULL值且没有默认值的列不给出值，DBMS将产生错误消息，并且相应的行插入不成功。

警告：省略所需的值
如果表中不允许有NULL值或者默认值，这时却省略了表中的值，DBMS就会产生错误消息，相应的行不能成功插入。

15.1.3 插入检索出的数据

INSERT一般用来给表插入具有指定列值的行。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表。为了试验这个例子，应该首先创建和填充CustNew表。CustNew表的结构与附录A中描述的Customers表相同。在填充CustNew时，不应该使用已经在Customers中用过的cust_id值（如果主键值重复，后续的INSERT操作将会失败）。

提示：INSERT SELECT中的列名
为简单起见，这个例子在INSERT和SELECT语句中使用了相同的列名。但是，不一定要求列名匹配。事实上，DBMS一点儿也不关心SELECT返回的列名。它使用的是列的位置，因此SELECT中的第一列（不管其列名）将用来填充表列中指定的第一列，第二列将用来填充表列中指定的第二列，如此等等。

INSERT SELECT中SELECT语句可以包含WHERE子句，以过滤插入的数据。

提示：插入多行
INSERT通常只插入一行。要插入多行，必须执行多个INSERT语句。INSERT SELECT是个例外，它可以用一条INSERT插入多行，不管SELECT语句返回多少行，都将被INSERT插入。

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

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

说明：DB2不支持
DB2不支持这里描述的SELECT INTO。

与INSERT SELECT将数据添加到一个已经存在的表不同，SELECT INTO将数据复制到一个新表（有的DBMS可以覆盖已经存在的表，这依赖于所使用的具体DBMS）。

说明：INSERT SELECT与SELECT INTO
它们之间的一个重要差别是前者导出数据，而后者导入数据。

SELECT *
INTO CustCopy
FROM Customers;

MariaDB、MySQL、Oracle、PostgreSQL和SQLite使用的语法稍有不同：

CREATE TABLE CustCopy AS
SELECT * FROM Customers;

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

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

提示：进行表的复制
SELECT INTO是试验新SQL语句前进行表复制的很好工具。先进行复制，可在复制的数据上测试SQL代码，而不会影响实际的数据。

第16课 更新和删除数据

16.1 更新数据

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

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

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

提示：UPDATE与安全
在客户端/服务器的DBMS中，使用UPDATE语句可能需要特殊的安全权限。在你使用UPDATE前，应该保证自己有足够的安全权限。

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

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

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

提示：在UPDATE语句中使用子查询
UPDATE语句中可以使用子查询，使得能用SELECT语句检索出的数据更新列数据。

提示：FROM关键字
有的SQL实现支持在UPDATE语句中使用FROM子句，用一个表的数据更新另一个表的行。如想知道你的DBMS是否支持这个特性，请参阅它的文档。

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

UPDATE Customers
SET cust_email = NULL
WHERE cust_id = '1000000005';

16.2 删除数据

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

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

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

提示：DELETE与安全
在客户端/服务器的DBMS中，使用DELETE语句可能需要特殊的安全权限。在你使用DELETE前，应该保证自己有足够的安全权限。

DELETE FROM Customers
WHERE cust_id = '1000000006';

提示：FROM关键字
在某些SQL实现中，跟在DELETE后的关键字FROM是可选的。但是即使不需要，也最好提供这个关键字。这样做将保证SQL代码在DBMS之间可移植。

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

说明：删除表的内容而不是表
DELETE语句从表中删除行，甚至是删除表中所有行。但是，DELETE不删除表本身。

提示：更快的删除
如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，而速度更快（因为不记录数据的变动）。

16.3 更新和删除的指导原则

如果省略了WHERE子句，则UPDATE或DELETE将被应用到表中所有的行。换句话说，如果执行UPDATE而不带WHERE子句，则表中每一行都将用新值更新。类似地，如果执行DELETE语句而不带WHERE子句，表的所有数据都将被删除。

下面是许多SQL程序员使用UPDATE或DELETE时所遵循的重要原则。

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

若是SQL没有撤销（undo）按钮，应该非常小心地使用UPDATE和DELETE，否则你会发现自己更新或删除了错误的数据。

第17课 创建和操纵表

17.1 创建表

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

• 多数DBMS都具有交互式创建和管理数据库表的工具；
• 表也可以直接用SQL语句操纵。

用程序创建表，可以使用SQL的CREATE TABLE语句。需要注意的是，使用交互式工具时实际上就是使用SQL语句。这些语句不是用户编写的，界面工具会自动生成并执行相应的SQL语句

警告：语法差别
在不同的SQL实现中，CREATE TABLE语句的语法可能有所不同。对于具体的DBMS支持何种语法，请参阅相应的文档。

17.1.1 表创建基础

利用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要求首先手工删除该表（请参阅后面的内容），然后再重建它，而不是简单地用创建表语句覆盖它。

17.1.2 使用NULL值

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

警告：指定NULL
在不指定NOT NULL时，多数DBMS认为指定的是NULL，但不是所有的DBMS都这样。DB2要求指定关键字NULL，如果不指定将出错。关于完整的语法信息，请参阅具体的DBMS文档。

提示：主键和NULL值
第1课介绍过，主键是其值唯一标识表中每一行的列。只有不允许NULL值的列可作为主键，允许NULL值的列不能作为唯一标识。

警告：理解NULL
不要把NULL值与空字符串相混淆。NULL值是没有值，不是空字符串。如果指定’'（两个单引号，其间没有字符），这在NOT NULL列中是允许的。空字符串是一个有效的值，它不是无值。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
);

默认值经常用于日期或时间戳列。例如，通过指定引用系统日期的函数或变量，将系统日期用作默认日期。MySQL用户指定DEFAULT CURRENT_DATE()，Oracle用户指定DEFAULT SYSDATE，而SQL Server用户指定DEFAULT GETDATE()。遗憾的是，这条获得系统日期的命令在不同的DBMS中几乎都是不同的。

DBMS	函数/变量
Access	NOW()
DB2	CURRENT_DATE
MySQL	CURRENT_DATE()
Oracle	SYSDATE
PostgreSQL	CURRENT_DATE
SQL Server	GETDATE()
SQLite	date(‘now’)

提示：使用DEFAULT而不是NULL值
许多数据库开发人员喜欢使用DEFAULT值而不是NULL列，对于用于计算或数据分组的列更是如此。

17.2 更新表

更新表定义，可以使用ALTER TABLE语句。虽然所有的DBMS都支持ALTER TABLE，但它们所允许更新的内容差别很大。以下是使用ALTER TABLE时需要考虑的事情。

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

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

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

因为给已有表增加列可能是所有DBMS都支持的唯一操作，所以我们举个这样的例子：

ALTER TABLE Vendors
ADD vend_phone CHAR(20);

更改或删除列、增加约束或增加键，这些操作也使用类似的语法（注意，下面的例子并非对所有DBMS都有效）：

ALTER TABLE Vendors
DROP COLUMN vend_phone;

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

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

说明：ALTER TABLE和SQLite
SQLite对使用ALTER TABLE执行的操作有所限制。最重要的一个限制是，它不支持使用ALTER TABLE定义主键和外键，这些必须在最初创建表时指定。

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

17.3 删除表

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

DROP TABLE CustCopy;

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

17.4 重命名表

每个DBMS对表重命名的支持有所不同。对于这个操作，不存在严格的标准。DB2、MariaDB、MySQL、Oracle和PostgreSQL用户使用RENAME语句，SQL Server用户使用sp_rename存储过程，SQLite用户使用ALTER TABLE语句。

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

第18课 使用视图

18.1 视图

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

说明：DBMS支持
Microsoft Access不支持视图，没有与SQL视图一致的工作方式。因此，这一课的内容不适用Microsoft Access。

MySQL从版本5起开始支持视图，因此，这一课的内容不适用较早版本的MySQL。

SQLite仅支持只读视图，所以视图可以创建，可以读，但其内容不能更改。

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

SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.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是一个视图，作为视图，它不包含任何列或数据，包含的是一个查询（与上面用以正确联结表的相同查询）。

提示：DBMS的一致支持
我们欣慰地了解到，所有DBMS非常一致地支持视图创建语法。

18.1.1 为什么使用视图

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

• 重用SQL语句。

• 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节。

• 使用表的一部分而不是整个表。

• 保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。

• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

创建视图之后，可以用与表基本相同的方式使用它们。可以对视图执行SELECT操作，过滤和排序数据，将视图联结到其他视图或表，甚至添加和更新数据

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

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

18.1.2 视图的规则和限制

下面是关于视图创建和使用的一些最常见的规则和限制。

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

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

这条语句通过WHERE子句从视图中检索特定数据。当DBMS处理此查询时，它将指定的WHERE子句添加到视图查询中已有的WHERE子句中，以便正确过滤数据。

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

视图的另一常见用途是重新格式化检索出的数据。

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

要检索数据，创建所有的邮件标签，可如下进行：

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子句与WHERE子句
从视图检索数据时如果使用了一条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语句。可将其视为批文件，虽然它们的作用不仅限于批处理。

说明：具体DBMS的支持
Microsoft Access和SQLite不支持存储过程。因此，本课的内容不适用它们。

MySQL 5已经支持存储过程。因此，本课的内容不适用MySQL较早的版本。

19.2 为什么要使用存储过程

理由很多，下面列出一些主要的。

• 通过把处理封装在一个易用的单元中，可以简化复杂的操作。

• 由于不要求反复建立一系列处理步骤，因而保证了数据的一致性。如果所有开发人员和应用程序都使用同一存储过程，则所使用的代码都是相同的。
  这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。

• 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，那么只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
  这一点的延伸就是安全性。通过存储过程限制对基础数据的访问，减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。

• 因为存储过程通常以编译过的形式存储，所以DBMS处理命令的工作较少，提高了性能。

• 存在一些只能用在单个请求中的SQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

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

不过，在将SQL代码转换为存储过程前，也必须知道它的一些缺陷。

• 不同DBMS中的存储过程语法有所不同。事实上，编写真正的可移植存储过程几乎是不可能的。不过，存储过程的自我调用（名字以及数据如何传递）可以相对保持可移植。因此，如果需要移植到别的DBMS，至少客户端应用代码不需要变动。
• 一般来说，编写存储过程比编写基本SQL语句复杂，需要更高的技能，更丰富的经验。因此，许多数据库管理员把限制存储过程的创建作为安全措施（主要受上一条缺陷的影响）。

说明：不能编写存储过程？你依然可以使用
大多数DBMS将编写存储过程所需的安全和访问权限与执行存储过程所需的安全和访问权限区分开来。这是好事情，即使你不能（或不想）编写自己的存储过程，也仍然可以在适当的时候执行别的存储过程。

19.3 执行存储过程

存储过程的执行远比编写要频繁得多，因此我们先介绍存储过程的执行。执行存储过程的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个参数都有值；
• 生成用作主键的唯一ID；
• 将新产品插入Products表，在合适的列中存储生成的主键和传递的数据。

这就是存储过程执行的基本形式。对于具体的DBMS，可能包括以下的执行选择：

• 参数可选，具有不提供参数时的默认值；
• 不按次序给出参数，以“参数=值”的方式给出参数值。
• 输出参数，允许存储过程在正执行的应用程序中更新所用的参数。
• 用SELECT语句检索数据。
• 返回代码，允许存储过程返回一个值到正在执行的应用程序。

19.4 创建存储过程

我们来看一个简单的存储过程例子，它对邮件发送清单中具有邮件地址的顾客进行计数。

下面是该过程的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;

下面是相同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课 管理事务处理

20.1 事务处理

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

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

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

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

提示：可以回退哪些语句？
事务处理用来管理INSERT、UPDATE和DELETE语句。不能回退SELECT语句（回退SELECT语句也没有必要），也不能回退CREATE或DROP操作。事务处理中可以使用这些语句，但进行回退时，这些操作也不撤销。

20.2 控制事务处理

警告：事务处理实现的差异
不同DBMS用来实现事务处理的语法有所不同。在使用事务处理时请参阅相应的DBMS文档。

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

有的DBMS要求明确标识事务处理块的开始和结束。如在SQL Server中，标识如下：

BEGIN TRANSACTION
...
COMMIT TRANSACTION

在这个例子中，BEGIN TRANSACTION和COMMIT TRANSACTION语句之间的SQL必须完全执行或者完全不执行。

MariaDB和MySQL中等同的代码为：

START TRANSACTION
...

Oracle使用的语法：

SET TRANSACTION
...

PostgreSQL使用ANSI SQL语法：

BEGIN
...

其他DBMS采用上述语法的变体。你会发现，多数实现没有明确标识事务处理在何处结束。事务一直存在，直到被中断。通常，COMMITT用于保存更改，ROLLBACK用于撤销。

20.2.1 使用ROLLBACK

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

DELETE FROM Orders;
ROLLBACK;

20.2.2 使用COMMIT

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

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

进行明确的提交，使用COMMIT语句。下面是一个SQL Server的例子：

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

为在Oracle中完成相同的工作，可如下进行：

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

20.2.3 使用保留点

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

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

在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以保留数据。

提示：保留点越多越好
可以在SQL代码中设置任意多的保留点，越多越好。为什么呢？因为保留点越多，你就越能灵活地进行回退。

第21课 使用游标

21.1 游标

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

结果集（result set）
SQL查询所检索出的结果。

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

说明：具体DBMS的支持
Microsoft Access不支持游标，所以本课的内容不适用于Microsoft Access。

MySQL 5已经支持游标。因此，本课的内容不适用MySQL较早的版本。

SQLite支持的游标称为步骤（step），本课讲述的基本概念适用于SQLite的步骤，但语法可能完全不同。

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

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

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

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

下面是Oracle和PostgreSQL版本：

DECLARE CURSOR CustCursor
IS
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;

下面是另一个例子，这次使用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特性

22.1 约束

关系数据库存储分解为多个表的数据，每个表存储相应的数据。利用键来建立从一个表到另一个表的引用（由此产生了术语引用完整性（referential integrity））。

虽然可以在插入新行时进行检查（在另一个表上执行SELECT，以保证所有值合法并存在），但最好不要这样做，原因如下：

• 如果在客户端层面上实施数据库完整性规则，则每个客户端都要被迫实施这些规则，一定会有一些客户端不实施这些规则。
• 在执行UPDATE和DELETE操作时，也必须实施这些规则。
• 执行客户端检查是非常耗时的，而DBMS执行这些检查会相对高效。

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

DBMS通过在数据库表上施加约束来实施引用完整性。大多数约束是在表定义中定义的，如第17课所述，用CREATE TABLE或ALTER TABLE语句。

注意：具体DBMS的约束
有几种不同类型的约束，每个DBMS都提供自己的支持。因此，这里给出的例子在不同的DBMS上可能有不同的反应。在进行试验之前，请参阅具体的DBMS文档。

22.1.1 主键

主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且永不改动。换句话说，表中的一列（或多个列）的值唯一标识表中的每一行。这方便了直接或交互地处理表中的行。没有主键，要安全地UPDATE或DELETE特定行而不影响其他行会非常困难。

表中任意列只要满足以下条件，都可以用于主键：

• 任意两行的主键值都不相同。

• 每行都具有一个主键值（即列中不允许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
);

ALTER TABLE Vendors 
ADD CONSTRAINT PRIMARY KEY (vend_id);

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

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

22.1.2 外键

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

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)

提示：外键有助防止意外删除
如第6课所述，除帮助保证引用完整性外，外键还有另一个重要作用。在定义外键后，DBMS不允许删除在另一个表中具有关联行的行。例如，不能删除关联订单的顾客。删除该顾客的唯一方法是首先删除相关的订单（这表示还要删除相关的订单项）。由于需要一系列的删除，因而利用外键可以防止意外删除数据。

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

22.1.3 唯一约束

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

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

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

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

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

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

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

22.2 索引

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

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

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

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

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

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

索引用CREATE INDEX语句创建（不同DBMS创建索引的语句变化很大）。下面的语句在Products表的产品名列上创建一个简单的索引：

CREATE INDEX prod_name_ind
ON PRODUCTS (prod_name);

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

提示：检查索引
索引的效率随表数据的增加或改变而变化。许多数据库管理员发现，过去创建的某个理想的索引经过几个月的数据处理后可能变得不再理想了。最好定期检查索引，并根据需要对索引进行调整。

22.3 触发器

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

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

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

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

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

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

• 保证数据一致。例如，在INSERT或UPDATE操作中将所有州名转换为大写。

• 基于某个表的变动在其他表上执行活动。例如，每当更新或删除一行时将审计跟踪记录写入某个日志表。

• 进行额外的验证并根据需要回退数据。例如，保证某个顾客的可用资金不超限定，如果已经超出，则阻塞插入。

• 计算计算列的值或更新时间戳。

读者可能已经注意到了，不同DBMS的触发器创建语法差异很大，更详细的信息请参阅相应的文档。

下面的例子创建一个触发器，它对所有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 数据库安全

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

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

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

安全性使用SQL的GRANT和REVOKE语句来管理，不过，大多数DBMS提供了交互式的管理实用程序，这些实用程序在内部使用GRANT和REVOKE语句。

第23课 附录C SQL语句的语法

在阅读语句语法时，应该记住以下约定。

• |符号用来指出几个选择中的一个，因此，NULL | NOT NULL表示或者给出NULL或者给出NOT NULL。
• 包含在方括号中的关键字或子句（如[like this]）是可选的。
• 下面列出的语法几乎对所有DBMS都有效。关于具体语法可能变动的细节，建议读者参考自己的DBMS文档。

23.1 ALTER TABLE

ALTER TABLE用来更新已存在表的结构。为了创建新表，应该使用CREATE TABLE。

ALTER TABLE tablename
(
  ADD|DROP column datatype [NULL|NOT NULL] [CONSTRAINTS],
  ADD|DROP column datatype [NULL|NOT NULL] [CONSTRAINTS],
    ...
);

23.2 COMMIT

COMMIT用来将事务写入数据库。

COMMIT [TRANSACTION];

23.3 CREATE INDEX

CREATE INDEX用于在一个或多个列上创建索引。

CREATE INDEX indexname
ON tablename (column, ...);

23.4 CREATE PROCEDURE

CREATE PROCEDURE用于创建存储过程。Oracle使用的语法稍有不同。

CREATE PROCEDURE procedurename [parameters] [options]
AS
SQL statement;

23.5 CREATE TABLE

CREATE TABLE用于创建新数据库表。更新已经存在的表的结构，使用ALTER TABLE。

CREATE TABLE tablename
(
    column datatype [NULL|NOT NULL] [CONSTRAINTS],
    column datatype [NULL|NOT NULL] [CONSTRAINTS],
       ...
);

23.6 CREATE VIEW

CREATE VIEW用来创建一个或多个表上的新视图。

CREATE VIEW viewname AS
SELECT columns, ...
FROM tables, ...
[WHERE ...]
[GROUP BY ...]
[HAVING ...];

23.7 DELETE

DELETE从表中删除一行或多行。

DELETE FROM tablename
[WHERE ...];

23.8 DROP

DROP永久地删除数据库对象（表、视图、索引等）。

DROP INDEX|PROCEDURE|TABLE|VIEW
indexname|procedurename|tablename|viewname; 

23.9 INSERT

INSERT为表添加一行。

INSERT INTO tablename [(columns, ...)]
VALUES(values, ...);

23.10 INSERT SELECT

INSERT SELECT将SELECT的结果插入到一个表。

INSERT INTO tablename [(columns, ...)]
SELECT columns, ... FROM tablename, ...
[WHERE ...];

23.11 ROLLBACK

ROLLBACK用于撤销一个事务块。

ROLLBACK [ TO savepointname];

或

ROLLBACK TRANSACTION; 

23.12 SELECT

SELECT用于从一个或多个表（视图）中检索数据。

SELECT columnname, ...
FROM tablename, ...
[WHERE ...]
[UNION ...]
[GROUP BY ...]
[HAVING ...]
[ORDER BY ...];

23.13 UPDATE

UPDATE更新表中的一行或多行。

UPDATE tablename
SET columname = value, ...
[WHERE ...];

第24课 附录D SQL数据类型

24.1 字符串数据类型

数据类型	说明
CHAR	1～255个字符的定长字符串。它的长度必须在创建时规定
NCHAR	CHAR的特殊形式，用来支持多字节或Unicode字符（此类型的不同实现变化很大）
NVARCHAR	TEXT的特殊形式，用来支持多字节或Unicode字符（此类型的不同实现变化很大）
TEXT（也称为LONG、MEMO或VARCHAR）	变长文本

提示：使用引号
不管使用何种形式的字符串数据类型，字符串值都必须括在单引号内。

警告：当数值不是数值时
你可能会认为电话号码和邮政编码应该存储在数值字段中（数值字段只存储数值数据），但是这样做并不可取。如果在数值字段中存储邮政编码01234，则保存的将是数值1234，实际上丢失了一位数字。

需要遵守的基本规则是：如果数值是计算（求和、平均等）中使用的数值，则应该存储在数值数据类型列中；如果作为字符串（可能只包含数字）使用，则应该保存在字符串数据类型列中。

24.2 数值数据类型

数据类型	说明
BIT	单个二进制位值，或者为0或者为1，主要用于开/关标志
DECIMAL（或NUMERIC）	定点或精度可变的浮点值
FLOAT（或NUMBER）	浮点值
INT（或INTEGER）	4字节整数值，支持-2147483648～2147483647的数
REAL	4字节浮点值
SMALLINT	2字节整数值，支持-32768～32767的数
TINYINT	1字节整数值，支持0～255的数

提示：不使用引号
与字符串不一样，数值不应该括在引号内。

提示：货币数据类型
多数DBMS支持一种用来存储货币值的特殊数值数据类型。一般记为MONEY或CURRENCY，这些数据类型基本上是有特定取值范围的DECIMAL数据类型，更适合存储货币值。

24.3 日期和时间数据类型

数据类型	说明
DATE	日期值
DATETIME（或TIMESTAMP）	日期时间值
SMALLDATETIME	日期时间值，精确到分（无秒或毫秒）
TIME	时间值

警告：指定日期
不存在所有DBMS都理解的定义日期的标准方法。多数实现都理解诸如2015-12-30或Dec 30th, 2015等格式，但即使这样，有的DBMS还是不理解它们。至于具体的DBMS能识别哪些日期格式，请参阅相应的文档。

提示：ODBC日期
因为每种DBMS都有自己特定的日期格式，所以ODBC创建了一种自己的格式，在使用ODBC时对每种数据库都起作用。ODBC格式对于日期类似于{d '2005-12-30'}，对于时间类似于{t '21:46:29'}，而对于日期时间类似于{ts '2005-12-30 21:46:29'}。如果通过ODBC使用SQL，应该以这种方式格式化日期和时间。

24.4 二进制数据类型

二进制数据类型是最不具有兼容性（幸运的是，也是最少使用）的数据类型。与迄今为止介绍的所有数据类型（它们具有特定的用途）不一样，二进制数据类型可包含任何数据，甚至可包含二进制信息，如图像、多媒体、字处理文档等

数据类型	说明
BINARY	定长二进制数据（最大长度从255字节到8000字节，有赖于具体的实现）
LONG RAW	变长二进制数据，最长2 GB
RAW（某些实现为BINARY）	定长二进制数据，最多255字节
VARBINARY	变长二进制数据（最大长度一般在255字节到8000字节间变化，依赖于具体的实现）