MySQL--集合运算

本文根据《SQL基础教程》第二版写，里面的概念、代码、例子均来自此书。

1 表的加减法

• 集合运算就是对满足同一规则的记录进行的加减等四则运算
• 集合运算符有union（并集）、intersect（交集）、except（差集）
• 集合运算符可以去除重复行
• 如果希望集合运算符保留重复行，需要使用all选项

什么是集合运算

集合在数据库中表示记录的集合。表、视图和查询的执行结果都是记录的集合。所谓集合运算，就是对满足同一规则的记录进行的加减等四则运算。通过集合运算，可以得到两张表中的记录的集合或者公共记录的集合，又或者其中某张表中的记录的集合。

表的加法–union（并集）

首先创建product2表，结构与product表相同。

CREATE TABLE Product2
(product_id      CHAR(4)      NOT NULL,
 product_name    VARCHAR(100) NOT NULL,
 product_type    VARCHAR(32)  NOT NULL,
 sale_price      INTEGER      ,
 purchase_price  INTEGER      ,
 regist_date      DATE         ,
 PRIMARY KEY (product_id));

--MySQL
START TRANSACTION;

INSERT INTO Product2 VALUES ('0001', 'T恤', '衣服', 1000, 500, '2009-09-20');
INSERT INTO Product2 VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product2 VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product2 VALUES ('0009', '手套', '衣服', 800, 500, NULL);
INSERT INTO Product2 VALUES ('0010', '水壶', '厨房用具', 2000, 1700, '2009-09-20');

COMMIT;

-- 结果为
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001       | T恤          | 衣服         |       1000 |            500 | 2009-09-20  |
| 0002       | 打孔器       | 办公用品     |        500 |            320 | 2009-09-11  |
| 0003       | 运动T恤      | 衣服         |       4000 |           2800 | NULL        |
| 0009       | 手套         | 衣服         |        800 |            500 | NULL        |
| 0010       | 水壶         | 厨房用具     |       2000 |           1700 | 2009-09-20  |
+------------+--------------+--------------+------------+----------------+-------------+

接下来，使用union对表进行假发运算

mysql> select product_id,product_name from product
    -> union
    -> select product_id,product_name from product2;
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 0001       | T恤          |
| 0002       | 打孔器       |
| 0003       | 运动T恤      |
| 0004       | 菜刀         |
| 0005       | 高压锅       |
| 0006       | 叉子         |
| 0007       | 擦菜板       |
| 0008       | 圆珠笔       |
| 0009       | 手套         |
| 0010       | 水壶         |
+------------+--------------+

集合运算符会除去重复的记录。

集合运算的注意事项

（不仅限于union，之后学习的所有运算符都要遵守这些注意事项）

1、作为运算对象的记录的列数必须相同
2、作为运算对象的记录中列的类型必须一致
3、可以使用任何select语句，但order by子句只能在最后使用一次

mysql> select product_id,product_name from product where product_type='厨房用具'
    -> union
    -> select product_id,product_name from product2 where product_type='厨房用具'
    -> order by product_id;
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 0004       | 菜刀         |
| 0005       | 高压锅       |
| 0006       | 叉子         |
| 0007       | 擦菜板       |
| 0010       | 水壶         |
+------------+--------------+

包含重复行的集合运算–all选项

很简单，只需要在union后面添加all就可以，all选项在union之外的集合运算符中同样可以使用。
例如保留重复行：

mysql> select product_id,product_name from product
    -> union all
    -> select product_id,product_name from product2;
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 0001       | T恤          |
| 0002       | 打孔器       |
| 0003       | 运动T恤      |
| 0004       | 菜刀         |
| 0005       | 高压锅       |
| 0006       | 叉子         |
| 0007       | 擦菜板       |
| 0008       | 圆珠笔       |
| 0001       | T恤          |
| 0002       | 打孔器       |
| 0003       | 运动T恤      |
| 0009       | 手套         |
| 0010       | 水壶         |
+------------+--------------+

选取表中的公共部分–intersect（交集）

MySQL中不支持交集运算

记录的减法–except（差集）

MySQL中不支持差集运算

2 联结（以列为单位对表进行联结）

• 联结（join）就是将其他表中的列添加过来，进行“添加列”的集合运算。union是以行（纵向）为单位进行操作，而联结则是以列（横向）为单位进行的
• 联结大体上分为内联结和外联结两种。

联结就是将其他表中的列添加过来，进行’'添加列"的运算。

内联结–inner join

A:两张表中都包含的列–商品编号
B:只存在于一张表内的列–商品编号之外的列
联结运算，就是以A中的列作为桥梁，将B中满足同样条件的列汇集到同意结果之中。
下面我们试着从product表中取出商品名称和销售单价，并与shopproduct表中的内容跟进行结合。

mysql> select sp.shop_id,sp.shop_name,sp.product_id,p.product_name,p.sale_price
    -> from shopproduct as sp inner join product as p
    -> on sp.product_id=p.product_id;
+---------+-----------+------------+--------------+------------+
| shop_id | shop_name | product_id | product_name | sale_price