MySQL经典50题-第31-35题

MySQL50-9-第31-35题

本文中介绍的是第31-35题目，主要涉及的知识点是：

• 模糊匹配
• 同时指定多种排序方式
• 多表的连接查询

5个题目是：

• 查询1990年出生的学生信息
• 查询每门课程的平均成绩，结果按平均成绩降序排列；平均成绩相同时，按课程编号c_id升序排列
• 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
• 查询课程名称为数学，且分数低于60的学生姓名和分数
• 查询所有学生的课程及分数情况

题目31

题目需求

查询1990年出生的学生信息

分析过程

这个题目我们还是通过模糊匹配，使用的字段是s_birth

SQL实现

select *
from Student
where s_birth like '1990%';   -- 模糊匹配

题目32

题目需求

查询每门课程的平均成绩，结果按平均成绩降序排列；平均成绩相同时，按课程编号c_id升序排列

分析过程

课程：Score/Course

成绩：Score

通过每门课程的平均成绩分组和排序

SQL实现

-- 自己的方法
select 
	c_id
	,round(avg(s_score),2) avg_score
from Score
group by 1
order by 2 desc, c_id;  -- 指定字段和排序方法

如果想带上课程的名称，需要和Course表进行联结

-- 自己的方法
select 
	c.c_id
	,c.c_name
	,round(avg(sc.s_score),2) avg_score
from Score sc
join Course c
on sc.c_id = c.c_id
group by 1,2
order by 3 desc, c.c_id;  -- 指定字段和排序方法

题目33

题目需求

查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

分析过程

• 按照学生求解平均分，再选择出大于85分的人
• 和学生信息表进行联结，查出具体的信息

SQL实现

-- 自己的方法
select 
	sc.s_id
	,s.s_name
	,round(avg(sc.s_score),2) avg_score
from Score sc
join Student s
on sc.s_id = s.s_id
group by sc.s_id,s.s_name
having avg_score >= 85;

题目34

题目需求

查询课程名称为数学，且分数低于60的学生姓名和分数

分析过程

1、先从Score和Course表中找出数学小于60分的学生学号

2、 再和Student表连接查询出姓名

SQL实现

select 
	s.s_name
	,sc.s_score
from Score sc   -- 成绩表
join Student s  -- 学生信息表
on sc.s_id = s.s_id
join Course c  -- 课程表，指定数学
on sc.c_id = c.c_id
where c.c_name = '数学'
and sc.s_score < 60;  -- 指定成绩

看看真正的数据，的确只有一个人满足

题目35

题目需求

查询所有学生的课程及分数情况

分析过程

1. 课程：Course
2. 分数：Score
3. 学生姓名：Student

通过Score的主表，两个字段和其他两个表连接起来查询即可

SQL实现

select 
	s.s_id
	,s.s_name
	,sum(case c.c_name when '语文' then sc.s_score else 0 end) as '语文'  -- 语文分数
	,sum(case c.c_name when '数学' then sc.s_score else 0 end) as '数学'
	,sum(case c.c_name when '英语' then sc.s_score else 0 end) as '英语'
	,sum(sc.s_score) as '总分'  -- 每个人的总分
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
group by s.s_id, s.s_name;   -- 学号和姓名的分组