SQL必知必会1 查询部分 P1-P129

第1课 了解SQL

这一课程介绍SQL究竟是什么，它能做什么事情

1.1 数据库基础

你正在读这本SQL图书，表明你需要以某种方式与数据库打交道。SQL正是用来实现这一任务的语言，因此在学习SQL之前，你应该对数据库及数据库技术的某些基本概念有所了解

你可能还没有意识到，其实自己一直在使用数据库。每当你从电子邮件地址簿里查找名字时，就是在使用数据库。你在网站上进行搜索，也是在使用数据库。你在工作中登录网络，也需要依靠数据库验证用户名和密码。即使是在自动取款机上使用ATM卡，也要利用数据库进行密码验证和查询余额

虽然我们一直都在使用数据库，但对究竟什么是数据库并不十分清楚。更何况人们可能会使用相同的数据库术语表示不同的事务，进一步加剧了这种混乱。因此，我们首先给出一些最重要的数据库术语，并加以说明

提示：基本概念回顾
后面是一些基本数据库概念的简要介绍。如果你已经具有一定的数据库经验，可以借此复习巩固一下；如果你刚开始接触数据库，可以由此了解必需的基本知识。理解数据库概念是掌握SQL的重要前提，如果有必要，你或许话应该参阅其他一些有关数据库基础知识的书籍

1.1.1 数据库

数据库这个术语的用法很多，但就本书而言（从SQL的角度来看），数据库是一个以某种有组织的方式存储的数据集合。最简单的办法是将数据库想象为一个文件柜。这个文件柜是一个存放数据的物理位置，不管数据是什么，也不管数据是如何组织的

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

注意：误用导致混淆
人们通常用数据库这个术语来代表他们使用的数据库软件，这是不正确的，也因此产生了许多混淆。确切地说，数据库软件应称为数据库管理系统（DBMS）。数据库是通过DBMS创建和操纵的容器，而具体它究竟是什么，形式如何，各种数据库都不一样

1.1.2 表

你往文件柜里放资料时，并不是随便将它们扔进某个抽屉就完事了，而是在文件柜中创建文件，然后将相关的资料放入特定的文件中

在数据库领域中，这种文件成为表。表是一种结构化的文件，可用来存储某种特定类型的数据。表可以保存顾客清单、产品目录，或者其他信息清单

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

这里的关键一点在于，存储在表中的数据是同一种类型的数据或者清单。决不应该将顾客的清单与订单的清单存储在同一个数据表中，否则以后的检索和访问会很困难。应该创建两个表，每个清单一个表

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

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

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

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

1.1.3

表由列组成。列存储表中某部分的信息

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

理解列的最好办法是将数据库表想象为一个网格，就像个电子表格那样。网格中每一列存储着某种特定的信息。例如，在顾客表中，一列存储顾客编号，另一列存储顾客姓名，而地址、城市、州以及邮政编码全都存储在各自的列中

提示：分解数据
正确地将数据分解为多个列极为重要。例如，城市、州、邮政编码应该总是彼此独立的列。通过分解这些数据，才有可能利用特定的列对数据进行分类和过滤（如找出特定州或特定城市的所有顾客）。如果城市和州组合在一个列中，则按州进行分类或过滤就会很困难
你可以根据自己的具体需求来决定把数据分解到何种程度。例如，一般可以把门牌号和街道名一起存储在地址里。这没有问题，除非你哪天想用街道名来排序，这是，最好将门牌号和街道名分开

数据库中每个列都有相应的数据类型。数据类型（datatype）定义了列可以存储哪些数据种类。例如，如果列中存储的是数字（或许是订单中的物品数），则相应的数据类型应该为数值类型。如果列中存储的是日期、文本、注释、金额等，则应该规定好恰当的数据类型

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

数据类型限定了可存储在列中的数据种类（例如，防止在数值字段中录入字符值）。数据类型还帮助正确地分类数据，并在优化磁盘使用方面起重要的作用。因此，在创建表时必须特别关注所用的数据类型

注意：数据类型兼容
数据类型及其名称是SQL不兼容的一个主要原因。虽然大多数基本数据类型得到了一致的支持，但许多高级的数据类型却没有。更糟糕的是，偶然会有相同的数据类型在不同的DBMS中具有不同的名称。对此用户毫无办法，重要的是在创建表结构时要记住这些差异

1.1.4 行

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

行（row）
表中的一个记录

说明：是记录还是行？
你可能听到用户在提到行时称其为数据库记录（record）。这两个术语多半是可以交替使用的，但从技术上说，行才是正确的术语

1.1.5 主键

表中每一行都应该有一列（或几列）可以唯一标识自己。顾客表可以使用顾客编号，而订单表可以使用订单ID。雇员表可以使用雇员ID或雇员社会安全号

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

唯一标识表中每行的一列（或这几列）称为主键。主键用来表示一个特定的行。没有主键，更新或删除表中特定行就集为困哪，因为你不能保证操作只涉及相关的行

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

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

• 任意两行都不具有相同的主键值
• 每一行都必须具有一个主键值（主键列不允许NULL值）
• 主键列中的值不允许修改或更新
• 主键不能重用（如果某行从表中删除，它的主键不能赋给以后的新行）
  主键通常定义在表的一列上，但并不是必需这么做，也可以一起使用多个列作为主键。在使用多列作为主键时，上述条件必须应用到所有列，所有列值的组合必须是唯一的（但单个列的值可以不唯一）

还有一种非常重要的键，成为外键，我们将在第12课中介绍

1.2 什么是SQL

SQL（发因为字母S-Q-L或sequel）是Structured Query Language（结构化查询语言）的缩写。SQL是一种专门用来与数据库沟通的语言

与其他语言（如英语或Java、C、PHP这样的编程语言）不一样，SQL中只有很少的词，这是有意而为的。设计SQL的目的是很好地完成一项任务——提供一种从数据库中读写数据的简单有效的方法

SQL有如下优点：

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

下面我们将开始真正学习SQL

说明：SQL的扩展
许多DBMS厂商通过增加语句或指令，对SQL进行了扩展。这种扩展的目的是提供执行特定操作的额外功能或简化方法。虽然这种扩展很有用，但一般都是针对个别DBMS的，很少有两个以上的供应商支持这种扩展
标准SQL由ANSI标准标准委员会管理，从而称为ANSI SQL。所有主要的DBMS，即使有自己的扩展，也都支持ANSI SQL。各个实现有自己的名称，如PL/SQL、Transact-SQL等
本书讲授的SQL主要是ANSI SQL。在使用某种DBMS特定的SQL时，会特别说明

1.3 动手实践

与其他任何语言一样，学习SQL的最好方法是自己动手实践。为此，需要一个数据库和用来测试SQL语句的应用系统

本书中所有课程采用的都是真实的SQL语句和数据表。附录A给出了具体的样例表，并介绍了获得（或创建）它们的详细步骤，便于读者理解每一课讲授的内容。附录B介绍在各种应用程序中执行SQL所需的步骤。在进入下一课之前，强烈建议读者先阅读这两个附录的内容，为以后的学习做好准备

1.4 小结

这一课介绍了什么是SQL，它为什么很有用。因为SQL是用来与数据库打交道的，所以，我们也复习了一些基本的数据库术语

第2课 检索数据

这一课介绍如何使用SELECT语句从表中检索一个或多个数据列

2.1 SELECT语句

正如第1课所述，SQL语句是由简单的英语单词构成的。这些单词称为关键字，每个SQL语句都是由一个或多个关键字构成的。最经常使用的SQL语句语句大概就是SELECT语句了。它的用途是从一个或多个表中检索信息

关键字（keyword）
作为SQL组成部分的保留字。关键字不能用作表或列的名字。附录E列出了某些经常使用的保留字

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

说明：理解例子
本书各课程中的样例SQL语句（和样例输出）使用了附录A中描述的一组数据文件。如果想要理解和试验这些样例（我强烈建议这样做），请参阅附录A，它解释了如何下载或创建这些数据文件，重要的是，要理解SQL是一种语言而不是一个应用程序。具体如何写SQL语句并显示语句输出，是随不同的应用程序而变化的。为帮助读者根据自己的环境使用相应的例子，附录B介绍了如何针对许多流行的应用程序及开发环境发出本书中介绍的语句。如果读者需要了解某个应用程序，附录B中也给出了相应的建议

2.2 检索单个列

我们将从简单的SQL SELECT语句讲起，此语句如下所示：

• 输入

SELECT prod_name
FROM Products;

• 分析
  上述语句利用SELECT语句从Products表中检索一个名为prod_name的列。所需的列名写在SELECT关键字之后，FROM关键字指出从哪个表中检索数据。此语句的输出如下所示

• 输出

+---------------------+
| prod_name           |
+---------------------+
| Fish bean bag toy   |
| Bird bean bag toy   |
| Rabbit bean bag toy |
| 8 inch teddy bear   |
| 12 inch teddy bear  |
| 18 inch teddy bear  |
| Raggedy Ann         |
| King doll           |
| Queen doll          |
+---------------------+

说明：未排序数据
如果你自己试验这个查询，可能会发现显示输出的数据排序与这里的不同。出现这种情况很正常。如果没有明确排序查询结果（下一课介绍），则返回的数据没有特定的排序。返回数据的顺序可能是数据被添加到表中的顺序，也可能不是。只要返回相同的行，就是正常的

如上的一条简单SELECT语句将返回表中的所有行。数据没有过滤（过滤将得出结果集的一个子集），也没有排序。以后几课将讨论这些内容

提示：结束SQL语句
多条SQL语句必须以分号（;）分隔。多数DBMS不需要在单条SQL语句后加分号，但也有DBMS可能必须在单条SQL语句后加上分号。当然，如果愿意可以总是加上分号。事实上，即使不一定需要，加上分号也肯定没有坏处

提示：SQL语句和大小写
请注意，SQL语句不区分大小写，因此SELECT与select是相同的。同样，写成Select也没有关系。许多SQL开发人员喜欢对SQL关键字使用大小写，而对列名和表名使用小写，这样做使代码更易于阅读和调试。不过一定要认识到虽然SQL是不区分大小写，但是表名、列名和值可能有所不同（这有赖于具体的DBMS及其如何配置）

提示：使用空格
在处理SQL语句时，其中所有空格都被忽略。SQL语句可以写成常常的一行，也可以分写在多行。下面3中写法的作用是一样的。

SELECT prod_name
FROM Products;

SELECT prod_name FROM Products;

SELECT 
prod_name 
FROM 
Products;

多数SQL开发人员认为，将SQL语句分成多行更容易阅读和调试

2.3 检索多个列

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

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

下面的SELECT语句从Products表中选择3列

• 输入

SELECT prod_id,prod_name,prod_price
FROM Products;

• 分析
  与前一个例子一样，这条语句使用SELECT语句从表Products中选择数据。在这个例子中，指定了3个列名，列名之间用逗号分隔。次语句的输出如下

• 输出

+---------+---------------------+------------+
| prod_id | prod_name           | prod_price |
+---------+---------------------+------------+
| BNBG01  | Fish bean bag toy   | 3.49       |
| BNBG02  | Bird bean bag toy   | 3.49       |
| BNBG03  | Rabbit bean bag toy | 3.49       |
| BR01    | 8 inch teddy bear   | 5.99       |
| BR02    | 12 inch teddy bear  | 8.99       |
| BR03    | 18 inch teddy bear  | 11.99      |
| RGAN01  | Raggedy Ann         | 4.99       |
| RYL01   | King doll           | 9.49       |
| RYL02   | Queen doll          | 9.49       |
+---------+---------------------+------------+

说明：数据表示
从上述输出可以看到，SQL语句一般返回原始的、无格式的数据。数据的格式化是表示问题，而不是检索问题。因此，表示（如把上面价格值显示为正确的十进制数值货币金额）一般在显示该数据的应用程序中规定。通常很少直接使用实际检索出的数据（没有应用程序提供的格式）

2.4 检索所有列

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

• 输入

SELECT * 
FROM Products;

• 分析

如果给定一个通配符（*），则返回表中所有列。列的顺序一般是列在表定义中出现的物理顺序，但并不是总是如此。不过，SQL数据很少这样（通常，数据返回给应用程序，根据需要进行格式化，再表示出来）。因此，这不应该造成什么问题

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

提示：检索未知列
使用通配符有一个大优点。由于不明确指定列名（因为星号检索每一列），所以能检索出名字未知的列

2.5 检索不同的值

如前所述，SELECT语句返回所有匹配的行。但是，如果你不希望每个值每次都出现，该怎么办？例如，你想检索products表中所有产品供应商的ID：

• 输入

SELECT vend_id
FROM Products;

• 输出

+---------+
| vend_id |
+---------+
| BRS01   |
| BRS01   |
| BRS01   |
| DLL01   |
| DLL01   |
| DLL01   |
| DLL01   |
| FNG01   |
| FNG01   |
+---------+

SELECT语句返回9行（即使表中只有3个产品供应商），因为productts表中有9种产品。那么如何检索出不同的值？

办法就是使用DISTINCT关键字，顾名思义，它指示数据库只返回不同的值

• 输入

SELECT DISTINCT vend_id
FROM Products;

• 分析
  SELECT DISTINCT vend_id告诉DBMS只返回不同（具有唯一性）的vend_id行，所以正如下面的输出，只有3行。如果使用DISTINCT关键字，它必须直接放在列名的前面

• 输出

+---------+
| vend_id |
+---------+
| BRS01   |
| DLL01   |
| FNG01   |
+---------+

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

2.6 限制结果

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

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

• 输入

SELECT TOP 5 prod_name
FROM Products;

• 输出

+---------------------+
| prod_name           |
+---------------------+
| Fish bean bag toy   |
| Bird bean bag toy   |
| Rabbit bean bag toy |
| 8 inch teddy bear   |
| 12 inch teddy bear  |
+---------------------+

• 分析
  上面代码使用SELECT TOP 5语句，只检索前5行数据。
  如果你使用的是DB2，很可能习惯使用下面这一DBMS特定的SQL语句，像这样：

• 输入

SELECT prod_name
FROM Products
FETCH FIRST 5 ROWS ONLY;

• 分析

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;

• 分析

上述代码使用SELECT语句来检索单独的一列数据。LIMIT 5指示MySQL等DBMS返回不超过5行的数据。这个语句的输出参见下面的代码
为了得到后面的5行数据，需要指定从哪儿开始以及检索的行数，像这样

• 输入

SELECT prod_name
FROM Products
LIMIT 5 OFFSET 5;

• 分析

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

• 输出

+--------------------+
| prod_name          |
+--------------------+
| 18 inch teddy bear |
| Raggedy Ann        |
| King doll          |
| Queen doll         |
+--------------------+

所以，LIMIT指定返回的行数。LIMIT带的OFFSET指定从哪儿开始。在我们的例子中，Products表中只有9种产品，所以LIMIT 5 OFFSET 5 只返回了4行数据（因为没有第5行）

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

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

说明滚：并非所有的SQL实现都一样
我加入这一节只有一个原因，就是要说明，SQL虽然通常都有相当一致的实现，但你不能想当然地认为它总是这样。非常基本的语句往往是容易移植的，但较复杂的语句就不同了。当你针对某个问题寻找SQL解决方案时，一定要记住这一点

2.7 使用注释

可以看到，SQL语句是由DBMS处理的指令。如果你希望包括不进行处理和执行的文本，该怎么办呢？为什么你想要这么做呢？原因有以下几点

• 我们这里使用的SQL语句都很短，也很简单。然而，随着SQL语句变长，复杂性增加，你就会想添加一些描述性的注释，这便于你自己今后参考，或者供项目后续参与人员参考。这些注释需要嵌入在SQL脚本中，但显然不能进行实际的DBMS处理（相关示例可以参见附录B使用的create.sql和populate.sql）
• 这样适用于SQL文件开始处的内容。它可能包含程序员的联系·方式、程序描述以及一些说明。（相关示例也可以参见附录B中的那些.sql文件）
• 注释的另一个重要应用是暂时停止要执行的SQL代码。如果你碰到一个长的SQL语句，而只想测试它的一部分，那么应该注释掉一些代码，以便DBMS将其视为注释而加以忽略

很多DBMS都支持各种形式的注释语法。我们先来看行内注释：

• 输入

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

• 分析

注释使用-- （两个连字符）嵌在行内。-- 之后的文本就是注释，例如，这用来描述CREATE TABLE语句中的列就很不错

下面是另一种形式的行内注释（虽然这种形式很少得到支持）

• 输入

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

• 分析

在一行的开始处使用#，这一整行都将作为注释。你在本书提供的脚本create.sql和populate.sql中可以看到这种形式的注释

你也可以进行多行注释，注释可以在脚本的任何位置停止和开始

• 输入

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

• 分析
  注释从/* 开始，到*/结束，/* 和 */之间的任何内容都是注释。这种方式常用于给代码加注释，就如这个例子演示的，这里定义了两个SELECT语句，但是第一个不会执行，因为他已经被注释掉了

2.8 小结

这一课学习了如何使用SQL的SELECT语句来检索单个表列、多个表列以及所有表列。你也学习了如何返回不同的值，如何注释代码。同时不幸的是，更复杂的SQL使得SQL代码变得不轻便。下一课将讲授如何对检索出来的数据进行排序

第3课 排序检索数据

这一课讲授如何使用SELECT语句的ORDER BY子句，根据需要排序检索出的数据

3.1 排序数据

正如上一课所述，下面的SQL语句返回某个数据库表的单个列。但请看其输出，并没有特定的顺序

• 输入

SELECT prod_name
FROM Products;

• 输出

+---------------------+
| prod_name           |
+---------------------+
| Fish bean bag toy   |
| Bird bean bag toy   |
| Rabbit bean bag toy |
| 8 inch teddy bear   |
| 12 inch teddy bear  |
| 18 inch teddy bear  |
| Raggedy Ann         |
| King doll           |
| Queen doll          |
+---------------------+

其实，检索出来的数据并不是随机显示的。如果不排序，数据一般将以他在底层表中出现的顺序显示，这有可能是数据最初添加到表中的顺序。但是，如果数据随后进行过更新或删除，那么这个顺序将会受到DBMS重用回收存储空间的方式的影响。因此，如果不明确控制的话，则最终的结果不能（也不应该）依赖该排序顺序。关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有任何意义

子句（clause）
SQL语句由子句构成，有些子句是必需的，有些则是可选的。一个子句通常由一个关键字加上随提供的数据组成。子句的例子由我们在前一课看到的SELECT语句的FROM子句

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

• 输出

SELECT prod_name
FROM Products
ORDER BY Prod_name;

• 分析

除了指示DBMS软件对prod_name列以字母顺序排序数据的ORDER BY子句以外，这条语句与前面的语句相同。结果如下：

• 输出

+---------------------+
| prod_name           |
+---------------------+
| 12 inch teddy bear  |
| 18 inch teddy bear  |
| 8 inch teddy bear   |
| Bird bean bag toy   |
| Fish bean bag toy   |
| King doll           |
| Queen doll          |
| Rabbit bean bag toy |
| Raggedy Ann         |
+---------------------+

注意：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;

• 输出

+---------+------------+---------------------+
| prod_id | prod_price | prod_name           |
+---------+------------+---------------------+
| BNBG02  | 3.49       | Bird bean bag toy   |
| BNBG01  | 3.49       | Fish bean bag toy   |
| BNBG03  | 3.49       | Rabbit bean bag toy |
| RGAN01  | 4.99       | Raggedy Ann         |
| BR01    | 5.99       | 8 inch teddy bear   |
| BR02    | 8.99       | 12 inch teddy bear  |
| RYL01   | 9.49       | King doll           |
| RYL02   | 9.49       | Queen doll          |
| BR03    | 11.99      | 18 inch teddy bear  |
+---------+------------+---------------------+

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

3.3 按列位置排序

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

• 输入

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

• 输出

+---------+------------+---------------------+
| prod_id | prod_price | prod_name           |
+---------+------------+---------------------+
| BNBG02  | 3.49       | Bird bean bag toy   |
| BNBG01  | 3.49       | Fish bean bag toy   |
| BNBG03  | 3.49       | Rabbit bean bag toy |
| RGAN01  | 4.99       | Raggedy Ann         |
| BR01    | 5.99       | 8 inch teddy bear   |
| BR02    | 8.99       | 12 inch teddy bear  |
| RYL01   | 9.49       | King doll           |
| RYL02   | 9.49       | Queen doll          |
| BR03    | 11.99      | 18 inch teddy bear  |
+---------+------------+---------------------+

• 分析

可以看到，这里的输出与上面的查询相同，不同之处在于ORDER BY子句。SELECT清单中指定的是选择列的相对位置而不是列明。ORDER BY 2表示按SELECT清单中的第二列prod_price进行排序。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;

• 输出

+---------+------------+---------------------+
| prod_id | prod_price | prod_name           |
+---------+------------+---------------------+
| BR03    | 11.99      | 18 inch teddy bear  |
| RYL01   | 9.49       | King doll           |
| RYL02   | 9.49       | Queen doll          |
| BR02    | 8.99       | 12 inch teddy bear  |
| BR01    | 5.99       | 8 inch teddy bear   |
| RGAN01  | 4.99       | Raggedy Ann         |
| BNBG01  | 3.49       | Fish bean bag toy   |
| BNBG02  | 3.49       | Bird bean bag toy   |
| BNBG03  | 3.49       | Rabbit bean bag toy |
+---------+------------+---------------------+

如果打算用多个列排序，该怎么办？下面的例子以降序排序产品（最贵的在最前面），再加上产品名：

• 输出

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

• 输出

+---------+------------+---------------------+
| prod_id | prod_price | prod_name           |
+---------+------------+---------------------+
| BR03    | 11.99      | 18 inch teddy bear  |
| RYL01   | 9.49       | King doll           |
| RYL02   | 9.49       | Queen doll          |
| BR02    | 8.99       | 12 inch teddy bear  |
| BR01    | 5.99       | 8 inch teddy bear   |
| RGAN01  | 4.99       | Raggedy Ann         |
| BNBG02  | 3.49       | Bird bean bag toy   |
| BNBG01  | 3.49       | Fish bean bag toy   |
| BNBG03  | 3.49       | Rabbit bean bag toy |
+---------+------------+---------------------+

• 分析
  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.5 小结

这一课学习了如何用SELECT语句的ORDER BY子句对检索出的数据进行排序。这个子句必须是SELECT语句中的最后一条子句。根据需要，可以利用它在一个或多个列上对数据进行排序

第4课 过滤数据

这一课将讲授如何使用SELECT语句的WHERE子句指定搜索条件

4.1 使用WHERE子句

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

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

• 输入

SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49;

• 分析

这条语句从products表中检索两个列，但不返回所有行，只返回prod_price值为3.49的行，如下所示：

• 输出

+---------------------+------------+
| prod_name           | prod_price |
+---------------------+------------+
| Fish bean bag toy   | 3.49       |
| Bird bean bag toy   | 3.49       |
| Rabbit bean bag toy | 3.49       |
+---------------------+------------+

这个示例使用了简单的相等检验：检验这一列的值是否为指定值，据此过滤数据。不过，SQL不只能测试等于，还能做更多的事情

提示：有多少个0？
你在练习这个示例时，会发现显示的结果可能是3.49、3.490、3.4900等。出现这样的情况，往往是因为DBMS指定了所使用的数据类型及其默认行为。所以，如果你的输出可能与书上的有一点不同，不必焦虑，毕竟从数学角度讲，3.49和3.4900是一样的

提示：SQL过滤与应用过滤
数据也可以在应用层过滤。为此，SQL的SELECT语句为客户端应用检索出超过实际所需的数据，然后客户端代码对返回数据进行循环，提取出需要的行
通常，这种做法极其不妥。优化数据库后可以更快速有效地对数据进行过滤。而让客户端应用（或开发语言）处理数据库的工作将会极大地影响应用的性能，并且使所创建的应用完全不具备可伸缩性，此外，如果在客户端过滤数据，服务器不得不通过网络发送多余的数据，这将导致网络带宽的浪费

注意：WHERE子句的位置
在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则将产生错误（关于ORDER BY的使用，请参阅第3课）

4.2 WHERE子句操作符

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

表4-1 WHERE子句操作符

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

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

4.2.1 检查单个值

我们已经看到了检验相等的例子，现在来看看几个使用其他操作符的例子。第一个例子是列出所有价格小于10美元的产品

• 输入

SELECT prod_name, prod_price
FROM Products
WHERE prod_price < 10;

• 输出

+---------------------+------------+
| prod_name           | prod_price |
+---------------------+------------+
| Fish bean bag toy   | 3.49       |
| Bird bean bag toy   | 3.49       |
| Rabbit bean bag toy | 3.49       |
| 8 inch teddy bear   | 5.99       |
| 12 inch teddy bear  | 8.99       |
| Raggedy Ann         | 4.99       |
| King doll           | 9.49       |
| Queen doll          | 9.49       |
+---------------------+------------+

下一条语句检索所有价格小于等于10美元的产品（因为没有价格恰好是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';

• 输出

+---------+--------------------+
| vend_id | prod_name          |
+---------+--------------------+
| BRS01   | 8 inch teddy bear  |
| BRS01   | 12 inch teddy bear |
| BRS01   | 18 inch teddy bear |
| FNG01   | King doll          |
| FNG01   | Queen doll         |
+---------+--------------------+

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

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

• 输出

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

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

4.2.3 范围值检查

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

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

• 输入

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

• 输出

+--------------------+------------+
| prod_name          | prod_price |
+--------------------+------------+
| 8 inch teddy bear  | 5.99       |
| 12 inch teddy bear | 8.99       |
| King doll          | 9.49       |
| Queen doll         | 9.49       |
+--------------------+------------+

• 分析

从这个例子可以看到，在使用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;

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

• 输入

SELECT cust_name
FROM Customers
WHERE cust_email IS NULL;

• 输出

+---------------+
| cust_name     |
+---------------+
| Kids Place    |
| The Toy Store |
+---------------+

提示：各DBMS特有的操作符
许多DBMS扩展了标准的操作符集，提供了更高级的过滤选择。更多信息请参阅相应的DBMS文档

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

4.3 小结

这一刻介绍了如何用SELECT语句的WHERE子句过滤返回的数据。我们学习了如何检验相等、不相等、大于、小于、值的范围以及NULL值等

第5课 高级数据过滤

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

5.1 组合WHERE子句

第4课介绍所有WHERE子句在过滤数据时使用的都是单一的条件。为了进行更强的过滤控制，SQL允许给出多个WHERE子句。这些子句有两种使用方式，即以AND子句或OR子句的方式使用

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

5.1.1 AND操作符

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

• 输入：

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

• 分析

此SQL语句检索由供应商DLL01制造且价格小于等于4美元的所有产品的名称和价格。这条SELECT语句中的WHERE子句包含两个条件，用AND关键字联结在一起。AND指示DBMS只返回满足所有给定条件的行。如果某个产品由供应商DLL01制造，但价格高于4美元，则不检索它。类似地，如果产品价格小于4美元，但不是由指定供应商制造的也不被检索。这条SQL语句产生的输出如下：

• 输出

+---------+------------+---------------------+
| prod_id | prod_price | prod_name           |
+---------+------------+---------------------+
| BNBG01  | 3.49       | Fish bean bag toy   |
| BNBG02  | 3.49       | Bird bean bag toy   |
| BNBG03  | 3.49       | Rabbit bean bag toy |
+---------+------------+---------------------+

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

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

说明：没有ORDER BY子句
为了节省空间，也为了减少你的输入，我在很多例子里省略了ORDER BY子句。因此，你的输出完全有可能与书上的输出不一致。虽然返回行的数量总是对的，但它们的顺序可能不同。当然，如果你愿意也可以加上一个ORDER BY子句，它因该放在WHERE子句之后

5.1.2 OR操作符

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

请看如下的SELECT语句：

• 输入

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

• 分析

此SQL语句检索由任一个指定供应商制造的所有产品的产品名和价格。OR操作符告诉DBMS匹配任一条件而不是同时匹配两个条件。如果这里使用的是AND操作符，则没有数据返回（因为会创建没有匹配行的WHERE子句）。这条SQL语句产生的输出如下：

• 输出

+---------------------+------------+
| prod_name           | prod_price |
+---------------------+------------+
| 8 inch teddy bear   | 5.99       |
| 12 inch teddy bear  | 8.99       |
| 18 inch teddy bear  | 11.99      |
| Fish bean bag toy   | 3.49       |
| Bird bean bag toy   | 3.49       |
| Rabbit bean bag toy | 3.49       |
| Raggedy Ann         | 4.99       |
+---------------------+------------+

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

5.1.3 求值顺序

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

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

• 输入

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

• 输出

+---------------------+------------+
| prod_name           | prod_price |
+---------------------+------------+
| 18 inch teddy bear  | 11.99      |
| Fish bean bag toy   | 3.49       |
| Bird bean bag toy   | 3.49       |
| Rabbit bean bag toy | 3.49       |
| Raggedy Ann         | 4.99       |
+---------------------+------------+

• 分析

请看上面的结果。返回的行中有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;

• 输出

+--------------------+------------+
| prod_name          | prod_price |
+--------------------+------------+
| 18 inch teddy bear | 11.99      |
+--------------------+------------+

• 分析

这条SELECT语句与前一条的唯一差别是，将前两个条件用圆括号括起来了。因为圆括号具有比AND或OR操作符更高的求值顺序，所以DBMS首先过滤圆括号内的OR条件。这时，SQL语句变成了选择由供应商DLL01或BRS01制造的且价格在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;

• 输出

+---------------------+------------+
| prod_name           | prod_price |
+---------------------+------------+
| 12 inch teddy bear  | 8.99       |
| 18 inch teddy bear  | 11.99      |
| 8 inch teddy bear   | 5.99       |
| Bird bean bag toy   | 3.49       |
| Fish bean bag toy   | 3.49       |
| Rabbit bean bag toy | 3.49       |
| Raggedy Ann         | 4.99       |
+---------------------+------------+

• 分析

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

• 输出

+---------------------+------------+
| prod_name           | prod_price |
+---------------------+------------+
| 12 inch teddy bear  | 8.99       |
| 18 inch teddy bear  | 11.99      |
| 8 inch teddy bear   | 5.99       |
| Bird bean bag toy   | 3.49       |
| Fish bean bag toy   | 3.49       |
| Rabbit bean bag toy | 3.49       |
| Raggedy Ann         | 4.99       |
+---------------------+------------+

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

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

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

5.3 NOT操作符

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

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

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

• 输入

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

• 输出

+--------------------+
| prod_name          |
+--------------------+
| 12 inch teddy bear |
| 18 inch teddy bear |
| 8 inch teddy bear  |
| King doll          |
| Queen doll         |
+--------------------+

• 分析

这里的NOT否定跟在其后的条件，因此，DBMS不是匹配vend_id为DLL01，而是匹配非DLL01之外的所有东西
上面的例子也可以使用<>操作符来完成，如下所示

• 输入

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

• 输出

+--------------------+
| prod_name          |
+--------------------+
| 12 inch teddy bear |
| 18 inch teddy bear |
| 8 inch teddy bear  |
| King doll          |
| Queen doll         |
+--------------------+

• 分析

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

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

5.4 小结

这一课讲授如何使用AND和OR操作符组合成WHERE子句，还讲授了如何明确地管理求值顺序，如何使用IN和NOT操作符

第6课 用通配符进行过滤

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

6.1 LIKE操作符

前面介绍的所有操作符都是针对已知的值进行过滤的。不管是匹配一个值还是多个值，检验大于还是小于已知值，后者检查某个范围的值，其共同点是过滤中使用的值都是已知的

但是，这种过滤方法并不是任何时候都好用。例如，则哪有那个搜索产品名中包含文本bean bag的所有产品，可以创建比较特定数据的搜索模式。在这个例子中，如果你想找出名称包含bean bag的所有产品，可以构造一个通配符搜索模式，找出在产品名的任何位置出现bean bag的产品

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

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

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

谓词（predicate）
操作符何时不是操作符？答案是，它作为谓词时。从技术上说，LIKE是谓词而不是操作符。虽然最终的结果是相同的，但应该对此术语有所了解，以免在SQL文献或手册中遇到此术语时不知所云

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

6.1.1 百分号（%）通配符

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

• 输入

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

• 输出

+---------+-------------------+
| prod_id | prod_name         |
+---------+-------------------+
| BNBG01  | Fish bean bag toy |
+---------+-------------------+

• 分析

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

说明：Access通配符
如果使用的是Microsoft Access，需要使用*而不是%

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

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

• 输入

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

• 输出

+---------+---------------------+
| prod_id | prod_name           |
+---------+---------------------+
| BNBG01  | Fish bean bag toy   |
| BNBG02  | Bird bean bag toy   |
| BNBG03  | Rabbit bean bag toy |
+---------+---------------------+

• 分析

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

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

• 输入

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

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

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

说明：请注意后面所跟的空格
包括Access在内的许多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之后的字符（或空格）。更好的解决办法是用函数去掉空格。请参阅第8课

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

6.1.2 下划线（_）通配符

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

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

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

举个例子：

• 输入

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

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

• 输出

+---------+--------------------+
| prod_id | prod_name          |
+---------+--------------------+
| BR02    | 12 inch teddy bear |
| BR03    | 18 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';

• 输出

+---------+--------------------+
| prod_id | prod_name          |
+---------+--------------------+
| BR01    | 8 inch teddy bear  |
| BR02    | 12 inch teddy bear |
| BR03    | 18 inch teddy bear |
+---------+--------------------+

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

6.1.3 方括号([])通配符

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

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

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

• 输入

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

• 输出

+----------------+
| cust_contact   |
+----------------+
| Jim Jones      |
| John Smith     |
| Michelle Green |
+----------------+

• 分析

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

• 输入

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 使用通配符的技巧

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

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

总之，通配符是一种极其重要和有用的搜索工具，以后我们经常会用到它

6.3 小结

这一课介绍了什么是通配符，如何在WHERE子句中使用SQL通配符，还说明了通配符应该细心使用，不要使用过度

第7课 创建计算字段

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

7.1 计算字段

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

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

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

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

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

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

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

7.2 拼接字段

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

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

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

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

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

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

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

• 输入

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

• 输出

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

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

• 输入

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

• 输出

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;

• 输出

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

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

• 输入

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

• 输出

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

• 分析

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

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

使用别名

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

为了解决这个问题，SQL支持列别名。别名是一个字段或值的替换名。别名用AS关键字赋予。请看下面的SELECT语句：

• 输入

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

• 输出

+------------------------+
| vend_title             |
+------------------------+
| Bear Emporium(USA)     |
| Bears R Us(USA)        |
| Doll House Inc.(USA)   |
| Fun and Games(England) |
| Furball Inc.(USA)      |
| Jouets et ours(France) |
+------------------------+

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

• 输入

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;

• 分析

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

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

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

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

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

7.3 执行算术计算

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

• 输入

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

• 输出

+---------+----------+------------+
| prod_id | quantity | item_price |
+---------+----------+------------+
| RGAN01  |        5 | 4.99       |
| BR03    |        5 | 11.99      |
| BNBG01  |       10 | 3.49       |
| BNBG02  |       10 | 3.49       |
| BNBG03  |       10 | 3.49       |
+---------+----------+------------+

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

• 输入

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

• 输出

+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| RGAN01  |        5 | 4.99       | 24.95          |
| BR03    |        5 | 11.99      | 59.95          |
| BNBG01  |       10 | 3.49       | 34.90          |
| BNBG02  |       10 | 3.49       | 34.90          |
| BNBG03  |       10 | 3.49       | 34.90          |
+---------+----------+------------+----------------+

• 分析

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

SQL支持表7-1中列出的基本算术操作符。此外，圆括号可用来区分优先顺序。关于优先顺序的介绍，请参阅第5课

操作符	说明
+	加
-	减
*	乘
/	除

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

7.4 小结

这一课介绍了计算字段以及如何创建计算字段。我们用例子说明了计算字段在字符串拼接和算术计算中的用途。此外，还讲述了如何创建和使用别名，以便应用程序能引用计算字段

第8课 使用函数处理数据

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

8.1 函数

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

前一课中用来去掉字符串尾的空格的RTRIM()就是一个函数

函数带来的问题

在学习这一课并进行实践之前，你应该了解使用SQL函数所存在的问题

与几乎所有DBMS都同等地支持SQL语句（如SELECT）不同，每一个DBMS都有特定的函数。事实上，只有少数几个函数被所有主要的DBMS等同地支持。虽然所有类型的函数一般都可以在每个DBMS中使用，但各个函数的名称和语法可能极其不同。为了说明可能存在的问题，表8-1列出了3个常用的函数及其在各个DBMS中的语法：
表8-1 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程序员不赞成使用特定于实现的功能。虽然这样做很有好处，但有的时候并不利于应用程序的性能。如果不使用这些函数，编写某些应用程序代码会很艰难。必须利用其他方法来实现DBMS可以非常有效完成工作

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

8.2 使用函数

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

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

我们在上一课看到函数用于SELECT后面的列名，但函数的作用不仅于此。它还可以作为SELECT语句的其他成分，如在WHERE子句中使用，在其他SQL语句中使用等，后面会做更多的介绍

8.2.1 文本处理函数

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

• 输入

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

• 输出

+-----------------+------------------+
| vend_name       | vend_name_upcase |
+-----------------+------------------+
| Bear Emporium   | BEAR EMPORIUM    |
| Bears R Us      | BEARS R US       |
| Doll House Inc. | DOLL HOUSE INC.  |
| Fun and Games   | FUN AND GAMES    |
| Furball Inc.    | FURBALL INC.     |
| Jouets et ours  | JOUETS ET OURS   |
+-----------------+------------------+

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

表8-2列出了一些常用的文本处理函数

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

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

说明：SOUNDEX支持
Microsoft Access和PostgreSQL不支持SOUNDEX()，因此以下的例子不适用于这些DBMS
另外，如果在创建SQLite时使用了SQLITE_SOUNDEX编译时选项，那么SOUNDES在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';

• 输出

Empty set

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

• 输入

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

• 输出

+------------+----------------+
| cust_name  | cust_contact   |
+------------+----------------+
| Kids Place | Michelle Green |
+------------+----------------+

• 分析

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

8.2.2 日期和时间处理函数

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

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

我们举个简单的例子，来说明日期处理函数的用法。Orders表中包含的订单都带有订单日期。为在SQLServer中检索2012年的所有订单，可如下进行

• 输入

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

• 输出

+-----------+
| order_num |
+-----------+
|     20005 |
|     20006 |
|     20007 |
|     20008 |
|     20009 |
+-----------+

在Access中使用如下版本：

• 输入

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

• 分析

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

下面是使用名为DATE_PART()的类似函数的PostgreSQL版本：

• 输入

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

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

• 输入

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

• 分析

在这个例子中，to_char()函数用来提取日期的成分，to_number()用来将提取出的成分转换为数值，以便能与2012进行比较

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

• 输入

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

• 分析

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

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具有比较日期、执行基于日期的运算、选择日期格式等的函数。但是，可以看到，不同DBMS的日期-时间处理函数可能不同。关于具体DBMS支持的日期-时间处理函数，请参阅相应的文档

8.2.3 数值处理函数

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

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

表8-3 常用数值处理函数

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

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

8.3 小结

这一课介绍了如何使用SQL的数据处理函数。虽然这些函数在格式化、处理和过滤数据中非常有用，但它们在各种SQL实现中很不一致（SQL Server和Oracle之间的差异说明了这一点）

第9课 汇总数据

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

9.1 聚集函数

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

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

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

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

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

表9-1 SQL聚集函数

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

下面说明各函数的使用

9.1.1 AVG()函数

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

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

• 输入

SELECT AVG(prod_price) AS avg_price
FROM Products;

• 输出

+-----------+
| avg_price |
+-----------+
| 6.823333  |
+-----------+

• 分析

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

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

• 输入

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

• 输出

+-----------+
| avg_price |
+-----------+
| 3.865     |
+-----------+

• 分析

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

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

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

9.1.2 COUNT()函数

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

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

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

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

• 输入

SELECT COUNT(*) AS num_cust
FROM Customers;

• 输出

+----------+
| num_cust |
+----------+
|        5 |
+----------+

• 分析

在此例子中，利用COUNT(*)对所有行进行计数，不管行中各列有什么值。计数值在num_cust中返回

下面的例子只对具有电子邮件地址的客户计数：

• 输入

SELECT COUNT(cust_email) AS num_cust
FROM Customers;

• 输出

+----------+
| num_cust |
+----------+
|        3 |
+----------+

• 分析

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

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

9.1.3 MAX()函数

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

• 输入

SELECT MAX(prod_price) AS max_price
FROM Prosucts;

• 输出

+-----------+
| max_price |
+-----------+
| 11.99     |
+-----------+

• 分析

这里，MAX()返回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_price |
+-----------+
| 3.49      |
+-----------+

• 分析

其中MIN()返回Products表中最便宜物品的价格

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

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

9.1.5 SUM()函数

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

下面举一个例子，OrderItems包含订单中实际的物品，每个物品有相应的数量。可如下检索所订购物品的总数（所有quantity值之和）：

• 输入

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

• 输出

+---------------+
| items_ordered |
+---------------+
|           200 |
+---------------+

• 分析

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

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

• 输入

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

• 输出

+-------------+
| total_price |
+-------------+
|     1648.00 |
+-------------+

• 分析

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

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

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

9.2 聚集不同值

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

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

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

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

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

• 输入

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

• 输出

+-----------+
| avg_price |
+-----------+
|  4.240000 |
+-----------+

• 分析

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

注意：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;

• 输出

+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
|         9 |      3.49 |     11.99 |  6.823333 |
+-----------+-----------+-----------+-----------+

• 分析

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

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

9.4 小结

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

第10课 分组数据

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

10.1 数据分组

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

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

• 输入

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

• 输出

+-----------+
| num_prods |
+-----------+
|         4 |
+-----------+

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

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

10.2 创建分组

分组是使用SELECT语句的GROUP BY子句建立的。理解分组的最好办法是看一个例子：

• 输入

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

• 输出

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| BRS01   |         3 |
| DLL01   |         4 |
| FNG01   |         2 |
+---------+-----------+

• 分析

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

因为使用了GROUP BY，就不必指定要计算和估值的每个组了。系统会自动完成。GROUP BY子句只是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）。具体的DBMS是否支持ALL，请参阅相应的文档

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

10.3 过滤分组

除了能用GROUP BY分组数据外，SQL还允许过滤分组，规定包括哪些分组，排除哪些分组。例如，你可能想要列出至少有两个订单的所有顾客。为此，必须基于完整的分组而不是个别的行进行过滤

我们已经看到了WHERE子句的作用（第4课提及）。但是，在这个例子中WHERE不能完成任务，因为WHERE过滤指定的是行而不是分组。事实上，WHERE没有分组的概念

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

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

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

• 输入

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

• 输出

+------------+--------+
| cust_id    | orders |
+------------+--------+
| 1000000001 |      2 |
+------------+--------+

• 分析

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

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

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

那么，有没有在一条语句中同时使用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;

• 输出

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| BRS01   |         3 |
| FNG01   |         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;

• 输出

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| BRS01   |         3 |
| DLL01   |         4 |
| FNG01   |         2 |
+---------+-----------+

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

10.4 分组和排序

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

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

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

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

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

• 输入

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

• 输出

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

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

• 输出

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

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

• 输出

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

• 分析

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

10.5 SELECT子句顺序

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

表10-2 SELECT子句及其顺序

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

10.6 小结

上一课介绍了如何用SQL聚集函数对数据进行汇总计算。这一课讲授了如何使用GROUP BY子句对多组数据进行汇总计算，返回每个组的结果。我们看到了如何使用HAVING子句过滤特定的组，还知道了ORDER BY和GROUP BY之间以及WHERE和HAVING之间的差异

第11课 使用子查询

这一课介绍什么是子查询，如何使用它们

11.1 子查询

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

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

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

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

11.2 利用子查询进行过滤

本书所有课中使用的数据库都是关系表（关于每个表及关系的描述，请参阅附录A）。订单存储在两个表中。每个订单包含订单编号、客户ID、订单日期，在Orders表中存储为一行。各订单的物品存储在相关的OrderItems表中。Orders表不存储顾客信息，只存储顾客ID。顾客的实际信息存储在Customers表中

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

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

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

也可以使用子查询来把3个查询组合成一条语句

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

• 输入

SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01';

• 输出

+-----------+
| order_num |
+-----------+
|     20007 |
|     20008 |
+-----------+

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

• 输入

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

• 输出

+------------+
| cust_id    |
+------------+
| 1000000004 |
| 1000000005 |
+------------+

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

• 输入

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

• 输出

+------------+
| cust_id    |
+------------+
| 1000000004 |
| 1000000005 |
+------------+

• 分析

在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子句所返回的值相同

提示：格式化SQL
包含子查询的SELECT语句难以阅读和调试，它们在较为复杂时更是如此。如上所示，把子查询分解为多行并进行适当的缩进，能极大地简化子查询的使用
顺便一提，这就是颜色编码起作用的地方，好的DBMS客户端正是由于这个原因使用了颜色代码SQL

现在得到了订购物品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'));

• 输出

+---------------+--------------------+
| cust_name     | cust_contact       |
+---------------+--------------------+
| Fun4All       | Denise L. Stephens |
| The Toy Store | Kim Howard         |
+---------------+--------------------+

• 分析

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

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

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

注意：子查询和性能
这里给出的代码有效，并获得了所需的结果，但是，使用子查询并不总是执行这类数据检索的最有效方法。更多的论述，请参阅第12课，其中将再次给出这个例子

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

• 输出

+---------------+------------+--------+
| cust_name     | cust_state | orders |
+---------------+------------+--------+
| Fun4All       | IN         |      1 |
| Fun4All       | AZ         |      1 |
| Kids Place    | OH         |      0 |
| The Toy Store | IL         |      1 |
| Village Toys  | MI         |      2 |
+---------------+------------+--------+

• 分析

这条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;

• 输出

+---------------+------------+--------+
| cust_name     | cust_state | orders |
+---------------+------------+--------+
| Fun4All       | IN         |      5 |
| Fun4All       | AZ         |      5 |
| Kids Place    | OH         |      5 |
| The Toy Store | IL         |      5 |
| Village Toys  | MI         |      5 |
+---------------+------------+--------+

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

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

提示：不止一种解决方案
正如这一课前面所述，虽然这里给出的样例代码运行良好，但它并不是解决这种数据检索的最有效方法。在后面两课学习JOIN时，我们还会遇到这个例子

11.4 小结

这一课学习了什么是子查询，如何使用它们。子查询常用于WHERE子句的IN操作符中，以及用来填充计算列。我们举了这两种操作类型的例子。

第12课 联结表

这一课会介绍什么是联结，为什么使用联结，如何编写使用联结的SELECT语句

12.1 联结

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

在能够有效地使用联结前，必须了解关系表以及关系数据库设计的一些基础知识。下面的介绍并不能涵盖这一主题的所有内容，但作为入门已经够了

12.1.1 关系表

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

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

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

• 同一供应商生产的每个产品，其供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间
• 如果供应商信息发生变化，例如供应商迁址或电话号码变动，只需修改一次即可
• 如果有重复（即每种产品都存储供应商信息），则很难保证每次输入该数据的方式都相同。不一致的数据在报表就很难利用

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

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

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

这样做的好处是：

• 供应商信息不重复，不会浪费时间和空间
• 如果供应商信息变动，可以只更新Vendors表中的单个记录，相关表中的数据不用改动
• 由于数据不重复，数据显然是一致的，使得处理数据和生成报表更简单

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

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

12.1.2 为什么使用联结

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

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

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

说明： 使用交互式DBMS工具
重要的是，要理解联结不是物理实体。换句话说，它在实际的数据库表中并不存在。DBMS会根据需要建立联结，它在查询执行期间一直存在
许多DBMS提供图形界面，用来交互式地定义表关系。这些工具极其有助于维护引用完整性。在使用关系表时，仅在关系列中插入合法数据是非常重要的。回到这里的例子，如果Products表中存储了无效的供应商ID，则相应的产品不可访问，因为它们没有关联到某个供应商。为避免这种情况发生，可只是数据库只允许在Products表的供应商ID列中出现合法值（即出现在Vendors表中的供应商）。引用完整性标识DBMS强制实施数据完整性规则。这些规则一般由提供了界面的DBMS管理

12.2 创建联结

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

• 输入

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

• 输出

+-----------------+---------------------+------------+
| vend_name       | prod_name           | prod_price |
+-----------------+---------------------+------------+
| Bears R Us      | 8 inch teddy bear   |       5.99 |
| Bears R Us      | 12 inch teddy bear  |       8.99 |
| Bears R Us      | 18 inch teddy bear  |      11.99 |
| Doll House Inc. | Fish bean bag toy   |       3.49 |
| Doll House Inc. | Bird bean bag toy   |       3.49 |
| Doll House Inc. | Rabbit bean bag toy |       3.49 |
| Doll House Inc. | Raggedy Ann         |       4.99 |
| Fun and Games   | King doll           |       9.49 |
| Fun and Games   | Queen doll          |       9.49 |
+-----------------+---------------------+------------+

• 分析

我们来看这段代码。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语句返回了两个不同表中的数据

警告：完全限定列名
就像前一课提到的，在引用的列可能出现歧义时，必须使用完全限定列名（用一个句点分隔表名和列名）。如果引用一个没有用表名限制的具有歧义的列名，大多数DBMS会返回错误

12.2.1 WHERE子句的重要性

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

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

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

• 输出

SELECT vend_name, prod_name, prod_price
FROM Vendors, Products;

• 输入

+-----------------+---------------------+------------+
| vend_name       | prod_name           | prod_price |
+-----------------+---------------------+------------+
| Jouets et ours  | Fish bean bag toy   |       3.49 |
| Furball Inc.    | Fish bean bag toy   |       3.49 |
| Fun and Games   | Fish bean bag toy   |       3.49 |
| Doll House Inc. | Fish bean bag toy   |       3.49 |
| Bears R Us      | Fish bean bag toy   |       3.49 |
| Bear Emporium   | Fish bean bag toy   |       3.49 |
| Jouets et ours  | Bird bean bag toy   |       3.49 |
| Furball Inc.    | Bird bean bag toy   |       3.49 |
| Fun and Games   | Bird bean bag toy   |       3.49 |
| Doll House Inc. | Bird bean bag toy   |       3.49 |
| Bears R Us      | Bird bean bag toy   |       3.49 |
| Bear Emporium   | Bird bean bag toy   |       3.49 |
| Jouets et ours  | Rabbit bean bag toy |       3.49 |
| Furball Inc.    | Rabbit bean bag toy |       3.49 |
| Fun and Games   | Rabbit bean bag toy |       3.49 |
| Doll House Inc. | Rabbit bean bag toy |       3.49 |
| Bears R Us      | Rabbit bean bag toy |       3.49 |
| Bear Emporium   | Rabbit bean bag toy |       3.49 |
| Jouets et ours  | 8 inch teddy bear   |       5.99 |
| Furball Inc.    | 8 inch teddy bear   |       5.99 |
| Fun and Games   | 8 inch teddy bear   |       5.99 |
| Doll House Inc. | 8 inch teddy bear   |       5.99 |
| Bears R Us      | 8 inch teddy bear   |       5.99 |
| Bear Emporium   | 8 inch teddy bear   |       5.99 |
| Jouets et ours  | 12 inch teddy bear  |       8.99 |
| Furball Inc.    | 12 inch teddy bear  |       8.99 |
| Fun and Games   | 12 inch teddy bear  |       8.99 |
| Doll House Inc. | 12 inch teddy bear  |       8.99 |
| Bears R Us      | 12 inch teddy bear  |       8.99 |
| Bear Emporium   | 12 inch teddy bear  |       8.99 |
| Jouets et ours  | 18 inch teddy bear  |      11.99 |
| Furball Inc.    | 18 inch teddy bear  |      11.99 |
| Fun and Games   | 18 inch teddy bear  |      11.99 |
| Doll House Inc. | 18 inch teddy bear  |      11.99 |
| Bears R Us      | 18 inch teddy bear  |      11.99 |
| Bear Emporium   | 18 inch teddy bear  |      11.99 |
| Jouets et ours  | Raggedy Ann         |       4.99 |
| Furball Inc.    | Raggedy Ann         |       4.99 |
| Fun and Games   | Raggedy Ann         |       4.99 |
| Doll House Inc. | Raggedy Ann         |       4.99 |
| Bears R Us      | Raggedy Ann         |       4.99 |
| Bear Emporium   | Raggedy Ann         |       4.99 |
| Jouets et ours  | King doll           |       9.49 |
| Furball Inc.    | King doll           |       9.49 |
| Fun and Games   | King doll           |       9.49 |
| Doll House Inc. | King doll           |       9.49 |
| Bears R Us      | King doll           |       9.49 |
| Bear Emporium   | King doll           |       9.49 |
| Jouets et ours  | Queen doll          |       9.49 |
| Furball Inc.    | Queen doll          |       9.49 |
| Fun and Games   | Queen doll          |       9.49 |
| Doll House Inc. | Queen doll          |       9.49 |
| Bears R Us      | Queen doll          |       9.49 |
| Bear Emporium   | Queen doll          |       9.49 |
+-----------------+---------------------+------------+

• 分析

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

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

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

12.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文档

说明：“正确的”语法
ANSI SQL规范首选INNER JOIN语法，之前使用的是简单的等值语法。其实，SQL语言纯正论者是用鄙视的眼光看待简单语法的。这就是说，DBMS的确支持简单格式和标准格式，我建议你要理解这两种格式，具体使用就看你用哪个更顺手了

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;

• 输出

+---------------------+-----------------+------------+----------+
| prod_name           | vend_name       | prod_price | quantity |
+---------------------+-----------------+------------+----------+
| 18 inch teddy bear  | Bears R Us      |      11.99 |       50 |
| Fish bean bag toy   | Doll House Inc. |       3.49 |      100 |
| Bird bean bag toy   | Doll House Inc. |       3.49 |      100 |
| Rabbit bean bag toy | Doll House Inc. |       3.49 |      100 |
| Raggedy Ann         | Doll House Inc. |       4.99 |       50 |
+---------------------+-----------------+------------+----------+

• 分析

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

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

注意：联结中表的最大数目
虽然SQL本身不限制每个联结约束中表的数目，但实际上许多DBMS都有限制。请参阅具体的DBMS文档以了解其限制

现在回顾一下第11课中的例子，如下的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'));

如第11课所述，子查询并不总是执行复杂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';

• 输出

+---------------+--------------------+
| cust_name     | cust_contact       |
+---------------+--------------------+
| Fun4All       | Denise L. Stephens |
| The Toy Store | Kim Howard         |
+---------------+--------------------+

• 分析

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

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

12.3 小结

联结是SQL中一个最重、最强大的特性，有效地使用联结需要对关系数据库设计有基本的了解。本课在介绍联结时，讲述了一些关系数据库设计的基本知识，包括等值联结（也称内联结）这种最常用的联结。下一课将介绍如何创建其他类型的联结

第13课 创建高级联结

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

13.1 使用表别名

第7课介绍了如何使用别名引用被检索的表列。给列起别名的语法如下：

• 输入

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子句以及其他语句部分

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

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

13.2 使用不同类型的联结

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

13.2.1 自联结

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

假如要给与Jim Jones同一公司的所有顾客发送一封信件。这个查询要求首先找出Jim Jones工作的公司，然后找出在该公司工作的顾客。下面是解决此问题的一种方法：

• 输入

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

• 输出

+------------+-----------+--------------------+
| cust_id    | cust_name | cust_contact       |
+------------+-----------+--------------------+
| 1000000003 | Fun4All   | Jim Jones          |
| 1000000004 | Fun4All   | Denise L. Stephens |
+------------+-----------+--------------------+

• 分析

这是第一种解决方案，使用了子查询。内部的SELECT语句做了一个简单检索，返回Jim Jones工作公司的cust_name。该名字用于外部查询的WHERE子句中，以检索出为该公司工作的所有雇员（第11课中讲授了子查询，更多信息请参阅该课）

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

• 输入

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

• 输出

+------------+-----------+--------------------+
| cust_id    | cust_name | cust_contact       |
+------------+-----------+--------------------+
| 1000000003 | Fun4All   | Jim Jones          |
| 1000000004 | Fun4All   | Denise L. Stephens |
+------------+-----------+--------------------+

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

• 分析

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

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

提示：用自联结而不用子查询
自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的，但许多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，实现中可能稍有不同。下面段落中描述的各种语法形式覆盖了大多数实现，在继续学习之前请参阅你使用的DBMS文档，以确实其语法

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

• 输出

+------------+-----------+
| cust_id    | order_num |
+------------+-----------+
| 1000000001 |     20005 |
| 1000000001 |     20009 |
| 1000000002 | NULL      |
| 1000000003 |     20006 |
| 1000000004 |     20007 |
| 1000000005 |     20008 |
+------------+-----------+

• 分析

类似上一课提到的内联结，这条SELECT语句使用了关键字OUTER JOIN来指定联结类型（而不是在WHERE子句中指定）。但是，与内联结关联两个表中的行不同的是，外联结还没包括没有关联行的行。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。上面的例子使用LEFT OUTER JOIN从FROM子句左边的表（Customers表）中选择所有行。为了从右边的表中选择所有行，需要使用RIGHT OUTER JOIN，如下例所示：

• 输入

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

注意：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 使用带聚集函数的联结

如第9课所述，聚集函数用来汇总数据。虽然至今为止我们举的聚集函数的例子都只是从一个表中汇总的数据，但这些函数也可以与联结一起使用

我们来看个例子，要检索所有顾客及每个顾客所下的订单数，下面代码使用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;

• 输出

+------------+---------+
| cust_id    | num_ord |
+------------+---------+
| 1000000001 |       2 |
| 1000000003 |       1 |
| 1000000004 |       1 |
| 1000000005 |       1 |
+------------+---------+

• 分析

这条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;

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

• 输出

+------------+---------+
| cust_id    | num_ord |
+------------+---------+
| 1000000001 |       2 |
| 1000000002 |       0 |
| 1000000003 |       1 |
| 1000000004 |       1 |
| 1000000005 |       1 |
+------------+---------+

• 分析

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

13.4 使用联结和联结条件

在总结讨论联结的这两课前，有必要汇总一下联结及其使用的要点

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

13.5 小结

本课是上一课的延续，首先讲授了如何以及为什么使用别名，然后讨论不同的联结类型以及每类联结所使用的语法。我们还介绍了如何与联结一起使用聚集函数，以及在使用联结时应该注意的问题

第14课 组合查询

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

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

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

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

• 输入

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

• 输出

+---------------+--------------+-----------------------+
| cust_name     | cust_contact | cust_email            |
+---------------+--------------+-----------------------+
| Village Toys  | John Smith   | sales@villagetoys.com |
| Fun4All       | Jim Jones    | jjones@fun4all.com    |
| The Toy Store | Kim Howard   | NULL                  |
+---------------+--------------+-----------------------+

• 输入

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

• 输出

+-----------+--------------------+-----------------------+
| cust_name | cust_contact       | cust_email            |
+-----------+--------------------+-----------------------+
| Fun4All   | Jim Jones          | jjones@fun4all.com    |
| Fun4All   | Denise L. Stephens | dstephens@fun4all.com |
+-----------+--------------------+-----------------------+

• 分析

第一条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';

• 输出

+---------------+--------------------+-----------------------+
| cust_name     | cust_contact       | cust_email            |
+---------------+--------------------+-----------------------+
| Village Toys  | John Smith         | sales@villagetoys.com |
| Fun4All       | Jim Jones          | jjones@fun4all.com    |
| The Toy Store | Kim Howard         | NULL                  |
| Fun4All       | Denise L. Stephens | dstephens@fun4all.com |
+---------------+--------------------+-----------------------+

• 分析

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

为了便于参考，这里给出使用多条WHERE子句而不是UNION的相同查询

• 输入

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

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

提示：UNION的限制
使用UNION组合SELECT语句的数目，SQL没有标准限制。但是，最好是参考一下具体的DBMS文档，了解它是否对UNION能组合的最大语句数目有限制

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

14.2.2 UNION规则

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

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

如果遵守了这些基本规则或限制，则可以将UNION用于任何数据检索操作

14.2.3 包含或取消重复的行

回到14.2.1节，我们看看所用的SELECT语句。注意到在分别执行语句时，第一条SELECT语句返回3行，第二条SELECT语句返回2行。而在用UNION组合两条SELECT语句后，只返回4行而不是5行

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

• 输出

+---------------+--------------------+-----------------------+
| cust_name     | cust_contact       | cust_email            |
+---------------+--------------------+-----------------------+
| Village Toys  | John Smith         | sales@villagetoys.com |
| Fun4All       | Jim Jones          | jjones@fun4all.com    |
| The Toy Store | Kim Howard         | NULL                  |
| Fun4All       | Jim Jones          | jjones@fun4all.com    |
| Fun4All       | Denise L. Stephens | dstephens@fun4all.com |
+---------------+--------------------+-----------------------+

• 分析

使用UNION ALL，DBMS不取消重复的行。因此，这里返回5行，其中有一行出现两次

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

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

• 输出

+---------------+--------------------+-----------------------+
| cust_name     | cust_contact       | cust_email            |
+---------------+--------------------+-----------------------+
| Fun4All       | Denise L. Stephens | dstephens@fun4all.com |
| Fun4All       | Jim Jones          | jjones@fun4all.com    |
| The Toy Store | Kim Howard         | NULL                  |
| Village Toys  | John Smith         | sales@villagetoys.com |
+---------------+--------------------+-----------------------+

• 分析

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

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

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

14.3 小结

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