当前位置：首页 > news >正文

13.MySQL联合查询、自连接、子查询、合并查询全梳理（附实战SQL+避坑指南）

news 2026/5/15 17:35:46

零、课前准备

一、复习重点：数据库设计&联合查询基础

1. 数据库设计（抽象）与三大范式

2. MySQL联合查询（多表查询）基础

二、自连接：自己连自己

场景：显示“MySQL”比“JAVA”分数高的同学成绩

自连接的代价

三、子查询：把多个SQL合并（但慎用！）

1. 单行子查询

2. 多行子查询

3. 查询重复录入的分数

4. 在from子句中用子查询（临时表）

四、合并查询：把多个查询结果合并

场景：合并student和student2表

五、select回顾：所有知识点串起来

最后碎碎念

零、课前准备

show DATABASES; use java117; show tables; -- 删除所有表（注意顺序，因为有外键引用） drop table if exists score; drop table if exists student; drop table if exists class; drop table if exists course; # 1.创建表 create table course ( course_id int primary key auto_increment, name varchar(20) ); -- 班级表：存储班级信息 create table class ( class_id int primary key auto_increment, name varchar(20) ); -- 学生表：存储学生信息，包含外键 class_id 关联班级 create table student ( student_id int primary key auto_increment, name varchar(20), sno varchar(10), class_id int ); -- 成绩表：存储学生成绩，包含外键 student_id 和 course_id create table score ( student_id int, course_id int, score int ); # 2.插入课程数据 insert into course (name) values ('Java'), ('C++'), ('MySQL'), ('操作系统'), ('计算机网络'), ('数据结构'); -- 插入班级数据 insert into class(name) values ('Java001班'), ('C++001班'), ('前端001班'); -- 插入学生数据（包含学号、姓名、所属班级ID） insert into student values (null, '唐三藏', '100001', 1), (null, '孙悟空', '100002', 1), (null, '猪八戒', '100003', 1), (null, '沙悟净', '100004', 1), (null, '宋江', '200001', 2), (null, '武松', '200002', 2), (null, '李逵', '200003', 2), (null, '不想毕业', '200004', 2); -- 插入成绩数据（student_id, course_id, score） insert into score (student_id, course_id, score) values (1, 1, 70.5), -- 学生1（唐三藏）的Java成绩 (1, 3, 98.5), -- 学生1的MySQL成绩 (1, 5, 33), -- 学生1的操作系统成绩 (1, 6, 98), -- 学生1的数据结构成绩 (2, 1, 60), -- 学生2（孙悟空）的Java成绩 (2, 5, 59.5), -- 学生2的操作系统成绩 (3, 1, 33), -- 学生3的Java成绩 (3, 3, 68), -- 学生3的MySQL成绩 (3, 5, 99), -- 学生3的操作系统成绩 (4, 1, 67), -- 学生4的Java成绩 (4, 3, 23), -- 学生4的MySQL成绩 (4, 5, 56), -- 学生4的操作系统成绩 (4, 6, 72), -- 学生4的数据结构成绩 (5, 1, 81), -- 学生5的Java成绩 (5, 5, 37), -- 学生5的操作系统成绩 (6, 2, 56), -- 学生6的C++成绩 (6, 4, 43), -- 学生6的计算机网络成绩 (6, 6, 79), -- 学生6的数据结构成绩 (7, 2, 80), -- 学生7的C++成绩 (7, 6, 92); -- 学生7的数据结构成绩 # 3.【验证数据是否创建成功】 select * from course; select * from class; select * from student; select * from score;

作为一名正在啃数据库的学生，最近把联合查询、自连接、子查询、合并查询这些知识点又过了一遍，整理了这篇博客，把自己学习时的理解和踩坑点都分享出来，希望能帮到同样在学MySQL的你~

一、复习重点：数据库设计&联合查询基础

1. 数据库设计（抽象）与三大范式

数据库设计要先找实体（关键名词），再分析实体间关系：

一对一：一个同学一个账号，一个账号属于一个同学
一对多：一个同学一个班级，一个班级多个同学
多对多：一个同学多门课，一门课多个同学
没关系：独立实体

2. MySQL联合查询（多表查询）基础

笛卡尔积：行数是两表行数之积，列数是两表列数之和（类似“排列组合”）。
指定连接条件：避免无意义的组合，常用表名.列名区分同名列。
别名：给表/列起别名（比如score as s1），简化书写。
连接类型：内连接（交集）、外连接（左外/右外）。

二、自连接：自己连自己

自连接很特殊，本质是把“行关系”转成“列关系”（比如比较同一同学的不同课程成绩）。

场景：显示“MySQL”比“JAVA”分数高的同学成绩

表结构（score表：student_id,course_id,score），需要先找到MySQL和JAVA对应的course_id，再自连接比较。

-- 步骤1：查看表数据 select * from score; -- 步骤2：找到MySQL和JAVA的course_id select * from course where name = 'MySQL' or name = 'JAVA'; -- 步骤3：自连接（给score起两个别名s1、s2） select s1.student_id, s1.score as 'mysql分数', s2.score as 'java分数' from score as s1, score as s2 where s1.student_id = s2.student_id -- 同一同学 and s1.course_id = 3 -- MySQL的course_id（假设是3） and s2.course_id = 1 -- JAVA的course_id（假设是1） and s1.score > s2.score; -- MySQL分数更高

自连接的代价

笛卡尔积开销极大！如果表很大，不建议直接用自连接。可以：

先查每个同学的所有成绩，转成Java对象存List；
再用Java代码遍历List，筛选符合条件的数据。

三、子查询：把多个SQL合并（但慎用！）

子查询是把多个查询合并成一个，但要注意可读性（别为了合并而合并，否则后期维护难）。

1. 单行子查询

嵌套的SQL返回单行结果。

场景：查“不想毕业”同学的同班同学（排除自己）。

-- 分开写： select class_id from student where name = '不想毕业'; -- 得到class_id=2 select name from student where class_id = 2 and name != '不想毕业'; -- 查同班 -- 合并（单行子查询）： select name from student where class_id = (select class_id from student where name = '不想毕业') and name != '不想毕业';

2. 多行子查询

嵌套的SQL返回多行结果，用in匹配。

场景：查“MySQL”或“JAVA”课程的成绩。

-- 分开写： select course_id from course where name = 'java' or name = 'MySQL'; -- 得到course_id=1,3 select * from score where course_id in (1, 3); -- 合并（多行子查询）： select * from score where course_id in (select course_id from course where name = 'java' or name = 'MySQL');

3. 查询重复录入的分数

构造重复数据后，用group by + having或in查重复。

-- 先插入重复数据（手动构造） insert into score values (1, 1, 71), (1, 3, 99), (1, 5, 33), (1, 6, 98); -- 方法1：group by + having select * from score group by student_id, course_id, score having count(*) > 1; -- 方法2：子查询+in select score from score where (student_id, course_id, score) in ( select student_id, course_id, score from score group by student_id, course_id, score having count(*) > 1 );

4. 在from子句中用子查询（临时表）

把子查询的结果当临时表，后续查询基于它。

场景：查所有比“Java001班”平均分高的成绩。

select * from score, ( select avg(score.score) as score from student, score, class where student.student_id = score.student_id and student.class_id = class.class_id and class.name = 'Java001班' ) as avg_score where score.score > avg_score.score;

四、合并查询：把多个查询结果合并

合并查询用union（去重）或union all（不去重），要求：

列的个数、类型对应；
列名可以不同（但建议一致，方便理解）。

场景：合并`student`和`student2`表

-- 创建student2表并插入数据 use java117_2; select * from student; create table student2 (id int, name varchar(20)); insert into student2 values(1, '张三'), (2, '李四'), (100, '王五'); select * from student2; -- union（合并+去重） select * from student union select * from student2; -- union all（合并+不去重） select * from student union all select * from student2;