MySQL- 集合运算

1.表的加减法

	1)集合运算
		UNION, intersect, except 集合运算符
		交集，差集，并集，子集	
		
	2）表的加法  -  UNION
		UNION 将两个表合并， 并去除重复记录
		
		UNION 与 OR
		在同一个表中，UNION 语句 也可以用 OR 实现
		不同表中，只能使用 UNION

SELECT * 
FROM product
WHERE sale_price / purchase_price < 1.3
OR sale_price / purchase_price IS NULL;

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

		UNION 默认去重, 添加 ALL 可不去重

SELECT product_id, product_name, product_type, purchase_price, regist_date
FROM product
WHERE sale_price < 1.5 * purchase_price
UNION ALL
SELECT product_id, product_name, product_type, purchase_price, regist_date
FROM product
WHERE sale_price < 1000;

		隐式数据类型转换
		数据不完全相同时，通过隐式数据转换
		将两个不同的列放在一起

-- 时间日期类型和字符串,数值以及缺失值均能兼容
SELECT SYSDATE(), SYSDATE(), SYSDATE()
UNION
SELECT 'chars', 123, null;

	3）MySQL 8.0 不支持 交运算 INTERSECT 
		可以通过 INNER JOIN 来求交集
	
	4) 差集，补集，表的减法
	
		减法，except, 还不支持
		可以使用 NOT IN 实现
		SELECT * 
		FROM Product
		WHERE product_id NOT IN 
		(SELECT product_id FROM Product2)

SELECT product_id, product_name, sale_price, purchase_price, regist_date
FROM product
WHERE sale_price > 2000 
AND
product_id NOT IN
(SELECT product_id FROM product 
WHERE
sale_price >= purchase_price * 1.3 );

		AND 与 INTERSECT 
		
		对称差
		两个集合A,B的对称差，
		是指那些仅属于A或仅属于B的元素构成的集合
		首先使用UNION求两个表的并集,
		然后使用INTERSECT求两个表的交集,
		然后用并集减去交集, 就得到了对称差
		SELECT * 
		FROM Product
		WHERE product_id NOT IN (SELECT product_id 
		FROM Product2)
		仅 product 的元素
		UNION
		SELECT * 
		FROM Product2 
		WHERE product_id NOT IN (SELECT product_id 
		FROM Product)
		仅 product2 的元素

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

2.连结（JOIN）

	添加列， 将其他表中的列添加到 一张表中
	
	1）内连结 INNER JOIN
	FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>
	
		内连结从两个表获取信息
		注：
		FROM 子句中使用多张表
		使用 ON 指定连结 条件，必不可少
		SELECT 最好按照 表名， 列名 格式

-- 内连结从两个表获取信息系
SELECT sp.shop_id
, sp.shop_name
, sp.product_id
, 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;

		结合 WHERE 使用内连结
		WHERE 语句需要写在 ON 子句后面
		执行顺序
		FROM 子句->WHERE 子句->SELECT 子句

SELECT *
FROM
(SELECT sp.shop_id
, sp.shop_name
, sp.product_id
, 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) AS sep1
WHERE shop_name = '东京'
AND product_type = '衣服';
-- WHERE 子句在 FROM 子句 执行之后 执行
-- 即 在内连结中，INNER JOIN 得到新的表后才执行 WHERE

SELECT p1.shop_id
, p1.shop_name
, p1.product_id
, p.product_type
, p.sale_price
, p1.quantity
FROM shop_product AS p1
INNER JOIN product AS p
ON p1.product_id = p.product_id
WHERE p1.shop_name = '东京'
AND p.product_type = '衣服';

		结合 GROUP BY 使用内连结
		需要根据分组区别对待
		可以再内连结之前使用 GROUP BY
		分组列和被聚合列 不在同一张表，且二者都为被用于
		连接两张表，只能先连结，再聚合

SELECT sp.shop_id
, sp.shop_name
, MAX(p.sale_price) AS max_price
FROM shop_product AS sp
INNER JOIN product AS p
ON sp.product_id = p.product_id
GROUP BY sp.shop_id, sp.shop_name;

		自连结
		将一张表与自身连结
		自连结可以是内连结也可以是外连结
	
		内连结 与 关联子查询

-- 先 分类计算 平均价格
SELECT product_type
, AVG(sale_price) AS avg_price
FROM product 
GROUP BY product_type;
-- 将平均价格与 原表 连结
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 条件
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;

		自然连结
		内连结一种特例
		两个表自然连结时，按照表中包含的列名等值内连结
		不需ON 指定条件
		SELECT *  FROM shopproduct NATURAL JOIN Product
	
		
		
	2） 外连结
	根据外连结种类有选择地保留无法匹配的行
	内连结只能取出 同时存在于两张表的数据
	外连结，只要在一张表中就可以取出
	使用 LEFT 时 FROM 子句中写在左侧的表是主表,	
	使用 RIGHT 时右侧的表是主表
	
	左连结保存左表中无法用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)>
	
	左连结 与 右连结
	由于连结时可以交换左表和右表的位置,
	因此左连结和右连结并没有本质区别

SELECT sp.shop_id
, sp.shop_name
, sp.product_id
, p.product_name
, p.sale_price
FROM product AS p
LEFT OUTER JOIN shop_product AS sp
ON sp.product_id = p.product_id;

-- 左连结 使用 WHERE

SELECT p.product_id
, p.product_name
, p.sale_price
, sp.shop_id
, sp.shop_name
, sp.quantity
FROM shop_product AS sp
RIGHT OUTER JOIN product AS p
ON sp.product_id = p.product_id
WHERE sp.quantity < 50;

SELECT p.product_id
, p.product_name
, p.sale_price
, sp.shop_id
, sp.shop_name
, sp.quantity
FROM product AS p
LEFT OUTER JOIN (
SELECT * 
FROM shop_product
WHERE quantity < 50) AS sp
ON sp.product_id = p.product_id;

3.多表连结

	1）多表内连结
			INNER JOIN

SELECT sp.shop_id
, sp.shop_name
, sp.product_id
, p.product_name
, p.sale_price
, ip.inventory_quantity
FROM
shop_product 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';

	2) 多表外连结
	
			OUTER JOIN

-- 多表外连结
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  shop_product AS sp
ON sp.product_id = p.product_id
LEFT OUTER JOIN InventoryProduct AS ip
ON sp.product_id = ip.product_id;

	4) ON  子句进阶 -- 非等值连接

		
		
	5) 交叉连结 --- 笛卡尔积
	去掉 ON 子句
	笛卡尔积：用集合 A 中的元素和 集合B中 元素
	组成一个有序的组合
	交叉笛卡尔积 是在横向对 表进行扩张，即增加新的列
	功能和连结一致
	没有ON 的限制，可能会出现一些无意义的检索结果

-- 交叉连结
-- 对两张表的数据进行 行 交叉组合，新的组合 行数会增多
-- 1
SELECT sp.shop_id
, sp.shop_name
, sp.product_id
, p.product_name
, p.sale_price
FROM shop_product AS sp
CROSS JOIN product AS p;

-- 2
SELECT sp.shop_id
, sp.shop_name
, sp.product_id
, p.product_name
, p.sale_price
FROM shop_product AS sp, product AS p;

	连结与笛卡尔积
	笛卡尔积可以看做一种特定连结

SELECT sp.*, p.*
FROM shop_product AS sp
CROSS JOIN product AS p;

-- 笛卡尔积 增加条件可以 变为内连结
SELECT sp.*, p.*
FROM shop_product AS sp
CROSS JOIN product AS p
WHERE sp.product_id = p.product_id;

SELECT sp.*, p.*
FROM shop_product AS sp
INNER JOIN product AS p
ON sp.product_id = p.product_id;

	6）连结的语法

-- 过时语法
-- 缺点： 
-- 不能判断是内连结 还是外连结
-- 连结条件在 WHERE 子句中，无法分辨连结条件
-- 可能会被替代
SELECT sp.shop_id
, sp.shop_name
, sp.product_id
, p.product_name
, p.sale_price
FROM shop_product AS sp
, product AS p
WHERE sp.product_id = p.product_id
AND sp.shop_id = '000A';