MySQL面试必会50题

– 1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）

select st.*,one.s_score,two.s_score from student st
INNER JOIN (select s_id,s_score from score where c_id='01') as one 
on st.s_id=one.s_id
INNER JOIN (select s_id,s_score from score where c_id='02') as two
on one.s_id=two.s_id
where one.s_score>two.s_score

– 2、查询平均成绩大于60分的学生的学号和平均成绩（简单，第二道重点）

select AVG(s_score),s_id from score
GROUP BY s_id
HAVING AVG(s_score)>60

– 查询平均成绩小于60分的学生的学号和平均成绩，需要加上null

SELECT s_id, AVG(s_score) from score
GROUP BY s_id
HAVING AVG(s_score)<60 or AVG(s_score) is NULL

– 3、查询所有学生的学号、姓名、选课数、总成绩（不重要）

select st.s_id,s_name,co.c_name,SUM(sc.s_score)
from student st
INNER JOIN score sc
on st.s_id=sc.s_id
INNER JOIN course co
on sc.c_id=co.c_id
GROUP BY st.s_id

– 4、查询姓“张”的老师的个数（不重要）

select COUNT(*) 
from teacher
where t_name like '张%'

– 5、查询没学过“张三”老师课的学生的学号、姓名（重点）
– 注意：使用in 而不是！

select * from student
where s_id not in 
 (select sc.s_id from score sc 
INNER JOIN course co
on sc.c_id=co.c_id
INNER JOIN teacher te
on co.t_id=te.t_id	
WHERE te.t_name='张三')

– 6、查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）
– 注意：使用in 而不是！

SELECT * from student 
WHERE s_id in
(select sc.s_id from score sc
INNER JOIN course co
on sc.c_id=co.c_id
INNER JOIN teacher te
on co.t_id=te.t_id
where te.t_name='张三')

– 7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）
– 先查出学过01的再查出学过02的，然后通过学生编号一个一个去比较

select * from student
where s_id in
(select one.s_id from (select s_id,c_id from score where c_id='01') as one
INNER JOIN (SELECT s_id,c_id from score where c_id='02') as two
on one.s_id=two.s_id)

– 8、查询课程编号为“02”的总成绩（不重点）

select SUM(s_score) 总成绩 
from score where c_id='02'

– 9、查询所有课程成绩小于60分的学生的学号、姓名

select st.s_id,s_name,sc.c_id 
from student st
INNER JOIN score sc
on st.s_id=sc.s_id
GROUP BY sc.s_id 
HAVING SUM(sc.s_score)<60

– 10.查询没有学全所有课的学生的学号、姓名(重点)
– 当课程数量小于总课程数量就是没学全

select st.s_id,