目录零、课前准备一、复习重点数据库设计联合查询基础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班), (C001班), (前端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_id2 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_id1,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); -- 方法1group 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;五、select回顾所有知识点串起来回顾select的核心操作全列查询 / 指定列查询指定表达式查询 / 别名查询去重查询 / 排序 / 条件查询查询结果作为插入数据分页查询 / 聚合查询 / 分组查询联合查询内/外/自连接、子查询、合并查询最后碎碎念SQL没有特别抽象的部分熟练度是关键多写多练把每个知识点的场景吃透面试笔试就不慌注所有SQL基于java117/java117_2库表结构需提前创建可看我前面几篇博客可根据自己环境调整~如果觉得这篇博客对你有帮助欢迎关注~ 有问题评论区交流一起进步
13.MySQL联合查询、自连接、子查询、合并查询全梳理（附实战SQL+避坑指南）
目录零、课前准备一、复习重点数据库设计联合查询基础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班), (C001班), (前端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_id2 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_id1,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); -- 方法1group 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;五、select回顾所有知识点串起来回顾select的核心操作全列查询 / 指定列查询指定表达式查询 / 别名查询去重查询 / 排序 / 条件查询查询结果作为插入数据分页查询 / 聚合查询 / 分组查询联合查询内/外/自连接、子查询、合并查询最后碎碎念SQL没有特别抽象的部分熟练度是关键多写多练把每个知识点的场景吃透面试笔试就不慌注所有SQL基于java117/java117_2库表结构需提前创建可看我前面几篇博客可根据自己环境调整~如果觉得这篇博客对你有帮助欢迎关注~ 有问题评论区交流一起进步
