1. 查询两⻔及其以上不及格课程的同学的学号，姓名及其平均成绩

    **

    1. select sc.s_id, s_name, avg(s_score) as avg_score from Score sc 
    2. left join Student st on sc.s_id = st.s_id
    3. where s_score < 60
    4. group by s_id
    5. having count(*) >= 2;

    结果截图
    image.png

    1. 检索” 01 “课程分数⼩于 60，按分数降序排列的学⽣信息

    **

    1. select st.*, s_score from Score sc 
    2. left join Student st on sc.s_id = st.s_id
    3. where c_id = '01' and s_score < 60 
    4. order by s_score desc;

    结果截图
    image.png

    1. 按平均成绩从⾼到低显示所有学生的所有课程的成绩以及平均成绩（难度等级：三颗星）

    **

    1. select s_id, 
    2. sum(if(c_id = '01', s_score, null)) as score1, 
    3. sum(if(c_id = '02', s_score, null)) as score2, 
    4. sum(if(c_id = '03', s_score, null)) as score3, 
    5. avg(s_score) as avg_score from Score sc 
    6. group by s_id 
    7. order by avg_score desc

    结果截图
    image.png
    其实这里提供了一种把按行显示的数据转化为按列显示的思路，这样也就可以进行行与行之间的比较了，可以作为我们Day1中第一题的一个补充

    1. 查询各科成绩最高分、最低分和平均分，以如下形式显示:课程 ID，课程name，选修人数，最高分，最低分，平均分，及格率，中等率，优良率，优秀率(及格为>=60，中等为:70-80，优良为:80-90，优秀为:>=90)。要求查询结果按选修⼈数降序排列，若⼈数相同，则按课程号升序排列（难度等级：三颗星）

    **

    1. select s.c_id, c.c_name, count(*), max(s_score), min(s_score), avg(s_score), 
    2. sum(if(s_score >= 60, 1, 0)) / count(*) as pass,
    3. sum(if(s_score >= 70 and s_score < 80, 1, 0)) / count(*) as middle,
    4. sum(if(s_score >= 80 and s_score < 90, 1, 0)) / count(*) as good,
    5. sum(if(s_score >= 90, 1, 0)) / count(*) as excellence
    6. from Score s 
    7. left join Course c on s.c_id = c.c_id
    8. group by c_id
    9. order by count(*) desc, c_id asc

    结果截图
    image.png

    1. 按平均成绩进⾏行排序，显示总排名和各科排名，Score重复时合并名次（难度等级：四颗星）

    **

    1. select s.*, a.rank1, b.rank2, c.rank3, d.total_rank from Student s
    2. left join 
    3.       (select s_id, s_score, if(@tmp1 != s_score, @rank1 := @rank1 + 1, @rank1) as rank1, 
    4.     @tmp1 := s_score as tmp1 from Score, (select @rank1 := 1, @tmp1 := null) as t 
    5.       where c_id = '01' order by s_score desc) as a 
    6. on s.s_id = a.s_id
    7. left join
    8.       (select s_id, s_score, if(@tmp2 != s_score, @rank2 := @rank2 + 1, @rank2) as rank2, 
    9.     @tmp2 := s_score as tmp2 from Score, (select @rank2 := 1, @tmp2 := null) as t 
    10.       where c_id = '02' order by s_score desc) as b
    11. on s.s_id = b.s_id
    12. left join 
    13.       (select s_id, s_score, if(@tmp3 != s_score, @rank3 := @rank3 + 1, @rank3) as rank3, 
    14.     @tmp3 := s_score as tmp3 from Score, (select @rank3 := 1, @tmp3 := null) as t 
    15.       where c_id = '03' order by s_score desc) as c
    16. on s.s_id = c.s_id
    17. left join
    18.       (select s_id, avg_score, @total_rank := @total_rank + 1 as total_rank from 
    19.     (select s_id, avg(s_score) as avg_score from Score group by s_id) as d, (select @total_rank := 0) as t 
    20.       order by avg_score desc) as d
    21. on s.s_id = d.s_id
    22. order by total_rank;

    结果截图
    image.png
    然而我个人觉得实操中根本不会写这样长的sql查询语句，有这功夫不如写程序来解决这个问题

    1. 查询各科成绩前三名的记录（难度等级：六颗星）

    方案一

    1. -- step 1
    2. select * from Score a left join Score b 
    3. on a.c_id = b.c_id
    4. order by a.c_id, a.s_id;
    6. -- step 2
    7. select * from Score a left join Score b 
    8. on a.c_id = b.c_id and a.s_score < b.s_score 
    9. order by a.c_id, a.s_id;
    11. -- step 3
    12. select a.s_id, a.c_id, a.s_score, count(*) from Score a left join Score b 
    13. on a.c_id = b.c_id and a.s_score < b.s_score 
    14. group by a.s_id, a.c_id, a.s_score 
    15. order by a.c_id, a.s_id;
    17. -- step 4
    18. select a.s_id, a.c_id, a.s_score from Score a left join Score b 
    19. on a.c_id = b.c_id and a.s_score < b.s_score 
    20. group by a.s_id, a.c_id, a.s_score having count(*) < 3 
    21. order by a.c_id, a.s_id;

    结果截图
    image.png
    这种求解的思路值得好好品味：先是成绩表与自身通过课程id进行左连接；然后使得右表中学生的成绩都大于左表中学生的成绩；进行分组操作；过滤掉组内元素数小于3的；

    该组内行数为1时，表明此时左表对应的学生成绩在该课程下要么排名第一，要么排名第二（右表数据行为空时表示第一，右表数据行有数据时表示第二）
    该组内行数为2时，表明此时左表对应的学生成绩在该课程下要么排名第二，要么排名第三（同上）

    方案二

    1. (select * from Score 
    2. where c_id = '01'
    3. order by s_score desc limit 3 )
    4. union 
    5. (select * from Score 
    6. where c_id = '02'
    7. order by s_score desc limit 3)
    8. union
    9. (select * from Score 
    10. where c_id = '03'
    11. order by s_score desc limit 3);

    结果截图
    image.png
    很明显方案二没办法处理第三名同名的情况，这里只能固定的筛选9条数据，而方案一则是筛选出了10条数据，包括下面的方案三也是一样，只能固定的筛选9条数据，这里只是提供一种思路

    方案三

    1. select s_id, c_id, s_score, rank from
    2.     (select s.*, 
    3.     if(@tmp = c_id, @curRank := @curRank + 1, @curRank := 1) as rank, 
    4.     @tmp := c_id as tmp
    5.     from Score s, (select @curRank := 0, @tmp := null) t
    6.     order by c_id, s_score desc) as a
    7. where a.rank <= 3;

    截图截图
    image.png
    补充几个知识点如下所示：

    • sql语句中，使用@来定义一个变量。如：@tmp
    • sql语句中，使用:=来给变量赋值。如：@tmp := 1, 则变量tmp的值为1
    • sql语句中，if(A, B, C)表示，如果条件A成立，那么执行B，否则执行C
    1. 查询出只选修两⻔课程的学生学号和姓名

    **

    1. select st.s_id, st.s_name from Score sc 
    2. left join Student st on sc.s_id = st.s_id
    3. group by s_id having count(*) = 2

    结果截图
    image.png

    1. 查询名字中含有「⻛」字的学⽣信息

    **

    1. select * from Student where s_name like '%风%';

    结果截图
    image.png

    1. 查询 1990 年出⽣的学⽣名单

    **

    1. select * from Student where year(s_birth) = '1990';

    结果截图
    image.png

    1. 查询选修「张三」⽼师所授课程的学生中，成绩最⾼的学⽣信息及其成绩

    **

    1. select st.*, s.s_score from Score s 
    2. left join Student st on s.s_id = st.s_id
    3. left join Course c on s.c_id = c.c_id
    4. left join Teacher t on c.t_id = t.t_id
    5. where t.t_name = '张三'
    6. order by s_score desc limit 1;

    结果截图
    image.png