t-sql查询的逻辑工作原理
很多朋友都会使用t-sql编写查询,但实际上对于其工作原理大多知之甚少。下面我们来分析一下查询的逻辑工作原理,也就是说一个查询到底是怎么一步一步的工作,最终给我们展示数据的。
认真观察下面这个图。想想看,这是我们常写的语句,你真的有没有想过哪个部分是先执行,而哪是个后面执行的呢
on,where,having是三种筛选的子句。正因为他们是在select语句执行之前执行的,所以他们所用的表达式是不可以为select子句中重命名的列的
他们还有一个共同特征,如果比较运算符的一个部分为null,则既不返回true也不返回false,而是返回unknown
下面我们用一个实例来讲解这些步骤
第一步:准备实验环境和数据
set nocount on;
use tempdb;
go
if object_id('dbo.orders') is not null
drop table dbo.orders;
go
if object_id('dbo.customers') is not null
drop table dbo.customers;
go
create table dbo.customers
(
customerid char(5) not null primary key,
city varchar(10) not null
);
insert into dbo.customers(customerid, city) values('fissa', 'madrid');
insert into dbo.customers(customerid, city) values('frndo', 'madrid');
insert into dbo.customers(customerid, city) values('krlos', 'madrid');
insert into dbo.customers(customerid, city) values('mrphs', 'zion');
create table dbo.orders
(
orderid int not null primary key,
customerid char(5) null references customers(customerid)
);
insert into dbo.orders(orderid, customerid) values(1, 'frndo');
insert into dbo.orders(orderid, customerid) values(2, 'frndo');
insert into dbo.orders(orderid, customerid) values(3, 'krlos');
insert into dbo.orders(orderid, customerid) values(4, 'krlos');
insert into dbo.orders(orderid, customerid) values(5, 'krlos');
insert into dbo.orders(orderid, customerid) values(6, 'mrphs');
insert into dbo.orders(orderid, customerid) values(7, null);
以上语句是在tempdb中创建了两个表,分别表示客户信息和订单信息。订单表与客户表是通过customerid进行连接的。我们也分别为他们输入了一些数据。如下面所示
如你所见,这两个表很简单的
第二步:准备一个查询做测试
/*
一个测试查询,检索那些订单个数小于3的客户,并且按订单总数排序(升序)
*/
select c.customerid, count(o.orderid) as numorders
from dbo.customers as c
left outer join dbo.orders as o
on c.customerid = o.customerid
where c.city = 'madrid'
group by c.customerid
having count(o.orderid) < 3
order by numorders;
同样是很简单的一个查询,它返回是订单总数小于3的客户。如下所示
很多朋友研究到这里就停止了,不过,我们下面要详细的分析这个查询的结果是怎么出来的
/*第一步:处理from子句,把来源的表进行cross join(笛卡尔乘积)
我这里把结果做一个生成表查询,写到一个临时表(vt1)中去
*/
select c.customerid as customer,c.city,o.* into #vt1 from dbo.customers c,dbo.orders o
select * from #vt1
–返回28行数据(4*7)
–第二步:处理on子句,只把那些两个表的customerid匹配的行找出来,我把它们放到vt2中去
select temp.* into #vt2 from (select * from #vt1 where customer=customerid) temp
select * from #vt2
–返回6行数据
–第三步:根据join语句的类型,决定是否要添加行到vt2中去,例如如果是left join的话,那么就要检查坐边的表(我们这里是customers表)的连接键值是否都存在,如果不存在就要去添加到vt2中
select temp.* into #vt3 from
(select * from #vt2
union all
select customerid,city,null,null from dbo.customers c where not exists(select distinct customer from #vt2 where customer=c.customerid)) temp
select * from #vt3
–返回7行数据,其中有一个客户,因为没有订单,这一步中被添加进来。它的orders的记录被标记为null
–第四步:处理where 子句,对vt3的结果集进行过滤,我们的条件是city=madid
select temp.* into #vt4 from
(select * from #vt3 where city='madrid') temp
select * from #vt4
–返回6行数据,因为有一个客户不是这个城市的
–第五步:处理group子句,进行分类汇总
select temp.* into #vt5 from
(select customer,count(orderid) as orderidcount,count(city) as citycount,count(customerid) as customeridcount from #vt4 group by customer) temp
select * from #vt5
–返回3行数据,根据客户分组,统计了订单的个数
–这里会不会去统计其他列的汇总呢
更正:这一步,逻辑上的数据结构是下面这样的,也就是说其实并没有计算。而是分组
–因为没有with rollup和with cube语句,所以跳过第六步,进入having子句的处理
–第六步:处理having子句,对group之后的结果进行筛选,我们这里的条件是orderidcount<3
select temp.* into #vt6 from
(select * from #vt5 where orderidcount<3) temp
select * from #vt6
–返回2行数据
–第七步:使用select 的字段列表过滤结果集
select #vt6.customer,#vt6.orderidcount as numorders into #vt7 from #vt6
select * from #vt7
–还是2行数据,只不过只有两个列了
select 返回的结果在内部其实是一个游标集,本身并没有顺序,它就好比是一个集合。
–第八步:跳过distinct,进行orderby操作
select #vt7.* into #vt8 from #vt7 order by #vt7.customer
select * from #vt8
–返回2行数据,经过排序
–这个查询结束演示。完整的8个步骤
需要了解更多数据库技术:T-SQL查询的逻辑工作原理,都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!
本文来自网络收集,不代表计算机技术网立场,如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址:https://www.ctvol.com/dtteaching/625987.html