資料庫與快取 / ENGINEERING NOTES
SQL 查詢入門:WHERE、HAVING、GROUP BY、JOIN 種類與子查詢
整理 SQL 常用查詢語法:WHERE 與 HAVING 的差異、GROUP BY、INNER/OUTER JOIN 的結果差別,以及子查詢與聚合函數。
SQL WHERE & HAVING Clauses
Overview
本文件詳盡解釋了 SQL 中的 WHERE 與 HAVING 子句,分別介紹如何過濾原始資料與分組後的結果。文中提供了多個應用實例,說明各自的使用場景和注意事項。
WHERE Clause
WHERE 子句用於根據條件篩選資料表中的原始行。它只能作用於未經聚合的資料。
Examples
SELECT * FROM employees WHERE salary > 50000;
SELECT * FROM employees WHERE age > 30 AND department = 'IT';
SELECT * FROM employees WHERE department IN ('IT', 'HR', 'Finance');
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;
SELECT * FROM employees WHERE last_name LIKE 'Sorane%';
SELECT * FROM employees WHERE middle_name IS NULL;
-- 將 first_name 欄位的值轉換為大寫,然後與 'JOHN' 進行比較。
SELECT * FROM employees WHERE UPPER(first_name) = 'JOHN';
Using the LIKE Operator
LIKE 用於進行模糊查詢:
SELECT birth FROM customer WHERE birth LIKE '1995%';
也可以用來對日期進行篩選:
SELECT birth FROM customer WHERE YEAR(birth) = 1995;
HAVING Clause
HAVING 子句用於在 GROUP BY 後對聚合後的資料進行篩選。必須先使用 GROUP BY 才能使用 HAVING。
GROUP BY Basics
當使用聚合函數時,SELECT 中所有的非聚合欄位必須包含在 GROUP BY 中。
Single Column Grouping
SELECT price FROM order_to_meal GROUP BY price;
Multiple Columns Grouping
SELECT price, meal_id FROM order_to_meal GROUP BY price, meal_id ORDER BY meal_id DESC;
SELECT price, meal_id, order_id FROM order_to_meal GROUP BY price, meal_id, order_id ORDER BY order_id DESC;
Aggregation Filtering with HAVING
按部門篩選平均工資大於 50000 的記錄:
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;
使用多個條件進行過濾:
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000 AND COUNT(*) > 10;
計算和過濾:
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING COUNT(*) * AVG(salary) > 500000;
使用別名過濾:
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 50000;
確認聚合結果不為 NULL:
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) IS NOT NULL;
SQL Injection
SQL Injection 是「把惡意 SQL 當作使用者輸入」,讓程式錯誤地執行攻擊者想要的查詢。
SQL JOIN 種類:INNER JOIN、LEFT/RIGHT OUTER JOIN 與 FULL OUTER JOIN 差別
Overview
本文介紹了 SQL 中不同類型的 JOIN 操作,包括 INNER JOIN、LEFT/RIGHT OUTER JOIN 以及 FULL OUTER JOIN,並通過示例程式碼幫助理解各自的使用場景。
INNER JOIN
INNER JOIN 僅返回兩個表中具有匹配關係的行,未匹配的行將被忽略。
Example
SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;
OUTER JOIN
OUTER JOIN 返回一個表中的所有行,即使另一個表中沒有匹配。常見的有:
LEFT OUTER JOIN
又稱 LEFT JOIN,返回左表中的所有行,以及右表中與之匹配的行,未匹配部分以 NULL 補齊。
SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.column = table2.column;
RIGHT OUTER JOIN
返回右表中的所有行,以及左表匹配的部分:
SELECT * FROM table1 RIGHT OUTER JOIN table2 ON table1.column = table2.column;
FULL OUTER JOIN
返回左右兩邊所有行,未匹配部分以 NULL 補齊,即 LEFT JOIN 與 RIGHT JOIN 結果的聯集。
SELECT *
FROM table1
FULL JOIN table2 ON table1.column = table2.column;
SQL 子查詢與聚合函數:WHERE 子查詢、COUNT、SUM 與進階技巧
子查詢
在查詢的 WHERE 中使用某變數 = (SELECT ...)就是子查詢的唯一用法。
子查詢是一種查詢條件,所以遵照原本 WHERE 的用法。
SELECT username
FROM customer
WHERE id = (SELECT MAX(id) FROM customer);
聚合函數
以下是常見的聚合函數:
- COUNT(): 用於計算行數或非 NULL 值的數量。
- SUM(): 用來計算數值列的總和。
- AVG(): 用於計算數值列的平均值。
- MIN(): 用來找出數值列的最小值。
- MAX(): 用來找出數值列的最大值。
- GROUP_CONCAT(): 用於將行合併成一個字串,通常與 GROUP BY 一起使用。
只能在 SELECT 後面放聚合函數。
計算 order_to_meal 表的總行數。
SELECT COUNT(*) FROM order_to_meal;
計算 quantity 欄位 非 null 欄位共幾個。
SELECT COUNT(quantity) FROM order_to_meal;
計算 quantity 欄位數字加總。
SELECT SUM(quantity) FROM order_to_meal;
如果故意 SUM()放一個被能加總的欄位? 出現 error:Operand data type xxx is invalid for sum operator.
聚合函數也可以搭配 WHERE 限定搜尋條件範圍。
SELECT COUNT(*) FROM order_to_meal WHERE quantity = 6;
SELECT SUM(quantity) FROM order_to_meal WHERE quantity = 7;
進階查詢技巧與注意事項
- 子查詢效能: 大量資料查詢時,建議優化子查詢或考慮 JOIN 替代,以避免效能瓶頸。
- 類型匹配: 確認運算欄位資料類型正確,避免使用不支援聚合的欄位導致錯誤。
- 查詢優化: 利用 EXPLAIN 查詢計劃,找出並改善查詢效率低下的部分。