1.书写顺序
SELECT …
FROM …
WHERE …
GROUP BY …
HAVING …
ORDER BY …
LIMIT…
--方式1:
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
--方式2:
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
2.执行顺序
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT
例如:
SELECT DISTINCT depart_name, MAX(salary) # 顺序 5
FROM emp JOIN depart ON emp.depart_id = depart.depart_id # 顺序 1
WHERE emp.birthday > '1990-01-01' # 顺序 2
GROUP BY depart.depart_name # 顺序 3
HAVING MAX(salary) > 5000 # 顺序 4
ORDER BY MAX(salary) DESC # 顺序 6
LIMIT 2 # 顺序 7
从执行顺序上可以看到,FROM执行早于WHERE早于SELECT,因此在FROM子句定义的表的别名可以在WHERE中使用,而SELECT定义的列的别名不可以在WHERE使用。