一、表的加法/组合查询
1.union
union将两个表的数据按行合并在一起,两个表重复的数据只保留一个。
- course
![81a67c3042c6b61d2b8cbcd6349a1046.png](https://i-blog.csdnimg.cn/blog_migrate/f117ec8d8703fca5a391e4748be000c6.png)
- course1
![3ba151abeac12ca842f709f481d52536.png](https://i-blog.csdnimg.cn/blog_migrate/653e96d318acdaa37b08fe6626353c8c.png)
合并:
![5141765766fc9e6aac51f84d119e6442.png](https://i-blog.csdnimg.cn/blog_migrate/50e678f9901e7d105c5336aece861403.png)
2.union all
union all将两个表的数据按行合并在一起并保留重复行。
![d870fc911bf9db5dbd4eec9311030c9b.png](https://i-blog.csdnimg.cn/blog_migrate/ec09633957f7f7ae36e42d5d9f7ad375.png)
注意:作为集合运算对象的表的前提是列数要一致。运算结果会导致行的增减,但是不会导致列的增减。
二、表的联结
联结的分类:
![7082bcd5122c6747859b88615bf14f47.png](https://i-blog.csdnimg.cn/blog_migrate/b729849aaefbcc925445c5df80f3a69e.png)
1.交叉联结cross join
交叉联结又称笛卡尔积,交叉联结是对两张表中的全部记录进行交叉组合,结果中的记录数通常是两张表中行数的乘积。例如,表1有n条记录,表2有m条记录,如果将两张表交叉联结,就是将表1中的每一行和表2中的每一行两两合并在一起,结果就会有n*m条记录。
交叉联结是所有联结运算的基础,但是在实际业务中很少使用,一是因为其结果行数太多,需要花费大量的运算时间和高性能设备的支持;二是因为其结果实用价值不高。
2.内联结 inner join
内联结是查找出同时存在于两张表的数据。
实例:student和score这两张表相同的字段是“学号”,通过学号可以将两个表进行内联结。
![e0448f4c77da31a951d0157d1fba2736.png](https://i-blog.csdnimg.cn/blog_migrate/04f17c863f631d1fb060cfd271a97259.png)
![f96a55706947293a239183dd6f70a5a8.png](https://i-blog.csdnimg.cn/blog_migrate/adaa7c0ad13d576f603567b8c75a9c33.png)
![0d1be912164dc771f9ed4f23e544ec6b.png](https://i-blog.csdnimg.cn/blog_migrate/3ea3799af6c7d179abb94166c8eff496.jpeg)
3.左联结 left join
左联结可将from子句左侧的表中的数据全部取出来,与右边进行匹配,若右表无匹配项,则其值为空值Null。
![67071563d4f831f405a28d2ecb5302a2.png](https://i-blog.csdnimg.cn/blog_migrate/0e6dceb975a0b2aefbdf45bdf5d1cd9d.jpeg)
如果两个表的数据有部分重叠,但是想要只取出from子句左侧的表独有的部分(如下图),则需要再加一个where子句进行限定。
![332b93497a4041343653d71b58e547e6.png](https://i-blog.csdnimg.cn/blog_migrate/16734482e1f322388777f1bcf77b409a.png)
比如对于下面student表和score表,想要只取出student表中独有的部分,也就是只取出学号为0002、0003、0004的信息。
![ae8ca09be8cb6bd857d7acdd6ddb0569.png](https://i-blog.csdnimg.cn/blog_migrate/0a5c94db51024146a923204a126b61a6.png)
![4c99a1149909103628b871c5c32de850.png](https://i-blog.csdnimg.cn/blog_migrate/4be73671de1ececadf3a068a6dafe2f3.png)
![a490266a5c4f257bc701a2cdb3f6bf24.png](https://i-blog.csdnimg.cn/blog_migrate/d53a17a23f7398c17d2710ac2fe5d2b3.png)
4.右联结 right join
右联结可将from子句右侧的表中的数据全部取出来,与左边进行匹配,若左表无匹配项,则其值为空值Null。
![1f2cc82e74c15e18a42335b511f608c7.png](https://i-blog.csdnimg.cn/blog_migrate/eb0a57c014e1a3718e1ab581b63a6d92.jpeg)
如果两个表的数据有部分重叠,但是想要只取出from子句右表独有的部分(如下图),可以再加一个where子句进行限定。
![2938d766db4f7f6eeb1db4e933862e8c.png](https://i-blog.csdnimg.cn/blog_migrate/457e80c3d3811c33179eccb06dd35656.png)
select a.学号,a.姓名,b.课程号,b.成绩
from student as a right join score as b
on a.学号=b.学号
WHERE a.学号 is null
5.全联结 full join
全联结会返回左表和右表中的所有行,如果两个表之间有匹配,则进行合并;当没有匹配行时,另一个表中对应的值用空值Null填充。
需要注意的是,mysql是不支持全联结的,这里了解这个概念即可。
SQL运行顺序
1)先运行子查询
2)在每个查询语句里的运行顺序:先运行from,where,group by,having这些子句,然后运行select子句,最后运行order by,limit子句。
三、联结应用案例
案例1:查询所有学生的学号、姓名、选课数、总成绩
需要用到学生表与成绩表,这两张表通过学号联结。涉及到联结就要考虑用哪种联结呢?因为要查到每个学生的信息,也就是要保留学生表里的所有学号,所以要用左联结。
select
![6fc538ee9d60ac3078188b649afa77c3.png](https://i-blog.csdnimg.cn/blog_migrate/66335ce8c46b8033a8958963c3c5e1e9.png)
案例2:查询平均成绩大于60的所有学生的学号,姓名和平均成绩
select
![a1efbf81fb05b50283c81f4b262eaf1d.png](https://i-blog.csdnimg.cn/blog_migrate/08b456f8f5d6672e35022b7c7a834a17.jpeg)
案例3:查询学生的选课情况,信息包括:学号,姓名,课程号,课程名称
此题涉及三个表的联结,用到两个inner join
select
![03be10a638170f4648cba31d29a750ed.png](https://i-blog.csdnimg.cn/blog_migrate/7513706f4539beebdb0cf886bef3be34.jpeg)
四、case表达式
CASE
case表达式的作用相当于一个条件判断的函数,用于判断每一行是否满足某一条件;如果满足when的判断表达式则继续运行then子句,然后case表达式结束,不会再运行后面的when子句了。如果未满足条件,则进行下一个when子句,如果直到最后的when子句也没有找到符合条件的数据,那么就会进行else后面的<表达式>。
注意事项:
(1)else 子句可以省略不写, 这时会默认else是空值,但是为了书写规范,尽量要写。
(2)最后的end不能省略。
(3)case表达式除了可以放在select子句中,还可以放在SQL的任意子句里面
实例1:对成绩表中的学生成绩进行及格或不及格的判断
-- 注意:case子句是select子句的一部分,因此不能忽略其前面的逗号
![e67428bb5db070eb41651749d018380c.png](https://i-blog.csdnimg.cn/blog_migrate/098d6e071ab4fbabce7419f701e06ce6.png)
实例2:查询出每门课程的及格人数和不及格人数
-- 注意下面语句的运行顺序:先按照课程号分组,然后运行case表达式,再运行求和函数sum
![674750d3af1b285d944147679c6d74ee.png](https://i-blog.csdnimg.cn/blog_migrate/965a50cbe605204a5ad00aa7460b91ee.png)
实例3:使用分段[100-85],[85-70],[70-60],[<60] 来统计各科成绩,分别统计:各分段人数,课程号和课程名称
- 思路:
各分数段人数: case when 分情况讨论筛选出各分数段的学生,再用sum汇总及格人数
各分数段人数:用到score表 课程号和课程名称:用到course表 涉及到两个表查询所以要用联结。
分别统计课程号和课程名称的分数段情况 :按course表的课程号和课程名称分组
为什么要用两个列来分组:查询结果要求显示出课程号和课程名称,select里的列名只能是group by子句里的列名,为了让查询结果显示出课程号和课程名称,所以要在group by子句里加入课程名称。这里加入课程名称的前提是课程号与课程名称是一对一的关系,所以去掉课程名称与加上课程名称对分组结果都是没有影响的;若不是一对一关系,加入后会改变分组结果,这时就不能随便加了。
SELECT
![a261e6487dd8b7b6f5dbb317518ff12e.png](https://i-blog.csdnimg.cn/blog_migrate/cd69157b04745271d6f50e3d99176bde.png)
三、SQLZOO平台The JOIN operation练习题
![1cf8bad634d8ba5f0192b3033fd59dda.png](https://i-blog.csdnimg.cn/blog_migrate/680eafcaf8686b1d6385acf1555350e2.jpeg)
![8096474bd583bc96cdbef6700a32bb62.png](https://i-blog.csdnimg.cn/blog_migrate/1d3f35cba74d1ea7c4052143b9ac02f9.jpeg)
![1616c0a36f100f06205655eee826f33a.png](https://i-blog.csdnimg.cn/blog_migrate/a26edfaea601e361259e0ebf32f83af8.jpeg)
![59c68ee8314d7fcd60858469b8ff191a.png](https://i-blog.csdnimg.cn/blog_migrate/215a2b6b31a8f03f147a556f59a2869f.jpeg)
![6d2c5cf63080acadbc4376ea2dd09e3d.png](https://i-blog.csdnimg.cn/blog_migrate/400d937991b0dad0b9725a86f4cd5398.jpeg)
![bc8eb374afc8bb1c7b698178acf9ba06.png](https://i-blog.csdnimg.cn/blog_migrate/5533ea53d4c37fccd7d5d0934e1c2511.jpeg)
![88b9d36f404437165cb96389d288aabc.png](https://i-blog.csdnimg.cn/blog_migrate/7adcaa6ba0e98260393f8f1afd12139b.jpeg)
![fac9356a576d67c5ee40f6f59f0f6470.png](https://i-blog.csdnimg.cn/blog_migrate/7ffc3556717e11902382b79590d82a59.jpeg)
![f8afb136b45ee0d6641effc8449ab451.png](https://i-blog.csdnimg.cn/blog_migrate/09972aef08ef286162c761159a650c88.jpeg)
![769a55d7292043d1de1c60de566f759e.png](https://i-blog.csdnimg.cn/blog_migrate/37bf68059867bb38ad658f43f5ea1cee.jpeg)
![c0f1efd98f40569a049fbaea335cc235.png](https://i-blog.csdnimg.cn/blog_migrate/3bbeb7bf44dc6418fa3b3c3234da6e9e.jpeg)
![831bd28cb5d63b545d5a28dd5b9e873b.png](https://i-blog.csdnimg.cn/blog_migrate/dc19c11302b990067688570e8ddcb7e8.jpeg)
![472accf22c709a4167c4007791a528e1.png](https://i-blog.csdnimg.cn/blog_migrate/8966278aa4f6c0d0daec48ba703ef320.jpeg)
再补充一些比较好的联结的习题:SQLZOO平台More JOIN operations练习题
![55338e5c68ff7594b985aa6e9d2655e8.png](https://i-blog.csdnimg.cn/blog_migrate/e560dc1508a936767188066aa28559ff.jpeg)
解法二:
SELECT
![361d0cc9af376a0fee419e05b61d0b47.png](https://i-blog.csdnimg.cn/blog_migrate/40ba965fb97de7994a2860ab6a5191a9.jpeg)
解法二:同上,用三表联结
![9acca31b35e7519b463c266c9432ee76.png](https://i-blog.csdnimg.cn/blog_migrate/103ce26ebc176e2a768700742e1279d9.jpeg)
![4edac841b36837cdade6e81fa51556a7.png](https://i-blog.csdnimg.cn/blog_migrate/59957c02052490b962a17daac599331d.jpeg)