Task 05 集合运算
Content
- 表的加减法
- 连结(JOIN)
- 练习
Reference：
[1]GitHub - datawhalechina/wonderful-sql: Follow me，从 0 到 1 掌握 SQL，决胜秋招。
1. 表的加减法
1.1 集合运算符
在标准 SQL 中, 可以分别对<表、检索结果>使用：
- UNION — 并
- INTERSECT — 交
-
EXCEPT — 差
但MySQL截止至8.0，只支持UNION语句
该文氏图展示了几种集合的基本运算.png
1.2 UNION
-- 示例1
SELECT product_id, product_name
FROM product
UNION
SELECT product_id, product_name
FROM product2;
-- 示例2
SELECT product_id,product_name,product_type,sale_price,purchase_price
FROM product
WHERE sale_price<800
UNION
SELECT product_id,product_name,product_type,sale_price,purchase_price
FROM product
WHERE sale_price>1.5*purchase_price;
相关说明
- UNION 等集合运算符通常都会除去重复的记录，若想保留，改为UNION ALL即可
- UNION不仅可以对不同的两张表进行求并集运算. 对于同一张表, 也可以进行求并集的，相当于作为一个表的附加筛选条件，与WHERE+OR的效果类似
- 但WHERE+OR无法合并两张表的查询结果，UNION可以
- 有时候用UNION更直观，查询效率更高
- 有时候, 即使数据类型不完全相同, 也会通过隐式类型转换来将两个类型不同的列放在一列里显示（时间日期类型和字符串, 数值以及缺失值均能兼容）
-- 示例3
SELECT SYSDATE(), SYSDATE(), SYSDATE()
UNION
SELECT ’chars’, 123, null;
1.3 INTERSECT
- 对于同一个表的两个查询结果而言, 他们的交 INTERSECT 实际上可以等价地将两个查询的检索条件用AND 谓词连接来实现。
- 对于两张表的交INTERSECT，在 MySQL 8.0 里无法直接得到
1.4 EXCEPT
- MySQL 8.0 还不支持表的减法运算符 EXCEPT
- 但是NOT IN 谓词, 我们同样可以实现表的减法，其效果和 SQL 标准语法中的EXCEPT 运算相同
1.5 对称差
- 两个集合 A,B 的对称差是指那些仅属于 A 或仅属于 B 的元素构成的集合
- 从直观上就能看出来, 两个集合的对称差等于 A-B 并上 B-A
- 抑或可以理解为A与B的UNION减去INTERSECT
-
同样，两个集合的交INTERSECT可以看作是两个集合的并UNION去掉两个集合的对称差
总而言之围绕Venn图.png
2. 连结(JOIN)
连结 (JOIN) 就是使用某种关联条件 (一般是使用相等判断谓词"="), 将其他表中的列添加过来, 进行“添加列”的集合运算.
- 集合运算的特征是以行方向为单位进行操作
- 使用关联子查询也可以从其他表获取信息, 但 连结 更适合从多张表获取信息
2.1 交叉连结/笛卡尔积(CROSS JOIN)
-- 示例1
SELECT SP.*, P.*
FROM shopproduct 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
shopproduct AS SP
CROSS JOIN
product AS P;
相关说明
- 在横向上对表进行扩张, 即增加新的列，但没有了 ON 子句的限制, 会对左表和右表的每一行进行组合, 这经常会导致很多无意义的行出现在检索结果中
- 交叉连结是对两张表中的全部记录进行交叉组合, 因此结果中的记录数通常是两张表中行数的乘积
- 交叉连结没有应用到实际业务之中的原因有两个。一是其结果没有实用价值, 二是由于其结果行数太多, 需要花费大量的运算时间和高性能设备的支持。
- 但是交叉连结可以用来快速产生非常大的（无意义的）表，可以给面试官露一手（bushi
- 内连结是交叉连结的一部分,“内”也可以理解为“包含在交叉连结结果中的部分”. 相反, 外连结的“外”可以理解为“交叉连结结果之外的部分”
2.2 内连接(INNER JOIN)
-- 示例1
SELECT
SP.shop_id,SP.shop_name,SP.product_id,
P.product_name,P.product_type,P.sale_price,SP.quantity
FROM
shopproduct AS SP
INNER JOIN
product AS P
ON SP.product_id = P.product_id; #指定连结条件
相关说明
- 进行连结时需要在 FROM 子句中使用多张表
- 必须使用 ON 子句来指定连结条件
- SELECT 子句中的列最好按照表名. 列名的格式来使用
2.2.1 结合 WHERE 子句使用内连结
SELECT
SP.shop_id,SP.shop_name,SP.product_id,
P.product_name,P.product_type,P.sale_price,SP.quantity
FROM
shopproduct AS SP
INNER JOIN
product AS P
ON SP.product_id = P.product_id
WHERE
SP.shop_name = ’东京’
AND
P.product_type = ’衣服’ ;
- WHERE 子句将在 FROM 子句之后执行, 也就是说, 在做完 INNER JOIN ... ON得到一个新表后, 才会执行 WHERE 子句
- 查询的执行顺序: FROM 子句->WHERE 子句->SELECT 子句
- 还可以将 WHERE 子句中的条件直接添加在 ON 子句中, 这时候 ON 子句后最好用括号将连结条件和筛选条件括起来，但这样子不太方便阅读，一般不建议
- 或者先分别在两张表里做筛选, 把复杂的筛选条件按表分拆, 然后把筛选结果 (作为表) 连接起来
-- 示例
SELECT SP.shop_id
- ,SP.shop_name
- ,SP.product_id
- ,P.product_name
- ,P.product_type
- ,P.sale_price
- ,SP.quantity
- FROM (-- 子查询 1:从shopproduct 表筛选出东京商店的信息
- SELECT *
- FROMshopproduct
- WHERE shop_name = ’东京’ ) AS SP
- INNER JOIN -- 子查询 2:从 product 表筛选出衣服类商品的信息
- (SELECT *
- FROMproduct
- WHERE product_type = ’衣服’) AS P
- ON SP.product_id = P.product_id;
2.2.2 结合 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
相关说明
- 结合 GROUP BY 子句使用内连结, 需要根据分组列位于哪个表区别对待
2.2.3 内连结与关联子查询
-- 示例
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;
2.2.4 自然连结 (NATURAL JOIN)
- 当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件
- 把两个表的公共列 (可以有多个公共列) 放在第一列, 然后按照两个表的顺序和表中列的顺序, 将两个表中的其他列都罗列出来
2.3 外连接(OUTER JOIN)
- 内连结是交叉连结的一部分,“内”也可以理解为“包含在交叉连结结果中的部分”. 相反, 外连结的“外”可以理解为“交叉连结结果之外的部分”
- 外连结会根据外连结的种类有选择地保留无法匹配到的行。
- 按照保留的行位于哪张表, 外连结有三种形式: 左连结, 右连结和全外连结。
-- 左连结
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)>
- 选取出单张表中全部的信息：对于外连结来说, 只要数据存在于某一张表当中, 就能够读取出来. 在实际的业务中, 例如想要生成固定行数的单据时, 就需要使用外连结
- 使用 LEFT、RIGHT 来指定主表：顾名思义, 使用 LEFT 时 FROM 子句中写在左侧的表是主表, 使用 RIGHT 时右侧的表是主表,通过交换两个表的顺序, 同时将 LEFT 更换为 RIGHT(如果原先是 RIGHT, 则更换为 LEFT), 两种方式会到完全相同的结果。
3. 练习
- 找出 product 和 product2 中售价高于 500 的商品的基本信息
SELECT * FROM product
WHERE sale_price>500
UNION # UNION ALL才是不删除重复行
SELECT * FROM product2
WHERE sale_price>500;
- 借助对称差的实现方式, 求 product 和 product2 的交集
-- 方法一：最直观
SELECT * FROM product
WHERE product_id in (SELECT product_id FROM product2)
-- 方法二：A+B-[(A-B)+(B-A)]
SELECT * FROM
(SELECT * FROM product UNION SELECT * FROM product2)
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));
- 每类商品中售价最高的商品都在哪些商店有售？
SELECT SP.shop_id, SP.shop_name, P.product_id, P.product_name, p.product_type
FROM shopproduct AS SP
INNER JOIN
(SELECT product_id, product_name,product_type, MAX(sale_price) FROM product
GROUP BY product_type) AS P
ON SP.product_id = P.product_id;
- 分别使用内连结和关联子查询每一类商品中售价最高的商品
-- 内连结
SELECT P.product_id,P.product_name,P.product_type,P.sale_price
FROM product AS P
INNER JOIN
(SELECT product_type,MAX(sale_price)AS maxp FROM product GROUP BY product_type)AS MP
ON (mp.product_type =P.product_type AND P.sale_price=MP.maxp);
-- 关联子查询
SELECT P.product_id,P.product_name,P.product_type,P.sale_price
FROM product AS P
WHERE sale_price=(
SELECT MAX(sale_price) FROM product AS MP
WHERE P.product_id=MP.product_id
GROUP BY product_type);
- 用关联子查询实现：在 product 表中，取出 product_id,product_name, sale_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和
