快捷導(dǎo)航

MySQL查詢SQL語句的執(zhí)行順序的方法

更新時(shí)間：2025年10月20日 08:49:50 作者：學(xué)亮編程手記

本文詳細(xì)介紹了MySQL SELECT語句各子句的邏輯執(zhí)行順序及各階段作用,并結(jié)合常見場(chǎng)景說明執(zhí)行流程,強(qiáng)調(diào)WHERE與HAVING區(qū)別、別名使用限制、JOIN條件及子查詢執(zhí)行特點(diǎn),對(duì)優(yōu)化和理解SQL查詢有重要指導(dǎo)意義,需要的朋友可以參考下

標(biāo)準(zhǔn)執(zhí)行順序

MySQL在執(zhí)行一條SELECT查詢時(shí)，邏輯上的執(zhí)行順序如下：

FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

讓我詳細(xì)解釋每個(gè)階段：

1. FROM階段

首先確定數(shù)據(jù)來源，包括表連接操作。如果有JOIN，會(huì)先執(zhí)行笛卡爾積或根據(jù)JOIN條件進(jìn)行表連接。多個(gè)表的JOIN按從左到右的順序執(zhí)行。

2. WHERE階段

對(duì)FROM階段產(chǎn)生的結(jié)果集進(jìn)行條件過濾，篩選出符合條件的行。此時(shí)還不能使用SELECT中定義的別名，因?yàn)镾ELECT還沒執(zhí)行。

3. GROUP BY階段

按照指定的列對(duì)數(shù)據(jù)進(jìn)行分組，為聚合函數(shù)的計(jì)算做準(zhǔn)備。

4. HAVING階段

對(duì)分組后的結(jié)果進(jìn)行過濾，通常與聚合函數(shù)配合使用。HAVING可以使用SELECT中的別名（某些MySQL版本支持）。

5. SELECT階段

選擇要返回的列，執(zhí)行計(jì)算、函數(shù)調(diào)用等操作，生成最終的列。

6. DISTINCT階段

如果有DISTINCT關(guān)鍵字，對(duì)結(jié)果集去重。

7. ORDER BY階段

對(duì)結(jié)果集進(jìn)行排序?？梢允褂肧ELECT中定義的別名。

8. LIMIT階段

限制返回的行數(shù)，通常用于分頁。

常見場(chǎng)景示例

場(chǎng)景1：基礎(chǔ)查詢

SELECT name, age
FROM users
WHERE age > 18
ORDER BY age DESC
LIMIT 10;

執(zhí)行順序：FROM users → WHERE age > 18 → SELECT name, age → ORDER BY age DESC → LIMIT 10

場(chǎng)景2：聚合查詢

SELECT department, COUNT(*) as emp_count, AVG(salary) as avg_salary
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY avg_salary DESC;

執(zhí)行順序：FROM employees → WHERE status = ‘active’ → GROUP BY department → HAVING COUNT() > 5 → SELECT department, COUNT(), AVG(salary) → ORDER BY avg_salary DESC

場(chǎng)景3：多表JOIN

SELECT u.name, o.order_date, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.order_date > '2025-01-01'
ORDER BY o.order_date DESC;

執(zhí)行順序：FROM users u → INNER JOIN orders o → WHERE o.order_date > ‘2025-01-01’ → SELECT u.name, o.order_date, o.amount → ORDER BY o.order_date DESC

場(chǎng)景4：子查詢

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

執(zhí)行順序：先執(zhí)行子查詢得到平均工資 → FROM employees → WHERE salary > 子查詢結(jié)果 → SELECT name, salary → ORDER BY salary DESC

場(chǎng)景5：DISTINCT去重

SELECT DISTINCT city
FROM customers
WHERE country = 'China'
ORDER BY city;

執(zhí)行順序：FROM customers → WHERE country = ‘China’ → SELECT city → DISTINCT → ORDER BY city

場(chǎng)景6：復(fù)雜的多表關(guān)聯(lián)與分組

SELECT d.name as dept_name, COUNT(e.id) as employee_count
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id AND e.status = 'active'
WHERE d.region = 'East'
GROUP BY d.id, d.name
HAVING employee_count > 0
ORDER BY employee_count DESC
LIMIT 5;

執(zhí)行順序：FROM departments d → LEFT JOIN employees e (包含ON條件) → WHERE d.region = ‘East’ → GROUP BY d.id, d.name → HAVING employee_count > 0 → SELECT d.name, COUNT(e.id) → ORDER BY employee_count DESC → LIMIT 5