MySQL中常用的查詢子句有哪些

# MySQL中常用的查詢子句有哪些

## 引言

MySQL作為最流行的關系型數據庫管理系統之一，其強大的查詢功能是開發者日常工作中不可或缺的工具。掌握各種查詢子句的使用方法，能夠幫助我們更高效地從數據庫中提取、分析和處理數據。本文將詳細介紹MySQL中常用的查詢子句，包括它們的基本語法、使用場景以及實際示例。

---

## 1. SELECT子句

### 1.1 基本語法
```sql
SELECT column1, column2, ...
FROM table_name;

1.2 功能說明

• 用于指定要從表中檢索的列
• 可以使用*選擇所有列
• 支持列別名(AS關鍵字可省略)

1.3 實際示例

-- 選擇特定列
SELECT first_name, last_name FROM employees;

-- 使用列別名
SELECT product_name AS name, unit_price * 0.9 AS discount_price FROM products;

-- 計算字段
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

1.4 高級用法

• DISTINCT去重
• 聚合函數(COUNT, SUM, AVG等)
• 窗口函數(MySQL 8.0+)

2. FROM子句

2.1 基本語法

FROM table_name [AS alias]

2.2 功能說明

• 指定查詢的數據來源表
• 支持多表連接
• 可以使用表別名簡化查詢

2.3 實際示例

-- 單表查詢
SELECT * FROM orders;

-- 使用表別名
SELECT e.employee_id, e.last_name 
FROM employees AS e;

-- 多表連接
SELECT o.order_id, c.customer_name
FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

3. WHERE子句

3.1 基本語法

WHERE condition

3.2 功能說明

• 過濾行數據
• 支持多種比較運算符(=, <>, >, <等)
• 支持邏輯運算符(AND, OR, NOT)

3.3 實際示例

-- 簡單條件
SELECT * FROM products WHERE price > 100;

-- 組合條件
SELECT * FROM employees 
WHERE department = 'Sales' AND salary > 5000;

-- 使用IN操作符
SELECT * FROM customers 
WHERE country IN ('USA', 'Canada', 'Mexico');

-- 模糊查詢
SELECT * FROM products 
WHERE product_name LIKE '%Laptop%';

3.4 高級用法

• BETWEEN范圍查詢
• IS NULL判斷空值
• 子查詢作為條件

4. GROUP BY子句

4.1 基本語法

GROUP BY column1, column2, ...

4.2 功能說明

• 對結果集進行分組
• 通常與聚合函數配合使用
• MySQL允許SELECT非聚合列(但可能產生意外結果)

4.3 實際示例

-- 簡單分組
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

-- 多列分組
SELECT country, city, COUNT(*) AS customer_count
FROM customers
GROUP BY country, city;

-- 配合HAVING使用
SELECT product_id, SUM(quantity) AS total_sold
FROM order_details
GROUP BY product_id
HAVING total_sold > 100;

5. HAVING子句

5.1 基本語法

HAVING condition

5.2 功能說明

• 對分組結果進行過濾
• 類似WHERE但作用于分組后數據
• 可以使用聚合函數作為條件

5.3 實際示例

-- 過濾分組結果
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING order_count > 5;

-- 復雜條件
SELECT product_category, AVG(price) AS avg_price
FROM products
GROUP BY product_category
HAVING avg_price > 100 AND COUNT(*) > 10;

6. ORDER BY子句

6.1 基本語法

ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...

6.2 功能說明

• 對結果集進行排序
• ASC升序(默認)，DESC降序
• 支持多列排序

6.3 實際示例

-- 簡單排序
SELECT * FROM products ORDER BY price DESC;

-- 多列排序
SELECT first_name, last_name, hire_date
FROM employees
ORDER BY department ASC, hire_date DESC;

-- 使用表達式排序
SELECT product_name, unit_price * units_in_stock AS stock_value
FROM products
ORDER BY stock_value DESC;

7. LIMIT子句

7.1 基本語法

LIMIT [offset,] row_count

7.2 功能說明

• 限制返回的行數
• 常用于分頁查詢
• MySQL特有語法(其他數據庫可能使用TOP或ROWNUM)

7.3 實際示例

-- 限制返回行數
SELECT * FROM customers LIMIT 10;

-- 分頁查詢
SELECT * FROM products 
ORDER BY product_id
LIMIT 20 OFFSET 40;  -- 等價于LIMIT 40, 20

-- 與ORDER BY配合使用
SELECT * FROM orders
WHERE order_date > '2023-01-01'
ORDER BY total_amount DESC
LIMIT 5;

8. JOIN子句

8.1 基本語法

FROM table1 
[JOIN_TYPE] JOIN table2 
ON table1.column = table2.column

8.2 連接類型

• INNER JOIN(默認)：返回匹配的行
• LEFT JOIN：返回左表所有行+匹配的右表行
• RIGHT JOIN：返回右表所有行+匹配的左表行
• FULL JOIN：MySQL不直接支持，需模擬實現
• CROSS JOIN：笛卡爾積

8.3 實際示例

-- 內連接
SELECT o.order_id, c.customer_name
FROM orders o INNER JOIN customers c 
ON o.customer_id = c.customer_id;

-- 左外連接
SELECT e.employee_name, d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;

-- 多表連接
SELECT o.order_id, c.customer_name, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id;

9. UNION子句

9.1 基本語法

SELECT_statement1
UNION [ALL]
SELECT_statement2

9.2 功能說明

• 合并多個查詢結果
• UNION會去重，UNION ALL保留所有行
• 要求列數和數據類型兼容

9.3 實際示例

-- 合并兩個查詢結果
SELECT product_id, product_name FROM current_products
UNION
SELECT product_id, product_name FROM discontinued_products;

-- 使用UNION ALL提高性能(當確定無重復時)
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

10. 子查詢

10.1 基本語法

SELECT * FROM table1 
WHERE column1 IN (SELECT column1 FROM table2)

10.2 使用場景

• WHERE子句中的子查詢
• FROM子句中的派生表
• SELECT子句中的標量子查詢
• 與EXISTS/NOT EXISTS配合使用

10.3 實際示例

-- WHERE中的子查詢
SELECT * FROM products
WHERE category_id IN (
    SELECT category_id FROM categories 
    WHERE category_name LIKE '%Electronics%'
);

-- FROM中的派生表
SELECT avg_sales.category, avg_sales.avg_amount
FROM (
    SELECT category, AVG(sales_amount) AS avg_amount
    FROM sales_data
    GROUP BY category
) AS avg_sales
WHERE avg_sales.avg_amount > 1000;

-- 相關子查詢
SELECT e.employee_id, e.last_name
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.employee_id = e.employee_id
    AND o.order_date > '2023-01-01'
);

11. 窗口函數(MySQL 8.0+)

11.1 基本語法

function_name OVER (
    [PARTITION BY partition_expression, ...]
    [ORDER BY sort_expression [ASC|DESC], ...]
    [frame_clause]
)

11.2 常用窗口函數

• 排名函數：ROW_NUMBER(), RANK(), DENSE_RANK()
• 聚合函數：SUM(), AVG(), COUNT()等
• 分布函數：PERCENT_RANK(), CUME_DIST()
• 前后函數：LAG(), LEAD()

11.3 實際示例

-- 計算移動平均
SELECT 
    order_date,
    daily_sales,
    AVG(daily_sales) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM sales_data;

-- 部門內薪資排名
SELECT 
    employee_id,
    last_name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_salary_rank
FROM employees;

12. WITH子句(CTE)

12.1 基本語法

WITH cte_name AS (
    SELECT_statement
)
SELECT * FROM cte_name;

12.2 功能說明

• 定義臨時命名結果集(公用表表達式)
• 提高復雜查詢的可讀性
• 支持遞歸查詢(處理層次結構數據)

12.3 實際示例

-- 簡單CTE
WITH high_value_customers AS (
    SELECT customer_id, SUM(order_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
    HAVING total_spent > 10000
)
SELECT c.customer_name, h.total_spent
FROM customers c JOIN high_value_customers h
ON c.customer_id = h.customer_id;

-- 遞歸CTE(查詢組織結構)
WITH RECURSIVE org_hierarchy AS (
    -- 基礎查詢(頂級管理者)
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 遞歸查詢(下屬員工)
    SELECT e.employee_id, e.name, e.manager_id, h.level + 1
    FROM employees e JOIN org_hierarchy h
    ON e.manager_id = h.employee_id
)
SELECT * FROM org_hierarchy
ORDER BY level, employee_id;

總結

MySQL提供了豐富的查詢子句，從基礎的SELECT、WHERE到高級的窗口函數和CTE，能夠滿足各種復雜的數據查詢需求。掌握這些子句的用法和適用場景，可以顯著提高數據庫查詢的效率和靈活性。在實際應用中，我們常常需要組合使用多個子句來構建復雜的查詢語句，因此理解每個子句的執行順序和作用范圍尤為重要。

查詢子句執行順序

1. FROM/JOIN
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT

通過合理運用這些查詢子句，我們可以從MySQL數據庫中高效地提取、轉換和分析數據，為業務決策提供有力支持。 “`