MySQL教程四——集合运算

---

一、表的加减法

1. 什么是集合运算？

集合在数学领域表示“各种各样的事物的总和”, 在数据库领域表示记录的集合. 具体来说,表、视图和查询的执行结果都是记录的集合, 其中的元素为表或者查询结果中的每一行。

在标准 SQL 中, 分别对检索结果使用 UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算, 像UNION,INTERSECT, EXCEPT这种用来进行集合运算的运算符称为集合运算符。

2. 表的加法–UNION

插入一个product2的表格

CREATE TABLE `product2` (
  `product_id` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `product_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `product_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `sale_price` int DEFAULT NULL,
  `purchase_price` int DEFAULT NULL,
  `regist_date` date DEFAULT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `product2` */

insert  into `product2`(`product_id`,`product_name`,`product_type`,`sale_price`,`purchase_price`,`regist_date`) values 
('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的具体用法及查询结果:

SELECT product_id, product_name
	FROM products
	UNION
	SELECT product_id, product_name
	FROM product2;

通过观察可以发现,商品编号为“ 0001 ”~“ 0003 ”的 3 条记录在两个表中都存在,因此大家可能会认为结果中会出现重复的记录,但是 UNION 等集合运算符通常都会除去重复的记录。

上述查询是对不同的两张表进行求并集运算. 对于同一张表, 实际上也是可以进行求并集的。

练习题:

假设连锁店想要增加毛利率超过 50%或者售价低于 800 的货物的存货量, 请使用 UNION 对分别满足上述两个条件的商品的查询结果求并集。

结果应该类似于:

SELECT  product_id,product_name,product_type
       ,sale_price,purchase_price
  FROM products 
 WHERE sale_price<800
  
 UNION
 
SELECT  product_id,product_name,product_type
       ,sale_price,purchase_price
  FROM products
 WHERE sale_price>1.5*purchase_price;

思考: 如果不使用 UNION 该怎么写查询语句?

SELECT  product_id,product_name,product_type
       ,sale_price,purchase_price
  FROM products
 WHERE sale_price < 800 
    OR sale_price > 1.5 * purchase_price;

2.2 UNION 与 OR 谓词

对于上边的练习题, 如果你已经正确地写出来查询, 你会发现, 使用 UNION 对两个查询结果取并集, 和在一个查询中使用 WHERE 子句, 然后使用 OR 谓词连接两个查询条件, 能够得到相同的结果。

那么是不是就没必要引入 UNION 了呢? 当然不是这样的. 确实, 对于同一个表的两个不同的筛选结果集, 使用 UNION 对两个结果集取并集, 和把两个子查询的筛选条件用 OR 谓词连接, 会得到相同的结果, 但倘若要将两个不同的表中的结果合并在一起, 就不得不使用 UNION 了.。

而且, 即便是对于同一张表, 有时也会出于查询效率方面的因素来使用 UNION。

练习题 :

分别使用 UNION 或者 OR 谓词,找出毛利率不足 30%或毛利率未知的商品。

-- 使用 OR 谓词
SELECT * 
  FROM products 
 WHERE sale_price / purchase_price < 1.3 
    OR sale_price / purchase_price IS NULL;

-- 使用 UNION
SELECT * 
  FROM products 
 WHERE sale_price / purchase_price < 1.3
 
 UNION
SELECT * 
  FROM products 
 WHERE sale_price / purchase_price IS NULL;

2.3 包含重复行的集合运算 UNION ALL

在4.1.1 中我们发现, SQL 语句的 UNION 会对两个查询的结果集进行合并和去重, 这种去重不仅会去掉两个结果集相互重复的, 还会去掉一个结果集中的重复行. 但在实践中有时候需要需要不去重的并集, 在 UNION 的结果中保留重复行的语法其实非常简单,只需要在 UNION 后面添加 ALL 关键字就可以了。

例如, 想要知道 product 和 product2 中所包含的商品种类及每种商品的数量, 第一步,就需要将两个表的商品种类字段选出来, 然后使用 UNION ALL 进行不去重地合并. 接下来再对两个表的结果按 product_type 字段分组计数。

-- 保留重复行
SELECT product_id, product_name
  FROM products
 UNION ALL
SELECT product_id, product_name
  FROM product2;

练习题:

商店决定对products表中利润低于50% 或者 售价低于1000的商品提价, 请使用UNION ALL 语句将分别满足上述两个条件的结果取并集. 查询结果类似下表:

SELECT * 
  FROM products
 WHERE sale_price < 1000
 UNION ALL
SELECT * 
  FROM products
 WHERE sale_price < 1.5 * purchase_price;

2.4 隐式数据类型转换

通常来说, 我们会把类型完全一致, 并且代表相同属性的列使用 UNION 合并到一起显示, 但有时候, 即使数据类型不完全相同, 也会通过隐式类型转换来将两个类型不同的列放在一列里显示, 例如字符串和数值类型:

SELECT product_id, product_name, '1'
  FROM products
 UNION
SELECT product_id, product_name,sale_price
  FROM Product2;

需要注意的是 hive中进行join关联时，关联列要避免使用隐式数据类型转换，否则容易导致数据倾斜

练习题:

使用 SYSDATE()函数可以返回当前日期时间, 是一个日期时间类型的数据, 试测试该数据类型和数值,字符串等类型的兼容性。

例如, 以下代码可以正确执行, 说明时间日期类型和字符串,数值以及缺失值均能兼容。

SELECT SYSDATE(), SYSDATE(), SYSDATE()
 
 UNION
 
SELECT 'chars', 123,  null

3 MySQL 8.0 不支持交运算INTERSECT

集合的交, 就是两个集合的公共部分, 由于集合元素的互异性, 集合的交只需通过文氏图就可以很直观地看到它的意义。

虽然集合的交运算在SQL标准中已经出现多年了, 然而很遗憾的是, 截止到 MySQL 8.0 版本, MySQL 仍然不支持 INTERSECT 操作。

SELECT product_id, product_name
  FROM products
INTERSECT
SELECT product_id, product_name
  FROM Product2

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘SELECT product_id, product_name FROM Product2’ at line 4

此时需要用 inner join 来求得交集

SELECT p1.product_id, p1.product_name
  FROM products p1
INNER JOIN Product2 p2
ON p1.product_id=p2.product_id;

4 差集,补集与表的减法

求集合差集的减法运算和实数的减法运算有些不同, 当使用一个集合A减去另一个集合B的时候,对于只存在于集合B而不存在于集合A的元素, 采取直接忽略的策略,因此集合A和B做减法只是将集合A中也同时属于集合B的元素减掉。

4.1 MySQL 8.0 还不支持 EXCEPT 运算

MySQL 8.0 还不支持 表的减法运算符 EXCEPT. 不过, 借助第六章学过的NOT IN 谓词, 我们同样可以实现表的减法。

练习题:

找出只存在于products表但不存在于product2表的商品。

-- 使用 NOT IN 子句的实现方法
SELECT * 
  FROM products
 WHERE product_id NOT IN (SELECT product_id 
                            FROM Product2)

4.2 EXCEPT 与 NOT 谓词

通过上述练习题的MySQL解法, 我们发现, 使用 NOT IN 谓词, 基本上可以实现和SQL标准语法中的EXCEPT运算相同的效果。

练习题:

使用NOT谓词进行集合的减法运算, 求出Product表中, 售价高于2000,但利润低于30%的商品, 结果应该如下表所示。

SELECT * 
  FROM products
 WHERE sale_price > 2000 
   AND product_id NOT IN (SELECT product_id 
                            FROM products 
                           WHERE sale_price<1.3*purchase_price);

4.3 INTERSECT 与 AND 谓词

对于同一个表的两个查询结果而言, 他们的交INTERSECT实际上可以等价地将两个查询的检索条件用AND谓词连接来实现。

练习题:

****使用AND谓词查找product表中利润率高于50%,并且售价低于1500的商品,查询结果如下所示。

SELECT * 
  FROM products
 WHERE sale_price > 1.5 * purchase_price 
   AND sale_price < 1500;

5 对称差

两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合. 对称差也是个非常基础的运算, 例如, 两个集合的交就可以看作是两个集合的并去掉两个集合的对称差.上述方法在其他数据库里也可以用来简单地实现表或查询结果的对称差运算: 首先使用UNION求两个表的并集, 然后使用INTERSECT求两个表的交集, 然后用并集减去交集, 就得到了对称差。

但由于在MySQL 8.0 里, 由于两个表或查询结果的并不能直接求出来, 因此并不适合使用上述思路来求对称差. 好在还有差集运算可以使用. 从直观上就能看出来, 两个集合的对称差等于 A-B并上B-A, 因此实践中可以用这个思路来求对称差。

练习题:

使用products表和Product2表的对称差来查询哪些商品只在其中一张表, 结果类似于:
提示: 使