阿里天池龙珠计划SQL训练营Task04笔记

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为

AI训练营SQL-阿里云天池

表的加减法

在标准 SQL 中, 分别对检索结果使用 UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算, 像UNION,INTERSECT, EXCEPT这种用来进行集合运算的运算符称为集合运算符。

表的加法–UNION

两表相加-并集

SELECT * FROM product
UNION
SELECT * FROM product2;

注：UNION 等集合运算符通常都会除去重复的记录 

大部分时候在一张表内的查询，UNION和OR没有区别，但是查询涉及到两表及以上的时候就要用到UNION了

UNION ALL

SQL 语句的 UNION 会对两个查询的结果集进行合并和去重, 这种去重不仅会去掉两个结果集相互重复的, 还会去掉一个结果集中的重复行

在实践中有时候需要需要不去重的并集,只需要在 UNION 后面添加 ALL 关键字就可以了

Bag模型

Bag 是和 set 类似的一种数学结构, 不一样的地方在于: bag 里面允许存在重复元素, 如果同一个元素被加入多次, 则袋子里就有多个该元素

由于 bag 允许元素重复出现,

对于两个 bag, 他们的并运算会按照: 1.该元素是否至少在一个 bag 里出现过, 2.该元素在两个 bag 中的最大出现次数 这两个方面来进行计算. 因此对于 A = {1,1,1,2,3,5,7}, B = {1,1,2,2,4,6,8} 两个 bag, 它们的并就等于 {1,1,1,2,2,3,4,5,6,7,8}

对于两个 bag, 他们的交运算会按照: 1.该元素是否同时属于两个 bag, 2.该元素在两个 bag 中的最小出现次数这两个方面来进行计算. 因此对于 A = {1,1,1,2,3,5,7}, B = {1,1,2,2,4,6,8} 两个 bag, 它们的交运算结果就等于 {1,1,2}

对于两个 bag, 他们的差运算会按照:1.该元素是否属于作为被减数的 bag,2.该元素在两个 bag 中的出现次数这两个方面来进行计算. 只有属于被减数的bag的元素才参与EXCEP ALL运算, 并且差bag中的次数,等于该元素在两个bag的出现次数之差(差为零或负数则不出现). 因此对于 A = {1,1,1,2,3,5,7}, B = {1,1,2,2,4,6,8} 两个 bag, 它们的差就等于 {1,3,5,7}

 表的减法-EXCEPT

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

例：求出product表中, 售价高于2000,但利润低于30%的商品

SELECT * 
FROM product
WHERE sale_price > 2000 
AND product_id NOT IN (SELECT product_id 
                       FROM product 
                       WHERE sale_price<1.3*purchase_price)

EXCEPT ALL 

类似于UNION ALL, EXCEPT ALL 也是按出现次数进行减法, 也是使用bag模型进行运算

INTERSECT

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

对称差

两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合，首先使用UNION求两个表的并集, 然后使用INTERSECT求两个表的交集, 然后用并集减去交集, 就得到了对称差

但由于在MySQL 8.0 里, 由于两个表或查询结果的并不能直接求出来, 因此并不适合使用上述思路来求对称差. 好在还有差集运算可以使用. 从直观上就能看出来, 两个集合的对称差等于 A-B并上B-A, 因此实践中可以用这个思路来求对称差

连结(JOIN)

找出一个类似于"轴"或者"桥梁"的公共列, 将两张表用这个列连结起来. 这就是连结运算所要作的事情

内连结(INNER JOIN)

SELECT * FROM product INNER JOIN product2 ON product.id = product2.id

注：进行连结时需要在 FROM 子句中使用多张表，必须使用 ON 子句来指定连结条件，SELECT 子句中的列最好按照 表名.列名 的格式来使用

 外连结(OUTER JOIN)

左连结会保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值;

右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值

全外连结则会同时保存两个表中无法按照 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)>

注：外连接会选取出主表中全部的信息，使用 LEFT、RIGHT 来指定主表