MySQL 综合分析题,MySQL经典50题-第1-5题

MySQL经典50题-3-第1-5题目

本文中介绍的是1-5题，从题目和答案两个方面进行记录，涉及到的知识点：

一个表的自连接

分组后再聚合

having是聚合之后再筛选

ifnull(exp1,exp2)：exp1不是空则取它本身，否则取exp2

image

题目1

题目要求

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

分析过程

学生信息：Student表的全部信息

课程分数：Score表中的s_score

sql实现

需要注意的是有些学生的有些课程是没有成绩的，所以只要一位同学的某科有成绩，而另外一位没有成绩，也算符合要求

-- 方法1

select

a.*

,b.s_score as 1_score

,c.s_score as 2_score

from Student a

join Score b on a.s_id = b.s_id and b.c_id = '01' -- 两个表通过学号连接，指定01

left join Score c on a.s_id = c.s_id and c.c_id='02' or c.c_id is NULL -- 指定02，或者c中的c_id直接不存在

-- 为NULL的条件可以不存在，因为左连接中会直接排除c表中不存在的数据，包含NULL

where b.s_score > c.s_score; -- 判断条件

-- 方法2：直接使用where语句

select

a.*

,b.s_score as 1_score

,c.s_score as 2_score

from Student a, Score b, Score c

where a.s_id=b.s_id -- 列出全部的条件

and a.s_id=c.s_id

and b.c_id='01'

and c.c_id='02'

and b.s_score > c.s_score; -- 前者成绩高

image

题目2

题目要求

查询"01"课程比"02"课程成绩低的学生的信息及课程分数(题目1是成绩高)

分析过程

学生信息：Student表的全部信息

课程分数：Score表中的s_score

sql实现

类比题目1的实现过程

-- 方法1：通过连接方式实现

select

a.*

,b.s_score as 1_score

,c.s_score as 2_score

from Student a

left join Score b on a.s_id=b.s_id and b.c_id='01' or b.c_id=NULL -- 包含NULL的数据

join score c on a.s_id=c.s_id and c.c_id='02'

where b.s_score < c.s_score;

-- 通过where子句实现

select

a.*

,b.s_score as 1_score

,c.s_score as 2_score

from Student a, Score b, Score c

where a.s_id=b.s_id

and a.s_id=c.s_id

and b.c_id='01'

and c.c_id='02'

and b.s_score < c.s_score; -- 前者比较小

题目3

image

题目需求

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

分析过程

成绩信息：Score表，s_score+s_id

学生信息：Student表，s_name+s_id

SQL实现

-- 执行顺序：先执行分组，再执行avg平均操作

select

b.s_id

,b.s_name

,round(avg(a.s_score), 2) as avg_score

from Student b

join Score a

on b.s_id = a.s_id

group by b.s_id -- 分组之后查询每个人的平均成绩

having avg_score >= 60;

-- 附加题：总分超过200分的同学

select

b.s_id

,b.s_name

,round(sum(a.s_score),2) as sum_score -- sum求和

from Student b

join Score a

on b.s_id=a.s_id

group by b.s_id

having sum_score > 200;

image

附加题：总分超过200分的同学

image

附加题

已知的题目是求解平均分大于60分(如果学生有两门成绩则算两门的，如果是三门则计算三门)，我把题目改成了：学生必须是考3门的成绩，也就是3门才是齐全的

-- 加入一个count()计算条件进行筛选

select

b.s_id

,b.s_name

,round(avg(a.s_score), 2) as avg_score

from Student b

join Score a

on b.s_id = a.s_id

group by b.s_id

having avg_score >= 60 and count(b.s_name)=3; -- 计数条件

image

结果对比

看下两种情况的结果对比：

image

题目4

题目要求

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

题目分析

有平均成绩小于60分：类似题目3的分析

没有成绩的同学：学生的成绩(s_score)、学号(s_id)不在已知的学号中，在这里就是

同学

SQL实现1-两种情况连接

平均分小于60

select

b.s_id

,b.s_name

,round(avg(a.s_score), 2) as avg_score -- round四舍五入函数

from Student b

join Score a

on b.s_id = a.s_id

group by b.s_id -- 分组之后查询每个人的平均成绩

having avg_score < 60;

结果为：

image

没有成绩的同学：

select

a.s_id

,a.s_name

,0 as avg_score

from Student a

where a.s_id not in ( -- 学生的学号不在给给定表的学号中

select distinct s_id -- 查询出全部的学号

from Score

);

image

image

最后将两个部分的结果连起来即可：通过union方法

image

SQL实现2-ifnull函数判断

使用ifnull函数

select

S.s_id

,S.s_name

,round(avg(ifnull(C.s_score,0)), 2) as avg_score -- ifnull 函数：第一个参数存在则取它本身，不存在取第二个值0

from Student S

left join Score C

on S.s_id = C.s_id

group by s_id

having avg_score < 60;

使用null判断

select

a.s_id

,a.s_name

,ROUND(AVG(b.s_score), 2) as avg_score

from Student a

left join Score b on a.s_id = b.s_id

GROUP BY a.s_id

HAVING avg_score < 60 or avg_score is null; -- 最后的NULL判断

题目5

题目需求

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

分析过程

学生编号、姓名：Student, s_id+s_name

课程：Score，c_id+s_score，一个统计个数，一个求和

SQL实现

select

a.s_id

,a.s_name

,count(b.c_id) as course_number -- 课程个数

,sum(b.s_score) as scores_sum -- 成绩总和

from Student a

left join Score b

on a.s_id = b.s_id

group by a.s_id,a.s_name;

image