01.MYSQL必刷50题--1-5题

– 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT  student.*,a_score.s_score 01_score,b_score.s_score 02_score
FROM student JOIN score a_score ON student.s_id = a_score.s_id AND a_score.c_id='01' 
JOIN score b_score ON student.s_id = b_score.s_id AND b_score.c_id='02' 
WHERE a_score.s_score>b_score.s_score;

思路：

这个结果要用到score表和student表

这里要知道01和02的课程要用到两次score表

把表join好后，就可以根据条件进行查询了

– 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

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

SELECT student.s_id,s_name,ROUND(AVG(s_score),1) avg_score
FROM student JOIN score ON student.s_id=score.s_id
GROUP BY score.s_id HAVING avg_score>=60;

SELECT student.s_id,s_name,ROUND(AVG(s_score),1) avg_score
FROM student NATURAL JOIN score 
GROUP BY student.s_id HAVING avg_score>=60;

– 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

– (包括有成绩的和无成绩的)

SELECT student.s_id,student.s_name,ROUND(AVG(s_score),1) avg_score  
FROM student JOIN score ON student.s_id=score.s_id 
GROUP BY student.s_id HAVING avg_score <60 
UNION
SELECT s_id,s_name,0 avg_score 
FROM student 
WHERE s_id NOT IN(SELECT DISTINCT s_id FROM score);

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

思路：

有些同学因为没成绩，就没在score表上。我们必须考虑这部分同学

因此union这部分s_id不在score表上的数据

– 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT student.s_id,student.s_name,COUNT(score.c_id) count_score,SUM(score.s_score) sum_score
FROM student NATURAL JOIN score
GROUP BY student.s_id
UNION 
SELECT s_id,s_name,0,0
FROM student
WHERE s_id NOT IN(SELECT s_id FROM score);

推荐使用

SELECT student.s_id,student.s_name,COUNT(score.c_id) count_score,SUM(score.s_score) sum_score
FROM student LEFT JOIN score ON student.s_id=score.s_id
GROUP BY student.s_id;

思路：

直接student left join就能把没有上课的同学的课程数，成绩统计在内，不需要进行union。

和第4题的区别？为什么第4题不能直接left join？

不同于第4题，第4题设置了平均值的条件，没办法直接从score表中获取没有选课的同学。