【Datawhale202208】SQL编程-Task 04

• ch04: 集合运算

1. 表的加法-union【SELECT product_id, product_name FROM product
    UNION SELECT product_id, product_nameFROM product2;】
2. UNION 等集合运算符通常都会除去重复的记录，可以用UNION ALL保留重复的记录。
3. 对于同一个表的两个不同的筛选结果集, 使用 UNION 对两个结果集取并集, 和把两个子查询的筛选条件用 OR 谓词连接, 会得到相同的结果, 但倘若要将两个不同的表中的结果合并在一起, 就不得不使用 UNION。
4. 即使数据类型不完全相同, 也会通过隐式类型转换来将两个类型不同的列放在一列里显示；需要注意的是 hive中进行join关联时，关联列要避免使用隐式数据类型转换，否则容易导致数据倾斜。
5. 集合的交运算- intersect【MySQL用inner join】
6. 对于同一个表的两个查询结果而言, 他们的交INTERSECT实际上可以等价地将两个查询的检索条件用AND谓词连接来实现。
7. 减法运算符-except【MySQL用not in子句】
8. 使用 NOT IN 谓词, 基本上可以实现和SQL标准语法中的EXCEPT运算相同的效果。
9. 对称差-指那些仅属于A或仅属于B的元素构成的集合。首先使用UNION求两个表的并集, 然后使用INTERSECT求两个表的交集, 然后用并集减去交集, 就得到了对称差。
10. 连结(JOIN)就是使用某种关联条件(一般是使用相等判断谓词"="), 将其他表中的列添加过来, 进行“添加列”的集合运算。
11. 内连结【inner join】【FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>】
12. 使用内连结注意的要点【进行连结时需要在 FROM 子句中使用多张表；必须使用 ON 子句来指定连结条件；SELECT 子句中的列最好按照 表名.列名 的格式来使用】
13. 如果需要在使用内连结的时候同时使用 WHERE 子句对检索结果进行筛选, 则需要把 WHERE 子句写在 ON 子句的后边。
14. 结合 GROUP BY 子句使用内连结, 需要根据分组列位于哪个表区别对待。
15. 自连结【self join】一张表也可以与自身作连结。
16. 自然连结【natural join】内连结的一种特例，当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件。【SELECT *  FROM shopproduct NATURAL JOIN Product】使用自然连结还可以求出两张表或子查询的公共部分。
17. 外连结【outer join】【按照保留的行位于哪张表,外连结有三种形式: 左连结, 右连结和全外连结。】
18. 左连结【 FROM <tb_1> LEFT OUTER JOIN <tb_2> ON <condition(s)>】
19. 右连结【 FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>】
20. 全外连结【 FROM <tb_1> FULL OUTER JOIN <tb_2> ON <condition(s)>】
21. 使用外连结注意的要点【选取出单张表中全部的信息；使用 LEFT、RIGHT 来指定主表】
22. MySQL8.0 目前还不支持全外连结, 不过我们可以对左连结和右连结的结果进行 UNION 来实现全外连结。

23. 非等值自左连结(SELF JOIN)

24. 交叉连结—— CROSS JOIN(笛卡尔积)

练习题

4.1

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

4.2

/*
先求出对称差集
再通过not in 子句选出不在不在对称差集里的数据
*/

select *
from product
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

SELECT SP.shop_id, SP.shop_name, SP.product_id 
FROM shop_product AS SP
WHERE SP.product_id IN
(SELECT p1.product_id 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);

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;

关联子查询

SELECT p1.product_id, p1.product_name, p1.product_type, p1.sale_price
FROM product AS p1
WHERE p1.sale_price = (SELECT MAX(sale_price) AS max_sale_price
											 FROM product AS p2
											 WHERE p1.product_type = p2.product_type
											 GROUP BY product_type);

4.5

select product_id , product_name , sale_price , (
select sum(sale_price) from product p2 
where p.sale_price >p2.sale_price or (p.sale_price =p2.sale_price and p.product_id<=p2.product_id)
)as cum_sale_price
from product p 
order by sale_price , cum_sale_price ;