一、表的加減法
UNION
把兩張表合并，UNION 等集合運(yùn)算符通常都會(huì)除去重復(fù)的記錄
使用UNION ALL 就可以不去重了
SELECT product_id, product_name
??FROM Product
UNION
SELECT product_id, product_name
??FROM Product2;
將兩個(gè)表中的字段進(jìn)行連接——內(nèi)連接（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;
關(guān)于使用內(nèi)連接，需要掌握三個(gè)要點(diǎn)
要點(diǎn)一: 進(jìn)行連結(jié)時(shí)需要在 FROM 子句中使用多張表.
之前的 FROM 子句中只有一張表, 使用關(guān)鍵字 INNER JOIN 就可以將ShopProduct 和 Product 兩張表連結(jié)在一起:
FROM ShopProduct AS SP INNER JOIN Product AS P
要點(diǎn)二:必須使用 ON 子句來(lái)指定連結(jié)條件.
ON 子句是專門用來(lái)指定連結(jié)條件的, 我們?cè)谏鲜霾樵兊?ON 之后指定兩張表連結(jié)所使用的列以及比較條件, 基本上, 它能起到與 WHERE 相同的篩選作用, 我們會(huì)在本章的結(jié)尾部分進(jìn)一步探討這個(gè)話題.
要點(diǎn)三: SELECT 子句中的列最好按照 表名.列名 的格式來(lái)使用.
當(dāng)兩張表的列除了用于關(guān)聯(lián)的列之外, 沒有名稱相同的列的時(shí)候, 也可以不寫表名, 但表名可以提升可讀性間
但是, 如果兩張表有其他名稱相同的列, 則必須使用上述格式來(lái)選擇列名, 否則查詢語(yǔ)句會(huì)報(bào)錯(cuò).
結(jié)合 WHERE 子句使用內(nèi)連結(jié)——對(duì)內(nèi)連接結(jié)果進(jìn)行篩選
方法一：
SELECT *
??FROM (-- 第一步查詢的結(jié)果
????????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) AS STEP1
WHERE shop_name = '東京'
???AND product_type = '衣服' ;
方法二：
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 = '衣服') ;
找出那些售價(jià)高于該類商品平均價(jià)格的商品.
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é)——它其實(shí)是內(nèi)連結(jié)的一種特例--當(dāng)兩個(gè)表進(jìn)行自然連結(jié)時(shí), 會(huì)按照兩個(gè)表中都包含的列名來(lái)進(jìn)行等值內(nèi)連結(jié), 此時(shí)無(wú)需使用 ON 來(lái)指定連接條件.（它會(huì)將兩個(gè)表中的公共部分提到前面）
SELECT *??FROM shopproduct NATURAL JOIN Product
使用內(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)
外連接-左連接
SELECT SP.shop_id
???????,SP.shop_name
???????,SP.product_id
???????,P.product_name
???????,P.sale_price
??FROM Product AS P
??LEFT OUTER JOIN ShopProduct AS SP
????ON SP.product_id = P.product_id;
最終的結(jié)果中會(huì)包含主表內(nèi)所有的數(shù)據(jù).指定主表的關(guān)鍵字是 LEFT 和 RIGHT.顧名思義,使用 LEFT JOIN 時(shí) FROM 子句中寫在左側(cè)的表是主表,使用 RIGHT JOIN時(shí)右側(cè)的表是主表
多表內(nèi)連接——本質(zhì)上是將內(nèi)連接運(yùn)用到多個(gè)表
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';
多表外連接——本質(zhì)上是將內(nèi)連接運(yùn)用到多個(gè)表
SELECT P.product_id
???????,P.product_name
???????,P.sale_price
???????,SP.shop_id
???????,SP.shop_name
???????,IP.inventory_quantity
??FROM Product AS P
??LEFT OUTER JOIN ShopProduct AS SP
ON SP.product_id = P.product_idLEFT OUTER JOIN InventoryProduct AS IP
ON SP.product_id = IP.product_id
On子句進(jìn)階——非等值連結(jié)
每種商品使用自左連結(jié), 找出比該商品售價(jià)價(jià)格更低或相等的商品
SELECT??P1.product_id
???????,P1.product_name
???????,P1.sale_price
???????,P2.product_id AS P2_id
???????,P2.product_name AS P2_name
???????,P2.sale_price AS P2_price
??FROM Product AS P1
??LEFT OUTER JOIN Product AS P2
????ON P1.sale_price >= P2.sale_price
ORDER BY P1.sale_price,P1.product_id
交叉連接
無(wú)論是外連結(jié)內(nèi)連結(jié), 一個(gè)共同的必備條件就是ON 子句, 用來(lái)指定連結(jié)的條件. 如果不適用, 結(jié)果會(huì)有很多行. 在連結(jié)去掉 ON 子句, 就是交叉連結(jié)(CROSS JOIN)
-- 1.使用關(guān)鍵字 CROSS JOIN 顯式地進(jìn)行交叉連結(jié)SELECT SP.shop_id——用法1
???????,SP.shop_name
???????,SP.product_id
???????,P.product_name
???????,P.sale_price
??FROM ShopProduct AS SP
CROSS JOIN Product AS P;
--2.使用逗號(hào)分隔兩個(gè)表,并省略 ON 子句SELECT SP.shop_id——用法2
???????,SP.shop_name
???????,SP.product_id
???????,P.product_name
???????,P.sale_price
??FROM ShopProduct AS SP , Product AS P;
連結(jié)的特定語(yǔ)法和過(guò)時(shí)語(yǔ)法
SELECT SP.shop_id
??????,SP.shop_name
??????,SP.product_id
???????,P.product_name
???????,P.sale_price
??FROM ShopProduct AS SP
CROSS JOIN Product AS P
WHERE SP.product_id = P.product_id;
