資料庫與快取 / ENGINEERING NOTES

SQL 查詢入門:WHERE、HAVING、GROUP BY、JOIN 種類與子查詢

整理 SQL 常用查詢語法:WHERE 與 HAVING 的差異、GROUP BY、INNER/OUTER JOIN 的結果差別,以及子查詢與聚合函數。

SQLWHEREHAVINGGROUP BY

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 查詢計劃,找出並改善查詢效率低下的部分。

TY的智慧庫

你有事?
問前想清楚,機會不是誰都有。

💡 建議主題:

放大圖片