SQL学习笔记04

Task 05 集合运算

Content

• 表的加减法
• 连结(JOIN)
• 练习

Reference：
[1]GitHub - datawhalechina/wonderful-sql: Follow me，从 0 到 1 掌握 SQL，决胜秋招。

1. 表的加减法

1.1 集合运算符

在标准 SQL 中, 可以分别对<表、检索结果>使用：

1. UNION — 并
2. INTERSECT — 交
3. 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. 练习

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

SELECT * FROM product
 WHERE sale_price>500
UNION # UNION ALL才是不删除重复行
SELECT * FROM product2
 WHERE sale_price>500;

2. 借助对称差的实现方式, 求 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)); 

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

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;

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

-- 内连结
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);

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