mysql练习——在同一张表中“查询学过编号为“01“但是没有学过编号为“02“的课程的同学的信息”多个SQL语句

1.数据准备

数据表名称：stu_sco

2.解题思路

先查询学过01和02课程的同学信息，再取两个结果的交集

2.1查询学过01课程的同学

select * from stu_sco where c_id = 01;

2.1查询学过02课程的同学

select * from stu_sco where c_id = 02;

3.SQL语句

3.1 写法1，学过课程1的同学信息要包含在学过课程2的同学信息--使用内连接（）

select stu01.* from 
(select stu_sco.s_id from stu_sco where stu_sco.c_id = '01') as stu01
inner join
(select stu_sco.s_id from stu_sco where stu_sco.c_id = '02') as stu02
on stu01.s_id = stu02.s_id;

3.2 写法2，使用左连接或者右连接则需要增加"stu01.s_id = stu02.s_id "相等的条件

左连接是以左表为主，右表为辅，若左表存在空值，会合并到查询结果中；

右连接是以右表为主，左表为辅，若右表存在空值，会合并到查询结果中；

select stu01.* from 
(select stu_sco.s_id from stu_sco where stu_sco.c_id = '01') as stu01
left join
(select stu_sco.s_id from stu_sco where stu_sco.c_id = '02') as stu02
on stu01.s_id = stu02.s_id where stu01.s_id = stu02.s_id;

3.3 写法3，用“in”

select stu01.*from

select stu_sco.s_id from stu_sco where stu_sco.c_id = 01) as stu01
where stu01.s_id in 
(select stu_sco.s_id from stu_sco where stu_sco.c_id = 02);

3.4 写法4，用“where exists”方法，用法类似与“in”；

select * from stu_sco stu01 where exists (select stu02.s_id from stu_sco stu02 where c_id = 02 and stu01.s_id=stu02.s_id)
and stu01.c_id = 01;

-----

select * from stu_sco stu01 where exists

 (select stu02.s_id from stu_sco stu02 where c_id = 02 and stu01.s_id=stu02.s_id)

用于查询stu_sco表哪些人学了02课程，约束条件“stu01.c_id = 01”则是保证同时学了01课程。

若有错误或描述不清楚的地方，请大家及时留言。