阿里云SQL训练营Task4学习笔记

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql；

表的加法 -- UNION

用法：SELECT ...    UNION     SELECT ... 

UNION 的查询效率比 OR 更高，而且适用于两个表格

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

UNION 后面添加 ALL 关键字可以保留重复行。

SQL 里也有隐式类型转换，；例如

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

 可以将字符串和数值放在同一列。

时间日期类型和字符串,数值以及缺失值均能兼容.

Mysql仍不支持交运算 INTERSECT 操作

表的减法

MySQL 8.0 还不支持 表的减法运算符 EXCEPT. 不过, 借助NOT IN 谓词, 同样可以实现表的减法.

-- 找出只存在于product表但不存在于product2表的商品.
SELECT * 
  FROM product
 WHERE product_id NOT IN (SELECT product_id 
                            FROM product2)

SQL的交并减是 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}.

 A = {1,1,1,2,3,5,7}, B = {1,1,2,2,4,6,8} 两个 bag, 它们的交运算结果就等于 {1,1,2}.

 A = {1,1,1,2,3,5,7}, B = {1,1,2,2,4,6,8} 两个 bag, 它们的差就等于 {1,3,5,7}.

对称差

-- 使用 NOT IN 实现两个表的差集
SELECT * 
  FROM product
 WHERE product_id NOT IN (SELECT product_id FROM product2)
UNION
SELECT * 
  FROM product2
 WHERE product_id NOT IN (SELECT product_id FROM product)

内连结(INNER JOIN)

内连结的语法格式是:

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

内连结的几个要点：

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

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROM shop_product AS SP
 INNER JOIN product AS P
    ON SP.product_id = P.product_id
     WHERE SP.shop_name = '东京'
   AND P.product_type = '衣服' ;

当先连结再筛选比较复杂时，也可以先筛选再连结

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROM (SELECT *
          FROMshopproduct
         WHERE shop_name = '东京' ) AS SP
 INNER JOIN
   (SELECT *
      FROMproduct
     WHERE product_type = '衣服') AS P
    ON SP.product_id = P.product_id;

自连结

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;

自然连结(NATURAL JOIN)

自然连结是内连结的一种特例 -- 当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件.

SELECT *  FROM shopproduct NATURAL JOIN product

两个缺失值用等号进行比较, 结果不为真. 而连结只会返回对连结条件返回为真的那些行.

使用连结求交集

SELECT P1.*
  FROM product AS P1
 INNER JOINproduct2 AS P2
    ON P1.product_id = P2.product_id

外连结

外连结会根据外连结的种类有选择地保留无法匹配到的行.

按照保留的行位于哪张表,外连结有三种形式: 左连结, 右连结和全外连结.

左连结会保存左表中无法按照 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)>

外连结要点

• ●1: 选取出单张表中全部的信息
• 2:使用 LEFT、RIGHT 来指定主表 -- 使用 LEFT 时 FROM 子句中写在左侧的表是主表,使用 RIGHT 时右侧的表是主表.

使用WHERE进行外连结

根据SQL查询的执行顺序，进行完外连结后才会执行WHERE子句, 那些主表中无法被匹配到的行会被WHERE条件筛选掉。

所以应把WHERE子句挪到外连结之前进行: 先写子查询, 然后再把这个子查询和主表连结起来。

全外连结

 MySQL目前还不支持全外连结, 不过可以对左连结和右连结的结果进行 UNION 来实现全外连结。

每个派生表都必须有自己的别名

GROUP BY 中如果多个值相等则只会保留一个值

交叉连结

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

对满足相同规则的表进行交叉连结的集合运算符是 CROSS JOIN . 进行交叉连结时无法使用ON 子句, 这是因为交叉连结是对两张表中的全部记录进行交叉组合,因此结果中的记录数通常是两张表中行数的乘积.

内连结旧式写法 -- 将 CROSS JOIN 改为逗号

SELECT SP.*, P.*
  FROM shopproduct AS SP 
 CROSS JOIN product AS P
 WHERE SP.product_id = P.product_id;

但目前使用 INNER JION …ON… 的写法规定为标准写法, 因此最好使用规范写法.

不推荐过时语法,理由主要有以下三点:

• 使用这样的语法无法马上判断出到底是内连结还是外连结（又或者是其他种类的连结）.
• 由于连结条件都写在 WHERE 子句之中,因此无法在短时间内分辨出哪部分是连结条件,哪部分是用来选取记录的限制条件.
• 我们不知道这样的语法到底还能使用多久.每个 DBMS 的开发者都会考虑放弃过时的语法,转而支持新的语法.虽然并不是马上就不能使用了,但那一天总会到来的.