Datawhale SQL TASK4

第4章 集合运算

摘要：感觉不是很难，但是知识点比较零碎，自己做了两张表格，可能有不大准确的地方，欢迎指正

4.1 表的加减法

1. 表的加法-UNION,两个SELECT FROM 语句中间使用UNION

   1. 同一张表，可以使用UNION或者OR，不同的表只能使用UNION
   2. 如果想要保留重复行，采用UNION ALL语句

2. SELECT *
   FROM product AS p1
   WHERE product_id NOT IN (SELECT product_id FROM product2) -- 这儿用SELECT 语句不能用p1.product_id，这--- 儿不能写p2否者报错
   UNION
   SELECT *
   FROM product2 AS p2
   WHERE  product_id NOT IN (SELECT product_id FROM product)
   

3. 如果用UNION，两个SELECT语句各有各的WHERE,而不是两个SELECT对应一个WHERE.

4. UNION可以隐式数据类型转换，hive中join关联时，应该避免隐式数据类型转换，否则容易数据倾斜

5. 日期时间类型可以和数值、字符串、NULL兼容

6. MySQL 8.0 不支持交运算INTERSECT,用 inner join 来求得交集,或者AND

   1. SELECT p1.product_id, p1.product_name
        FROM Product p1
      INNER JOIN Product2 p2
      ON p1.product_id=p2.product_id
      

7. MySQL 8.0 不支持交运算EXCEPT ,用 NOT IN

8. 对称差=并集-交集

表格	加法		减法
集合	并	交	差	对称差
关键字	UNION	INTERSECT	EXCEPT
MySQL 8.0是否支持	支持	不支持	不支持	否
替代方案	单表用OR，多表只能UNION	INNER JOIN ON	WHERE NOT IN	NOT IN UNION NOT IN
方案简记	两个SELECT FROM 语句中间使用UNION	内连结	NOT IN	A-B UNION B-A
用法	SELECT FROM UNION SELECT FROM	两个SELECT FROM 语句中使用INNER JOIN,最后放ON
注意事项	合并后删除重复记录，不想删除用UNION ALL	也可以用对称差，并集-对称差

4.2 连结 JOIN

1. 能够简化关联子查询，关联子查询在数据量较多的时候时间消耗很大

2. 内连结，INNER JOIN

   1. FROM tab_1 
      INNER JOIN
      tab_2
      ON conditions
      

   2. 表先连结后筛选，

3. 自然连结，NATURAL JOIN ,不需要使用ON,就是一个定义表示一张表的连结

4. 外连接，LETF/RIGHT/FULL OUTER JOIN,那个连结就会保留哪个的不匹配的数据

5. 全连接

   1. 含义：对左表和右表的所有数据予以保留，能用ON关联的，就搞在一起一行显示，不能用ON 关联的就分别显示，多余的行用缺失值进行填充
   2. MySQL 8.0不支持全连接，😲，惊呆！替代方案是：左连结+右连结+UNION

6. 多表连结

   1. 一般是2-3张表，原则上无限制

7. ON 子句非等值连结

   1. SELECT column1,column2,**
      FROM 
      (
          连结语句
      )
      
      GROUP BY
      ORDER BY
      

   2. 连结语句内部，使用不等式，如果有相等的数值，可以进一步细化约束规则

8. 交叉连结

   1. CROSS JOIN，第一项每一行与第二项的每一行相乘，得出的结果意义不大
   2. 交叉连结又称为笛卡尔积

9. 多表连结，多加INNER JOIN ON 语句即可，最后才是WHERE，外连结同理。

10. 连结与集合的区别，连结一般有ON条件，集合没有

11. 在做多表查询，或者查询的时候产生新的表的时候会出现这个错误：Every derived table must have its own alias（每一个派生出来的表都必须有一个自己的别名）。

12. duplicate column name，有列重名，内连结的时候ON条件的列不需要都选出来。

内连结	外连结
ON决定筛选条件	有选择保留无法匹配到的列，左/右/全连结
SELECT FROM AS INNER JOIN AS ON	SELECT FROM AS LEFT OUTER JOIN AS ON
所有的列写在SELECT语句里面，表名.列名，公共列做桥梁，写在ON语句后面	先筛选后连结，可以避免NULL被筛掉，
可以用小括号把子查询封装再写SELECT *，最后加WHERE	不支持全连结，左右连结之后用UNION
WHERE在FROM 后执行，直接最后加WHERE即可	右连结可以用左连结+调整表的顺序
可以把WHERE条件拆开分别写在两个SELECT中

练习题

4.1

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

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

4.2

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

SELECT *
FROM (SELECT *
FROM product
UNION
SELECT *
FROM product2 ) AS bingji
WHERE product_id NOT IN (SELECT product_id FROM 
(
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))AS duichencha);

4.3

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

USE shop1;
SELECT product_id,product_type,sale_price,product_name
	,shop_id,shop_name
FROM (SELECT p.product_id,p.product_type,p.sale_price,p.product_name
	,sp.shop_id,sp.shop_name
	FROM product AS p
	INNER JOIN
	shopproduct AS sp
	ON p.product_id=sp.product_id
    )AS lianjie1		-- 内连结表格
WHERE product_id IN (
	SELECT product_id FROM 
	(SELECT product_id,MAX(sale_price),product_type
	FROM product
	GROUP BY product_type) AS t1)
ORDER BY product_type;		-- 每类价钱最高的商品的ID

4.4

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

内连结见4.3

4.5

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