Day 1

1. 查询” 01 “课程⽐比” 02 “课程成绩⾼高的学⽣生的信息及课程分数（难度等级：三星）

方法一

select a.c_id, a.s_score, b.c_id, b.s_score, st.* from Score a 
left join Score b on a.s_id = b.s_id
left join Student st on a.s_id = st.s_id
where a.c_id = '01' and b.c_id = '02' and a.s_score > b.s_score;

方法二

select st.*, a.s_score, b.s_score from Student st, 
(select * from Score where c_id = '01') as a,
(select * from Score where c_id = '02') as b
where a.s_id = b.s_id and a.s_score > b.s_score and a.s_id = st.s_id

结果截图

这个题目的求解其实给我们提供了一种思路，如果想要行与行之间进行比较，一种是与自身进行join，另一种就是把子查询放到from子句里，还有一种是使用group by，具体参见第13题

1. 查询平均成绩⼤大于等于 60 分的同学的学⽣生编号和学⽣生姓名和平均成绩

**

select a.s_id, s_name, avg(s_score) as avg_score from Score a 
left join Student b on a.s_id = b.s_id
group by a.s_id 
having avg_score >= 60;

结果截图

1. 查询在 Score 表中成绩存在的学⽣生信息

**

select * from Student st 
where st.s_id in
(select s_id from Score sc where sc.s_score is not null);

结果截图

1. 查询所有同学的学生编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩的显示为 null)

**

select st.s_id, st.s_name, count(*) as num, sum(s_score) as total_score from Student st
left join Score sc 
on st.s_id = sc.s_id
group by st.s_id;

结果截图

1. 查询所有同学的学生编号，姓名，选课总数，总成绩，“01”课程成绩，“02”课程成绩，“03”课程成绩，如果该门课没成绩则显示为null（难度等级：三星）

**

select st.s_id, st.s_name, count(*) as num, sum(sc.s_score) as total_score, 
sum(if(c_id = '01', s_score, null)) as score1, 
sum(if(c_id = '02', s_score, null)) as score2,
sum(if(c_id = '03', s_score, null)) as score3 
from Student st 
left join Score sc on st.s_id = sc.s_id
group by st.s_id;

结果截图

注意：对于每个组别内的数据，如果是唯一值，则可以直接选取；如果不是唯一值，则必须应用聚合函数。
上面我们应用sum进行求和，组别内的null被视为0处理了

1. 查询学过「张三」⽼师授课的同学的信息

**
方法一

select st.* from Score sc 
left join Student st on sc.s_id = st.s_id
left join Course c on sc.c_id = c.c_id 
left join Teacher t on c.t_id = t.t_id
where t_name = '张三';

方法二

select * from Student where s_id in 
(select s_id from Score s, Course c, Teacher t 
where s.c_id = c.c_id and c.t_id = t.t_id and t.t_name = '张三');

结果截图

1. 查询没有学全所有课程的同学的信息

**

select * from Student 
where s_id in
(select s_id from Score 
group by s_id having count(*) < 3);

结果截图

1. 查询⾄少有⼀门课与学号为” 01 “的同学所学相同的同学的信息

**

select * from Student 
where s_id in 
(select distinct s_id from Score where c_id in 
(select c_id from Score where s_id = '01'));

结果截图

1. 查询和” 01 “号的同学学习的课程完全相同的其他同学的信息（难度等级：五颗星）

**

select * from Student where s_id in
(select s_id from Score where c_id in
(select c_id from Score where s_id = '01') 
and s_id != '01' group by s_id having count(*) >= 3)

结果截图

1. 查询没学过”张三”⽼师讲授的任意一⻔课程的学⽣姓名

**

select s_name from Student where s_id not in
(select distinct s_id from Score s, Course c, Teacher t 
where s.c_id = c.c_id and c.t_id = t.t_id and t.t_name = '张三');

结果截图

有时候使用inner join就能实现相应的查询结果，不过个人在实操中倒是应用left join多一些