MySQL数据库精研之旅第十一期：打造高效联合查询的实战宝典（二）
专栏：MySQL数据库成长记
个人主页：手握风云
目录
一、自链接
1.1. 应用场景
1.2. 示例
1.3. 表连接练习
二、子查询
2.1. 语法
2.2. 单行子查询
2.3. 多行子查询
2.4. 多列子查询
2.5. 在from子句中使用子查询
三、合并查询
3.1. 创建新表并初始化数据
3.2. Union
3.3. Union all
四、插入查询结果
4.1. 语法
4.2. 示例
一、自链接
1.1. 应用场景
自连接是⾃⼰与⾃⼰取笛卡尔积，可以把行转化成列，在查询的时候可以使用where条件对结果进行过滤，或者说实现行与行之间的比较。在做表连接时为表起不同的别名。
1.2. 示例
- 显示所有"MySQL"成绩比"JAVA"成绩高的成绩信息
-- 显示MySQL成绩比Java成绩高
select s1.score as 'Java成绩',s2.score as 'MySQL成绩' from score s1, score s2 , course c1, course c2
wheres1.student_id = s2.student_idandc1.`name` = 'Java'and c2.`name` = 'MySQL'ands1.course_id = c1.id and s2.course_id = c2.idand s2.score > s1.score;
1.3. 表连接练习
- 显示所有"MySQL"成绩比"JAVA"成绩高的学生信息和班级以及成绩信息
-- 显示所有"MySQL"成绩比"JAVA"成绩高的学生信息和班级以及成绩信息
select stu.*, c.`name`,s1.score as 'Java成绩',s2.score as 'MySQL成绩' from
student stu,
class c,
score s1,
score s2,
course c1,
course c2
where
stu.id = s1.student_id -- 学生表和分数表之间建立关系
AND
s1.student_id = s2.student_id -- 两个分数表之间建立s
AND
stu.class_id = c.id -- 学生表和班级表之间建立关联关系
AND
c1.`name` = 'Java'
AND
c2.`name` = 'MySQL'
AND
s1.course_id = c1.id
AND
s2.course_id = c2.id
AND
s2.score > s1.score; -- 进行分数的比较
二、子查询
子查询是把一个SELECT语句的结果当作另一个SELECT语句的条件，也叫嵌套查询。子查询可以无限嵌套，在工作中要注意嵌套的深度。
2.1. 语法
select * from table1 where col_name1 { = | IN } (select col_name1 from table2 where col_name2 {= | IN} [(select ……)]
)2.2. 单行子查询
- 示例：查询与"Paul"同学的同班同学
步骤：1.查出不想毕业同学的class_id；2.根据class_id查出学生表中学生信息。
SELECT class_id FROM student WHERE `name` = 'Paul';
SELECT * FROM student WHERE class_id = 1;SELECT class_id FROM student WHERE `name` = 'Paul';
SELECT * FROM student WHERE class_id = 2;
2.3. 多行子查询
嵌套的查询中返回多行数据，使用[NOT] IN关键字。
- 示例：查询MySQL或Java的成绩信息
步骤：1.先确定参与查询的表(分数表和课程表)；2.先查询课程信息；3.通过课程编号在分数表中查询对应的分数。
SELECT * FROM course WHERE `name` = 'Java' or `name` = 'MySQL';
SELECT * FROM score WHERE course_id = 1 or course_id = 3;
SELECT * FROM score WHERE course_id IN (SELECT id FROM course WHERE `name` = 'Java' OR 'MySQL');
- 示例：查询不包含MySQL或Java的成绩信息
SELECT * FROM score WHERE course_id NOT IN (SELECT id FROM course WHERE `name` = 'Java' OR 'MySQL');
2.4. 多列子查询
单行子查询和多行子查询都只返回一列数据，多列子查询可以返回多列个数据，外层查询与嵌套的内层查询的列要匹配。注意外层与内层列的顺序必须要对应。
- 示例：查询重复录入的分数
-- 1.对重复的列进行分组
select distinct score, student_id, course_id from score GROUP BY score, student_id, course_id;-- 2.统计分组之后每个组包含的记录数
select distinct score, student_id, course_id, count(*) from score GROUP BY score, student_id, course_id;-- 分组中的记录数大于1，表示有重复数据
select distinct score, student_id, course_id, count(*) from score GROUP BY score, student_id, course_id having count(*) > 1;
2.5. 在from子句中使用子查询
当⼀个查询产生结果时，MySQL子动创建⼀个临时表，然后把结果集放在这个临时表中，最终返回给用户，在from⼦句中也可以使⽤临时表进行子查询或表连接操作。表连接查询时，临时表也可以参与连接，只需要确定好关联字段即可。
- 示例：查询所有比"101班"平均分高的成绩信息
我们先查出101班的班级编号，并根据班级编号在学生表查出这个班的所有学生信息。
select * from student stu, class c where stu.class_id = c.id and c.`name` = '101班';
接下来根据学生id在分数表中查出所有学生的分数并计算平均分。
select avg(sco.score) from
student stu, class c,score sco where
stu.class_id = c.id and c.`name` = '101班';
最后根据平均分过滤，找出比平均分高的分数。
select * from score sco, (
select avg(sco.score) as score from student stu, class c,score sco where stu.class_id = c.id and c.`name` = '101班'
and sco.student_id = stu.id) as temp1 where sco.score > temp1.score;
三、合并查询
在实际应⽤中，为了合并多个select操作返回的结果，可以使用集合操作符union、union all。
3.1. 创建新表并初始化数据
-- 根据现有的表创建一张新表，新表结构与老表结构一致
create table student1 like student;-- 插入数据
insert into student1 (`name`,sno,age,gender,enroll_date,class_id) values
('Paul','10001',18,1,'2025-09-01',1),
('Bruce','30001',19,1,'2025-09-01',3),
('Ginny', '30002',19,0,'2025-09-01',3),
('Nora', '30003',18,0,'2025-09-01',3);3.2. Union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
- 示例：查询student表中id<3的同学和student1表中的所有同学
select * from student where id < 3 union select * from student1;
3.3. Union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
select * from student where id < 3 union all select * from student1;
四、插入查询结果
向表中写入数据时，数据是通过一个查询得到的。
4.1. 语法
insert into table_name [(column [, column …])] select …查询的列要与写入的匹配。
4.2. 示例
将student表中“102班”学生复制到student1表中。
select stu.`name`,stu.sno, stu.age, stu.gender, stu.enroll_date, stu.class_id from student stu, class c where stu.class_id = c.id and c.`name` = '102班';insert into student1 (`name`, sno, age, gender, enroll_date, class_id)
select stu.`name`,stu.sno, stu.age, stu.gender, stu.enroll_date, stu.class_id from student stu, class c where stu.class_id = c.id and c.`name` = '102班';