3 / 15

SQL 查询 | 自在学

SQL 查询

在上一节课中，我们学会了如何创建数据库、设计表结构，以及如何向表中插入、更新和删除数据。现在，我们已经有了一个包含学生信息和选课记录的数据库，但仅仅存储数据是不够的。真正的价值在于我们能够从这些数据中提取有用的信息，这正是 SQL 查询语句发挥作用的地方。

如果你是一位教务管理员，你需要回答各种各样的问题，比如：哪些学生选择了计算机科学专业？李明选修了哪些课程？有多少学生是在 2024 年入学的？这些问题都需要我们能够从数据库中精确地提取数据。SQL 的 SELECT 语句就像是一把万能钥匙，能够帮助我们打开数据宝库的大门，找到我们需要的任何信息。

SQL 查询

在这一部分，我们将深入学习 SQL 查询的各种技巧。从最简单的数据检索开始，逐步掌握如何筛选数据、如何连接多个表、如何进行统计计算，以及如何编写复杂的嵌套查询。这些技能将帮助我们成为真正的数据库查询高手。

SELECT 语句：数据查询的起点

SELECT 语句是 SQL 中最基本也是最重要的语句。它的作用就像是在图书馆中查找书籍一样，告诉数据库我们想要查看哪些信息。SELECT 语句的基本结构非常简单，但它的功能却非常强大。

最简单的查询

让我们从最简单的查询开始。假设我们想要查看 student 表中所有学生的信息，可以使用以下语句：

|
SELECT * FROM student;

这个查询中的 * 符号表示"所有列"，FROM student 指定了我们要查询的表。执行这个查询后，MySQL 会返回 student 表中的所有行和所有列。

|
mysql> SELECT * FROM student;
+------------+------------+-----------+--------+------------+----------------+------------------------+--------------+----------------+
| student_id | first_name | last_name | gender | birth_date | major          | email                  | phone         | enrollment_year |
+------------+------------+-----------+--------+------------+----------------+------------------------+--------------+----------------+
|          1 | 明         | 李        | M      | 2002-03-15 | 计算机科学     | liming@university.edu   | 136-6666-8888 |           2024 |
|          2 | 红         | 王        | F      | 2003-08-22 | 英语文学       | wanghong@university.edu | 138-8888-9999 |           2024 |
|          3 | 强         | 张        | M      | 2002-11-05 | 数学           | zhangqiang@university.edu | 139-7777-6666 |           2023 |
+------------+------------+-----------+--------+------------+----------------+------------------------+--------------+----------------+
3 rows in set (0.02 sec)

虽然使用 * 很方便，但在实际工作中，我们通常只需要查看特定的列。这样做不仅可以让查询结果更加清晰，还能提高查询效率。

选择特定的列

如果我们只想查看学生的姓名和专业，可以这样写：

|
SELECT first_name, last_name, major 
FROM student;

这个查询只选择了我们关心的三列：first_name（名字）、last_name（姓氏）和 major（专业）。执行结果如下：

|
mysql> SELECT first_name, last_name, major FROM student;
+------------+-----------+----------------+
| first_name | last_name | major          |
+------------+-----------+----------------+
| 明         | 李        | 计算机科学     |
| 红         | 王        | 英语文学       |
| 强         | 张        | 数学           |

在 SELECT 语句中，列名之间用逗号分隔。列的顺序决定了它们在结果中出现的顺序，我们可以按照自己的需求来安排列的顺序。

使用列别名

有时候，我们可能希望查询结果中的列名更加友好或者更加符合我们的习惯。比如，我们可能希望将 first_name 显示为"名字"，将 last_name 显示为"姓氏"。这时，我们可以使用 AS 关键字来为列创建别名：

|
SELECT first_name AS 名字, 
       last_name AS 姓氏, 
       major AS 专业
FROM student;

执行这个查询后，结果中的列名就会变成我们指定的别名：

|
mysql> SELECT first_name AS 名字, last_name AS 姓氏, major AS 专业 FROM student;
+--------+--------+----------------+
| 名字   | 姓氏   | 专业           |
+--------+--------+----------------+
| 明     | 李     | 计算机科学     |
| 红     | 王     | 英语文学       |
| 强     |

AS 关键字实际上是可以省略的，我们可以直接写 first_name 名字，效果是一样的。但使用 AS 可以让代码更加清晰易读。

列别名只在查询结果中显示，不会改变表中实际的列名。别名对于生成报告或者让查询结果更容易理解非常有用。

查询表达式

SELECT 语句不仅可以查询表中的列，还可以使用表达式进行计算。比如，我们想要查看每个学生的全名（将名字和姓氏组合在一起），可以使用字符串连接函数：

|
SELECT CONCAT(last_name, first_name) AS 姓名,
       major AS 专业
FROM student;

CONCAT 函数可以将多个字符串连接在一起。在这个例子中，我们将 last_name 和 first_name 连接起来，形成完整的姓名。执行结果如下：

|
mysql> SELECT CONCAT(last_name, first_name) AS 姓名, major AS 专业 FROM student;
+--------+----------------+
| 姓名   | 专业           |
+--------+----------------+
| 李明   | 计算机科学     |
| 王红   | 英语文学       |
| 张强   | 数学           |
+--------+----------------+
3

我们还可以在 SELECT 语句中使用数学表达式。比如，如果我们想要计算每个学生的年龄（假设当前是 2024 年），可以这样写：

|
SELECT CONCAT(last_name, first_name) AS 姓名,
       2024 - enrollment_year AS 在校年数
FROM student;

这个查询会计算每个学生已经在校多少年。对于 2024 年入学的学生，在校年数就是 0；对于 2023 年入学的学生，在校年数就是 1。

WHERE 子句：精确筛选数据

WHERE

在实际应用中，我们很少需要查看表中的所有数据。更多时候，我们需要根据某些条件来筛选数据。比如，我们可能只想查看计算机科学专业的学生，或者只想查看 2024 年入学的学生。这时，我们就需要使用 WHERE 子句。

WHERE 子句就像是给查询添加了一个过滤器，只有满足条件的数据才会出现在结果中。它的语法非常简单：在 FROM 子句后面添加 WHERE 关键字，然后跟上筛选条件。

使用等号进行精确匹配

最简单的筛选条件就是精确匹配。比如，我们想要查看所有计算机科学专业的学生：

|
SELECT first_name, last_name, major, enrollment_year
FROM student
WHERE major = '计算机科学';

这个查询中的 WHERE major = '计算机科学' 表示只选择 major 列的值等于 '计算机科学' 的行。执行结果如下：

|
mysql> SELECT first_name, last_name, major, enrollment_year FROM student WHERE major = '计算机科学';
+------------+-----------+----------------+----------------+
| first_name | last_name | major          | enrollment_year |
+------------+-----------+----------------+----------------+
| 明         | 李        | 计算机科学     |           2024 |
+------------+-----------+----------------+----------------+
1 row in

在 SQL 中，字符串值需要用单引号括起来。如果我们忘记加引号，MySQL 会报错，因为它会把 '计算机科学' 当作列名而不是字符串值。

比较运算符

除了等号，SQL 还提供了其他比较运算符，让我们可以进行更灵活的筛选。这些运算符包括：

= 等于
<> 或 != 不等于
< 小于
> 大于
<= 小于等于
>= 大于等于

比如，我们想要查看所有在 2024 年之前入学的学生：

|
SELECT first_name, last_name, major, enrollment_year
FROM student
WHERE enrollment_year < 2024;

这个查询会返回所有 enrollment_year 小于 2024 的学生记录。执行结果如下：

|
mysql> SELECT first_name, last_name, major, enrollment_year FROM student WHERE enrollment_year < 2024;
+------------+-----------+--------+----------------+
| first_name | last_name | major  | enrollment_year |
+------------+-----------+--------+----------------+
| 强         | 张        | 数学   |           2023 |
+------------+-----------+--------+----------------+
1 row in set

我们也可以使用 <= 来包含边界值。比如，如果我们想要查看所有在 2024 年或之前入学的学生，可以这样写：

|
SELECT first_name, last_name, major, enrollment_year
FROM student
WHERE enrollment_year <= 2024;

逻辑运算符：组合多个条件

很多时候，我们需要同时满足多个条件。比如，我们想要查看所有计算机科学专业且是 2024 年入学的学生。这时，我们可以使用 AND 运算符来组合多个条件：

|
SELECT first_name, last_name, major, enrollment_year
FROM student
WHERE major = '计算机科学' AND enrollment_year = 2024;

这个查询使用了 AND 运算符，表示两个条件都必须满足。只有专业是"计算机科学"并且入学年份是 2024 的学生才会出现在结果中。执行结果如下：

|
mysql> SELECT first_name, last_name, major, enrollment_year FROM student WHERE major = '计算机科学' AND enrollment_year = 2024;
+------------+-----------+----------------+----------------+
| first_name | last_name | major          | enrollment_year |
+------------+-----------+----------------+----------------+
| 明         | 李        | 计算机科学     |           2024 |
+------------+-----------+----------------+----------------+

除了 AND，我们还可以使用 OR 运算符来表示"或者"的关系。比如，我们想要查看所有计算机科学专业或者数学专业的学生：

|
SELECT first_name, last_name, major, enrollment_year
FROM student
WHERE major = '计算机科学' OR major = '数学';

这个查询会返回所有专业是"计算机科学"或者"数学"的学生。执行结果如下：

|
mysql> SELECT first_name, last_name, major, enrollment_year FROM student WHERE major = '计算机科学' OR major = '数学';
+------------+-----------+----------------+----------------+
| first_name | last_name | major          | enrollment_year |
+------------+-----------+----------------+----------------+
| 明         | 李        | 计算机科学     |           2024 |
| 强

我们还可以使用 NOT 运算符来否定一个条件。比如，我们想要查看所有不是计算机科学专业的学生：

|
SELECT first_name, last_name, major, enrollment_year
FROM student
WHERE NOT major = '计算机科学';

这个查询会返回所有专业不是"计算机科学"的学生。我们也可以使用 <> 运算符来达到同样的效果：

|
SELECT first_name, last_name, major, enrollment_year
FROM student
WHERE major <> '计算机科学';

这两个查询的效果是完全一样的。

IN 操作符：匹配多个值

当我们想要匹配多个可能的值时，使用 OR 运算符会让查询变得冗长。比如，如果我们想要查看所有计算机科学、数学或英语文学专业的学生，可以这样写：

|
SELECT first_name, last_name, major, enrollment_year
FROM student
WHERE major = '计算机科学' OR major = '数学' OR major = '英语文学';

虽然这个查询可以工作，但使用 IN 操作符会让它更加简洁：

|
SELECT first_name, last_name, major, enrollment_year
FROM student
WHERE major IN ('计算机科学', '数学', '英语文学');

IN 操作符后面跟着一个用括号括起来的值列表，只要列的值在这个列表中，就会被选中。执行结果如下：

|
mysql> SELECT first_name, last_name, major, enrollment_year FROM student WHERE major IN ('计算机科学', '数学', '英语文学');
+------------+-----------+----------------+----------------+
| first_name | last_name | major          | enrollment_year |
+------------+-----------+----------------+----------------+
| 明         | 李        | 计算机科学     |           2024 |
| 红

IN 操作符也可以与 NOT 结合使用，表示不在列表中的值。比如，我们想要查看所有专业不在指定列表中的学生：

|
SELECT first_name, last_name, major, enrollment_year
FROM student
WHERE major NOT IN ('计算机科学', '数学');

BETWEEN 操作符：范围查询

当我们想要查询某个范围内的值时，可以使用 BETWEEN 操作符。比如，我们想要查看所有在 2023 年到 2024 年之间入学的学生：

|
SELECT first_name, last_name, major, enrollment_year
FROM student
WHERE enrollment_year BETWEEN 2023 AND 2024;

BETWEEN 操作符是包含边界的，也就是说，这个查询会返回所有 enrollment_year 大于等于 2023 且小于等于 2024 的学生。执行结果如下：

|
mysql> SELECT first_name, last_name, major, enrollment_year FROM student WHERE enrollment_year BETWEEN 2023 AND 2024;
+------------+-----------+----------------+----------------+
| first_name | last_name | major          | enrollment_year |
+------------+-----------+----------------+----------------+
| 明         | 李        | 计算机科学     |           2024 |
| 红         |

我们也可以使用 NOT BETWEEN 来查询不在指定范围内的值。BETWEEN 操作符实际上等价于使用 >= 和 <= 的组合，所以上面的查询也可以写成：

|
SELECT first_name, last_name, major, enrollment_year
FROM student
WHERE enrollment_year >= 2023 AND enrollment_year <= 2024;

LIKE 操作符：模式匹配

有时候，我们可能不记得完整的值，或者想要查找符合某种模式的数据。比如，我们想要查找所有邮箱地址以 "wang" 开头的学生。这时，我们可以使用 LIKE 操作符配合通配符来进行模式匹配。

SQL 提供了两个通配符：% 表示任意数量的字符（包括零个字符），_ 表示单个字符。比如，我们想要查找所有姓氏以"王"开头的学生：

|
SELECT first_name, last_name, email
FROM student
WHERE last_name LIKE '王%';

这个查询中的 '王%' 表示以"王"开头，后面跟任意字符的字符串。执行结果如下：

|
mysql> SELECT first_name, last_name, email FROM student WHERE last_name LIKE '王%';
+------------+-----------+------------------------+
| first_name | last_name | email                  |
+------------+-----------+------------------------+
| 红         | 王        | wanghong@university.edu |
+------------+-----------+------------------------+
1 row in set (0.00 sec)

我们也可以使用 _ 通配符来匹配单个字符。比如，我们想要查找所有邮箱地址的格式是 "xxx@university.edu" 的学生（其中 xxx 是三个字符）：

|
SELECT first_name, last_name, email
FROM student
WHERE email LIKE '___@university.edu';

这个查询中的 ___ 表示三个任意字符。需要注意的是，LIKE 操作符是区分大小写的，但我们可以使用 LOWER() 或 UPPER() 函数来忽略大小写。

在使用 LIKE 操作符时，如果我们需要匹配字面上的 % 或 _ 字符，可以使用转义字符。比如，LIKE '50\%' 会匹配字符串 "50%"，而不是以 "50" 开头的任意字符串。

NULL 值的处理

在数据库中，NULL 表示缺失或未知的值。处理 NULL 值需要特别注意，因为 NULL 不等于任何值，包括它自己。我们不能使用 = 或 <> 来比较 NULL 值，而必须使用 IS NULL 或 IS NOT NULL。

比如，我们想要查找所有还没有填写邮箱的学生：

|
SELECT first_name, last_name, email
FROM student
WHERE email IS NULL;

如果我们想要查找所有已经填写了邮箱的学生，可以这样写：

|
SELECT first_name, last_name, email
FROM student
WHERE email IS NOT NULL;

需要注意的是，任何与 NULL 进行的比较操作（包括 = NULL、<> NULL、< NULL 等）都会返回 NULL（在 SQL 中被视为假），而不是真或假。这就是为什么我们必须使用 IS NULL 和 IS NOT NULL 的原因。

排序和限制结果

当我们从数据库中查询数据时，结果的顺序通常是不确定的。如果我们希望结果按照某种特定的顺序排列，就需要使用 ORDER BY 子句。同时，如果结果集很大，我们可能只想查看前几条记录，这时可以使用 LIMIT 子句。

ORDER BY：排序查询结果

ORDER BY 子句允许我们按照一个或多个列对查询结果进行排序。默认情况下，排序是升序的（从小到大），但我们也可以指定降序排序（从大到小）。

比如，我们想要按照入学年份对学生进行排序：

|
SELECT first_name, last_name, major, enrollment_year
FROM student
ORDER BY enrollment_year;

这个查询会按照 enrollment_year 列的值从小到大排序。执行结果如下：

|
mysql> SELECT first_name, last_name, major, enrollment_year FROM student ORDER BY enrollment_year;
+------------+-----------+----------------+----------------+
| first_name | last_name | major          | enrollment_year |
+------------+-----------+----------------+----------------+
| 强         | 张        | 数学           |           2023 |
| 明         | 李        | 计算机科学

如果我们想要按照降序排序（从大到小），可以在列名后面添加 DESC 关键字：

|
SELECT first_name, last_name, major, enrollment_year
FROM student
ORDER BY enrollment_year DESC;

我们也可以按照多个列进行排序。比如，我们想要先按照入学年份降序排序，然后按照姓氏升序排序：

|
SELECT first_name, last_name, major, enrollment_year
FROM student
ORDER BY enrollment_year DESC, last_name ASC;

在这个查询中，ASC 表示升序（这是默认值，可以省略），DESC 表示降序。执行结果如下：

|
mysql> SELECT first_name, last_name, major, enrollment_year FROM student ORDER BY enrollment_year DESC, last_name ASC;
+------------+-----------+----------------+----------------+
| first_name | last_name | major          | enrollment_year |
+------------+-----------+----------------+----------------+
| 明         | 李        | 计算机科学     |           2024 |
| 红         |

LIMIT：限制返回的行数

有时候，我们可能只需要查看查询结果的前几条记录。比如，我们想要查看入学年份最早的三名学生。这时，我们可以使用 LIMIT 子句：

|
SELECT first_name, last_name, major, enrollment_year
FROM student
ORDER BY enrollment_year
LIMIT 3;

这个查询会先按照入学年份排序，然后只返回前 3 条记录。执行结果如下：

|
mysql> SELECT first_name, last_name, major, enrollment_year FROM student ORDER BY enrollment_year LIMIT 3;
+------------+-----------+--------+----------------+
| first_name | last_name | major  | enrollment_year |
+------------+-----------+--------+----------------+
| 强         | 张        | 数学   |           2023 |
| 明         | 李        |

OFFSET：跳过指定数量的行

LIMIT 子句还可以配合 OFFSET 使用，实现分页功能。OFFSET 表示跳过前面的多少行。比如，我们想要查看第二页的数据（假设每页显示 2 条记录）：

|
SELECT first_name, last_name, major, enrollment_year
FROM student
ORDER BY enrollment_year
LIMIT 2 OFFSET 2;

这个查询会跳过前 2 条记录，然后返回接下来的 2 条记录。我们也可以使用简写形式：

|
SELECT first_name, last_name, major, enrollment_year
FROM student
ORDER BY enrollment_year
LIMIT 2, 2;

这里的 LIMIT 2, 2 表示跳过 2 行，返回 2 行。第一个数字是偏移量，第二个数字是要返回的行数。

在使用 LIMIT 和 OFFSET 进行分页时，通常需要配合 ORDER BY 使用，以确保每次查询的结果顺序是一致的。否则，分页的结果可能会不一致。

JOIN 操作：连接多个表

在关系型数据库中，数据通常被分散在多个表中，每个表存储特定类型的信息。比如，学生信息存储在 student 表中，选课信息存储在 course_enrollment 表中。当我们想要同时查看学生的基本信息和他们的选课情况时，就需要将这两个表连接起来。这就是 JOIN 操作的作用。

JOIN 操作是 SQL 中最重要也最强大的功能之一。它允许我们将多个表中的数据组合在一起，形成更完整、更有用的查询结果。理解 JOIN 的工作原理，就像是学会了如何将散落的拼图碎片组合成一幅完整的图画。

为什么需要 JOIN？

让我们先看一个简单的例子。假设我们想要查看每个学生选修了哪些课程。如果我们只查询 course_enrollment 表，会得到这样的结果：

|
SELECT student_id, course_code, course_name, semester
FROM course_enrollment;

|
mysql> SELECT student_id, course_code, course_name, semester FROM course_enrollment;
+------------+-------------+------------------------+----------+
| student_id | course_code | course_name            | semester |
+------------+-------------+------------------------+----------+
|          1 | CS101       | 计算机程序设计基础     | 2024春季 |
|          1 | MATH201     | 高等数学               | 2024春季 |

这个结果显示了选课信息，但我们只能看到 student_id，看不到学生的姓名。如果我们想要同时看到学生的姓名和选课信息，就需要将 student 表和 course_enrollment 表连接起来。

INNER JOIN：内连接

INNER JOIN 是最常用的连接类型。它只返回两个表中都有匹配记录的行。让我们看看如何使用 INNER JOIN 来连接 student 表和 course_enrollment 表：

|
SELECT s.first_name, s.last_name, ce.course_code, ce.course_name, ce.semester
FROM student s
INNER JOIN course_enrollment ce ON s.student_id = ce.student_id;

在这个查询中，我们使用了表别名 s 代表 student 表，ce 代表 course_enrollment 表。ON 关键字后面指定了连接条件：s.student_id = ce.student_id，这表示只有当两个表中的 student_id 相等时，才会将这两行连接在一起。

执行结果如下：

|
mysql> SELECT s.first_name, s.last_name, ce.course_code, ce.course_name, ce.semester FROM student s INNER JOIN course_enrollment ce ON s.student_id = ce.student_id;
+------------+-----------+-------------+------------------------+----------+
| first_name | last_name | course_code | course_name            | semester |
+------------+-----------+-------------+------------------------+----------+
| 明         | 李        | CS101

现在我们可以看到每个学生的姓名以及他们选修的课程了。INNER JOIN 的工作原理可以用下面的图来理解：

INNER JOIN 只返回两个表中都有匹配的行。如果某个学生在 student 表中存在，但在 course_enrollment 表中没有选课记录，那么这个学生不会出现在结果中。同样，如果 course_enrollment 表中有某个 student_id 在 student 表中不存在（虽然由于外键约束这不应该发生），这条记录也不会出现在结果中。

LEFT JOIN：左连接

有时候，我们可能想要查看所有学生的信息，即使他们没有选课记录。这时，我们可以使用 LEFT JOIN（左连接）。LEFT JOIN 会返回左表（JOIN 关键字左边的表）中的所有行，即使右表中没有匹配的行。

|
SELECT s.first_name, s.last_name, ce.course_code, ce.course_name, ce.semester
FROM student s
LEFT JOIN course_enrollment ce ON s.student_id = ce.student_id;

这个查询会返回 student 表中的所有学生，即使他们没有选课记录。对于没有选课的学生，course_code、course_name 和 semester 列会显示为 NULL。执行结果如下：

|
mysql> SELECT s.first_name, s.last_name, ce.course_code, ce.course_name, ce.semester FROM student s LEFT JOIN course_enrollment ce ON s.student_id = ce.student_id;
+------------+-----------+-------------+------------------------+----------+
| first_name | last_name | course_code | course_name            | semester |
+------------+-----------+-------------+------------------------+----------+
| 明         | 李        | CS101

我们可以看到，张强没有选课记录，所以他的课程相关列都显示为 NULL。LEFT JOIN 的工作原理可以用下面的图来理解：

如果我们想要查找所有没有选课的学生，可以在 LEFT JOIN 的基础上添加 WHERE 条件：

|
SELECT s.first_name, s.last_name
FROM student s
LEFT JOIN course_enrollment ce ON s.student_id = ce.student_id
WHERE ce.student_id IS NULL;

这个查询会返回所有在 course_enrollment 表中没有匹配记录的学生。

RIGHT JOIN：右连接

RIGHT JOIN（右连接）与 LEFT JOIN 相反，它会返回右表（JOIN 关键字右边的表）中的所有行，即使左表中没有匹配的行。在实际应用中，RIGHT JOIN 使用得比较少，因为我们可以通过调换表的顺序，使用 LEFT JOIN 来达到同样的效果。

|
SELECT s.first_name, s.last_name, ce.course_code, ce.course_name
FROM student s
RIGHT JOIN course_enrollment ce ON s.student_id = ce.student_id;

这个查询会返回 course_enrollment 表中的所有记录，即使对应的学生在 student 表中不存在（虽然由于外键约束这不应该发生）。

多表连接

我们不仅可以连接两个表，还可以连接多个表。比如，假设我们有一个 course 表，存储了课程的详细信息，包括课程编号、课程名称、学分等。如果我们想要查看每个学生选修的课程及其详细信息，就需要连接三个表：

|
-- 假设我们有 course 表
SELECT s.first_name, s.last_name, c.course_code, c.course_name, c.credits
FROM student s
INNER JOIN course_enrollment ce ON s.student_id = ce.student_id
INNER JOIN course c ON ce.course_code = c.

这个查询首先将 student 表和 course_enrollment 表连接起来，然后再将结果与 course 表连接起来。连接多个表时，我们需要确保每个连接都有明确的连接条件。

在连接多个表时，要特别注意连接条件的正确性。错误的连接条件可能导致查询结果不正确，或者产生笛卡尔积（两个表的每一行都与另一个表的每一行组合），这会导致结果集非常大。

JOIN 的另一种语法

在早期的 SQL 标准中，连接操作是使用 WHERE 子句来完成的，而不是使用 JOIN 关键字。这种语法现在仍然被支持，但不太推荐使用，因为它不够清晰。下面是一个例子：

|
-- 使用 WHERE 子句的连接（不推荐）
SELECT s.first_name, s.last_name, ce.course_code, ce.course_name
FROM student s, course_enrollment ce
WHERE s.student_id = ce.student_id;

这个查询与使用 INNER JOIN 的查询效果相同，但使用 JOIN 关键字可以让查询的意图更加明确，特别是在连接多个表时。

聚合函数和分组

到目前为止，我们学习的查询都是返回表中的原始数据。但在实际应用中，我们经常需要对数据进行统计和汇总。比如，我们可能想知道有多少学生选择了某门课程，或者每个专业有多少学生。这时，我们就需要使用聚合函数和 GROUP BY 子句。

聚合函数和分组

聚合函数可以对一组值进行计算，并返回单个值。SQL 提供了多个聚合函数，包括 COUNT、SUM、AVG、MAX 和 MIN。这些函数就像是数学中的统计工具，帮助我们理解数据的整体特征。

COUNT：计数

COUNT 函数用于计算行数。它是最常用的聚合函数之一。比如，我们想要知道 student 表中有多少学生：

|
SELECT COUNT(*) AS 学生总数
FROM student;

这个查询会返回 student 表中的总行数。COUNT(*) 会计算所有行，包括包含 NULL 值的行。执行结果如下：

|
mysql> SELECT COUNT(*) AS 学生总数 FROM student;
+------------+
| 学生总数   |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)

如果我们想要计算某个非 NULL 值的数量，可以指定列名。比如，我们想要知道有多少学生填写了邮箱：

|
SELECT COUNT(email) AS 有邮箱的学生数
FROM student;

COUNT(email) 只会计算 email 列不为 NULL 的行数。

SUM、AVG、MAX、MIN：数值统计

对于数值类型的列，我们可以使用 SUM（求和）、AVG（平均值）、MAX（最大值）和 MIN（最小值）函数。比如，假设我们在 course_enrollment 表中添加了一个 score 列来存储学生的课程成绩，我们想要计算某门课程的平均分：

|
-- 假设 course_enrollment 表有 score 列
SELECT AVG(score) AS 平均分
FROM course_enrollment
WHERE course_code = 'CS101';

这个查询会计算所有选修 CS101 课程的学生的平均成绩。

GROUP BY：分组统计

当我们想要按照某个列的值进行分组统计时，就需要使用 GROUP BY 子句。比如，我们想要知道每个专业有多少学生：

|
SELECT major AS 专业, COUNT(*) AS 学生人数
FROM student
GROUP BY major;

这个查询会按照 major 列的值进行分组，然后对每个组计算学生人数。执行结果如下：

|
mysql> SELECT major AS 专业, COUNT(*) AS 学生人数 FROM student GROUP BY major;
+----------------+------------+
| 专业           | 学生人数   |
+----------------+------------+
| 计算机科学     |          1 |
| 英语文学       |          1 |
| 数学           |          1 |

我们也可以按照多个列进行分组。比如，我们想要知道每个专业、每个入学年份有多少学生：

|
SELECT major AS 专业, enrollment_year AS 入学年份, COUNT(*) AS 学生人数
FROM student
GROUP BY major, enrollment_year;

这个查询会先按照 major 分组，然后在每个专业内再按照 enrollment_year 分组。

HAVING：过滤分组结果

HAVING 子句与 WHERE 子句类似，但 WHERE 用于过滤行，而 HAVING 用于过滤分组。比如，我们想要查找学生人数大于 1 的专业：

|
SELECT major AS 专业, COUNT(*) AS 学生人数
FROM student
GROUP BY major
HAVING COUNT(*) > 1;

这个查询会先按照专业分组，然后只返回学生人数大于 1 的专业。需要注意的是，HAVING 子句中可以使用聚合函数，而 WHERE 子句中不能直接使用聚合函数。

如果我们想要同时使用 WHERE 和 HAVING，WHERE 会在分组之前过滤行，HAVING 会在分组之后过滤分组。比如，我们想要查找 2024 年入学的学生中，学生人数大于 1 的专业：

|
SELECT major AS 专业, COUNT(*) AS 学生人数
FROM student
WHERE enrollment_year = 2024
GROUP BY major
HAVING COUNT(*) > 1;

这个查询会先过滤出 2024 年入学的学生，然后按照专业分组，最后只返回学生人数大于 1 的专业。

WHERE 和 HAVING 的区别：WHERE 在分组之前过滤行，不能使用聚合函数；HAVING 在分组之后过滤分组，可以使用聚合函数。理解这个区别对于编写正确的查询非常重要。

子查询：查询中的查询

子查询，也称为嵌套查询或内部查询，是在另一个 SQL 查询内部嵌套的查询。子查询可以出现在 SELECT、FROM、WHERE 或 HAVING 子句中，它允许我们使用一个查询的结果作为另一个查询的条件或数据源。理解子查询就像是学会了如何在一个问题中嵌套另一个问题，从而解决更复杂的数据查询需求。

子查询：查询中的查询

标量子查询

标量子查询是返回单个值的子查询。它通常用在 WHERE 子句中，与比较运算符一起使用。比如，我们想要查找所有入学年份与李明相同的学生：

|
SELECT first_name, last_name, major, enrollment_year
FROM student
WHERE enrollment_year = (
    SELECT enrollment_year
    FROM student
    WHERE first_name = '明' AND last_name = '李'
);

这个查询中的子查询 (SELECT enrollment_year FROM student WHERE first_name = '明' AND last_name = '李') 会先执行，返回李明的入学年份（2024），然后外层查询会查找所有入学年份等于 2024 的学生。执行结果如下：

|
mysql> SELECT first_name, last_name, major, enrollment_year FROM student WHERE enrollment_year = (SELECT enrollment_year FROM student WHERE first_name = '明' AND last_name = '李');
+------------+-----------+----------------+----------------+
| first_name | last_name | major          | enrollment_year |
+------------+-----------+----------------+----------------+
| 明         |

标量子查询必须返回单个值。如果子查询返回多行，MySQL 会报错。如果子查询可能返回多行，我们需要使用其他类型的子查询。

IN 子查询

当我们想要查找某个列的值在另一个查询结果中的行时，可以使用 IN 子查询。比如，我们想要查找所有选修了课程的学生：

|
SELECT first_name, last_name, major
FROM student
WHERE student_id IN (
    SELECT DISTINCT student_id
    FROM course_enrollment
);

这个查询中的子查询会返回所有在 course_enrollment 表中出现过的 student_id，然后外层查询会查找这些学生。DISTINCT 关键字确保每个 student_id 只出现一次。执行结果如下：

|
mysql> SELECT first_name, last_name, major FROM student WHERE student_id IN (SELECT DISTINCT student_id FROM course_enrollment);
+------------+-----------+----------------+
| first_name | last_name | major          |
+------------+-----------+----------------+
| 明         | 李        | 计算机科学     |
| 红         | 王        | 英语文学       |

我们也可以使用 NOT IN 来查找不在子查询结果中的行。比如，我们想要查找所有没有选课的学生：

|
SELECT first_name, last_name, major
FROM student
WHERE student_id NOT IN (
    SELECT DISTINCT student_id
    FROM course_enrollment
);

EXISTS 子查询

EXISTS 子查询用于检查子查询是否返回任何行。如果子查询返回至少一行，EXISTS 返回真；如果子查询不返回任何行，EXISTS 返回假。EXISTS 通常比 IN 更高效，特别是当子查询可能返回大量数据时。

比如，我们想要查找所有选修了课程的学生：

|
SELECT first_name, last_name, major
FROM student s
WHERE EXISTS (
    SELECT 1
    FROM course_enrollment ce
    WHERE ce.student_id = s.student_id
);

这个查询中的子查询使用了 SELECT 1，因为我们只关心子查询是否返回行，而不关心返回什么值。子查询中的 WHERE ce.student_id = s.student_id 是一个相关子查询，它引用了外层查询的 s.student_id。这意味着对于外层查询的每一行，子查询都会执行一次，检查是否存在匹配的记录。

我们也可以使用 NOT EXISTS 来查找不满足条件的行。比如，我们想要查找所有没有选课的学生：

|
SELECT first_name, last_name, major
FROM student s
WHERE NOT EXISTS (
    SELECT 1
    FROM course_enrollment ce
    WHERE ce.student_id = s.student_id
);

子查询作为表

子查询也可以出现在 FROM 子句中，作为一个临时表使用。这种子查询被称为派生表或内联视图。比如，我们想要查找每个学生选修的课程数量：

|
SELECT s.first_name, s.last_name, course_count.选课数量
FROM student s
INNER JOIN (
    SELECT student_id, COUNT(*) AS 选课数量
    FROM course_enrollment
    GROUP BY student_id
) AS course_count ON s.student_id = course_count.student_id;

这个查询中的子查询 (SELECT student_id, COUNT(*) AS 选课数量 FROM course_enrollment GROUP BY student_id) 会先执行，创建一个包含每个学生及其选课数量的临时表，然后外层查询将这个临时表与 student 表连接起来。执行结果如下：

|
mysql> SELECT s.first_name, s.last_name, course_count.选课数量 FROM student s INNER JOIN (SELECT student_id, COUNT(*) AS 选课数量 FROM course_enrollment GROUP BY student_id) AS course_count ON s.student_id = course_count.student_id;
+------------+-----------+------------+
| first_name | last_name | 选课数量   |
+------------+-----------+------------+
| 明         | 李        |          3

小结

通过这一部分的学习，我们掌握了 SQL 查询的核心技能。从最简单的 SELECT 语句开始，我们逐步学会了如何筛选数据、如何排序和限制结果、如何连接多个表、如何进行统计计算，以及如何编写复杂的嵌套查询。

SELECT 语句是 SQL 的基础，它允许我们从数据库中提取数据。WHERE 子句帮助我们精确筛选需要的数据，ORDER BY 和 LIMIT 让我们能够控制结果的顺序和数量。JOIN 操作是关系型数据库的核心，它让我们能够将分散在多个表中的数据组合在一起，形成完整的信息视图。

聚合函数和 GROUP BY 子句让我们能够对数据进行统计和汇总，这对于生成报告和分析数据非常重要。HAVING 子句允许我们在分组之后进一步过滤结果。子查询则让我们能够编写更复杂、更灵活的查询，解决各种实际的数据查询需求。

掌握这些技能后，我们就能够应对大多数数据查询任务了。但 SQL 的功能远不止这些，还有窗口函数、CTE（公共表表达式）、存储过程等高级特性等待我们去探索。在实际工作中，我们经常会遇到需要组合使用这些技术的情况。比如，我们可能需要使用 JOIN 连接多个表，然后使用 WHERE 筛选数据，使用 GROUP BY 进行分组统计，最后使用 HAVING 过滤分组结果。

小练习

1. 以下哪个 SQL 语句可以查询 student 表中的所有数据？

2. 关于 WHERE 和 HAVING 子句的区别，以下哪个说法是正确的？

3. 关于 INNER JOIN 的描述，以下哪个是正确的？

4. 关于 COUNT(*) 和 COUNT(列名) 的区别，以下哪个说法是正确的？

5. 关于子查询的描述，以下哪个是正确的？

6. 关于 LEFT JOIN 的描述，以下哪个是正确的？

7. 编写查询：查找每个专业的学生人数

假设我们有一个 student 表，包含以下列：student_id、first_name、last_name、major、enrollment_year。请编写一个 SQL 查询，统计每个专业的学生人数，并按照学生人数降序排列。

答案：

|
SELECT major AS 专业, COUNT(*) AS 学生人数
FROM student
GROUP BY major
ORDER BY COUNT(*) DESC;

解释：

这个查询使用了 GROUP BY 子句按照 major 列进行分组，然后使用 COUNT(*) 聚合函数计算每个专业的学生人数。ORDER BY COUNT(*) DESC 按照学生人数降序排列结果。

如果我们想要只显示学生人数大于 1 的专业，可以在查询中添加 HAVING 子句：

|
SELECT major AS 专业, COUNT(*) AS 学生人数
FROM

8. 编写查询：查找选修了特定课程的学生信息

假设我们有两个表：student 表（包含 student_id、first_name、last_name、major）和 course_enrollment 表（包含 student_id、course_code、course_name）。请编写一个 SQL 查询，查找所有选修了课程代码为 'CS101' 的学生的姓名和专业。

答案：

|
SELECT s.first_name, s.last_name, s.major
FROM student s
INNER JOIN course_enrollment ce ON s.student_id = ce.student_id
WHERE ce.course_code = 'CS101';

解释：

这个查询使用了 INNER JOIN 将 student 表和 course_enrollment 表连接起来。连接条件是 s.student_id = ce.student_id，表示只有当两个表中的 student_id 相等时，才会将这两行连接在一起。

WHERE ce.course_code = 'CS101' 筛选出课程代码为 'CS101' 的记录。

我们也可以使用子查询来实现同样的功能：

SQL 查询

SELECT 语句：数据查询的起点

最简单的查询

选择特定的列

使用列别名

查询表达式

WHERE 子句：精确筛选数据

使用等号进行精确匹配

比较运算符

逻辑运算符：组合多个条件

IN 操作符：匹配多个值

BETWEEN 操作符：范围查询

LIKE 操作符：模式匹配

NULL 值的处理

排序和限制结果

ORDER BY：排序查询结果

LIMIT：限制返回的行数

OFFSET：跳过指定数量的行

JOIN 操作：连接多个表

为什么需要 JOIN？

INNER JOIN：内连接

LEFT JOIN：左连接

RIGHT JOIN：右连接

多表连接

JOIN 的另一种语法

聚合函数和分组

COUNT：计数

SUM、AVG、MAX、MIN：数值统计

GROUP BY：分组统计

HAVING：过滤分组结果

子查询：查询中的查询

标量子查询

IN 子查询

EXISTS 子查询

子查询作为表

相关子查询与非相关子查询

小结

小练习