文章目录
- 1、基本格式
- 1、sql执行叙述
- 2、执行from语句(查找)
- 3、执行on(过滤条件)
- 4、添加外部行(left outer join、right outer join、full outer join)[连接方式]
- 5、 执行where(指定约束过滤)
- 6 、执行group by(分组)
- 7、 执行having(过滤)
- 8、select列表 (执行)
- 9、 执行distinct子句(去重)
- 10、 执行order by子句(排序)
- 11、执行limit子句(限制显示条数)
SQL逻辑查询语句执行顺序
一、SELECT语句关键字的定义顺序
1、基本格式
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>
二、SELECT语句关键字的执行顺序
【select执行语句URL】
1、执行顺序
首先执行命令:select —>找到表:from —>拿着指定的约束条件,去文件/表中取出一条条记录:where —>将取出的一条条记录进行分组,如果没有group by,则整体作为一组:group by —>将分组的结果进行过滤:having —>然后进行去重:distinct —>将结果按条件进行排序:order by —> 最后输出限制结果的显示条数:limit
2、基本流程
(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>
三、准备表和数据
1、 新建一个测试数据库TestDB
create database TestDB;
2、创建测试表table1和table2;
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.插入测试数据
#表一插入数据
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、检查table1和table2状态
#table1的表状态
mysql> select * from table1;
+-------------+----------+
| customer_id | city |
+-------------+----------+
| 163 | hangzhou |
| 9you | shanghai |
| baidu | hangzhou |
| tx | hangzhou |
+-------------+----------+
4 rows in set (0.00 sec)
#table2的表状态
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)
四、准备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;
五、SQL执行顺序分析
1、sql执行叙述
在这些SQL语句的执行过程中,都会产生一个虚拟表,用来保存SQL语句的执行结果(这是重点),我现在就来跟踪这个虚拟表的变化,得到最终的查询结果的过程,来分析整个SQL逻辑查询的执行顺序和过程
2、执行from语句(查找)
#第一步,执行FROM语句
我们首先需要知道最开始从哪个表开始的,这就是FROM告诉我们的,现在有了<left_table>和<right_table>两个表,我们到底从哪个表开始,还是从两个表进行某种联系以后再开始呢?它们之间如何产生联系呢?--——笛卡尔积
#关于什么是笛卡尔积,请自行Google补脑
经过from语句对两个表执行笛卡尔积,会得到一个虚拟表,暂且叫VT1(vitual table 1),内容如下:
+-------------+----------+----------+-------------+
| 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 |
3、执行on(过滤条件)
#第二步,执行on过滤
执行完笛卡尔积以后,接着就进行ON a.customer_id = b.customer_id条件过滤,根据ON中指定的条件,去掉那些不符合条件的数据,得到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 |
+-------------+----------+----------+-------------+
#VT2就是经过ON条件筛选以后得到的有用数据,而接下来的操作将在VT2的基础上继续进行。
4、添加外部行(left outer join、right outer join、full outer join)[连接方式]
#第三步,添加外部行
这一步只有在连接类型为OUTER JOIN时才发生,如LEFT OUTER JOIN、RIGHT OUTER JOIN和FULL OUTER JOIN。在大多数的时候,我们都是会省略掉OUTER关键字的,但OUTER表示的就是外部行的概念:
1》#left outer join(左外连接)把左表记为保留表,得到的结果为:
+-------------+----------+----------+-------------+
| 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 |
+-------------+----------+----------+-------------+
2》#right outer join(右外连接)把右表记为保留表,得到的结果为:
+-------------+----------+----------+-------------+
| 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 |
+-------------+----------+----------+-------------+
3》#full outer join(全外连接)把左右表都作为保留表,得到的结果为:
+-------------+----------+----------+-------------+
| 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 |
| NULL | NULL | 7 | NULL |
+-------------+----------+----------+-------------+
添加外部行的工作就是在VT2表的基础上添加保留表中被过滤条件过滤掉的数据,非保留表中的数据被赋予NULL值,最后生成虚拟表VT3
由于我在准备的测试SQL查询逻辑语句中使用的是LEFT JOIN,过滤掉了以下这条数据:
| baidu | hangzhou | NULL | NULL |
现在就把这条数据添加到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 |
+-------------+----------+----------+-------------+
#接下来的操作都会在该VT3表上进行---》
5、 执行where(指定约束过滤)
<where_condition>的记录才会输出到虚拟表VT4中。当我们执行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 |
+-------------+----------+----------+-------------+
#但是在使用WHERE子句时,需要注意以下两点:
1>由于数据还没有分组,因此现在还不能在WHERE过滤器中使用where_condition=MIN(col)这类对分组统计的过滤;
2>由于还没有进行列的选取操作,因此在SELECT中使用列的别名也是不被允许的,如:SELECT city as c FROM t WHERE c='shanghai';是不允许出现的
6 、执行group by(分组)
(默认只显示组内第一条):
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163 | hangzhou | 1 | 163 |
| baidu | hangzhou | NULL | NULL |
| tx | hangzhou | 6 | tx |
7、 执行having(过滤)
(b.order_id) < 2时,将得到以下内容:
+-------------+----------+----------+-------------+
| customer_id | city | order_id | customer_id |
+-------------+----------+----------+-------------+
| baidu | hangzhou | NULL | NULL |
| tx | hangzhou | 6 | tx |
+-------------+----------+----------+-------------+
#这就是虚拟表VT6
8、select列表 (执行)
(b.order_id) as total_orders,从虚拟表VT6中选择出我们需要的内容。我们将得到以下内容:
+-------------+--------------+
| customer_id | total_orders |
+-------------+--------------+
| baidu | 0 |
| tx | 1 |
+-------------+--------------+
###!!还没有完,这只是虚拟表VT7
9、 执行distinct子句(去重)
如果在查询中指定了DISTINCT子句,则会创建一张内存临时表(如果内存放不下,就需要存放在硬盘了)。这张临时表的表结构和上一步产生的虚拟表VT7是一样的,不同的是对进行DISTINCT操作的列增加了一个唯一索引,以此来除重复数据。
由于我的测试SQL语句中并没有使用DISTINCT,所以,在该查询中,这一步不会生成一个虚拟表
10、 执行order by子句(排序)
| customer_id | total_orders |
+-------------+--------------+
| tx | 1 |
| baidu | 0 |
+-------------+--------------+
#可以看到这是对total_orders列进行降序排列的。上述结果会存储在VT8中
11、执行limit子句(限制显示条数)
#实列:
select * from employee limit 0,5;