SQL练习记录（经典50题）

这个记录一下我练习经典50题的code。
题目来源是这个：图解SQL面试题：经典50题
代码练习的环境是NAVICAT，版本是MySQL8.0的，因为有窗口函数。

还有几个在做题过程中现学的教程，例如窗口函数（😂终于学窗口函数了）：
窗口函数教程看这里！简洁易懂
mysql8.0新特性 之 窗口函数
如何进行行列互换呢（case when方法学习）

给自己提个醒：
22、27、28、30、40、43和窗口函数那部分比较难，要回顾一下，还有那几道有注释的题。
43题非常难，case when 的用法超级不熟练！！！！

1.简单查询

1.查询名字开头是“猴”的所有学生

select *
from student
where 姓名 like "猴%";

2.查询名字以“猴”字结尾的学生

SELECT *
FROM student
WHERE 姓名 LIKE "%猴"

3.查询名字中含有“猴”的学生

select *
from student
where 姓名 like "%猴%"

4.查询以“孟”字开头的老师姓名

select count(教师姓名)
from teacher
where 教师姓名 like "孟%"

2.汇总分析

2.1汇总分析

5.面试题：查询课程编号为“0002”的总成绩

select sum(成绩)
from score
where 课程号 = "0002";

6.查询选了课程的学生人数（这个题目翻译成大白话就是：查询有多少人选了课程）

select count(distinct 学号) as 学生人数
from score

2.2 分组

7.查询各科成绩最高和最低的分， 以如下的形式显示：课程号，最高分，最低分

select 课程号,max(成绩) as 最高分,min(成绩) as 最低分
from score
group by 课程号;

8.查询每门课程被选修的学生数

select 课程号, count(学号)
from score
group by 课程号;

9.查询男生、女生人数

select 性别, count(*)
from student
group by 性别;

2.3.分组结果的条件

10.查询平均成绩大于60分学生的学号和平均成绩

/*分析:
平均成绩：展开来说就是计算每个学生的平均成绩
这里涉及到“每个”就是要分组了
平均成绩大于60分，就是对分组结果指定条件*/
select 学号, avg(成绩) as 平均成绩
from score
group by 学号
having avg(成绩) > 60;

11.查询至少选修两门课程的学生学号

/*
第1步，需要先计算出每个学生选修的课程数据，需要按学号分组
第2步，至少选修两门课程：也就是每个学生选修课程数目>=2，对分组结果指定条件
*/
select 学号, count(课程号) as 课程数量
from score 
group by 学号
having count(课程号) >=2;

12.查询同名同姓学生名单并统计同名人数

/*
1）查找出姓名相同的学生有谁，每个姓名相同学生的人数
查询结果：姓名,人数
条件：怎么算姓名相同？按姓名分组后人数大于等于2，因为同名的人数大于等于2
*/
select 姓名,count(姓名)
from student
group by 姓名
having count(姓名) >= 2;

13.查询不及格的课程并按课程号从大到小排列

select 课程号
from score
where 成绩 <= 60
order by 课程号 desc;

14.查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

SELECT 课程号, avg(成绩) as 平均成绩
from score
group by 课程号
order by 平均成绩 asc, 课程号 desc;

15.检索课程编号为“0004”且分数小于60的学生学号，结果按按分数降序排列

select 学号
from score
where 课程号 = "0004"
and 成绩 < 60 
order by 成绩 desc;

16.统计每门课程的学生选修人数(超过2人的课程才统计)
#要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序

select 课程号, count(学号) '选课人数'
from score
group by 课程号
having count(学号) >2
order by 选课人数 desc, 课程号 asc;

17.查询两门以上不及格课程的同学的学号及其平均成绩

/*
先分解题目：
1）[两门以上][不及格课程]限制条件
2）[同学的学号及其平均成绩]，也就是每个学生的平均成绩，显示学号，平均成绩
分析过程：
第1步：得到每个学生的平均成绩，显示学号，平均成绩
第2步：再加上限制条件：
1）不及格课程   (分组前筛选)
2）两门以上[不及格课程]：课程数目>2  （分组后筛选）
*/
select 学号, avg(成绩) as 平均成绩
from score
where 成绩 <60
group by 学号
having count(课程号)>2;

2.4 汇总分析(查询结构排序/分组指定条件)

18.查询学生的总成绩并进行排名

select 学号, sum(成绩) as 总成绩
from score
group by 学号
order by 总成绩 desc;

19.查询平均成绩大于60分的学生的学号和平均成绩

select 学号, avg(成绩) as 平均成绩
from score
group by 学号
having avg(成绩) > 60;

3.复杂查询

3.1 子查询

20.查询所有课程成绩小于60分学生的学号、姓名

select 学号,姓名
from student
where  学号 in (
select 学号 from score where 成绩 < 60
);

#另一个思路是用join：
select student.学号, student.姓名
from score 
inner join student
on score.`学号` = student.`学号`
where 成绩 < 60;

21.查询没有学全所有课的学生的学号、姓名

/*
吐血，强行用了两个子查询😂
*/
select 学号, 姓名
from student
where 学号 <