个人学习4之--SQL中逻辑查询处理的各个阶段

最新推荐文章于 2022-06-13 18:35:37 发布

amwftip10

最新推荐文章于 2022-06-13 18:35:37 发布

阅读量362

点赞数

文章标签： null join table c# sql c

【个人学习4之--SQL中逻辑查询处理的各个阶段】收藏
ps：首先郁闷下：前面在CSDN博客上直接写，写了一个多小时，按了个另存为草稿，结果什么都没了。。郁闷这次在WORD上写。
今天我来谈下有关SQL中逻辑查询处理的各个阶段（自己搞的例子，不对的欢迎指正哦）
SQL不同于其他的编程语言的最大最大特征有3个吧，

一个是它是面向集合的编程思想，第二个是三值逻辑（这个后面会说到），还有一个就是今天主要要说的查询元素的逻辑处理次序。

请看一个基本查询的逻辑过程：
(8) SELECT (9) DISTINCT (11) <TOP_specification> <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) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
大家可以看到这里的运行步骤不是像一般的编程一句句从上往下它是跳动的有活力的
这里提前说下每一步都会产生一个虚拟的表（也可能是游标，下面会提到），作为下一个步骤的输入。大家最后看到的结果其实就是最后一个虚拟表了。
好了，下面我开始具体阐释每个步骤：
测试环境：

--题目要求：求出学生最低科目成绩不低于分且年龄在岁以上的学生姓名

create table #student(s# int,sname varchar(10),age int)

create table #study (s# int, c# char(1),score int)

insert #student

1,'xiaozhu',10 union all select

2,'xiaomao' ,9union all select

3,'xiaozhe' ,7union all select

4,'xiaophai',8 union all select

5,'xiaoduo',9

insert #study select

1,'A',99 union all select

1,'B',90 union all select

1,'C',99 union all select

2,'A',99 union all select

2,'b',99 union all select

2,'c',98 union all select

3,'A',99 union all select

3,'b',92 union all select

3,'c',91 union all select

3,'d',90 union all select

4,'A',88 union all select

4,'B',96

--SQL语句

select top 1 sname,MIN(score) as minsocre

from #student s left outer join #study sc

on s.s#=sc.s#

where age>7

group by sname

having MIN(score)>=90

order by minsocre

sname minsocre

---------- -----------

xiaozhu 90

(1)：执行笛卡尔积（CROSS JOIN ）
大家这里可能会觉得奇怪明明上面的2后面只有一个表怎么笛卡尔积呢？其实不是这样的，它做的就是要将FROM最前面2表进行CROSS JOIN（因为可能多表）
在我的例子里就是#student 和#study表进行笛卡尔积操作(这个笛卡尔积相信大家都会)，这里就会出现第一个虚拟表，我们叫它VTB1
/*

s# sname age s# c# score

----------- ---------- ----------- ----------- ---- -----------

1 xiaozhu 10 1 A 99

1 xiaozhu 10 1 B 90

1 xiaozhu 10 1 C 99

1 xiaozhu 10 2 A 99

1 xiaozhu 10 2 b 99

1 xiaozhu 10 2 c 98

1 xiaozhu 10 3 A 99

1 xiaozhu 10 3 b 92

....

.....太多了，不占空间

5 xiaoduo 9 2 b 99

5 xiaoduo 9 2 c 98

5 xiaoduo 9 3 A 99

5 xiaoduo 9 3 b 92

5 xiaoduo 9 3 c 91

5 xiaoduo 9 3 d 90

5 xiaoduo 9 4 A 88

5 xiaoduo 9 4 B 96

(2)应用ON筛选器（联接条件）
说到这里，首先得说下 SQL用于查询的有三个筛选器，分别是ON，where，having。
这里的ON筛选器就是用VTB1作为输入，再利用ON后面的联结条件进行筛选，生成VTB2。
sql编程第二特征：三值逻辑：它其实就是所谓的TRUE FALSE UNKOWN
主要来说这个UNKOWN 它这逻辑值和NULL在一起就会”出事“。凡是NULL参与的比如 NULL>42, NULL-8>9 这些结果都是UNKOWN
UNKOWN 的逻辑结果在不同的环境下是被不同的方式处理的：如
在三个筛选器上，它是会被当做FALSE处理；
在CHECK约束上是会被当成TRUE来处理，check(col>8) 你插入的COL为NULL，因为NULL>8 的结果不就是UNKOWN ，所以是可以插入的。
在筛选器上的2个NULL比较将是FALSE 但是在UNIQUE约束，分组，排序上2个NULL是等价的。
好了，大概就这些吧。我贴下VTB2的结果：

s# sname age s# c# score

----------- ---------- ----------- ----------- ---- -----------

1 xiaozhu 10 1 A 99

1 xiaozhu 10 1 B 90

1 xiaozhu 10 1 C 99

2 xiaomao 9 2 A 99

2 xiaomao 9 2 b 99

2 xiaomao 9 2 c 98

3 xiaozhe 7 3 A 99

3 xiaozhe 7 3 b 92

3 xiaozhe 7 3 c 91

3 xiaozhe 7 3 d 90

4 xiaophai 8 4 A 88

4 xiaophai 8 4 B 96

(3)添加外部行（outer row）
这里首先要知道一个地方：如果你的表没有涉及到OUTER JOIN 那么就不需要这步，这第三步就是为外部联接准备的。
我的测试环境下为了说明这个地方，加了LEFT JOIN ，它这里起到效果简单来说就是拿回刚才第二步损失的部分记录，因为你也看到了第五个学生'xiaoduo'
在虚拟表VTB2中没出现，这里完成后它又回来了。vtb3

s# sname age s# c# score

----------- ---------- ----------- ----------- ---- -----------

1 xiaozhu 10 1 A 99

1 xiaozhu 10 1 B 90

1 xiaozhu 10 1 C 99

2 xiaomao 9 2 A 99

2 xiaomao 9 2 b 99

2 xiaomao 9 2 c 98

3 xiaozhe 7 3 A 99

3 xiaozhe 7 3 b 92

3 xiaozhe 7 3 c 91

3 xiaozhe 7 3 d 90

4 xiaophai 8 4 A 88

4 xiaophai 8 4 B 96

5 xiaoduo 9 null null null

PS: 如果你涉及到三表及三表以上操作，你就可以将这个VTB3表和你FROM后面的第三个表进行1-3步骤的重复操作，依次类推，最后再拿出一个虚拟表就OK

(4)应用WHERE筛选器
这里看起来很简单，只要将VTB3表用WHERE 后面的条件过滤下得到VTB4表就可以。其实还是有很多知识在这的。
首先，在这我知道了为什么这里不可以用聚合函数了，因为数据在前面那几步还没被分组了，怎么可以用聚合函数呢？也知道了不可以用那些你在select
后面定义的列别名，理由很简单，你都没操作那步呢。
再者，我们考虑一个问题：我们那个条件age>7 可以写在ON后面么？答案在这里是否定的！
A：from #student s left outer join #study sc
on s.s#=sc.s#
where age>7

B：from #student s left outer join #study sc
on s.s#=sc.s# and s.age>7
这里2种情况形成的虚拟表是这样的
A:

s# sname age s# c# score

----------- ---------- ----------- ----------- ---- -----------

1 xiaozhu 10 1 A 99

1 xiaozhu 10 1 B 90

1 xiaozhu 10 1 C 99

2 xiaomao 9 2 A 99

2 xiaomao 9 2 b 99

2 xiaomao 9 2 c 98

4 xiaophai 8 4 A 88

4 xiaophai 8 4 B 96

5 xiaoduo 9 null null null

B:也就是VTB3

s# sname age s# c# score

----------- ---------- ----------- ----------- ---- -----------

1 xiaozhu 10 1 A 99

1 xiaozhu 10 1 B 90

1 xiaozhu 10 1 C 99

2 xiaomao 9 2 A 99

2 xiaomao 9 2 b 99

2 xiaomao 9 2 c 98

3 xiaozhe 7 3 A 99

3 xiaozhe 7 3 b 92

3 xiaozhe 7 3 c 91

3 xiaozhe 7 3 d 90

4 xiaophai 8 4 A 88

4 xiaophai 8 4 B 96

5 xiaoduo 9 null null null

大家可以看到，如果把AGE>7写ON后面这个出来的临时表比写WHERE后面多了那个年龄是7岁的xiaozhe，这显然不是我们想要看到的。
原因就是因为你这个ON后面的条件是在第二步就做完了，这个时候你过滤掉了7岁的xiaozhe 可是你第三步外联接下它就又可以回来的，
而如果写where 后面就可以防止这种情况发生了。现在可以给出结论了，写2个地方区别就在于ON过滤掉的记录可以再恢复，但是WHERE过滤掉的就回不来了。
PS：我这说的这种情况也只是在有OUTER JOIN的情况下才会出现的，如果是INNER join之类的就不会出现差别，因为它不存在第三步。
大家可以将语句换成

select sname,MIN(score) as minsocre --这里注意把TOP 1去掉不然看不出来了

from #student s left outer join #study sc

on s.s#=sc.s# and age>7

group by minsocre

having MIN(score)>=90

order by minsocre

sname minsocre

---------- -----------

xiaomao 98

xiaozhu 90

为了清楚说明，我再写下这步产生的VTB4表：

s# sname age s# c# score

----------- ---------- ----------- ----------- ---- -----------

1 xiaozhu 10 1 A 99

1 xiaozhu 10 1 B 90

1 xiaozhu 10 1 C 99

2 xiaomao 9 2 A 99

2 xiaomao 9 2 b 99

2 xiaomao 9 2 c 98

4 xiaophai 8 4 A 88

4 xiaophai 8 4 B 96

5 xiaoduo 9 null null null

(5) 分组
这步就是分组，你确定你要分组的对象，它唯一的。然后根据这个基础对象，把组分好。例子里就是学生的姓名(假设没出现重名的).
这里的VTB5书上的意思就是其实由2部分组成：、
1是实际组构成的成组部分（xiaozhu,xiaomao,xiaophai,xiaoduo）这几个基础对象吧，
2就是本来那个VTB4传下来的基础数据。
这里我们就可以看出一个东西：你在SELECT后面的列要么就包含在聚合函数里面要么就成为GROUP BY 的基础对象。
例如:你直接select sname,score 因为你分组了你这组只能返回关于这个组的一条信息。那么对于XIAOZHU这组来说有三个SCORE，你让它如何返回？
所以我们要用聚合函数来限定这个SCORE，如MIN(SCORE)。。。。。。
这里再提一个小地方：原本在2000里面你如果GROUP BY MIN(SCORE) 你在select 后面用SELECT MIN(SCORE)+1 as newscore 这样是不可以的。
就是说：你不能对GROUP BY 的基础对象做处理，除非他是表里的列。但是2005里面你可以放心使用我上面说的情况。
由于GROUP BY ALL是非标准的遗留物，所以不推荐，我也就不说了。。
VTB5：

s# sname age s# c# score

----------- ---------- ----------- ----------- ---- -----------

1 xiaozhu 10 1 A 99

1 xiaozhu 10 1 B 90

1 xiaozhu 10 1 C 99

------------------------------------------------------------

2 xiaomao 9 2 A 99

2 xiaomao 9 2 b 99

2 xiaomao 9 2 c 98

-------------------------------------------------------

4 xiaophai 8 4 A 88

4 xiaophai 8 4 B 96

-------------------------------------------------------

5 xiaoduo 9 null null null

(6) 应用CUBE 或者ROLLUP
我这稍微介绍一下ROLLUP 具体可以百度
用 ROLLUP 汇总数据，在生成包含小计和合计的报表时，ROLLUP 运算符很有用。
CUBE 和 ROLLUP 之间的区别在于：
CUBE 生成的结果集显示了所选列中值的所有组合的聚合。
ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。
例如，简单表 Inventory 中包含：

Item                 Color                Quantity
-------------------- -------------------- --------------------------
Table                Blue                 124
Table                Red                  223
Chair                Blue                 101
Chair                Red                  210
下列查询将生成小计报表：

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'

ELSE ISNULL(Item, 'UNKNOWN')

END AS Item,

CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'

ELSE ISNULL(Color, 'UNKNOWN')

END AS Color,

SUM(Quantity) AS QtySum

FROM Inventory

GROUP BY Item, Color WITH ROLLUP

Item Color QtySum

-------------------- -------------------- --------------------------

Chair Blue 101.00

Chair Red 210.00

Chair ALL 311.00

Table Blue 124.00

Table Red 223.00

Table ALL 347.00

ALL ALL 658.00

有时，查询优化器为 ROLLUP 生成的执行计划比为 COMPUTE BY 生成的更为高效。

(7) 应用HAVING筛选器
它是第一个用到分组以后的筛选器，也是最后一个，它在VTB5的基础上(如果第六步有的话就是VTB6)经过条件再次筛选生成VTB7（这里为了和步骤一致，我就写VTB7了）
例子中因为xiaophai有门课是88分<90 xiaoduo因为SCORE是NULL 所以都被排序掉

s# sname age s# c# score

----------- ---------- ----------- ----------- ---- -----------

1 xiaozhu 10 1 A 99

1 xiaozhu 10 1 B 90

1 xiaozhu 10 1 C 99

------------------------------------------------------------

2 xiaomao 9 2 A 99

2 xiaomao 9 2 b 99

2 xiaomao 9 2 c 98

(8)处理SELECT列表
终于轮到了SELECT操作了，这步很简单了，就是要注意分组后你的基本列只能用分组里的基础对象列了，其他列要用就得用聚合函数分组了，有重复的
列的时候要限定所属表(这里的s#)。这里经过选择产生VTB8
这里有个SQL特性-同时操作:
就是表达式的别名不能用于SELECT列表的其他表达式。如 select c1+1 as c2 ,c2+1 as c3 应该改成 select c1+1 as c2 select c1+1+1 as c3
这个特性你可以理解为操作都是一瞬间同时完成。否则你看下面的列子
UPDATE A
SET COL=(SELECT MAX(COL) FROM A)
如果你的更新不是一起完成，有先后顺序，那你这里就会出问题了。最大的COL随时都在变可能，这跟你愿意是不对老的。
这里产生的VTB8
/*
sname      minsocre
---------- -----------
xiaomao    98
xiaozhu    90

(9)应用DISTINCT 子句
这里顾名思义就是从VTB8去重复的记录得到VTB9

sname minsocre

---------- -----------

xiaomao 98

xiaozhu 90

注意这里如果你的代码中出现了GROUP BY 那么它是不会移除任何行的。而且DISTINCT会试效率降低很多，因为它要遍历表，不推荐使用

(10)应用ORDER BY 子句
这里要将VTB9表里的排序后，注意返回的是一个游戏VC10.这里的ORDER BY后面可以用SELECT后面的别名.
这里有个地方要注意：如果你用了DISTINCT 那么你的ORDER BY 只能访问那个VTB9这个虚拟表。这的意思这么讲抽象，我举例子：
select distinct sname,MIN(score) as minscore 我后面ORDER BY 的时候只能order by sname 不能order by sname+'a'
如果你用 select distinct sname,MIN(score) as minscore OK 你可以用order by sname+'a'
PS:这步返回一个游标而不是一个虚拟表原因是这样：SQL是基于集合理论的。集合不会排序，只是包含数据。但是你排序后它是有特定物理
组织的行，ANSI就把这个对象叫做游标.也是因为OEDER BY返回的不是表，所以我们知道了使用了ORDER BY 的SQL语句不可以再试图，子查询，派生表，
CTE等表表达式中使用了。还有一般如果没有特殊要求，别去排序，那是需要成本的。
话说回来，这次返回的游标VC10：

sname minsocre

---------- -----------

xiaozhu 90

xiaomao 98

(11)应用TOP选项
SQL2005里面允许你在TOP后面加变量了，多好啊。(2000里关于top +@n 的解决办法看http://blog.csdn.net/feixianxxx/archive/2009/08/03/4405684.aspx)
通过限定，返回VTB11
这里提个东西:如果你没有指定ORDER BY 子句，带有TOP的查询不确定。它返回的行正好是SQL物理上最先访问的行。

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/feixianxxx/archive/2009/10/18/4694354.aspx