MySql精选15道面试题（错过即遗憾，快来看看）

前言

在上期Mysql博客中我们学习了一些关于Mysql的基础知识，今天给大家带来Mysql精选的15面试题目，希望对大家有帮助和大家能够认真对待。需要的资料放在下方链接了。

链接：https://pan.baidu.com/s/1R2JOXHN4-VdVY-aEA5GO9A

提取码：yx04

1.查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

分析题目

所需表

t_mysql_score、t_mysql_student

解题思路

1. 先分别获取01课程（t1）和02的课程（t2）的成绩表的信息
2. 要求t1.sid=t2.sid（即是同一名学生）
3. 再联系t_mysql_student学生信息表
4. 01课程（t1)的成绩要高于02的课程（t2）的成绩，即t1.score > t2.score

解题代码

SELECT
  t3.*,
  ( CASE WHEN t1.cid = '01' THEN t1.score END ) 语文,
  ( CASE WHEN t2.cid = '02' THEN t2.score END ) 数学 
FROM
  ( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1,
  ( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2,
  t_mysql_student t3 
WHERE
  t1.score > t2.score 
  AND t1.sid = t2.sid 
  AND t1.sid = t3.sid

展示结果

注意

2.查询同时存在" 01 “课程和” 02 "课程的情况

分析题目：

所需表：

t_mysql_score、t_mysql_student

解题思路：

1. 先分别获取01课程（t1）和02的课程（t2）的成绩表的信息
2. 要求t1.sid=t2.sid（即是同一名学生）
3. 再联系t_mysql_student学生信息表

解题代码：

SELECT
  t3.*,
  ( CASE WHEN t1.cid = '01' THEN t1.score END ) 语文,
  ( CASE WHEN t2.cid = '02' THEN t2.score END ) 数学 
FROM
  ( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1,
  ( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2,
  t_mysql_student t3 
WHERE
  t1.sid = t2.sid 
  AND t1.sid = t3.sid

展示结果：

3.查询存在" 01 “课程但可能不存在” 02 (不存在时显示为 null )

分析题目

所需表

t_mysql_score

解题思路

1. 考虑使用连表查询（这里使用左外连接，以存在01课程结果的为主）

即SELECT * FROM t_mysql_score WHERE cid = ‘01’（t1）

2.要求是学生选择了01课程，即t1.sid=t2.sid (t2:SELECT * FROM t_mysql_score WHERE cid = ‘02’)

3.查询字段：t1.*，t2.cid 数学 ， t2.score 02成绩

解题代码

SELECT
  t1.*,
  t2.cid 数学 ,
  t2.score 02成绩
FROM
  ( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1
  LEFT JOIN ( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2 ON t1.sid = t2.sid

展示结果

4.查询不存在" 01 “课程但存在” 02 "课程的情况

分析题目

所需表

t_mysql_score

解题思路

1. 先获取到01课程的学生的sid
2. 再查询02课程的学生信息并且不包含01课程的sid

解题代码

SELECT
  * 
FROM
  t_mysql_score 
WHERE
  sid NOT IN ( SELECT sid FROM t_mysql_score WHERE cid = '01' ) 
  AND cid = '02'

展示结果

5.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

分析题目

所需表

t_mysql_score （sc）、t_mysql_student (s)

解题思路

1. 先联系两张表，即sc.sid=s.sid
2. 再计算各个学生的平均成绩，即AVG(sc.score)
3. 在筛选出大于等于 60 分的同学，即HAVING AVG(sc.score)>=60
4. 查询字段：s.sid,s.sname,AVG( sc.score )

解题代码

SELECT
  s.sid,
  s.sname,
  AVG( sc.score ) 
FROM
  t_mysql_score sc,
  t_mysql_student s 
WHERE
  sc.sid = s.sid 
GROUP BY
  s.sid,
  s.sname 
HAVING
  AVG( sc.score ) >= 60

展示结果

6.查询在t_mysql_score表存在成绩的学生信息

分析题目

所需表

t_mysql_student、t_mysql_score

解题思路

1. 先获取t_mysql_score中的sid
2. 查询t_mysql_student出来的sid要在上面sid之中,即t_mysql_student.sid in t_mysql_score.sid

解题代码

SELECT
  * 
FROM
  t_mysql_student 
WHERE
  sid IN ( SELECT sid FROM t_mysql_score GROUP BY sid )

展示结果

7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

分析问题

所需表

t_mysql_score、t_mysql_student

解题思路

1. 先将t_mysql_score和t_mysql_student通过左外连接连接起来，t_mysql_score为主表，即（( SELECT * FROM t_mysql_student ) t1 LEFT JOIN ( SELECT * FROM t_mysql_score ) t2 ON t1.sid = t2.sid ）
2. 在将连表查询后的作为一个新表来查询，计算所选课程总成绩（sum(t2.score)）和选课总数（count(sc.cid)），再根据sid、sname分组（GROUP BY t1.sid,t1.sname）
3. 查询字段：s.sid,s.sname,count(sc.cid),SUM(sc.score)

解题代码

SELECT
  t1.sid,
  t1.sname,
  COUNT( t2.cid ),
  sum( t2.score ) 
FROM
  ( SELECT * FROM t_mysql_student ) t1
  LEFT JOIN ( SELECT * FROM t_mysql_score ) t2 ON t1.sid = t2.sid 
GROUP BY
  t1.sid,
  t1.sname

展示结果

8.查询「李」姓老师的数量

分析题目

所需表

t_mysql_teacher

解题思路

1. 查询t_mysql_teacher表，筛选name姓李的老师,即（WHERE tname like ‘李%’）
2. 计算数量COUNT( * )

解题代码

SELECT
  COUNT( * ) 
FROM
  t_mysql_teacher 
WHERE
  tname LIKE '李%'

展示结果

9.查询学过「张三」老师授课的同学的信息

分析题目

所需表

t_mysql_course（c）、t_mysql_teacher（t）、t_mysql_student（s）、t_mysql_score（sc）

解题思路

1. 先根据「张三」老师的编号获取到教的相应课程,即（t.tid=c.tid）
2. 再通过课程的编号获取对应课程的成绩信息，即（c.cid=sc.cid）
3. 最后再通过对应课程的成绩的学生编号获取对应学生的信息，即（sc.sid=s.sid）
4. 查询字段：s.*

解题代码

SELECT
  s.* 
FROM
  t_mysql_teacher t,
  t_mysql_course c,
  t_mysql_student s,
  t_mysql_score sc 
WHERE
  t.tname = '张三' 
  AND t.tid = c.tid 
  AND c.cid = sc.cid 
  AND s.sid = sc.sid

展示结果

10. 查询没有学全所有课程的同学的信息

分析题目

所需表

t_mysql_score（sc）、t_mysql_student（s）、t_mysql_course

解题思路

1. 先将两张表格联系起来，即sc.sid=s.sid,
2. 根据学生姓名和学生编号分组（s.sid、s.sname），再筛选出选课总数少于课程总数的学生,即（HAVING COUNT( sc.score ) < ( SELECT count( 1 ) FROM t_mysql_course )）
3. 查询字段：s.sid,s.sname,COUNT( sc.score )

解题代码

SELECT
  s.sid,
  s.sname,
  COUNT( sc.score ) 
FROM
  t_mysql_score sc,
  t_mysql_student s 
WHERE
  sc.sid = s.sid 
GROUP BY
  s.sid,
  s.sname 
HAVING
  COUNT( sc.score ) < ( SELECT count( 1 ) FROM t_mysql_course )

展示结果

11.查询没学过"张三"老师讲授的任一门课程的学生姓名

分析题目

所需表

t_mysql_student（s）、t_mysql_score（sc）、t_mysql_teacher（t）、t_mysql_course（c）

解题思路

1. 先根据张三老师的编号获取到所教课程的编号，再根据所教课程的课程编号在成绩表中获取到所教学生编号，即（SELECT sc.sid FROM t_mysql_score sc, t_mysql_teacher t, t_mysql_course c WHERE t.tid = c.tid AND c.cid = sc.cid AND t.tname = ‘张三’ GROUP BY sc.sid）
2. 在学生信息表中筛选出不是张三老师的学生
3. 查询字段：s.sname

解题代码

SELECT
  s.sname
FROM
  t_mysql_student s
WHERE
  sid NOT IN (
SELECT
  sc.sid 
FROM
  t_mysql_score sc,
  t_mysql_teacher t,
  t_mysql_course c 
WHERE
  t.tid = c.tid 
  AND c.cid = sc.cid 
  AND t.tname = '张三' 
GROUP BY
  sc.sid 
  )

展示结果

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

分析题目

所需表

t_mysql_score（sc）、t_mysql_student（s）

解题思路

1. 先筛选出各学生不及格的记录数
2. 再分组得到存在科目不及格的学生对应科目数
3. 最后得出不及格课程数大于等于二的学生信息即平均成绩
4. 查询字段：s.sid,s.sname,count(sc.score) n,ROUND(AVG(sc.score),2)

解题代码

SELECT 
s.sid,s.sname,count(sc.score) n,ROUND(AVG(sc.score),2)
FROM
t_mysql_score sc,
t_mysql_student s
WHERE 
sc.sid=s.sid
and sc.score<60
GROUP BY
s.sid,s.sname
HAVING
n>=2

展示结果

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

分析题目

所需表

t_mysql_score（sc）、t_mysql_student（s）

解题思路

1. 先查询出01课程成绩小于60分的学生信息
2. 再通过desc进行降序排序
3. 查询字段:s.*,sc.score

解题代码

SELECT 
s.*,sc.score
FROM
t_mysql_score sc,
t_mysql_student s
WHERE 
sc.sid=s.sid
and sc.score<60
and sc.cid='01'
ORDER BY
sc.score desc

展示结果

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

分析题目

所需表

t_mysql_score（sc）、t_mysql_student（s）

解题思路

1. 先计算出所有学生的平均成绩和查询出所有学生所有课程的成绩
2. 在通过平均成绩从高到低排序
3. 查询字段：s.sid,s.sname,ROUND(AVG(sc.score),2) avgNum,max( CASE WHEN sc.cid = ‘01’ THEN sc.score END ) 语文，max( CASE WHEN sc.cid = ‘02’ THEN sc.score END ) 数学，max( CASE WHEN sc.cid = ‘03’ THEN sc.score END ) 英语

解题代码

SELECT
s.sid,s.sname,ROUND(AVG(sc.score),2) avgNum,
max( CASE WHEN sc.cid = '01' THEN sc.score END ) 语文,
max( CASE WHEN sc.cid = '02' THEN sc.score END ) 数学,
max( CASE WHEN sc.cid = '03' THEN sc.score END ) 英语
FROM
t_mysql_score sc,
t_mysql_student s
WHERE
s.sid=sc.sid
GROUP BY
s.sid,s.sname
ORDER BY avgNum desc

展示结果

15. 查询各科成绩最高分、最低分和平均分：

– 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

分析题目

所需表

t_mysql_score（sc）、t_mysql_student（s）、t_mysql_course（c）

解题思路

1. 分组：课程ID,课程name
2. 算及格人数，中等人数，优良人数，优秀人数
3. 算每门课程参与考试的总人数
4. 做计算

解题代码

SELECT
c.cid,c.cname,MAX(sc.score) 最高分,min(sc.score) 最低分,ROUND(avg(sc.score),2) 平均分,
ROUND(sum(if(sc.score >=60,1,0))/count(sc.score)*100,2) 及格率,
ROUND(sum(if(sc.score >=70 and sc.score<80,1,0))/count(sc.score)*100,2) 中等率,
ROUND(sum(if(sc.score >=80 and sc.score<90,1,0))/count(sc.score)*100,2) 优良率,
ROUND(sum(if(sc.score >=90 and sc.score<=100,1,0))/count(sc.score)*100,2) 优秀率
from
t_mysql_score sc,
t_mysql_course c,
t_mysql_student s
WHERE
sc.cid=c.cid and sc.sid=s.sid
GROUP BY c.cid,c.cname

展示结果

SELECT
c.cid,c.cname,MAX(sc.score) 最高分,min(sc.score) 最低分,ROUND(avg(sc.score),2) 平均分,
ROUND(sum(if(sc.score >=60,1,0))/count(sc.score)*100,2) 及格率,
ROUND(sum(if(sc.score >=70 and sc.score<80,1,0))/count(sc.score)*100,2) 中等率,
ROUND(sum(if(sc.score >=80 and sc.score<90,1,0))/count(sc.score)*100,2) 优良率,
ROUND(sum(if(sc.score >=90 and sc.score<=100,1,0))/count(sc.score)*100,2) 优秀率
from
t_mysql_score sc,
t_mysql_course c,
t_mysql_student s
WHERE
sc.cid=c.cid and sc.sid=s.sid
GROUP BY c.cid,c.cname

展示结果

结束语

这期博客分享就到这，希望能给大家带来帮助，三连必关注。