MySQL经典50题-3-第1-5题
本文中介绍的是1-5题,从题目和答案两个方面进行记录,涉及到的知识点:
- 一个表的自连接
- 分组后再聚合
- having是聚合之后再筛选
- ifnull(exp1,exp2):exp1不是空则取它本身,否则取exp2
具体的题目包含:
- 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
- 查询"01"课程比"02"课程成绩低的学生的信息及课程分数(题目1是成绩高)
- 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
- 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
- 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
题目1
题目要求
查询"01"课程比"02"课程成绩高的学生的信息及课程分数
分析过程
学生信息:Student
表的全部信息
课程分数:Score
表中的s_score
SQL实现
需要注意的是有些学生的有些课程是没有成绩的,所以只要一位同学的某科有成绩,而另外一位没有成绩,也算符合要求。
1 | -- 方法1 |
第二种方法实现:
题目2
题目要求
查询"01"课程比"02"课程成绩低的学生的信息及课程分数(题目1是成绩高)
分析过程
学生信息:Student
表的全部信息
课程分数:Score
表中的s_score
SQL实现
类比题目1的实现过程
1 | -- 方法1:通过连接方式实现 |
题目3
题目需求
查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
分析过程
成绩信息:Score表,s_score+s_id
学生信息:Student表,s_name+s_id
SQL实现
1 | -- 执行顺序:先执行分组,再执行avg平均操作 |
附加题:总分超过200分的同学
附加题
已知的题目是求解平均分大于60分(如果学生有两门成绩则算两门的,如果是三门则计算三门),我把题目改成了:学生必须是考3门的成绩,也就是3门才是齐全的
1 | -- 加入一个count()计算条件进行筛选 |
结果对比
看下两种情况的结果对比:
题目4
题目要求
查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
题目分析
有平均成绩小于60分:类似题目3的分析
没有成绩的同学:学生的成绩(s_score)、学号(s_id)不在已知的学号中,在这里就是$\color{red}{王菊}$同学
SQL实现1-两种情况连接
平均分小于60
1 | select |
结果为:
没有成绩的同学:
1 | select |
最后将两个部分的结果连起来即可:通过union
方法
SQL实现2-ifnull函数判断
使用ifnull函数
1 | select |
使用null判断
1 | select |
题目5
题目需求
查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
分析过程
学生编号、姓名:Student, s_id+s_name
课程:Score,c_id+s_score,一个统计个数,一个求和
SQL实现
1 | select |