一、用户手写
SELECT DISTINCT <select-list>
FROM <left-table>
<join-type> JOIN <right-table> ON <join-condition>
WHERE <where-condition>
GROUP BY <group-by-list>
HAVING <having-condition>
ORDER BY <order-by-condition>
LIMIT <limit-number>
二、DBMS执行:从FROM开始
FROM <left-table> ON <join-condition>
<join-type> JOIN <right-table>
WHERE <where-condition>
GROUP BY <group-by-list>
HAVING <having-condition>
SELECT DISTINCT <select-list>
ORDER BY <order-by-condition>
LIMIT <limit-number>
三、目的:减小表的规模,缩小操作的数据的规模,提高效率
首先from
字句找到要使用的所有表,之后用选择where
来筛选符合条件的元组,这样就可以减少元组数(行数),之后分组group by
实现物以类聚,分组后having
再筛选也可以减少元组数,终于可以进行投影select
咯,select
减少属性数(列数),之后排序字句order by
提高可读性,最后limit
分页显示。
select
在distinct
之前执行。