[[toc]]

了解 Sql

使用的工具

本文档的代码都是在 MySQL 8.0.23 版本下面运行的
MySQL 下载地址 z
navicat 数据库可视化工具

数据库

数据库这个术语的用法很多，（从 SQL 的角度来看），数据库是一个以某种有组织的方式存储的数据集合。最简单的办法是将数据库想象为一个文件柜。这个文件柜是一个存放数据的物理位置，不管数据是什么也不管数据是如何组织的。
如果你使用 navicat 查看数据库，那么你可以在下面查看到数据库

::: warning 注意数据库概念混淆

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

:::

表

拥有了数据库我们就可以把数据存储在表中，表是一个存储数据的容器，它可以是一个文件，也可以是一个数据库中的一个表。

表是一种结构化的文件，可用来存储某种特定类型的数据。

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

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

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

在 navicaat 中，表的表现形式如图

列和数据类型

表由列组成。列存储表中某部分的信息。
列是表中的一个数据单元，它可以是一个字段，也可以是一个索引。

:::tip 列
表中的一个字段。所有表都是由一个或多个列组成的。
:::

在 navicat 中，列的表现形式如图

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

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

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

行

表中的数据是按行存储的，所保存的每个记录存储在自己的行内。

在 navicat 中，行的表现形式如图

上图中的每一行都是表中的一个记录或者称为数据库记录(record)

::: tip 说明：是记录还是行？

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

:::

主键(primary key)

表中每一行都应该有一列（或几列）可以唯一标识自己。用户表可以用用户编号，订单表可以用订单号来标识主键。
唯一标识表中每行的这个列（或这几列）称为主键。主键用来表示一个 特定的行。没有主键，更新或删除表中特定行就极为困难，因为你不能 保证操作只涉及相关的行。

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

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

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

在 navicat 中，主键的表现形式如图，右键表打开设计表

什么是 SQL

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

SQL 有如下的优点。

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

::: tip SQL 扩展知识

许多 DBMS 厂商通过增加语句或指令，对 SQL 进行了扩展。这种扩 展的目的是提供执行特定操作的额外功能或简化方法。虽然这种扩展 很有用，但一般都是针对个别 DBMS 的，很少有两个以上的供应商支 持这种扩展。

标准 SQL 由 ANSI 标准委员会管理，从而称为 ANSI SQL。所有主要 的 DBMS，即使有自己的扩展，也都支持 ANSI SQL。各个实现有自 己的名称，如 PL/SQL、Transact-SQL 等。

本教程 SQL 主要是 ANSI SQL。在使用某种 DBMS 特定的 SQL 时，会特别说明。

:::

检索数据(SELECT)

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

开始练习之前记得下载练习数据

SELECT 语句

select 意思为选择

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

检索单列

1
2

-- 检索 subways 表里面的 area_subway_name 字段
SELECT area_subway_name FROM subways;

上述代码利用 SELECT 语句从 subways 表中检索一个名为 area_subway_name 的列。所需的列名写在 SELECT 关键字之后，FROM 关键字指出从哪个表 中检索数据。

语法总结：

1

SELECT 列名 FROM 表名;

输出结果

::: tip 未排序数据—怎么我执行的结果和图中不一样?

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

:::

如上的一条简单 SELECT 语句将返回表中的所有行。数据没有过滤（过滤将得出结果集的一个子集），也没有排序。以后的章节将会详细讨论。

::: warning SQL 语句的分号

多条 SQL 语句必须以分号（；）分隔。有些 DBMS 不需要在单挑语句后面加分号，但是也有需要要分号的。

总之，加上分号一定没有坏处。

:::

::: tip SQL 语句不区分大小写

SQL 语句不区分大小写，因此 SELECT 与 select 是相同的。 同样，写成 Select 也没有关系。许多 SQL 开发人员喜欢对 SQL 关键字使用大写，而对列名和表名使用小写，

:::

::: tip 使用空格

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

1
2
3
4
5
6
7
8
9
10

SELECT area_subway_name
FROM subways;

SELECT
area_subway_name
FROM
subways;

SELECT area_subway_name FROM subways;
-- 以上都是可以正常运行的,注意以分号结尾来表达一段代码的结束

:::

检索多列

要想从一个表中检索多个列，仍然使用相同的 SELECT 语句。从检索单列一样只不过是要把检索的列名用逗号分隔罢了。

::: tip 小心逗号引发的错误

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

:::

下面的 SELECT 语句从 subways 表中检索 3 列

1
2

-- 检索 subways 表里面的 id,area_subway_name,station 字段
SELECT id,area_subway_name,station FROM subways;

输出结果

检索所有列

和之前的检索单列、多列一样，检索所有列只是把列名换成了通配符（ * ）

1
2

-- 检索 subways 表里面的所有字段
SELECT * FROM subways;

输出结果

::: tip 注意通配符

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

:::

检索不重复的值

之前的练习案例你会发现重复出现了很多相同的值，如何让这些重复的值不出现呢？

办法就是使用 DISTINCT 关键字, distinct 意为不同的、清楚的、确切的，顾名思义，它会返回数据库中不同的值

1
2

-- 检索重复的值
SELECT DISTINCT area_subway_name FROM subways

输出结果,与检索单列对比是不是少了重复的值

::: tip 不能部分使用 DISTINCT

DISTINCT 关键字作用于所有的列，不仅仅是跟在其后的那一列。例如，你指定SELECT DISTINCT id,area_subway_name,station FROM subways;除非指定的 两列完全相同，否则所有的行都会被检索出来。

:::

限制结果

SELECT 语句返回指定表中所有匹配的行，很可能是每一行。如果你只想返回第一行或者一定数量的行，该怎么办呢？

我们可以使用 TOP 但是注意的是在各种数据库中这一 SQL 的实现并不相同。由于本教程使用的是 MySQL 所以只显示 MySQL 的用法

1
2

-- top 选择前面 5 条数据
SELECT * FROM subways LIMIT 10;

1
2

-- top 选择第 5 行往后的 5 行数据 用数学语言表示 (5,10]
SELECT * FROM subways LIMIT 5 OFFSET 5;

注释

在很多编辑器里面你都可以使用快捷键ctrl+/来注释所以本章只是简单是说明有哪些注释可以用的。

1
2
3
4
5

-- 注释
# 注释
/*
多行注释
*/

排序检索数据(ORDER BY )

SQL 语句返回某个数据库表的单个列。是没有特定顺序的。

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

::: 子句 (clause)

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

:::

为了明确地排序用 SELECT 语句检索出的数据，可使用 ORDER BY 子句。

ORDER 意思为命令、顺序。

ORDER BY 子句取一个或多个列的名字，据此对输出进行排序。

1

SELECT area_subway_name FROM subways ORDER BY area_subway_name;

:::tip 注意：ORDER BY 子句的位置

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

:::

按多个列排序

我们有时候的排序是不止一个裂的数据排序。例如， 地铁路线，我们希望先按地铁城市名排序，然后再按照地铁线路，最后按照地铁站排序。

1

SELECT area_subway_name,station,platform FROM subways ORDER BY area_subway_name,station,platform;

按列位置排序

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

1

SELECT area_subway_name,station,platform FROM subways ORDER BY 1,2,3;

可以看到，这里的输出与上面的查询相同，不同之处在于 ORDER BY 子句。

1
2
3
4

/* select 后面选择了 area_subway_name,station,platform 列
order by 1,2,3 代表了,先按 area_subway_name 排序,然后按 station 排序,最后按 platform 排序
*/
SELECT area_subway_name,station,platform FROM subways ORDER BY 1,2,3;

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

指定排序方向 （升序、降序）

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

1
2

-- id 降序排列
SELECT * FROM subways order by id desc;

DESC 关键字只应用到直接位于其前面的列名。

:::warning 在多个列上降序排序

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

:::

，DESC 是 DESCENDING 的缩写，这两个关键字都可以使用。与 DESC 相对的是 ASC（或 ASCENDING），在升序排序时可以指定它。但是你指定也没啥用，因为升序的默认的。

::: tip

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

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

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

:::

过滤数据(WHERE)

使用 WHERE 子句

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

人话就是寻找符合条件的数据

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

1
2

-- 语句使用通配符检索所有的列，但是只会返回 id=1987的行
SELECT * FROM subways WHERE id = 1987;

::: tip 注意：WHERE 子句的位置

在同时使用 ORDER BY 和 WHERE 子句时，应该让 ORDER BY 位于 WHERE 之后，否则将会产生错误（[关于 ORDER BY 的使用](#排序检索数据(ORDER BY ))）

:::

WHERE 子句操作符

我们可以通过子句操作符来获得我们想要的数据

1

select * from 表名 where 列名 运算符 想要的结果

让我们来几个案例来看看。

找出 id 小于 3000 的数据。

1

SELECT * FROM subways WHERE id < 3000;

找出 area_subway_name 不是郑州的数据

1
2
3

SELECT * FROM subways WHERE area_subway_name <> "郑州";
-- 等价
SELECT * FROM subways WHERE area_subway_name != "郑州";

::: tip 何时使用引号

如果将值与字符串类型的 列进行比较，就需要限定引号。用来与数值列进行比较的值不用引号。

:::

::: tip 是!=还是<>？

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

:::

算术运算符

比较运算符

逻辑运算符

范围值检查

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

让我们来检索 id 是 2000 到 2005 之间的数据吧。数学语言表述为[2000,2005]

1
2

-- 范围值检查
SELECT * FROM subways WHERE id BETWEEN 2000 AND 2005;

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

空值检查

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

::: tip NULL

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

:::

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

1
2

-- 空值检查
SELECT * FROM subways WHERE id IS NULL;

这条语句返回所有数据(空的 id 字段)，因为表中没有空的行，所以没有返回数据。

高级数据过滤

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

组合 WHERE 子句

为了进行更强的过滤控制，SQL 允许给出多个 WHERE 子句。

AND 操作符

找出 id 小于 2007 且大于 2000 的数据

1
2

-- AND
SELECT * FROM subways WHERE id > 2000 AND id < 2007;

::: AND

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

:::

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

OR 操作符

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

1
2

-- OR
SELECT * FROM subways WHERE id = 2000 OR id = 2001;

这一段 SQL 语句返回了 id = 2000 或 id = 2001 的数据。

::: tip OR

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

:::

IN 操作符

IN 操作符用来指定条件范围，范围中的每个条件都可以进行匹配。

IN 取 一组由逗号分隔、括在圆括号中的合法值。

1
2

-- IN
SELECT * FROM subways WHERE code IN("SUY|54d7ed78-86ac-7095","SUY|15c420a5-8ea4-9495");

此 SELECT 语句检索 code 列里面符合 IN 操作符后面由逗号分隔的合法值，这些值必须括在圆括号中。

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

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

NOT 操作符

WHERE 子句中的 NOT 操作符有且只有一个功能，那就是否定其后所跟的任何条件。

::: tip NOT

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

:::

1
2

-- NOT
SELECT * FROM subways WHERE area_subway_name NOT IN("郑州","上海") ORDER BY area_subway_name;

返回 area_subway_name 不是 郑州和上海的数据，并进行了 area_subway_name 排序

用通配符进行过滤

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

LIKE 操作符进行模糊查询

前面介绍的所有操作符都是针对已知值进行过滤的。在我们的日常使用中肯定还有未知的，比如寻找姓李的同学有几个之类的。例如下面的例子。

查找姓王的学生

1
2

-- LINK
SELECT * FROM students WHERE sname LIKE "王%";

上面的代码使用了通配符。

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

::: tip 通配符（wildcard）

用来匹配值的一部分的特殊字符。

:::

::: tip 搜索模式（search pattern）

由字面值、通配符或两者组合构成的搜索条件

:::

::: tip 谓词（predicate）

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

:::

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

百分号（%）通配符

最常使用的通配符是百分号（%）。在搜索串中，% 表示任何字符出现任意次数。

1
2

-- LINK
SELECT * FROM students WHERE sname LIKE "王%";

以上代码查找了以王开头的字段。

::: tip Access 通配符

如果使用的是 Microsoft Access，需要使用*而不是%。

:::

::: tip 说明：区分大小写

根据 DBMS 的不同及其配置，搜索可以是区分大小写的。如果区分大 小写，则 “A%” 与 “a%”是不一样的

:::

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

1

SELECT * FROM students WHERE class LIKE "%95033%";

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

::: warning 请注意 NULL

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

:::

下划线（_）通配符

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

::: tip 说明：Access 通配符

如果使用的是 Microsoft Access，需要使用?而不是_。

:::

1
2

-- 下划线（_）通配符
SELECT * FROM subways WHERE id LIKE "__80";

注意使用了两个下划线用来匹配所以是匹配前面任意两个字符后面以 80 结尾的数据。

可以与 % 通配符对比一下

1

SELECT * FROM subways WHERE id LIKE "%80";

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

使用通配符的技巧

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

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

计算字段

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

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

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

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

::: tip 字段（field）

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

:::

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

::: 提示：客户端与服务器的格式

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

:::

拼接字段

我们直接来看案例

1
2

-- 拼接字段
SELECT CONCAT(sno,sname) from students;

如果我们想去掉数据中的前后空格可以使用 TRIM()函数，同时还有 LTRIM( )去掉左空格和 RTIRM( )去掉右空格；

1

SELECT CONCAT(trim(sno),rtrim(sname)) from students;

使用别名

从前面的输出可以看到，SELECT 语句可以很好地拼接地址字段。但是， 这个新计算列的名字是什么呢？实际上它没有名字，它只是一个值。如 果仅在 SQL 查询工具中查看一下结果，这样没有什么不好。但是，一个 未命名的列不能用于客户端应用中，因为客户端没有办法引用它。
为了解决这个问题，SQL 支持列别名。别名（alias）是一个字段或值的 替换名。别名用 AS 关键字赋予。请看下面的 SELECT 语句：

1
2

-- 拼接字段
SELECT CONCAT(sno,sname) as noAndName from students;

执行算术计算

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

查看案例

1
2

-- 执行算术计算
SELECT *,sno*score as product FROM students;

使用函数处理数据

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

函数

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

之前我们见过的 trim() 就是一个函数。

使用函数

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

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

::: danger 8.2

81 页 正在施工中….

:::

MySQL 常用函数

汇总数据

聚集函数

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

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

::: tip 聚集函数（aggregate function）

对某些行运行的函数，计算并返回一个值。

:::

::: tip 列值为 null

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

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

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

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

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

:::

分组数据 GROUP

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

数据分组

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

目前为止的所有计算都是在表的所有数据或匹配特定的 WHERE 子句的数 据上进行的。例如下面的例子

1

SELECT area_subway_name,COUNT(area_subway_name) from subways WHERE area_subway_name = '郑州';

如果我们要返回每个地铁名称出现的次数怎么办?总可能一个一个的去判断吧?

这时候就需要使用到分组了。使用分组可以将数据分为多个逻辑组， 对每个组进行聚集计算。

创建分组

1

SELECT area_subway_name,COUNT(area_subway_name) from subways GROUP BY area_subway_name;

因为使用了 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 子句之前。

过滤分组

除了能用 GROUP BY 分组数据外，SQL 还允许过滤分组，规定包括哪些 分组，排除哪些分组。例如，上一节的例子中我们不想看见郑州的统计次数就可以这样写。

1
2

-- 过滤分组
SELECT area_subway_name,COUNT(area_subway_name) from subways GROUP BY area_subway_name having area_subway_name != '郑州';

还记得上一节,”使用 GROUP BY 子句前的规定”吗?GROUP 子句 where 子句之后,order by 子句之前,而且因为 where 过滤指定的是行而不是分组。事实上，where 没有分组的概念。

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

::: tip 说明：HAVING 和 WHERE 的差别

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

:::

::: tip 说明：使用 HAVING 和 WHERE

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

:::

分组和排序

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

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

::: tip 不要忘记 ORDER BY

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

:::

为说明 GROUP BY 和 ORDER BY 的使用方法，来看一个例子。

1
2

-- 排序和分组
SELECT area_subway_name,COUNT(area_subway_name) from subways GROUP BY area_subway_name having area_subway_name != '郑州' ORDER BY id DESC;

SELECT 子句顺序

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

使用子查询

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

::: tip 查询（query）

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

:::

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

利用子查询进行过滤

比如 student 表中有没有记录 学生的 degree,但是 score 表中记录了,同时 student 表中的 sno 和 score 表中的 sno 有所对应，我们可以使用子查询来找到学生的 degree

1
2

-- 子查询
select sname from students WHERE sno IN (SELECT sno from score) ORDER BY sno;

分析 : 这一条 SQL 语句可以看成 先执行 SELECT sno from score 返回了

然后把值替换进了 select sname from students WHERE sno IN () IN 的里面。

联结表

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

联结

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

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

关系表

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

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

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

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

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

在这个例子中可建立两个表：一个存储供应商信息，另一个存储产品信 息。Vendors 表包含所有供应商信息，每个供应商占一行，具有唯一的 标识。此标识称为主键（primary key），可以是供应商 ID 或任何其他唯 一值。
Products 表只存储产品信息，除了存储供应商 ID（Vendors 表的主键） 外，它不存储其他有关供应商的信息。Vendors 表的主键将 Vendors 表 与 Products 表关联，利用供应商 ID 能从 Vendors 表中找出相应供应 商的详细信息。

这样做的好处是：

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

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

::: tip 可伸缩（scale）

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

:::

为什么使用联结

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

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

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

创建联结

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

1
2

-- 创建联结
SELECT students.sno,students.sname,score.degree FROM students,score WHERE students.sno = score.sno;

我们来看这段代码。SELECT 语句与前面所有语句一样指定要检索的列。

这里最大的差别是所指定的两列（sno 和 sname）在一个 表中，而第三列（degree）在另一个表中。

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

我们注意到要匹配的两列指定为 students.sno 和 students.sno = score.snoscore.sno 这里需要这种完全限定列名，如果只给出 sno，DBMS 就不知道指的是哪一个了。

::: tip 警告：完全限定列名

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

:::

WHERE 子句的重要性

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

1
2

-- WHERE 子句的重要性
SELECT students.sno,students.sname,score.degree FROM students,score;

::: tip 注意：不要忘了 WHERE 子句

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

:::

内联结 INNER JOIN

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

1
2

-- 内连接
SELECT students.sno,students.sname,score.degree FROM students INNER JOIN score ON students.sno = score.sno;

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

联结多个表

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

1

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;

1
2
3

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

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

创建高级联结

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

使用表别名

之前介绍了如何使用别名引用被检索的表列。例如

1

SELECT CONCAT(sno,sname) as noAndName from students;

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

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

请看下面的 SELECT 语句。它与前一课例子中所用的语句基本相同，但 改成了使用别名：

1

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

使用不同类型的联结

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

自联结

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

1
2

-- 自联结
SELECT * FROM students WHERE sname IN (SELECT sname FROM students WHERE ssex = "男");

1

SELECT * FROM students WHERE sname = (SELECT sname FROM students WHERE birthday = "1998-09-01 00:00:00");

::: tip 提示：用自联结而不用子查询

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

:::

自然联结

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

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

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

外联结 OUTER JOIN

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

1
2
3
4

-- 内联结
SELECT DISTINCT sname FROM students INNER JOIN score ON students.sno = score.sno ORDER BY sname;
-- 外联结
SELECT DISTINCT sname FROM students LEFT OUTER JOIN score ON students.sno = score.sno ORDER BY sname;

这条 SELECT 语句使用了关键字 OUTER JOIN 来指定联结类型。但是，与内联结关联两个表中的行不同的是，外联结还包括没有关联行的行。在使用 OUTER JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表 （RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT 指出的是 OUTER JOIN 左边的表）。

上面的例子使用 LEFT OUTER JOIN 从 FROM 子句左边的表 （students 表）中选择所有行。

为了从右边的表中选择所有行，需要使 用 RIGHT OUTER JOIN

1
2

-- 外联结 右
SELECT DISTINCT sname FROM students RIGHT OUTER JOIN score ON students.sno = score.sno ORDER BY sname;

组合查询 UNION

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

组合查询

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

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

• 在一个查询中从不同的表返回结构数据；

• 对一个表执行多个查询，按一个查询返回数据

创建组合查询

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

比如输出 匡明 的总分

1
2

-- 创建组合查询
SELECT sname,SUM(score.degree) AS 总分 FROM students INNER JOIN score ON students.sno = score.sno AND students.sname = "匡明" ORDER BY sname;

输出 王芳的总分

1

SELECT sname,SUM(score.degree) AS 总分 FROM students INNER JOIN score ON students.sno = score.sno AND students.sname = "王芳" ORDER BY sname;

接下来组合这两条语句

1
2
3

SELECT sname,SUM(score.degree) AS 总分 FROM students INNER JOIN score ON students.sno = score.sno AND students.sname = "匡明"
UNION
SELECT sname,SUM(score.degree) AS 总分 FROM students INNER JOIN score ON students.sno = score.sno AND students.sname = "王芳" ORDER BY sname

注意 order by 子句需要在最后的位置，所以去掉了第一段代码的 order by 子句

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

UNION 规则

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

包含或取消重复的行

UNION 默认会去掉重复的行，如果想显示重复的行可以使用

1

UNION ALL

插入数据 INSERT

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

数据插入

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

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

插入完整的行

1
2

-- 插入完整行
INSERT INTO students VALUES(110,"张三","男",NOW(),95033,100);

这段代码将一个新学生插入到 students 表中。。存储到表中每一列的数 据在 VALUES 子句中给出，必须给每一列提供一个值。如果某列没有值则应该使用 NULL 。

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

更安全清晰的方法如下

1

INSERT INTO students(sno,sname,ssex,birthday,class,score) VALUES(110,"张三","男",NOW(),95033,100);

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

参考资料

SQL 必知必会第四版

SQL 基础教程第二版

Mysql 官方文档

SQL 教程 - 廖雪峰的官方网站

练习数据

练习数据 subways 下载

148

单词