训练营笔记——Task04：集合运算-表的加减法和join等

#表的加减法
集合运算符：UNION,INTERSECT, EXCEPT

UNION：表的加法

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

UNION 对两个查询结果取并集=在一个查询中使用 WHERE 子句, 然后使用 OR 谓词连接两个查询条件
BUT，将两个不同的表中的结果合并在一起, 就不得不使用 UNION 了

在 UNION 的结果中保留重复行的语法,只需要在 UNION 后面添加 ALL 关键字就可以了

-- 保留重复行
SELECT product_id, product_name
  FROM product
 UNION ALL
SELECT product_id, product_name
  FROM product2;

通常来说, 我们会把类型完全一致, 并且代表相同属性的列使用 UNION 合并到一起显示, 但有时候, 即使数据类型不完全相同, 也会通过隐式类型转换来将两个类型不同的列放在一列里显示, 例如字符串和数值类型:

SELECT product_id, product_name, '1'
  FROM product
 UNION
SELECT product_id, product_name,sale_price
  FROM product2;

MySQL 仍然不支持 INTERSECT 操作.不支持表的减法运算符 EXCEPT.

EXCEPT = NOT 谓词

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

两个集合A,B的对称差：指那些仅属于A或仅属于B的元素构成的集合
e.g两个集合的交就可以看作是两个集合的并去掉两个集合的对称差

首先使用UNION求两个表的并集, 然后使用INTERSECT求两个表的交集, 然后用并集减去交集, 就得到了对称差

#连结（JOIN）
连结(JOIN)：使用某种关联条件(一般是使用相等判断谓词"="), 将其他表中的列添加过来, 进行“添加列”的集合运算

内连结：

-- 内连结
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>

INNER 关键词表示使用了内连结
三个要点：

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

如果需要在使用内连结的时候同时使用 WHERE 子句对检索结果进行筛选, 则需要把 WHERE 子句写在 ON 子句的后边

执行顺序：FROM 子句->WHERE 子句->SELECT 子句

结合 GROUP BY 子句使用内连结, 需要根据分组列位于哪个表区别对待
最简单的情形, 是在内连结之前就使用 GROUP BY 子句
如果分组列和被聚合的列不在同一张表, 且二者都未被用于连结两张表, 则只能先连结, 再聚合

自连结：一张表与自身作连结
自连结并不是区分于内连结和外连结的第三种连结, 自连结可以是外连结也可以是内连结, 它是不同于内连结外连结的另一个连结的分类方法

自然连结并不是区别于内连结和外连结的第三种连结, 它其实是内连结的一种特例–当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件

SELECT *  FROM shopproduct NATURAL JOIN product

内连结会丢弃两张表中不满足 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)>

三个要点：

1. 选取出单张表中全部的信息
2. 使用 LEFT、RIGHT 来指定主表
3. 结合 WHERE 子句使用左连结

全外连结本质上就是对左表和右表的所有行都予以保留, 能用 ON 关联到的就把左表和右表的内容在一行内显示, 不能被关联到的就分别显示, 然后把多余的列用缺失值填充

原则上连结表的数量并没有限制

交叉连结—— CROSS JOIN(笛卡尔积)
在连结去掉 ON 子句

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

在笛卡尔积的基础上, 我们增加一个 WHERE 子句, 将之前的连结条件作为筛选条件加进去, 我们会发现, 得到的结果恰好是直接使用内连接的结果