DW学习--MySQL04（集合运算）_dw数字加减-优快云博客

本文链接：https://blog.youkuaiyun.com/OohMuYi/article/details/111504009

本文详细介绍了MySQL中的集合运算，包括UNION的加法操作、交集和差集的实现，以及联结（JOIN）的内联结、外联结和交叉联结。讲解了各种集合运算是如何处理不同表之间的数据合并和筛选，并提供了相应的例子和注意事项，如数据类型的匹配、去重和使用WHERE子句的情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1 表的加减法

集合：在数据库领域表示记录的集合，具体来说，表、视图和查询的执行结果都是记录的集合。
集合运算：是对满足同一规则的记录进行的加减等四则运算。几种集合运算的文氏图：

1.1 表的加法-UNION

UNION的用法：相当于并集运算
【例子】包含两张表的全部商品

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

注意： UNION 等集合运算符通常都会除去重复的记录，即UNION 会对两个查询的结果集进行合并和去重.

UNION 与 OR 谓词：使用 UNION对两个查询结果取并集, 和在一个查询中使用WHERE子句, 然后使用 OR 谓词连接两个查询条件, 能够得到相同的结果。对于将不同的两张表合并在一起，只能使用UNION。
【例子】UNION 与 OR 谓词相同的结果

# 使用 OR 谓词
SELECT * 
  FROM product 
 WHERE sale_price / purchase_price < 1.3 
    OR sale_price / purchase_price IS NULL;
# 使用 UNION
SELECT * 
FROM product 
WHERE sale_price / purchase_price < 1.3

UNION
SELECT * 
FROM product 
WHERE sale_price / purchase_price IS NULL;

集合运算的注意事项：
- 1.作为运算对象的记录的列数必须相同
  【例子】一部分记录包含 2 列，另一部分记录包含 3 列时会发生错误，无法进行加法运算。
```
-- 列数不一致时会发生错误
SELECT product_id, product_name
FROM Product
UNION
SELECT product_id, product_name, sale_price
FROM Product2;
```
- 2.作为运算对象的记录中列的类型必须一致（相同位置上的列必须是同一数据类型）
  【例子】列数相同，但是第 2 列的数据类型并不一致，（一个是数值类型，一个是日期类型型），会发生错误。
```
-- 数据类型不一致时会发生错误
SELECT product_id, sale_price
  FROM Product
 UNION
SELECT product_id, regist_date
  FROM Product2;
```
- 3.可以使用任何SELECT语句，但ORDER BY子句只能在最后使用一次
  通过 UNION进行并集运算时可以使用任何形式的 SELECT 语句， WHERE、GROUP BY、HAVING 等子句都可以使用
  【例子】ORDER BY子句只能在最后使用一次
```
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 的结果中保留重复行，得到不去重的并集。

语法：只需要在 UNION后面添加 ALL关键字就可以了。
【例子】包含两张表的全部商品并保留重复商品

# 保留重复行
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  | 水壶

隐式类型转换：有时候, 即使数据类型不完全相同, 也会通过隐式类型转换来将两个类型不同的列放在一列里显示
【例子】字符串和数值类型
```
SELECT product_id, product_name, '1'
FROM product
UNION
SELECT product_id, product_name,sale_price
FROM product2;
```
INTERSECT： MySQL 8.0 不支持INTERSECT交运算，集合的交, 就是两个集合的公共部分。
INTERSECT 与 AND 谓词：对于同一个表的两个查询结果而言, 他们的交INTERSECT实际上可以等价地将两个查询的检索条件用AND谓词连接来实现.
【例子】使用AND谓词查找product表中利润率高于50%,并且售价低于1500的商品
```
SELECT * 
FROM product
WHERE sale_price > 1.5 * purchase_price 
  AND sale_price < 1500
```

1.2 差集,补集与表的减法

表的减法：集合A和B做减法只是将集合A中也同时属于集合B的元素减掉.
EXCEPT：MySQL 8.0 还不支持EXCEPT 运算

EXCEPT 与 NOT 谓词：使用NOT IN谓词, 基本上可以实现和SQL标准语法中的EXCEPT运算相同的效果。
【例子】求出product表中, 售价高于2000,但利润低于30%的商品，结果如下
在这里插入图片描述

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

对称差：两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合。两个集合的交就可以看作是两个集合的并去掉两个集合的对称差。
- 理想实现方法：首先使用UNION求两个表的并集, 然后使用INTERSECT求两个表的交集, 然后用并集减去交集, 就得到了对称差。
- 现实实现方法：由于在MySQL 8.0 里, 由于两个表或查询结果的交不能直接求出来，因此使用差集运算。两个集合的对称差等于 A-B并上B-A。
  【例子】使用product表和product2表的对称差来查询哪些商品只在其中一张表，结果如下:
```
-- 使用 NOT IN 实现两个表的差集
SELECT * 
FROM product
WHERE product_id NOT IN (SELECT product_id FROM product2)
UNION
SELECT * 
FROM product2
WHERE product_id NOT IN (SELECT product_id FROM product)
```
借助并集和差集迂回实现交集运算 INTERSECT：两个集合的交可以看作是两个集合的并去掉两个集合的对称差

1.3 拓展–bag模型

bag模型：Bag 是和 set 类似的一种数学结构, 不一样的地方在于: bag 里面允许存在重复元素, 如果同一个元素被加入多次, 则袋子里就有多个该元素.
bag的并运算：对于两个 bag, 他们的并运算会按照:
- 1.该元素是否至少在一个 bag 里出现过,
- 2.该元素在两个 bag 中的最大出现次数。
  对于 A = {1,1,1,2,3,5,7}, B = {1,1,2,2,4,6,8} 两个 bag, 它们的并就等于 {1,1,1,2,2,3,4,5,6,7,8}.
bag的交运算：
- 1.该元素是否同时属于两个 bag
- 2.该元素在两个 bag 中的最小出现次数
  对于 A = {1,1,1,2,3,5,7}, B = {1,1,2,2,4,6,8} 两个 bag, 它们的交运算结果就等于 {1,1,2}.
EXCEPT ALL 与bag 的差：
- 1.该元素是否属于作为被减数的 bag
- 2.该元素在两个 bag 中的出现次数
  这两个方面来进行计算. 只有属于被减数的bag的元素才参与EXCEP ALL运算, 并且差bag中的次数,等于该元素在两个bag的出现次数之差(差为零或负数则不出现)。
  对于 A = {1,1,1,2,3,5,7}, B = {1,1,2,2,4,6,8} 两个 bag, 它们的差就等于 {1,3,5,7}.

2 联结

联结（JOIN）：就是将其他表中的列添加过来，进行“添加列”的集合运算。
JOIN和UNION：UNION是以行（纵向）为单位进行操作，而联结则是以列（横向）为单位
进行的。
作用：使用关联子查询也可以从其他表获取信息，但使用联结更适合从多张表中选取数据

2.1 内联结–INNER JOIN

语法格式：FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)> ，
- INNER 关键词表示使用了内连结
- ON 子句是专门用来指定连结条件的

使用内连结从两个表获取信息：
【例子】：将商品表和商店商品表对应起来
在这里插入图片描述

SELECT SP.shop_id
   ,SP.shop_name
   ,SP.product_id
   ,P.product_name
   ,P.product_type
   ,P.sale_price
   ,SP.quantity
 FROM shopproduct AS SP
INNER JOIN product AS P
   ON SP.product_id = P.product_id;

在这里插入图片描述

内联结使用的三个注意点：
- 1.进行连结时需要在 FROM 子句中使用多张表.
  FROM ShopProduct AS SP INNER JOIN Product AS P，要习惯使用别名
- 2.必须使用 ON 子句来指定连结条件.
  ON 是专门用来指定联结条件的，它能起到与 WHERE 相同的作用，需要指定多个键时，同样可以使用 AND、OR， ON 必须书写在 FROM 和 WHERE 之间。
- 3.SELECT 子句中的列最好按照 表名.列名 的格式来使用
  SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price，，由于多表联结时，某个列到底属于哪张表比较容易混乱，因此采用了这样的防范措施。

结合 WHERE 子句使用内连结：

第一种增加 WEHRE 子句的方式, 就是把上述查询作为子查询, 用括号封装起来, 然后在外层查询增加筛选条件.

SELECT *
FROM (-- 第一步查询的结果
    SELECT SP.shop_id
           ,SP.shop_name
           ,SP.product_id
           ,P.product_name
           ,P.product_type
           ,P.sale_price
           ,SP.quantity
      FROM shopproduct AS SP
     INNER JOIN product AS P
        ON SP.product_id = P.product_id) AS STEP1
WHERE shop_name = '东京'
AND product_type = '衣服' ;

查询的结果其实也是一张表,只不过是一张虚拟的表, 它并不真实存在于数据库中。
上述查询的执行顺序：FROM 子句->WHERE 子句->SELECT 子句。WHERE子句将在FROM 子句之后执行, 也就是说, 在做完INNER JOIN … ON得到一个新表后, 才会执行 WHERE 子句。标准写法如下：

    SELECT SP.shop_id
           ,SP.shop_name
           ,SP.product_id
           ,P.product_name
           ,P.product_type
           ,P.sale_price
           ,SP.quantity
      FROM shopproduct AS SP
     INNER JOIN product AS P
        ON SP.product_id = P.product_id
WHERE shop_name = '东京'
AND product_type = '衣服' ;
/*
两张表是先按照连结列进行了连结, 得到了一张新表,
 然后 WHERE 子句对这张新表的行按照两个条件进行了筛选, 
 最后, SELECT 子句选出了那些我们需要的列.
*/

先连结再筛选的标准写法的执行顺序是，两张完整的表做了连结之后再做筛选，如果要连结多张表，或者需要做的筛选比较复杂时，在写 SQL 查询时会感觉比较吃力。
因此采用任务分解的方法,先分别在两个表使用 WHERE 进行筛选,然后把上述两个子查询连结起来。

SELECT SP.shop_id
   ,SP.shop_name
   ,SP.product_id
   ,P.product_name
   ,P.product_type
   ,P.sale_price
   ,SP.quantity
FROM (-- 子查询 1:从 shopproduct 表筛选出东京商店的信息
    SELECT *
      FROM shopproduct
     WHERE shop_name = '东京' ) AS SP
INNER JOIN -- 子查询 2:从 product 表筛选出衣服类商品的信息
(SELECT *
  FROM product
 WHERE product_type = '衣服') AS P
ON SP.product_id = P.product_id;

结合 GROUP BY 子句使用内连结：需要根据分组列位于哪个表区别对待。如果分组列和被聚合的列不在同一张表, 且二者都未被用于连结两张表, 则只能先连结, 再聚合。
【例子】每个商店中, 售价最高的商品的售价分别是多少?
```
-- 参考答案
SELECT SP.shop_id
  ,SP.shop_name
  ,MAX(P.sale_price) AS max_price
FROM shopproduct AS SP
INNER JOINproduct AS P
ON SP.product_id = P.product_id
GROUP BY SP.shop_id,SP.shop_name
```
自连结(SELF JOIN)：一张表也可以与自身作连结，这种连接称之为自连结。

注意：自连结并不是区分于内连结和外连结的第三种连结, 自连结可以是外连结也可以是内连结, 它是不同于内连结外连结的另一个连结的分类方法

内连结与关联子查询：
【例子】找出每个商品种类当中售价高于该类商品的平均售价的商品

# 使用关联子查询来实现
SELECT product_type, product_name, sale_price
FROM product AS P1
WHERE sale_price > (SELECT AVG(sale_price)
                   FROM product AS P2
                  WHERE P1.product_type = P2.product_type
                  GROUP BY product_type);

在这里插入图片描述

自然连结(NATURAL JOIN)：它其实是内连结的一种特例–当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件
- 语法：SELECT * FROM shopproduct NATURAL JOIN product
- 结果：上述查询得到的结果, 会把两个表的公共列放在第一列, 然后按照两个表的顺序和表中列的顺序, 将两个表中的其他列都罗列出来。

2.2 外联结–OUTER JOIN

外联结：外连结会根据外连结的种类有选择地保留无法匹配到的行。（内连结会丢弃两张表中不满足 ON 条件的行）
外联结形式；
- 左连结：会保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值
- 右连结：会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值
- 全外连结：会同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充.

使用左连结从两个表获取信息：
【例子】：内连结前后的结果，product 表中有两种商品并未在内连结的结果里。

SELECT SP.shop_id
   ,SP.shop_name
   ,SP.product_id
   ,P.product_name
   ,P.sale_price
FROM product AS P
LEFT OUTER JOIN shopproduct AS SP
ON SP.product_id = P.product_id;

在这里插入图片描述

外联结要点：
- 1.选取出单张表中全部的信息
  外连结名称的由来也跟 NULL 有关，即“结果中包含原表中不存在（在原表之外）的信息”。相反，只包含表内信息的连结也就被称为内连结了。
- 2.使用 LEFT、RIGHT 来指定主表
  把哪张表作为主表，最终的结果中会包含主表内所有的数据，指定主表的关键字是 LEFT 和 RIGHT。它们的功能没有任何区别，使用哪一个都可以，结果也完全相同。
全外连结： MySQL8.0 目前还不支持全外连结，不过我们可以对左连结和右连结的结果进行 UNION 来实现全外连结。

2.3 多表联结

多表内联结：
【例子】根据Inventoryproduct表及 shopproduct 表和 product 表, 使用内连接找出每个商店都有那些商品, 每种商品的库存总量分别是多少
```
SELECT SP.shop_id
   ,SP.shop_name
   ,SP.product_id
   ,P.product_name
   ,P.sale_price
   ,IP.inventory_quantity
FROM shopproduct AS SP
INNER JOIN product AS P
ON SP.product_id = P.product_id
INNER JOIN Inventoryproduct AS IP
ON SP.product_id = IP.product_id
WHERE IP.inventory_id = 'P001';
```
连结第三张表的时候, 也是通过 ON 子句指定连结条件。由于 product 表和 shopproduct 表已经进行了连结,因此就无需再对 product 表和 Inventoryproduct 表进行连结了

多表外联结：
【例子】

SELECT P.product_id
   ,P.product_name
   ,P.sale_price
   ,SP.shop_id
   ,SP.shop_name
   ,IP.inventory_quantity
FROM product AS P
LEFT OUTER JOIN shopproduct AS SP
ON SP.product_id = P.product_id
LEFT OUTER JOIN Inventoryproduct AS IP
ON SP.product_id = IP.product_id