题目参考https://zhuanlan.zhihu.com/p/38354000https://www.cnblogs.com/lhx0827/p/9567199.html一、建表学生表-student-- 创建学生表createtablestudent(student_numbervarchar(255)notnullcomment学号,student_namevarchar(255)notnullcomment学生姓名,student_birthdatenotnullcomment出生日期,student_sexvarchar(255)notnullcomment性别,PRIMARYKEY(student_number))engineInnoDBdefaultcharsetutf8comment学生表;-- 初始化学生表数据insertintostudent(student_number,student_name,student_birth,student_sex)values(S0001,猴子,1989-01-01,男),(S0002,猴子,1990-12-21,女),(S0003,马云,1991-12-21,男),(S0004,王思聪,1990-05-20,男);成绩表-achievement-- 创建成绩表createtableachievement(student_numbervarchar(255)notnullcomment学号,course_numbervarchar(255)notnullcomment课程号,scorefloat(3)notnullcomment成绩,primarykey(student_number,course_number))engineInnoDBdefaultcharsetutf8comment成绩表;-- 初始化成绩表数据insertintoachievement(student_number,course_number,score)values(S0001,C0001,80),(S0001,C0002,90),(S0001,C0003,99),(S0002,C0002,60),(S0002,C0003,80),(S0003,C0001,80),(S0003,C0002,80),(S0003,C0003,80);课程表-coursecreatetablecourse(course_numbervarchar(255)notnullcomment课程号,course_namevarchar(255)notnullcomment课程名称,teacher_numbervarchar(255)notnullcomment教师号,primarykey(course_number))engineInnoDBdefaultcharsetutf8comment课程表;-- 初始化课程表数据insertintocourse(course_number,course_name,teacher_number)values(C0001,语文,T0002),(C0002,数学,T0001),(C0003,英语,T0003);教师表-teachercreatetableteacher(teacher_numbervarchar(20)notnullcomment教师号,teacher_namevarchar(20)comment教师姓名,primarykey(teacher_number))engineInnoDBcharsetutf8comment教师表;-- 初始化教师表数据insertintoteacher(teacher_number,teacher_name)values(T0001,孟扎扎),(T0002,马化腾),(T0003,null),(T0004,);二、练习第一题查询姓猴的学生名单select*fromstudentwherestudent_namelike猴%;第二题查询姓名中最后一个字是子的学生名单select*fromstudentwherestudent_namelike%子;第三题查询姓名中带猴的学生名单select*fromstudentwherestudent_namelike%猴%;第四题查询姓“孟”老师的个数selectcount(teacher_number)fromteacherwhereteacher_namelike孟%;第五题查询课程编号为C0002的总成绩selectsum(score)fromachievementwherecourse_numberC0002;第六题查询选了课程的学生人数注意count(*)统计时注意要去重selectcount(distinctstudent_number)fromachievement;第七题查询各科成绩最高分和最低分selectcourse_number,max(score),min(score)fromachievementgroupbycourse_number;第八题查询每门课程被选修的学生数注意为什么不用count(distinct student_number) 因为course_number student_number 是唯一主键不存在同一个学生一门课两个成绩selectcourse_number,count(student_number)fromachievementgroupbycourse_number;第九题查询男生女生人数selectstudent_sex,count(student_number)fromstudentgroupbystudent_sex;第十题查询平均成绩大于80分学生的学号和平均成绩注意对分组结果再指定条件用 havingselectstudent_number,avg(score)fromachievementgroupbystudent_numberhavingavg(score)80;第十一题查询至少选修了两门课程的学生学号selectstudent_number,count(course_number)fromachievementgroupbystudent_numberhavingcount(course_number)2;第十二题查询同名同姓学生名单并统计同名人数selectstudent_name,count(student_number)fromstudentgroupbystudent_namehavingcount(student_number)2;第十三题查询分数大于60分的课程并按课程号从大到小排列selectcourse_number,scorefromachievementwherescore60groupbycourse_number,scoreorderbycourse_numberdesc;第十四题查询每门课程的平均成绩结果按平均成绩升序排序平均成绩相同时按课程号降序排列selectcourse_number,avg(score)fromachievementgroupbycourse_numberorderbyavg(score)asc,course_numberdesc;第十五题检索课程编号为“C0002”且分数大于等于80的学生学号结果按分数降序排列selectcourse_number,student_number,scorefromachievementwherecourse_numberC0002andscore80orderbyscoredesc;第十六题统计每门课程的学生选修人数(超过1人的课程才统计)要求输出课程号和选修人数查询结果按人数降序排序若人数相同按课程号升序排序selectcourse_number,count(student_number)fromachievementgroupbycourse_numberhavingcount(student_number)1orderbycount(student_number)desc,course_numberasc;第十七题查询两门以上及格课程的同学的学号及其平均成绩selectstudent_number,count(course_number),avg(score)fromachievementwherescore60groupbystudent_numberhavingcount(course_number)2;第十八题查询所有课程成绩大于等于60分学生的学号、姓名方法一为了查询姓名所以连接学生表做关联查询selectdistinctachievement.student_number,student.student_namefromachievementjoinstudentonstudent.student_numberachievement.student_numberwhereachievement.score60;方法二子查询selectstudent_number,student_namefromstudentwherestudent_numberin(selectstudent_numberfromachievementwherescore60);第十九题查询没有学全所有课的学生的学号、姓名selectstudent_number,student_namefromstudentwherestudent_numberin(selectstudent_numberfromachievementgroupbystudent_numberhavingcount(course_number)(selectcount(course_number)fromcourse));第二十题查询出只选修了两门课程的全部学生的学号和姓名selectstudent_number,student_namefromstudentwherestudent_numberin(selectstudent_numberfromachievementgroupbystudent_numberhavingcount(course_number)2);第二十一题查找1990年出生的学生名单select*fromstudentwhereyear(student_birth)1990;第二十二题查询各科成绩前两名的记录limit 2 弊端成绩相同时前两名实际人数会 2;核心思路“前两名等价于比我分数高的人少于2个”第1名比我高的人数 0第2名比我高的人数 1第3名比我高的人数 2 ❌ 被过滤所有数据外层每执行一次内层都会代入参数同样也执行一次SELECTa.course_number,a.student_number,a.scorefromachievement awhere(SELECTcount(*)fromachievement bwherea.course_numberb.course_numberanda.scoreb.score)2orderbya.course_numberdesc第二十三题查询所有学生的学号、姓名、选课数、总成绩注意查询所有学生所以采用外连接的时候要注意student要放置在左边否则没有选课的学生信息就无法显示出来了。-- 解法一先查出成绩表并分组再与学生表进行关联查询selectS.student_number,S.student_name,T.totalCourse,T.totalScorefromstudent Sleftjoin(selectstudent_number,count(course_number)astotalCourse,sum(score)astotalScorefromachievementgroupbystudent_number)TonT.student_numberS.student_number;-- 解法二由于学号是主键并且按照学号分组可以直接关联学生表selects.student_number,s.student_name,count(a.course_number),sum(a.score)fromstudent sleftjoinachievement aons.student_numbera.student_numbergroupbys.student_number;第二十四题查询平均成绩大于85的所有学生的学号、姓名和平均成绩此题与上一题解法类似添加了分组后再过滤关键字 having-- 解法一先分组查询出结果集再连接student表查询出学生名-- 注意右联是以右表为主要表右表存在的字段都会在结果中显示出来。selectS.student_number,S.student_name,T.avgScorefromstudent Srightjoin(selectstudent_number,avg(score)asavgScorefromachievementgroupbystudent_numberhavingavg(score)85)TONS.student_numberT.student_number;-- 解法二直接左联再用having过滤小于85和NULL的成绩selects.student_number,s.student_name,avg(a.score)fromstudent sleftjoinachievement aons.student_numbera.student_numbergroupbys.student_numberhavingavg(score)85;第二十五题查询学生的选课情况学号姓名课程号课程名称selects.student_number,s.student_name,c.course_number,c.course_namefromstudent sjoinachievement aons.student_numbera.student_numberjoincourse cona.course_numberc.course_number;第二十六题查询出每门课程的及格人数和不及格人数selectcourse_number,sum(casewhenscore60then1else0end)as及格人数,sum(casewhenscore60then1else0end)as不及格人数fromachievementgroupbycourse_number;第二十七使用分段[100-90],[89-70],[69-60],[60]来统计各科成绩分别统计各分数段人数课程号和课程名称selecta.course_number,c.course_name,sum(casewhena.score100anda.score90then1else0end)as100-90,sum(casewhena.score89anda.score80then1else0end)as89-80,sum(casewhena.score79anda.score70then1else0end)as79-70,sum(casewhena.score69anda.score60then1else0end)as69-60,sum(casewhena.score60then1else0end)as60fromachievement ajoincourse cona.course_numberc.course_numbergroupbya.course_number;第二十八题查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名selects.student_number,s.student_name,a.scorefromstudent sjoinachievement aons.student_numbera.student_numberwherea.course_numberC0003anda.score80;第二十九题下面是学生的成绩表achievement编写SQL使之一下面这种方式显示出来解此题解法参考https://mp.weixin.qq.com/s/6Kll4Q6Xp37i2PiLUh4cMA第一步selectstudent_number学号,课程号C0001,课程号C0002,课程号C0003fromachievement;第二步selectstudent_number学号,(casewhencourse_numberC0001thenscoreelse0end)as课程号C0001,(casewhencourse_numberC0002thenscoreelse0end)as课程号C0002,(casewhencourse_numberC0003thenscoreelse0end)as课程号C0003fromachievement;第三步selectstudent_number学号,max(casewhencourse_numberC0001thenscoreelse0end)as课程号C0001,max(casewhencourse_numberC0002thenscoreelse0end)as课程号C0002,max(casewhencourse_numberC0003thenscoreelse0end)as课程号C0003fromachievementgroupbystudent_number;第三十题未完待续。。。。
常见的SQL面试题: 经典50题
发布时间：2026/5/20 10:12:12
题目参考https://zhuanlan.zhihu.com/p/38354000https://www.cnblogs.com/lhx0827/p/9567199.html一、建表学生表-student-- 创建学生表createtablestudent(student_numbervarchar(255)notnullcomment学号,student_namevarchar(255)notnullcomment学生姓名,student_birthdatenotnullcomment出生日期,student_sexvarchar(255)notnullcomment性别,PRIMARYKEY(student_number))engineInnoDBdefaultcharsetutf8comment学生表;-- 初始化学生表数据insertintostudent(student_number,student_name,student_birth,student_sex)values(S0001,猴子,1989-01-01,男),(S0002,猴子,1990-12-21,女),(S0003,马云,1991-12-21,男),(S0004,王思聪,1990-05-20,男);成绩表-achievement-- 创建成绩表createtableachievement(student_numbervarchar(255)notnullcomment学号,course_numbervarchar(255)notnullcomment课程号,scorefloat(3)notnullcomment成绩,primarykey(student_number,course_number))engineInnoDBdefaultcharsetutf8comment成绩表;-- 初始化成绩表数据insertintoachievement(student_number,course_number,score)values(S0001,C0001,80),(S0001,C0002,90),(S0001,C0003,99),(S0002,C0002,60),(S0002,C0003,80),(S0003,C0001,80),(S0003,C0002,80),(S0003,C0003,80);课程表-coursecreatetablecourse(course_numbervarchar(255)notnullcomment课程号,course_namevarchar(255)notnullcomment课程名称,teacher_numbervarchar(255)notnullcomment教师号,primarykey(course_number))engineInnoDBdefaultcharsetutf8comment课程表;-- 初始化课程表数据insertintocourse(course_number,course_name,teacher_number)values(C0001,语文,T0002),(C0002,数学,T0001),(C0003,英语,T0003);教师表-teachercreatetableteacher(teacher_numbervarchar(20)notnullcomment教师号,teacher_namevarchar(20)comment教师姓名,primarykey(teacher_number))engineInnoDBcharsetutf8comment教师表;-- 初始化教师表数据insertintoteacher(teacher_number,teacher_name)values(T0001,孟扎扎),(T0002,马化腾),(T0003,null),(T0004,);二、练习第一题查询姓猴的学生名单select*fromstudentwherestudent_namelike猴%;第二题查询姓名中最后一个字是子的学生名单select*fromstudentwherestudent_namelike%子;第三题查询姓名中带猴的学生名单select*fromstudentwherestudent_namelike%猴%;第四题查询姓“孟”老师的个数selectcount(teacher_number)fromteacherwhereteacher_namelike孟%;第五题查询课程编号为C0002的总成绩selectsum(score)fromachievementwherecourse_numberC0002;第六题查询选了课程的学生人数注意count(*)统计时注意要去重selectcount(distinctstudent_number)fromachievement;第七题查询各科成绩最高分和最低分selectcourse_number,max(score),min(score)fromachievementgroupbycourse_number;第八题查询每门课程被选修的学生数注意为什么不用count(distinct student_number) 因为course_number student_number 是唯一主键不存在同一个学生一门课两个成绩selectcourse_number,count(student_number)fromachievementgroupbycourse_number;第九题查询男生女生人数selectstudent_sex,count(student_number)fromstudentgroupbystudent_sex;第十题查询平均成绩大于80分学生的学号和平均成绩注意对分组结果再指定条件用 havingselectstudent_number,avg(score)fromachievementgroupbystudent_numberhavingavg(score)80;第十一题查询至少选修了两门课程的学生学号selectstudent_number,count(course_number)fromachievementgroupbystudent_numberhavingcount(course_number)2;第十二题查询同名同姓学生名单并统计同名人数selectstudent_name,count(student_number)fromstudentgroupbystudent_namehavingcount(student_number)2;第十三题查询分数大于60分的课程并按课程号从大到小排列selectcourse_number,scorefromachievementwherescore60groupbycourse_number,scoreorderbycourse_numberdesc;第十四题查询每门课程的平均成绩结果按平均成绩升序排序平均成绩相同时按课程号降序排列selectcourse_number,avg(score)fromachievementgroupbycourse_numberorderbyavg(score)asc,course_numberdesc;第十五题检索课程编号为“C0002”且分数大于等于80的学生学号结果按分数降序排列selectcourse_number,student_number,scorefromachievementwherecourse_numberC0002andscore80orderbyscoredesc;第十六题统计每门课程的学生选修人数(超过1人的课程才统计)要求输出课程号和选修人数查询结果按人数降序排序若人数相同按课程号升序排序selectcourse_number,count(student_number)fromachievementgroupbycourse_numberhavingcount(student_number)1orderbycount(student_number)desc,course_numberasc;第十七题查询两门以上及格课程的同学的学号及其平均成绩selectstudent_number,count(course_number),avg(score)fromachievementwherescore60groupbystudent_numberhavingcount(course_number)2;第十八题查询所有课程成绩大于等于60分学生的学号、姓名方法一为了查询姓名所以连接学生表做关联查询selectdistinctachievement.student_number,student.student_namefromachievementjoinstudentonstudent.student_numberachievement.student_numberwhereachievement.score60;方法二子查询selectstudent_number,student_namefromstudentwherestudent_numberin(selectstudent_numberfromachievementwherescore60);第十九题查询没有学全所有课的学生的学号、姓名selectstudent_number,student_namefromstudentwherestudent_numberin(selectstudent_numberfromachievementgroupbystudent_numberhavingcount(course_number)(selectcount(course_number)fromcourse));第二十题查询出只选修了两门课程的全部学生的学号和姓名selectstudent_number,student_namefromstudentwherestudent_numberin(selectstudent_numberfromachievementgroupbystudent_numberhavingcount(course_number)2);第二十一题查找1990年出生的学生名单select*fromstudentwhereyear(student_birth)1990;第二十二题查询各科成绩前两名的记录limit 2 弊端成绩相同时前两名实际人数会 2;核心思路“前两名等价于比我分数高的人少于2个”第1名比我高的人数 0第2名比我高的人数 1第3名比我高的人数 2 ❌ 被过滤所有数据外层每执行一次内层都会代入参数同样也执行一次SELECTa.course_number,a.student_number,a.scorefromachievement awhere(SELECTcount(*)fromachievement bwherea.course_numberb.course_numberanda.scoreb.score)2orderbya.course_numberdesc第二十三题查询所有学生的学号、姓名、选课数、总成绩注意查询所有学生所以采用外连接的时候要注意student要放置在左边否则没有选课的学生信息就无法显示出来了。-- 解法一先查出成绩表并分组再与学生表进行关联查询selectS.student_number,S.student_name,T.totalCourse,T.totalScorefromstudent Sleftjoin(selectstudent_number,count(course_number)astotalCourse,sum(score)astotalScorefromachievementgroupbystudent_number)TonT.student_numberS.student_number;-- 解法二由于学号是主键并且按照学号分组可以直接关联学生表selects.student_number,s.student_name,count(a.course_number),sum(a.score)fromstudent sleftjoinachievement aons.student_numbera.student_numbergroupbys.student_number;第二十四题查询平均成绩大于85的所有学生的学号、姓名和平均成绩此题与上一题解法类似添加了分组后再过滤关键字 having-- 解法一先分组查询出结果集再连接student表查询出学生名-- 注意右联是以右表为主要表右表存在的字段都会在结果中显示出来。selectS.student_number,S.student_name,T.avgScorefromstudent Srightjoin(selectstudent_number,avg(score)asavgScorefromachievementgroupbystudent_numberhavingavg(score)85)TONS.student_numberT.student_number;-- 解法二直接左联再用having过滤小于85和NULL的成绩selects.student_number,s.student_name,avg(a.score)fromstudent sleftjoinachievement aons.student_numbera.student_numbergroupbys.student_numberhavingavg(score)85;第二十五题查询学生的选课情况学号姓名课程号课程名称selects.student_number,s.student_name,c.course_number,c.course_namefromstudent sjoinachievement aons.student_numbera.student_numberjoincourse cona.course_numberc.course_number;第二十六题查询出每门课程的及格人数和不及格人数selectcourse_number,sum(casewhenscore60then1else0end)as及格人数,sum(casewhenscore60then1else0end)as不及格人数fromachievementgroupbycourse_number;第二十七使用分段[100-90],[89-70],[69-60],[60]来统计各科成绩分别统计各分数段人数课程号和课程名称selecta.course_number,c.course_name,sum(casewhena.score100anda.score90then1else0end)as100-90,sum(casewhena.score89anda.score80then1else0end)as89-80,sum(casewhena.score79anda.score70then1else0end)as79-70,sum(casewhena.score69anda.score60then1else0end)as69-60,sum(casewhena.score60then1else0end)as60fromachievement ajoincourse cona.course_numberc.course_numbergroupbya.course_number;第二十八题查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名selects.student_number,s.student_name,a.scorefromstudent sjoinachievement aons.student_numbera.student_numberwherea.course_numberC0003anda.score80;第二十九题下面是学生的成绩表achievement编写SQL使之一下面这种方式显示出来解此题解法参考https://mp.weixin.qq.com/s/6Kll4Q6Xp37i2PiLUh4cMA第一步selectstudent_number学号,课程号C0001,课程号C0002,课程号C0003fromachievement;第二步selectstudent_number学号,(casewhencourse_numberC0001thenscoreelse0end)as课程号C0001,(casewhencourse_numberC0002thenscoreelse0end)as课程号C0002,(casewhencourse_numberC0003thenscoreelse0end)as课程号C0003fromachievement;第三步selectstudent_number学号,max(casewhencourse_numberC0001thenscoreelse0end)as课程号C0001,max(casewhencourse_numberC0002thenscoreelse0end)as课程号C0002,max(casewhencourse_numberC0003thenscoreelse0end)as课程号C0003fromachievementgroupbystudent_number;第三十题未完待续。。。。
