Mysql经典面试题20道_mysql二十个经典面试题

我整理的必刷SQL经典题目

SQL语句在工作与面试时都必不可少，下面我整理了20道题目供大家练习，常见的使用方法和开窗函数都有考察，来测测你的sql技能是否过关。

一、创建表

共有4个表，分别是学生信息表、课程表、老师信息表和成绩表。

1 学生信息表

--建表语句
CREATE TABLE Student (
  SID VARCHAR (10),
  Sname VARCHAR (10),
  Sage datetime,
  Ssex VARCHAR (10)
)

--插入测试数据
INSERT INTO Student VALUES('01' , '赵雷' , '1990-01-01' , '男')
INSERT INTO Student VALUES('02' , '钱电' , '1990-12-21' , '男')
INSERT INTO Student VALUES('03' , '孙风' , '1990-05-20' , '男')
INSERT INTO Student VALUES('04' , '李云' , '1990-08-06' , '男')
INSERT INTO Student VALUES('05' , '周梅' , '1991-12-01' , '女')
INSERT INTO Student VALUES('06' , '吴兰' , '1992-03-01' , '女')
INSERT INTO Student VALUES('07' , '郑竹' , '1989-07-01' , '女')
INSERT INTO Student VALUES('08' , '王菊' , '1990-01-20' , '女')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

2 课程表

--建表语句
CREATE TABLE Course (
  CID VARCHAR (10),
  Cname VARCHAR (10),
  TID VARCHAR (10)
)

--插入测试数据
INSERT INTO Course VALUES('01' , '语文' , '02')
INSERT INTO Course VALUES('02' , '数学' , '01')
INSERT INTO Course VALUES('03' , '英语' , '03')
1
2
3
4
5
6
7
8
9
10
11

3 老师信息表

--建表语句
CREATE TABLE Teacher (
  TID VARCHAR (10),
  Tname varchar (10)
)

--插入测试数据
INSERT INTO Teacher VALUES('01' , '张三')
INSERT INTO Teacher VALUES('02' , '李四')
INSERT INTO Teacher VALUES('03' , '王五')
1
2
3
4
5
6
7
8
9
10

4 成绩表

--建表语句
CREATE TABLE SC (
  SID VARCHAR (10),
  CID VARCHAR (10),
  score int	(5)
)

--插入测试数据
INSERT INTO SC VALUES('01' , '01' , 80)
INSERT INTO SC VALUES('01' , '02' , 90)
INSERT INTO SC VALUES('01' , '03' , 99)
INSERT INTO SC VALUES('02' , '01' , 70)
INSERT INTO SC VALUES('02' , '02' , 60)
INSERT INTO SC VALUES('02' , '03' , 80)
INSERT INTO SC VALUES('03' , '01' , 80)
INSERT INTO SC VALUES('03' , '02' , 80)
INSERT INTO SC VALUES('03' , '03' , 80)
INSERT INTO SC VALUES('04' , '01' , 50)
INSERT INTO SC VALUES('04' , '02' , 30)
INSERT INTO SC VALUES('04' , '03' , 20)
INSERT INTO SC VALUES('05' , '01' , 76)
INSERT INTO SC VALUES('05' , '02' , 87)
INSERT INTO SC VALUES('06' , '01' , 31)
INSERT INTO SC VALUES('06' , '03' , 34)
INSERT INTO SC VALUES('07' , '02' , 89)
INSERT INTO SC VALUES('07' , '03' , 98)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

二、练习题

Q1：查询「李」姓老师的数量

SELECT
	count( * ) 李老师数据 
FROM
	teacher 
WHERE
	Tname LIKE '李%'
1
2
3
4
5
6

Q2：查询存在成绩的学生信息

--方法1：
SELECT DISTINCT
	s.* 
FROM
	sc g
	JOIN student s ON g.sid = s.sid 
WHERE
	g.score IS NOT NULL

--方法2：
SELECT
	* 
FROM
	student 
WHERE
	sid IN ( SELECT sid FROM sc WHERE sc.score IS NOT NULL )
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

Q3：查询不存在"01"课程但存在"02"课程的情况

SELECT
	* 
FROM
	sc 
WHERE
	cid = '02' 
	AND SID NOT IN ( SELECT SID FROM SC WHERE CID = '01' )
1
2
3
4
5
6
7

Q4：查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT
	A.*,
	B.score 
FROM
	Student A
	JOIN ( SELECT * FROM SC WHERE CID = '01' ) B ON A.SID = B.SID
	JOIN ( SELECT * FROM SC WHERE CID = '02' ) C ON C.SID = B.SID 
WHERE
	B.score > C.score
1
2
3
4
5
6
7
8
9

Q5：查询存在"01"课程但可能不存在"02"课程的情况(不存在时显示为null)

SELECT
	* 
FROM
	student s
	JOIN ( SELECT * FROM sc WHERE cid = '01' ) g1 ON s.SID = g1.sid
	LEFT JOIN ( SELECT * FROM sc WHERE cid = '02' ) g2 ON s.SID = g2.sid
1
2
3
4
5
6

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

SELECT
	s.*,
	T.Tname 
FROM
	student s
	JOIN SC G ON S.SID = G.SID
	JOIN course C ON G.CID = C.CID
	JOIN teacher T ON C.TID = T.TID 
WHERE
	TNAME = '张三'
1
2
3
4
5
6
7
8
9
10

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

SELECT
	s.Sname 
FROM
	student s 
WHERE
	sid NOT IN (
	SELECT
		g.sid 
	FROM
		sc g
		JOIN course c ON g.cid = c.cid
		JOIN teacher t ON t.tid = c.tid 
	WHERE
		t.tname = '张三' 
	)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

Q8：查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

SELECT
	c.Sname,
	c.score,
	d.Cname 
FROM
	(
	SELECT
		a.sid,
		a.Sname,
		b.score,
		b.cid 
	FROM
		student a
		JOIN ( SELECT sid, cid, score FROM sc WHERE score > 70 ) b ON a.sid = b.sid 
	) c
	JOIN course d ON d.cid = c.cid
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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

SELECT
	cid,
	max( SCORE ),
	min( score ),
	AVG( SCORE ) 
FROM
	sc 
GROUP BY
	CID
1
2
3
4
5
6
7
8
9

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

SELECT
	s.SID,
	s.Sname,
	sum( g.score ),
	COUNT( g.cid ) 
FROM
	student s
	LEFT JOIN sc g ON s.sid = g.sid 
GROUP BY
	s.SID
1
2
3
4
5
6
7
8
9
10

Q11：查询没有学全所有课程的同学的信息

SELECT
	s.*,
	count( g.cid ) 
FROM
	student s
	JOIN sc g ON s.sid = g.SID 
GROUP BY
	s.sid 
HAVING
	count( g.cid ) < ( SELECT count( * ) FROM course)
1
2
3
4
5
6
7
8
9
10

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

SELECT
	s.*,
	g.score 
FROM
	student s
	RIGHT JOIN ( SELECT sid, score FROM sc WHERE score < 60 AND cid = '01' ) g ON s.sid = g.SID 
ORDER BY
	g.score DESC
1
2
3
4
5
6
7
8

Q13：查询出只选修两门课程的学生学号和姓名

SELECT
	sid,
	sname 
FROM
	student 
WHERE
	sid IN ( SELECT sid FROM sc GROUP BY sid HAVING count( CID ) = 2 )
1
2
3
4
5
6
7

Q14：查询平均成绩大于等于85分的所有学生的学号、姓名和平均成绩

SELECT
	a.*,
	b.平均成绩 
FROM
	student a
	JOIN ( SELECT sid, avg( score ) 平均成绩 FROM sc GROUP BY sid HAVING avg( score ) >= 85 ) b ON a.sid = b.sid
1
2
3
4
5
6

Q15：查询课程名称为「数学」，且分数低于60的学生姓名和分数

SELECT
	* 
FROM
	student a
	JOIN (
	SELECT
		g.sid,
		c.Cname,
		g.score 
	FROM
		sc g
		JOIN course c ON g.cid = c.cid 
	WHERE
		c.Cname = '数学' 
		AND g.score < 60 
	) b ON a.SID = b.SId
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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

SELECT
	SID,
	avg( score ),
	MAX( CASE WHEN CID = '01' THEN score ELSE 0 END ) '01',
	MAX( CASE WHEN CID = '02' THEN score ELSE 0 END ) '02',
	MAX( CASE WHEN CID = '03' THEN score ELSE 0 END ) '03' 
FROM
	sc 
GROUP BY
	SID 
ORDER BY
	avg( score ) DESC
1
2
3
4
5
6
7
8
9
10
11
12

Q17：展示每位同学的各科成绩，并将各科成绩排名

SELECT
	*,
	RANK ( ) over ( PARTITION BY sid ORDER BY score DESC ) 排名 
FROM
	SC

1
2
3
4
5
6

Q18：查询学生的总成绩，并进行排名

SELECT
	sid,
	a.总成绩,
	rank ( ) over ( ORDER BY a.总成绩 DESC ) 总成绩排名 
FROM
	( SELECT SID, sum( score ) 总成绩 FROM sc GROUP BY sid ) a
1
2
3
4
5
6

Q19：查询各科成绩前三名的记录

SELECT
	* 
FROM
	( SELECT CID, score, RANK ( ) OVER ( PARTITION BY CID ORDER BY SCORE DESC ) 排名 FROM SC ) a 
WHERE
	a.排名 <4
1
2
3
4
5
6

Q20：查询每门功课成绩最好的前两名

SELECT
	a.* 
FROM
	( SELECT cid, score, rank ( ) over ( PARTITION BY cid ORDER BY score DESC ) 排名 FROM sc ) a 
WHERE
	a.排名 <= 2
1
2
3
4
5
6

我整理的必刷SQL经典题目