MYSQL：联合查询

技术文档

文章目录

联合查询：从多个表中获取信息的艺术
- 1. 为什么需要联合查询？
- - 1.1 联合查询的内部计算过程
  - 1.2 准备练习数据
- 2. 内连接 (INNER JOIN)
- - 2.1 内连接的推荐语法
  - 2.2 内连接示例
- 3. 外连接 (OUTER JOIN)
- - 3.1 示例1：查询所有同学的考试情况（包括未参加考试的）
  - 3.2 示例2：查询所有班级及其学生情况（包括没有学生的班级）
- 4. 自连接
- - 4.1 示例：查询\"MySQL\"成绩比\"Java\"成绩高的学生信息
- 5. 子查询（嵌套查询）
- - 5.1 单行子查询
  - 5.2 多行子查询
  - 5.3 在 FROM 子句中使用子查询
- 6. 合并查询 (UNION)
- - 6.1 示例
- 7. 插入查询结果

联合查询：从多个表中获取信息的艺术

大家好！在数据库的世界里，数据很少是孤立存在的。为了保证设计的合理性，我们通常会把信息拆分到多个相互关联的表中。那么，当我们需要获取一份完整的信息时，就需要一种能将这些表“联合”起来的查询方式。这就是我们今天要深入探讨的主题——联合查询。

通过今天的学习，我们希望能达到以下几个目标：

理解联合查询背后的计算过程，特别是“笛卡尔积”的概念。
熟练掌握最核心的几种连接查询：内连接、左外连接、右外连接和自连接。
学习使用子查询和合并查询来处理更复杂的场景。
掌握如何将查询结果直接插入到其他表中，实现数据的快速迁移。

1. 为什么需要联合查询？

在设计数据库时，我们通常会遵循“范式”原则，将信息拆分到不同的表中以减少数据冗余。例如，学生的基本信息存放在 student 表，而班级的详细信息则存放在 class 表。

MYSQL：联合查询

如果现在需要查询某个学生的姓名和他所在班级的名称，只查单张表是无法完成的。这时，就必须使用联合查询，将 student 表和 class 表“连接”起来，从两张表中共同提取数据，拼接成我们需要的信息。

1.1 联合查询的内部计算过程

理解联合查询在数据库内部是如何工作的，对我们写出高效的SQL至关重要。这个过程大致可以分为两步：

计算笛卡尔积： 首先，数据库会在内存中生成一个庞大的临时表，这个表是所有参与查询的表的“笛卡尔积”。简单来说，就是从第一张表中取出第一条记录，与第二张表中的所有记录逐一组合；接着取出第一张表的第二条记录，再与第二张表的所有记录组合……直到所有记录都匹配完毕。
筛选有效数据： 笛卡尔积包含了大量无意义的组合。因此，第二步就是根据我们提供的连接条件（例如 student.class_id = class.id），从这个临时的笛卡尔积中筛选出那些真正有关联的、有效的记录行。

一点思考： 这个过程也解释了为什么联合查询可能会很慢。参与连接的表越多，或者表中的数据量越大，中间产生的笛卡尔积临时表就会呈爆炸式增长，极大地消耗系统资源。因此，在实际开发中，我们需要谨慎设计查询，避免不必要的表连接。

1.2 准备练习数据

为了更好地学习，我们先准备一些测试数据。这里我们创建四个表：课程表、班级表、学生表和成绩表。

-- 课程表DROP TABLE IF EXISTS course;CREATE TABLE course ( course_id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20));INSERT INTO course (name) VALUES (\'Java\'), (\'C++\'), (\'MySQL\'), (\'操作系统\'), (\'计算机网络\'), (\'数据结构\');-- 班级表DROP TABLE IF EXISTS class;CREATE TABLE class ( class_id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20));INSERT INTO class(name) VALUES (\'Java001班\'), (\'C++001班\'), (\'前端001班\');-- 学生表DROP TABLE IF EXISTS student;CREATE TABLE student ( student_id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL, sno VARCHAR(10) NOT NULL, age INT DEFAULT 18, gender TINYINT(1), enroll_date DATE, class_id BIGINT, FOREIGN KEY (class_id) REFERENCES class(class_id) -- [校正笔记：原文此处外键引用的是 class(id)，已修正为 class(class_id) 以匹配表结构]);INSERT INTO student (name, sno, age, gender, enroll_date, class_id) VALUES(\'唐三藏\', \'100001\', 18, 1, \'1986-09-01\', 1),(\'孙悟空\', \'100002\', 18, 1, \'1986-09-01\', 1),(\'猪悟能\', \'100003\', 18, 1, \'1986-09-01\', 1),(\'沙悟净\', \'100004\', 18, 1, \'1986-09-01\', 1),(\'宋江\', \'200001\', 18, 1, \'2000-09-01\', 2),(\'武松\', \'200002\', 18, 1, \'2000-09-01\', 2),(\'李逵\', \'200003\', 18, 1, \'2000-09-01\', 2), -- [校正笔记：原文为“李逹”，为保持人物系列一致性，修正为“李逵”](\'不吃香菜\', \'200004\', 18, 1, \'2000-09-01\', 2);-- 成绩表 (学生与课程的中间表)DROP TABLE IF EXISTS score;CREATE TABLE score ( score_id BIGINT PRIMARY KEY AUTO_INCREMENT, score FLOAT, student_id BIGINT, course_id BIGINT, FOREIGN KEY (student_id) REFERENCES student(student_id), -- [校正笔记：原文此处外键引用的是 student(id)，已修正为 student(student_id) 以匹配表结构] FOREIGN KEY (course_id) REFERENCES course(course_id));INSERT INTO score (score, student_id, course_id) VALUES(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),(60, 2, 1),(59.5, 2, 5),(33, 3, 1),(68, 3, 3),(99, 3, 5),(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),(81, 5, 1),(37, 5, 5),(56, 6, 2),(43, 6, 4),(79, 6, 6),(80, 7, 2),(92, 7, 6);

2. 内连接 (INNER JOIN)

内连接只返回两张表中能够通过连接条件完全匹配上的记录。 这是最常用的一种连接方式。

我们通过一个实例来走一遍内连接查询的完整流程。

需求： 查询学生姓名为“不吃香菜”的详细信息，包括其个人信息和所在的班级信息。

第一步：确定参与查询的表
我们需要 student 表和 class 表。

第二步：添加连接条件，筛选有效数据
关联的桥梁是 student 表的 class_id 和 class 表的 class_id。

第三步：添加业务筛选条件并精简字段
筛选出姓名为“不吃香菜”的记录，并只选择我们关心的字段。

第四步：使用别名简化 SQL
当 SQL 语句越来越长时，为表指定别名（如 student s, class c）能让语句更清爽。

-- 最终的查询语句SELECT s.student_id, s.name, s.sno, s.age, s.gender, s.enroll_date, c.name AS class_name -- 为班级名称列指定别名，避免与学生姓名字段混淆FROM student s, class cWHERE s.class_id = c.class_id -- 连接条件AND s.name = \'不吃香菜\'; -- 业务筛选条件

MYSQL：联合查询

2.1 内连接的推荐语法

我们刚刚使用的 FROM table1, table2 WHERE ... 的写法被称为“隐式内连接”。虽然它能工作，但我们强烈推荐使用 JOIN ... ON 的显式语法。

-- 隐式内连接 (旧式写法)SELECT 字段 FROM 表1, 表2 WHERE 连接条件 AND 其他条件;-- 显式内连接 (推荐写法)SELECT 字段 FROM 表1 JOIN 表2 ON 连接条件 WHERE 其他条件;

INNER 关键字通常可以省略。JOIN ... ON 的语法能将“连接条件”和“业务筛选条件”清晰地分离开，让 SQL 的结构更规范，可读性更好。

2.2 内连接示例

示例1：查询\"唐三藏\"同学的所有成绩

-- 使用 JOIN ... ON 语法SELECT s.name, sc.scoreFROM student sJOIN score sc ON s.student_id = sc.student_id -- 连接条件WHERE s.name = \'唐三藏\'; -- 筛选条件

MYSQL：联合查询

示例2：查询所有同学的总成绩及个人信息

-- 使用 SUM() 聚合函数和 GROUP BY 分组SELECT s.name, SUM(sc.score) AS total_score -- 使用别名让结果更清晰FROM student sJOIN score sc ON s.student_id = sc.student_idGROUP BY s.student_id, s.name; -- 按学生分组，确保每个学生只出现一次

MYSQL：联合查询

关于 GROUP BY 的说明：

GROUP BY 在这个查询里的核心作用是**“分组”**，它告诉 SUM() 这类聚合函数应该如何计算。

如果没有 GROUP BY：SUM(sc.score) 会计算表中所有成绩的总和，得到一个全校总分。

加上 GROUP BY s.student_id 后：这句指令就像在命令数据库：“在计算前，先按 student_id 把成绩记录分一下组”。于是，所有 student_id 为 1 的成绩归为一组，为 2 的归为另一组，以此类推。

分组完成后，SUM() 就会对每一个小组分别进行计算，最终得到每个学生各自的总分。所以，GROUP BY 在这里的作用就是为聚合函数划定计算范围。

为什么 s.name 可以直接出现在 SELECT 中？

这和 MySQL 内部执行 SQL 的顺序有关。一个查询语句的逻辑执行顺序大致是

FROM -> JOIN ON-> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT->ORDER BY->LIMIT。

核心原因在于：GROUP BY s.student_id 在 SELECT 之前执行，已经保证了每个分组内的 s.name 都是唯一的。 既然我们是按唯一的学生ID分的组，那么每个组里所有记录的 name 必然是相同的。MySQL 识别到了这种“函数依赖”关系，因此在后续的 SELECT 阶段，允许我们直接查询 s.name。

示例3：查询所有同学每门课的成绩及个人信息（三表连接）

分析思路（编写顺序）：

我们思考和编写复杂SQL的顺序，和最终SQL语句的呈现顺序通常是不同的。

明确目标： 我们需要“学生个人信息”、“课程信息”和“成绩”。

定位数据源： 这三样信息分别在 student、course 和 score 表中。

搭建连接框架 (FROM 和 JOIN)： 这是最核心的一步。

先从 student 表开始: FROM student s

然后思考 student 如何与 score 关联，通过 student_id：JOIN score sc ON s.student_id = sc.student_id

最后思考如何将 course 关联进来，通过 course_id：JOIN course c ON c.course_id = sc.course_id

挑选显示列 (SELECT)： 连接框架搭好后，再回头从 s, sc, c 三个表中挑选需要的字段。

排序 (ORDER BY)： 最后，为了让结果更规整，添加排序条件。

-- 连接三张表：student, score, courseSELECT s.student_id, s.name AS 姓名, s.sno AS 学号, c.name AS 课程, sc.score AS 分数FROM student sJOIN score sc ON s.student_id = sc.student_idJOIN course c ON c.course_id = sc.course_idORDER BY s.student_id;

MYSQL：联合查询

结果分析： 我们会发现，结果集中没有\"不吃香菜\"同学的任何成绩记录。这是内连接的特性决定的：因为 score 表中没有这位同学的记录，所以在连接时无法匹配上，最终就被过滤掉了。

3. 外连接 (OUTER JOIN)

为了解决内连接可能“丢失”数据的问题（如上例中“消失”的“不吃香菜”同学），我们需要使用外连接。

外连接的核心思想是：以一张表为“基准”，保证这张基准表的记录全部出现在结果中，再去尝试匹配另一张表的数据。 如果找不到匹配项，对应的字段就用 NULL 填充。

左外连接 (LEFT JOIN): 以 JOIN 关键字左边的表为基准。
右外连接 (RIGHT JOIN): 以 JOIN 关键字右边的表为基准。

3.1 示例1：查询所有同学的考试情况（包括未参加考试的）

需求核心： 我们要看的是“所有同学”的情况，所以 student 表是基准。我们应该把它放在 JOIN 的左边，并使用 LEFT JOIN。

-- 使用 LEFT JOIN，以 student 表为基准SELECT s.student_id, s.name, s.sno, sc.score, sc.course_idFROM student sLEFT JOIN score sc ON s.student_id = sc.student_id;

MYSQL：联合查询

结果解读： “不吃香菜”同学终于出现了！他对应的 score 和 course_id 字段都为 NULL，这清晰地告诉我们：“这位同学存在，但他没有匹配的成绩记录”。

利用这个特点，我们就可以轻松筛选出所有“没有参加考试的同学”。

-- 在左连接的基础上，筛选出 score 字段为 NULL 的记录SELECT s.*FROM student sLEFT JOIN score sc ON s.student_id = sc.student_idWHERE sc.score IS NULL; -- 注意：判断是否为NULL要用 IS NULL

MYSQL：联合查询

3.2 示例2：查询所有班级及其学生情况（包括没有学生的班级）

需求核心： 我们要查询的是“所有班级”的情况，所以 class 表是基准。

-- 使用 RIGHT JOIN，以 class 表为基准SELECT s.name AS student_name, c.name AS class_nameFROM student sRIGHT JOIN class c ON c.class_id = s.class_id;

MYSQL：联合查询

结果解读： “前端001班”成功地显示了出来，它对应的 student_name 字段是 NULL，这说明没有任何学生关联到这个班级。

4. 自连接

自连接是一种特殊的连接，它指的是一张表与它自己进行连接。这听起来有点奇怪，但它在处理一些特定问题时非常有用，比如实现同一张表中行与行之间的比较。

进行自连接时，必须为表指定不同的别名，否则数据库会无法区分。

4.1 示例：查询\"MySQL\"成绩比\"Java\"成绩高的学生信息

分析思路：
我们需要比较同一个学生的两门课成绩。可以想象我们有两份一模一样的 score 表，一份叫 s1（代表MySQL成绩），另一份叫 s2（代表Java成绩）。

连接条件： s1.student_id = s2.student_id，确保我们比较的是同一个学生。
筛选条件：
- s1 这份表只看MySQL的成绩 (s1.course_id = 3)。
- s2 这份表只看Java的成绩 (s2.course_id = 1)。
- s1.score > s2.score，这是核心的比较逻辑。

-- 先找出 Java 和 MySQL 的 course_id 分别是 1 和 3SELECT * FROM course WHERE name IN (\'Java\', \'MySQL\');

MYSQL：联合查询

-- 进行自连接查询SELECT s1.*FROM score s1, score s2WHERE s1.student_id = s2.student_id -- 连接同一个学生 AND s1.course_id = 3 -- s1 代表 MySQL 成绩 AND s2.course_id = 1 -- s2 代表 Java 成绩 AND s1.score > s2.score; -- 核心比较

MYSQL：联合查询

5. 子查询（嵌套查询）

子查询就是将一个 SELECT 语句的查询结果，作为另一个 SELECT 语句的查询条件。

一点建议： 子查询虽然灵活，但如果嵌套层级过深，其性能可能会变得不可控。在实际项目中，很多子查询都可以通过 JOIN 来实现，通常 JOIN 的效率会更高。

5.1 单行子查询

子查询只返回一行一列的结果。

示例：查询与\"不吃香菜\"同学同班的所有同学

SELECT *FROM studentWHERE class_id = (SELECT class_id FROM student WHERE name = \'不吃香菜\');

MYSQL：联合查询

5.2 多行子查询

子查询返回多行一列的结果，通常与 IN 或 NOT IN 配合使用。

示例：查询所有修了\"MySQL\"或\"Java\"课程的成绩信息

SELECT *FROM scoreWHERE course_id IN (SELECT course_id FROM course WHERE name IN (\'Java\', \'MySQL\'));

MYSQL：联合查询

5.3 在 FROM 子句中使用子查询

我们可以将一个子查询的结果集当作一张临时的表来使用。

示例：查询所有比\"Java001班\"平均分还高的成绩信息

-- 步骤一：先查出 Java001 班的平均分SELECT AVG(sc.score) FROM student sJOIN class c ON s.class_id = c.class_id JOIN score sc ON s.student_id = sc.student_idWHERE c.name = \'Java001班\';

MYSQL：联合查询

-- 步骤二：将上一步的查询作为临时表 tmp，再与 score 表进行比较SELECT * FROM score s, ( SELECT AVG(sc.score) AS avg_score FROM student s JOIN class c ON s.class_id = c.class_id JOIN score sc ON s.student_id = sc.student_id WHERE c.name = \'Java001班\') tmpWHERE s.score > tmp.avg_score;

MYSQL：联合查询

6. 合并查询 (UNION)

UNION 和 UNION ALL 用于合并多个 SELECT 语句的结果集。

UNION：合并结果并自动去重。
UNION ALL：合并结果但保留所有重复行。

使用前提： 参与合并的多个 SELECT 语句，其查询的列数必须相同，且对应列的数据类型也应该兼容。

6.1 示例

我们先创建一个 student1 表用于演示。

CREATE TABLE student1 LIKE student;INSERT INTO student1 (name, sno, age, gender, enroll_date, class_id) VALUES(\'唐三藏\', \'100001\', 18, 1, \'1986-09-01\', 1),(\'刘备\', \'300001\', 18, 1, \'1993-09-01\', 3),(\'张飞\', \'300002\', 18, 1, \'1993-09-01\', 3),(\'关羽\', \'300003\', 18, 1, \'1993-09-01\', 3);

合并 student 表中 id < 3 的同学和 student1 表中的所有同学

-- 使用 UNION，\"唐三藏\"只出现一次SELECT * FROM student WHERE student_id < 3 UNION SELECT * FROM student1;

MYSQL：联合查询

-- 使用 UNION ALL，\"唐三藏\"出现两次SELECT * FROM student WHERE student_id < 3 UNION ALL SELECT * FROM student1;

MYSQL：联合查询

关于 UNION 的一个重要提醒：
在使用 UNION 或 UNION ALL 时，有一个非常关键的前提：所有参与合并的 SELECT 语句，它们的查询列数必须完全一致，并且对应位置上列的数据类型也应该是相互兼容的。

这一点需要我们特别留意，因为 MySQL 在执行合并时，并不会严格地去校验每一列的数据类型是否真的“匹配”。它更多是确保列的数量相同。如果我们不小心将一个字符串类型的列和一个数字类型的列合并在了一起，查询可能不会报错，但最终得到的结果集的意义就会变得混乱且无意义。因此，在编写合并查询时，我们自己要承担起校验的责任，确保合并的字段在逻辑上是对应的。
select student_id,name from student UNION SELECT class_id,name from class;

7. 插入查询结果

我们可以将一个 SELECT 语句的查询结果，直接 INSERT 到另一张表中，这在数据迁移和备份时非常有用。

示例：将 student 表中 “C++001班” 的所有学生复制到 student1 表中

INSERT INTO student1 (name, sno, age, gender, enroll_date, class_id)SELECT s.name, s.sno, s.age, s.gender, s.enroll_date, s.class_idFROM student sJOIN class c ON s.class_id = c.class_idWHERE c.name = \'C++001班\';SELECT * FROM student1;

MYSQL：联合查询

虚拟游戏攻略

MYSQL：联合查询

文章目录

联合查询：从多个表中获取信息的艺术

1. 为什么需要联合查询？

1.1 联合查询的内部计算过程

1.2 准备练习数据

2. 内连接 (INNER JOIN)

2.1 内连接的推荐语法

2.2 内连接示例

3. 外连接 (OUTER JOIN)

3.1 示例1：查询所有同学的考试情况（包括未参加考试的）

3.2 示例2：查询所有班级及其学生情况（包括没有学生的班级）

4. 自连接

4.1 示例：查询\"MySQL\"成绩比\"Java\"成绩高的学生信息

5. 子查询（嵌套查询）

5.1 单行子查询

5.2 多行子查询

5.3 在 FROM 子句中使用子查询

6. 合并查询 (UNION)

6.1 示例

7. 插入查询结果

公告

DeepSeek全套部署资料免费下载

免费可商用字体批量下载

标签

MYSQL：联合查询

文章目录

联合查询：从多个表中获取信息的艺术

1. 为什么需要联合查询？

1.1 联合查询的内部计算过程

1.2 准备练习数据

2. 内连接 (INNER JOIN)

2.1 内连接的推荐语法

2.2 内连接示例

3. 外连接 (OUTER JOIN)

3.1 示例1：查询所有同学的考试情况（包括未参加考试的）

3.2 示例2：查询所有班级及其学生情况（包括没有学生的班级）

4. 自连接

4.1 示例：查询\"MySQL\"成绩比\"Java\"成绩高的学生信息

5. 子查询（嵌套查询）

5.1 单行子查询

5.2 多行子查询

5.3 在 FROM 子句中使用子查询

6. 合并查询 (UNION)

6.1 示例

7. 插入查询结果

相关问题

公告

DeepSeek全套部署资料免费下载

免费可商用字体批量下载

标签