MySQL面试50题
1 1到5
-- 1 查询课程编号为‘01’的课程比‘02’的课程成绩高的所有学生的学号（重点） -- 整体的机构是先把三个表合并起来，再用where语句进行筛选，这里的as都可以省略， -- select -- a.s_id as "sno", -- a.s_score as "01", -- b.s_score as "02", -- c.s_name -- from -- (select s_id,c_id,s_score from score where c_id = '01') as a -- inner join -- (select s_id,c_id,s_score from score where c_id = '02')as b -- on a.s_id = b.s_id -- inner join -- student as c on c.s_id = a.s_id -- where a.s_score > b.s_score -- 2 查询平均成绩大于60分的学生学号和平均成绩 -- -- 注意select后面的列尽量只选在group by后面出现的字段或使用统计函数统计过的字段，不要添加其它的列，因为没有意义 -- select s_id, avg(s_score), c_id -- from score -- group by s_id having avg(s_score)>60 -- 3 查询所有同学的学号、姓名、选课总数、总成绩 -- 使用的表：student,score -- 由于有的学生可能没有成绩，所以这里用左连接， -- select后面尽量只出现groupby后面的字段和统计字段，所以把名字列也加到groupby后面 -- 为了避免求和列出现null值，所以判断为空值时赋值为0 -- select a.s_id,a.s_name, count(b.s_id), -- sum(case when b.s_score is null then 0 else b.s_score end) -- from student as a left join score as b on a.s_id = b.s_id -- group by s_id,a.s_name -- 4 查询姓猴老师的个数 -- 使用的表：teacher -- %代表任意字符，如果姓氏有重复可以用count(distinct t_name)来去重后再统计 -- select count(t_name) -- from teacher -- where t_name like '猴%' -- 5 查询没学过张三老师课的学生的学号，姓名（重点） -- 注意这里必须是用not in方法来解决，否则是错误的，即先把选过张三老师课程的学号找到，再取反 -- select s_id, s_name from student -- where s_id not in ( -- select s_id from score -- where c_id = ( -- select c_id from course -- where t_id = -- (select t_id from teacher where t_name = '张三') -- ) -- ) -- 错误写法，这样选出的是其它老师上的课 -- select * from score where c_id != '02' -- 先把三个表合并，再进行选择 -- select s_id, s_name from student -- where s_id not in -- ( -- select s.s_id from score as s -- inner join course as c on s.c_id = c.c_id -- inner join teacher as t on c.t_id = t.t_id -- where t.t_name = '张三' -- )
2 6到10
-- 6 查询学过’张三‘老师所教的所有课的同学的学号，姓名（重点） -- 自己的代码，可能有问题，先把选张三老师的课的学号选出来，再从student表中选对应的学号 -- select s_id,s_name from student as st -- where st.s_id in -- ( -- select s.s_id from score as s -- inner join course as c on s.c_id = c.c_id -- inner join teacher as t on t.t_id = c.t_id -- where t.t_name = '张三' -- ) -- 把四个表直接合并，再选择 -- select st.s_id, st.s_name -- from student as st -- inner join score as s on st.s_id = s.s_id -- inner join course as c on c.c_id = s.c_id -- inner join teacher as t on t.t_id = c.t_id -- where t.t_name = '张三' -- order by st.s_id -- -- 7 查询学过编号为‘01’的课程且学过编号为‘02’的课程的学生的学号，姓名（重点） -- 先利用子查询建立两个表，再用inner join表连接来筛选出都学过的学号，再对student表进行筛选 -- 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 -- inner join -- (select s_id from score where c_id = '02') as b -- on a.s_id = b.s_id -- ) -- NULL求长度后结果是null select LENGTH(null) -- 8 查询课程编号为‘02’的总成绩（不重点） -- select sum(s_score),avg(s_score),count(distinct s_id) from score -- where c_id = '02' -- -- 利用groupby可以对不同课程做统计 -- select c_id,sum(s_score),avg(s_score),count(distinct s_id) from score -- group by c_id -- -- 或者利用having -- select c_id,sum(s_score),avg(s_score),count(distinct s_id) from score -- group by c_id having c_id = '02' -- 9 查询所有课程成绩小于60分的学生的学号，姓名 -- 先计算每个同学小于60分的课程的个数，再计算每个同学所学的课程数，然后利用子查询把两个表按学号连接起来 -- 再对表进行筛选，最后和student表进行联结 -- 注意1>select后面是最后要展示的列 2>where要再join的后面不能先筛选了再进行join -- select a.s_id, st.s_name -- from -- ( -- select s_id, count(c_id) as cnt from score -- where s_score < 60 -- group by s_id -- ) as a -- inner join -- ( -- select s_id, count(c_id) as cnt from score -- group by s_id -- ) as b -- on a.s_id = b.s_id -- inner join student as st -- on a.s_id = st.s_id -- where a.cnt = b.cnt -- 10 查询没有学全所有课的学生的学号、姓名(重点) -- 利用group by进行聚合筛选 -- select只能跟列名或者*，不能直接选择表名，选表名会报错 -- 利用in方法的时候后面只能选一个列，选多个列的时候无法筛选，会报错 -- select s_id, s_name from student -- where s_id in -- ( -- select s_id from score -- group by s_id having count(c_id) < (select count(distinct c_id) from course) -- ) -- 上面方法有缺陷，因为可能有的学生没有成绩，所以在score表中不会出现，改进后的方法 -- 把student表和score表合并后再筛选 -- 注意这里之所以要用left join是因为student中的学生多，是一对多， -- select st.s_id, st.s_name from student as st -- left join score as sc -- on st.s_id = sc.s_id -- group by st.s_id having count(distinct sc.c_id) < (select count(distinct c_id) from course)
3 11到15
-- 11 查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名（重点） -- 用in效率较低，适合小数据 注意and在括号外用 -- select * from student -- where s_id in -- (select distinct s_id from score -- where c_id in -- (select c_id from score -- where s_id = '01') -- and s_id != '01') -- 当表较大的时候，用inner join ... on 比用in效率高 -- select a.s_id,a.s_name from student as a -- inner join -- ( -- (select distinct s_id from score -- where c_id in -- (select c_id from score -- where s_id = '01') -- and s_id != '01') -- ) as b on a.s_id = b.s_id -- 12 查询和“01”号同学所学课程完全相同的其他同学的学号(重点) -- 思路：01号同学选了(01 02 03)三门课程，先选出所有和01号同学学习课程数目相同的学生， -- 再把其中选了其它课程的同学删除，或者反过来理解也行， -- select * from student -- where s_id in -- ( -- select s_id from score -- where s_id != '01' -- group by s_id having count(distinct c_id) = (select count(distinct c_id) from score where s_id = '01') -- ) -- and s_id not in -- ( -- select distinct s_id from score -- where c_id not in -- (select c_id from score where s_id = '01') -- ) -- 15 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点） -- 先把满足条件的学号筛选处来，再对合并后的表格进行筛选和groupby -- select st.s_id, st.s_name, avg(s_score) from student as st -- inner join score as sc on st.s_id = sc.s_id -- where st.s_id in -- ( -- select s_id from score -- where s_score < 60 -- group by s_id having count(distinct c_id) >= 2 -- ) -- group by s_id, s_name
4 16到20
-- 16 检索"01"课程分数小于60，按分数降序排列的学生信息（和34题重复，不重点） -- 考察了order by ，where实际上是用于筛选条件的 -- select st.*,sc.c_id,sc.s_score from student as st -- inner join score as sc on st.s_id = sc.s_id -- where sc.c_id = '01' and sc.s_score < 60 -- order by sc.s_score desc -- 17 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重重点与35一样) -- 自己的代码，注意inner join后面必须要有on，否则会出错，代码有问题，没有课程列 -- select k.s_id, k.s_name, avg(s_score) from -- ( -- select st.*, sc.s_score from student as st -- inner join score as sc on st.s_id = sc.s_id -- ) as k -- group by s_id -- order by avg(s_score) desc -- 这个代码有问题，因为s_score列并没有意义，也不满足题目要求 -- select sc1.s_id, sc1.s_score, avg_s_score from score as sc1 -- inner join -- ( -- select s_id, avg(s_score) as avg_s_score from score -- group by s_id -- ) as sc2 on sc1.s_id = sc2.s_id -- order by avg_s_score desc -- 这个写法非常巧妙，这里之所以要用统计函数max是因为group by前最好只出现group by使用过的列或统计函数 -- 这里的目的并不是为了求max，而是因为group by用法的原因， -- select s_id '学号', -- max(case when c_id = '01' then s_score else null end) '语文', -- max(case when c_id = '02' then s_score else null end) '数学', -- max(case when c_id = '03' then s_score else null end) '英语', -- avg(s_score) '平均成绩' -- from score -- group by s_id -- order by avg(s_score) desc -- 18 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name， -- 最高分，最低分，平均分，及格率，中等率，优良率，优秀率 -- 这个题主要考察了group by后聚合函数case when的用法 -- select -- sc.c_id -- ,c.c_name -- ,max(s_score) -- ,min(s_score) -- ,avg(s_score) -- ,sum(case when sc.s_score>=60 then 1 else 0 end)/count(s_id) '及格率' -- ,sum(case when sc.s_score>=70 and sc.s_score<=80 then 1 else 0 end)/count(s_id) '中等率' -- ,sum(case when sc.s_score>=80 and sc.s_score<=90 then 1 else 0 end)/count(s_id) '优良率' -- ,sum(case when sc.s_score>=90 and sc.s_score<=100 then 1 else 0 end)/count(s_id) '优秀率' -- from score as sc -- inner join course as c on sc.c_id = c.c_id -- group by c_id -- 19 按各科成绩进行排序，并显示排名(重点row_number) row_number(）over （order by 列） -- rank() 跳跃排序，即如果第二名和第三名分数一样，则二三都是2，第四名为4 -- dense_rank() 连续排序，二三同上，第四名为3，即名次连续 -- row_number() 无重复值排序，直接为1 2 3 4，不分是否分数相等 -- select s_id, c_id, s_score, row_number() -- over(order by s_score desc) from score -- 20 查询学生的总成绩并进行排名（不重点） -- 有问题，为什么列名sum(s_score)在外面无法选择 select * from student as st inner join ( select s_id, sum(s_score) from score group by s_id order by sum(s_score) desc ) as sc on st.s_id = sc.s_id
5 21到30
-- 21 查询不同老师所教不同课程平均分从高到低显示(不重点) -- 自己的代码，和视频里不一样，把表合并为一个大表后，前面可以自由的选择列 -- select t.t_name, c.c_name, avg(s_score) from teacher as t -- left join course as c on c.t_id = t.t_id -- left join score as sc on sc.c_id = c.c_id -- group by t.t_name, c.c_name -- order by avg(s_score) desc -- 22 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（重要 25类似） -- 考察了窗口函数，先做一个子查询，再用where对m列进行筛选，这里的窗口函数其实可以用rank，因为有相同分数的情况 -- select * -- from -- (select st.s_id, st.s_name, c_id, s_score, -- row_number() over (partition by c_id order by s_score desc) m -- from score as sc -- inner join student as st -- on sc.s_id = st.s_id) as tab -- where m in (2,3) -- 23 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称(重点和18题类似) -- 这个题主要考察了统计函数sum 和 count的不同用法，注意count是对非空值进行统计 -- 这里的内连接和视频不同，导致结果不同 -- select c.c_id -- , c.c_name -- , sum(case when sc.s_score<=100 and sc.s_score>=85 then 1 else 0 end) '[100-85]' -- , count(case when sc.s_score<=85 and sc.s_score>=70 then 3 else null end) '[85-70]' -- , count(case when sc.s_score<=70 and sc.s_score>=60 then 3 else null end) '[70-60]' -- , count(case when sc.s_score<60 then 3 else null end) '[<60]' -- from course as c -- inner join score as sc -- on c.c_id = sc.c_id -- group by c.c_id, c.c_name -- 24 查询学生平均成绩及其名次（同19题，重点） -- 注意窗口函数不用partition by时，就是对整体的数据进行排序 -- select s_id -- , avg(s_score) -- , row_number() over (order by avg(s_score) desc) -- from score -- group by s_id -- 26 查询每门课程被选修的学生数(不重点) -- group by后面的列尽量都在select中 -- select c.c_id, c.c_name, count(distinct sc.s_id) '学生数' from score as sc -- inner join course as c -- on sc.c_id = c.c_id -- group by c.c_id, c.c_name -- 27 查询出只有两门课程的全部学生的学号和姓名(不重点) -- 主要考察如何作筛选 -- 利用子查询，先把s_id找出来，再筛选 -- select st.s_id, st.s_name from student as st -- where s_id in -- ( -- select s_id from score -- group by s_id having count(distinct c_id)=2 -- ) -- 先把两个表联结后再group by利用having进行筛选 -- select st.s_id, st.s_name from score as sc -- inner join student as st -- on sc.s_id = st.s_id -- group by s_id having count(distinct c_id)=2 -- 28 查询男生、女生人数(不重点) -- 这道题把group by去掉也行 -- 利用count实现 -- select s_sex -- , count(s_sex) '人数' -- from student -- group by s_sex -- 利用sum实现 -- select -- sum(case when s_sex='男' then 1 else 0 end) '男', -- sum(case when s_sex='女' then 1 else 0 end) '女' -- from student -- 29 查询名字中含有"风"字的学生信息（不重点） -- 考察like的用法，%风是以风开头，风%是以风结尾 -- select * from student where s_name like '%风%'
6 31到40
-- 31 查询1990年出生的学生名单（重点year） -- 考察时间函数的用法 -- select * from student where year(s_birth) = 1990 -- select month('190101') -- 时间格式如下的可以直接转换 -- YYYYMM-DD YYYYMMDD YYYY/MM//DD YYMMDD -- 32 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩（不重要） -- 注意group by后面用having可以起筛选作用，这道题也可以先对score表求平均后，再用子查询和表student做联结 -- select st.s_id, st.s_name, avg(s_score) as '平均成绩' from student as st -- inner join score as sc -- on st.s_id = sc.s_id -- group by st.s_id, st.s_name having avg(s_score)>85 -- 33 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列（不重要） -- 考察order by同时排序两列的用法 -- select sc.c_id, c.c_name, avg(s_score) from score as sc -- inner join course as c on sc.c_id = c.c_id -- group by c_id -- order by avg(s_score) asc, c_id desc -- 34 查询课程名称为"数学"，且分数低于60的学生姓名和分数（不重点） -- 考察了利用where进行条件筛选，写程序时先筛选完再inner join联结student表来添加名字列 -- select st.s_id, st.s_name, sc.s_score from score as sc -- inner join course as c on sc.c_id = c.c_id -- inner join student as st on sc.s_id = st.s_id -- where c.c_name = '数学' and sc.s_score<60 -- 35 查询所有学生的课程及分数情况（重点） -- 这个题主要考察了利用group by把一列按条件拆成多列的写法，这里case when的用法是groupby后的每个c.c_name -- 都会与语文进行比较，所以除了语文，其余都是空值，在空值和语文分数中取最大值，结果就是语文分数， -- select st.s_id, st.s_name, -- max( case when c.c_name='语文' then sc.s_score else null end) '语文', -- max( case when c.c_name='数学' then sc.s_score else null end) '数学', -- max( case when c.c_name='英语' then sc.s_score else null end) '英语' -- from score as sc -- inner join student as st on sc.s_id = st.s_id -- inner join course as c on sc.c_id = c.c_id -- group by st.s_id, st.s_name -- 36 查询课程成绩在70分以上的姓名、课程名称和分数（重点） -- 由于是score对student course表都是多对一，所以可以用inner join -- select st.s_name, c.c_name, sc.s_score from score as sc -- inner join student as st on sc.s_id = st.s_id -- inner join course as c on c.c_id = sc.c_id -- where sc.s_score > 70 -- 37查询不及格的课程并按课程号从大到小排列(不重点) -- select st.s_id, st.s_name, c.c_name, c.c_id, sc.s_score -- from score as sc -- inner join course as c on sc.c_id = c.c_id -- inner join student as st on sc.s_id = st.s_id -- where sc.s_score < 60 -- order by c.c_id desc -- 38 查询课程编号为03且课程成绩在80分以上的学生的学号和姓名（不重要） -- select st.s_id, st.s_name, sc.s_score, c.c_name from score as sc -- inner join student as st on st.s_id = sc.s_id -- inner join course as c on c.c_id = sc.c_id -- where sc.c_id='03' and sc.s_score>80 -- 39 求每门课程的学生人数（不重要） -- select c.c_id, c.c_name, count(distinct c.c_id) as '人数' from score as sc -- inner join course as c on sc.c_id = c.c_id -- group by c.c_id, c.c_name -- 40查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩（重要top） -- 考察了limit的用法，用于排序后取某些行，左闭右开， -- select st.s_id, st.s_name, sc.s_score from score as sc -- inner join course as c on c.c_id=sc.c_id -- inner join teacher as t on t.t_id=c.t_id -- inner join student as st on st.s_id=sc.s_id -- where t.t_name='张三' -- order by sc.s_score desc limit 0,1
7 41到50
-- 41 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 （重点） -- 多个子查询嵌套，先把只选一个课程的去掉，再两次group by进行筛选 -- select s_id from -- ( -- select b.s_id, b.s_score from score as b -- inner join -- ( -- select s_id from score -- group by s_id having count(distinct c_id)>1 -- ) as c on b.s_id=c.s_id -- group by b.s_id, b.s_score -- ) as a -- group by s_id having count(s_id)=1 -- 43 统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数， -- 查询结果按人数降序排列，若人数相同，按课程号升序排列（不重要） -- where后面不能跟聚合函数 -- select sc.c_id, count(distinct s_id) from score as sc -- group by sc.c_id having count(s_id)>5 -- order by count(s_id) desc, c_id asc -- 44、检索至少选修两门课程的学生学号（不重要） -- 使用count函数的时候要判断是否需要用distinct -- select s_id, count(distinct c_id) from score -- group by s_id having count(distinct c_id)>=2 -- 45 查询选修了全部课程的学生信息（重点划红线地方） -- 要学会这里计算course表时的子查询，直接用count(course.c_id)会报错，因为course表没有被选中 -- select s_id from score as sc -- group by s_id having count(distinct c_id)= -- (select count(distinct c_id) from course) -- 46 查询各学生的年龄（精确到月份） -- floor()函数用于向下取整， datediff(date1, data2) 第一个参数减第二个差值是天数，now()获取当前时间 -- select s_id, s_birth, floor(datediff(now(), s_birth)/365) as '年龄' from student -- 47 查询没学过“张三”老师讲授的任一门课程的学生姓名 -- 必须用取反的方法not in -- select * from student -- where s_id not in -- ( -- select sc.s_id from score as sc -- inner join course as c on sc.c_id=c.c_id -- inner join teacher as t on t.t_id=c.t_id -- where t.t_name='张三' -- ) -- 48 查询下周过生日的同学 -- 先把年份做字符串替换，替换成今年的，再做判断，年末不够严谨 -- select * from student -- where week(concat('2020-', substring(s_birth, 6, 5)), 1) = week('2019-05-15', 1)+1 -- 49 查询本月过生日的人 -- select * from student -- where month(s_birth)=month(now()) -- 50 查询下月过生日的人 -- 为防止出现13直接对12取整 -- select * from student -- where month(s_birth)=mod(month(now())+1, 12)
