MySQL50-5-第11-15题
本文中介绍的是第11-15题,具体的题目包含:
- 查询没有学完全部课程的同学的信息
- 查询至少有一门课与学号为01的同学所学相同的同学的信息
- 查询和01同学学习的课程完全相同的同学的信息
- 查询没有修过张三老师讲授的任何一门课程的学生姓名
- 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
题目11
题目需求
查询没有学完全部课程的同学的信息
分析过程
课程:Course
学生:Student
SQL实现
1 | -- 自己的方法 |
自己的方法一开始在课程的最大数中没有使用Course表,导致多使用了一个临时表的结果,现在改成使用Course表的统计值(3)作为课程的总数:
1 | select s.* |
1 | -- 方法2:having |
题目12
题目需求
查询至少有一门课与学号为01的同学所学相同的同学的信息
分析过程
题目的意思就是至少有一门课程和01同学的所学课程相同
课程:Score——>c_id
学生:Student——>s_id
SQL实现
首先看看结果的:因为01号同学修了全部课程,所以其他的同学都是满足要求,除了08号同学没有任何成绩,不符合。
具体实现过程为:
1 | select * -- 3、求出学生信息 |
1 | -- 方法2 |
题目13
题目需求
查询和01同学学习的课程完全相同的同学的信息
分析过程
本题我们可以$\color{red}{投机}$:因为总课程数3,而01号同学的课程数刚好是3,所以我们只要找出在Score表中课程也修满3门的同学即可
SQL实现
- 自己的方法
1 | select * |
我们在上面的步骤2中不考虑直接指定3(where number=3),而是用01学生所修的课程数(虽然也是3)来代替:
1 | select * |
- 使用
group_concat
函数
group_concat
的使用方法为:
1 | group_concat([DISTINCT] 字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符']) |
我们将Score
表中每个s_id
的c_id
进行分组合并,实际的效果如下:
1 | select |
需要进行排序的原因是防止出现这种情况:01修的课程顺序是:01,02,03;如果有同学修课的顺序是02,03,01,虽然顺序不同,但是本质上他们修的课程是相同的
使用排序后都会变成:01,02,03,保证结果相同
那么之后,我们只需要判断合并后和01号同学相同的结果即可,取出学号:
1 | select * -- 3、查询信息 |
题目14
题目需求
查询没有修过张三老师讲授的任何一门课程的学生姓名
分析过程
老师:Teacher——>t_name(t_id)
课程:Course——>t_id——>c_id
姓名:Student
SQL实现
自己的方法,具体过程如下:
1 | select s_name -- 4、学号取反找到学生姓名 |
1 | -- 修过张老师课程的学生的学号 |
题目15
题目需求
查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
分析过程
我们需要统计每个学生不及格的课程的数量:通过Score表中的数据直接统计c_id小于等于60的数量;然后再和Student表进行联结
SQL实现
首先看看哪些同学是满足两门或者两门以上是及格的
1 | select |
说明04,06是我们最终想要的结果
1 | -- 自己的方法 |
1 | -- 参考方法1 |
改进点
上面的两种方法都没有考虑都08学生,3门都没有成绩,这个本题需要改进的地方。