MySQL 查询语句的执行顺序
<p><span data-cke-copybin-start="1"></span>整理了下MySQL查询语句的执行顺序。</p><pre class="language-sql highlighter-hljs"><code>(1) FROM
(2) ON
(3) JOIN
(4) WHERE
(5) GROUP BY
(6) WITH (CUBE|ROLLUP)
(7) HAVING
(8) SELECT
(9) DISTINCT
(10) ORDER BY
(11) LIMIT</code></pre>
<h3>(1) FROM 子句 - <strong>首先执行</strong></h3>
<pre class="language-sql highlighter-hljs"><code>FROM employees e</code></pre>
<ul>
<li>MySQL 会先读取 FROM 子句中的表信息</li>
</ul>
<h3>(2) ON 条件 - <strong>连接条件过滤</strong></h3>
<pre class="language-sql highlighter-hljs"><code>JOIN departments d ON e.dept_id = d.id</code></pre>
<ul>
<li>对连接表的行进行匹配</li>
<li>只有满足 ON 条件的行才会被保留</li>
</ul>
<h3>(3) JOIN 操作 - <strong>执行表连接</strong></h3>
<ul>
<li>MySQL 支持多种连接方式：INNER JOIN、LEFT/RIGHT JOIN、CROSS JOIN</li>
</ul>
<h3>(4) WHERE 条件 - <strong>行级过滤</strong></h3>
<pre class="language-sql highlighter-hljs"><code>WHERE e.salary > 5000 AND d.location = 'NY'</code></pre>
<ul>
<li>此时不能使用 SELECT 中的别名</li>
<li>不能使用聚合函数（如 COUNT, SUM 等）</li>
</ul>
<h3>(5) GROUP BY - <strong>分组操作</strong></h3>
<pre class="language-sql highlighter-hljs"><code>GROUP BY d.name, e.position</code></pre>
<ul>
<li>可以 GROUP BY 不在 SELECT 中的列</li>
<li>分组后每组生成一行结果</li>
</ul>
<h3>(6) WITH CUBE/ROLLUP - <strong>生成超组</strong></h3>
<pre class="language-sql highlighter-hljs"><code>GROUP BY d.name WITH ROLLUP</code></pre>
<ul>
<li>生成小计和总计行</li>
</ul>
<h3>(7) HAVING - <strong>分组后过滤</strong></h3>
<pre class="language-sql highlighter-hljs"><code>HAVING AVG(e.salary) > 6000</code></pre>
<ul>
<li>可以使用聚合函数</li>
<li>可以使用 SELECT 中的别名</li>
</ul>
<h3>(8) SELECT - <strong>选择输出列</strong></h3>
<pre class="language-sql highlighter-hljs"><code>SELECT d.name, AVG(e.salary) as avg_sal</code></pre>
<ul>
<li>计算表达式和函数调用</li>
<li>定义列别名</li>
</ul>
<h3>(9) DISTINCT - <strong>去重操作</strong></h3>
<pre class="language-sql highlighter-hljs"><code>SELECT DISTINCT department</code></pre>
<ul>
<li>MySQL 可能在 GROUP BY 时就已经去重</li>
</ul>
<h3>(10) ORDER BY - <strong>结果排序</strong></h3>
<pre class="language-sql highlighter-hljs"><code>ORDER BY avg_sal DESC</code></pre>
<ul>
<li>可以使用 SELECT 中定义的别名</li>
<li>对最终结果集排序，性能消耗较大</li>
</ul>
<h3>(11) LIMIT - <strong>结果限制</strong></h3>
<pre class="language-sql highlighter-hljs"><code>LIMIT 10 OFFSET 5</code></pre>
<ul>
<li>MySQL 特有语法，其他数据库可能用不同方式</li>
<li>限制返回的行数</li>
</ul>
<p style="text-align: right"><span style="color: rgba(53, 152, 219, 1)">恐惧不是真实的，它只是对未来的一种自我暗示，是我们心灵的产物。-- 烟沙九洲</span></p>
<div>
<div style="position: fixed; right: 0; top: 0; width: 18px; height: 100%; z-index: 999999; pointer-events: none; background-image: url("data:,"); background-size: 100% 100%; background-repeat: no-repeat"> </div>
<button class="move-button" style="display: none; position: fixed; right: 18px; left: auto; top: 50%; z-index: 999999">◀</button></div><br><br>
来源：https://www.cnblogs.com/yanshajiuzhou/p/18903288
頁:
[1]