Task04：集合运算

笔记：
一.集合运算
1表的加减法
1.1什么是集合运算
在标准 SQL 中, 分别对检索结果使用 UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算, 像UNION,INTERSECT, EXCEPT这种用来进行集合运算的运算符称为集合运算符
在数据库中，所有的表以及查询结果都可以视为集合，因此也可以把表视为集合进行进行上述集合运算。
1.2表的加法-UNION
1.2.1UNION
UNION等集合运算符通常都会除去重复的记录

SELECT product_id, product_name
  FROM product
 UNION
SELECT product_id, product_name
  FROM product2;

1.2.2UNION 与OR谓词
对于同一个表的两个不同的筛选结果集，使用UNION对两个结果集取并集，和把两个子查询的筛选条件用OR谓词连接，会得到相同的结果，但倘若要将两个不同的表中的结果合并在一起，就不得不使用UNION。并且UNION的查询效率高于OR谓词。

1.2.3包含重复行的集合运算 UNION ALL
作用：在需要进行不去重的并集是使用 union all。
【扩展】bag模型和set模型
区别：bag模型允许存在重复元素。
因为bag允许元素重复出现，对于两个bag,她们的并运算会按照：1.该元素是否至少在一个bag里出现过，2.该元素在两个bag中的最大出现次数。
PS： 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 中的最小出现次数
PS：A = {1,1,1,2,3,5,7}, B = {1,1,2,2,4,6,8} 结果为：{1,1,2}
1.2.4 隐式类型转换
可以通过隐式类型转换将两个类型不同的列放在一列中显示/
MySQL 8.0 不支持交运算INTERSECT
1.2.4差集，补集与表的减法
集合A和B做减法只是将集合A中也同时属于集合B的元素减掉.
1.2.5 MySQL 8.0 还不支持 EXCEPT 运算，我们可以通过NOT IN谓词来实现表的减法

SELECT * 
  FROM product
 WHERE product_id NOT IN (SELECT product_id 
                            FROM product2)

1.2.6
只有属于被减数的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}.
1.2.7对称差
是指那些仅属于A或仅属于B的元素构成的集合

2连结（JON)
使用某种关联条件(一般是使用相等判断谓词"="), 将其他表中的列添加过来, 进行“添加列”的集合运算. 可以说,连结是 SQL 查询的核心操作, 掌握了连结, 能够从两张甚至多张表中获取列, 能够将过去使用关联子查询等过于复杂的查询简化为更加易读的形式, 以及进行一些更加复杂的查询.
2.1内连结(INNER JOIN)
语法格式是:
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>
用法：
找出一个类似于"轴"或者"桥梁"的公共列, 将两张表用这个列连结起来. 这就是连结运算所要作的事情.
2.1.1关于内连结,需要注意以下三点:
要点一: 进行连结时需要在 FROM 子句中使用多张表.
FROM shopproduct AS SP INNER JOIN product AS P
要点二:必须使用 ON 子句来指定连结条件.
ON 子句是专门用来指定连结条件的, 基本上, 它能起到与 WHERE 相同的筛选作用
要点三: SELECT 子句中的列最好按照 表名.列名 的格式来使用.
当两张表的列除了用于关联的列之外, 没有名称相同的列的时候, 也可以不写表名, 但表名使得我们能够在今后的任何时间阅读查询代码的时候, 都能马上看出每一列来自于哪张表, 能够节省我们很多时间.
但是, 如果两张表有其他名称相同的列, 则必须使用上述格式来选择列名, 否则查询语句会报错.
2.1.2结合 WHERE 子句使用内连结
如果需要在使用内连结的时候同时使用 WHERE 子句对检索结果进行筛选, 则需要把 WHERE 子句写在 ON 子句的后边.
查询顺序如下：
FROM 子句->WHERE 子句->SELECT 子句
两张表是先按照连结列进行了连结, 得到了一张新表, 然后 WHERE 子句对这张新表的行按照两个条件进行了筛选, 最后, SELECT 子句选出了那些我们需要的列

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 子句中的条件直接添加在 ON 子句中, 这时候 ON 子句后最好用括号将连结条件和筛选条件括起来.
（因不太容易阅读，不建议大家使用）

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
   AND SP.shop_name = '东京'
   AND P.product_type = '衣服') ;

先连结再筛选的标准写法的执行顺序是, 两张完整的表做了连结之后再做筛选,如果要连结多张表, 或者需要做的筛选比较复杂时, 在写 SQL 查询时会感觉比较吃力. 在结合 WHERE 子句使用内连结的时候, 我们也可以更改任务顺序, 并采用任务分解的方法,先分别在两个表使用 WHERE 进行筛选,然后把上述两个子查询连结起来。

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 *
          FROM shopproduct
         WHERE shop_name = '东京' ) AS SP
 INNER JOIN -- 子查询 2:从 product 表筛选出衣服类商品的信息
   (SELECT *
      FROM product
     WHERE product_type = '衣服') AS P
    ON SP.product_id = P.product_id;

先分别在两张表里做筛选, 把复杂的筛选条件按表分拆, 然后把筛选结果(作为表)连接起来, 避免了写复杂的筛选条件, 因此这种看似复杂的写法, 实际上整体的逻辑反而非常清晰. 在写查询的过程中, 首先要按照最便于自己理解的方式来写, 先把问题解决了, 再思考优化的问题.
2.1.3
自连结(SELF JOIN)
之前的内连结, 连结的都是不一样的两个表. 但实际上一张表也可以与自身作连结, 这种连接称之为自连结. 需要注意, 自连结并不是区分于内连结和外连结的第三种连结, 自连结可以是外连结也可以是内连结, 它是不同于内连结外连结的另一个连结的分类方法.
2.1.4自然连结(NATURAL JOIN)
自然连结并不是区别于内连结和外连结的第三种连结, 它其实是内连结的一种特例–当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件.
SELECT * FROM shopproduct NATURAL JOIN product
上述查询得到的结果, 会把两个表的公共列(这里是 product_id, 可以有多个公共列)放在第一列, 然后按照两个表的顺序和表中列的顺序, 将两个表中的其他列都罗列出来。使用自然连结还可以求出两张表或子查询的公共部分

2.2外连结(OUTER JOIN)
2.2.1外连结会根据外连结的种类有选择地保留无法匹配到的行.
2.2.2按照保留的行位于哪张表,外连结有三种形式: 左连结, 右连结和全外连结.
三种外连结的对应语法分别为:
– 左连结
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)>
由于连结时可以交换左表和右表的位置, 因此左连结和右连结并没有本质区别
2.2.3外连结要点
1: 选取出单张表中全部的信息
外连结能够得到固定行数的结
2.使用 LEFT、RIGHT 来指定主表.
要把哪张表作为主表.最终的结果中会包含主表内所有的数据.指定主表的关键字是 LEFT 和 RIGHT.顾名思义,使用 LEFT 时 FROM 子句中写在左侧的表是主表,使用 RIGHT 时右侧的表是主表
2.2.4在MY SQL中实现全外连结
MySQL8.0 目前还不支持全外连结, 不过我们可以对左连结和右连结的结果进行 UNION 来实现全外连结.