MYSQL 50题其二

6. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩-- (包括有成绩的和无成绩的)

平均成绩 这个要用到一个函数AVG，这个是求平均值的函数，当然前提是要分组，所以根据学生ID分组之后求平均值即可，当然那些全部缺考的同学也要统计出来：

SELECT s.*, ROUND(AVG(sc.s_score),2) AS '平均成绩'
FROM student s
LEFT JOIN score sc
ON s.s_id = sc.s_id
GROUP BY s.s_id HAVING AVG(sc.s_score) < 60 OR AVG(sc.s_score) IS NULL

你会发现最后一个啥都没考试的人是null，总归有些不雅，我们可以将最后的null给他置为0. 所以我们可以学一个新的语法，叫做case when,这个是一个控制流语法：

CASE
WHEN Boolean_expression  THEN
    result_expression [...n ] 
WHEN xxx   THEN
    resut
ELSE
    else_result_expression
  END 
或者

CASE 列
WHEN when_expression THEN
    result_expression [...n ] [
ELSE
    else_result_expression
END

所以我们可以改成：

SELECT s.*, 
(CASE
 WHEN ROUND(AVG(sc.s_score),2) IS NULL   THEN
	0
ELSE 
  ROUND(AVG(sc.s_score),2)
END) AS '平均成绩'
FROM student s
LEFT JOIN score sc
ON s.s_id = sc.s_id
GROUP BY s.s_id HAVING AVG(sc.s_score) < 60 OR AVG(sc.s_score) IS NULL

结果：

7. 查询学过"张三"老师授课的同学的信息
   这个其实就是多张表的关联，没啥难度。学生表和成绩表有共同的学生id, 成绩表关联课程表，课程表和老师表有联系，所以多张表关联即可：

SELECT s.*
FROM student s
LEFT JOIN  score sc 
ON s.s_id = sc.s_id
LEFT JOIN course c
ON sc.c_id = c.c_id
LEFT JOIN teacher t
ON t.t_id = c.t_id
WHERE t.t_name = "张三"

8. 查询没学过"张三"老师授课的同学的信息
7题查询的是所有张三老师教过的学生，那只要不在这个里面的 就是没有教过的学生咯，所以可以用not in

SELECT st.* FROM student st
WHERE st.s_id NOT IN
(
SELECT s.s_id
FROM student s
LEFT JOIN  score sc 
ON s.s_id = sc.s_id
LEFT JOIN course c
ON sc.c_id = c.c_id
LEFT JOIN teacher t
ON t.t_id = c.t_id
WHERE t.t_name = "张三")

这种多查询了一次student，就是没必要两次查询student，只要根据score，课程表，和 老师表，找到张三老师教过的学生的id，就能找到不在这个id中的学生了

SELECT st.* 
FROM student st
WHERE st.s_id NOT IN
(SELECT sc.s_id
FROM score sc
LEFT JOIN course c
ON c.c_id = sc.c_id
LEFT JOIN teacher t
ON t.t_id = c.t_id
WHERE t.t_name = '张三')

结果：

9. 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
   其实就是两次join，和第一题类似，第一次学生表和成绩表join，根据条件就能选出学习了01课程的所有学生，再来一次和成绩表join，选出学了02课程的学生即可：

SELECT s.*
FROM student s, score c, score c2
WHERE s.s_id = c.s_id AND s.s_id = c2.s_id AND c.c_id='01' AND c2.c_id = '02'
或者：
SELECT s.*
FROM student s
 JOIN score sc
ON s.s_id = sc.s_id AND sc.c_id='01'
 JOIN score sc2
ON s.s_id = sc2.s_id AND sc2.c_id = '02'

两种写法其实是一样的，不能用left join，因为left join 是以学生表为基础的话，不管有没有课程都会全部选出来的，right join 肯定也不能用，score表本身就有很多重复的学生id，用了肯定不对

10 . 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

我们可以先求出所有学过01课程的学生，再求出02课程的学生的id ,只有学过01学生的id不在学过02的id里面即可：

SELECT s.*
FROM student s, score c
WHERE s.s_id = c.s_id  AND c.c_id='01' AND s.s_id 
NOT IN
(
SELECT s_id FROM score WHERE c_id = '02'
)

结果：

欢迎关注我的微信公众号： 北风中独行的蜗牛

参考：https://blog.csdn.net/fashion2014/article/details/78826299
https://www.cnblogs.com/coder-wf/p/11128033.html