在上一节课中,我们学会了如何创建数据库、设计表结构,以及如何向表中插入、更新和删除数据。现在,我们已经有了一个包含学生信息和选课记录的数据库,但仅仅存储数据是不够的。真正的价值在于我们能够从这些数据中提取有用的信息,这正是 SQL 查询语句发挥作用的地方。
如果你是一位教务管理员,你需要回答各种各样的问题,比如:哪些学生选择了计算机科学专业?李明选修了哪些课程?有多少学生是在 2024 年入学的?这些问题都需要我们能够从数据库中精确地提取数据。SQL 的 SELECT 语句就像是一把万能钥匙,能够帮助我们打开数据宝库的大门,找到我们需要的任何信息。

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

在实际应用中,我们很少需要查看表中的所有数据。更多时候,我们需要根据某些条件来筛选数据。比如,我们可能只想查看计算机科学专业的学生,或者只想查看 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 <> '计算机科学';
这两个查询的效果是完全一样的。
当我们想要匹配多个可能的值时,使用 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 操作符。比如,我们想要查看所有在 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;
有时候,我们可能不记得完整的值,或者想要查找符合某种模式的数据。比如,我们想要查找所有邮箱地址以 "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 值,而必须使用 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 子句允许我们按照一个或多个列对查询结果进行排序。默认情况下,排序是升序的(从小到大),但我们也可以指定降序排序(从大到小)。
比如,我们想要按照入学年份对学生进行排序:
|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 子句:
|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 | | 明 | 李 |
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 使用,以确保每次查询的结果顺序是一致的。否则,分页的结果可能会不一致。

在关系型数据库中,数据通常被分散在多个表中,每个表存储特定类型的信息。比如,学生信息存储在 student 表中,选课信息存储在 course_enrollment 表中。当我们想要同时查看学生的基本信息和他们的选课情况时,就需要将这两个表连接起来。这就是 JOIN 操作的作用。
JOIN 操作是 SQL 中最重要也最强大的功能之一。它允许我们将多个表中的数据组合在一起,形成更完整、更有用的查询结果。理解 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 来连接 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 会返回左表(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(右连接)与 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 表连接起来。连接多个表时,我们需要确保每个连接都有明确的连接条件。
在连接多个表时,要特别注意连接条件的正确性。错误的连接条件可能导致查询结果不正确,或者产生笛卡尔积(两个表的每一行都与另一个表的每一行组合),这会导致结果集非常大。
在早期的 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 函数用于计算行数。它是最常用的聚合函数之一。比如,我们想要知道 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(最小值)函数。比如,假设我们在 course_enrollment 表中添加了一个 score 列来存储学生的课程成绩,我们想要计算某门课程的平均分:
|-- 假设 course_enrollment 表有 score 列 SELECT AVG(score) AS 平均分 FROM course_enrollment WHERE course_code = 'CS101';
这个查询会计算所有选修 CS101 课程的学生的平均成绩。
当我们想要按照某个列的值进行分组统计时,就需要使用 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 子句与 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 子查询。比如,我们想要查找所有选修了课程的学生:
|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 通常比 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
子查询可以分为两类:相关子查询和非相关子查询。非相关子查询是独立的,可以单独执行;相关子查询依赖于外层查询,不能单独执行。
我们之前看到的标量子查询和 IN 子查询通常是非相关子查询,而 EXISTS 子查询通常是相关子查询。相关子查询对于外层查询的每一行都会执行一次,所以性能可能不如非相关子查询。但在某些情况下,相关子查询是唯一的选择。
在使用子查询时,要注意性能问题。如果子查询返回大量数据,可能会影响查询性能。在某些情况下,使用 JOIN 可能比使用子查询更高效。但子查询通常更容易理解和维护。
通过这一部分的学习,我们掌握了 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' 的记录。
我们也可以使用子查询来实现同样的功能:
|
这两种方法都可以得到正确的结果,但在大多数情况下,使用 JOIN 的性能会更好。