共计 10288 个字符，预计需要花费 26 分钟才能阅读完成。
以下是 SQL 面试必备的经典的 50 道题目，每道题都有博主本人的解题思路和对应的 SQL 语句。
每道题的思路与答案均为博主本人主观理解，仅供参考。
环境：MySQL8.0
可视化工具：Navicat
1、查询课程编号为 01 的课程比 02 的课程高的所有学生的学号和成绩
解题思路：
(1) 先把课程为 01 的学号和成绩找出来 as 表 a
(2) 再把课程为 02 的学号和成绩找出来 as 表 b
(3) 用 inner join 将表 a 和表 b 按照 s_id 连接起来
(4) 最后用 where 筛选表 a 成绩大于表 b 成绩的学生编号
select a.s_id
from
(select s_id,s_score from score where c_id='01') as a
inner join
(select s_id,s_score from score where c_id='02') as b on a.s_id=b.s_id
where a.s_score>b.s_score;2、查询平均成绩大于 60 分的学生的学号和平均成绩
解题思路：
(1) 先用 group by 对 s_id 进行分组
(2) 再用 having 过滤平均分大于 60
tips：group by 里的东西必须是 select 里的东西，除非是统计函数(avg,max 等)
select s_id,avg(s_score)
from score
group by s_id
having avg(s_score)>60;3、查询所有学生的学号、姓名、选课数、总成绩
解题思路：
(1) 姓名在 student 表，成绩在 score 表，因此需要连接两表；student 表左连接 score 表，这样才能保证保留所有学生的信息
(2) 按 s_id 和 s_score 进行分组
(3) 选课数使用 count()，总成绩使用 sum(if(…))
select
a.s_id,
a.s_name,
count(b.c_id),
sum(if(b.s_score is null,0,b.s_score))
from student as a
left join score as b on a.s_id=b.s_id
group by a.s_id,a.s_name;4、查询姓“猴”的老师的人数
解题思路：
(1) 使用 like 和 % 进行模糊查询
(2) 人数使用 count() 函数
select count(t_id)
from teacher
where t_name like '猴 %';5、查询没学过“张三”老师课的学生的学号、姓名
解题思路：
(1) 先找出学过 ” 张三 ” 老师可的学生，这些学生以外的学生就是没学过 ” 张三 ” 老师课的
(2) 在 teacher 表中获取 ” 张三 ” 的 t_id，在 course 表中获取所有老师 t_id 和课程 c_id，在 score 表中获取学生 S_id 和课程 c_id
(3) teacher 表与 course 表按 t_id 内连接，course 表与 score 表按 s_id 内连接，然后选出学生 s_id
(4) 最后从 student 表中中过滤不在上面的学生 s_id
select s_id,s_name
from student
where s_id not in(
select c.s_id
from
(select t_id from teacher where t_name='张三') as a
inner join
(select t_id, c_id from course) as b on a.t_id=b.t_id
inner join
(select s_id, c_id from score) as c on b.c_id=c.c_id
);6、查询学过“张三”老师所教的所有课的同学的学号和姓名
解题思路：
(1) 首先查找 ” 张三 ” 教的所有课程 c_id：将 teahcer 表和 course 表按 t_id 内连接
(2) 接着查找学过以上课程的学生：将 student 表和 score 表按 s_id 内连接
(3) 最后对 s_id 进行 group by，过滤所选课程数量等于 ” 张三 ” 所教课程数量
select a.s_id,a.s_name
from student as a
join score as b on a.s_id=b.s_id
where b.c_id in (-- (2)接着查找学过以上课程的学生：将 student 表和 score 表按 s_id 内连接
-- (1)首先查找 "张三" 教的所有课程 c_id：将 teahcer 表和 course 表按 t_id 内连接
select b.c_id
from teacher as a
join course as b on a.t_id=b.t_id
where a.t_name='张三'
)
-- (3)最后对 s_id 进行 group by，过滤所选课程数量等于 "张三" 所教课程数量
group by a.s_id
having count(b.c_id)=(select count(b.c_id)
from teacher as a
join course as b on a.t_id=b.t_id
where a.t_name='张三'
);7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号和姓名
解题思路：
(1) 查出学过编号 01 课程学生 s_id 作为表 a
(2) 查找学过编号 02 课程学生 s_id 作为表 b
(3) 将表 a 和表 b 内连接，选出学生编号 s_id
(4) 最后从 student 表筛选学生编号包含以上 s_id 的学生
select s_id,s_name
from student
where s_id in (
select a.s_id
from
(select s_id from score where c_id='01') as a
join
(select s_id from score where c_id='02') as b on a.s_id=b.s_id
);8、查询课程编号为“02”的总成绩
select sum(s_score)
from score
group by c_id
having c_id='02';9、查询所有课程成绩小于 60 分的学生的学号和姓名
解题思路：
(1) 将 score 表按 s_id 进行 group by，过滤条件为课程最大成绩小于 60(如果最大成绩都小于 60，那就意味所有成绩都小于 60)
(2) 从 student 表中选出学生编号包含以上 s_id 的学生
select s_id,s_name
from student
where s_id in (
select s_id
from score
group by s_id
having max(s_score)<60
);10、查询没有学全所有课的学生的学号和姓名
解题思路：
(1) 首先从 course 表获取所有课程的总数
(2) 接着 student 表左连接 score 表并按 student.s_id 进行 group by，过滤条件是每个学生的课程数量小于第一步的课程总数
(3) 最后从 student 表中筛选出学生编号包含以上 s_id 的学生
tips： 可能存在一门课也没有学的学生
select a.s_id,a.s_name
from
student as a
left join score as b on a.s_id=b.s_id
group by a.s_id
having count(b.c_id)<(select count(c_id) from course
);11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名
解题思路：
(1) 查找出学号为 ”01″ 的学生所学课程编号 c_id
(2) student 表左连接 score 表，选出 c_id 有包含上面 c_id 的学生
(3) 接着对 s_id 进行 group by，过滤 s_id!=1，最后选择学生编号和姓名
select a.s_id,a.s_name
from
student as a
left join score as b on a.s_id=b.s_id
where b.c_id in (select c_id from score where s_id='01')
group by a.s_id
having a.s_id<>'01';12. 查询和“01”号同学所学课程完全相同的其他同学的学号
解题思路：
(1) 对 score 表按 s_id 进行 group by，对每个 s_id 的 c_id 进行 group_concat 转换，结果作为表 a
(2) 查出 ”01″ 同学所学课程编号 c_id，并进行 group_concat 转换，结果作为表 b，将表 a 与表 b 按照 group_concat 的结果进行内连接
(3) 以上结果就是与 ”01″ 同学课程完全相同的同学(包括 ”01″)，最后筛选学生编号不为 ”01″ 的
select a.s_id
from
(select s_id,group_concat(c_id order by c_id separator ',') as c_id_str
from score
group by s_id) as a
inner join
(select group_concat(c_id order by c_id separator ',') as c_id_str
from score
where s_id='01') as b on a.c_id_str=b.c_id_str
where a.s_id<>'01';13、查询没学过“张三”老师讲授的任一门课程的学生姓名
解题思路：
(1) 查出 ” 张三 ” 的教师编号 t_id as 表 a
(2) 表 a 按 t_id 内连接 course 表，course 表再按 c_id 内连接 score 表，便选出学过 ” 张三 ” 课的学生编号 s_id
(3) 最后 student 表中选出不包含上面编号的学生
select s_name
from student
where s_id not in (
select distinct c.s_id
from
(select t_id from teacher where t_name='张三') as a
inner join
(select t_id,c_id from course) as b on a.t_id=b.t_id
inner join
(select s_id,c_id from score) as c on b.c_id=c.c_id
);14、查询有两门及以上课程不及格的同学的学号、姓名和平均成绩
解题思路：
(1) 筛选 score 表中 score 小于 60 的记录，然后按 s_id 进行 group by，过滤条件是课程数大于 2，最后选出这些 s_id
(2) 将上面结果作为表 a 内连接 score 表，score 表按 s_id 内连接 student 表，如此一来即可获得有两门及其以上不及格课程同学的信息
(3) 对以上联合表按 s_id 进行 group by，计算每个 s_id 的平均分
select a.s_id,c.s_name,avg(b.s_score)
from
(select s_id from score where s_score<60 group by s_id having count(c_id)>=2) as a
inner join
(select s_id,s_score from score) as b on a.s_id=b.s_id
inner join
(select s_id,s_name from student) as c on a.s_id=c.s_id
group by a.s_id;15、检索“01”课程分数小于 60、按分数降序排列的学生信息
解题思路：
(1) 从 score 表查找 ”01″ 课程分数小于 60 的学生编号 s_id 和 ”01″ 课程分数，并按按分数降序排序
(2) 将上面结果作为表 a 与 student 表内连接，最后输出学生信息
select b.*,a.s_score
from
(select s_id,s_score
from score
where c_id='01' and s_score<60
order by s_score desc) as a
inner join student as b on a.s_id=b.s_id;16、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
解题思路：
(1) 将 student 表与 score 表左连接，可以得到学生信息与各科成绩
(2) 因为要按照平均成绩来排序，所以将第一步的连接表按 s_id 进行 group by
(3) 最终显示格式为学生编号、学生姓名、01 成绩、02 成绩、03 成绩、平均成绩，因此需要使用到 case when 语句
select
a.s_id,
a.s_name,
-- 如果 c_id 是 01，那么这一列显示 01 课程对应的成绩，否则为空
max(case when b.c_id='01' then b.s_score else null end) as '01',
max(case when b.c_id='02' then b.s_score else null end) as '02',
max(case when b.c_id='03' then b.s_score else null end) as '03',
avg(b.s_score) as avg
from
(select s_id,s_name from student) as a
left join
(select s_id,c_id,s_score from score) as b on a.s_id=b.s_id
group by a.s_id
order by avg desc;17、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率（及格为 >=60，中等为：70-80，优良为：80-90，优秀为：>=90）
解题思路：
(1) 显示列中有分数和课程名字，所以需要将 score 表与 course 表内连接
(2) 将上一步的连接表按课程编号 c_id 进行 group by，分组后即可获取每个课程的最高、低分和平均分
(3) 计算及格率中等率等需要使用 case when
select
a.c_id,
a.c_name,
max(b.s_score) as '最高分',
min(b.s_score) as '最低分',
avg(b.s_score) as '平均分',
-- 如果成绩大于等于 60 就标记为 1，否则标记为 0，最后计算平均值就得到对应的合格率
avg(case when b.s_score>=60 then 1 else 0 end) as '及格率',
avg(case when b.s_score>=70 and b.s_score<80 then 1 else 0 end) as '中等率',
avg(case when b.s_score>=80 and b.s_score<90 then 1 else 0 end) as '优良率',
avg(case when b.s_score>=90 then 1 else 0 end) as '优秀率'
from
course as a
inner join score as b on a.c_id=b.c_id
group by a.c_id;18、按各科成绩进行排序，并显示排名
解题思路：
(1) 因为需要显示排名，因此这里需要使用 row_number()函数，相似的还有 rank()和 dense_rank()
(2) 分别显示课程编号、学生编号、课程成绩、排名
select
c_id,
s_id,
s_score,
row_number() over (partition by c_id order by s_score desc) as 'rank'
from
score;19、查询学生的总成绩并进行排名
解题思路：
(1) 将 score 表按学生编号 s_id 进行 group by
(2) 使用 row_number()对 sum(s_score)进行排序
select
s_id,
sum(s_score) as sum_score,
row_number() over(order by sum(s_score) desc) as 'rank'
from
score
group by
s_id;20、查询不同老师所教不同课程的平均分并从高到低显示
解题思路：
(1) 不同老师不同课程的平均分：每个老师教的每一门课的平均分
(2) 需要的字段有老师编号、课程编号、分数，因此需要将 course 表左连接 score 表
(3) 接着对老师编号和课程编号进行 group by
(4) 输出老师编号、课程编号、分数，按 avg(score.s_score) 降序排列
select
a.t_id,
a.c_id,
avg(b.s_score)
from
course as a
left join score as b on a.c_id=b.c_id
group by a.t_id,a.c_id
order by avg(b.s_score) desc;21、查询所有课程的成绩第 2 名到第 3 名的学生信息及该课程成绩
解题思路：
(1) 需要有学生信息和课程成绩，因此需要连接 student 表和 score 表
(2) 使用 row_number() 函数按课程编号 c_id 分组并按成绩 s_score 降序排列
(3) 最后从上面的结果中筛选排名为第 2 名到第 3 名的记录
select *
from (
select
b.*,
a.c_id,
a.s_score,
row_number() over(partition by a.c_id order by a.s_score desc) as 'rank'
from
score as a
left join student as b on a.s_id=b.s_id
) as info
where info.rank in (2,3);22、使用分段 [100-85]、[85-70]、[70-60]、[<60] 来统计各科成绩，分别统计各分数段人数、课程 ID 和课程名称
解题思路：
(1) 需要的字段有课程名称和分数，因此需要连接 course 表和 score 表
(2) 统计各科成绩分数段人数，所以要先对课程编号进行 group by
(3) 接着就要使用 sum() 函数和 case when 来进行分段
tips： 也可以使用 count()，但是 count 中 else 后面得是 null 不能是 0
select
a.c_id,
a.c_name,
sum(case when b.s_score<=100 and b.s_score>=85 then 1 else 0 end) as '[100-85]',
sum(case when b.s_score<85 and b.s_score>=70 then 1 else 0 end) as '(85-70]',
sum(case when b.s_score<70 and b.s_score>=60 then 1 else 0 end) as '(70-60]',
sum(case when b.s_score<60 then 1 else 0 end) as '(<60)'
from
course as a
left join score as b on a.c_id=b.c_id
group by a.c_id;23、查询学生平均成绩及其名次
解题思路：
(1) 学生一定要包含所有学生，因此 score 中未包含所有学生，因此需要连接 student 表和 score 表
(2) 要查询每个学生的平均成绩，就需要对 s_id 进行 group by
(3) 使用 rank() 函数对平均分排序和打名次
select
a.s_id,
avg(b.s_score) as avg_score,
rank() over(order by avg(b.s_score) desc) as 'rank'
from
student as a
left join score as b on a.s_id=b.s_id
group by a.s_id;24、查询各科成绩前三名的记录（不考虑成绩并列情况）
解题思路：
(1) 首先使用 row_number()函数对 score 表中所有课程进行分组并对每门课程的所有学生分数进行排序
(2) 从上面的结果中筛选排序小于等于 3 的，则为各科成绩前三名
select *
from (
select
c_id,
s_id,
s_score,
row_number() over(partition by c_id order by s_score desc) as 'row_number'
from score
) as info
where info.row_number<=3;25、查询每门课程被选修的学生数
解题思路：
(1) 在 score 表中对 c_id 进行 group by
(2) 用 count()函数统计每个 c_Id 下的学生数
select c_id,count(s_id)
from score
group by c_id;26、查询出只有两门课程的全部学生的学号和姓名
解题思路：
(1) 需要使用到的字段有课程和姓名，因此连接 student 表和 score 表
(2) 要计算每个学生的选课数，因此先对 s_id 进行 group by
(3) 经过 group by 后，过滤条件为选课数量 count(c_id) 等于两门
select
a.s_id,a.s_name
from
student as a
left join score as b on a.s_id=b.s_id
group by a.s_id
having count(c_id)=2;27、查询男生、女生人数
解题思路：
(1) 直接在 student 表中对性别 s_sex 进行 group by
(2) 随后使用 count()函数即可统计男生人数和女生人数
select
s_sex,count(s_id)
from
student
group by s_sex;28、查询名字中含有 ” 风 ” 字的学生信息
解题思路：
(1) 在 student 表中操作即可
(2) 在筛选条件中使用模糊查询 ”%%”
select
*
from
student
where s_name like '% 风 %';29、查询 1990 年出生的学生名单
解题思路：
(1) 在 student 表中操作即可
(2) 筛选条件为出生日期 s_birth 的年份是 1990，即可得到符合条件的学生
tips： 这里会使用到 year()函数，year(datetime)可以得到年份
select
*
from
student
where year(s_birth)=1990;30、查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
解题思路：
(1) 需要的字段包含有姓名和成绩，因此连接 student 表和 score 表
(2) 计算平均成绩需要先对学号进行 group by
(3) 过滤条件为平均成绩 avg(s_score) 大于等于 85 分，这样借的到符合条件的成绩
select
a.s_id,a.s_name,avg(b.s_score) as avg
from
student as a
left join score as b on a.s_id=b.s_id
group by a.s_id
having avg(b.s_score)>=85;31、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
解题思路：
(1) 在 score 表中操作即可
(2) 求每门课的平均成绩，需要先对课程编号进行 group by
(3) 最后使用 order by 对平均成绩升序排序，平均成绩相同时按课程号降序排列
select
c_id,avg(s_score)
from
score
group by c_id
order by avg(s_score),c_id desc;32、查询课程名称为 ” 数学 ”，且分数低于 60 的学生姓名和分数
解题思路：
(1) 需要使用到的字段有课程名称、姓名和分数，因此要连接 student、score 和 course 这三表
(2) 筛选条件为课程名称 c_name 为 ” 数学 ”，且分数 s_score 低于 60
select
a.s_name,b.s_score
from
student as a
left join score as b on a.s_id=b.s_id
left join course as c on b.c_id=c.c_id
where c.c_name='数学' and b.s_score<60;33、查询所有学生的课程及分数情况
解题思路：
(1) 题目中说的是所有学生，因此需要使用 student 表左连接 score 表(因为可能存在没有选课的学生)
(2) 需要的是每个学生的情况，因此先对学生编号进行 group by
(3) 返回的字段格式为学生编号、语文成绩、数学成绩、英语成绩，因此要用到 case when
select
a.s_id,
max(case when c.c_name='语文' then b.s_score else null end) as '语文',-- case when 当课程名字 c_name 是某门课时则得到这门课对应的成绩 s_core
max(case when c.c_name='数学' then b.s_score else null end) as '数学',
max(case when c.c_name='英语' then b.s_score else null end) as '英语'
-- 因为 group by 要与 select 列一致，所以 case when 需要加修饰 max
from
student as a
left join score as b on a.s_id=b.s_id
left join course as c on b.c_id=c.c_id
group by a.s_id;34、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
解题思路：
(1) 首先从 score 表中选出每个学生自己所选课程的成绩都在 70 分以上的学生编号
(2) 需要选出的字段有姓名、课程名称以及分数，因此连接 student、score 和 course 三张表
(3) 连接表的筛选条件为 s_id 含有第 1 步中的那些学生编号
select
a.s_name,
c.c_name,
b.s_score
from
student as a
left join score as b on a.s_id=b.s_id
left join course as c on b.c_id=c.c_id
where a.s_id in (
select
s_id
from
score
group by s_id
having min(s_score)>=70
);35、查询学生不及格的课程并按课程号从大到小排列
解题思路：
(1) 在 score 表中操作即可
select
c_id,
s_id,
s_score
from
score
where s_score<60
order by c_id desc;
