SQL学习|集合运算

表的加法–UNION

union的简单使用，将两个查询的结果加起来：

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

• UNION会自动去除两个查询结果中的重复数据。
• 想要让UNION不自动去除重复数据，可以使用UNION ALL
• 在同一个表中查询时，使用or往往能取得和使用union相同的效果，但是有时候为了效率考虑会使用union。当涉及到两个表的内容时就不得不使用UNION了。
• 使用UNION查询时一般会把相同类型的列放到一起，但是如果是不同类型，也可以通过隐式类型转换将两个不同类型的列放在一列显示。

交运算–INTERSECT

mysql 8.0 不支持交运算。要求交运算可以使用inner join 进行联结。
交运算也可以使用AND来实现。

差集,补集与表的减法

标准SQL中表的减法运算符 为EXCEPT，但是mysql8.0同样不支持。可以使用not in 实现except同样的效果：

-- 使用 NOT IN 子句的实现存在于Product 但不在Product2 表中的记录
SELECT
	* 
FROM
	Product 
WHERE
	product_id NOT IN ( SELECT product_id   FROM Product2 )

对称差

两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合.

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

联结

内联结

通过内连接查将商店信息与商品信息联结起来：

SELECT
	sp.shop_id,
	sp.shop_name,
	sp.product_id,
	p.product_name,
	p.product_type,
	p.sale_price,
	sp.quantity   
FROM
	shop_product AS sp  
	INNER JOIN product AS p ON sp.product_id = p.product_id;

使用子查询的方式得到东京商店里, 售价低于 2000 的商品信息：

SELECT
	sp.shop_id,
	sp.shop_name,
	sp.product_id,
	sp.quantity,
	p.product_id,
	p.product_name,
	p.product_type,
	p.sale_price 
FROM
	shop_product AS sp
	INNER JOIN ( SELECT product_id, product_name, product_type, sale_price FROM product WHERE sale_price < 2000 ) AS p ON sp.product_id = p.product_id 
WHERE
	sp.shop_name = '东京';

自联结

一张表自己与自己联结就是自联结。
如下，找出售价高于该类商品平均价格的商品

SELECT
	P1.product_id,
	P1.product_name,
	P1.product_type,
	P1.sale_price,
	P2.avg_price 
FROM
	product AS P1
	INNER JOIN ( SELECT product_type, AVG( sale_price ) AS avg_price FROM product GROUP BY product_type ) AS P2 ON P1.product_type = P2.product_type 
WHERE
	P1.sale_price > P2.avg_price;

自然联结

自然联结按照两个表中都包含的列名来进行等值内连结。

SELECT
	* 
FROM
	shop_product
	NATURAL JOIN product

上述语句查询出两个表中所有的列。两个表的公共列放在最前面。

外联结

按照保留的行位于哪张表,外连结有三种形式: 左连结, 右连结和全外连结。

左连结会保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值; 右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值; 而全外连结则会同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充。

三种外连结的对应语法分别为:

-- 左连结 
FROM <tb_1> LEFT  OUTER JOIN <tb_2> ON <condition(s)>
-- 右连结 
FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>
-- 全外连结
FROM <tb_1> FULL  OUTER JOIN <tb_2> ON <condition(s)>

非等值联结

on字句的联结条件不一定都是=， 包括比较运算符(<,<=,>,>=, BETWEEN)和谓词运算(LIKE, IN, NOT 等等)在内的所有的逻辑运算都可以放在 ON 子句内作为连结条件。
对 Product 表中的商品按照售价赋予排名：

SELECT
	product_id,
	product_name,
	sale_price,
	COUNT( p2_id ) AS my_rank 
FROM
	(
	SELECT
		P1.product_id,
		P1.product_name,
		P1.sale_price,
		P2.product_id AS P2_id,
		P2.product_name AS P2_name,
		P2.sale_price AS P2_price 
	FROM
		Product AS P1
		LEFT OUTER JOIN Product AS P2 ON P1.sale_price <= P2.sale_price 
	) AS X 
GROUP BY
	product_id,
	product_name,
	sale_price 
ORDER BY
	my_rank;

结果如下：

其中子查询的作用是对每一种商品,找出售价不低于它的所有商品，结果如下：

子查询中用到的就是非等值联结，查询出这个商品所有比他售价高的商品，这个数量就是它的排名了。

交叉联结–CROSS JOIN(笛卡尔积)

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
  FROM ShopProduct AS SP
 CROSS JOIN Product AS P;

使用cross join得到两张表的笛卡尔积。

作业

4.1

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

4.2

SELECT
	* 
FROM
	product 
WHERE
	product_id IN ( SELECT product_id FROM product2 )

4.3

SELECT
	sp.product_id,
	sp.shop_name,
	p.product_name
FROM
	shop_product AS sp
	INNER JOIN (
	SELECT
		product_id, product_name
	FROM
		product AS p1 
	WHERE
		sale_price =(
		SELECT
			max( sale_price ) 
		FROM
			product AS p2 
		WHERE
			p1.product_type = p2.product_type 
		GROUP BY
		product_type 
	)) AS p ON sp.product_id = p.product_id;

使用关联子查询先查找出每个种类中售价最高的商品id，然后使用id作为联结条件与shop_product表联结。

4.4

-- 内联结方式
SELECT
	p.product_type,
	p.product_name,
	mp.max_price 
FROM
	product 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 );

-- 关联子查询方式
SELECT
	product_id,
	product_name,
	product_type
FROM
	product AS p1 
WHERE
	sale_price =(
	SELECT
		max( sale_price ) 
	FROM
		product AS p2 
	WHERE
		p1.product_type = p2.product_type 
GROUP BY
	product_type)

4.5

参考资料：
[1] https://github.com/datawhalechina/wonderful-sql/blob/main/ch04:%20%E9%9B%86%E5%90%88%E8%BF%90%E7%AE%97.md