与C/C++等大多数语言不同的是,C/C++的处理逻辑是顺序处理,即处理顺序与我们编码的顺序几乎一致,而sql语言的处理却不是按编码顺序执行的,而总是从from开始处理。下边做个sql语句编写与处理的顺序对比
编写顺序:select dinstinct ... from ... join ... on ... where .. group by ... having ... order by ... limit ...
处理顺序:from ... on ... join ... where ... group by ... having ... select dinstinct ... order by limit ...
即处理顺序大概分为10个步骤,分别如下:
1、from:对from子句中的左表和右表执行笛卡尔积,生成虚拟表VT1
2、on:对虚拟表VT1进行筛选,留下符合join条件的结果集,生成虚拟表VT2
3、join :如果指定left join 或者 right join,则将2步骤中未匹配的行添加到VT2中,再汇总生成VT3
4、where :对VT3进行where过滤,剩下的结果集生成VT4
5、group by:对VT4中的数据进行group by 分组,结果数据生成VT5
6、having :对VT5进行having过滤,过滤后的数据生成VT6
7、select :对VT6进行select操作,选择对应的列,生成VT7
8、dinstinct:对VT7进行去重操作,去重后的数据生成VT8
9、order by:对VT8进行order by 排序,生成VT9
10、limit :对VT9进行limit操作,提取指定行数的记录,生成VT10,并返回给查询用户
下边以一个例子说明,首先准备两张表,customers(客户表)、orders(订单表)
create table customers
(
customer_id varchar(10) not null,
city varchar(10) not null,
primary key(customer_id)
);
insert into customers values('163', 'hangzhou');
insert into customers values('9you', 'shanghai');
insert into customers values('TX', 'hangzhou');
insert into customers values('baidu', 'hangzhou');
create table orders
(
order_id int not null auto_increment,
customer_id varchar(10),
primary key(order_id)
);
insert into orders values(NULL, '163');
insert into orders values(NULL, '163');
insert into orders values(NULL, '9you');
insert into orders values(NULL, '9you');
insert into orders values(NULL, '9you');
insert into orders values(NULL, 'TX');
insert into orders values(NULL, NULL);
上述两张表的建表及插入语句在mysql中执行后,两张表的数据如下:
那么需求为:来自杭州且订单数小于2的客户,并且查询出他们订单数量,查询结果按订单数从小到达排序
select c.customer_id, count(o.order_id) as total_orders
from customers as c left join orders as o
on c.customer_id = o.customer_id
where c.city = 'hangzhou'
group by c.customer_id
having count(o.order_id) < 2
order by total_orders desc;
查询结果如下
现在就来对这个查询sql按照上边说的10个步骤来分析:
(1)、from:对from子句中的左表和右表执行笛卡尔积,生成虚拟表VT1
sql:select * from customers join orders
(2)、on:对虚拟表VT1进行筛选,留下符合join条件的结果集,生成虚拟表VT2
sql:select * from customers c join orders o
on c.customer_id=o.customer_id;
(3)、join:如果指定left join 或者 right join,则将2步骤中未匹配的行添加到VT2中,再汇总生成VT3
sql:select * from customers c left join orders o
on c.customer_id=o.customer_id;
(4)、where:对VT3进行where过滤,剩下的结果集生成VT4
sql:select * from customers c left join orders o
on c.customer_id=o.customer_id
where c.city='hangzhou';
(5)、group by:对VT4中的数据进行group by 分组,结果数据生成VT5
sql:select * from customers c left join orders o
on c.customer_id=o.customer_id
where c.city='hangzhou'
group by c.customer_id;
(6)、having:对VT5进行having过滤,过滤后的数据生成VT6
sql:select * from customers c left join orders o
on c.customer_id=o.customer_id
where c.city='hangzhou'
group by c.customer_id
having count(o.order_id) < 2;
(7)、select:对VT6进行select操作,选择对应的列,生成VT7
sql:select c.customer_id, count(o.order_id) as total_orders
from customers c left join orders o
on c.customer_id=o.customer_id
where c.city='hangzhou'
group by c.customer_id
having count(o.order_id) < 2;
(8)、distinct:对VT7进行去重操作,去重后的数据生成VT8(此处仅仅举例,该sql中并不涉及distinct操作)
sql:select distinct c.customer_id, count(o.order_id) as total_orders
from customers c left join orders o
on c.customer_id=o.customer_id
where c.city='hangzhou'
group by c.customer_id
having count(o.order_id) < 2;
(9)、order by:对VT8进行order by 排序,生成VT9
sql:select distinct c.customer_id, count(o.order_id) as total_orders
from customers c left join orders o
on c.customer_id=o.customer_id
where c.city='hangzhou'
group by c.customer_id
having count(o.order_id) < 2
order by total_orders desc;
(10)、limit:对VT9进行limit操作,提取指定行数的记录,生成VT10,并返回给查询用户(此处仅仅举例,该sql中并不涉及limit操作)
sql:select distinct c.customer_id, count(o.order_id) as total_orders
from customers c left join orders o
on c.customer_id=o.customer_id
where c.city='hangzhou'
group by c.customer_id
having count(o.order_id) < 2
order by total_orders desc
limit 1;
以上便是sql语句的执行顺序。