`
lvwenwen
  • 浏览: 955538 次
  • 性别: Icon_minigender_1
  • 来自: 魔都
社区版块
存档分类
最新评论

SQL语句的解析过程(转)

阅读更多

SQL语句的解析过程

http://www.cnblogs.com/myprogram/archive/2013/01/24/2874666.html

 

    由于最近需要做一些sql query性能提升的研究,因此研究了一下sql语句的解决过程。在园子里看了下,大家写了很多相关的文章,大家的侧重点各有不同。本文是我在看了各种资料后手机总结的,会详细的,一步一步的讲述一个sql语句的各个关键字的解析过程,欢迎大家互相学习。


SQL语句的解析顺序

简单的说一个sql语句是按照如下的顺序解析的:

  • 1. FROM FROM后面的表标识了这条语句要查询的数据源。和一些子句如,(1-J1)笛卡尔积,(1-J2)ON过滤,(1-J3)添加外部列,所要应用的对象。FROM过程之后会生成一个虚拟表VT1。
  • (1-J1)笛卡尔积 这个步骤会计算两个相关联表的笛卡尔积(CROSS JOIN) ,生成虚拟表VT1-J1。
  • (1-J2)ON过滤 这个步骤基于虚拟表VT1-J1这一个虚拟表进行过滤,过滤出所有满足ON 谓词条件的列,生成虚拟表VT1-J2。
  • (1-J3)添加外部行  如果使用了外连接,保留表中的不符合ON条件的列也会被加入到VT1-J2中,作为外部行,生成虚拟表VT1-J3。    
  • 2. WHERE 对VT1过程中生成的临时表进行过滤,满足where子句的列被插入到VT2表中。
  • 3. GROUP BY 这个子句会把VT2中生成的表按照GROUP BY中的列进行分组。生成VT3表。
  • 4. HAVING 这个子句对VT3表中的不同的组进行过滤,满足HAVING条件的子句被加入到VT4表中。
  • 5. SELECT 这个子句对SELECT子句中的元素进行处理,生成VT5表。
  • (5-1)计算表达式 计算SELECT 子句中的表达式,生成VT5-1
  • (5-2)DISTINCT 寻找VT5-1中的重复列,并删掉,生成VT5-2
  • (5-3)TOP 从ORDER BY子句定义的结果中,筛选出符合条件的列。生成VT5-3表
  • ORDER BY 从VT5-3中的表中,根据ORDER BY 子句的条件对结果进行排序,生成VC6表。

客户,订单的查询例子

首先创建一个Customers表,插入如下数据:

customerid city
FISSA Madrid
FRNDO Madrid
KRLOS Madrid
MRPHS Zion

创建一个Orders表,插入如下数据:

orderid customerid
1 FRNDO
2 FRNDO
3 KRLOS
4 KRLOS
5 KRLOS
6 MRPHS
7 NULL

    假如我们想要查询来自Madrid的,订单数小于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
复制代码

查询结果为:

customerid numorders
FISSA 0
FRNDO 2

下面我们会详细的讲述sql是怎样计算出这个结果的:

 

FROM子句

    FROM子句标识了需要查询的表,如果指定了表操作,会从左到右的处理,每一个基于一个或者两个表的表操作都会返回一个输出表。左边表的输出结果会作为下一个表操作的输入结果。例如,交表相关的操作有 (1-J1)笛卡尔积,(1-J2)ON过滤器,(1-J3)添加外部列。FROM句子生成虚拟表VT1。

Step 1-J1:执行笛卡尔积(CROSS JOIN)

    笛卡尔积会把左右两个表每一行所有可能的组合都列出来生成表VT1-J1,如果左表有m列,右表有n列,那么笛卡尔积之后生成的VT1-J1表将会有m×n列。

Step 1-J1这个步骤等价于执行:

SELECT * from Customers C  CROSS JOIN Orders O

执行结果为:(共有4×7列)

C.customerid C.city O.orderid O.customerid
FISSA Madrid 1 FRNDO
FISSA Madrid 2 FRNDO
FISSA Madrid 3 KRLOS
FISSA Madrid 4 KRLOS
FISSA Madrid 5 KRLOS
FISSA Madrid 6 MRPHS
FISSA Madrid 7 NULL
FRNDO Madrid 1 FRNDO
FRNDO Madrid 2 FRNDO
FRNDO Madrid 3 KRLOS
FRNDO Madrid 4 KRLOS
FRNDO Madrid 5 KRLOS
FRNDO Madrid 6 MRPHS
FRNDO Madrid 7 NULL
KRLOS Madrid 1 FRNDO
KRLOS Madrid 2 FRNDO
KRLOS Madrid 3 KRLOS
KRLOS Madrid 4 KRLOS
KRLOS Madrid 5 KRLOS
KRLOS Madrid 6 MRPHS
KRLOS Madrid 7 NULL
MRPHS Zion 1 FRNDO
MRPHS Zion 2 FRNDO
MRPHS Zion 3 KRLOS
MRPHS Zion 4 KRLOS
MRPHS Zion 5 KRLOS
MRPHS Zion 6 MRPHS
MRPHS Zion 7 NULL

 

Step 1-J2:应用ON过滤,(JOIN 条件)

ON过滤条件是sql的三个过滤条件(ON,WHERE,HAVING)中最先执行的,ON过滤条件应用于前一步生成的虚拟表(VT1-J1),满足ON过滤条件的行会被加入到虚拟表VT1-J2中。在应用了ON 过滤之后,生成的VT1-J2表如下所示:

C.customerid C.city O.orderid O.customerid
FRNDO Madrid 1 FRNDO
FRNDO Madrid 2 FRNDO
KRLOS Madrid 3 KRLOS
KRLOS Madrid 4 KRLOS
KRLOS Madrid 5 KRLOS
MRPHS Zion 6 MRPHS

 

Step 1-J3:添加外部列

    这个步骤只会出现在使用了外连接的情况。对于外连接(LEFT,RIGHT, or FULL),你可以标记一个或者两个表作为保留表。作为保留表意味着你希望这个表里面的所有列都被返回,即使它里面的数据不满足ON子句的过滤条件。LEFT OUTER JOIN 把左边的表标记为保留表,RIGHTOUTER JOIN把右边的表作为保留表,FULL OUTER JOIN把两个表都标记为保留表.Step 1-J3为根据VT1-J2中的虚拟表,添加了保留表中不满足ON 条件的列,在未保留表中没有对应的列,因此标记为NULL。这个过程生成了虚拟表VT1-J3。

C.customerid C.city O.orderid O.customerid
FISSA Madrid NULL NULL
FRNDO Madrid 1 FRNDO
FRNDO Madrid 2 FRNDO
KRLOS Madrid 3 KRLOS
KRLOS Madrid 4 KRLOS
KRLOS Madrid 5 KRLOS
MRPHS Zion 6 MRPHS

如果FROM子句中有多个表操作运算,sql会按照从左到右的顺序处理,左边生成的临时表结果作为右边表的输入表。

Step 2 WHERE 子句

WHERE过滤被应用到前一步生成的临时表中,根据WHERE过滤条件生成临时表VT2。

注意:由于数据现在还没有被分组,因此现在你不能使用聚合运算-例如:你不能使用这样的句子 WHERE orderdate = MAX(orderdate)。另外你也不能使用SELECT子句中创建的变量别名,因为现在还没有处理SELECT子句-例如你不能写这样的句子:SELECT YEAR(orderdate) AS orderyear . . . WHERE orderyear > 2008.

应用这个过滤

WHERE C.city = 'Madrid'

这时生成的临时表VT2的内容如下:

C.customerid C.city O.orderid O.customerid
FISSA Madrid NULL NULL
FRNDO Madrid 1 FRNDO
FRNDO Madrid 2 FRNDO
KRLOS Madrid 3 KRLOS
KRLOS Madrid 4 KRLOS
KRLOS Madrid 5 KRLOS

    在这个例子中,你需要在ON子句中使用ON C.customerid = O.customerid过滤,没有订单的客户在1-J2这一步中被过滤掉,但是在1-J3这一步中作为外部列又被加回来。但是,由于你只想返回来自Madrid的客户,因此你需要在WHERE子句中过滤城市(WHERE C.city = ‘Madrid’),如果你放在ON过滤中,不属于Madrid的客户在添加外部列中会被添加回来。

关于ON 和 WHERE 的区别需要在这里说明一下,ON 和WHERE 的主要区别在于 ON 实在添加外部列之前进行过滤,WHERE 是在之后。ON过滤掉的列会在1-J3中添加回来。如果你不需要添加外部列,那么这两个过滤是相同的。

Step 3 GROUP BY子句

    这个子句会把前一步中生成的临时表中的数据进行分组,每一行都会分到并且只分到一个组里,生成虚拟表VT3。VT3表中包含了VT2表中所有的数据,和分组标识符。

这是生成的临时表VT3的内容如下:

Groups 
C.customerid
C.customerid C.city O.orderid O.customerid
FISSA FISSA Madrid     NULL NULL
FRNDO FRNDO Madrid 1 FRNDO
FRNDO Madrid 2 FRNDO
  KRLOS Madrid 3 KRLOS
KRLOS KRLOS Madrid 4 KRLOS
  KRLOS Madrid 5 KRLOS

    sql最终返回的结果中,每一个分组必须只能返回一行(除非被过滤掉),因此当一个sql语句中使用了GROUP BY时,在GROUP BY后面处理的子句,如SELECT,HAVING子句等,只能使用出现在GROUP BY后面的列,对于没有出现GROUP BY后面的列必须使用聚合函数(如 MAX ,MIN,COUNT,AVG等),保证每一个GROUP只返回一行。

Step 4 HAVING子句

    HAVING子句用来过滤前一步生成的临时表,并且只作用于分组后的数据,满足HAVING条件的GROUP被添加到虚拟表VT4中。

当应用了这个过滤:

HAVING COUNT(O.orderid) < 3

之后,生成的VT4表内容如下:

Groups 
C.customerid
C.customerid C.city O.orderid O.customerid
FISSA FISSA Madrid     NULL NULL
FRNDO FRNDO Madrid 1 FRNDO
  FRNDO Madrid 2 FRNDO

需要注意的一点是,这里面使用的是COUNT(O.orderid),而不是COUNT(*),由于这个查询中添加了外部列,COUNT方法会忽略NULL的列,导致出现了你不想要的结果。

Step 5 SELECT 子句

    尽管出现在sql语句的最前面,SELECT在第五步的时候才被处理,SELECT子句返回的表会最终返回给调用者。这个子句包含三个子阶段:(5-1)计算表达式,(5-2) 处理DISTINCT,(5-3)应用TOP过滤。

Step 5-1 计算表达式

    SELECT子句中的表达式可以返回或者操作前一步表中返回的基本列。如果这个sql语句是一个聚合查询,在Step 3之后,你只能使用GROUP BY中的列,对不属于GROUP集合中的列必须使用聚合运算。不属于FROM表中基本列的必须为其起一个别名,如YEAR(orderdate) AS orderyear。

注意:在SELECT子句中创建的别名,不能在之前的Step中使用,即使在SELECT子句中也不能。原因是sql的很多操作是同时操作(all at once operation),至于什么是all-at-once operation这里就不再介绍了。因此,SELECT子句中创建的别名只能在后面的子句中使用,如ORDER BY。例如:SELECT YEAR(orderdate) AS orderyear . . . ORDER BY orderyear。

在这个例子中:

SELECT C.customerid, COUNT(O.orderid) AS numorders

结果会得到一个虚拟表VT5-1:

C.customerid numorders
FIFSSA 0
FRNDO 2

Step 5-2:应用DISTINCT子句

    如果sql语句中使用了DISTINCT,sql会把重复列去掉,生成虚拟表VT5-2。

Step 5-3:应用TOP选项

    TOP选项是T-SQL提供的一个功能,用来表示显示多少行。基于ORDER BY子句定义的顺序,指定个数的列会被查询出来。这个过程生成虚拟表VT5-3。

    正如上文提到的,这一步依赖于ORDER BY定义的顺序来决定哪些列应该显示在前面。如果你没有指定结果的ORDER BY顺序,也没有使用WITH TIES子句 ,每一次的返回结果可能会不一致。

    在我们的例子中,Step 5-3被省略了,因为我们没有使用TOP关键字。

Step 6:ORDER BY子句

    前一步返回的虚拟表在这一步被排序,根据ORDER BY子句指定的顺序,返回游标VC6。ORDER BY子句也是唯一一个可以使用SELECT子句创建的别名的地方。

注意:这一步和之前不同的地方在于,这一步返回的结果是一个游标,而不是表。sql是基于集合理论的,一个集合没有对他的行定义顺序,它只是一个成员的逻辑集合,因此成员的顺序并不重要。带有ORDER BY子句的sql返回一个按照特定序列组织每一行的对象。ANSI 把这样的一个对象叫游标。理解这一点对你了解sql很重要。


 

上面的步骤如图所示:

image

 

    本书中主要内容是参照 Inside Microsoft SQL Server 2008:T-SQL Query,中的内容,大家如果想深入了解sql查询相关的知识,可以找这本书看看,我这有英文原版的pdf,需要的可以找我要。

分享到:
评论

相关推荐

    JsonSQL:用SQL语句解析JSON文件

    **JsonSQL: SQL语句解析JSON文件** 在大数据处理和Web应用中,JSON(JavaScript Object Notation)格式已经成为数据交换的常见格式。然而,对于习惯使用SQL查询关系型数据库的人来说,处理JSON数据可能会觉得不太...

    通用SQL语句解析类SqlParser(JAVA版)

    项目中已使用的一个比较简单的SQL语句查询字段解析工具类

    通过解析sql语句获取表血缘关系项目

    1. **SQL语句解析**:SQL(Structured Query Language)是用于管理和处理关系型数据库的标准语言。解析SQL语句是指将人类可读的SQL命令转化为计算机可执行的指令。这一过程通常由数据库管理系统(DBMS)完成,但开发者...

    SQL语句执行过程详解

    SQL语句执行过程是一个涉及到客户端与服务器端交互、多个阶段的复杂过程,包含了对SQL语句的处理、解析、优化以及最终的执行。这个过程对于数据库管理员来说,需要对数据库内部结构有深入的了解,才能够完全掌握。 ...

    SQL 语句解析适用于初学者

    5. 过程化SQL(PL/SQL或T-SQL):在某些数据库系统中,如Oracle和SQL Server,支持将多个SQL语句组合成过程化的代码块。 三、基本SQL操作 1. 创建表:使用`CREATE TABLE`语句,定义表名和字段名、数据类型等,例如...

    oracle sql语句执行流程解析

    一条普通的SQL语句从应用端提交开始,到在数据库中执行完毕,涉及了一系列复杂的过程。理解这些过程,有助于提高数据操作的效率以及进行更有效的SQL优化。 首先,客户端将SQL语句发送到服务器端。这个过程涉及到...

    C#将Lambda表达式转成Sql语句

    3. **构造SQL语句**:根据解析出的信息,我们可以构造对应的SQL语句片段。例如,`Expression.Equal`可能对应于SQL的`=`,`Expression.Call`可能对应于数据库中的某个函数调用。 4. **处理查询上下文**:在转换过程...

    存储过程中怎么动态执行sql语句

    “存储过程中怎么动态执行SQL语句”这一标题表明文章将介绍如何在Oracle数据库的存储过程中编写能够动态执行的SQL语句。动态SQL是指在运行时才能确定其具体内容的SQL语句,它允许用户根据不同的条件构造不同的查询或...

    基于Python的sqlparse库SQL语句解析设计源码

    该项目是采用Python编写的基于sqlparse库的SQL语句解析设计源码,共计46个文件,涵盖32个Python源文件、5个配置文件(YAML格式)、1个代码风格检查配置文件、1个GitHub代码所有者配置文件、1个Git忽略规则文件、1个...

    数据库sql语句解析系统.pdf

    综上所述,数据库SQL语句解析系统的设计和实现是一个包含多方面知识点的复杂过程。从数据结构的设计到SQL语句的解析执行,每一个环节都需要精心规划和编程实现。通过这样的系统,用户可以更高效地管理和操作数据库,...

    从mybatis日志中解析出可执行的sql语句

    适用场景:生产环境从sql日志获取可执行sql语句 使用方法:从日志中获取完整的sql片段(可以前后多复制一部分,确保准确性),打开html文件,粘贴到输入框中点击解析sql,获得可执行的sql

    sql语句解析.zip

    sql语句分析,主要用于dug模式下打印出来的sql分析,自动对sql语句进行解析,对查询条件、字段赋值进行准确的赋值,非常实用的一款工具。 操作非常简单: 1、copy打印出来的sql 2、点击sql分析,就得到你想要的...

    面向数据库性能的SQL语句解析与翻译.pdf

    【SQL语句解析与翻译的重要性】 SQL(Structured Query Language)是用于管理和操作关系型数据库的标准语言,对于数据库性能优化和安全至关重要。SQL语句解析与翻译是数据库管理系统中的关键技术,它能够帮助非专业...

    oracle-查找硬解析问题SQL语句

    在数据库中硬解析是万恶之源,为大家提供一个查找并且定位oracle硬解析问题SQL语句脚本

    基于SQL 语法解释器JSqlParser实现的SQL解析工具

    然而,对于复杂的SQL语句处理,有时需要编写自定义的解析工具,这就是JSqlParser的用武之地。JSqlParser是一个用Java编写的开源项目,它提供了一个强大的SQL语法解释器,能够帮助开发者解析、理解和操作SQL语句。 ...

    HW_SQL_TOOL.rar_SQL 解析_python sql_sql解析_sql语句 python_tool

    SQL解析是将用户输入的SQL语句转换为解析树的过程,这是一个数据结构,表示了SQL语句的语法结构。这个过程通常由数据库管理系统(DBMS)内部的解析器完成,但它也可以通过编程方式实现,比如使用Python。Python有...

    java版本的SQL语句解析源代码,包含demo(zql)

    SQL语句解析java包,含源代码: SELECT ANTIQUEOWNERS.OWNERLASTNAME, ANTIQUEOWNERS.OWNERFIRSTNAME FROM ANTIQUEOWNERS, ANTIQUES WHERE ANTIQUES.BUYERID = ANTIQUEOWNERS.OWNERID AND ANTIQUES.ITEM = 'Chair'; ...

    Python解析Excel表格,生成sql语句,写入本地文件

    由于工作中需要写精算,给的一个Excel中,有大量数据需要写入数据库,一个个写,不知要到何年何月,于是使用Python,利用xlrd模块,解析Excel,将其中需要的数据生成想要的sql语句,根据sql语句插入到数据库,后续会...

    SQL Server中存储过程比直接运行SQL语句慢的原因

    然而,有时人们发现存储过程的执行速度比直接运行SQL语句要慢,这主要归因于几个因素。 首先,存储过程的编译和缓存机制。存储过程在创建时进行一次性编译,之后的执行会使用已编译的计划,从而避免了每次执行时的...

Global site tag (gtag.js) - Google Analytics