1.表的加減法

集合在數(shù)據(jù)庫領(lǐng)域表示記錄的集合.
具體來說,表横缔、視圖和查詢的執(zhí)行結(jié)果都是記錄的集合, 其中的元素為表或者查詢結(jié)果中的每一行.
加法：UNION（并集）

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

UNION 等集合運(yùn)算符通常都會(huì)除去重復(fù)的記錄.
對于同一張表, 實(shí)際上也是可以進(jìn)行求并集的.

UNION 與 OR 謂詞

對于同一個(gè)表的兩個(gè)不同的篩選結(jié)果集, 使用 UNION 對兩個(gè)結(jié)果集取并集, 和把兩個(gè)子查詢的篩選條件用 OR 謂詞連接, 會(huì)得到相同的結(jié)果, 但倘若要將兩個(gè)不同的表中的結(jié)果合并在一起, 就不得不使用 UNION 了.
而且, 即便是對于同一張表, 有時(shí)也會(huì)出于查詢效率方面的因素來使用 UNION.

包含重復(fù)行的集合運(yùn)算 UNION ALL

SQL 語句的 UNION 會(huì)對兩個(gè)查詢的結(jié)果集進(jìn)行合并和去重, 這種去重不僅會(huì)去掉兩個(gè)結(jié)果集相互重復(fù)的, 還會(huì)去掉一個(gè)結(jié)果集中的重復(fù)行. 但在實(shí)踐中有時(shí)候需要需要不去重的并集, 在 UNION 的結(jié)果中保留重復(fù)行的語法其實(shí)非常簡單,只需要在 UNION 后面添加 ALL 關(guān)鍵字就可以了.

隱式類型轉(zhuǎn)換

通常來說, 我們會(huì)把類型完全一致, 并且代表相同屬性的列使用 UNION 合并到一起顯示, 但有時(shí)候, 即使數(shù)據(jù)類型不完全相同, 也會(huì)通過隱式類型轉(zhuǎn)換來將兩個(gè)類型不同的列放在一列里顯示, 例如字符串和數(shù)值類型:

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

交運(yùn)算INTERSECT和減法運(yùn)算符EXCEPT

截止到 MySQL 8.0 版本, MySQL 仍然不支持 INTERSECT 操作.
對于同一個(gè)表的兩個(gè)查詢結(jié)果而言, 他們的交INTERSECT實(shí)際上可以等價(jià)地將兩個(gè)查詢的檢索條件用AND謂詞連接來實(shí)現(xiàn).

--使用AND謂詞查找product表中利潤率高于50%,并且售價(jià)低于1500的商品
SELECT * 
  FROM product
 WHERE sale_price > 1.5 * purchase_price 
   AND sale_price < 1500

MySQL 8.0 還不支持表的減法運(yùn)算符 EXCEPT. 不過, 借助學(xué)過的NOT IN 謂詞, 我們同樣可以實(shí)現(xiàn)表的減法.
使用 NOT IN 謂詞, 基本上可以實(shí)現(xiàn)和SQL標(biāo)準(zhǔn)語法中的EXCEPT運(yùn)算相同的效果.

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

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

類似于UNION ALL, EXCEPT ALL 也是按出現(xiàn)次數(shù)進(jìn)行減法, 也是使用bag模型進(jìn)行運(yùn)算.
留意bag模型與set模型的區(qū)別：是否允許元素重復(fù)導(dǎo)致了 set 和 bag 的并交差等運(yùn)算都存在一些區(qū)別背率，使用 bag 模型來描述數(shù)據(jù)庫中的表在很多時(shí)候更加合適.

對稱差

兩個(gè)集合A,B的對稱差是指那些僅屬于A或僅屬于B的元素構(gòu)成的集合.
兩個(gè)集合的對稱差等于A-B并上B-A, 因此在MySQL 8.0 里實(shí)踐中可以用這個(gè)思路來求對稱差.
示例

--使用product表和product2表的對稱差來查詢哪些商品只在其中一張表
-- 使用 NOT IN 實(shí)現(xiàn)兩個(gè)表的差集
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)

▲借助并集和差集迂回實(shí)現(xiàn)交集運(yùn)算 INTERSECT
兩個(gè)集合的交可以看作是兩個(gè)集合的并去掉兩個(gè)集合的對稱差.

2.連結(jié)（JOIN)

前一節(jié)我們學(xué)習(xí)了 UNION和INTERSECT 等集合運(yùn)算, 這些集合運(yùn)算的特征就是以行方向?yàn)閱挝贿M(jìn)行操作. 通俗地說, 就是進(jìn)行這些集合運(yùn)算時(shí), 會(huì)導(dǎo)致記錄行數(shù)的增減. 使用 UNION 會(huì)增加記錄行數(shù),而使用 INTERSECT 或者 EXCEPT 會(huì)減少記錄行數(shù).

但這些運(yùn)算不能改變列的變化

連結(jié)(JOIN)就是使用某種關(guān)聯(lián)條件(一般是使用相等判斷謂詞"="), 將其他表中的列添加過來, 進(jìn)行“添加列”的集合運(yùn)算.

可以說,連結(jié)是 SQL 查詢的核心操作, 掌握了連結(jié), 能夠從兩張甚至多張表中獲取列, 能夠?qū)⑦^去使用關(guān)聯(lián)子查詢等過于復(fù)雜的查詢簡化為更加易讀的形式, 以及進(jìn)行一些更加復(fù)雜的查詢.

內(nèi)連結(jié)（INNER JOIN)

示例

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;

上面分別為兩張表指定了簡單的別名, 這種操作在使用連結(jié)時(shí)是非常常見的, 通過別名會(huì)讓我們在編寫查詢時(shí)少打很多字, 并且更重要的是, 會(huì)讓查詢語句看起來更加簡潔.

注意：
進(jìn)行連結(jié)時(shí)需要在 FROM 子句中使用多張表
必須使用 ON 子句來指定連結(jié)條件
SELECT 子句中的列最好按照 表名.列名 的格式來使用
（表名使得我們能夠在今后的任何時(shí)間閱讀查詢代碼的時(shí)候, 都能馬上看出每一列來自于哪張表, 能夠節(jié)省我們很多時(shí)間.如果兩張表有其他名稱相同的列, 則必須使用上述格式來選擇列名, 否則查詢語句會(huì)報(bào)錯(cuò).）

▲熟記查詢的執(zhí)行順序:
FROM 子句->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 = '衣服' ;

另外, 先連結(jié)再篩選的標(biāo)準(zhǔn)寫法的執(zhí)行順序是, 兩張完整的表做了連結(jié)之后再做篩選,如果要連結(jié)多張表, 或者需要做的篩選比較復(fù)雜時(shí), 在寫 SQL 查詢時(shí)會(huì)感覺比較吃力. 在結(jié)合 WHERE 子句使用內(nèi)連結(jié)的時(shí)候, 我們也可以更改任務(wù)順序, 并采用任務(wù)分解的方法,先分別在兩個(gè)表使用 WHERE 進(jìn)行篩選,然后把上述兩個(gè)子查詢連結(jié)起來.

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;

結(jié)合 GROUP BY 子句使用內(nèi)連結(jié)

自連結(jié)(SELF JOIN)

之前的內(nèi)連結(jié), 連結(jié)的都是不一樣的兩個(gè)表. 但實(shí)際上一張表也可以與自身作連結(jié), 這種連接稱之為自連結(jié).

內(nèi)連結(jié)與關(guān)聯(lián)子查詢

示例：
找出每個(gè)商品種類當(dāng)中售價(jià)高于該類商品的平均售價(jià)的商品
用內(nèi)連結(jié)來進(jìn)行實(shí)現(xiàn)

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;

自然連結(jié)(NATURAL JOIN)

自然連結(jié)并不是區(qū)別于內(nèi)連結(jié)和外連結(jié)的第三種連結(jié), 它其實(shí)是內(nèi)連結(jié)的一種特例–當(dāng)兩個(gè)表進(jìn)行自然連結(jié)時(shí), 會(huì)按照兩個(gè)表中都包含的列名來進(jìn)行等值內(nèi)連結(jié), 此時(shí)無需使用 ON 來指定連接條件.
上述查詢得到的結(jié)果, 會(huì)把兩個(gè)表的公共列(這里是 product_id, 可以有多個(gè)公共列)放在第一列, 然后按照兩個(gè)表的順序和表中列的順序, 將兩個(gè)表中的其他列都羅列出來。

注意：運(yùn)動(dòng) T 恤的 regist_date 字段為空（NULL)
在進(jìn)行自然連結(jié)時(shí), 來自于 product 和 product2 的運(yùn)動(dòng) T 恤這一行數(shù)據(jù)在進(jìn)行比較時(shí), 實(shí)際上是在逐字段進(jìn)行等值連結(jié), 兩個(gè)缺失值用等號進(jìn)行比較, 結(jié)果不為真. 而連結(jié)只會(huì)返回對連結(jié)條件返回為真的那些行.(NULL只能用IS或者IS NOT比較）

1.png

如果我們這樣查詢

SELECT * 
  FROM (SELECT product_id, product_name
          FROM product ) AS A 
NATURAL JOIN 
   (SELECT product_id, product_name 
      FROM product2) AS B;

2.png

就會(huì)得到運(yùn)動(dòng)T恤那一行記錄。
只選取了product_id那列褐缠，這樣實(shí)際上是避免了比較NULL（空值）蹂匹，從而返回我們想要的結(jié)果

使用連結(jié)求交集

使用內(nèi)連結(jié)求 product 表和 product2 表的交集.

SELECT P1.*
  FROM product AS P1
 INNER JOIN product2 AS P2
    ON (P1.product_id  = P2.product_id
   AND P1.product_name = P2.product_name
   AND P1.product_type = P2.product_type
   AND P1.sale_price   = P2.sale_price
   AND P1.regist_date  = P2.regist_date)

3.png

外連結(jié)(OUTER JOIN)

內(nèi)連結(jié)會(huì)丟棄兩張表中不滿足 ON 條件的行,和內(nèi)連結(jié)相對的就是外連結(jié). 外連結(jié)會(huì)根據(jù)外連結(jié)的種類有選擇地保留無法匹配到的行.

按照保留的行位于哪張表,外連結(jié)有三種形式: 左連結(jié), 右連結(jié)和全外連結(jié).
左連結(jié)會(huì)保存左表中無法按照 ON 子句匹配到的行, 此時(shí)對應(yīng)右表的行均為缺失值;
右連結(jié)則會(huì)保存右表中無法按照 ON 子句匹配到的行, 此時(shí)對應(yīng)左表的行均為缺失值;
全外連結(jié)則會(huì)同時(shí)保存兩個(gè)表中無法按照 ON子句匹配到的行, 相應(yīng)的另一張表中的行用缺失值填充.

▲shopproduct 和 product 進(jìn)行內(nèi)連結(jié)時(shí)蚤认，product 表中有兩種商品并未在內(nèi)連結(jié)的結(jié)果里, 就是說, 這兩種商品并未在任何商店有售(這通常意味著比較重要的業(yè)務(wù)信息, 例如, 這兩種商品在所有商店都處于缺貨狀態(tài), 需要及時(shí)補(bǔ)貨).

4.png

我們觀察上述結(jié)果可以發(fā)現(xiàn), 有兩種商品: 高壓鍋和圓珠筆, 在所有商店都沒有銷售.
由于我們在 SELECT 子句選擇列的顯示順序以及未對結(jié)果進(jìn)行排序的原因, 這個(gè)事實(shí)需要你仔細(xì)地進(jìn)行觀察.
所以可以猜測在數(shù)據(jù)量很大的時(shí)候執(zhí)行這個(gè)操作需要進(jìn)行排序

外連結(jié)要點(diǎn) 1: 選取出單張表中全部的信息
外連結(jié)要點(diǎn) 2:使用 LEFT米苹、RIGHT 來指定主表.

★結(jié)合 WHERE 子句使用左連結(jié)

由于外連結(jié)的結(jié)果很可能與內(nèi)連結(jié)的結(jié)果不一樣, 會(huì)包含那些主表中無法匹配到的行, 并用缺失值填寫另一表中的列, 由于這些行的存在, 因此在外連結(jié)時(shí)使用WHERE子句, 情況會(huì)有些不一樣.
▲主要還是缺失值（NULL）的問題
示例：
使用外連結(jié)從shopproduct表和product表中找出那些在某個(gè)商店庫存少于50的商品及對應(yīng)的商店.希望得到如下結(jié)果.

5.png

我們可能自然地想到

SELECT P.product_id
       ,P.product_name
       ,P.sale_price
       ,SP.shop_id
       ,SP.shop_name
       ,SP.quantity
  FROM product AS P
  LEFT OUTER JOIN shopproduct AS SP
    ON SP.product_id = P.product_id
 WHERE quantity< 50

然而結(jié)果是

6.png

在WHERE子句中增加 quantity IS NULL 的條件是一種解決方法
然而在真實(shí)的查詢環(huán)境中, 由于數(shù)據(jù)量大且數(shù)據(jù)質(zhì)量并非如系統(tǒng)說明和我們設(shè)想的那樣"干凈", 我們并不能很容易地意識(shí)到缺失值等問題數(shù)據(jù)的存在，所以還是考慮如何改寫我們的查詢

聯(lián)系到我們已經(jīng)掌握了的SQL查詢的執(zhí)行順序(FROM->WHERE->SELECT),我們發(fā)現(xiàn), 問題可能出在篩選條件上, 因?yàn)樵谶M(jìn)行完外連結(jié)后才會(huì)執(zhí)行WHERE子句, 因此那些主表中無法被匹配到的行就被WHERE條件篩選掉了.
所以我們可以試著把WHERE子句挪到外連結(jié)之前進(jìn)行: 先寫個(gè)子查詢,用來從shopproduct表中篩選quantity<50的商品, 然后再把這個(gè)子查詢和主表連結(jié)起來.

SELECT P.product_id
      ,P.product_name
      ,P.sale_price
       ,SP.shop_id
      ,SP.shop_name
      ,SP.quantity 
  FROM product AS P
  LEFT OUTER JOIN-- 先篩選quantity<50的商品
   (SELECT *
      FROM shopproduct
     WHERE quantity < 50 ) AS SP
    ON SP.product_id = P.product_id

這樣就可以得到我們想要的結(jié)果啦

▲MySQL8.0 目前還不支持全外連結(jié), 不過我們可以對左連結(jié)和右連結(jié)的結(jié)果進(jìn)行 UNION 來實(shí)現(xiàn)全外連結(jié).

多表連結(jié)

原則上連結(jié)表的數(shù)量并沒有限制.
示例：

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
       ,IP.inventory_quantity
  FROM shopproduct AS SP
 INNER JOIN product AS P
    ON SP.product_id = P.product_id
 INNER JOIN Inventoryproduct AS IP
    ON SP.product_id = IP.product_id
 WHERE IP.inventory_id = 'P001';

連結(jié)第三張表的時(shí)候, 也是通過 ON 子句指定連結(jié)條件(這里使用最基礎(chǔ)的等號將作為連結(jié)條件的 product 表和 shopproduct 表中的商品編號 product _id 連結(jié)了起來), 由于 product 表和 shopproduct 表已經(jīng)進(jìn)行了連結(jié),因此就無需再對 product 表和Inventoryproduct 表進(jìn)行連結(jié)了(雖然也可以進(jìn)行連結(jié),但結(jié)果并不會(huì)發(fā)生改變, 因?yàn)楸举|(zhì)上并沒有增加新的限制條件).

即使想要把連結(jié)的表增加到 4 張或其以上砰琢，使用 INNER JOIN 進(jìn)行添加的方式也是完全相同的.

類似地蘸嘶，多表也可以進(jìn)行外連結(jié)

ON 子句進(jìn)階–非等值連結(jié)

實(shí)際上, 包括比較運(yùn)算符(<,<=,>,>=, BETWEEN)和謂詞運(yùn)算(LIKE, IN, NOT 等等)在內(nèi)的所有的邏輯運(yùn)算都可以放在 ON 子句內(nèi)作為連結(jié)條件.
ON子句內(nèi)不一定必須使用=號
▲####非等值自左連結(jié)(SELF JOIN)
示例：希望對 product 表中的商品按照售價(jià)賦予排名

交叉連結(jié)—— CROSS JOIN(笛卡爾積)

交叉連結(jié)是對兩張表中的全部記錄進(jìn)行交叉組合,因此結(jié)果中的記錄數(shù)通常是兩張表中行數(shù)的乘積.
交叉連結(jié)沒有應(yīng)用到實(shí)際業(yè)務(wù)之中的原因有兩個(gè)
一是其結(jié)果沒有實(shí)用價(jià)值,二是由于其結(jié)果行數(shù)太多,需要花費(fèi)大量的運(yùn)算時(shí)間和高性能設(shè)備的支持.

注意連結(jié)的特定語法和過時(shí)語法

練習(xí)題

1.找出 product 和 product2 中售價(jià)高于 500 的商品的基本信息.

SELECT *
FROM product
WHERE sale_price > 500
UNION
SELECT *
FROM product2
WHERE sale_price > 500;

--注意：創(chuàng)建視圖時(shí)記得給視圖指定別名AS...  否則MySQL會(huì)報(bào)錯(cuò)
--子查詢的結(jié)果必須要有一個(gè)別名
SELECT *
FROM (SELECT *
      FROM product
      UNION 
      SELECT *
      FROM product2) AS P
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.每類商品中售價(jià)最高的商品都在哪些商店有售?

SELECT SP.shop_id,SP.shop_name,product_type,
       MAX(sale_price) AS max_price 
FROM shopproduct AS SP
INNER JOIN product AS P
ON SP.product_id = P.product_id
GROUP BY P.product_type;

4.分別使用內(nèi)連結(jié)和關(guān)聯(lián)子查詢每一類商品中售價(jià)最高的商品.

--1.內(nèi)連結(jié)
SELECT  P1.product_id
       ,P1.product_name
       ,P1.product_type
       ,P1.sale_price
       ,P2.max_price
FROM product AS P1
INNER JOIN 
     (SELECT product_type,MAX(sale_price) AS max_price 
      FROM product 
      GROUP BY product_type) AS P2 
ON P1.product_type=P2.product_type;
--2.關(guān)聯(lián)子查詢
SELECT product_id,product_name,product_type,sale_price,
       (SELECT MAX(sale_price) FROM product AS P2 
        WHERE P1.product_type=P2.product_type 
        GROUP BY product_type) AS max_price
FROM product AS P1