MySQL经典50题-第1-5题

作者: 皮皮大 | 来源:发表于2021-03-16 09:45 被阅读0次

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)不在已知的学号中,在这里就是\color{red}{王菊}同学

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

相关文章

  • MySQL经典50题-第1-5题

    MySQL经典50题-3-第1-5题目 本文中介绍的是1-5题,从题目和答案两个方面进行记录,涉及到的知识点: 一...

  • MySQL经典50题-第16到20题

    MySQL50-6-第16-20题 本文中介绍的是第16-20题,涉及到的知识点包含: 自连接 SQL实现排序 多...

  • MySQL经典50题-第6-10题

    MySQL50-4-第6-10题 本文中介绍的是第6-10题,涉及到的主要知识点: 模糊匹配和通配符使用 表的自连...

  • MySQL经典50题-第11-15题

    MySQL50-5-第11-15题 本文中介绍的是第11-15题,具体的题目包含: 查询没有学完全部课程的同学的信...

  • MySQL经典50题-第26到30题

    本文中介绍的是第26-30题目,主要涉及的知识点是: 分组之后count统计人数 模糊匹配 同一个表的自连接 ha...

  • MySQL经典50题-第21到25题

    MySQL50-7-第21-25题 本文中介绍的是第21-25题目,主要涉及的知识点是: 分组统计求和,百分比 如...

  • MySQL经典50题-第31到35题

    MySQL50-9-第31-35题 本文中介绍的是第31-35题目,主要涉及的知识点是: 模糊匹配 同时指定多种排...

  • MySQL经典50题-第36到40题

    MySQL50-10-第36-40题 本文中介绍的是第36-40题目,涉及到的知识点都是多表的连接查询,需要指定不...

  • MySQL经典50题-第41到45题

    MySQL50-11-第41-45题 本文中介绍的是第41-45题,主要包含的知识点: 表的自连接查询比较信息 找...

  • 完结篇!MySQL经典50题-第46到50题

    MySQL50-12-第46-50题 本文中介绍的是第46-50题,主要的知识点:各种时间和日期函数的使用 yea...

网友评论

    本文标题:MySQL经典50题-第1-5题

    本文链接:https://www.haomeiwen.com/subject/xamrcltx.html