Mysql

MySQL执行底层原理

查询结构

代码块

#方式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 ...,...​
#其中：​
#（1）from：从哪些表中筛选​
#（2）on：关联多表查询时，去除笛卡尔积​
#（3）where：从表中筛选的条件​
#（4）group by：分组依据​
#（5）having：在统计结果中再次筛选​
#（6）order by：排序​
#（7）limit：分页​

SELECT执行顺序

代码块

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT​

代码块

SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5​
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1​
WHERE height > 1.80 # 顺序 2​
GROUP BY player.team_id # 顺序 3​
HAVING num > 2 # 顺序 4​
ORDER BY num DESC # 顺序 6​
LIMIT 2 # 顺序 7​

Mysql​

Mysql