SQL操作指南八（集合运算）
2018-11-11 本文已影响0人
了不起的树懒
表的加减法
- 表的加法——UNION
首先创建一个名称为shop_list2的表便于代码演示，如下：
+---------+-----------+-----------+------------+-----------+---------------+
| shop_id | shop_name | shop_type | sell_price | buy_price | register_date |
+---------+-----------+-----------+------------+-----------+---------------+
| 0001 | T恤衫 | 衣服 | 1000 | 500 | 2008-09-20 |
| 0002 | 打孔器 | 工具 | 500 | 320 | 2008-09-11 |
| 0003 | 运动T恤 | 衣服 | 4000 | 2800 | NULL |
| 0009 | 手套 | 衣服 | 800 | 500 | NULL |
| 0010 | 水壶 | 厨房用具 | 2000 | 1700 | 2009-09-20 |
+---------+-----------+-----------+------------+-----------+---------------+
接下来，将两张表相加：
SELECT shop_id,shop_name
FROM shop_list
UNION
SELECT shop_id,shop_name
FROM shop_list2;
+---------+-----------+
| shop_id | shop_name |
+---------+-----------+
| 0001 | T恤衫 |
| 0002 | 打孔器 |
| 0003 | 运动T恤 |
| 0004 | 菜刀 |
| 0005 | 砧板 |
| 0006 | 扳手 |
| 0009 | 手套 |
| 0010 | 水壶 |
+---------+-----------+
由表可以看出UNION集合就是并集，该集合运算符会除去重复的记录。
- 集合运算符的注意事项
1.作为运算对象的记录列数必须相同；
当两张表的列数不相同时，UNION是不能将两张表相加的。
2.作为运算对象的记录中列的类型必须一致；
不同类型的数据肯定不能存放在同一列的，如果非要相加的话，可以尝试类型转换。
3.可以使用任何SELECT 语句，但ORDER BY子句只能在最后使用一次；
示例：
SELECT shop_id,shop_name
FROM shop_list
WHERE shop_type = '厨房用具'
UNION
SELECT shop_id,shop_name
FROM shop_list2
WHERE shop_type='厨房用具'
ORDER BY shop_id;
+---------+-----------+
| shop_id | shop_name |
+---------+-----------+
| 0004 | 菜刀 |
| 0005 | 砧板 |
| 0010 | 水壶 |
+---------+-----------+
- 包含重复行的集合运算——ALL选项
UNION的结果没有包含重复行，那么只需要在UNION后面加上ALL关键字的结果就是可以显示所有行，包括重复行。
SELECT shop_id,shop_name
FROM shop_list
UNION ALL
SELECT shop_id,shop_name
FROM shop_list2;
+---------+-----------+
| shop_id | shop_name |
+---------+-----------+
| 0001 | T恤衫 |
| 0002 | 打孔器 |
| 0003 | 运动T恤 |
| 0004 | 菜刀 |
| 0005 | 砧板 |
| 0006 | 扳手 |
| 0001 | T恤衫 |
| 0002 | 打孔器 |
| 0003 | 运动T恤 |
| 0009 | 手套 |
| 0010 | 水壶 |
+---------+-----------+
- 选取表中公共部分——INTERSECT
INTERSECT即为获取两个表的交集。
SELECT shop_id,shop_name
FROM shop_list
INTERSECT
SELECT shop_id,shop_name
FROM shop_list2
ORDER BY shop_id;
MySQL暂时不支持INTERSECT用法。
- 表的减法——EXCEPT
EXCEPT即为表1减取与表2中重复的部分剩下的记录，用法与UNION相同。
SELECT shop_id,shop_name
FROM shop_list
EXCEPT
SELECT shop_id,shop_name
FROM shop_list2
ORDER BY shop_id;
Oracle不适用EXCEPT，其拥有特有的MINUS代替EXCEPT，MySQL暂时不支持EXCEPT这种用法。
联结（以列为单位对表进行联结）
前面学习的集合运算都是造成了行的变化，而联结的作用就是进行列的变化。
- 内联结——INNER JOIN
演示代码将会用到两张表：
+---------+-----------+-----------+------------+-----------+---------------+
| shop_id | shop_name | shop_type | sell_price | buy_price | register_date |
+---------+-----------+-----------+------------+-----------+---------------+
| 0001 | T恤衫 | 衣服 | 2000 | 500 | 2009-09-20 |
| 0002 | 打孔器 | 工具 | 600 | 100 | 2009-09-20 |
| 0003 | 运动T恤 | 衣服 | 3000 | 2800 | 2009-09-20 |
| 0004 | 菜刀 | 厨房用具 | 6000 | 2800 | 2009-09-20 |
| 0005 | 砧板 | 厨房用具 | 2000 | 1299 | 2009-09-20 |
| 0006 | 扳手 | 工具 | 3000 | 1500 | 2009-09-20 |
+---------+-----------+-----------+------------+-----------+---------------+
+---------+-----------+---------+--------+
| area_id | area_name | shop_id | number |
+---------+-----------+---------+--------+
| 000A | 北京 | 0001 | 30 |
| 000A | 北京 | 0002 | 50 |
| 000A | 北京 | 0003 | 15 |
| 000B | 上海 | 0002 | 30 |
| 000B | 上海 | 0003 | 120 |
| 000B | 上海 | 0004 | 20 |
| 000B | 上海 | 0006 | 10 |
| 000B | 上海 | 0007 | 40 |
| 000C | 重庆 | 0003 | 20 |
| 000C | 重庆 | 0004 | 50 |
| 000C | 重庆 | 0006 | 90 |
| 000C | 重庆 | 0007 | 70 |
| 000D | 杭州 | 0001 | 100 |
+---------+-----------+---------+--------+
如上表，两张表的列可以分为如下两类：
1.两张表中有包含相同的列→shop_id；
2.只存在与一张表内的列→除shop_id以外的列；
所谓联结，就是"以1中的的列作为桥梁，将2中的满足其他条件的列汇集到同一结果中"。
我们可以试着从shop_list中取出shop_name和sell_price与shop_area中的内容进行结合。
SELECT SA.area_id,SA.area_name,SA.shop_id,SL.shop_name,SL.sell_price
FROM shop_area AS SA JOIN shop_list AS SL
ON SA.shop_id=SL.shop_id;
+---------+-----------+---------+-----------+------------+
| area_id | area_name | shop_id | shop_name | sell_price |
+---------+-----------+---------+-----------+------------+
| 000A | 北京 | 0001 | T恤衫 | 2000 |
| 000A | 北京 | 0002 | 打孔器 | 600 |
| 000A | 北京 | 0003 | 运动T恤 | 3000 |
| 000B | 上海 | 0002 | 打孔器 | 600 |
| 000B | 上海 | 0003 | 运动T恤 | 3000 |
| 000B | 上海 | 0004 | 菜刀 | 6000 |
| 000B | 上海 | 0006 | 扳手 | 3000 |
| 000C | 重庆 | 0003 | 运动T恤 | 3000 |
| 000C | 重庆 | 0004 | 菜刀 | 6000 |
| 000C | 重庆 | 0006 | 扳手 | 3000 |
| 000D | 杭州 | 0001 | T恤衫 | 2000 |
+---------+-----------+---------+-----------+------------+
联结需要注意的点：
1.FROM子句：因为联结需要用到两张或多张表，使用关键字INNER JOIN就可以把两张表联结在一起，虽然使用别名不是必须的，但是过长的表名会影响可读性，所以尽量使用别名。
2.ON子句：ON后面记载的是联结条件，我们可以在ON后面指定联结所使用的列（联结键），ON子句在进行联结时是必不可少的，并且必须书写在FROM和WHERE之间。
3.SELECT子句：由于使用多张表，所以列的归属会非常混乱。所以为了避免混淆列的归属，可以使用<表的别名>.<列名>的方法书写。
内联结和WHERE子句结合使用
如果不想了解所有地区的情况，只想了解上海地区的情况，可以在联结语句中加入WHERE来限定条件。
SELECT SA.area_id,SA.area_name,SA.shop_id,SL.shop_name,SL.sell_price
FROM shop_area AS SA JOIN shop_list AS SL
ON SA.shop_id=SL.shop_id
WHERE SA.area_id='000B';
+---------+-----------+---------+-----------+------------+
| area_id | area_name | shop_id | shop_name | sell_price |
+---------+-----------+---------+-----------+------------+
| 000B | 上海 | 0002 | 打孔器 | 600 |
| 000B | 上海 | 0003 | 运动T恤 | 3000 |
| 000B | 上海 | 0004 | 菜刀 | 6000 |
| 000B | 上海 | 0006 | 扳手 | 3000 |
+---------+-----------+---------+-----------+------------+
像这样的联结运算结果，不仅可以使用WHERE子句，还能使用GROUP BY、HAVING、ORDER BY等。准确地说，可以完全把它看作一张新的表。
- 外联结——OUTER JOIN
外联结也是通过ON子句使用联结键将两张表进行联结，同时从两张表中选出相应的列，基本使用方法没有改变，但是结果却有所不同。
SELECT SA.area_id,SA.area_name,SA.shop_id,SL.shop_name,SL.sell_price
FROM shop_area AS SA RIGHT OUTER JOIN shop_list AS SL
ON SA.shop_id=SL.shop_id;
+---------+-----------+---------+-----------+------------+
| area_id | area_name | shop_id | shop_name | sell_price |
+---------+-----------+---------+-----------+------------+
| 000A | 北京 | 0001 | T恤衫 | 2000 |
| 000A | 北京 | 0002 | 打孔器 | 600 |
| 000A | 北京 | 0003 | 运动T恤 | 3000 |
| 000B | 上海 | 0002 | 打孔器 | 600 |
| 000B | 上海 | 0003 | 运动T恤 | 3000 |
| 000B | 上海 | 0004 | 菜刀 | 6000 |
| 000B | 上海 | 0006 | 扳手 | 3000 |
| 000C | 重庆 | 0003 | 运动T恤 | 3000 |
| 000C | 重庆 | 0004 | 菜刀 | 6000 |
| 000C | 重庆 | 0006 | 扳手 | 3000 |
| 000D | 杭州 | 0001 | T恤衫 | 2000 |
| NULL | NULL | NULL | 砧板 | 2000 |
+---------+-----------+---------+-----------+------------+
即使联结键在两张表中没有都存在，该行还是会进行联结，不存在的数据以NULL显示。
外联结要点：
1.选取出单张表中的全部的信息：只要数据存在于某一个表中，就能够读取出来。
2.外联结的主表：外联结中的主表很重要，哪张为主表，那么最总的结果中会包含这张主表内的所有数据。指定主表的关键字是LEFT和RIGTH，使用LEFT时FROM子句写在左侧的为主表，反之右侧为主表。
- 3张以上表的联结
原则上，表的联结对表的数量没有限制。
首先创建一张名称为storage的表作为第三张需要联结的表：
+------------+---------+----------------+
| storage_id | shop_id | storage_number |
+------------+---------+----------------+
| S001 | 0001 | 0 |
| S001 | 0002 | 120 |
| S001 | 0003 | 200 |
| S001 | 0004 | 3 |
| S001 | 0005 | 0 |
| S001 | 0006 | 99 |
| S001 | 0007 | 999 |
| S001 | 0008 | 200 |
| S002 | 0001 | 10 |
| S002 | 0002 | 25 |
| S002 | 0003 | 34 |
| S002 | 0004 | 19 |
| S002 | 0005 | 99 |
| S002 | 0006 | 0 |
| S002 | 0007 | 0 |
| S002 | 0008 | 18 |
+------------+---------+----------------+
对第三张表进行联结
SELECT SA.area_id,SA.area_name,SA.shop_id,SL.shop_name,SL.sell_price,ST.storage_number
FROM shop_area AS SA INNER JOIN shop_list AS SL
ON SA.shop_id=SL.shop_id
INNER JOIN storage AS ST
ON SA.shop_id=ST.shop_id
WHERE ST.storage_id='S001';
+---------+-----------+---------+-----------+------------+----------------+
| area_id | area_name | shop_id | shop_name | sell_price | storage_number |
+---------+-----------+---------+-----------+------------+----------------+
| 000A | 北京 | 0001 | T恤衫 | 2000 | 0 |
| 000A | 北京 | 0002 | 打孔器 | 600 | 120 |
| 000A | 北京 | 0003 | 运动T恤 | 3000 | 200 |
| 000B | 上海 | 0002 | 打孔器 | 600 | 120 |
| 000B | 上海 | 0003 | 运动T恤 | 3000 | 200 |
| 000B | 上海 | 0004 | 菜刀 | 6000 | 3 |
| 000B | 上海 | 0006 | 扳手 | 3000 | 99 |
| 000C | 重庆 | 0003 | 运动T恤 | 3000 | 200 |
| 000C | 重庆 | 0004 | 菜刀 | 6000 | 3 |
| 000C | 重庆 | 0006 | 扳手 | 3000 | 99 |
| 000D | 杭州 | 0001 | T恤衫 | 2000 | 0 |
+---------+-----------+---------+-----------+------------+----------------+
可以简单地理解成，将1，2两张表先联结，然后将重新生成的表再与表3进行联结。
- 交叉联结——CROSS JOIN
SELECT SA.area_id,SA.area_name,SA.shop_id,SL.shop_name
FROM shop_area AS SA CROSS JOIN shop_list AS SL;
+---------+-----------+---------+-----------+
| area_id | area_name | shop_id | shop_name |
+---------+-----------+---------+-----------+
| 000A | 北京 | 0001 | T恤衫 |
| 000A | 北京 | 0001 | 打孔器 |
| 000A | 北京 | 0001 | 运动T恤 |
| 000A | 北京 | 0001 | 菜刀 |
| 000A | 北京 | 0001 | 砧板 |
| 000A | 北京 | 0001 | 扳手 |
| 000A | 北京 | 0002 | T恤衫 |
| 000A | 北京 | 0002 | 打孔器 |
| 000A | 北京 | 0002 | 运动T恤 |
| 000A | 北京 | 0002 | 菜刀 |
| 000A | 北京 | 0002 | 砧板 |
| 000A | 北京 | 0002 | 扳手 |
| 000A | 北京 | 0003 | T恤衫 |
| 000A | 北京 | 0003 | 打孔器 |
| 000A | 北京 | 0003 | 运动T恤 |
| 000A | 北京 | 0003 | 菜刀 |
| 000A | 北京 | 0003 | 砧板 |
| 000A | 北京 | 0003 | 扳手 |
| 000B | 上海 | 0002 | T恤衫 |
| 000B | 上海 | 0002 | 打孔器 |
| 000B | 上海 | 0002 | 运动T恤 |
| 000B | 上海 | 0002 | 菜刀 |
| 000B | 上海 | 0002 | 砧板 |
| 000B | 上海 | 0002 | 扳手 |
| 000B | 上海 | 0003 | T恤衫 |
| 000B | 上海 | 0003 | 打孔器 |
| 000B | 上海 | 0003 | 运动T恤 |
| 000B | 上海 | 0003 | 菜刀 |
| 000B | 上海 | 0003 | 砧板 |
| 000B | 上海 | 0003 | 扳手 |
| 000B | 上海 | 0004 | T恤衫 |
| 000B | 上海 | 0004 | 打孔器 |
| 000B | 上海 | 0004 | 运动T恤 |
| 000B | 上海 | 0004 | 菜刀 |
| 000B | 上海 | 0004 | 砧板 |
| 000B | 上海 | 0004 | 扳手 |
| 000B | 上海 | 0006 | T恤衫 |
| 000B | 上海 | 0006 | 打孔器 |
| 000B | 上海 | 0006 | 运动T恤 |
| 000B | 上海 | 0006 | 菜刀 |
| 000B | 上海 | 0006 | 砧板 |
| 000B | 上海 | 0006 | 扳手 |
| 000B | 上海 | 0007 | T恤衫 |
| 000B | 上海 | 0007 | 打孔器 |
| 000B | 上海 | 0007 | 运动T恤 |
| 000B | 上海 | 0007 | 菜刀 |
| 000B | 上海 | 0007 | 砧板 |
| 000B | 上海 | 0007 | 扳手 |
| 000C | 重庆 | 0003 | T恤衫 |
| 000C | 重庆 | 0003 | 打孔器 |
| 000C | 重庆 | 0003 | 运动T恤 |
| 000C | 重庆 | 0003 | 菜刀 |
| 000C | 重庆 | 0003 | 砧板 |
| 000C | 重庆 | 0003 | 扳手 |
| 000C | 重庆 | 0004 | T恤衫 |
| 000C | 重庆 | 0004 | 打孔器 |
| 000C | 重庆 | 0004 | 运动T恤 |
| 000C | 重庆 | 0004 | 菜刀 |
| 000C | 重庆 | 0004 | 砧板 |
| 000C | 重庆 | 0004 | 扳手 |
| 000C | 重庆 | 0006 | T恤衫 |
| 000C | 重庆 | 0006 | 打孔器 |
| 000C | 重庆 | 0006 | 运动T恤 |
| 000C | 重庆 | 0006 | 菜刀 |
| 000C | 重庆 | 0006 | 砧板 |
| 000C | 重庆 | 0006 | 扳手 |
| 000C | 重庆 | 0007 | T恤衫 |
| 000C | 重庆 | 0007 | 打孔器 |
| 000C | 重庆 | 0007 | 运动T恤 |
| 000C | 重庆 | 0007 | 菜刀 |
| 000C | 重庆 | 0007 | 砧板 |
| 000C | 重庆 | 0007 | 扳手 |
| 000D | 杭州 | 0001 | T恤衫 |
| 000D | 杭州 | 0001 | 打孔器 |
| 000D | 杭州 | 0001 | 运动T恤 |
| 000D | 杭州 | 0001 | 菜刀 |
| 000D | 杭州 | 0001 | 砧板 |
| 000D | 杭州 | 0001 | 扳手 |
+---------+-----------+---------+-----------+
交叉联结时，无法使用ON子句，这是因为交叉联结是对两张表内全部记录进行交叉组合，因此结果中记录数通常是两张表的行数乘积。
