MySQL50题第610题

公众号&＃xff1a;尤而小屋
作者&＃xff1a;Peter
编辑&＃xff1a;Peter

大家好&＃xff0c;我是Peter。

今天带来的是50道SQL经典练习题的第6-10题&＃xff0c;题目具体为&＃xff1a;

• 查询“李”姓老师的数量
• 查询学过张三老师授课的同学的信息
• 找出没有学过张三老师课程的学生
• 查询学过编号为01&＃xff0c;并且学过编号为02课程的学生信息
• 查询学过01课程&＃xff0c;但是没有学过02课程的学生信息&＃xff08;注意和上面题目的区别&＃xff09;

题目需求

查询“李”姓老师的数量

这题怕是最简单的吧&＃x1f62d;

分析过程

使用通配符和like来解决

SQL实现

select count(t_name) from Teacher where t_name like "李%"; -- 通配符


题目需求

查询学过张三老师授课的同学的信息

分析过程

张三老师&＃xff1a;Course--->t_name
课程&＃xff1a;c_id------>Score.c_id------->Student.*


SQL实现

-- 方法1&＃xff1a;通过张三老师的课程的学生来查找&＃xff1b;自己的方法
select * -- 3. 通过学号找出全部学生信息
from Student
where s_id in (select s_id -- 2.通过课程找出对应的学号from Score Sjoin Course Con S.c_id &＃61; C.c_id -- 课程表和成绩表where C.t_id&＃61;(select t_id from Teacher where t_name&＃61;"张三") -- 1.查询张三老师的课程
);-- 方法2&＃xff1a;通过张三老师的课程来查询
select s1.*
from Student s1
join Score s2
on s1.s_id&＃61;s2.s_id
where s2.c_id in (select c_id from Course c where t_id&＃61;( -- 1. 通过老师找出其对应的课程select t_id from Teacher t where t_name&＃61;"张三")
)-- 方法3
select s.* from Teacher t
left join Course c on t.t_id&＃61;c.t_id -- 教师表和课程表
left join Score sc on c.c_id&＃61;sc.c_id -- 课程表和成绩表
left join Student s on s.s_id&＃61;sc.s_id -- 成绩表和学生信息表
where t.t_name&＃61;&＃39;张三&＃39;;


自己的方法&＃xff1a;

方法2来实现&＃xff1a;

方法3实现&＃xff1a;

题目需求

找出没有学过张三老师课程的学生

分析过程

和上面&＃x1f446;的题目是互补的&＃xff0c;考虑取反操作

SQL实现

select * -- 3. 通过学号找出全部学生信息
from Student
where s_id not in ( -- 2.通过学号取反&＃xff1a;学号不在张三老师授课的学生的学号中select s_id from Score Sjoin Course Con S.c_id &＃61; C.c_idwhere C.t_id&＃61;(select t_id from Teacher where t_name &＃61;"张三") -- 1.查询张三老师的课程
);-- 方法2&＃xff1a;
select *
from Student s1
where s1.s_id not in (select s2.s_id from Student s2 join Score s3 on s2.s_id&＃61;s3.s_id where s3.c_id in(select c.c_id from Course c join Teacher t on c.t_id&＃61;t.t_id where t_name&＃61;"张三" )
);-- 方法3
select s1.*
from Student s1
join Score s2
on s1.s_id&＃61;s2.s_id
where s2.c_id not in (select c_id from Course c where t_id&＃61;( -- 1. 通过老师找出其对应的课程select t_id from Teacher t where t_name&＃61;"张三")
);


方法2&＃xff1a;

题目需求

查询学过编号为01&＃xff0c;并且学过编号为02课程的学生信息

分析过程

• 课程编号&＃xff1a;Score——>c_id&＃xff08;课程编号&＃xff09;
• 学生信息&＃xff1a;Student——>*&＃xff08;学生信息&＃xff09;

SQL实现

-- 自己的方法&＃xff1a;通过自连接实现
select s1.*
from Student s1
where s_id in (select s2.s_id from Score s2join Score s3on s2.s_id&＃61;s3.s_idwhere s2.c_id&＃61;&＃39;01&＃39; and s3.c_id&＃61;&＃39;02&＃39;
);-- 方法2&＃xff1a;直接通过where语句实现
select s1.*
from Student s1, Score s2, Score s3
where s1.s_id&＃61;s2.s_id
and s1.s_id&＃61;s3.s_id
and s2.c_id&＃61;01 and s3.c_id&＃61;02;-- 方法3&＃xff1a;两个子查询
-- 1. 先查出学号
select sc1.s_id
from (select * from Score s1 where s1.c_id&＃61;&＃39;01&＃39;) sc1,(select * from Score s1 where s1.c_id&＃61;&＃39;02&＃39;) sc2
where sc1.s_id&＃61;sc2.s_id;-- 2.找出学生信息
select *
from Student
where s_id in (select sc1.s_id -- 指定学号是符合要求的from (select * from Score s1 where s1.c_id&＃61;&＃39;01&＃39;) sc1,(select * from Score s1 where s1.c_id&＃61;&＃39;02&＃39;) sc2
where sc1.s_id&＃61;sc2.s_id);


1. 先从Score表中看看哪些人是满足要求的&＃xff1a;01-05同学是满足的

通过自连接查询的语句如下&＃xff1a;

查询出学号后再匹配出学生信息&＃xff1a;

通过where语句实现&＃xff1a;

方法3的实现&＃xff1a;

题目需求

查询学过01课程&＃xff0c;但是没有学过02课程的学生信息&＃xff08;注意和上面&＃x1f446;题目的区别&＃xff09;

分析过程

使用的表和字段是相同的&＃xff1a;

• 课程编号&＃xff1a;Score——>c_id&＃xff08;课程编号&＃xff09;
• 学生信息&＃xff1a;Student——>*&＃xff08;学生信息&＃xff09;

SQL实现

首先看看哪些同学是满足要求的&＃xff1a;只有06号同学是满足的

下面先介绍两种自己犯过的错误思路&＃xff1a;

错误思路1&＃xff1a;直接将上面一题的结果全部排出&＃xff0c;导致那些没有学过01课程的学生也出现了&＃xff1a;07&＃xff0c;08

select s1.*
from Student s1
where s_id not in ( -- 直接将上面一题的结果全部排出&＃xff0c;导致那些没有学过01课程的学生也出现了&＃xff1a;07&＃xff0c;08select s2.s_id from Score s2join Score s3on s2.s_id&＃61;s3.s_idwhere s2.c_id&＃61;&＃39;01&＃39; and s3.c_id &＃61;&＃39;02&＃39;
);


错误思路2&＃xff1a;将上面题目中的02课程直接取反&＃xff0c;导致同时修过01&＃xff0c;02&＃xff0c;03或者只修01&＃xff0c;03的同学也会出现

select s1.*
from Student s1
where s_id in (select s2.s_id from Score s2join Score s3on s2.s_id&＃61;s3.s_idwhere s2.c_id&＃61;&＃39;01&＃39; and s3.c_id !&＃61;&＃39;02&＃39; -- 直接取反是不行的&＃xff0c;因为修改(01,02,03)的同学也会出现
);


正确思路

下面介绍的是正确解答过程&＃xff0c;方法1&＃xff1a;

-- 方法1&＃xff1a;根据两种修课情况来判断select s1.*
from Student s1
where s1.s_id in (select s_id from Score where c_id&＃61;&＃39;01&＃39;) -- 修过01课程&＃xff0c;要保留
and s1.s_id not in (select s_id from Score where c_id&＃61;&＃39;02&＃39;); -- 哪些人修过02&＃xff0c;需要排除


方法2&＃xff1a;先把06号学生找出来

select * from Student where s_id in (select s_id from Score where c_id&＃61;&＃39;01&＃39; -- 修过01课程的学号and s_id not in (select s_id -- 不能修02课程from Score where c_id&＃61;&＃39;02&＃39;)
);


如何Score中找出06号学生

如何找出06号学生&＃x1f603;

select s_id
from Score
where c_id&＃61;&＃39;01&＃39; -- 修过01课程的学号
and s_id not in (select s_id -- 不能修过02课程from Score where c_id&＃61;&＃39;02&＃39;)