转自http://www.cnblogs.com/linhaifeng/articles/7372774.html
1.select语句定义顺序
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.select语句执行顺序
(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.准备表和数据
3.1建库
create database TestDB;
3.2建表
CREATE TABLE table1
(
customer_id VARCHAR(10) NOT NULL,
city VARCHAR(10) NOT NULL,
PRIMARY KEY(customer_id)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;
CREATE TABLE table2
(
order_id INT NOT NULL auto_increment,
customer_id VARCHAR(10),
PRIMARY KEY(order_id)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;
3.3插入数据
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);
"查看表中数据"
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)
3.4准备SQL语句
#查询来自杭州,并且订单数少于2的客户。
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;
3.5分析查询顺序
在这些SQL语句执行过程中,会善生一个虚拟表,用于保存SQL语句的结果
跟踪虚拟表的变化,得到最终的结果
"1.首先执行FROM语句
我们首先要知道从哪个表开始,这里是table1和table2
经过from语句后,产生一个虚拟表VT1"
+-------------+----------+----------+-------------+
| 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 |
+-------------+----------+----------+-------------+
"2.在VT1的基础上,执行ON a.customer_id = b.customer_id条件过滤
得到VT2"
+-------------+----------+----------+-------------+
| 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 |
+-------------+----------+----------+-------------+
"3.执行join
left join 保留左表数据
得到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 |
+-------------+----------+----------+-------------+
"4.执行where过滤WHERE a.city = 'hangzhou
得到VT4'"
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163 | hangzhou | 1 | 163 |
| 163 | hangzhou | 2 | 163 |
| tx | hangzhou | 6 | tx |
| baidu | hangzhou | NULL | NULL |
+-------------+----------+----------+-------------+
"1.由于数据还没有分组,因此现在还不能在WHERE过滤器中使用where_condition=MIN(col)这类对分组统计的过滤;
2.由于还没有进行列的选取操作,因此在SELECT中使用列的别名也是不被允许的,如:SELECT city as c FROM t WHERE c='shanghai';是不允许出现的。"
"5.执行group by
GROU BY子句主要是对使用WHERE子句得到的虚拟表进行分组操作。
我们执行测试语句中的GROUP BY a.customer_id,就会得到以下内容(默认只显示组内第一条)
得到VT5:
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163 | hangzhou | 1 | 163 |
| baidu | hangzhou | NULL | NULL |
| tx | hangzhou | 6 | tx |
+-------------+----------+----------+-------------+
"
"6.执行having过滤
HAVING子句主要和GROUP BY子句配合使用,对分组得到的VT5虚拟表进行条件过滤。
当我执行测试语句中的HAVING count(b.order_id) < 2时,将得到以下内容:
得到VT6"
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| baidu | hangzhou | NULL | NULL |
| tx | hangzhou | 6 | tx |
+-------------+----------+----------+-------------+
"7.现在才会执行到SELECT子句,不要以为SELECT子句被写在第一行,就是第一个被执行的。
我们执行测试语句中的SELECT a.customer_id, COUNT(b.order_id) as total_orders,从虚拟表VT6中选择出我们需要的内容。我们将得到以下内容:
得到VT7"
+-------------+--------------+
| customer_id | total_orders |
+-------------+--------------+
| baidu | 0 |
| tx | 1 |
+-------------+--------------+
"8.执行distinct子句"
如果在查询中指定了DISTINCT子句,则会创建一张内存临时表(如果内存放不下,就需要存放在硬盘了)。
这张临时表的表结构和上一步产生的虚拟表VT7是一样的,不同的是对进行DISTINCT操作的列增加了一个唯一索引,以此来除重复数据。
由于我的测试SQL语句中并没有使用DISTINCT,所以,在该查询中,这一步不会生成一个虚拟表。
"9.执行order by,得到VT8"
+-------------+--------------+
| customer_id | total_orders |
+-------------+--------------+
| tx | 1 |
| baidu | 0 |
+-------------+--------------+
"10.执行Limit"
LIMIT子句从上一步得到的VT8虚拟表中选出从指定位置开始的指定行数据。
对于没有应用ORDER BY的LIMIT子句,得到的结果同样是无序的,所以,很多时候,我们都会看到LIMIT子句会和ORDER BY子句一起使用。
MySQL数据库的LIMIT支持如下形式的选择:
LIMIT n, m
表示从第n条记录开始选择m条记录。而很多开发人员喜欢使用该语句来解决分页问题。
对于小数据,使用LIMIT子句没有任何问题,当数据量非常大的时候,使用LIMIT n, m是非常低效的。
因为LIMIT的机制是每次都是从头开始扫描,如果需要从第60万行开始,读取3条数据,就需要先扫描定位到60万行,然后再进行读取,而扫描的过程是一个非常低效的过程。
所以,对于大数据处理时,是非常有必要在应用层建立一定的缓存机制(现在的大数据处理,大都使用缓存)
转载于:https://blog.51cto.com/10983441/2399304