常见Mysql41道面试题及答案(题目很经典,还有部分优化,希望能帮助到大家)

 

#4.3.1    查询student表的所有记录
SELECT * FROM student

#4.3.2    查询student表中的所有记录的sname、ssex和class列
SELECT sname,ssex,class FROM student

#4.3.3    查询编号为101的同学记录
SELECT * FROM student
WHERE sno=101

#4.3.4    查询姓“王”的同学记录
SELECT * FROM student
WHERE sname LIKE '王%'

#4.3.5    查询不姓“王”的同学记录
SELECT * FROM student
WHERE sname NOT LIKE '王%'

#4.3.6    查询scores表中成绩在60到80之间的所有记录
SELECT * FROM scores
WHERE degree BETWEEN 60 AND 80

SELECT * FROM scores
WHERE degree>=60 AND degree<=80

#4.3.7    查询scores表中成绩为85，86或88的记录
SELECT * FROM scores
WHERE degree=85 OR degree=86 OR degree=88

SELECT * FROM scores
WHERE degree IN (85,86,88)

#4.3.8    查询“95031”班的“男”同学的记录
SELECT * FROM student
WHERE class='95031' AND ssex='男'

#4.3.9    查询“95031”班或性别为“女”的同学记录
SELECT * FROM student
WHERE class='95031' OR ssex = '女'

#4.3.10    查询teacher表所有的系即不重复的depart列
SELECT DISTINCT depart FROM teacher

#4.3.11    以班级class降序查询student表的所有记录
SELECT * FROM student
ORDER BY class DESC

#4.3.12    以cno升序、degree降序查询scores表的所有记录
SELECT * FROM scores
ORDER BY cno, degree DESC

#4.3.13    查询“95031”班的学生人数
SELECT COUNT(*) FROM student
WHERE class='95031'

#4.3.14    查询每个班的学生人数
#分组后，选择字段只能写两种
#1.聚合函数，2.分组中出现字段

SELECT class,COUNT(*) FROM student
GROUP BY class

#4.3.15    查询大于2名男生的班号
SELECT class,COUNT(*) FROM student
WHERE ssex='男'
GROUP BY class
HAVING COUNT(*)>2 
#having条件，它和group by搭配

#4.3.16    查询最高分
SELECT MAX(degree) FROM scores   

#4.3.17    查询最低分
SELECT MIN(degree) FROM scores    

#4.3.18    查询scores表中的最高分的学生学号和课程号
SELECT cno,sno FROM scores
WHERE degree = (SELECT MAX(degree) FROM scores)

#4.3.19    查询‘3-105’号课程的平均分
SELECT cno,AVG(degree),ROUND(AVG(degree),1) FROM scores
GROUP BY cno
HAVING cno='3-105'

#4.3.20    查询各科的平均分
SELECT cno,ROUND(AVG(degree),2) FROM scores
GROUP BY cno

#4.3.21    查询最低分大于70，最高分小于90的sno列
select * from scores
where degree>70 and degree<90

#4.3.22    查询95033班和95031班全体学生的记录
select * from student
where class='95033' or class='95031'
#union 把两个结果集相加(优化)
select sno,sname as s1 from student where class='95033'
union
SELECT sno,sname as s2 FROM student WHERE class='95031'

#4.3.23    查询存在有85分以上成绩的课程cno
select cno from scores where degree>85

#4.3.24    查询所有教师teacher和同学student的name、sex和birthday
select tname name,tsex sex,tbirthday birthday from teacher
union
select sname,ssex,sbirthday from student

#4.3.25    查询所有“女”教师和“女”同学的name、sex和birthday
select tname,tsex,tbirthday from teacher where tsex='女'
union
select sname,ssex,sbirthday from student where ssex='女' 

#4.3.26    查询所有任课教师的tname和depart
课程表courses中有老师编号tno的就算任课

SELECT tname,depart FROM teacher
WHERE tno IN ( SELECT DISTINCT tno FROM courses )

#4.3.27    查询所有未讲课的教师的tname和depart
SELECT tname,depart FROM teacher
WHERE tno NOT IN ( SELECT DISTINCT tno FROM courses )

#4.3.28    查询同名的同学记录
SELECT sname FROM student
GROUP BY sname
HAVING COUNT(*)>1

SELECT * FROM student
WHERE sname = (
    SELECT sname FROM student
    GROUP BY sname
    HAVING COUNT(*)>1
)

#4.3.29    查询学生的姓名和年龄=当前年-birthday年   AS别名可省略
SELECT sname,sbirthday,YEAR(NOW())-YEAR(sbirthday) AS age 
FROM student

#4.3.30    查询学生中最大和最小的出生年月
SELECT MIN(sbirthday),MAX(sbirthday) FROM student

#4.3.31    以班号和年龄从大到小的顺序查询student表中的全部记录
SELECT * FROM student
ORDER BY class DESC, YEAR(NOW())-YEAR(sbirthday) DESC

#4.3.32    查询男教师及其所上的课程
SELECT t.tname,c.cname FROM
(SELECT * FROM teacher WHERE tsex='男') t
LEFT JOIN
(SELECT * FROM courses) c
ON t.tno=c.tno

SELECT t.tname,c.cname FROM
teachers t LEFT JOIN courses c 
ON t.tno=c.tno
WHERE t.tsex='男'

#4.3.33    查询各门课程的最高分同学的sno、cno和degree列
SELECT cno,MAX(degree) FROM scores
GROUP BY cno

#4.3.34    查询课程对应的老师姓名、职称、所属系
SELECT cname,tname,prof,depart FROM
(SELECT tno,cname FROM courses) a
LEFT JOIN
(SELECT tno,tname,prof,depart FROM teacher) b
ON a.tno=b.tno

#4.3.35    查询课程对应的女老师姓名、职称、所属系
SELECT cname,tname,prof,depart FROM
(SELECT tno,cname FROM courses) a
LEFT JOIN
(SELECT tno,tname,prof,depart,tsex FROM teacher) b
ON a.tno=b.tno
WHERE tsex='女'

行列转置，SQL没有真正实现，即使实现效率也不高，但是毕竟是SQL直接写成，不是应用代码

4.3.36    行列转置：各科最好成绩
分组方式：
SELECT cno,MAX(degree) degree FROM scores
GROUP BY cno;

SELECT 
MAX(CASE cno WHEN '3-105' THEN degree ELSE 0 END) '3-105',
MAX(CASE cno WHEN '3-245' THEN degree ELSE 0 END) '3-245',
MAX(CASE cno WHEN '6-106' THEN degree ELSE 0 END) '6-106',
MAX(CASE cno WHEN '6-166' THEN degree ELSE 0 END) '6-166'
FROM scores
 
行列转置：
SELECT 
MAX(CASE cno WHEN '3-105' THEN degree ELSE 0 END) '3-105',
MAX(CASE cno WHEN '3-245' THEN degree ELSE 0 END) '3-245',
MAX(CASE cno WHEN '6-106' THEN degree ELSE 0 END) '6-106',
MAX(CASE cno WHEN '6-166' THEN degree ELSE 0 END) '6-166'
 FROM scores;
 
#4.3.37    行列转置：每个学员各科最好成绩
每个学员（分组）；各科成绩（行列转置）；最好成绩（MAX函数）
SELECT 
sno,
MAX(CASE cno WHEN '3-105' THEN degree ELSE 0 END) '3-105',
MAX(CASE cno WHEN '3-245' THEN degree ELSE 0 END) '3-245',
MAX(CASE cno WHEN '6-106' THEN degree ELSE 0 END) '6-106',
MAX(CASE cno WHEN '6-166' THEN degree ELSE 0 END) '6-166'

 FROM scores
GROUP BY sno;
 
4.3.38    行列转置：课程编号和课程名称
SELECT cno,cname FROM courses;
#group_concat 
SELECT 
    REPLACE(GROUP_CONCAT(CASE cno WHEN '3-105' THEN cname ELSE '' END),',','') AS '3-105',
    REPLACE(GROUP_CONCAT(CASE cno WHEN '3-245' THEN cname ELSE '' END),',','') AS '3-245',
    REPLACE(GROUP_CONCAT(CASE cno WHEN '6-106' THEN cname ELSE '' END),',','') AS '6-106',
    REPLACE(GROUP_CONCAT(CASE cno WHEN '6-166' THEN cname ELSE '' END),',','') AS '6-166',
    REPLACE(GROUP_CONCAT(CASE cno WHEN '9-888' THEN cname ELSE '' END),',','') AS '9-888'
FROM courses;
 
SELECT DISTINCT
    REPLACE(GROUP_CONCAT(CASE cno WHEN '3-105' THEN cname ELSE '' END),',','') AS '3-105',
    REPLACE(GROUP_CONCAT(CASE cno WHEN '3-245' THEN cname ELSE '' END),',','')  AS '3-245',
    REPLACE(GROUP_CONCAT(CASE cno WHEN '6-106' THEN cname ELSE '' END),',','')  AS '6-106',
    REPLACE(GROUP_CONCAT(CASE cno WHEN '6-166' THEN cname ELSE '' END),',','')  AS '6-166',
    REPLACE(GROUP_CONCAT(CASE cno WHEN '9-888' THEN cname ELSE '' END),',','')  AS '9-888'
FROM courses;
 
#4.3.39    课程号“3-105”的前3名学员排行
SELECT * FROM scores
WHERE cno='3-105'
 ORDER BY degree DESC
LIMIT 3

4.3.40    课程“计算机导论”的前3名学员排行
SELECT * FROM scores
WHERE cno=(SELECT cno FROM courses WHERE cname='计算机导论')
 ORDER BY degree DESC
LIMIT 3

4.3.41    课程号“3-105”的倒数最后3名学员排行
SELECT * FROM
(SELECT * FROM scores WHERE cno='3-105' ORDER BY degree
LIMIT 3) t
ORDER BY degree desc