15道常见的MySQL面试题及解析

2201_75869073

8507人浏览 · 2023-07-08 16:51:36

2201_75869073 · 2023-07-08 16:51:36 发布

标题

引言：
题目：十五道综合的MySQL面试题
结尾：

引言：

在数据库管理系统中，MySQL是最受欢迎的关系型数据库之一。在 MySQL 的面试中，了解常见的问题和解决方法是非常重要的。本文将介绍15道常见的MySQL面试题，并提供详细的解析和答案。

正文：

题目：十五道综合的MySQL面试题

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

SELECT
	s.*,
	s1.score a,
	s2.score b 
FROM
	( SELECT * FROM t_mysql_score WHERE cid = "01" ) s1,
	( SELECT * FROM t_mysql_score WHERE cid = "02" ) s2,
	t_mysql_student s 
WHERE
	s1.sid = s2.sid 
	AND s1.sid = s.sid 
	AND s1.score > s2.score

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

	s.*,
	( CASE WHEN s1.cid = "01" THEN s1.score END ) a,
	( CASE WHEN s2.cid = "02" THEN s1.score END ) b 
FROM
	( SELECT * FROM t_mysql_score WHERE cid = "01" ) s1,
	( SELECT * FROM t_mysql_score WHERE cid = "02" ) s2,
	t_mysql_student s 
WHERE
	s1.sid = s2.sid 
	AND s1.sid = s.sid

03）查询存在 " 01 “课程但可能不存在” 02 "课程的情况 ( 不存在时显示为 NULL ) SELECT

SELECT
	s1.*,
	s2.score 
FROM
	( SELECT * FROM t_mysql_score sc WHERE sc.cid = "01" ) s1
	LEFT JOIN ( SELECT * FROM t_mysql_score sc WHERE sc.cid = "02" ) s2 ON s1.sid = s2.sid

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

SELECT
	* 
FROM
	t_mysql_score s 
WHERE
	s.sid NOT IN ( SELECT sid FROM t_mysql_score WHERE cid = "01" ) 
	AND s.cid = "02"

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

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

06）查询在t_mysql_score表存在成绩的学生信息

SELECT
	s.* 
FROM
	t_mysql_score sc,
	t_mysql_student s 
WHERE
	sc.sid = s.sid 
GROUP BY
	s.sid

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

SELECT
	s.sid,
	s.sname,
	count( sc.cid ),
	sum( sc.score ) 
FROM
	t_mysql_student s,
	t_mysql_score sc 
WHERE
	s.sid = sc.sid 
GROUP BY
	s.sid,
	s.sname

08）查询「李」姓老师的数量

SELECT
	* 
FROM
	t_mysql_teacher t 
WHERE
	tname LIKE '李%'

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

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

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

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

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

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

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

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

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

SELECT
	s.*,
	sc.score 
FROM
	t_mysql_student s,
	t_mysql_score sc 
WHERE
	s.sid = sc.sid 
	AND sc.cid = "01" 
	AND sc.score < 60 
ORDER BY
	sc.score DESC

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

SELECT
	s.sid,
	s.sname,
	ROUND(AVG(sc.score),2),
	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_student s,
	t_mysql_score sc 
WHERE
	s.sid = sc.sid 
GROUP BY
	s.sname,
	s.sid 
ORDER BY
	AVG( sc.score ) DESC

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

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

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