【MySQL】针对常用子查询与联合查询的进阶案例

标签：进阶 -- sid 查询 course score student MySQL id

表创建

SHOW DATABASES;
create database practice;
USE practice;

-- 创建(插入)表顺序，先从父表建随后子表，删除时相反
-- class -> teacher -> course -> student -> score

-- show tables
SHOW tables;
-- table class
truncate table class;
drop table if exists class;

create table class(
    `cid` int(11) not null auto_increment,  -- 注意column要加的话加反引号`
    `caption` varchar(32) not null, -- caption 标题
    primary key (`cid`)
) engine=InnoDB auto_increment=1 default charset=utf8;  -- engine 设置存储引擎 InnoDB mysql默认存储引擎
-- 支持事务、外键和高效并发，适合绝大多数需要可靠性和性能的场景。

-- table course
truncate table course;
drop table if exists course;

create table `course`(
    cid int(11) not null auto_increment,
    cname varchar(32) not null,
    teacher_id int(11) not null,
    primary key (cid),
    # teacher_id 作为外键，引用teacher表的tid列
    constraint fk_course_teacher foreign key (teacher_id) references teacher(tid)
) engine=InnoDB auto_increment=1 default charset=utf8;

-- table score
truncate table score;
drop table if exists `score`;

create table score(
    sid int(11) not null auto_increment,
    student_id int(11) not null,
    course_id int(11) not null,
    num int(11) not null,
    primary key (sid),
    key fk_score_course(course_id),
    key fk_score_student(student_id),
    constraint fk_score_course foreign key(course_id) references course(cid),
    constraint fk_score_student foreign key(student_id) references student(sid)
) engine=InnoDB auto_increment=1 default charset=utf8;

-- table student 
truncate table student; 
drop table if exists student; 

create table student( 
    sid int(11) not null auto_increment primary key, 
    gender char(1) not null, 
    class_id int(11) not null, 
    sname varchar(32) not null, 
#     primary key(sid), 
    key fk_class(class_id), -- 建立索引，加速对class_id列的查询操作 
    # 这样单独将外键建立索引然后再定义外键约束的方式既能维护数据完整性，又能提高查询效率 
    constraint fk_class foreign key(class_id) references class(cid) -- 定义外键约束，确保数据完整性

) engine=InnoDB auto_increment=1 default charset=utf8;


-- table teacher 
truncate table teacher; 
drop table if exists teacher; 

create table teacher( 
    tid int(11) not null auto_increment, 
    tname varchar(32) not null, 
    primary key (tid) 
) engine=InnoDB auto_increment  =1 default charset=utf8;

待查数据插入

-- 插入数据--------------------------------
-- 创建 class 表的数据
insert into class (caption) values
('Class A'),
('Class B'),
('Class C');

-- 创建 teacher 表的数据
insert into teacher (tname) values
('张老师'),
('李老师'),
('王老师');

-- 创建 student 表的数据
insert into student (gender, class_id, sname) values
('M', 1, '张三'),
('F', 2, '李四'),
('M', 3, '王五'),
('F', 1, '赵六'),
('M', 2, '钱七'),
('F', 3, '孙八');

-- 创建 course 表的数据
insert into course (cname, teacher_id) values
('C++高级', 1),  -- 张老师授课
('音视频技术', 2), -- 李老师授课
('数据库原理', 3), -- 王老师授课
('算法设计', 1);  -- 张老师授课

-- 创建 score 表的数据
insert into score (student_id, course_id, num) values
(1, 1, 85),   -- 张三 选 C++高级 成绩 85
(1, 2, 90),   -- 张三 选 音视频技术 成绩 90
(1, 3, 75),   -- 张三 选 数据库原理 成绩 75
(2, 1, 60),   -- 李四 选 C++高级 成绩 60
(2, 2, 95),   -- 李四 选 音视频技术 成绩 95
(3, 1, 88),   -- 王五 选 C++高级 成绩 88
(3, 3, 82),   -- 王五 选 数据库原理 成绩 82
(4, 1, 70),   -- 赵六 选 C++高级 成绩 70
(4, 2, 65),   -- 赵六 选 音视频技术 成绩 65
(5, 1, 78),   -- 钱七 选 C++高级 成绩 78
(5, 3, 80),   -- 钱七 选 数据库原理 成绩 80
(6, 2, 85);   -- 孙八 选 音视频技术 成绩 85

子查询与联合查询案例展示

表结构关联

-- 查询1课程比2课程成绩高的所有学生的学号
select A.student_id
from (select student_id, num from score where course_id = 1) A -- 因为课程1选的人多所以 left join
         left join
         (select student_id, num from score where course_id = 2) B
         on A.student_id = B.student_id
where A.num > if(isnull(B.num), 0, B.num);

-- 查询平均成绩大于60分的同学学号和平均成绩

-- 适用于当我们想要计算每个学生在成绩大于60的科目上的平均成绩时。它会忽略成绩小于或等于60的科目。
select score.student_id, avg(score.num)
from score
where num > 60
group by score.student_id;
-- 适用于当我们想要计算每个学生所有课程的平均成绩，并且筛选出平均成绩大于60的学生。
select score.student_id, avg(score.num) snum
from score
group by score.student_id
having snum > 60;

-- 查询所有同学学号、姓名、选课数，总成绩
-- score、student
select score.student_id,
       student.sname,
       count(score.course_id),
       sum(score.num)
from score
         inner join
     student on score.student_id = student.sid
group by student_id;

-- 查询姓谢老师的个数
select *
from teacher;

select count(*)
from teacher
where tname like '谢%'; -- 执行模糊查询效率高于正则表达式

select count(*)
from teacher
where tname regexp '^谢';

-- 查询没学过’谢‘老师课的同学的学号、姓名
select student.sid, student.sname
from student
where student.sid not in (select distinct score.student_id
                          from score
                          where course_id in (select course.cid
                                              from course
                                                       left join teacher on course.teacher_id = teacher.tid
                                              where teacher.tname = '谢老师'));

-- 查询同时学过 1 、2课程的学生的学号，姓名

select *
from score;
select student.sid, student.sname
from student
where student.sid in (select score.student_id
                      from score
                      where course_id in (1, 2)
                      group by score.student_id
                      having count(distinct course_id) = 2);

-- 或者
select sid, sname
from student
         right join (select student_id
                     from score
                     where course_id
                               in (1, 2)
                     group by student_id
                     having count(distinct course_id) = 2) A on A.student_id = sid;

-- 查询c++高级课程比音视频课程成绩高的所有学生学号
select A.student_id
from (select student_id, num
      from score
      where course_id = (select cid from course where cname = 'c++高级')) A
         left join ( -- 确保子查询 A 中的所有学生都能保留，即便其未选修 "音视频课程"。
    select student_id, num
    from score
    where course_id = (select cid from course where cname = '音视频')) B
                   on A.student_id = B.student_id
where A.num > coalesce(B.num, 0);
-- coalesce(v1, v2, v3)检查参数，返回第一个不为null的参数，如果全为null返回null
# where A.num > if(isnull(B.num), 0, B.num);

select *
from score;
insert into score (student_id, course_id, num)
values (1, 2, 77),
       (1, 3, 66),
       (5, 1, 99);

-- 查询平均成绩大于60分的同学的学号和平均成绩
select sid, avg(num) 平均成绩
from score
group by sid
having avg(num) > 60;
-- SQL 中的 HAVING 子句不能直接引用 SELECT 中定义的别名（如 平均成绩），应该使用 AVG(num) 函数本身来进行筛选。


-- 查询所有同学学号、姓名、选课数、总成绩；

-- INNER JOIN 返回 只有在两张表中都有匹配的行 时的记录
-- 使用 INNER JOIN 确保返回的学生都是有选课记录的。
-- 如果你使用 LEFT JOIN，即使某个学生没有选课记录，查询也会返回这个学生的学号和姓名，但是选课数和总成绩会是 NULL，因为没有对应的 score 记录。
-- 你需要显示那些没有选课的学生吗？ 如果需要，LEFT JOIN 适用，但如果只关心那些选课的学生，INNER JOIN 更合适。
-- 在这种查询中，使用 RIGHT JOIN 不太合理，因为 score 表中有可能存在成绩记录，但没有对应的学生。
-- 通常来说，你查询的目的是找出学生的信息（而不是成绩表中的记录），所以 RIGHT JOIN 通常不适合。
select B.student_id, A.sname, count(*) 选课数, sum(num) 总成绩
from student A
         inner join score B on A.sid = B.student_id
group by B.student_id, A.sname;

-- 查询姓谢的老师的个数；
insert into teacher (tname)
values ('谢得主'),
       ('谢东风'),
       ('谢西风');
select count(*)
from teacher
where tname like '谢%';

-- 查询没学过'谢东风'老师课的同学学号，姓名
select distinct A.sid, A.sname
from student A
         left join score B on A.sid = B.student_id
where B.course_id not in (select cid
                          from course
                          where teacher_id = (select tid from teacher where tname = '谢东风'))
   or B.course_id is null;

/*
 如果你的数据量较小，且查询结构简单，使用子查询的方法是可以的。
如果你的数据量较大，并且你需要处理大量没有选课记录的学生，LEFT JOIN 可能会更加高效且更具可读性。
 */

-- 子查询实现
select sid, sname
from student
where sid not in ( -- 排除学过的，就是没学过的
    select distinct score.student_id -- 先找到学过谢东风课的
    from score
    where course_id in (select cid
                        from course
                        where teacher_id = (select tid from teacher where tname = '谢东风')));

-- 查询学过1也学过2课程的同学学号，姓名
select sid, sname
from student
         left join (select student_id
                    from score
                    where course_id in (1, 2)
                    group by student_id
                    having count(distinct course_id) = 2) A on A.student_id = sid;
/*
 如果未来需求简单且固定，使用第一种写法。
如果可能扩展或优化性能是关键，使用第二种写法。
 */
select A.sid, A.sname
from student A
         left join score B on A.sid = B.student_id
where B.course_id in (1, 2)
group by A.sid, A.sname
having count(distinct course_id) = 2;

-- 查询学过张老师所教所有课的学生的学号，姓名
select A.sid, A.sname
from student A
         inner join score B on A.sid = B.student_id
where B.course_id in (select cid
                      from course
                      where teacher_id = (select tid from teacher where tname = '张老师'))
group by A.sid, A.sname
having count(distinct B.course_id) = (select count(cid)
                                      from course
                                      where teacher_id = (select tid from teacher where tname = '张老师'));

-- 方法二 使用not exists
-- NOT EXISTS 是 SQL 中的一种用于检查子查询结果是否为空的条件子句
-- NOT EXISTS 用来判断“某种条件是否不成立”。
-- 适合处理需要验证“某一项不存在”的问题。
select A.sid, A.sname
from student A -- 从 student 表中逐一检查每个学生 A.sid，满足 where 条件的学生将作为结果返回
where not exists ( -- not exists (...) 确保只有那些满足内部逻辑的学生才会被返回。
    select 1
    from course C
    where C.teacher_id = (select tid from teacher where tname = '张老师')
      and not exists ( -- 如果某一门张老师的课程找不到匹配的学生选课记录，则这名学生被排除。
        -- 检查当前学生是否选修了特定课程。如果查不到，则返回
        select 1
        from score B
        where B.student_id = A.sid
          and B.course_id = C.cid));

-- 查询有课程成绩小于60分的同学的学号、姓名
select A.sid, A.sname
from student A
         inner join score B on A.sid = B.student_id
where B.num < 60;
-- num < 60会直接过滤num为null的不需要 left join

-- 查询没有学全所有课的学生的学号、姓名
-- 保证 SELECT 的所有非聚合列都包含在分组中
select A.sid, A.sname
from student A
         left join score B on A.sid = B.student_id
group by A.sid, A.sname
having count(distinct B.course_id) < (select count(*) from course);

-- not exists
select A.sid, A.sname
from student A
where exists ( -- 存在没有学全则返回    这里改为not exists就相当于查学全的
    select 1
    from course C
    where not exists ( -- 不存在所有课程和学生id对应的，即不存在学全的，那么就相当于返回没有学全的学生
        select 1
        from score B
        where B.student_id = A.sid
          and B.course_id = C.cid));

-- 查询至少有一门课与学号1同学所学相同的同学的学号和姓名
select distinct A.sid, A.sname
from student A
         inner join score B on A.sid = B.student_id -- 这里left与inner效果相同，因为student_id != 1 会排除null
where student_id != 1
  and B.course_id in (select course_id from score where student_id = 1);

-- 相较于上面优点：
-- 子查询先行筛选，减少了后续处理的数据量，对性能有一定帮助。
-- 不使用 DISTINCT 在最终结果中去重（因为子查询本身已去重），逻辑清晰。
select sid, sname
from student
         inner join (select distinct student_id -- DISTINCT student_id 避免重复
                     from score
                     where student_id != 1
                       and course_id in (select course_id from score where student_id = 1)) A
                    on A.student_id = student.sid;

-- 统计每个学生与学号 1 同学所学相同的课程的数量，并筛选出这些数量等于学号 1 所选课程总数的学生。
select distinct A.sid, A.sname, count(B.course_id) '相同课程数量'
from student A
         left join score B on A.sid = B.student_id
where student_id != 1
  and B.course_id in (select course_id from score where student_id = 1)
group by A.sid, A.sname;

-- 查询至少有一门课与学号1同学所学相同的同学的学号和姓名和所学课程数
select A.sid, A.sname, count(B.course_id) 所学课程数量
from student A
         inner join score B on A.sid = B.student_id -- 这里只关心有匹配的学生，所以不用left join
where A.sid != 1
  and B.course_id in (select course_id from score where student_id = 1)
group by A.sid, A.sname;
-- group by 本身保证了去重  distinct多余

-- 查询至少学过1号同学所有课的其他同学学号姓名
-- 子查询法
select A.sid, A.sname
from student A
where A.sid != 1 -- 不存在在非1号的学生里不存在没学过1号所学课的
  and not exists(select 1 # 查询是否不存在，sid != 1的学生里，是否不存在学号=1的学生所学的课是其他学号有没学过的
                 from score S1
                 where S1.student_id = 1
                   and not exists (select 1
                                   from score S2
                                   where S2.student_id = A.sid
                                     and S2.course_id = S1.course_id));
/*
 你的理解可以稍调整为：

查询是否存在 学号 ≠ 1 的学生中，是否不存在 1 号同学的课程被这个学生漏掉的情况。

或者说：

返回所有 学号 ≠ 1 且修完了 1 号同学所有课程的学生。
 */

-- 查询至少学过1号同学所有课的其他同学学号姓名
select A.sid, A.sname
from student A
where A.sid != 1
  and not exists (select 1
                  from score S1
                  where S1.student_id = 1
                    and not exists (select 1
                                    from score S2
                                    where A.sid = S2.student_id
                                      and S1.course_id = S2.course_id))

子查询和联合查询的比较，并根据性能和使用场景做出取舍建议

类型	查询案例	SQL 代码	性能与取舍
子查询	查询所有学过与学号 1 同学相同课程的学生学号、姓名	`sql SELECT DISTINCT A.sid, A.sname FROM student A WHERE A.sid != 1 AND B.course_id IN ( SELECT course_id FROM score WHERE student_id = 1);`	优点：结构简单，逻辑清晰；适合查询较简单的需求。缺点：性能较低，尤其当子查询返回大量数据时，可能导致多次查询。
子查询	查询学号 1 同学学过的所有课程的学生学号、姓名	`sql SELECT A.sid, A.sname FROM student A WHERE A.sid != 1 AND NOT EXISTS ( SELECT 1 FROM score S1 WHERE S1.student_id = 1 AND NOT EXISTS ( SELECT 1 FROM score S2 WHERE A.sid = S1.student_id AND S1.course_id = S2.course_id );`	优点：使用 `NOT EXISTS` 避免了重复数据，适合处理复杂的包含排除条件的查询。缺点：嵌套多层子查询时可能会影响查询效率。
联合查询	查询所有学生的学号、姓名、选课数和总成绩（使用 `LEFT JOIN`）	`sql SELECT B.student_id, A.sname, COUNT(*) AS 选课数, SUM(num) AS 总成绩 FROM student A INNER JOIN score B ON A.sid = B.student_id GROUP BY B.student_id;`	优点：使用 `JOIN` 可以高效的获取多个表的数据，适合多表数据联合查询。缺点：需要通过适当的条件来确保连接的准确性，如果条件不当可能会导致返回冗余数据。
联合查询	查询所有学号 1 同学所学所有课程的学生学号、姓名，学号 1 学生所学的课程存在于其他学生的成绩中	`sql SELECT A.sid, A.sname FROM student A LEFT JOIN score B ON A.sid = B.student_id WHERE B.course_id IN ( SELECT course_id FROM score WHERE student_id = 1);`	优点：`LEFT JOIN` 可以获取左表（学生）所有记录，同时匹配右表（成绩）数据，确保没有成绩的学生信息仍会保留。缺点：如果左表记录非常多，连接的复杂度会导致性能问题。
联合查询	查询学号 1 同学学过的所有课程的学生学号、姓名，采用 `RIGHT JOIN`	`sql SELECT A.sid, A.sname FROM student A RIGHT JOIN score B ON A.sid = B.student_id WHERE B.course_id IN ( SELECT course_id FROM score WHERE student_id = 1);`	优点：使用 `RIGHT JOIN` 可以确保右表（课程成绩）的所有记录被返回，即使左表（学生）没有相应的记录。缺点：与 `LEFT JOIN` 类似，如果右表数据量非常大，也会影响性能。
联合查询	查询学过张老师所教所有课程的学生学号、姓名	`sql SELECT A.sid, A.sname FROM student A INNER JOIN score B ON A.sid = B.student_id WHERE B.course_id IN ( SELECT cid FROM course WHERE teacher_id = (SELECT tid FROM teacher WHERE tname = '张老师'));`	优点：`INNER JOIN` 可以直接返回两个表匹配的结果，确保查询高效且结果准确。缺点：如果两表中有部分记录没有匹配，查询结果会被排除掉。

性能取舍

子查询：
- 优选：当查询逻辑简单，且子查询返回的记录不多时。
- 不适合：子查询的返回数据量大时，尤其是嵌套子查询，这会导致性能下降。可以考虑重写为 JOIN 查询以优化性能。
联合查询（JOIN）：
- 优选：当涉及多表连接，并且需要从多个表获取完整的记录时，JOIN 查询是更好的选择。INNER JOIN 和 LEFT JOIN 在性能上表现较好。
- 不适合：当你只需要某些表中的特定数据时（例如，子查询可以排除某些记录），避免不必要的连接可能会更高效。

总结：

子查询 适用于查询较为简单且返回的数据量较小的场景。如果查询逻辑复杂且无法通过 JOIN 简化，使用子查询会更加直观和易于理解。
联合查询（特别是 JOIN）适合多表连接查询，通常在性能上更优，尤其是当查询涉及到多个表时，使用 JOIN 通常比多个子查询要高效。

标签：进阶,--,sid,查询,course,score,student,MySQL,id
From： https://blog.csdn.net/m0_74282926/article/details/143984035

【MySQL】针对常用子查询与联合查询的进阶案例

表创建

待查数据插入

子查询与联合查询案例展示

子查询和联合查询的比较，并根据性能和使用场景做出取舍建议

性能取舍

总结：

相关文章

赞助商

阅读排行

【MySQL】针对常用子查询与联合查询的进阶案例

表创建

待查数据插入

子查询与联合查询案例展示

子查询 和 联合查询 的比较，并根据性能和使用场景做出取舍建议

性能取舍

总结：

相关文章

赞助商

阅读排行

子查询和联合查询的比较，并根据性能和使用场景做出取舍建议