一、表的加法
关键词:
union:将两个表的数据按行合并在一起。会将两个表里重复的数据删除,只保留一个。
Union all 可以保留两个表里重复的行。
用union将两个表合并
SELECT
用union all将两个表合并(保留重复值)
SELECT
二、表的联结
关系数据库是由多张表组成的,表和表之间通过列产生联系。
联结类型:交叉联结、内联结、左联结、右联结、全联结
交叉联结(笛卡尔积)cross join:将一个表中的每一行都与另外一个表中的每一行合并在一起。得出结果的行数是两张表中行数的乘积。
交叉联结在实际业务中应用的比较少,原因:
- 结果没有实用价值
- 结果行数太多,要花费大量的计算时间和高性能设备的支持。
但是交叉联结是所有联结运算的基础。
交叉联结
SELECT
内联结 inner join:查找出同时存在两张表中的数据。先取出两个表中符合条件的行,再进行交叉联结。
内联结
SELECT
左联结left join:将左侧的表作为主表,将主表中的数据全部取出来,右边的表选出和左表相同的行。
SELECT
可以看出如果右表没有左表的数据,结果将返回为空值。
将左表只包含的数据查询出来:
SELECT
右联结:right join,将右表的数据全部取出来。左边的表只选出和右表相同的行,将两个表取出的数据合并。
SELECT
可以看出结果中包含了右表中所有学号0001,0002,0003,但不包括左表中的0004。
同样可以用 null 来取出只有右表包含的学号:
SELECT
全联结full join:返回左表和右表中的所有行,当某行有匹配的时候,两行进行合并,如果没有匹配的,对应用空值进行填充。
MySQL不支持全联结。
各种联结的总结:
- 如果要得出两个表的公共部分,使用内联结
- 在实际业务中,如果要生成固定行数的单据,或者取出某一个表的全部数据,就用左联结或者右联结。
三、用SQL中的联结语句解决业务问题
查询所有学生的学号、姓名、选课数、总成绩
解题思路:
- 首先查询结果中的列名来源student和score两个表,并且是所有学生的学号,所以使用左联结将两个表结合
- 结合之后,将表按学号、姓名进行分组
- 对分组之后的表使用count和sum函数
SELECT
查询平均成绩大于85的所有学生的学号、姓名和平均成绩
解题思路:
- 首先查询结果中的列名来源student和score两个表,并且是所有学生的学号,所以使用左联结将两个表结合
- 结合之后,将表按学号、姓名进行分组
- 用having 子句对分组之后的结果进行筛选(avg成绩>85)
SELECT
查询学生的选课情况:学号、姓名、课程号、课程名称
解题思路:
学号、姓名在student中,课程号和课程成名在course中,但是两个表之间没有联结键。又因为score中包含了学号和课程号,所以使用score将表student和course联结起来
SELECT
四、Case表达式
Case表达式在区分情况时使用。
Case表达式的语法:
Case
其中的判断表达式类似 列=值,case表达式会从对最初的when子句中的“判断表达式”进行求值开始执行,如果结果为真,就返回then子句中的表达式,case表达式的执行到此为止。如果结果不为真,就转到对下一条when子句求值。如果直到最后的when子句返回结果都不为真,就返回else中的表达式。
- Else子句可以省略,表示默认else null,但是最好不要省略。
- End子句不可以省略。
- Case表达式可以书写在任意位置。
- Case表达式可以将select语句中的行列结果进行互换。
将学生每门课程的得分用及格和不及格标识出来
SELECT
查询每门课程的及格人数和不及格人数
解题思路:首先将score按课程号分组,再对分组结果使用case表达式
SELECT
使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩,分别统计:各分数段人数、课程号、和课程名称
解题思路:
- 课程号和成绩在score中,所有课程名称在course中,用右联结将两个表结合在一起
- 将联结之后的表按课程号分组,再对分组之后的成绩运用case表达式
SELECT
五、SQLzoo练习题
The JOIN operationsqlzoo.net1
select
2
SELECT
3
SELECT
4
select
5
SELECT
6
select
7
select
8
SELECT
9
SELECT
10
select
11
select
12
select
13
select