你可能不知道的 SQL 小筆記(查詢篇)
Published in
8 min readJan 3, 2020
在使用 SQL 時,可能會漏掉一些小觀念,這些觀念可能是在操作時會無意間忽略的。因此本篇文章特別整理了一些可能會被忽略的技巧。
選擇數據
DISTINCT
會限制所有的欄位,並不單單只是套用在跟隨著欄位。除非所有選定的欄位都一致,不然仍然會顯示部分不一致的欄位。ORDER BY
應該要出現在最後一行,避免出現 Error。ORDER BY
能夠進行複數欄位的排列,只要以逗號分隔即可。如果想要對多欄進行降序,必須在每一欄都指定一次,例如:
ORDER BY A_Column ASC, B_Column DESC;
篩選數據
- AS 盡量於 SQL 中使用,可用於簡化欄位。
- 如果要檢驗某個區間的資料,可以使用
BETWEEN
。用法為:
WHERE A_Column BETWEEN 10 AND 20。
- 要檢查是否有 NULL,可以使用
WHERE A_Col IS NULL
。 - 在進行 WHERE 條件篩選時,為避免錯誤篩選,務必將複合條件以括號分開。如果沒有括號,SQL 的判斷順序為 () > AND > OR
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')
AND prod_price >= 10;
IN
用來取得符合特定條件的資料,可用來取代OR
進行重複的指令。NOT
會加在條件前面,例如:WHERE NOT vend_id = 'DLL01'
用來查找特定字元的方式有(NULL 例外):
- LIKE:能夠找到匹配的資料
- %:前方或後方不特定的字元長度(可為零),或是中間
WHERE prod_name LIKE 'F%y'
- _:特定字元長度
拼接資料格:
- 拼接資料格(Concatenate),使用
||
或是+
來連接資料: - 用
RTRIM()
可以把輸出的空格全部取消。
SELECT vend_name + ' (' + vend_country + ')' -- Majority of DB SELECT vend_name || ' (' || vend_country || ')'
SELECT Concat(vend_name, ' (', vend_country, ')') -- MYSQL Bear Emporium (USA )--AFTER RTRIM
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'Bear Emporium (USA)
函數使用(Aggregation):
COUNT(column)
會忽略 NULL,COUNT(*)
則不會忽略- 對不同的列進行計算,默認會加上 ALL 在欄位前方,否則要加上
DISTINCT
。 - 如果指定列名,用
COUNT()
,會忽略其中 NULL 的值;相反地,用COUNT(*)
則不忽略 NULL。 - 如果指定列名,則 DISTINCT 只能用
COUNT()
,因為會忽略 NULL。 SELECT
可包含多個函數(如AVG
、MIN
等)。
群組(Group)
- 大多 DBS 不允許 GROUP BY 帶有可變長度的欄位,如字串。
- 如果在
SELECT
中使用表達式,則必須在GROUP BY
中使用相同的表達式,不能用簡稱。 GROUP BY
必須出現在WHERE
之後,ORDER BY
之前。- 如果必須要在分組後在進行篩選,則使用
HAVING
。
子查詢(Subquery)
- 子查詢不能相嵌太多層,會嚴重限制性能,而且有時子查詢也會有閱讀上的困難。
技巧差異:
- 使用 WHERE:
FROM Customers
WHERE cust_id IN (SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01'));
- 使用 SELECT:
SELECT cust_name, cust_state,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;
WHERE Orders.cust_id = Customers.cust_id
完全限定列名,當名稱相同時,就必須用前綴來限定特定列名。
關連表 JOIN
- 主鍵(Primary Key)用於視為該表格中的唯一值,用以辨別個別資料的獨特性。
- AS 所使用的別名可以使用在任何地方,無論是
SELECT
、WHERE
或是FROM
。 - 創建關連的最簡單形式,就是把兩張表一起讀取(亦即,表的關連式會以 WHERE 子句來串連):
# 三個表格關連
/* WHERE 前面兩個用於關連三個表格,最後一個則用於限定條件*/SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;
- 可以使用子查詢的方式來設定條件式,同樣也能夠透過自連結(Self-Join)進行兩次查詢。
可以自行測試自關連或是子查詢何者效能更好,以作為常態查詢方式。
SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
FROM Customers
WHERE cust_contact = ‘Jim Jones’);-- Self Join
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = ‘Jim Jones’;
UNION
必須:
- 由兩條或兩條以上的
SELECT
語句組成 - 兩個
SELECT
選擇的列應該要完全一樣 - 列數據類型必須為可以兼容的類別,例如數值或日期。
UNION ALL
會返回兩個表設定條件的所有資料,因此可能會出現重複資料。UNION
幾乎總是用來完成多重 WHERE
的條件。
- UNION 中有兩個高級關連方式:
- EXCEPT:A 表存在但 B 表不存在的內容
- INTERSECT:A、B 表都存在的內容
視圖(View Table):
- 視圖是一種查詢快捷,同時也能滿足不同資料格式的呈現。
- 創建視圖
CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;
以上就是 SQL 在查詢時,容易忘記的一些小觀念,本文可以作為參考與複習使用。
謝謝你/妳,願意把我的文章閱讀完
如果你喜歡筆者在 Medium 的文章,可以拍個手(Claps),最多可以按五個喔!也歡迎你分享給你覺得有需要的朋友們。
參考資料:
- SQL 必知必會
- SQL Join的一些总结|黄钧航(JKhuang)