SQL编程task04作业-集合运算

1 学习内容

DataWhale SQL组队学习

2 表的加减法

什么是集合运算？
集合在数学领域表示“各种各样的事物的总和”, 在数据库领域表示记录的集合。具体来说,表、视图和查询的执行结果都是记录的集合, 其中的元素为表或者查询结果中的每一行. 在标准 SQL 中, 分别对检索结果使用UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算, 像UNION,INTERSECT,EXCEPT这种用来进行集合运算的运算符称为集合运算符.

2.1 表的加法UNION

2.1.1 UNION

SQL语句：（表示两个集合的并）

SELECT product_id, product_name
  FROM Product
 UNION
SELECT product_id, product_name
  FROM Product2;

注：UNION 等集合运算符通常都会除去重复的记录

2.1.2 UNION 与 OR 谓词

SQL语句：（两者结果一致）

-- 使用 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;
**

2.1.3 UNION ALL

包含重复行的集合运算，在UNION后加ALL即可

2.2 MySQL 8.0 不支持交运算INTERSECT

2.3 差集,补集与表的减法

2.3.1 MySQL 8.0 还不支持 EXCEPT 运算

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

只存在于Product表但不存在于Product2表：

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

2.3.2 INTERSECT 与 AND 谓词

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

2.4 对称差

两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合。两个集合的交就可以看作是两个集合的并去掉两个集合的对称差。
但由于在MySQL8.0 里，由于两个表或查询结果的并不能直接求出来。因此并不适合使用上述思路来求对称差。好在还有差集运算可以使用。从直观上就能看出来，两个集合的对称差等于 A-B并上B-A，因此实践中可以用这个思路来求对称差。

使用Product表和Product2表的对称差来查询哪些商品只在其中一张表
SQL语句：

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

3 连结(JOIN)

时间问题，语法规则部分暂不做整理，后续补充。
详情参考：
DataWhale SQL组队学习
MySQL- -集合运算

4 练习题

4.1

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

select * 
from product
where sale_price > 500
union
select * 
from product2
where sale_price > 500;

运行结果：

4.2

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

select * 
from (select * from product 
			union 
			select * from product2) as p
where product_id not in
			(SELECT product_id FROM product
			 WHERE product_id NOT IN (SELECT product_id FROM product2)
			 UNION
       		 SELECT product_id FROM product2
			 WHERE product_id NOT IN (SELECT product_id FROM product));

运行结果：

4.3

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

select p1.shop_id,p1.shop_name,p1.quantity,
			 p2.product_id,p2.product_name,p2.product_type,p2.sale_price,
			 mp.max_price as '该类商品中售价最高为'
from shopproduct as p1
inner join product as p2
	on p1.product_id = p2.product_id
inner join (select product_type ,max(sale_price)as max_price
						from product
						group by product_type
						)as mp
on mp.product_type = p2.product_type
and p2.sale_price = mp.max_price;

运行结果：

4.4

分别使用内连结和关联子查询每一类商品中售价最高的商品。
内连结SQL语句：（与上题一致）

select p.product_id,p.product_id,p.product_type,p.sale_price,
			 mp.max_price as '该类商品最大价格'
from product as p
inner join(select product_type,max(sale_price) as max_price
					 from product
					 group by product_type)as mp
on p.product_type = mp.product_type
and p.sale_price = mp.max_price;		 

运行结果：

关联子查询SQL语句：

select p.product_id,p.product_type,p.sale_price,
			 mp.max_price as '该类商品最大价格'
from product as p,
		 (select product_type,max(sale_price) as max_price
		  from product
			group by product_type) as mp
where p.product_type = mp.product_type
and p.sale_price = mp.max_price;

4.5

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

SELECT	p.product_id, p.product_name, p.sale_price,
				(select sum(sale_price) from product as p1
				 where p.sale_price > p1.sale_price
				 or(p.sale_price=p1.sale_price)
				 )as '累计求和'
from product as p
order by sale_price;   

运行结果：

习题参考：
天池龙珠SQL训练营日常 task4 打卡