case when 子查询_SQL学习(五)多表查询

一.表的加法(union)

  • UNION:把两张表合并起来

如下图:

2cfb2fdd801906395f28b1c070dfc810.png
select 课程号,课程名称 from course
union 
select 课程号,课程名称 from course1;

运行结果如下:

d68c194088e5d96df9746b4f1c77b73a.png
  • UNION ALL 合并表格并保留重复行数据
select 课程号,课程名称 from course
union all
select 课程号,课程名称 from course1;

3c9b113f9ffe4fd31f6e402ffaf0f2f1.png

二.表的联结

下面是数据库中的四张表格,存放着对应的数据

0106b7f61de650363e1ed8494001f6e6.png

18e41e291f348f21142fd9a4b20c7bb3.png
  • 交叉联结 cross join

又称为笛卡尔积,把两个表里的每行数据两两结合,如下图

5d876ad6b5477eeb1b781c3c50b3bc25.png
  • 内联结 inner join

查找同时存在于两张表里的数据,也就是两张表的交集

我们通过下表的演示,简单说明:

37e348c88f70591ffc456efceec5f046.png

案例:

select s1.学号,s1.姓名,s2.成绩 from student as s1 
inner join score as s2 on s1.学号=s2.学号;

28b029d40007ce54cb57115a5345f7be.png
  • 左联结 left join

以左边的表为标准联结右表取出指定数据,对于没有匹配上的用null值显示。

我们还是以上面的学生表和成绩表为例:

d2cac2ff4e319ad72081c2417d31feec.png
select s1.学号,s1.姓名,s2.成绩 from student s1
left join score s2 on s1.学号=s2.学号;

0fb04e6ea32446121cf212fc9459f99d.png
  • 右联结 right join

指以右边的表为标准联结左表取出指定数据,对于没有匹配的用NULL值显示。

ee3abd8b2b90abcda2aba9dd72eaae44.png
select s1.学号,s1.姓名,s2.成绩 from student s1
right join score s2 on s1.学号=s2.学号;

bd707d520e17d220685b2a93a2dd13ff.png
  • 全联结 full join

把两张表按照对应关系连接起来,返回2张表的所有行,没有匹配的值用NULL表示。

select s1.学号,s1.姓名,s2.成绩 from student s1
full join score s2 on s1.学号=s2.学号;

mysql不支持全联结,运行会报错,所以我们理解一下即可。

总结上述几个联结

66912929f8f0965b68141cd29dbdafb6.png

我们的各种联结都是在from子句中,所以不会影响sql的整体运行顺序,具体顺序如下图:

315c4c3fafd03230262e28c4b7a85a35.png

三.联结应用案例

我们依旧按照sql解决业务问题的思路来解决实际问题,如下几个案例:

1.查询所有学生的学号、姓名、选课数、总成绩

翻译问题:

每个学生的学号、姓名;

每个学生的课程总数,且按学号分组,对课程号计数count;

每个学生的总成绩,且按学号分组,对成绩求和sum;

分析思路:

select 查询结果:学号、姓名、选课数COUNT(*)、总成绩SUM(成绩);

from 从哪张表取出数据:student表和score表,用左联结;

where 指定条件:无

group by 分组:学号

having 对分组结果指定条件:无

order by 对查询结果排序:无

limit 从查询结果中选取指定行:无

运行结果:

d51fc5cc37ac2f56a07f7b3cdd21c51e.png

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

翻译问题:

每个学生的学号、姓名;

每个学生的平均成绩,且按学号分组,求平均成绩avg;

对分组得到的平均成绩进行判断:平均成绩大于85;

分析思路:

select 查询结果:学号、姓名、平均成绩avg(成绩);

from 从哪张表选取数据:student表左联结score表 ;

where 查询条件:无

group by 分组:对学号分组,得到每个学生的平均成绩;

having 对分组结果进行条件查询:对平均成绩进行判断,大于85的保留;

order by 对查询结果排序:无

limit 从查询结果中选取指定行:无

运行结果:

0d26ce0b5f45476aa2407676b18d9baf.png

3.查询学生的选课情况:学号、姓名、课程号、课程名称

翻译问题:

每个学生的学号、姓名,来源于student表;

每个学生的课程号、课程名称,来源于course表;

分析思路:

select 查询结果:student表-学号、student表-姓名、course表-课程号、course-课程名称

from 从哪张表查询数据:student表内联结score表内联结course表;

where 查询条件:无

group by 分组:无

having 对分组结果设置查询条件:无

order by 对查询结果进行排序:无

limit 从查询结果中选取指定行:无

运行结果:

10f63c32388feb9e3f729341d6039662.png

四.case表达式

case表达式是一个条件判断函数,判断每一行子句是否符合条件,符合就执行then子句,不符合就执行else子句,最后end结束,代码如下:

case when <判断表达式> then <表达式>
     when <判断表达式> then <表达式>
     when <判断表达式> then <表达式>
     ...
     else <表达式>
end

什么情况需使用case表达式:

1.多种情况需要条件判断时

2.自定义分组时

练习:

1.查询出每门课程的及格人数和不及格人数

代码及运行结果如下:

select 课程号,
sum(case when 成绩 >= 60 then 1 else 0 end) as 及格人数,
sum(case when 成绩 < 60 then 1 else 0 end) as 不及格人数 
from score
group by 课程号;

2e6fba99b3ac5ce49827e59ad9dd88d5.png

2.使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩,分别统计各分数段人数、课程id和课程名称

代码及运行结果如下:

select a.课程号,b.课程名称,
sum(case when a.成绩 >= 85 and a.成绩 < 100 then 1 else 0 end) as '[100-85]',
sum(case when a.成绩 >= 70 and a.成绩 < 85 then 1 else 0 end) as '[85-70]',
sum(case when a.成绩 >= 60 and a.成绩 < 70 then 1 else 0 end) as '[70-60]',
sum(case when a.成绩 <60 then 1 else 0 end) as '[<60]'
from score a right join course b on a.课程号=b.课程号
group by a.课程号,b.课程名称;

05aca92ccb59982583ea98473ace9092.png

注意事项:

1.else子句可以省略不写,为了良好的sql书写习惯,建议不要省略;

2.end不能省略,必须写出来;

3.case表达式可以写在sql语句的任意子句中。

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值