這是線上平台課程的筆記內容，就是讓自己方便複習用。
老師講課的進度掌握得很好，教得也很淺寫易懂。
線上平台課程內有提供 SQL套件，如果有興趣的朋友一定要去支持老師的課程。
這樣才能取得老師提供的套件包做練習使用。
如果有想要知道老師的課程，再詢問我。
CH8 子查詢
EXISTS
使用他來覆蓋一些使用它的存在。
SELECT 欄位
FROM table
WHERE EXISTS(SELECT 欄位 FROM table WHERE 條件)
看到EXISTS 就要看一下子查詢的條件是否有滿足。
有滿足的話就會從中提取紀錄。
範例:我想找到1997年4月份訂購的所有客戶。
這邊我們可以看到我們從customers那拉出了，companyname
SELECT companyname
FROM customers
WHERE EXISTS ()
這時候我們剩下的查詢客戶是否有1997年4月訂單的動作。
在EXISTS裡面做。
因為這邊的動作橫跨兩張表，所以要使用到JOIN
(SELECT customerid FROM orders
WHERE orders.customerid = customers.customerid
AND orderdate BETWEEN '1997-04-01' AND '1997-04-30');
這邊去掉INNER JOIN 直接做JOIN的動作。
透過兩張表的customerid 將兩者JOIN起來。
orders.customerid = customers.customerid
合起來
SELECT companyname
FROM customers
WHERE EXISTS
(SELECT customerid FROM orders
WHERE orders.customerid = customers.customerid
AND orderdate BETWEEN '1997-04-01' AND '1997-04-30');
子查詢要JOIN 可以直接把父子查詢的表中的ID對應起來就好。
現在來找4月沒有訂單的客戶吧
這時候表示我們要的就是子查詢(EXISTS)的結果的相反。
所以上NOT ，表示不存在的狀況。
SELECT companyname
FROM customers
WHERE NOT EXISTS
(SELECT customerid FROM orders
WHERE orders.customerid = customers.customerid
AND orderdate BETWEEN '1997-04-01' AND '1997-04-30');
我們來找找看那些產品在1997/04沒有訂單。
1.找產品
2.子查詢找第二需求，符合的條件日期，
要符合的:WHERE EXISTS
不要符合的:WHERE NOT EXISTS
SELECT productname
FROM products
WHERE NOT EXISTS ()
因為一個問題我們要找的資料只有在order_details裡面才有。
但是我們product能JOIN的表只到orders而已。
SELECT
FROM orders
WHERE orders.productid = products.product.id
父子的關係用完之後，只好再用JOIN來完成後面的連結。
SELECT
FROM orders
INNER JOIN order_details ON order_details.orderid = orders.orderid
WHERE orders.productid = products.product.id
這邊要把要對應的欄位放上去。
因為在products表上面只能認知到productid
所以我們在以查詢內做的事情，是要告訴父查詢。
那些資料是你要的。而那些你要的資料你要能認得才行。
所以我們只能提供productid
SELECT productid
FROM orders
INNER JOIN order_details ON order_details.orderid = orders.orderid
WHERE orders.productid = products.product.id
加上條件。WHERE的第一步是連接父子。
再來才是條件。接著把我們要的日期欄位放上去
SELECT productid
FROM orders
INNER JOIN order_details ON order_details.orderid = orders.orderid
WHERE orders.productid = products.product.id AND orderdate BETWEEN '1997-04-01' AND '1997-04-30'
最後整個語法合起來。
SELECT productname
FROM products
WHERE NOT EXISTS (SELECT productid
FROM order_details
INNER JOIN orders ON orders.orderid = order_details.orderid
WHERE order_details.productid = products.productid
AND orderdate BETWEEN '1997-04-01' AND '1997-04-30');
子查詢找出的productid就是有在那段期間接獲訂單的產品。
返回的productid就是有接獲訂購的產品答案。
如果要相反，就要再加上NOT。
找到有提供價格超過200元產品的供應商。
1 要顯示的是供應商
SELECT companyname
FROM suppliers
WHERE EXISTS
();
2 找尋產品超過200元 的廠商supplierid。
SELECT supplierid
FROM products
WHERE products.supplierid = suppliers.supplierid
AND unitprice > 200
3合起來
SELECT companyname
FROM suppliers
WHERE EXISTS
(SELECT productid
FROM products
WHERE products.supplierid = suppliers.supplierid
AND unitprice > 200);
這邊suppliers這張表沒有productid的值。
但這邊會自動對應到那一行當中，有辦法辨識的值當中?
現階段比較能理解的還是用 supplierid去返回資料。
如果硬要解釋，就是productid那行的資料，會全部餵回去父查詢那邊。
它可辨別到supplierid = 18的資料，然後帶出我們的值。
LAB 想要找到1996/12沒有訂單的供應商
1找供應商。
2在1996/12沒有訂單的。 反向條件NOT
想到訂單要考慮到，orders，order_details，products
SELECT companyname
FROM suppliers
WHERE NOT EXISTS ()
遇到瓶頸，現在連結不回去，所以一定要在JOIN表。
SELECT products.productid FROM products
JOIN oreder_details ON order_details.productid = products.productid
JOIN orders ON orders.orderid = order_details.orderid
WHERE products. supplierid = suppliers.supplierid
AND orderdate BETWEEN '1996-12-01' AND '1996-12-31'
這邊透過抓到productid的關鍵，可以抓到supplierid的值。
所以找到那一行的資料，等同於把supplierid拋回去。
3合併
SELECT companyname
FROM suppliers
WHERE NOT EXISTS
(SELECT products.productid FROM products
JOIN order_details ON order_details.productid = products.productid
JOIN orders ON orders.orderid = order_details.orderid
WHERE products. supplierid = suppliers.supplierid
AND orderdate BETWEEN '1996-12-01' AND '1996-12-31')
;
這邊是這其間有訂單的商品清單
表示沒有的就是沒有被購買的商品清單。
每個商品都會有它的供應商，若沒有產品清單就表示沒有貨的供應商。
主要也是因為能夠對應到供應商的就只有product，
所以這邊特別把product拉進來。
接著要連接到orders.orderdate欄位 ，
products最終目標要連接orders，需要透過order_details來當中介值。
因order_details有productid & orderid可以當雙向連接的值。
之後就可以order_details.orderid去連接orders.orderid
就能取得orders.orderdate欄位
SELECT supplierid, companyname
FROM suppliers
WHERE NOT EXISTS
(SELECT products.supplierid FROM products
JOIN order_details ON order_details.productid = products.productid
JOIN orders ON orders.orderid = order_details.orderid
WHERE products. supplierid = suppliers.supplierid
AND orderdate BETWEEN '1996-12-01' AND '1996-12-31')
ANY ALL的使用
SELECT 欄位
FROM table
WHERE 條件 operator ANY
(SELECT 欄位 FROM table WHERE條件)
Operator 指的是 = , > , < , >=, <= ,!=
EXISTS 主要就是EXISTS 以及NOT EXISTS，就只有 是，不是而已。
找到訂購一件產品的客戶，這些產品單件產品超過50件。
SELECT companyname
FROM customers
WHERE customerid =
(SELECT customerid FROM orders
INNER JOIN order_details ON order_details.orderid = orders.orderid
WHERE quantity >= 50);
如果沒有加上ANY，那他就只能比對一行資料。
如下圖的錯誤。
SELECT companyname
FROM customers
WHERE customerid = ANY
(SELECT customerid
FROM orders
INNER JOIN order_details ON order_details.orderid = orders.orderid
WHERE quantity >= 50);
LAB 找到訂單中只有一個項目的供應商
Suppliers只有一個order
SELECT companyname
FROM suppliers
WHERE supplierid = ()
條件，order只有一個項目。
SELECT supplierid
FROM order_details
WHERE quantity =1
要怎麼連接兩張表，這邊可以透過product去做連接。
因為order_details 沒有supplierid。但 products有。
order_details & products有共同的productid可以連接。
SELECT supplierid
FROM order_details
INNER JOIN products ON products. productid = order_details. productid
WHERE quantity =1
合併
多個條件，加上ANY
SELECT companyname
FROM suppliers
WHERE supplierid = ANY
(SELECT supplierid FROM order_details
INNER JOIN products ON products. productid = order_details. productid
WHERE quantity =1);
換成 ALL ，完全一樣的語法。
SELECT 欄位
FROM table
WHERE 條件 operator ALL
(SELECT 欄位 FROM table WHERE條件)
Operator 指的是 = , > , < , >=, <= ,!=
EXISTS 主要就是EXISTS 以及NOT EXISTS，就只有 是，不是而已。
他會選擇所有匹配的紀錄。
ANY & ALL都是用在子查詢。
兩者最大的區別是，ANY只要一項符合就OK。ALL 要全部項目符合。
找到ORDER_DETAILS內產品金額 高於各項產品統計後最大訂單平均金額的產品
(訂單金額只有order_detail有)
可能訂單平均值是10000，但是有其中幾張訂單的平均值遠高於
但我們要顯示productname，所以要先JOIN。
SELECT productname
FROM products
INNER JOIN order_details ON order_detailers.productid = products.productid
WHERE order_details.unitprice*quantity > ALL
()
子查詢條件
SELECT AVG(order_details.unitprice*quantity)
FROM orders_details
GROUP BY productid
分組處理一下，依照productid來分類。
這邊的group by 會依照productid區分組別，。
合併
SELECT productname
FROM products
INNER JOIN order_details ON order_details.productid = products.productid
WHERE order_details.unitprice*quantity > ALL
(SELECT AVG(order_details.unitprice*quantity)
FROM order_details
GROUP BY productid );
ANY只要這邊的產品金額大於任意一個產品平均值就會顯示。
用ALL表示，這邊的產品金額要大於所有商品內最大的平均值金額。
例如:
子查詢內找到 產品13號是最大的平均值金額。假設10000。
那我父查詢這邊就是我商品的金額要大於這個產品13的最大金額才會顯示。
SELECT productname, order_details.unitprice, order_details.quantity
FROM products
INNER JOIN order_details ON order_details.productid = products.productid
WHERE order_details.unitprice*quantity > ALL
(SELECT AVG(order_details.unitprice*quantity)
FROM order_details
GROUP BY productid );
這邊會發現有多個訂單的產品賣出金額，超過平均值。
所以用ALL在比較的時候，產品賣出的金額大於所有平均值的。
訂單有時候的單價會不一樣。
這邊會算出產品1 每張單加起來的總額的平均值。
這邊可以看到產品1的單價是18元。
這邊可以看到許多單超過375元。
表示群組抓出來的確實是平均值。
最後因為重複的品項太多。
我們用DISTINCT來把一樣的篩選掉。
SELECT DISTINCT productname
FROM products
INNER JOIN order_details ON order_details.productid = products.productid
WHERE order_details.unitprice*quantity > ALL
(SELECT AVG(order_details.unitprice*quantity)
FROM order_details
GROUP BY productid );
這邊換成ANY就是不一樣的結果
只要這邊的產品金額大於任意一個產品平均值就會顯示。
LAB 找到一個客戶，購買的東西價格高於平均值。
SELECT DISTINCT companyname
FROM customers
INNER JOIN orders ON customers.customerid = orders.customerid
INNER JOIN order_details ON order_details.orderid = orders.orderid
WHERE order_details.unitprice*quantity > ALL
(SELECT AVG(order_details.unitprice*quantity)
FROM order_details
JOIN orders ON order_details.orderid = orders.orderid
GROUP BY customerid );
這邊是要找客戶，所以先找customers表。
因為最後是要算出購買金額的平均值，
必須要有unitprice & quantity的值。
這兩個的值必須抓order_details表。 這其中透過orders做橋梁連接。
SELECT DISTINCT companyname
FROM customers
INNER JOIN orders ON customers.customerid = orders. customerid
INNER JOIN order_details ON order_details.order.id = orders.orderid
WHERE order_details.unitprice*quantity > ALL
子查詢的資料
要從unitprice & quantity找出平均值。
所以採用order_detail的表。
但因為要以customerid 作為分類的條件(題目是以客戶為主，所以要先分出來)，
所以把orders這張表帶進來。
SELECT AVG(order_details.unitprice*quantity)
FROM order_details
JOIN orders ON order_details.order.id = orders.orderid
GROUP BY customerid
ANY 與 ALL的比較。
這邊用學生來比較比較簡單。
例如說: A班 VS B班，查詢A班年紀大於B班的。
用ALL來說。查詢A班當中，年齡大於B班所有人的人。
用ANY來說。查詢A班當中，年齡大於B班任意一個人的人。
上面有用到金額的平均值來比較。
產品金額 VS 產品的平均值
用ALL 來說，產品金額，大於所有產品平均值的。
SELECT * FROM TABEL WHERE MONEY > ALL (SELECT MONEY FROM TABLE)
等於
SELECT * FROM TABEL WHERE MONEY > (SELECT MAX(MONEY) FROM TABLE)
用ANY來說，產品金額，大於任意一個平均值的。
SELECT * FROM TABEL WHERE MONEY > ANY (SELECT MONEY FROM TABLE)
等於
SELECT * FROM TABEL WHERE MONEY > (SELECT MIN(MONEY) FROM TABLE)
SELECT * FROM TABEL WHERE MONEY = ANY (SELECT MONEY FROM TABLE)
等於
SELECT * FROM TABEL WHERE MONEY IN (SELECT MONEY) FROM TABLE)
IN 的子查詢。
SELECT 欄位
FROM table
WHERE 欄位 IN (SELECT statement)
選擇的值，必須與IN裡面的匹配。
主要就是比較裡面有沒有相同的值。
我們將找到與供應商在同一個國家地區的客戶。
SELECT companyname
FROM customers
WHERE country IN (SELECT country FROM suppliers);
他會找到兩邊一樣的值，然後拉出來。
LAB 找到與供應商在同CITY的客戶。
SELECT companyname
FROM suppliers
WHERE city IN
(SELECT city FROM customer);
這邊會先在IN裡面拉出city欄位。
讓他作為條件，然後去比對suppliers內有沒有相同CITY的suppliers。
他就是一層一層迴圈式的去找，
最終找到五家客戶&供應商在同一個CITY的資料。
<本篇完>
第一篇: IT讀書室-SQL語法學習- postgre安裝與匯入
第四篇: IT讀書室-SQL語法學習- 中間的SELECT語句
如果筆記造成任何法律或者侵權的問題，作者會立即將筆記下架。
留言列表
IT記事本 (4)
