SQL 概述

表（ table）

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

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

模式

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

列（ column）

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

数据类型

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

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

行

表中的一个记录。

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

主键

一列（或几列），其值能够唯一标识表中每一行

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

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

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

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

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

什么是SQL？

SQL（发音为字母 S-Q-L 或 sequel）是 Structured Query Language（结构化查询语言）的缩写。 SQL 是一种专门用来与数据库沟通的语言。与其他语言（如英语或 Java、 C、 PHP 这样的编程语言）不一样， SQL中只有很少的词，这是有意而为的。设计 SQL 的目的是很好地完成一项任务——提供一种从数据库中读写数据的简单有效的方法。

SQL 有哪些优点呢？

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

检索数据

select语句

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

select就是一个关键字

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

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

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

1
2
3
4
5
6
7
8
9
SELECT prod_name
FROM Products;

SELECT prod_name FROM Products;
SELECT

prod_name
FROM
Products;

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

检索单个列

1
2
SELECT prod_name
FROM Products;

检索多个列

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

1
2
SELECT prod_id, prod_name, prod_price
FROM Products;

说明：数据表示
SQL 语句一般返回原始的、无格式的数据，不同 的 DBMS 和客户端显示数据的方式略有不同（如对齐格式不同、小数位数不同）。数据的格式化是表示问题，而不是检索问题。因此，如何表示一般会在显示该数据的应用程序中规定。通常很少直接使用实际检索出的数据没有应用程序提供的格式）。

检索所有列

在实际列名的位置使用星号（ *）通配符可以做到这点，如下所示 

1
2
SELECT *
FROM Products;

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

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

检索不同的值

SELECT 语句返回 9 行（即使表中只有 3 个产品供应商），因为 Products表中有 9 种产品。那么如何检索出不同的值？
办法就是使用 DISTINCT 关键字，顾名思义，它指示数据库只返回不同的值

注意：不能部分使用 DISTINCT
DISTINCT 关键字作用于所有的列，不仅仅是跟在其后的那一列。例如，你指定 SELECT DISTINCT vend_id, prod_price，则 9 行里的
6 行都会被检索出来，因为指定的两列组合起来有 6 个不同的结果。若想看看究竟有什么不同，你可以试一下这样两条语句：

1
2
SELECT DISTINCT vend_id, prod_price FROM Products;
SELECT vend_id, prod_price FROM Products;

限制结果

SQL Server 中使用 SELECT 时，可以用 TOP 关键字来限制最多返回多少行 

1
2
SELECT TOP 5 prod_name
FROM Products;

如果你使用 MySQL、 MariaDB、 PostgreSQL 或者 SQLite，需要使用 LIMIT子句， 

1
2
3
SELECT prod_name
FROM Products
LIMIT 5;

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

1
2
3
SELECT prod_name
FROM Products
LIMIT 5 OFFSET 5;

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

1
2
3
4
5
6
prod_name
-------------------
Rabbit bean bag toy
Raggedy Ann
King doll
Queen doll

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

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

使用注释

输入▼

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

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

下面是另一种形式的行内注释（但这种形式有些 DBMS 不支持 ）。
输入▼

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

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

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

1
2
3
4
/* SELECT prod_name, vend_id
FROM Products; */
SELECT prod_name
FROM Products;

排序检索数据

排序数据

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

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

1
2
3
SELECT prod_name
FROM Products
ORDER BY prod_name;

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

1
2
3
4
5
6
7
8
9
10
11
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 个列，并按其中两个列对结果进行排序——首先按价格，然后按名称排序。
输入▼

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

按列位置排序

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

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

输出▼

1
2
3
4
5
6
7
8
9
10
11
12
13
prod_id prod_price prod_name

------- ---------- --------------------

BNBG02 3.4900 Bird bean bag toy
BNBG01 3.4900 Fish bean bag toy
BNBG03 3.4900 Rabbit bean bag toy
RGAN01 4.9900 Raggedy Ann
BR01 5.9900 8 inch teddy bear
BR02 8.9900 12 inch teddy bear
RYL01 9.4900 King doll
RYL02 9.4900 Queen doll
BR03 11.9900 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 清单中，显然不能使用这项技术。

指定排序方向

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

1
2
3
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 允许数据库管理员在需要时改变这种行为（如果你的数据库包含大量外语字符，可能必须这样做）。
这里的关键问题是，如果确实需要改变这种排序顺序，用简单的 ORDERBY 子句可能做不到。你必须请求数据库管理员的帮助。

过滤数据

使用 WHERE 子句

只检索所需数据需要指
定搜索条件（ search criteria），搜索条件也称为过滤条件（ filter condition）。在 SELECT 语句中，数据根据 WHERE 子句中指定的搜索条件进行过滤。WHERE 子句在表名（ FROM 子句）之后给出，如下所示：
输入▼

1
2
3
SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49;

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

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

WHERE 子句操作符

image-20211218101846299

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

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

范围检查：

要检查某个范围的值，可以使用 BETWEEN 操作符。其语法与其他 WHERE子句的操作符稍有不同，因为它需要两个值，即范围的开始值和结束值。例如， BETWEEN 操作符可用来检索价格在 5 美元和 10 美元之间的所有产品，或在指定的开始日期和结束日期之间的所有日期。在使用 BETWEEN 时，必须指定两个值——所需范围的低端值和高端值。这两个值必须用 AND 关键字分隔。 BETWEEN 匹配范围中所有的值，包括指定的开始值和结束值。

空值检查
在创建表时，表设计人员可以指定其中的列能否不包含值。在一个列不包含值时，称其包含空值 NULL。
NULL：无值（ no value），它与字段包含 0、空字符串或仅仅包含空格不同。
确定值是否为 NULL，不能简单地检查是否等于 NULL。 SELECT 语句有一个特殊的 WHERE 子句，可用来检查具有 NULL 值的列。

高级数据过滤

组合 WHERE 子句

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

AND操作符

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

OR操作符

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

求值顺序

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

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

输出▼

1
2
3
4
5
6
7
8
9
prod_name prod_price

------------------- ----------

Fish bean bag toy 3.4900
Bird bean bag toy 3.4900
Rabbit bean bag toy 3.4900
18 inch teddy bear 11.9900
Raggedy Ann 4.9900

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

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

IN 操作符

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

输入▼

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

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

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

NOT操作符

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

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

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

1
2
3
4
SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;

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

用通配符进行过滤

LIKE 操作符

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

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

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

百分号（%）通配符

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

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

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

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

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

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

需要特别注意，除了能匹配一个或多个字符外， %还能匹配 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 之后的字符（或空格）。更好的解决办法是用函数去掉空格。请参阅第 8 课。

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

下划线（_）通配符

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

方括号（ [ ]）通配符

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

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

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

1
2
3
4
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

使用通配符的技巧

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

创建计算字段

计算字段

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

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

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

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

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

拼接字段

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

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

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

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

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

输出▼

1
2
3
4
5
6
7
-----------------------------------------------------------
Bear Emporium (USA )
Bears R Us (USA )
Doll House Inc. (USA )
Fun and Games (England )
Furball Inc. (USA )
Jouets et ours (France )

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

使用别名

如果仅在 SQL 查询工具中查看一下结果，这样没有什么不好。但是，一个未命名的列不能用于客户端应用中，因为客户端没有办法引用它。为了解决这个问题， SQL 支持列别名。别名（ alias）是一个字段或值的替换名。别名用 AS 关键字赋予。

输入▼

1
2
3
4
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
AS vend_title
FROM Vendors
ORDER BY vend_name;

输出▼

1
2
3
4
5
6
7
8
vend_title
-----------------------------------------------------------
Bear Emporium (USA)
Bears R Us (USA)
Doll House Inc. (USA)
Fun and Games (England)
Furball Inc. (USA)
Jouets et ours (France)

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

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

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

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

执行算术计算

如下汇总物品的价格（单价乘以订购数量）：
输入▼

1
2
3
4
5
6
SELECT prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

输出▼

image-20211218105707457

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

使用函数处理数据

函数

与大多数其他计算机语言一样， SQL 也可以用函数来处理数据。函数一般是在数据上执行的，为数据的转换和处理提供了方便。前一课中用来去掉字符串尾的空格的 RTRIM()就是一个函数。

image-20211218105929045

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

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

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

使用函数

文本处理函数

提示：大写，小写，大小写混合
此时你应该已经知道 SQL函数不区分大小写，因此 upper(), UPPER(),Upper()都可以， substr(), SUBSTR(), SubStr()也都行。随你的喜好，不过注意保持风格一致，不要变来变去，否则你写的程序代码就不好读了。

image-20211218110339050

日期和时间处理函数

数值处理函数

image-20211218111247310

汇总数据

聚集函数

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

image-20211218111639926

AVG()函数

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

注意：只用于单个列
AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个 AVG()函数。只有一个例外是要从多个列计算出一个值时，本课后面会讲到。

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

COUNT()函数

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

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

MAX()函数

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

1
2
SELECT MAX(prod_price) AS max_price
FROM Products;

输出▼

1
2
3
max_price
----------
11.9900

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

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

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

MIN()函数

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

1
2
SELECT MIN(prod_price) AS min_price
FROM Products;

输出▼

1
2
3
min_price
----------
3.4900

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

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

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

SUM()函数

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

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

输出▼

1
2
3
items_ordered
----------
200

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

SUM()也可以用来合计计算值。

在下面的例子中，合计每项物品的item_price*quantity，得出总的订单金额：
输入▼

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

输出▼

1
2
3
total_price
----------
1648.0000

分析▼
函数 SUM(item_price*quantity)返回订单中所有物品价钱之和， WHERE

子句同样保证只统计某个物品订单中的物品。
提示：在多个列上进行计算
如本例所示，利用标准的算术操作符，所有聚集函数都可用来执行多个列上的计算。
说明： NULL 值
SUM()函数忽略列值为 NULL 的行。

聚集不同值

以上 5 个聚集函数都可以如下使用。
对所有行执行计算，指定 ALL 参数或不指定参数（因为 ALL 是默认行为）。
只包含不同的值，指定 DISTINCT 参数。

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

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

输入▼

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

输出▼

1
2
3
avg_price
-----------
4.2400

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

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

组合聚集函数

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

1
2
3
4
5
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;

分组数据

数据分组

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

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

输出▼

1
2
3
num_prods
-----------
4

创建分组

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

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

输出▼

1
2
3
4
5
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 子句指示 DBMS 分组数据，然后对每个组而不是整个结果集进行聚集。

过滤分组

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

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

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

输入▼

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

输出▼

1
2
3
cust_id orders
---------- -----------
1000000001 2

分析▼
这条 SELECT 语句的前三行类似于上面的语句。最后一行增加了 HAVING子句，它过滤 COUNT(*) >= 2（两个以上订单）的那些分组。可以看到， WHERE 子句在这里不起作用，因为过滤是基于分组聚集值，而不是特定行的值

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

输入▼

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

输出▼

1
2
3
4
vend_id   num_prods
-------   -----------
BRS01     3
FNG01     2

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

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

输出▼

1
2
3
4
5
6
7
vend_id num_prods

------- -----------

BRS01 3
DLL01 4
FNG01 2

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

分组和排序

image-20211218125956671

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

SELECT 子句顺序

image-20211218130054616

使用子查询

子查询

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

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

利用子查询进行过滤

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

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

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

1
2
3
SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01';

输出▼

1
2
3
4
order_num
-----------
20007
20008

现在，我们知道了哪个订单包含要检索的物品，下一步查询与订单 20007和 20008 相关的顾客 ID。

利用第 5 课介绍的 IN 子句，编写如下的 SELECT语句：
输入▼

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

输出▼

1
2
3
4
cust_id
----------
1000000004
1000000005

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

1
2
3
4
5
SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01');

输出▼

1
2
3
4
cust_id
----------
1000000004
1000000005

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

1
2
3
SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (1000000004,1000000005);

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

1
2
3
4
5
6
7
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'));

输出▼

1
2
3
4
cust_name                     cust_contact
----------------------------- --------------------
Fun4All                       Denise L. Stephens
The Toy                       Store Kim Howard

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

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

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

作为计算字段使用子查询

使用子查询的另一方法是创建计算字段。假如需要显示 Customers 表中每个顾客的订单总数。订单与相应的顾客 ID 存储在 Orders 表中。
执行这个操作，要遵循下面的步骤：
(1) 从 Customers 表中检索顾客列表；
(2) 对于检索出的每个顾客，统计其在 Orders 表中的订单数目。
正如前两课所述，可以使用 SELECT COUNT(*)对表中的行进行计数，并且通过提供一条 WHERE 子句来过滤某个特定的顾客 ID，仅对该顾客的订单进行计数。例如，下面的代码对顾客 1000000001 的订单进行计数：
输入▼

1
2
3
SELECT COUNT(*) AS orders
FROM Orders
WHERE cust_id = 1000000001;

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

1
2
3
4
5
6
7
SELECT cust_name,
cust_state,
(SELECT COUNT( * )
FROM Orders
WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;

输出▼

1
2
3
4
5
6
7
cust_name                 cust_state orders
------------------------- ---------- ------
Fun4All                     IN          1
Fun4All                     AZ          1
Kids Place                  OH          0
The Toy Store               IL          1
Village Toys                MI          2

联结表

联结

关系表

有一个包含产品目录的数据库表，其中每类物品占一行。对于每一种物品，要存储的信息包括产品描述、价格，以及生产该产品的供应商。现在有同一供应商生产的多种物品，那么在何处存储供应商名、地址、联系方法等供应商信息呢？将这些数据与产品信息分开存储的理由是：

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

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

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

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

image-20211218154431834

image-20211218154449473

这样做的好处是：

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

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

为什么使用联结

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

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

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

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

创建联结

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

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

输出▼

1
2
3
4
5
6
7
8
9
10
11
12
13
vend_name            prod_name           prod_price

-------------------- -------------------- ----------

Doll House Inc.      Fish bean bag toy    3.4900
Doll House Inc.      Bird bean bag toy    3.4900
Doll House Inc.      Rabbit bean bag toy  3.4900
Bears R Us 8 inch    teddy bear           5.9900
Bears R Us 12 inch   teddy bear           8.9900
Bears R Us 18 inch   teddy bear           11.9900
Doll House Inc.      Raggedy Ann          4.9900
Fun and Games        King doll            9.4900
Fun and Games        Queen doll           9.4900

WHERE子句的重要性

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

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

内联结

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

1
2
3
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 文档

创建高级联结

使用表别名

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

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

输入▼

1
2
3
4
5
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 子句以及其他语句部分。

使用不同类型的联结

自联结

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

下面举一个例子。image-20211218155831705

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

输入▼

1
2
3
4
5
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 子句中，以检索出为该公司工作的所有雇员 。

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

1
2
3
4
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 过滤数据，返回所需的数据。

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

自然联结

无论何时对表进行联结，应该至少有一列不止出现在一个表中（被联结的列）。标准的联结（前一课中介绍的内联结）返回所有数据，相同的列甚至多次出现。自然联结排除多次出现，使每一列只返回一次。

怎样完成这项工作呢？答案是，系统不完成这项工作，由你自己完成它。自然联结要求你只能选择那些唯一的列，一般通过对一个表使用通配符（ SELECT *），而对其他表的列使用明确的子集来完成。

外联结

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

在上述例子中，联结包含了那些在相关表中没有关联行的行(可能为null)。这种联结称为外联结。

注意：语法差别
需要注意，用来创建外联结的语法在不同的 SQL 实现中可能稍有不同。下面段落中描述的各种语法形式覆盖了大多数实现，在继续学习之前请参阅你使用的 DBMS 文档，以确定其语法。

下面的 SELECT 语句给出了一个简单的内联结。它检索所有顾客及其订单：
输入▼

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

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

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

输出▼

1
2
3
4
5
6
7
8
9
10
cust_id order_num

---------- ---------

1000000001 20005
1000000001 20009
1000000002 NULL
1000000003 20006
1000000004 20007
1000000005 20008

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

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

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

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

注意： FULL OUTER JOIN 的支持
MariaDB、 MySQL 和 SQLite 不支持 FULL OUTER JOIN 语法。

使用带聚集函数的联结

要检索所有顾客及每个顾客所下的订单数，下面的代码使用 COUNT()函数完成此工作：
输入▼

1
2
3
4
5
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;

输出▼

1
2
3
4
5
6
7
8
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 返回。

使用联结和联结条件

联结及其使用的要点

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

组合查询

组合查询

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

主要有两种情况需要使用组合查询：
在一个查询中从不同的表返回结构数据；
对一个表执行多个查询，按一个查询返回数据。

创建组合查询

利用 UNION，可给出多条SELECT 语句，将它们的结果组合成一个结果集。

使用UNION

假如需要 Illinois、 Indiana 和 Michigan 等美国几个州的所有顾客的报表，还想包括不管位于哪个州的所有的 Fun4All。当然可以利用WHERE 子句来完成此工作，不过这次我们使用 UNION。如上所述，创建 UNION 涉及编写多条 SELECT 语句。首先来看单条语句：
输入▼

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

输入▼

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

将上述两个连结起来输入▼

1
2
3
4
5
6
7
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规则

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

说明：UNION 的列名
如果结合 UNION 使用的 SELECT 语句遇到不同的列名，那么会返回什么名字呢？比如说，如果一条语句是 SELECT prod_name，而另一条语句是 SELECT productname，那么查询结果返回的是什么名字呢？
答案是它会返回第一个名字，举的这个例子就会返回 prod_name，而不管第二个不同的名字。这也意味着你可以对第一个名字使用别名，因而返回一个你想要的名字。
这种行为带来一个有意思的副作用。由于只使用第一个名字，那么想要排序也只能用这个名字。拿我们的例子来说，可以用 ORDER BY prod_name 对结果排序，如果写成 ORDER BY productname 就会出错，因为查询结果里没有叫作 productname 的列。

包含或取消重复的行

UNION 从查询结果集中自动去除了重复的行；换句话说，它的行为与一条 SELECT 语句中使用多个 WHERE 子句条件一样。因为 Indiana 州有一个Fun4All 单位，所以两条 SELECT 语句都返回该行。使用 UNION 时，重复的行会被自动取消。

这是 UNION 的默认行为，如果愿意也可以改变它。事实上，如果想返回所有的匹配行，可使用 UNION ALL 而不是 UNION。

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

对组合查询结果排序

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

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

插入数据

数据插入

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

插入完整的行

输入▼

1
2
3
4
5
6
7
8
9
10
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 语句的更安全（不过更烦琐）的方法如下：
输入▼

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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 语句仍然能正确工作。

提示：总是使用列的列表
不要使用没有明确给出列的 INSERT 语句。给出列能使 SQL 代码继续发挥作用，即使表结构发生了变化。

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

插入部分行

正如所述，使用 INSERT 的推荐方法是明确给出表的列名。使用这种语法，还可以省略列，这表示可以只给某些列提供值，给其他列不提供值。
请看下面的例子：
输入▼

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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语句省略了这两列及其对应的值。

注意：省略列
如果表的定义允许，则可以在 INSERT 操作中省略某些列。省略的列必须满足以下某个条件。
该列定义为允许 NULL 值（无值或空值）。
在表定义中给出默认值。这表示如果不给出值，将使用默认值。

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

插入检索出的数据

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

输入▼

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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 中的列名
为简单起见，这个例子在 INSERT和 SELECT语句中使用了相同的列名。但是，不一定要求列名匹配。事实上， DBMS 一点儿也不关心 SELECT返回的列名。它使用的是列的位置，因此 SELECT 中的第一列（不管其列名）将用来填充表列中指定的第一列，第二列将用来填充表列中指定的第二列，如此等等。

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

从一个表复制到另一个表

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

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

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

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

1
CREATE TABLE CustCopy AS SELECT * FROM Customers;

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

1
SELECT * INTO CustCopy FROM Customers;

更新和删除数据

更新数据

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

下面分别介绍。
注意：不要省略 WHERE 子句
在使用 UPDATE 时一定要细心。因为稍不注意，就会更新表中的所有行。使用这条语句前，请完整地阅读本节。
提示： UPDATE 与安全
在客户端/服务器的 DBMS 中，使用 UPDATE 语句可能需要特殊的安全权限。在你使用 UPDATE 前，应该保证自己有足够的安全权限。

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

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

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

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

提示：在 UPDATE 语句中使用子查询
UPDATE 语句中可以使用子查询，使得能用 SELECT 语句检索出的数据更新列数据。关于子查询及使用的更多内容，请参阅第 11 课。
提示： FROM 关键字
有的 SQL 实现支持在 UPDATE 语句中使用 FROM 子句，用一个表的数据更新另一个表的行。如想知道你的 DBMS 是否支持这个特性，请参阅它的文档。

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

1
2
3
UPDATE Customers
SET cust_email = NULL
WHERE cust_id = 1000000005;

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

删除数据

注意：不要省略 WHERE 子句
在使用 DELETE 时一定要细心。因为稍不注意，就会错误地删除表中所有行。在使用这条语句前，请完整地阅读本节。
提示： DELETE 与安全
在客户端/服务器的 DBMS 中，使用 DELETE 语句可能需要特殊的安全权限。在你使用 DELETE 前，应该保证自己有足够的安全权限。

输入▼

1
2
DELETE FROM Customers
WHERE cust_id = 1000000006;

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

提示：友好的外键
第 12 课介绍了联结，简单联结两个表只需要这两个表中的公用字段。也可以让 DBMS 通过使用外键来严格实施关系（这些定义在附录 A
中）。存在外键时， DBMS 使用它们实施引用完整性。例如要向Products 表中插入一个新产品， DBMS 不允许通过未知的供应商 id插入它，因为 vend_id 列是作为外键连接到 Vendors 表的。那么，这与 DELETE 有什么关系呢？使用外键确保引用完整性的一个好处是，DBMS 通常可以防止删除某个关系需要用到的行。例如，要从Products 表中删除一个产品，而这个产品用在 OrderItems 的已有订单中，那么 DELETE 语句将抛出错误并中止。这是总要定义外键的另一个理由。
提示： FROM 关键字
在某些 SQL 实现中，跟在 DELETE 后的关键字 FROM 是可选的。但是即使不需要，也最好提供这个关键字。这样做将保证SQL代码在DBMS之间可移植。

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

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

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

更新和删除的指导原则

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

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

创建和操纵表

创建表

一般有两种创建表的方法：
多数 DBMS 都具有交互式创建和管理数据库表的工具；
表也可以直接用 SQL 语句操纵，—–create table

表创建基础

下面的 SQL 语句创建本书中所用的 Products 表：
输入▼

1
2
3
4
5
6
7
8
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
);

使用NULL值

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

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

注意：理解 NULL
不要把 NULL 值与空字符串相混淆。 NULL 值是没有值，不是空字符串。
如果指定’’（两个单引号，其间没有字符），这在 NOT NULL 列中是允
许的。空字符串是一个有效的值，它不是无值。 NULL 值用关键字 NULL
而不是空字符串指定。

指定默认值

默认值经常用于日期或时间戳列。例如，通过指定引用系统日期的函数或变量，将系统日期用作默认日期。

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

更新表

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

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

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

1
2
ALTER TABLE Vendors
ADD vend_phone CHAR(20);

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

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

1
2
ALTER TABLE Vendors
DROP COLUMN vend_phone;

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

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

删除表

输入▼

1
DROP TABLE CustCopy;

分析▼
这条语句删除 CustCopy 表（第 15 课中创建的）。删除表没有确认步骤，也不能撤销，执行这条语句将永久删除该表。

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

重命名表

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

使用视图

视图

理解视图的最好方法是看例子。第 12 课用下面的 SELECT 语句从三个表
中检索数据：
输入▼

1
2
3
4
5
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 的虚拟表，则可以如下轻松地检索出相同的数据：
输入▼

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

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

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

为什么使用视图

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

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

视图的规则和限制

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

创建视图

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

说明：视图重命名
删除视图，可以使用 DROP 语句，其语法为 DROP VIEW viewname;。
覆盖（或更新）视图，必须先删除它，然后再重新创建。

利用视图简化复杂的联结

输入▼

1
2
3
4
5
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 的顾客，可如下进行：
输入▼

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

输出▼

1
2
3
4
5
6
cust_name cust_contact

------------------- ------------------

Fun4All Denise L. Stephens
The Toy Store Kim Howard

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

提示：创建可重用的视图
创建不绑定特定数据的视图是一种好办法。例如，上面创建的视图返回订购所有产品而不仅仅是 RGAN01 的顾客（这个视图先创建）。扩展视图的范围不仅使得它能被重用，而且可能更有用。这样做不需要创建和维护多个类似视图。

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

输入▼

1
2
3
4
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'
AS vend_title
FROM Vendors
ORDER BY vend_name

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

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

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

1
SELECT * FROM VendorLocations;

说明： SELECT 约束全部适用
在这一课的前面提到，各种 DBMS 中用来创建视图的语法相当一致。那么，为什么会有多种创建视图的语句版本呢？因为视图只包含一个SELECT 语句，而这个语句的语法必须遵循具体 DBMS 的所有规则和约束，所以会有多个创建视图的语句版本。

用视图过滤不想要的数据

输入▼

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

使用视图与计算字段

输入▼

1
2
3
4
5
6
7
CREATE VIEW OrderItemsExpanded AS
SELECT order_num,
prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM OrderItems

使用存储过程

存储过程

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

为什么要使用存储过程

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

执行存储过程

创建存储过程

下面是该过程的 Oracle 版本：
输入▼

1
2
3
4
5
6
7
8
9
10
CREATE PROCEDURE MailingListCount (
ListCount OUT INTEGER
)
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 例子可以像下面这样：
输入▼

1
2
3
var ReturnValue NUMBER
EXEC MailingListCount(:ReturnValue);
SELECT ReturnValue;

分析▼
这段代码声明了一个变量来保存存储过程返回的任何值，然后执行存储过程，再使用 SELECT 语句显示返回的值。

管理事务处理

事务处理

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

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

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

控制事务处理

输入▼

1
2
3
BEGIN TRANSACTION
...
COMMIT TRANSACTION

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

使用ROLLBACK

输入▼

1
2
DELETE FROM Orders;
ROLLBACK;

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

使用COMMIT

输入▼

1
2
3
4
BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT TRANSACTION

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

使用保留点

要支持回退部分事务，必须在事务处理块中的合适位置放置占位符。这样，如果需要回退，可以回退到某个占位符。在 SQL 中，这些占位符称为保留点。在 MariaDB、 MySQL 和 Oracle 中创建占位符，可使用 SAVEPOINT 语句。

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

使用游标

游标

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

常见的一些选项和特性如下：

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

使用游标

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

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

创建游标

下面是创建此游标的 DB2、 MariaDB、 MySQL 和 SQL Server 版本。
输入▼

1
2
3
4
DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL;

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

使用游标

输入▼

1
OPEN CURSOR CustCursor

分析▼
在处理 OPEN CURSOR 语句时，执行查询，存储检索出的数据以供浏览和滚动。

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

输入▼

1
2
3
4
5
6
7
8
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 语法）中，从第一行到最后一行，对检索出来的数据进行循环：
输入▼

1
2
3
4
5
6
7
8
9
10
11
12
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 使在取不出更多的行时终止处理（退出循环）。这个例子也没有做实际的处理，实际例子中可用具体的处理代码替换省略号

关闭游标

输入▼

1
CLOSE CustCursor

高级 SQL 特性

约束

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

主键

主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且永不改动。换句话说，表中的一列（或多个列）的值唯一标识表中的每一行。这方便了直接或交互地处理表中的行。

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

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

外键

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

Orders 表将录入到系统的每个订单作为一行包含其中。顾客信息存储在Customers 表中。 Orders 表中的订单通过顾客 ID 与 ustomers 表中的特定行相关联。顾客 ID 为 Customers 表的主键，每个顾客都有唯一的ID。订单号为 Orders 表的主键，每个订单都有唯一的订单号。

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

唯一约束

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

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

检查约束

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

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

索引

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

触发器

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

触发器内的代码具有以下数据的访问权：
 INSERT 操作中的所有新数据；
 UPDATE 操作中的所有新数据和旧数据；
 DELETE 操作中删除的数据。
根据所使用的 DBMS 的不同，触发器可在特定操作执行之前或之后执行

常见用途：

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