Task04-集合运算-CSDN博客

本文链接：https://blog.csdn.net/weixin_52078689/article/details/132002792

本文介绍了SQL中的集合运算，包括UNION用于并集，INTERSECT（在MySQL中用INNERJOIN替代）用于交集，以及如何通过NOTIN或子查询实现差集。还详细讲解了内连接(INNERJOIN)的使用，包括如何结合WHERE子句进行筛选，并给出了多个示例来展示这些操作的应用。

摘要由CSDN通过智能技术生成

4.1 表的加减法

4.1.1 集合运算概念

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-05P2zSaW-1690656817282)(C:\Users\a\Desktop\MySQL\example.png)]

4.1.2 表的加法–`UNION`

UNION:求并集运算. 对于同一张表, 实际上也是可以进行求并集的.UNION 等集合运算符通常都会除去重复的记录

4.1.2.2 `UNION` 与 `OR` 谓词

对于同一个表的两个不同的筛选结果集, 使用 UNION 对两个结果集取并集, 和把两个子查询的筛选条件用 OR 谓词连接, 会得到相同的结果, 但倘若要将两个不同的表中的结果合并在一起, 就不得不使用 UNION 了。而且, 即便是对于同一张表, 有时也会出于查询效率方面的因素来使用 UNION。

OR相当于“或者”，类似数学中的取并集。

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

UNION 的结果中保留重复行的语法在 UNION 后面添加 ALL 关键字:UNION ALL

4.1.3 MySQL 8.0 不支持交运算`INTERSECT`,用 `inner join` 来求得交集

4.1.4 差集,补集与表的减法

4.1.4.1 MySQL 8.0 不支持 `EXCEPT` 运算，使用`NOT IN` 谓词, 实现表的减法

4.1.4.2 `INTERSECT` 与 `AND` 谓词

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

例：使用AND谓词查找product表中利润率高于50%,并且售价低于1500的商品

SELECT * 
  FROM Product
 WHERE sale_price > 1.5 * purchase_price 
   AND sale_price < 1500

4.1.5 对称差

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

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

-- 使用 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)

4.2 连结(JOIN)

4.2.1 内连结(INNER JOIN)

语法格式是:

-- 内连结
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>

其中 INNER关键词表示使用了内连结.

要点一: 进行连结时需要在 FROM 子句中使用多张表.
要点二:必须使用 ON 子句来指定连结条件.
要点三: SELECT 子句中的列最好按照表名.列名的格式来使用。

4.2.1.2 结合 WHERE 子句使用内连结

如果需要在使用内连结的时候同时使用 WHERE 子句对检索结果进行筛选, 则需要把 WHERE 子句写在 ON 子句的后边。

shop_product表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nBxSyTWD-1690656817283)(C:\Users\a\Desktop\MySQL\ch03.04view4.png)]

第一种增加 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 = '衣服' ;

此外, 一种不是很常见的做法是,还可以将 WHERE 子句中的条件直接添加在 ON 子句中, 这时候 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
   AND SP.shop_name = '东京'
   AND P.product_type = '衣服') ;

练习所使用数据：

新建product2表

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

向表中插入数据，可以借助如下数据：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zB7ITshM-1690656817284)(C:\Users\a\Desktop\MySQL\7-30.png)]

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', '手套', '衣服', 3000, 2800, '2009-09-20');
INSERT INTO product2 VALUES('0010', '水壶', '厨房用具', 6800, 5000, '2009-01-15');

结果：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AiFPvtqR-1690656817285)(C:\Users\a\Desktop\MySQL\7-30-1.png)]

练习题

4.1

找出 product 和 product2 中售价高于 500 的商品的基本信息。

代码

SELECT * FROM product WHERE ` sale_price` > 500
UNION
SELECT * FROM product2 WHERE ` sale_price` > 500;

结果

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZalBZ6An-1690656817285)(C:\Users\a\Desktop\MySQL\r1.png)]

4.2

借助对称差的实现方式, 求product和product2的交集。

代码

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);

结果

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dHFDQh9I-1690656817285)(C:\Users\a\Desktop\MySQL\r2.png)]

4.3

每类商品中售价最高的商品都在哪些商店有售？

代码

结果

4.4

分别使用内连结和关联子查询每一类商品中售价最高的商品。

代码

内连结

SELECT p1.product_id, p1.` product_name`, p1.` product_type`, p1.` sale_price`, p2.max_sale_price
FROM product AS p1
INNER JOIN 
    (SELECT ` product_type`, MAX(` sale_price`) AS max_sale_price
    FROM product
    GROUP BY ` product_type`) AS p2
ON p1.` product_type` = p2.` product_type`
AND p1.` sale_price` = p2.max_sale_price;

关联子查询

结果

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yHEUsiKD-1690656817286)(C:\Users\a\Desktop\MySQL\r4.png)]

4.5

用关联子查询实现：在 product 表中，取出 product_id, product_name, sale_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。

代码

select product_id, ` product_name`, ` sale_price`, 
    (
        select sum(` sale_price`)
        from product p2
        where p2.` sale_price` <= p1.` sale_price`
    ) sum
from product p1
order by ` sale_price`;