1、检索数据

1.1、检索不同的值：DISTINCT关键字

    SELECT DISTINCT vend_id FROM Products;

SELECT DISTINCT vend_id告诉DBMS只返回不同（具有唯一性）的vend_id行。

**注意：**不能部分使用DISTINCT。DISTINCT关键字作用于所有的列，不仅仅是跟在其后的那一列。

1.2、限制结果

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

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

    SELECT TOP 5 prod_name
    FROM Products;

上面代码使用SELECT TOP 5语句，只检索前5行数据。

2、如果你使用的是DB2，就得使用下面这样的DB2特有的SQL语句：

    SELECT prod_name
    FROM Products
    FETCH FIRST 5 ROWS ONLY;

3、如果你使用Oracle，需要基于ROWNUM（行计数器）来计算行，像这样：

    SELECT prod_name
    FROM Products
    WHERE ROWNUM <=5;

4、如果你使用MySQL、MariaDB、PostgreSQL或者SQLite，需要使用LIMIT子句，像这样：

    SELECT prod_name
    FROM Products
    LIMIT 5;

上述代码使用SELECT语句来检索单独的一列数据。LIMIT 5指示MySQL等DBMS返回不超过5行的数据。这个语句的输出参见下面的代码。

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

    SELECT prod_name
    FROM Products
    LIMIT 5 OFFSET 5;

LIMIT 5 OFFSET 5指示MySQL等DBMS返回从第5行起的5行数据。第一个数字是检索的行数，第二个数字是指从哪儿开始。

2、排序检索数据

关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有任何意义。

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

2.1、排序数据

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

    SELECT prod_name
    FROM Products
    ORDER BY prod_name;

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

2.2、按多个列排序

要按多个列排序，只须指定这些列名，列名之间用逗号分开即可（就像选择多个列时那样）。
下面的代码检索3个列，并按其中两个列对结果进行排序——首先按价格，然后按名称排序。

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

重要的是理解在按多个列排序时，排序的顺序完全按规定进行。换句话说，对于上述例子中的输出，仅在多个行具有相同的prod_price值时才对产品按prod_name进行排序。如果prod_price列中所有的值都是唯一的，则不会按prod_name排序。

2.3、按列位置排序

除了能用列名指出排序顺序外，ORDER BY还支持按相对列位置进行排序。为理解这一内容，我们来看个例子：

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

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

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

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

2.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关键字。

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

提示：区分大小写和排序顺序在对文本性数据进行排序时，A与a相同吗？a位于B之前，还是Z之后？这些问题不是理论问题，其答案取决于数据库的设置方式。在字典（dictionary）排序顺序中，A被视为与a相同，这是大多数数据库管理系统的默认做法。但是，许多DBMS允许数据库管理员在需要时改变这种行为（如果你的数据库包含大量外语字符，可能必须这样做）。这里的关键问题是，如果确实需要改变这种排序顺序，用简单的ORDER BY子句可能做不到。你必须请求数据库管理员的帮助。

3、过滤数据

3.1、使用WHERE子句

数据库表一般包含大量的数据，很少需要检索表中的所有行。通常只会根据特定操作或报告的需要提取表数据的子集。只检索所需数据需要指定搜索条件（search criteria），搜索条件也称为过滤条件（filter condition）。

在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之后，否则将会产生错误。

3.2、WHERE子句操作符

我们在做相等检验时看到了第一个WHERE子句，它确定一个列是否包含指定的值。下表列出的所有条件操作符。

注意：上表中列出的某些操作符是冗余的（如< >与！=相同，!<相当于>=）。并非所有DBMS都支持这些操作符。想确定你的DBMS支持哪些操作符，请参阅相应的文档。

3.2.1、检查单个值

列出所有价格小于10美元的产品。

    SELECT prod_name, prod_price
    FROM Products
    WHERE prod_price < 10;

3.2.2、不匹配检查

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

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

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

下面是相同的例子，其中使用！=而不是<>操作符：

    SELECT vend_id, prod_name
    FROM Products
    WHERE vend_id != 'DLL01';

注意：是！=还是<>？！=和<>通常可以互换。但是，并非所有DBMS都支持这两种不等于操作符。如果有疑问，请参阅相应的DBMS文档。

3.2.3、范围值检查

要检查某个范围的值，可以使用BETWEEN操作符。其语法与其他WHERE子句的操作符稍有不同，因为它需要两个值，即范围的开始值和结束值。例如，BETWEEN操作符可用来检索价格在5美元和10美元之间的所有产品，或在指定的开始日期和结束日期之间的所有日期。

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

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

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

3.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;

这条语句返回所有没有价格（空prod_price字段，不是价格为0）的产品，由于表中没有这样的行，所以没有返回数据。但是，Customers表确实包含具有NULL值的列：如果没有电子邮件地址，则cust_email列将包含NULL值：

    SELECT cust_name
    FROM Customers
    WHERE cust_email IS NULL;

注意：NULL和非匹配通过过滤选择不包含指定值的所有行时，你可能希望返回含NULL值的行。但是这做不到。因为NULL比较特殊，所以在进行匹配过滤或非匹配过滤时，不会返回这些结果。

4、高级数据过滤

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

4.1、组合WHERE子句

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

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

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

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

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

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

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

此SQL语句检索由任一个指定供应商制造的所有产品的产品名和价格。OR操作符告诉DBMS匹配任一条件而不是同时匹配两个条件。

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

但是，组合AND和OR会带来了一个有趣的问题。为了说明这个问题，来看一个例子。假如需要列出价格为10美元及以上，且由DLL01或BRS01制造的所有产品。下面的SELECT语句使用组合的AND和OR操作符建立了一个WHERE子句：

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

请看上面的结果。返回的行中有4行价格小于10美元，显然，返回的行未按预期的进行过滤。为什么会这样呢？原因在于求值的顺序。SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符。当SQL看到上述WHERE子句时，它理解为：由供应商BRS01制造的价格为10美元以上的所有产品，以及由供应商DLL01制造的所有产品，而不管其价格如何。换句话说，由于AND在求值过程中优先级更高，操作符被错误地组合了。此问题的解决方法是使用圆括号对操作符进行明确分组。请看下面的SELECT语句及输出：

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

这条SELECT语句与前一条的唯一差别是，将前两个条件用圆括号括了起来。因为圆括号具有比AND或OR操作符更高的优先级，所以DBMS首先过滤圆括号内的OR条件。这时，SQL语句变成了选择由供应商DLL01或BRS01制造的且价格在10美元及以上的所有产品，这正是我们希望的结果。

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

4.2、IN操作符

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

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

此SELECT语句检索由供应商DLL01和BRS01制造的所有产品。IN操作符后跟由逗号分隔的合法值，这些值必须括在圆括号中。

你可能会猜测IN操作符完成了与OR相同的功能，恭喜你猜对了！下面的SQL语句完成与上面的例子相同的工作。

    SELECT prod_name, prod_price
    FROM Products
    WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'
    ORDER BY prod_name;

为什么要使用IN操作符？其优点如下。

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

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

4.3、NOT操作符

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

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

下面的例子说明NOT的用法。为了列出除DLL01之外的所有供应商制造的产品，可编写如下的代码。

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

这里的NOT否定跟在其后的条件，因此，DBMS不是匹配vend_id为DLL01，而是匹配非DLL01之外的所有东西。

上面的例子也可以使用<>操作符来完成，如下所示。

    SELECT prod_name
    FROM Products
    WHERE vend_id  <> 'DLL01'
    ORDER BY prod_name;

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

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

5、用通配符进行过滤

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

5.1、LIKE操作符

通配符（wildcard）：用来匹配值的一部分的特殊字符。
搜索模式（search pattern）：由字面值、通配符或两者组合构成的搜索条件。

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

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

5.1.1、百分号（%）通配符

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

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

此例子使用了搜索模式’Fish%'。在执行这条子句时，将检索任意以Fish起头的词。%告诉DBMS接受Fish之后的任意字符，不管它有多少字符。

说明：区分大小写根据DBMS的不同及其配置，搜索可以是区分大小写的。如果区分大小写，则’fish%’与Fish bean bag toy就不匹配。

2、通配符可在搜索模式中的任意位置使用，并且可以使用多个通配符。下面的例子使用两个通配符，它们位于模式的两端：

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

搜索模式’%bean bag%’表示匹配任何位置上包含文本bean bag的值，不论它之前或之后出现什么字符。

3、通配符也可以出现在搜索模式的中间，虽然这样做不太有用。下面的例子找出以F起头、以y结尾的所有产品：

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

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

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

说明：请注意后面所跟的空格有些DBMS用空格来填补字段的内容。例如，如果某列有50个字符，而存储的文本为Fish bean bag toy（17个字符），则为填满该列需要在文本后附加33个空格。这样做一般对数据及其使用没有影响，但是可能对上述SQL语句有负面影响。子句WHERE prod_name LIKE 'F%y’只匹配以F开头、以y结尾的prod_name。如果值后面跟空格，则不是以y结尾，所以Fish bean bag toy就不会检索出来。简单的解决办法是给搜索模式再增加一个%号：'F%y%’还匹配y之后的字符（或空格）。更好的解决办法是用函数去掉空格。

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

5.1.2、下划线（_）通配符

另一个有用的通配符是下划线（）。下划线的用途与%一样，但它只匹配单个字符，而不是多个字符。
**说明：DB2通配符DB2不支持通配符。**

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

说明：请注意后面所跟的空格与上例一样，可能需要给这个模式添加一个通配符。

这个WHERE子句中的搜索模式给出了后面跟有文本的两个通配符。结果只显示匹配搜索模式的行：第一行中下划线匹配12，第二行中匹配18。8 inch teddy bear产品没有匹配，因为搜索模式要求匹配两个通配符而不是一个。对照一下，下面的SELECT语句使用%通配符，返回三行产品：

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

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

5.1.3、方括号（[ ]）通配符

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

说明：并不总是支持集合：与前面描述的通配符不一样，并不是所有DBMS都支持用来创建集合的[]。微软的SQL Server支持集合，但是MySQL,Oracle,DB2,SQLite都不支持。为确定你使用的DBMS是否支持集合，请参阅相应的文档。

例如，找出所有名字以J或M起头的联系人，可进行如下查询：

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

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

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

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

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

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

5.2、使用通配符的技巧

正如所见，SQL的通配符很有用。但这种功能是有代价的，即通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间。这里给出一些使用通配符时要记住的技巧。

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

6、创建计算字段

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

6.1、计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式，下面举几个例子。

• 需要显示公司名，同时还需要显示公司的地址，但这两个信息存储在不同的表列中。
• 城市、州和邮政编码存储在不同的列中（应该这样），但邮件标签打印程序需要把它们作为一个有恰当格式的字段检索出来。
• 列数据是大小写混合的，但报表程序需要把所有数据按大写表示出来。
• 物品订单表存储物品的价格和数量，不存储每个物品的总价格（用价格乘以数量即可）。但为打印发票，需要物品的总价格。
• 需要根据表数据进行诸如总数、平均数的计算。

在上述每个例子中，存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换、计算或格式化过的数据，而不是检索出数据，然后再在客户端应用程序中重新格式化。

这就是计算字段可以派上用场的地方了。与前面介绍的列不同，计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的。

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

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

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

6.1、拼接字段

为了说明如何使用计算字段，我们来举一个简单例子，创建由两列组成的标题。

Vendors表包含供应商名和地址信息。假如要生成一个供应商报表，需要在格式化的名称（位置）中列出供应商的位置。

此报表需要一个值，而表中数据存储在两个列vend_name和vend_country中。此外，需要用括号将vend_country括起来，这些东西都没有存储在数据库表中。这个返回供应商名称和地址的SELECT语句很简单，但我们是如何创建这个组合值的呢？

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

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

说明：是+还是||？SQL Server使用+号。DB2、Oracle、PostgreSQL和SQLite使用||。详细请参阅具体的DBMS文档。

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;

分析：上面两个SELECT语句拼接以下元素。

• 存储在vend_name列中的名字；
• 包含一个空格和一个左圆括号的字符串；
• 存储在vend_country列中的国家；
• 包含一个右圆括号的字符串。

从上述输出中可以看到，SELECT语句返回包含上述四个元素的一个列（计算字段）。

再看看上述SELECT语句返回的输出。结合成一个计算字段的两个列用空格填充。许多数据库（不是所有）保存填充为列宽的文本值，而实际上你要的结果不需要这些空格。为正确返回格式化的数据，必须去掉这些空格。这可以使用SQL的RTRIM()函数来完成，如下所示：

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

使用别名：从前面的输出可以看到，SELECT语句可以很好地拼接地址字段。但是，这个新计算列的名字是什么呢？实际上它没有名字，它只是一个值。如果仅在SQL查询工具中查看一下结果，这样没有什么不好。但是，一个未命名的列不能用于客户端应用中，因为客户端没有办法引用它。

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

SELECT语句本身与以前使用的相同，只不过这里的计算字段之后跟了文本AS vend_title。它指示SQL创建一个包含指定计算结果的名为vend_title的计算字段。从输出可以看到，结果与以前的相同，但现在列名为vend_title，任何客户端应用都可以按名称引用这个列，就像它是一个实际的表列一样。

说明：AS通常可选在很多DBMS中，AS关键字是可选的，不过最好使用它，这被视为一条最佳实践。
提示：别名还有其他用途。常见的用途包括在实际的表列名包含不合法的字符（如空格）时重新命名它，在原来的名字含混或容易误解时扩充它。
注意：别名的名字既可以是一个单词，也可以是一个字符串。如果是后者，字符串应该括在引号中。虽然这种做法是合法的，但不建议这么去做。多单词的名字可读性高，不过会给客户端应用带来各种问题。因此，别名最常见的使用是将多个单词的列名重命名为一个单词的名字。
说明：导出列别名有时也称为导出列（derived column），不管怎么叫，它们所代表的是相同的东西。

6.3、执行算术计算

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

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

item_price列包含订单中每项物品的单价。如下汇总物品的价格（单价乘以订购数量）：

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

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

SQL支持表7-1中列出的基本算术操作符。此外，圆括号可用来区分优先顺序。

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

7、使用函数处理数据

什么是函数，DBMS支持何种函数，以及如何使用这些函数；还将讲解为什么SQL函数的使用可能会带来问题。

7.1、函数

去掉字符串尾的空格的RTRIM()就是一个函数。
与几乎所有DBMS都等同地支持SQL语句（如SELECT）不同，每一个DBMS都有特定的函数。事实上，只有少数几个函数被所有主要的DBMS等同地支持。虽然所有类型的函数一般都可以在每个DBMS中使用，但各个函数的名称和语法可能极其不同。为了说明可能存在的问题，下表列出了3个常用的函数及其在各个DBMS中的语法：

可以看到，与SQL语句不一样，SQL函数不是可移植的。这意味着为特定SQL实现编写的代码在其他实现中可能不能用。

为了代码的可移植，许多SQL程序员不赞成使用特定于实现的功能。虽然这样做很有好处，但有的时候并不利于应用程序的性能。如果不使用这些函数，编写某些应用程序代码会很艰难。必须利用其他方法来实现DBMS可以非常有效完成的工作。

7.2、使用函数

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

• 用于处理文本字符串（如删除或填充值，转换值为大写或小写）的文本函数。
• 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。
• 用于处理日期和时间值并从这些值中提取特定成分（如返回两个日期之差，检查日期有效性）的日期和时间函数。
• 用于生成美观好懂的输出内容的格式化函数（如用语言形式表达出日期，用货币符号和千分位表示金额）。
• 返回DBMS正使用的特殊信息（如返回用户登录信息）的系统函数。

7.2.1、文本处理函数

经看过一个文本处理函数的例子，其中使用RTRIM()函数来去除列值右边的空格。下面是另一个例子，这次使用的是UPPER()函数：

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

可以看到，UPPER()将文本转换为大写，因此本例子中每个供应商都列出两次，第一次为Vendors表中存储的值，第二次作为列vend_name_upcase转换为大写。

下表列出了一些常用的文本处理函数：

上表中的SOUNDEX需要做进一步的解释。SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对字符串进行发音比较而不是字母比较。虽然SOUNDEX不是SQL概念，但多数DBMS都提供对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子句正确地过滤出了所需的数据。

7.2.2、日期和时间处理函数

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

应用程序一般不使用日期和时间的存储格式，因此日期和时间函数总是用来读取、统计和处理这些值。由于这个原因，日期和时间函数在SQL中具有重要的作用。遗憾的是，它们很不一致，可移植性最差。

我们举个简单的例子，来说明日期处理函数的用法。Orders表中包含的订单都带有订单日期。要检索出某年的所有订单，需要按订单日期去找，但不需要完整日期，只要年份即可。
PostgreSQl:

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

这个例子使用了DATEPART()函数，顾名思义，此函数返回日期的某一部分。DATEPART()函数有两个参数，它们分别是返回的成分和从中返回成分的日期。在此例子中，DATEPART()只从order_date列中返回年份。通过与2020比较，WHERE子句只过滤出此年份的订单。

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

    SELECT order_num
    FROM Orders
    WHERE EXTRACT(year FROM order_date) = 2020;

在这个例子中，EXTRACT()函数用来提取日期的成分，year表示提取哪个部分，返回值再与2020进行比较。

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

    SELECT order_num
    FROM Orders
    WHERE order_date BETWEEN to_date('2020-01-01', 'yyyy-mm-dd')
      AND to_date('2020-12-31', 'yyyy-mm-dd');

在此例子中，Oracle的to_date()函数用来将两个字符串转换为日期。一个包含2020年1月1日，另一个包含2020年12月31日。BETWEEN操作符用来找出两个日期之间的所有订单。值得注意的是，相同的代码在SQL Server中不起作用，因为它不支持to_date()函数。但是，如果用DATEPART()替换to_date()，当然可以使用这种类型的语句。

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

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

在SQLite中有个小技巧：

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

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

DBMS提供的功能远不止简单的日期成分提取。大多数DBMS具有比较日期、执行日期的运算、选择日期格式等的函数。但是，可以看到，不同DBMS的日期-时间处理函数可能不同。关于你的DBMS具体支持的日期-时间处理函数，请参阅相应的文档。

7.2.2、数值处理函数

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

具有讽刺意味的是，在主要DBMS的函数中，数值函数是最一致、最统一的函数。下表列出一些常用的数值处理函数：

关于具体DBMS所支持的算术处理函数，请参阅相应的文档。

8、汇总数据

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

8.1、聚集函数

我们经常需要汇总数据而不用把它们实际检索出来，为此SQL提供了专门的函数。使用这些函数，SQL查询可用于检索数据，以便分析和报表生成。这种类型的检索例子有：

• 确定表中行数（或者满足某个条件或包含某个特定值的行数）；
• 获得表中某些行的和；
• 找出表列（或所有行或某些特定的行）的最大值、最小值、平均值。

上述例子都需要汇总出表中的数据，而不需要查出数据本身。因此，返回实际表数据纯属浪费时间和处理资源（更不用说带宽了）。再说一遍，我们实际想要的是汇总信息。

为方便这种类型的检索，SQL给出了5个聚集函数，见表下表。这些函数能进行上述检索。与前一章介绍的数据处理函数不同，SQL的聚集函数在各种主要SQL实现中得到了相当一致的支持。

8.1.1、AVG()函数

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

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

    SELECT AVG(prod_price) AS avg_price
    FROM Products;

此SELECT语句返回值avg_price，它包含Products表中所有产品的平均价格。如第7课所述，avg_price是一个别名。

AVG()也可以用来确定特定列或行的平均值。下面的例子返回特定供应商所提供产品的平均价格：

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

这条SELECT语句与前一条的不同之处在于，它包含了WHERE子句。此WHERE子句仅过滤出vend_id为DLL01的产品，因此avg_price中返回的值只是该供应商产品的平均值。

8.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;

这条SELECT语句使用COUNT(cust_email)对cust_email列中有值的行进行计数。在此例子中，cust_email的计数为3（表示5个顾客中只有3个顾客有电子邮件地址）。

8.1.3、MAX()函数

    SELECT MAX(prod_price) AS max_price
    FROM Products;

这里，MAX()返回Products表中最贵物品的价格。

8.1.4、MIN()函数

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

    SELECT MIN(prod_price) AS min_price
    FROM Products;

8.1.5、SUM()函数

SUM()用来返回指定列值的和（总计）。下面举一个例子，OrderItems包含订单中实际的物品，每个物品有相应的数量。可如下检索所订购物品的总数（所有quantity值之和）：

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

函数SUM(quantity)返回订单中所有物品数量之和，WHERE子句保证只统计某个物品订单中的物品。

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

函数SUM(item_price＊quantity)返回订单中所有物品价钱之和，WHERE子句同样保证只统计某个物品订单中的物品。

8.2、聚集不同值

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

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

下面的例子使用AVG()函数返回特定供应商提供的产品的平均价格。它与上面的SELECT语句相同，但使用了DISTINCT参数，因此平均值只考虑各个不同的价格：

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

可以看到，在使用了DISTINCT后，此例子中的avg_price比较高，因为有多个物品具有相同的较低价格。排除它们提升了平均价格。

8.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;

这里用单条SELECT语句执行了4个聚集计算，返回4个值（Products表中物品的数目，产品价格的最高值、最低值以及平均值）。

8.4、小结

聚集函数用来汇总数据。SQL支持5个聚集函数，可以用多种方法使用它们，返回所需的结果。这些函数很高效，它们返回结果一般比你在自己的客户端应用程序中计算要快得多。

9、分组数据

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

9.1、数据分组

使用SQL聚集函数可以汇总数据。这样，我们就能够对行进行计数，计算和与平均数，不检索所有数据就获得最大值和最小值。

目前为止的所有计算都是在表的所有数据或匹配特定的WHERE子句的数据上进行的。比如下面的例子返回供应商DLL01提供的产品数目：

    SELECT COUNT(＊) AS num_prods
    FROM Products
    WHERE vend_id = 'DLL01';

如果要返回每个供应商提供的产品数目，该怎么办？或者返回只提供一项产品的供应商的产品，或者返回提供10个以上产品的供应商的产品，怎么办？

这就是分组大显身手的时候了。使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算。

9.2、创建分组

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

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

上面的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子句指示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子句之前。
• 

9.3、过滤分组

除了能用GROUP BY分组数据外，SQL还允许过滤分组，规定包括哪些分组，排除哪些分组。例如，你可能想要列出至少有两个订单的所有顾客。为此，必须基于完整的分组而不是个别的行进行过滤。在这个例子中WHERE不能完成任务，因为WHERE过滤指定的是行而不是分组。事实上，WHERE没有分组的概念。

那么，不使用WHERE使用什么呢？SQL为此提供了另一个子句，就是HAVING子句。HAVING非常类似于WHERE。事实上，目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是，WHERE过滤行，而HAVING过滤分组。

那么，怎么过滤分组呢？请看以下的例子：

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

这条SELECT语句的前三行类似于上面的语句。最后一行增加了HAVING子句，它过滤COUNT(＊) >= 2（两个以上订单）的那些分组。

可以看到，WHERE子句在这里不起作用，因为过滤是基于分组聚集值，而不是特定行的值。

那么，有没有在一条语句中同时使用WHERE和HAVING子句的需要呢？事实上，确实有。假如想进一步过滤上面的语句，使它返回过去12个月内具有两个以上订单的顾客。为此，可增加一条WHERE子句，过滤出过去12个月内下过的订单，然后再增加HAVING子句过滤出具有两个以上订单的分组。

为了更好地理解，来看下面的例子，它列出具有两个以上产品且其价格大于等于4的供应商：

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

这条语句中，第一行是使用了聚集函数的基本SELECT语句，很像前面的例子。WHERE子句过滤所有prod_price至少为4的行，然后按vend_id分组数据，HAVING子句过滤计数为2或2以上的分组。如果没有WHERE子句，就会多检索出一行（供应商DLL01，销售4个产品，价格都在4以下）：

    SELECT vend_id, COUNT(＊) AS num_prods
    FROM Products
    GROUP BY vend_id
    HAVING COUNT(＊) >= 2;

9.4、分组和排序

GROUP BY和ORDER BY经常完成相同的工作，但它们非常不同，理解这一点很重要。下表汇总了它们之间的差别。

上表中列出的第一项差别极为重要。我们经常发现，用GROUP BY分组的数据确实是以分组顺序输出的。但并不总是这样，这不是SQL规范所要求的。此外，即使特定的DBMS总是按给出的GROUP BY子句排序数据，用户也可能会要求以不同的顺序排序。就因为你以某种方式分组数据（获得特定的分组聚集值），并不表示你需要以相同的方式排序输出。应该提供明确的ORDER BY子句，即使其效果等同于GROUP BY子句。

为说明GROUP BY和ORDER BY的使用方法，来看一个例子。下面的SELECT语句类似于前面那些例子。它检索包含三个或更多物品的订单号和订购物品的数目：

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

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

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

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

9.5、SELECT子句顺序

下面回顾一下SELECT语句中子句的顺序。下表以在SELECT语句中使用时必须遵循的次序，列出迄今为止所学过的子句。

10、使用子查询

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

10.1、子查询

SELECT语句是SQL的查询。我们迄今为止所看到的所有SELECT语句都是简单查询，即从单个数据库表中检索数据的单条语句。

SQL还允许创建子查询（subquery），即嵌套在其他查询中的查询。为什么要这样做呢？理解这个概念的最好方法是考察几个例子。

10.2、利用子查询进行过滤

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

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

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

上述每个步骤都可以单独作为一个查询来执行。可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句。

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

    SELECT order_num
    FROM OrderItems
    WHERE prod_id = 'RGAN01';

现在，我们知道了哪个订单包含要检索的物品，下一步查询与订单20007和20008相关的顾客ID。利用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');

在SELECT语句中，子查询总是从内向外处理。在处理上面的SELECT语句时，DBMS实际上执行了两个操作"
首先，它执行下面的查询：

    SELECT order_num FROM orderitems WHERE prod_id='RGAN01'

此查询返回两个订单号：20007和20008。然后，这两个值以IN操作符要求的逗号分隔的格式传递给外部查询的WHERE子句。外部查询变成：

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

可以看到，输出是正确的，与前面硬编码WHERE子句所返回的值相同。

现在得到了订购物品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 Orders
                      WHERE order_num IN (SELECT order_num
                                            FROM OrderItems
                                            WHERE prod_id = 'RGAN01'));

分析：
为了执行上述SELECT语句，DBMS实际上必须执行三条SELECT语句。最里边的子查询返回订单号列表，此列表用于其外面的子查询的WHERE子句。外面的子查询返回顾客ID列表，此顾客ID列表用于最外层查询的WHERE子句。最外层查询返回所需的数据。

可见，在WHERE子句中使用子查询能够编写出功能很强且很灵活的SQL语句。对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。

10.3、作为计算字段使用子查询

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

正如前两课所述，可以使用SELECT COUNT(＊)对表中的行进行计数，并且通过提供一条WHERE子句来过滤某个特定的顾客ID，仅对该顾客的订单进行计数。例如，下面的代码对顾客1000000001的订单进行计数：

    SELECT COUNT(＊) AS orders
    FROM Orders
    WHERE cust_id = 1000000001;

要对每个顾客执行COUNT(＊)，应该将它作为一个子查询。请看下面的代码：

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

这条SELECT语句对Customers表中每个顾客返回三列：cust_name、cust_state和orders。orders是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个顾客执行一次。在此例中，该子查询执行了5次，因为检索出了5个顾客。

子查询中的WHERE子句与前面使用的WHERE子句稍有不同，因为它使用了完全限定列名，而不只是列名（cust_id）。它指定表名和列名（Orders.cust_id和Customers.cust_id）。下面的WHERE子句告诉SQL，比较Orders表中的cust_id和当前正从Customers表中检索的cust_id：

    WHERE Orders.cust_id = Customers.cust_id

用一个句点分隔表名和列名，在有可能混淆列名时必须使用这种语法。在这个例子中，有两个cust_id列：一个在Customers中，另一个在Orders中。如果不采用完全限定列名，DBMS会认为要对Orders表中的cust_id自身进行比较。因为 SELECT COUNT(＊) FROM Orders WHERE cust_id = cust_id总是返回Orders表中订单的总数，而这个结果不是我们想要的：

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

虽然子查询在构造这种SELECT语句时极有用，但必须注意限制有歧义的列。

子查询常用于WHERE子句的IN操作符中，以及用来填充计算列。

11、联结表

SQL最强大的功能之一就是能在数据查询的执行中联结（join）表。联结是利用SQL的SELECT能执行的最重要的操作，很好地理解联结及其语法是学习SQL的极为重要的部分。

在能够有效地使用联结前，必须了解关系表以及关系数据库设计的一些基础知识。

11.1、联结

11.1、关系表

理解关系表，最好是来看个例子。

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

现在有同一供应商生产的多种物品，那么在何处存储供应商名、地址、联系方法等供应商信息呢？将这些数据与产品信息分开存储的理由是： 同一供应商生产的每个产品，其供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间； 如果供应商信息发生变化，例如供应商迁址或电话号码变动，只需修改一次即可； 如果有重复数据（即每种产品都存储供应商信息），则很难保证每次输入该数据的方式都相同。不一致的数据在报表中就很难利用。

关键是，相同的数据出现多次决不是一件好事，这是关系数据库设计的基础。关系表的设计就是要把信息分解成多个表，一类数据一个表。各表通过某些共同的值互相关联（所以才叫关系数据库）。

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

Products表只存储产品信息，除了存储供应商ID（Vendors表的主键）外，它不存储其他有关供应商的信息。Vendors表的主键将Vendors表与Products表关联，利用供应商ID能从Vendors表中找出相应供应商的详细信息。这样做的好处是：
供应商信息不重复，不会浪费时间和空间； 如果供应商信息变动，可以只更新Vendors表中的单个记录，相关表中的数据不用改动；由于数据不重复，数据显然是一致的，使得处理数据和生成报表更简单。总之，关系数据可以有效地存储，方便地处理。因此，关系数据库的可伸缩性远比非关系数据库要好。

11.1.2、为什么使用联结

如前所述，将数据分解为多个表能更有效地存储，更方便地处理，并且可伸缩性更好。但这些好处是有代价的。

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

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

11.2、创建联结

创建联结非常简单，指定要联结的所有表以及关联它们的方式即可。请看下面的例子：

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

分析：我们来看这段代码。SELECT语句与前面所有语句一样指定要检索的列。这里最大的差别是所指定的两列（prod_name和prod_price）在一个表中，而第一列（vend_name）在另一个表中。

现在来看FROM子句。与以前的SELECT语句不一样，这条语句的FROM子句列出了两个表：Vendors和Products。它们就是这条SELECT语句联结的两个表的名字。这两个表用WHERE子句正确地联结，WHERE子句指示DBMS将Vendors表中的vend_id与Products表中的vend_id匹配起来。

可以看到，要匹配的两列指定为Vendors.vend_id和Products.vend_id。这里需要这种完全限定列名，如果只给出vend_id,DBMS就不知道指的是哪一个（每个表中有一个）。从前面的输出可以看到，一条SELECT语句返回了两个不同表中的数据。

11.2.1、WHERE子句的重要性

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

理解这一点，请看下面的SELECT语句及其输出：

    SELECT vend_name, prod_name, prod_price
    FROM Vendors, Products;

从上面的输出可以看到，相应的笛卡儿积不是我们想要的。这里返回的数据用每个供应商匹配了每个产品，包括了供应商不正确的产品（即使供应商根本就没有产品）。

11.2.2、内联结

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

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

分析：此语句中的SELECT与前面的SELECT语句相同，但FROM子句不同。这里，两个表之间的关系是以INNER JOIN指定的部分FROM子句。在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给WHERE的相同。

至于选用哪种语法，请参阅具体的DBMS文档。

11.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;

分析：这个例子显示订单20007中的物品。订单物品存储在OrderItems表中。每个产品按其产品ID存储，它引用Products表中的产品。这些产品通过供应商ID联结到Vendors表中相应的供应商，供应商ID存储在每个产品的记录中。这里的FROM子句列出三个表，WHERE子句定义这两个联结条件，而第三个联结条件用来过滤出订单20007中的物品。

如下的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';

这个查询中的返回数据需要使用3个表。但在这里，我们没有在嵌套子查询中使用它们，而是使用了两个联结来连接表。这里有三个WHERE子句条件。前两个关联联结中的表，后一个过滤产品RGAN01的数据。

12、创建高级联结

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

12.1、使用表别名

给列起别名的语法如下：

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

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

• 缩短SQL语句；
• 允许在一条SELECT语句中多次使用相同的表。

请看下面的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';

分析：可以看到，FROM子句中的三个表全都有别名。Customers AS C使用C作为Customers的别名，如此等等。这样，就可以使用省略的C而不用全名Customers。在这个例子中，表别名只用于WHERE子句。其实它不仅能用于WHERE子句，还可以用于SELECT的列表、ORDER BY子句以及其他语句部分。

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

12.2、使用不同类型的联结

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

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

分析：此查询中需要的两个表实际上是相同的表，因此Customers表在FROM子句中出现了两次。虽然这是完全合法的，但对Customers的引用具有歧义性，因为DBMS不知道你引用的是哪个Customers表。

解决此问题，需要使用表别名。Customers第一次出现用了别名c1，第二次出现用了别名c2。现在可以将这些别名用作表名。例如，SELECT语句使用c1前缀明确给出所需列的全名。如果不这样，DBMS将返回错误，因为名为cust_id、cust_name、cust_contact的列各有两个。DBMS不知道想要的是哪一列（即使它们其实是同一列）。WHERE首先联结两个表，然后按第二个表中的cust_contact过滤数据，返回所需的数据。

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

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

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

12.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表）中选择所有行。为了从右边的表中选择所有行，需要使用RIGHT OUTER JOIN，如下例所示：


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

12.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;

分析：这条SELECT语句使用INNER JOIN将Customers和Orders表互相关联。GROUP BY子句按顾客分组数据，因此，函数调用COUNT(Orders.order_num)对每个顾客的订单计数，将它作为num_ord返回。

聚集函数也可以方便地与其他联结一起使用。请看下面的例子：

    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;

分析：这个例子使用左外部联结来包含所有顾客，甚至包含那些没有任何订单的顾客。结果中也包含了顾客1000000002，他有0个订单，这和使用INNER JOIN时不同。

12.4、使用联结和联结条件

汇总一下联结及其使用的要点：

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

13、组合查询

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

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

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

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

13.1、创建组合查询

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

13.1.1、使用UNION

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

举个例子，假如需要Illinois、Indiana和Michigan等美国几个州的所有顾客的报表，还想包括不管位于哪个州的所有的Fun4All。当然可以利用WHERE子句来完成此工作，不过这次我们使用UNION。

如上所述，创建UNION涉及编写多条SELECT语句。首先来看单条语句：

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

    SELECT cust_name, cust_contact, cust_email
    FROM Customers
    WHERE cust_name = 'Fun4All';

分析：第一条SELECT把Illinois、Indiana、Michigan等州的缩写传递给IN子句，检索出这些州的所有行。第二条SELECT利用简单的相等测试找出所有Fun4All。你会发现有一条记录出现在两次结果里，因为它满足两次的条件。

组合这两条语句，可以如下进行：

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

分析：这条语句由前面的两条SELECT语句组成，之间用UNION关键字分隔。UNION指示DBMS执行这两条SELECT语句，并把输出组合成一个查询结果集。

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

13.1.2、UNION规则

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

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

13.1.3、 包含或取消重复的行

UNION从查询结果集中自动去除了重复的行；换句话说，它的行为与一条SELECT语句中使用多个WHERE子句条件一样。因为Indiana州有一个Fun4All单位，所以两条SELECT语句都返回该行。使用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不取消重复的行。因此，这里返回5行，其中有一行出现两次。

13.1.4、 对组合查询结果排序

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

下面的例子对前面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'
    ORDER BY cust_name, cust_contact;

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

利用UNION，可以把多条查询的结果作为一条组合查询返回，不管结果中有无重复。使用UNION可极大地简化复杂的WHERE子句，简化从多个表中检索数据的工作。

14、插入数据

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

14.1、数据插入

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

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

14.1.1、 插入完整的行

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

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

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

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

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

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

这个例子与前一个INSERT语句的工作完全相同，但在表名后的括号里明确给出了列名。在插入行时，DBMS将用VALUES列表中的相应值填入列表中的对应项。VALUES中的第一个值对应于第一个指定列名，第二个值对应于第二个列名，如此等等。

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

14.1.1、 插入部分行

正如所述，使用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');

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

14.1.2、插入检索出的数据

INSERT一般用来给表插入具有指定列值的行。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。

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

14.2、从一个表复制到另一个表

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

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

下面的例子说明如何使用CREATE SELECT：

    CREATE TABLE CustCopy AS SELECT ＊ FROM Customers;

若是使用SQL Server，可以这么写：

    SELECT ＊ INTO CustCopy FROM Customers;

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

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

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

15、更新和删除数据

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

15.1、 更新数据

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

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

下面分别介绍。

使用UPDATE语句非常容易，甚至可以说太容易了。基本的UPDATE语句由三部分组成，分别是：

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

举一个简单例子。客户1000000005现在有了电子邮件地址，因此他的记录需要更新，语句如下：

    UPDATE Customers
    SET cust_email = '[email protected]'
    WHERE cust_id = 1000000005;

UPDATE语句总是以要更新的表名开始。在这个例子中，要更新的表名为Customers。SET命令用来将新值赋给被更新的列。在这里，SET子句设置cust_email列为指定的值。

UPDATE语句以WHERE子句结束，它告诉DBMS更新哪一行。没有WHERE子句，DBMS将会用这个电子邮件地址更新Customers表中的所有行，这不是我们希望的。

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

    UPDATE Customers
    SET cust_contact = 'Sam Roberts',
        cust_email = '[email protected]'
    WHERE cust_id = 1000000006;

在更新多个列时，只需要使用一条SET命令，每个“列=值”对之间用逗号分隔（最后一列之后不用逗号）。在此例子中，更新顾客1000000006的cust_contact和cust_email列。

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

    UPDATE Customers
    SET cust_email = NULL
    WHERE cust_id = 1000000005;

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

15.2、 删除数据

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

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

下面分别介绍。

前面说过，UPDATE非常容易使用，而DELETE更容易使用。

下面的语句从Customers表中删除一行：

    DELETE FROM Customers
    WHERE cust_id = 1000000006;

这条语句很容易理解。DELETE FROM要求指定从中删除数据的表名，WHERE子句过滤要删除的行。在这个例子中，只删除顾客1000000006。如果省略WHERE子句，它将删除表中每个顾客。

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

15.3、 更新和删除的指导原则

UPDATE和DELETE语句都有WHERE子句，这样做的理由很充分。如果省略了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，否则你会发现自己更新或删除了错误的数据。

16、创建和操纵表

讲授创建、更改和删除表的基本知识。

16.1、创建表

SQL不仅用于表数据操纵，而且还用来执行数据库和表的所有操作，包括表本身的创建和处理。

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

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

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

不会介绍创建表时可以使用的所有选项，那超出了本课的范围，我只给出一些基本选项。详细的信息说明，请参阅具体的DBMS文档。

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

从上面的例子可以看到，表名紧跟CREATE TABLE关键字。实际的表定义（所有列）括在圆括号之中，各列之间用逗号分隔。这个表由5列组成。每列的定义以列名（它在表中必须是唯一的）开始，后跟列的数据类型（关于数据类型的解释，请参阅第1课。此外，附录C列出了常见的数据类型及兼容性）。整条语句以圆括号后的分号结束。

前面提到，不同DBMS的CREATE TABLE的语法有所不同，这个简单脚本也说明了这一点。这条语句在绝大多数DBMS中有效，但对于DB2，必须从最后一列中去掉NULL。这就是对于不同的DBMS，要编写不同的表创建脚本的原因。

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

这条语句创建本书中所用的Orders表。Orders包含三列：订单号、订单日期和顾客ID。这三列都需要，因此每一列的定义都含有关键字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)
    );

这条语句创建使用的Vendors表。供应商ID和供应商名字列是必需的，因此指定为NOT NULL。其余五列全都允许NULL值，所以不指定NOT NULL。NULL为默认设置，如果不指定NOT NULL，就认为指定的是NULL。

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

这条语句创建OrderItems表，包含构成订单的各项（订单本身存储在Orders表中）。quantity列为订单中每个物品的数量。在这个例子中，这一列的描述增加了DEFAULT 1，指示DBMS，如果不给出数量则使用数量1。

默认值经常用于日期或时间戳列。例如，通过指定引用系统日期的函数或变量，将系统日期用作默认日期。MySQL用户指定DEFAULT CURRENT_DATE(),Oracle用户指定DEFAULT SYSDATE，而SQL Server用户指定DEFAULT GETDATE()。遗憾的是，这条获得系统日期的命令在不同的DBMS中大多是不同的。下表列出了这条命令在某些DBMS中的语法。这里若未列出某个DBMS，请参阅相应的文档。

16.2、 更新表

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

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

可以看出，对已有表做更改既复杂又不统一。对表的结构能进行何种更改，请参阅具体的DBMS文档。

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

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

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

    ALTER TABLE Vendors
    ADD vend_phone CHAR(20);

这条语句给Vendors表增加一个名为vend_phone的列，其数据类型为CHAR。

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

    ALTER TABLE Vendors
    DROP COLUMN vend_phone;

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

16.3、 删除表

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

    DROP TABLE CustCopy;

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

16.3、 重命名表

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

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

17、使用视图

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

17.1、视图

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

理解视图的最好方法是看例子。用下面的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';

此查询用来检索订购了某种产品的顾客。任何需要这个数据的人都必须理解相关表的结构，知道如何创建查询和对表进行联结。检索其他产品（或多个产品）的相同数据，必须修改最后的WHERE子句。

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

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

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

17.1.1、为什么使用视图

我们已经看到了视图应用的一个例子。下面是视图的一些常见应用。

• 重用SQL语句。
• 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节。
• 使用表的一部分而不是整个表。
• 保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。
• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。创建视图之后，可以用与表基本相同的方式使用它们。可以对视图执行SELECT操作，过滤和排序数据，将视图联结到其他视图或表，甚至添加和更新数据（添加和更新数据存在某些限制，关于这个内容稍后做介绍）。

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

17.1.2、视图的规则和限制

创建视图前，应该知道它的一些限制。不过，这些限制随不同的DBMS而不同，因此在创建视图时应该查看具体的DBMS文档。

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

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

17.2、创建视图

理解了什么是视图以及管理它们的规则和约束后，我们来创建视图。

视图用CREATE VIEW语句来创建。与CREATE TABLE一样，CREATE VIEW只能用于创建不存在的视图。

17.2.1、利用视图简化复杂的联结

一个最常见的视图应用是隐藏复杂的SQL，这通常涉及联结。请看下面的例子：

    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子句中，以便正确过滤数据。

可以看出，视图极大地简化了复杂SQL语句的使用。利用视图，可一次性编写基础的SQL，然后根据需要多次使用。

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

如前所述，视图的另一常见用途是重新格式化检索出的数据。下面的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;

现在，假设经常需要这个格式的结果。我们不必在每次需要时执行这种拼接，而是创建一个视图，使用它即可。把此语句转换为视图，可按如下进行：

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

这条语句使用与以前SELECT语句相同的查询创建视图。要检索数据，创建所有的邮件标签，可如下进行：

    SELECT ＊ FROM VendorLocations;

17.2.3、用视图过滤不想要的数据

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

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

分析：显然，在将电子邮件发送到邮件列表时，需要排除没有电子邮件地址的用户。这里的WHERE子句过滤了cust_email列中具有NULL值的那些行，使它们不被检索出来。

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

    SELECT ＊
    FROM CustomerEMailList;

17.2.4、使用视图与计算字段

在简化计算字段的使用上，视图也特别有用。下面是一条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;

可以看到，视图非常容易创建，而且很好使用。正确使用，视图可极大地简化复杂数据的处理。

17.3、小结

视图为虚拟的表。它们包含的不是数据而是根据需要检索数据的查询。视图提供了一种封装SELECT语句的层次，可用来简化数据处理，重新格式化或保护基础数据。

18、使用存储过程

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

18.1、存储过程

迄今为止，我们使用的大多数SQL语句都是针对一个或多个表的单条语句。并非所有操作都这么简单，经常会有一些复杂的操作需要多条语句才能完成，例如以下的情形。

• 为了处理订单，必须核对以保证库存中有相应的物品。
• 如果物品有库存，需要预定，不再出售给别的人，并且减少物品数据以反映正确的库存量。
• 库存中没有的物品需要订购，这需要与供应商进行某种交互。
• 关于哪些物品入库（并且可以立即发货）和哪些物品退订，需要通知相应的顾客。

执行这个处理需要针对许多表的多条SQL语句。此外，需要执行的具体SQL语句及其次序也不是固定的，它们可能会根据物品是否在库存中而变化。

那么，怎样编写代码呢？可以单独编写每条SQL语句，并根据结果有条件地执行其他语句。在每次需要这个处理时（以及每个需要它的应用中），都必须做这些工作。

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

18.2、为什么要使用存储过程

我们知道了什么是存储过程，那么为什么要使用它们呢？理由很多，下面列出一些主要的。

• 通过把处理封装在一个易用的单元中，可以简化复杂的操作（如前面例子所述）。
• 由于不要求反复建立一系列处理步骤，因而保证了数据的一致性。如果所有开发人员和应用程序都使用同一存储过程，则所使用的代码都是相同的。
• 上一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
• 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，那么只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
• 上一点的延伸就是安全性。通过存储过程限制对基础数据的访问，减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。
• 因为存储过程通常以编译过的形式存储，所以DBMS处理命令所需的工作量少，提高了性能。
• 存在一些只能用在单个请求中的SQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。换句话说，使用存储过程有三个主要的好处，即简单、安全、高性能。显然，它们都很重要。不过，在将SQL代码转换为存储过程前，也必须知道它的一些缺陷。
• 不同DBMS中的存储过程语法有所不同。事实上，编写真正的可移植存储过程几乎是不可能的。不过，存储过程的自我调用（名字以及数据如何传递）可以相对保持可移植。因此，如果需要移植到别的DBMS，至少客户端应用代码不需要变动。
• 一般来说，编写存储过程比编写基本SQL语句复杂，需要更高的技能，更丰富的经验。因此，许多数据库管理员把限制存储过程的创建作为安全措施（主要受上一条缺陷的影响）。

尽管有这些缺陷，存储过程还是非常有用的，并且应该使用。事实上，多数DBMS都带有用于管理数据库和表的各种存储过程。更多信息请参阅具体的DBMS文档。

18.3、执行存储过程

存储过程的执行远比编写要频繁得多，因此我们先介绍存储过程的执行。执行存储过程的SQL语句很简单，即EXECUTE。EXECUTE接受存储过程名和需要传递给它的任何参数。请看下面的例子（你无法运行这个例子，因为AddNewProduct这个存储过程还不存在）：

    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的过程自动化（而不是依赖于最终用户的输入）。这也是这个例子使用存储过程的原因。

对于具体的DBMS，可能包括以下的执行选择：

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

18.4、创建存储过程

正如所述，存储过程的编写很重要。为了获得感性认识，我们来看一个简单的存储过程例子，它对邮件发送清单中具有邮件地址的顾客进行计数。

    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语句显示返回的值。

19、管理事务处理

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

19.1、事务处理

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

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

前面使用的Orders表就是一个很好的例子。订单存储在Orders和OrderItems两个表中：Orders存储实际的订单，OrderItems存储订购的各项物品。这两个表使用称为主键的唯一ID互相关联，又与包含客户和产品信息的其他表相关联。

给系统添加订单的过程如下：
(1) 检查数据库中是否存在相应的顾客，如果不存在，添加他；
(2) 检索顾客的ID；
(3) 在Orders表添加一行，它与顾客ID相关联；
(4) 检索Orders表中赋予的新订单ID；
(5) 为订购的每个物品在OrderItems表中添加一行，通过检索出来的ID把它与Orders表关联（并且通过产品ID与Products表关联）。

现在假设由于某种数据库故障（如超出磁盘空间、安全限制、表锁等），这个过程无法完成。数据库中的数据会出现什么情况？

如果故障发生在添加顾客之后，添加Orders表之前，则不会有什么问题。某些顾客没有订单是完全合法的。重新执行此过程时，所插入的顾客记录将被检索和使用。可以有效地从出故障的地方开始执行此过程。

但是，如果故障发生在插入Orders行之后，添加OrderItems行之前，怎么办？现在，数据库中有一个空订单。

更糟的是，如果系统在添加OrderItems行之时出现故障，怎么办？结果是数据库中存在不完整的订单，而你还不知道。

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

在使用事务处理时，有几个反复出现的关键词。下面是关于事务处理需要知道的几个术语：

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

19.2、控制事务处理

管理事务的关键在于将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采用上述语法的变体。你会发现，多数实现没有明确标识事务处理在何处结束。事务一直存在，直到被中断。通常，COMMIT用于保存更改，ROLLBACK用于撤销，详述如下。

19.2.1、使用ROLLBACK

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

    DELETE FROM Orders;
    ROLLBACK;

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

19.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

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

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

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

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

20、使用游标

什么是游标，如何使用游标。

20.1、游标

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

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

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

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

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

20.2、使用游标

使用游标涉及几个明确的步骤。

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

声明游标后，可根据需要频繁地打开和关闭游标。在游标打开时，可根据需要频繁地执行取操作。

20.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值）的所有顾客的游标。

定义游标之后，就可以打开它了。

20.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在取不出更多的行时终止处理（退出循环）。这个例子也不进行具体的处理，实际代码中，应该用具体的处理代码替换其中的．…。

20.2.3、关闭游标

如前面几个例子所述，游标在使用完毕时需要关闭。此外，SQL Server等DBMS要求明确释放游标所占用的资源。下面是DB2、Oracle和PostgreSQL的语法。

    CLOSE CustCursor

下面是Microsoft SQL Server的版本。

    CLOSE CustCursor
    DEALLOCATE CURSOR CustCursor

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

21、高级SQL特性

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

21.1、约束

SQL已经改进过多个版本，成为非常完善和强大的语言。许多强有力的特性给用户提供了高级的数据处理技术，如约束。

关联表和引用完整性已经在前面讨论过几次。正如所述，关系数据库存储分解为多个表的数据，每个表存储相应的数据。利用键来建立从一个表到另一个表的引用[由此产生了术语引用完整性（referential integrity）]。

正确地进行关系数据库设计，需要一种方法保证只在表中插入合法数据。例如，如果Orders表存储订单信息，OrderItems表存储订单详细内容，应该保证OrderItems中引用的任何订单ID都存在于Orders中。类似地，在Orders表中引用的任意顾客必须存在于Customers表中。

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

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

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

21.1.1、主键

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

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

• 任意两行的主键值都不相同。
• 每行都具有一个主键值（即列中不允许NULL值）。
• 包含主键值的列从不修改或更新。（大多数DBMS不允许这么做，但如果你使用的DBMS允许这样做，好吧，千万别！）
• 主键值不能重用。如果从表中删除某一行，其主键值不分配给新行。

一种定义主键的方法是创建它，如下所示：

    CREATE TABLE Vendors
    (
        vend_id           CHAR(10)        NOT NULL PRIMARY KEY,
        vend_name        CHAR(50)        NOT NULL,
        vend_address     CHAR(50)        NULL,
        vend_city        CHAR(50)        NULL,
        vend_state       CHAR(5)          NULL,
        vend_zip          CHAR(10)        NULL,
        vend_country     CHAR(50)        NULL
    );

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

    ALTER TABLE Vendors
    ADD CONSTRAINT PRIMARY KEY (vend_id);

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

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

除帮助保证引用完整性外，外键还有另一个重要作用。在定义外键后，DBMS不允许删除在另一个表中具有关联行的行。例如，不能删除关联订单的顾客。删除该顾客的唯一方法是首先删除相关的订单（这表示还要删除相关的订单项）。由于需要一系列的删除，因而利用外键可以防止意外删除数据。
有的DBMS支持称为级联删除（cascading delete）的特性。如果启用，该特性在从一个表中删除行时删除所有相关的数据。例如，如果启用级联删除并且从Customers表中删除某个顾客，则任何关联的订单行也会被自动删除。

21.1.3、唯一约束

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

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

employees表是一个使用约束的例子。每个雇员都有唯一的社会安全号，但我们并不想用它作主键，因为它太长（而且我们也不想使该信息容易利用）。因此，每个雇员除了其社会安全号外还有唯一的雇员ID（主键）。
雇员ID是主键，可以确定它是唯一的。你可能还想使DBMS保证每个社会安全号也是唯一的（保证输入错误不会导致使用他人号码）。可以通过在社会安全号列上定义UNIQUE约束做到。

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

21.1.4、检查约束

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

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

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

下面的例子对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]');

21.2、索引

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

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

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

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

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

但是，搜索其他列中的值通常效率不高。例如，如果想搜索住在某个州的客户，怎么办？因为表数据并未按州排序，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用来指定被索引的表，而索引中包含的列（此例中仅有一列）在表名后的圆括号中给出。

21.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;

21.4、数据库安全

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

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

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

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

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