mysql练习——“查询没有学全所有课程的同学的信息”3个SQL语句写法

1.数据准备

数据表1：student

数据表2：stu_sco，c_id是课程编号，score是课程成绩

2.思路

2.1 在stu_sco表中查询哪些同学（s_id）学了所有的课程，在student中排除学了所有课程的同学，剩下的就是没有学全课程的同学；

2.2 在stu_sco表中查询哪些同学（s_id）没有学全所有的课程的同学，并将同学（s_id）传递给student，需要考虑有的同学没上课；

3.SQL语句写法

3.1从数据表查看，所有课程指的是3门课程，所有查询一下哪些同学学了3门课程即可

select count(distinct(c_id)) from stu_sco;

count(*)，用于查询有几行数据

distinct，用于去重

使用“not in”查询

select student.* from student 
where student.s_id not in 
(
select stu_sco.s_id from stu_sco group by stu_sco.s_id having count(stu_sco.c_id) =
(select count(distinct(c_id)) from stu_sco)
);

3.2写法2，使用“where not exists”

select student.* from student 
where not exists 
(
select stu_sco.s_id from stu_sco group by stu_sco.s_id having count(stu_sco.c_id) =
(select count(distinct(c_id)) from stu_sco) 
and student.s_id = stu_sco.s_id
);

3.3写法3

查询没有学全课程的同学信息（即课程总数小于3）

select student.* from student 
where student.s_id in 
(select stu_sco.s_id from stu_sco group by stu_sco.s_id having count(stu_sco.c_id) <
(select count(distinct(stu_sco.c_id)) from stu_sco))

or student.s_id not in (select stu_sco.s_id from stu_sco);

3.4 写法4--查询结果没考虑有的同学没学课程，导致查询结果不全

只查询课程总数少于3的同学（开始我也是这么写的）

select student.* from student 
where student.s_id in 
(select stu_sco.s_id from stu_sco group by stu_sco.s_id having count(stu_sco.c_id) <
(select count(distinct(stu_sco.c_id)) from stu_sco));