datawhale-sql打卡之Task 4:集合运算

1 表的加减法

表、视图和查询的执行结果都是记录的集合, 其中的元素为表或者查询结果中的每一行
在标准 SQL 中, 分别对检索结果使用 UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算, 像UNION,INTERSECT, EXCEPT这种用来进行集合运算的运算符称为集合运算符

UNION

SELECT product_id, product_nameFROM productUNION
SELECT product_id, product_nameFROM product2;

UNION 等集合运算符通常都会除去重复的记录
使用 UNION 对两个查询结果取并集, 和在一个查询中使用 WHERE 子句, 然后使用 OR 谓词连接两个查询条件, 能够得到相同的结果

-- UNION
SELECT  product_id,product_name,product_type,sale_price,purchase_priceFROM product WHERE sale_price<800UNIONSELECT  product_id,product_name,product_type,sale_price,purchase_priceFROM product WHERE sale_price>1.5*purchase_price;

-- OR
SELECT  product_id,product_name,product_type,sale_price,purchase_priceFROM product WHERE sale_price < 800 OR sale_price > 1.5 * purchase_price;

包含重复行的集合运算可以用 UNION ALL

-- 保留重复行
SELECT product_id, product_nameFROM productUNION ALL
SELECT product_id, product_nameFROM product2;

INTERSECT

虽然集合的交运算在SQL标准中已经出现多年了, 然而很遗憾的是, MySQL 8.0 不支持交运算INTERSECT.

对于同一个表的两个查询结果而言, INTERSECT实际上可以等价地将两个查询的检索条件用AND来实现.

SELECT * FROM productWHERE sale_price > 1.5 * purchase_price AND sale_price < 1500

EXCEPT

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

找出只存在于product表但不存在于product2表的商品

-- 使用 IN 子句的实现方法
SELECT * FROM productWHERE product_id NOT IN (SELECT product_id FROM product2)

对称差

-- 使用 NOT IN 实现两个表的差集
SELECT * FROM productWHERE product_id NOT IN (SELECT product_id FROM product2)
UNION
SELECT * FROM product2WHERE product_id NOT IN (SELECT product_id FROM product)

2 表的连结

2.1 内连结(INNER JOIN)

SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.product_type,P.sale_price,SP.quantityFROM shopproduct AS SP INNER JOIN product AS PON SP.product_id = P.product_id;

• 必须使用 ON 子句来指定连结条件
• SELECT 子句中的列最好按照 表名.列名 的格式来使用
  结合where

SELECT  SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.product_type,P.sale_price,SP.quantityFROM shopproduct AS SPINNER JOIN product AS PON SP.product_id = P.product_idWHERE SP.shop_name = '东京'AND P.product_type = '衣服' ;

可以把where的条件放到on里

SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.product_type,P.sale_price,SP.quantityFROM shopproduct AS SPINNER JOIN product AS PON (SP.product_id = P.product_idAND SP.shop_name = '东京'AND P.product_type = '衣服') ;

自连结(SELF JOIN)

实际上一张表也可以与自身作连结, 这种连接称之为自连结.。需要注意, 自连结并不是区分于内连结和外连结的第三种连结, 自连结可以是外连结也可以是内连结, 它是不同于内连结外连结的另一个连结的分类方法。
找出每个商品种类当中售价高于该类商品的平均售价的商品，可以使用关联子查询来实现

SELECT product_type, product_name, sale_priceFROM product AS P1WHERE sale_price > (SELECT AVG(sale_price)FROM product AS P2WHERE P1.product_type = P2.product_typeGROUP BY product_type);

使用内连结同样可以解决这个问题
1.首先使用 GROUP BY 按商品类别分类计算每类商品的平均价格

SELECT  product_type,AVG(sale_price) AS avg_price FROM product GROUP BY product_type;

2.将上述查询与表 product 按照 product_type (商品种类)进行内连结

SELECT  P1.product_id,P1.product_name,P1.product_type,P1.sale_price,P2.avg_priceFROM 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;

3.增加 WHERE 子句, 找出那些售价高于该类商品平均价格的商品

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

自然连结(NATURAL JOIN)

当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件.
SELECT * FROM shopproduct NATURAL JOIN product
与上述自然连结等价的内连结

SELECT  SP.product_id,SP.shop_id,SP.shop_name,SP.quantity,P.product_name,P.product_type,P.sale_price,P.purchase_price,P.regist_date  FROM shopproduct AS SP INNER JOIN product AS P ON SP.product_id = P.product_id

2.2 外连结(OUTER JOIN)

内连结会丢弃两张表中不满足 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 子句匹配到的行, 此时对应右表的行均为缺失值
右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值
全外连结则会同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充

2.3 多表连接

• 内连接

SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.sale_price,IP.inventory_quantityFROM shopproduct AS SPINNER JOIN product AS PON SP.product_id = P.product_idINNER JOIN Inventoryproduct AS IPON SP.product_id = IP.product_idWHERE IP.inventory_id = 'P001';

• 外连接

SELECT P.product_id,P.product_name,P.sale_price,SP.shop_id,SP.shop_name,IP.inventory_quantityFROM product AS PLEFT OUTER JOIN shopproduct AS SP
ON SP.product_id = P.product_id
LEFT OUTER JOIN Inventoryproduct AS IP
ON SP.product_id = IP.product_id;

2.4 非等值连结

除了使用相等判断的等值连结, 也可以使用比较运算符来进行连接. 实际上, 包括比较运算符(<,<=,>,>=,
BETWEEN)和谓词运算(LIKE, IN, NOT 等等)在内的所有的逻辑运算都可以放在 ON 子句内作为连结条件

SELECT  product_id,product_name,sale_price,COUNT(p2_id) AS rankFROM (--使用自左连结对每种商品找出价格不低于它的商品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 XGROUP BY product_id, product_name, sale_priceORDER BY rank; 

试修改上述查询使得二者的排名均为第六

SELECT  product_id,product_name,sale_price,COUNT(p2_id)+1  AS rankFROM (--使用自左连结对每种商品找出价格不低于它的商品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 XGROUP BY product_id, product_name, sale_priceORDER BY rank; 

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

无论是外连结内连结, 一个共同的必备条件就是连结条件–ON 子句, 用来指定连结的条件. 如果你试过不使用这个连结条件的连结查询,
你可能已经发现, 结果会有很多行. 在连结去掉 ON 子句, 就是所谓的交叉连结(CROSS JOIN)

-- 1.使用关键字 CROSS JOIN 显式地进行交叉连结
SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.sale_priceFROM shopproduct AS SPCROSS JOIN product AS P;
--2.使用逗号分隔两个表,并省略 ON 子句
SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.sale_priceFROM shopproduct AS SP , product AS P;

交叉连结是对两张表中的全部记录进行交叉组合,因此结果中的记录数通常是两张表中行数的乘积.本例中,因为 shopproduct 表存在 13 条记录,product 表存在 8 条记录,所以结果中就包含了 13 × 8 = 104 条记录.

---

Task 04:集合运算