-- 方法1 select a.* ,b.s_score as 1_score ,c.s_score as 2_score from Student a join Score b on a.s_id = b.s_id and b.c_id = '01' -- 两个表通过学号连接,指定01 left join Score c on a.s_id = c.s_id and c.c_id='02' or c.c_id is NULL -- 指定02,或者c中的c_id直接不存在 -- 为NULL的条件可以不存在,因为左连接中会直接排除c表中不存在的数据,包含NULL where b.s_score > c.s_score; -- 判断条件
-- 方法2:直接使用where语句 select a.* ,b.s_score as 1_score ,c.s_score as 2_score from Student a, Score b, Score c where a.s_id=b.s_id -- 列出全部的条件 and a.s_id=c.s_id and b.c_id='01' and c.c_id='02' and b.s_score > c.s_score; -- 前者成绩高
第二种方法实现:
题目2
题目要求
查询'01'课程比'02'课程成绩低的学生的信息及课程分数(题目1是成绩高)
SQL实现
类比题目1的实现过程
-- 方法1:通过连接方式实现 select a.* ,b.s_score as 1_score ,c.s_score as 2_score from Student a left join Score b on a.s_id=b.s_id and b.c_id='01' or b.c_id=NULL -- 包含NULL的数据 join score c on a.s_id=c.s_id and c.c_id='02' where b.s_score < c.s_score;
-- 通过where子句实现 select a.* ,b.s_score as 1_score ,c.s_score as 2_score from Student a, Score b, Score c where a.s_id=b.s_id and a.s_id=c.s_id and b.c_id='01' and c.c_id='02' and b.s_score < c.s_score; -- 前者比较小
题目3
题目需求
查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SQL实现
-- 执行顺序:先执行分组,再执行avg平均操作 select b.s_id ,b.s_name ,round(avg(a.s_score), 2) as avg_score from Student b join Score a on b.s_id = a.s_id group by b.s_id -- 分组之后查询每个人的平均成绩 having avg_score >= 60;
-- 附加题:总分超过200分的同学 select b.s_id ,b.s_name ,round(sum(a.s_score),2) as sum_score -- sum求和 from Student b join Score a on b.s_id=a.s_id group by b.s_id having sum_score > 200;
附加题:总分超过200分的同学
题目4
题目要求
查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
SQL实现1-两种情况连接
平均分小于60
select b.s_id ,b.s_name ,round(avg(a.s_score), 2) as avg_score -- round四舍五入函数 from Student b join Score a on b.s_id = a.s_id group by b.s_id -- 分组之后查询每个人的平均成绩 having avg_score < 60;
结果为:
没有成绩的同学:
select a.s_id ,a.s_name ,0 as avg_score from Student a where a.s_id not in ( -- 学生的学号不在给给定表的学号中 select distinct s_id -- 查询出全部的学号 from Score );
最后将两个部分的结果连起来即可:通过union方法
SQL实现2-ifnull函数判断
使用ifnull函数
select S.s_id ,S.s_name ,round(avg(ifnull(C.s_score,0)), 2) as avg_score -- ifnull 函数:第一个参数存在则取它本身,不存在取第二个值0 from Student S left join Score C on S.s_id = C.s_id group by s_id having avg_score < 60;
使用null判断
select a.s_id ,a.s_name ,ROUND(AVG(b.s_score), 2) as avg_score from Student a left join Score b on a.s_id = b.s_id GROUP BY a.s_id HAVING avg_score < 60 or avg_score is null; -- 最后的NULL判断
题目5
题目需求
查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SQL实现
select a.s_id ,a.s_name ,count(b.c_id) as course_number -- 课程个数 ,sum(b.s_score) as scores_sum -- 成绩总和 from Student a leftjoin Score b on a.s_id = b.s_id groupby a.s_id,a.s_name;
题目6
题目需求
查询“李”姓老师的数量
SQL实现
selectcount(t_name) from Teacher where t_name like'李%'; -- 通配符
这题怕是最简单的吧😭
题目7
题目需求
查询学过张三老师授课的同学的信息
SQL实现
-- 方法1:通过张三老师的课程的学生来查找;自己的方法 select * -- 3. 通过学号找出全部学生信息 from Student where s_id in ( select s_id -- 2.通过课程找出对应的学号 from Score S join Course C on S.c_id = C.c_id -- 课程表和成绩表 where C.t_id=(select t_id from Teacher where t_name='张三') -- 1.查询张三老师的课程 );
-- 方法2:通过张三老师的课程来查询 select s1.* from Student s1 join Score s2 on s1.s_id=s2.s_id where s2.c_id in ( select c_id from Course c where t_id=( -- 1. 通过老师找出其对应的课程 select t_id from Teacher t where t_name='张三' ) )
-- 方法3 select s.* from Teacher t leftjoin Course c on t.t_id=c.t_id -- 教师表和课程表 leftjoin Score sc on c.c_id=sc.c_id -- 课程表和成绩表 leftjoin Student s on s.s_id=sc.s_id -- 成绩表和学生信息表 where t.t_name='张三';
自己的方法:
方法2来实现:
方法3实现:
题目8
题目需求
找出没有学过张三老师课程的学生
SQL实现
select * -- 3. 通过学号找出全部学生信息 from Student where s_id notin ( -- 2.通过学号取反:学号不在张三老师授课的学生的学号中 select s_id from Score S join Course C on S.c_id = C.c_id where C.t_id=(select t_id from Teacher where t_name ='张三') -- 1.查询张三老师的课程 );
-- 方法2: select * from Student s1 where s1.s_id notin ( select s2.s_id from Student s2 join Score s3 on s2.s_id=s3.s_id where s3.c_id in( select c.c_id from Course c join Teacher t on c.t_id=t.t_id where t_name='张三' ) );
-- 方法3 select s1.* from Student s1 join Score s2 on s1.s_id=s2.s_id where s2.c_id notin ( select c_id from Course c where t_id=( -- 1. 通过老师找出其对应的课程 select t_id from Teacher t where t_name='张三' ) );
方法2:
题目9
题目需求
查询学过编号为01,并且学过编号为02课程的学生信息
SQL实现
-- 自己的方法:通过自连接实现 select s1.* from Student s1 where s_id in ( select s2.s_id from Score s2 join Score s3 on s2.s_id=s3.s_id where s2.c_id='01' and s3.c_id='02' );
-- 方法2:直接通过where语句实现 select s1.* from Student s1, Score s2, Score s3 where s1.s_id=s2.s_id and s1.s_id=s3.s_id and s2.c_id=01 and s3.c_id=02;
-- 方法3:两个子查询 -- 1. 先查出学号 select sc1.s_id from (select * from Score s1 where s1.c_id='01') sc1, (select * from Score s1 where s1.c_id='02') sc2 where sc1.s_id=sc2.s_id;
-- 2.找出学生信息 select * from Student where s_id in (select sc1.s_id -- 指定学号是符合要求的 from (select * from Score s1 where s1.c_id='01') sc1, (select * from Score s1 where s1.c_id='02') sc2 where sc1.s_id=sc2.s_id);
先从Score表中看看哪些人是满足要求的:01-05同学是满足的
通过自连接查询的语句如下:
查询出学号后再匹配出学生信息:
通过where语句实现:
方法3的实现:
题目10
题目需求
查询学过01课程,但是没有学过02课程的学生信息(注意和上面👆题目的区别)
SQL实现
首先看看哪些同学是满足要求的:只有06号同学是满足的
错误思路1
直接将上面一题的结果全部排出,导致那些没有学过01课程的学生也出现了:07,08
select s1.* from Student s1 where s_id notin ( -- 直接将上面一题的结果全部排出,导致那些没有学过01课程的学生也出现了:07,08 select s2.s_id from Score s2 join Score s3 on s2.s_id=s3.s_id where s2.c_id='01'and s3.c_id ='02' );
错误思路2
将上面题目中的02课程直接取反,导致同时修过01,02,03或者只修01,03的同学也会出现
select s1.* from Student s1 where s_id in ( select s2.s_id from Score s2 join Score s3 on s2.s_id=s3.s_id where s2.c_id='01'and s3.c_id !='02'-- 直接取反是不行的,因为修改(01,02,03)的同学也会出现 );
正确思路
https://www.jianshu.com/p/9abffdd334fa
-- 方法1:根据两种修课情况来判断
select s1.* from Student s1 where s1.s_id in (select s_id from Score where c_id='01') -- 修过01课程,要保留 and s1.s_id notin (select s_id from Score where c_id='02'); -- 哪些人修过02,需要排除
!!!!!方法2:先把06号学生找出来
select * from Student where s_id in ( select s_id from Score where c_id='01'-- 修过01课程的学号 and s_id notin (select s_id -- 同时学号不能在修过02课程中出现 from Score where c_id='02') );