【MySQL】(8) 联合查询-CSDN博客

本文链接：https://blog.csdn.net/2401_86272648/article/details/147432463

一、联合查询的作用

由于范式的规则，数据分到多个表中，想要查询完整的信息，就需要联合查询多张表。比如查询学生的学生信息和所在班级的信息，就需要联合查询学生表和班级表。

二、联合查询过程

案例：查询学生姓名为孙悟空的详细信息，包括学学个人信息和班级信息。

 # 课程表
insert into course (name) values ('Java'), ('C++'), ('MySQL'), ('操作系统'), ('计算机网络'), ('数据结构');

# 班级表
insert into class(name) values ('Java001班'), ('C++001班'), ('前端001班');

# 学生表
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);

# 成绩表
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);

1、确定联合查询的表

学生表 student 和班级表 class，进行笛卡尔积：

2、过滤掉班级号不匹配的信息

学生表和班级表都有 id，需要指定是哪个表的 id。

3、筛选出指定学生

4、筛选出想要的字段，并精简表名

三、内连接

上面的示例就是内连接。

1、语法

select 字段 from 表1 别名1, 表2 别名2 where 连接条件 and 其他条件;

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 where 其他条件;

2、示例1：查询“唐三藏”同学的成绩。

查询的表：student、score。

确定连接条件需要查看表结构：s.id = sc.student_id。

其他条件：s.name = '唐三藏'。

字段：s.name、sc.score。

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

查询的表：student、score。

连接条件：s.id = sc.student_id。

分组查询：sc.student_id，① 唯一。② 对哪个表的列求和，就对那个表的列进行分组。

查询字段：s.id、s.sno、s.name、s.age、s.gender、s.enroll_date、sc.s（分组后，这些字段的值对于某一组都是确定的，可以在分组后查询）、avg(sc.score)（聚合函数的值也是确定的）。

查询结果：

4、示例3：查询所有同学的个人信息、每门课的成绩

查询表：student、score、course。

连接条件：

查询结果：

也可以使用：

工作中尽量少对大表关联查询，最多关联 3 个表，因为很耗资源。

四、外连接

左外连接：以左表为基准，显示左表所有值，显示右表匹配的值，没有匹配的就显示 NULL。
右外连接：跟左外连接反着来。
全连接：左、右都全显示，没有匹配的就显示 NULL。（MySQL 不支持）

内连接是有匹配的才显示，外连接没有匹配的也会显示 NULL。

1、语法

-- 左外连接，表1完全显⽰ 
select 字段 from 表名1 left join 表名2 on 连接条件;

-- 右外连接，表2完全显⽰
select 字段 from 表名1 right join 表名2 on 连接条件;

2、示例1：查询没有参加考试的同学信息

该查询就无法使用内连接完成。

查询的表：student、score。

连接条件：s.id = sc.student_id。

没有参加考试的同学：同学信息要全显示，没有匹配的成绩显示 NULL。

其它条件：sc.score is null。（注意不要用＝，null 与任意值做计算都是 null）

3、示例2：查询没有学生的班级

五、自连接

自连接是自己跟自己做笛卡尔积。上面的连接都是列与列的比较，有时候我们想要同一列中行与行的比较，但MySQL 又不支持直接比较行与行。就需要用自连接，把行转为列。

比如我们想要比较同一个学生的 score 字段的不同课程成绩：

1、示例1：显示所有 'Java' 比 'MySQL' 分数低的学生和成绩信息

查询的表：score sc1、score sc2、student s。

连接条件：sc1.student_id = sc2.student_id。同一个学生的成绩进行比较。（自连接）

sc1.student_id = student.id。

其它条件：sc1.class_id = Java 的编号，sc2.class_id = MySQL 的编号。（选出对应的课程）。sc1.score < sc2.score。

2、示例2：通过课程名实现示例1

有时候希望实现用户输入 2 个课程名称，就能直接查询的功能。所以先把 score 表与 class 表关联后，在对课程名进行筛选。

查询的表：score sc1、class c1、score sc2、class c2。

连接条件：sc1.class_id = c1.id（代表课程1）

sc2.class_id = c2.id（代表课程2）

sc1.student_id = sc2.student_id（代表同一个学生）

其他条件：c1.name = 'Java', c2.name = 'MySQL'

sc1.score < sc2.score

六、子查询

子查询就是一个 select 语句的结果是另一个 select 语句的条件，可以嵌套多层，也叫嵌套查询。子查询只是把多条的查询语句合并成一条查询语句，在工作中不能嵌套太多，影响查询效率。

1、语法

in 表示内层的 select 返回的是一个结果集。

2、单行子查询。

内层只返回一行数据。

示例：查询 '不想毕业' 的同班同学

先要查询 '不想毕业' 是哪个班的（内层返回1行），然后根据班级 id 查询同班同学（外层）。最后排除 '不想毕业' 的信息。

3、多行子查询

内层返回多行数据。

示例：查询 'Java' 或 'MySQL' 的成绩信息。

先查询 'Java' 或 'MySQL' 的课程 id（返回2行），然后根据课程 id 查询成绩信息。

4、多列子查询

内层返回多列数据，外层条件与内层查询的列要匹配。

示例：查询重复录入的分数。

首先查询重复的成绩信息（分数、学生 id、课程 id，相同的分为一组，统计每组数据行数，计数大于1的则重复），再根据内层的查询结果在外层查询完整的成绩信息（成绩 id，分数、学生 id、课程 id）。

5、在 from 子句中使用子查询

from 后面接查询的表，这个表可以是真实表或者临时表，而子查询的结果就是存储在临时表中。

示例：查询大于 'Java001' 班平均分的成绩信息。

先查找班级为 'Java001班' 的学生成绩，即将 class、score、student 关联，选出 class.name = 'Java001班' 的数据行，再求平均值，平均值存储在临时表中。

再将平均值临时表与 score 表进行内连接，最后使用其它条件 score > avg。

七、合并查询

想让多个 select 查询结果集一起返回，就用合并查询。

1、union（结果集去重）

准备一个与 student 结构一致的 student2 表：

插入数据：

insert into student2 (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 表数据：