图解面试题：经典50题

已知有如下4张表：

学生表：student(学号,学生姓名,出生年月,性别)

成绩表：score(学号,课程号,成绩)

课程表：course(课程号,课程名称,教师号)

教师表：teacher(教师号,教师姓名)

1.汇总分析

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

/*【知识点】分组查询分析思路select查询结果 [总成绩：sum(成绩), 学号]from从哪张表中查找数据 [成绩表score]where查询条件 [没有]groupby 分组 [学生的总成绩：按照每个学生学号进行分组]orderby 排序 [按照总成绩进行排序：sum(成绩)];/*select学号 ,sum(成绩) from score groupby 学号orderby sum(成绩) ;

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

/*【知识点】分组+条件分析思路select查询结果 [学号, 平均成绩: avg(成绩)]from从哪张表中查找数据 [成绩表score]where查询条件 [没有]groupby 分组 [学号]having分组条件 [平均成绩大于60分：avg(成绩 ) >60]orderby 排序 [没有];/*select学号 ,avg(成绩) from score groupby 学号  havingavg(成绩 ) >60

2.复杂查询

-查询各学生的年龄（精确到月份）

/*【知识点】时间格式转化*/select学号 ,timestampdiff(month,出生日期 ,now())/12fromstudent ;

 -查询本月过生日的学生

select*fromstudentwheremonth(出生日期 ) =month(now())+2;

3.多表查询

-检索"0001"课程分数小于60，按分数降序排列的学生信息

思路如图：

selecta.*,b.成绩fromstudentasainnerjoinscoreasbona.学号 =b.学号whereb.成绩 <60andb.课程号 =01orderbyb.成绩desc;

-查询不同老师所教不同课程平均分从高到低显示

【知识点】分组+条件+排序+多表连接，思路如图

selecta.教师号,a.教师姓名,avg(c.成绩)fromteacherasainnerjoincourseasbona.教师号= b.教师号innerjoinscore  conb.课程号= c.课程号groupbya.教师姓名orderbyavg(c.成绩)desc;

-查询课程名称为"数学"，且分数低于60的学生姓名和分数

【知识点】多表连接，思路如图

selecta.姓名,b.成绩fromstudentasainnerjoinscoreasbona.学号 =b.学号innerjoincourse conb.课程号 =c.课程号whereb.成绩  <60andc.课程名称 ='数学';

-查询任何一门课程成绩在70分以上的姓名、课程名称和分数（与上题类似）

selecta.姓名,c.课程名称 ,b.成绩fromstudentasainnerjoinscoreasbona.学号=b.学号innerjoincourse conb.课程号 =c.课程号whereb.成绩 >70;

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

【知识点】分组+条件+多表连接

翻译成大白话:计算每个学号不及格分数个数，筛选出大于2个的学号并找出姓名，平均成绩，思路如图：

selectb.姓名,avg(a.成绩),a.学号fromscoreasainnerjoinstudentasbona.学号 =b.学号wherea.成绩 <60groupbya.学号havingcount(a.学号 ) >=2;

-查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

selectdistincta.学号 ,a.成绩 ,a.课程号fromscoreasainnerjoinscoreasbona.学号 =b.学号wherea.成绩 =b.成绩anda.课程号 != b.课程号 ;

-查询课程编号为“0001”的课程比“0002”的课程成绩高的所有学生的学号

【知识点】多表连接+条件，思路如图

selecta.学号from(select学号 ,成绩fromscorewhere课程号=01)asainnerjoin(select学号 ,成绩fromscorewhere课程号=02)asbona.学号 =b.学号innerjoinstudent conc.学号 =a.学号wherea.成绩 >b.成绩 ;

-查询学过编号为“0001”的课程并且也学过编号为“0002”的课程的学生的学号、姓名

思路如图

selecta.学号from(select学号 ,成绩fromscorewhere课程号=01)asainnerjoin(select学号 ,成绩fromscorewhere课程号=02)asbona.学号 =b.学号innerjoinstudent conc.学号 =a.学号wherea.成绩 >b.成绩 ;

-查询学过“孟扎扎”老师所教的所有课的同学的学号、姓名

​

思路如图

selects.学号 ,s.姓名,a.学号 ,b.课程号,c.教师号 ,c.教师姓名fromstudentassinnerjoinscoreasaons.学号 =a.学号innerjoincourse  bona.课程号 =b.课程号innerjointeacher conb.教师号 = c.教师号wherec.教师姓名 ='孟扎扎';

-查询没学过"孟扎扎"老师讲授的任一门课程的学生姓名 （与上题类似，"没学过"用not in来实现)

select姓名 ,学号fromstudentwhere学号notin(selecta.学号fromstudentasainnerjoinscoreasbona.学号 =b.学号innerjoincourseasconb.课程号 =c.课程号innerjointeacherasdonc.教师号 =d.教师号whered.教师姓名 ='孟扎扎');

-查询没学过“孟扎扎”老师课的学生的学号、姓名（与上题类似）

select学号, 姓名fromstudentwhere学号notin(select学号fromscorewhere课程号=(select课程号fromcoursewhere教师号 =(select教师号fromteacherwhere教师姓名 ='孟扎扎')));

 -查询选修“孟扎扎”老师所授课程的学生中成绩最高的学生姓名及其成绩（与上题类似,用成绩排名，用 limit 1得出最高一个）

selecta.姓名,b.成绩fromstudentasainnerjoinscoreasbona.学号=b.学号innerjoincourseasconb.课程号 =c.课程号innerjointeacherasdonc.教师号 = d.教师号whered.教师姓名 ='孟扎扎'orderbyb.成绩desclimit1;

-查询至少有一门课与学号为“0001”的学生所学课程相同的学生的学号和姓名

select学号 ,姓名fromstudentwhere学号in(selectdistinct(学号)fromscorewhere课程号in(select课程号fromscorewhere学号=0001))and学号 !=0001;

 -按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

【知识点】多表连接 新建字段 ，思路如图

selecta.学号,avg(a.成绩 ),max(casewhenb.课程名称  ='数学'thena.成绩elsenullend)as'数学',max(casewhenb.课程名称  ='语文'thena.成绩elsenullend)as'语文',max(casewhenb.课程名称  ='英语'thena.成绩elsenullend)as'英语'fromscoreasainnerjoincourseasbona.课程号 =b.课程号groupbya.学号 ;

4.SQL高级功能

-查询学生平均成绩及其名次

【知识点】窗口函数排名，思路如图

select学号 ,avg(成绩),row_number ()over(orderbyavg(成绩)desc)fromscoregroupby学号  ;

-按各科成绩进行排序，并显示排名

select课程号 ,row_number ()over(partitionby课程号orderby成绩 )fromscore ;

-查询每门功成绩最好的前两名学生姓名

【知识点】窗口函数排名+多表连接+条件

selecta.课程号 ,b.姓名 ,a.成绩,a.rankingfrom(select课程号 ,学号 ,成绩 ,row_number ()over(partitionby课程号orderby成绩desc)asrankingfromscore)asainnerjoinstudentasbona.学号 =b.学号wherea.ranking <3;

-查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（与上一题相似）

selectb.姓名 ,a.课程号 ,a.成绩from(select课程号 ,学号 ,成绩 ,row_number ()over(partitionby课程号orderby成绩desc)asrankingfromscore )asainnerjoinstudentasbona.学号 =b.学号wherea.rankingin(2,3) ;

-查询各科成绩前三名的记录（不考虑成绩并列情况）（与上一题相似）

selectb.姓名 ,a.课程号 ,a.成绩from(select课程号 ,学号 ,成绩 ,row_number ()over(partitionby课程号orderby成绩desc)as'ranking'fromscore )asainnerjoinstudentasbona.学号 =b.学号wherea.ranking <4;

这些题要融会贯通，以后碰到类似的面试题都可以找到对应场景的使用案例。

推荐：如何从零学会sql？