在學(xué)習(xí)sql的過程中發(fā)現(xiàn)一篇簡(jiǎn)潔有力的描述sql語句執(zhí)行順序干貨文章汗侵，特此在此做個(gè)筆記記錄一下文章重點(diǎn)用來復(fù)習(xí)催什。原文地址http://www.jellythink.com/archives/924
1.一個(gè)常見的sql語句
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>
2.SQL邏輯查詢語句執(zhí)行順序
(7) SELECT
(8) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>
3.sql實(shí)例
SELECT a.customer_id, COUNT(b.order_id) as total_orders
FROM table1 AS a LEFT JOIN table2 AS b
ON a.customer_id = b.customer_id
WHERE a.city = 'hangzhou' GROUP BY a.customer_id
HAVING count(b.order_id) < 2 ORDER BY total_orders DESC;
詞句sql的作用是：獲得來自杭州，并且訂單數(shù)少于2的客戶镊屎。
4.準(zhǔn)備相關(guān)數(shù)據(jù)庫和數(shù)據(jù)
4.1 新建一個(gè)測(cè)試數(shù)據(jù)庫TestDB惹挟；
create database TestDB;
4.2 創(chuàng)建測(cè)試表table1和table2；
table1:
CREATE TABLE table1 (
customer_id VARCHAR(10) NOT NULL,
city VARCHAR(10) NOT NULL,
PRIMARY KEY(customer_id)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;
table2:
CREATE TABLE table2 (
order_id INT NOT NULL auto_increment,
customer_id VARCHAR(10),
PRIMARY KEY(order_id)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;
4.3 插入測(cè)試數(shù)據(jù)缝驳；
INSERT INTO table1(customer_id,city) VALUES('163','hangzhou');
INSERT INTO table1(customer_id,city) VALUES('9you','shanghai');
INSERT INTO table1(customer_id,city) VALUES('tx','hangzhou');
INSERT INTO table1(customer_id,city) VALUES('baidu','hangzhou');
INSERT INTO table2(customer_id) VALUES('163');
INSERT INTO table2(customer_id) VALUES('163');
INSERT INTO table2(customer_id) VALUES('9you');
INSERT INTO table2(customer_id) VALUES('9you');
INSERT INTO table2(customer_id) VALUES('9you');
INSERT INTO table2(customer_id) VALUES('tx');
INSERT INTO table2(customer_id) VALUES(NULL);
4.4 查看table1连锯，table2
mysql> select * from table1;
+-------------+----------+
| customer_id | city |
+-------------+----------+
| 163 | hangzhou |
| 9you | shanghai |
| baidu | hangzhou |
| tx | hangzhou |
+-------------+----------+
4 rows in set (0.00 sec)
mysql> select * from table2;
+----------+-------------+
| order_id | customer_id |
+----------+-------------+
| 1 | 163 |
| 2 | 163 |
| 3 | 9you |
| 4 | 9you |
| 5 | 9you |
| 6 | tx |
| 7 | NULL |
+----------+-------------+
7 rows in set (0.00 sec)
5.SQL語句的執(zhí)行順序
在這些SQL語句的執(zhí)行過程中，都會(huì)產(chǎn)生一個(gè)虛擬表用狱，用來保存SQL語句的執(zhí)行結(jié)果（這是重點(diǎn)）运怖，我現(xiàn)在就來跟蹤這個(gè)虛擬表的變化，得到最終的查詢結(jié)果的過程夏伊，來分析整個(gè)SQL邏輯查詢的執(zhí)行順序和過程摇展。
5.1 執(zhí)行FROM語句。
經(jīng)過FROM語句對(duì)兩個(gè)表執(zhí)行笛卡爾積溺忧，會(huì)得到一個(gè)虛擬表咏连，暫且叫VT1（vitual table 1），內(nèi)容如下：
+-------------+----------+----------+-------------+
|customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163 | hangzhou | 1 | 163 |
| 9you | shanghai | 1 | 163 |
| baidu | hangzhou | 1 | 163 |
| tx | hangzhou | 1 | 163 |
| 163 | hangzhou | 2 | 163 |
| 9you | shanghai | 2 | 163 |
| baidu | hangzhou | 2 | 163 |
| tx | hangzhou | 2 | 163 |
| 163 | hangzhou | 3 | 9you |
| 9you | shanghai | 3 | 9you |
| baidu | hangzhou | 3 | 9you |
| tx | hangzhou | 3 | 9you |
| 163 | hangzhou | 4 | 9you |
| 9you | shanghai | 4 | 9you |
| baidu | hangzhou | 4 | 9you |
| tx | hangzhou | 4 | 9you |
| 163 | hangzhou | 5 | 9you |
| 9you | shanghai | 5 | 9you |
| baidu | hangzhou | 5 | 9you |
| tx | hangzhou | 5 | 9you |
| 163 | hangzhou | 6 | tx |
| 9you | shanghai | 6 | tx |
| baidu | hangzhou | 6 | tx |
| tx | hangzhou | 6 | tx |
| 163 | hangzhou | 7 | NULL |
| 9you | shanghai | 7 | NULL |
| baidu | hangzhou | 7 | NULL |
| tx | hangzhou | 7 | NULL |
+-------------+----------+----------+-------------+
總共有28（table1的記錄條數(shù) * table2的記錄條數(shù)）條記錄鲁森。這就是VT1的結(jié)果祟滴，接下來的操作就在VT1的基礎(chǔ)上進(jìn)行。
5.2 執(zhí)行ON過濾
執(zhí)行完笛卡爾積以后歌溉，接著就進(jìn)行ON a.customer_id = b.customer_id條件過濾踱启，根據(jù)ON中指定的條件，去掉那些不符合條件的數(shù)據(jù)研底，得到VT2表，內(nèi)容如下：
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163 | hangzhou | 1 | 163 |
| 163 | hangzhou | 2 | 163 |
| 9you | shanghai | 3 | 9you |
| 9you | shanghai | 4 | 9you |
| 9you | shanghai | 5 | 9you |
| tx | hangzhou | 6 | tx |
+-------------+----------+----------+-------------+
VT2就是經(jīng)過ON條件篩選以后得到的有用數(shù)據(jù)透罢，而接下來的操作將在VT2的基礎(chǔ)上繼續(xù)進(jìn)行榜晦。
5.3 添加外部行
這一步只有在連接類型為OUTER JOIN時(shí)才發(fā)生，如LEFT OUTER JOIN羽圃、RIGHT OUTER JOIN和FULL OUTER JOIN乾胶。在大多數(shù)的時(shí)候，我們都是會(huì)省略掉OUTER關(guān)鍵字的，但OUTER表示的就是外部行的概念识窿。
LEFT OUTER JOIN把左表記為保留表斩郎，得到的結(jié)果為：
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163 | hangzhou | 1 | 163 |
| 163 | hangzhou | 2 | 163 |
| 9you | shanghai | 3 | 9you |
| 9you | shanghai | 4 | 9you |
| 9you | shanghai | 5 | 9you |
| tx | hangzhou | 6 | tx |
| baidu | hangzhou | NULL | NULL |
+-------------+----------+----------+-------------+
RIGHT OUTER JOIN把右表記為保留表，得到的結(jié)果為：
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163 | hangzhou | 1 | 163 |
| 163 | hangzhou | 2 | 163 |
| 9you | shanghai | 3 | 9you |
| 9you | shanghai | 4 | 9you |
| 9you | shanghai | 5 | 9you |
| tx | hangzhou | 6 | tx |
| NULL | NULL | 7 | NULL |
+-------------+----------+----------+-------------+
添加外部行的工作就是在VT2表的基礎(chǔ)上添加保留表中被過濾條件過濾掉的數(shù)據(jù)喻频，非保留表中的數(shù)據(jù)被賦予NULL值缩宜，最后生成虛擬表VT3。由于我在準(zhǔn)備的測(cè)試SQL查詢邏輯語句中使用的是LEFT JOIN甥温，過濾掉了以下這條數(shù)據(jù)：
| baidu | hangzhou | NULL | NULL |
現(xiàn)在就把這條數(shù)據(jù)添加到VT2表中锻煌，得到的VT3表如下：
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163 | hangzhou | 1 | 163 |
| 163 | hangzhou | 2 | 163 |
| 9you | shanghai | 3 | 9you |
| 9you | shanghai | 4 | 9you |
| 9you | shanghai | 5 | 9you |
| tx | hangzhou | 6 | tx |
| baidu | hangzhou | NULL | NULL |
+-------------+----------+----------+-------------+
接下來的操作都會(huì)在該VT3表上進(jìn)行。
5.4 執(zhí)行WHERE過濾
對(duì)添加外部行得到的VT3進(jìn)行WHERE過濾姻蚓，只有符合<where_condition>的記錄才會(huì)輸出到虛擬表VT4中宋梧。當(dāng)我們執(zhí)行WHERE a.city = 'hangzhou'
的時(shí)候，就會(huì)得到以下內(nèi)容狰挡，并存在虛擬表VT4中：
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163 | hangzhou | 1 | 163 |
| 163 | hangzhou | 2 | 163 |
| tx | hangzhou | 6 | tx |
| baidu | hangzhou | NULL | NULL |
+-------------+----------+----------+-------------+
但是在使用WHERE子句時(shí)捂龄，需要注意以下兩點(diǎn)：
由于數(shù)據(jù)還沒有分組，因此現(xiàn)在還不能在WHERE過濾器中使用
where_condition=MIN(col)這類對(duì)分組統(tǒng)計(jì)的過濾加叁；
由于還沒有進(jìn)行列的選取操作倦沧，因此在SELECT中使用列的別名也是不被允許的，如：SELECT city as c FROM t WHERE c='shanghai';
是不允許出現(xiàn)的殉农。
5.5 執(zhí)行GROUP BY分組
GROU BY子句主要是對(duì)使用WHERE子句得到的虛擬表進(jìn)行分組操作刀脏。我們執(zhí)行測(cè)試語句中的GROUP BY a.customer_id，就會(huì)得到以下內(nèi)容：
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163 | hangzhou | 1 | 163 |
| baidu | hangzhou | NULL | NULL |
| tx | hangzhou | 6 | tx |
+-------------+----------+----------+-------------+
得到的內(nèi)容會(huì)存入虛擬表VT5中超凳，此時(shí)愈污，我們就得到了一個(gè)VT5虛擬表，接下來的操作都會(huì)在該表上完成轮傍。
5.6 執(zhí)行HAVING過濾
HAVING
子句主要和GROUP BY子句配合使用暂雹，對(duì)分組得到的VT5虛擬表進(jìn)行條件過濾。當(dāng)我執(zhí)行測(cè)試語句中的HAVING count(b.order_id) < 2
時(shí)创夜，將得到以下內(nèi)容：
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| baidu | hangzhou | NULL | NULL |
| tx | hangzhou | 6 | tx |
+-------------+----------+----------+-------------+
這就是虛擬表VT6杭跪。
5.7 SELECT列表
現(xiàn)在才會(huì)執(zhí)行到SELECT子句。我們執(zhí)行測(cè)試語句中的SELECT a.customer_id, COUNT(b.order_id) as total_orders驰吓，從虛擬表VT6中選擇出我們需要的內(nèi)容涧尿。我們將得到以下內(nèi)容：
+-------------+--------------+
| customer_id | total_orders |
+-------------+--------------+
| baidu | 0 |
| tx | 1 |
+-------------+--------------+
這是虛擬表VT7。
5.8 執(zhí)行DISTINCT子句
如果在查詢中指定了DISTINCT
子句檬贰，則會(huì)創(chuàng)建一張內(nèi)存臨時(shí)表（如果內(nèi)存放不下姑廉，就需要存放在硬盤了）。這張臨時(shí)表的表結(jié)構(gòu)和上一步產(chǎn)生的虛擬表VT7是一樣的翁涤。
5.9 執(zhí)行ORDER BY子句
對(duì)虛擬表中的內(nèi)容按照指定的列進(jìn)行排序桥言，然后返回一個(gè)新的虛擬表萌踱，我們執(zhí)行測(cè)試SQL語句中的ORDER BY total_orders DESC，就會(huì)得到以下內(nèi)容：
+-------------+--------------+
| customer_id | total_orders |
+-------------+--------------+
| tx | 1 |
| baidu | 0 |
+-------------+--------------+
可以看到這是對(duì)total_orders列進(jìn)行降序排列的号阿。上述結(jié)果會(huì)存儲(chǔ)在VT8中并鸵。
5.9 執(zhí)行LIMIT子句
LIMIT
子句從上一步得到的VT8虛擬表中選出從指定位置開始的指定行數(shù)據(jù)。對(duì)于沒有應(yīng)用ORDER BY的LIMIT子句扔涧，得到的結(jié)果同樣是無序的园担，所以，很多時(shí)候扰柠，我們都會(huì)看到LIMIT子句會(huì)和ORDER BY子句一起使用粉铐。
MySQL數(shù)據(jù)庫的LIMIT支持如下形式的選擇：
LIMIT n, m表示從第n條記錄開始選擇m條記錄。
