一、表的加法
关键词union和union all 用来合并表,其中,union 去重,union all 不去重。
例如:将course和course1两张表合并
两张表的数据分别如图所示:course表的数据course1表的数据
用union合并两张表:course+course1(去重)
用union all合并两张表course+course1(不去重)
注意:字段顺序应保持一致。
二,多表联结
联结就是以各表的公共列(关系列)为基础,把所有表合并为一张表。
联结方式具体分为以下五种:交叉联结(cross join)
内联结(inner join)
左联结(left join)
右联结(right join)
全联结(full join)
1、交叉联结(cross join)
交叉联结(又称笛卡尔积)是将表1的每一行与表2的每一行都进行合并。
联结后的行数是表1行数与表2行数的乘积。交叉联结
交叉联结日常工作用得较少,因为联结后行数较多,查询效率低。
交叉联结是其他联结方式的基础。
2、内联结(inner join)
内联结是只保留两张表的公共列都有的数据,然后以交叉联结的方式进行合并。内联结内联结的运行逻辑
第一步,先取出两张表的公共列都有的数据,案例中的公共列【学号】只有‘0001’是两表都有的。
第二步,将两表取出的数据进行交叉联结,得到最终效果。
内联结的SQL语句:
多表联结的SQL语句有两个关键点:因为涉及多张表,为了便于辨认和阅读,建议给各表转换别名
用关键字on指定各表的公共列
因为这里是内联结,所以需要用关键词 inner join 连接各表,即:from 表1inner join 表2(如果是其他联结方式,则替换成对应的关键词)。
-- 内联结selelct ,...
from 表1 inner join 表2
on =
3、左联结(left join)
左联结是仅保留公共列左表的数据,然后以交叉联结的方式进行合并。左联结左联结的运行逻辑
第一步,先取出左表的数据,右表则取两张表的公共列都有的数据,案例中的公共列【学号】只有‘0001’是两表都有的。
第二步,将两表取出的数据进行交叉联结,得到最终效果。因为右表不存在学号‘0002’的数据,所有结果中‘0002’的其他数据是空值。
-- 左联结selelct ,...
from 表1 left join 表2
on =
4、右联结(right join)
右联结是仅保留公共列右表的数据,然后以交叉联结的方式进行合并。右联结右联结的运行逻辑
第一步,先取出右表都有的数据,左表则取两张表的公共列都有的数据,案例中的公共列【学号】只有‘0001’是两表都有的。
第二步,将两表取出的数据进行交叉联结,得到最终效果。因为左表不存在学号‘0005’的数据,所有结果中除了‘0005’对应的课程号‘0003’,其他数据是空值。
-- 右联结selelct ,...
from 表1 right join 表2
on =
5、全联结(full join)
全联结是保留两张表的数据,然后以交叉联结的方式进行合并。全联结全联结的运行逻辑
第一步,先将两边的数据都各自取出。
第二步,将两表取出的数据进行合并,得到最终效果。
注意:MySQL不支持全联结,仅作为了解概念。但可通过union合并左联结和右联结,达到全联结的效果。
-- 全联结selelct ,...
from 表1 full join 表2
on =
SQL联结方式的总结:
如何选择使用哪种联结方式?
要生成固定行数的表单,或者保留某张表的全部数据时,选择左联结或者右联结;其他情况,用内联结获取各表的公共部分
三、案例分析
例1:查询所有学生的学号、姓名、选课数、总成绩
第一步:翻译成大白话
学号和姓名可从student表查询,但选课数和总成绩只能从score表查询,所以这个问题涉及两张表,需要使用联结。
第二步:写出分析思路
关键点1:如何联结?
学号是两张表的公共列(关系列),所以选择学号。
关键点2:用哪种联结?
题目要求保留每个学生的数据,因此选左联结。
第三步:写出对应的SQL语句
因为select语句中有聚合函数,所以需要group by进行分组,最终结果如下所示:
四、case表达式
case表达式的作用是进行条件判断,相当于Excel的if函数。
语句中when后面的是需要判断的条件,then后面的是符合该条件后输出的值,不符合条件的,进入下一个判断条件······如果不符合全部条件,则输出else指定的值。
例1:将score表中成绩大于等于60的,标为‘及格’;小于60的,标为‘不及格’
select 学号,课程号,成绩,
(case when 成绩 >= 60 then '及格'
when 成绩 < 60 then '不及格'
else null
end) as 是否及格
from score;
SQL先选择score表中【成绩】的第一行数据,因为80符合第一条的条件判断,所以标为‘及格’,暂时结束判断;
然后选择第二行数据,50不符合第一条的条件判断,进入下一条,符合第二条的条件判断,标为‘不及格’,暂时结束判断;
如此循环,直到【成绩】每行的数据都被判断完毕。
例2:查询出每门课程的及格人数和不及格人数
先查询每门课程的人数
由于要将人数分为及格和不及格的,可以用case表达式进行分类统计,即用case表达式替换上图红框中的语句。
因为要汇总及格和不及格人数,所以需要在case表达式前加sum函数进行求和。sum + case 的运行逻辑
第一步:数据分组,先根据课程号进行分组
第二步:应用函数,先运行case表达式,后运行sum函数。case表达式依次对每个成绩进行判断,将成绩大于等于60的,标为‘1’,其他的标为‘0’;然后sum函数对各分组进行求和,得到每个分组的及格人数。
case表达式的注意事项:else子句可以省略,默认为空值,建议写上
end是必要的,不能省略
case表达式不仅可写在select字句,还可写在其他任意字句中
什么时候使用case表达式?
需要进行条件判断的时候
五、SQLZOO练习题