MS SQL Server 2005 邏輯查詢(xún)處理中的各個(gè)階段(一)
大家好，我是浪客，和大家分享一些最近我從《Microsoft SQL SERVER 2005技術(shù)內幕：T-SQL查詢(xún)》書(shū)中的心得，時(shí)間倉促，有錯誤大家盡管提出來(lái)。
先來(lái)看個(gè)查詢(xún):
(8) SELECT (9) DISTINCT (11) <TOP_specification> <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_codition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
熟悉SQL的人應該一眼就能看出來(lái)上面語(yǔ)句的作用，基本包括了我們一般查詢(xún)表的寫(xiě)法，SQL不同于其他編程語(yǔ)言最明顯的特征就是他的代碼順訓。在大多數語(yǔ)言中，代碼按編碼的順序被處理，但在SQL語(yǔ)言中，第一個(gè)被處理的子句是FROM子句，盡管SELECT語(yǔ)句第一個(gè)出現，但幾乎總是最后處理。
其實(shí)從邏輯上來(lái)說(shuō)，每個(gè)步驟都會(huì )產(chǎn)生一個(gè)虛擬表，該虛擬表用作下一個(gè)步驟的輸入，下面就一步一步來(lái)分析，由于我們只分析其中的邏輯處理順序，所以很多效率問(wèn)題沒(méi)有考慮太多，關(guān)于查詢(xún)優(yōu)化的文章，我會(huì )在以后慢慢分享給大家，呵呵（上面的標記代表執行步驟的順序）。
為了演示這些步驟，我們打開(kāi)MS SQL SERVER 2005 -SQL Server Management Studio新建一個(gè)查詢(xún)輸入：
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
SELECT 'FISSA','Madrid'
UNION ALL
SELECT 'FRNDO','Madrid'
UNION ALL
SELECT 'KRLOS','Madrid'
UNION ALL
SELECT 'MRPHS','Zion'
CREATE TABLE dbo.orders
(
orderid INT NOT NULL PRIMARY KEY,
customerid CHAR(5) NULL REFERENCES customers(customerid)
);
INSERT INTO dbo.orders
SELECT 1,'FRNDO'
UNION ALL
SELECT 2,'FRNDO'
UNION ALL
SELECT 3,'KRLOS'
UNION ALL
SELECT 4,'KRLOS'
UNION ALL
SELECT 5,'KRLOS'
UNION ALL
SELECT 6,'MRPHS'
UNION ALL
SELECT 7,NULL
我們建立了兩個(gè)表，一個(gè)是customers 客戶(hù)表，字段按分別為id（用戶(hù)的姓名） 和城市，
Orders表是訂單 字段分別為 id和客戶(hù)的姓名，下面我們再新建一個(gè)查詢(xún)：
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;
運行結果如下：
FISSA 0
FRNDO 2
查詢(xún)的作用我想大家都應該知道吧，就不說(shuō)了
1. FROM：對FROM子句中的前兩個(gè)表執行笛卡爾積（交叉連接 CROSS JOIN）生成一個(gè)虛擬表VIRTUAL TABLE 1 簡(jiǎn)稱(chēng)VT1。這個(gè)過(guò)程是怎么樣的，我們大致可以用一個(gè)查詢(xún)來(lái)表示代碼如下：
SELECT c.customerid,c.city,o.orderid,o.customerid
FROM customers AS c
CROSS JOIN orders AS o
運行結果如下：
切圖切不下來(lái)大家講究下，我直接Copy 下來(lái)的。
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
2. 嘿嘿，聰明的朋友可能已經(jīng)發(fā)現CROSS JOIN 所謂的笛卡爾積其實(shí)就是為限定的聯(lián)接，也就是說(shuō)，如果左表包含n，右表包含m，總結果就是n*m行的結果，細心數一下就是28行。前面的orders有7條記錄，customers有4條記錄，總結果就有4*7=28行。所以前面我也就說(shuō)過(guò)，其實(shí)最先執行的是FROM子句，也就是先獲取最先的兩個(gè)表的笛卡爾積。
下面我們進(jìn)行第2個(gè)步驟的演示，看最上面的那個(gè)查詢(xún)語(yǔ)句，接下來(lái)就要執行的是ON條件的篩選（ON,WHERE,HAVING）中的第一個(gè)，ON篩選器中的邏輯表達式被應用到上一步的返回的虛擬表VT1中的所有行。只有使 <join_codition> 的條件為TRUE 時(shí)候那些行才會(huì )被包含到步驟2返回的虛擬表VT2中。
在這里我給大家說(shuō)個(gè)相關(guān)聯(lián)的知識，也很重要噢。
在SQL中邏輯表達式的可能值包括TRUE,FALSE,UNKOWN。他們就叫三值邏輯，SQL中的UNKNOWN邏輯值通常出現在包含NULL值的邏輯表達式中（例如，NULL>42,NULL=NULL）。NULL值通常表示丟失或者不相關(guān)的值。當比較丟失值和另一個(gè)值（也可能是NULL）時(shí)，邏輯結果總是為UNKONWN。
下面我給大家總結了幾個(gè)用法：
1.在篩選語(yǔ)句中（ON,WHERE,HAVING）中所有UNKONWN都當作FALSE來(lái)處理，包括NULL IS NULL==之類(lèi)的語(yǔ)句
2.在CHECK約束中，當作TRUE處理，比如你的CHECK 約束為 salary>30 當用戶(hù)插入一個(gè)NULL值的時(shí)候，NULL>30 是返回TRUE的。
3.在UNIQUE唯一約束中，當作TRUE來(lái)處理，所以不能插入兩個(gè)都為NULL的值。
4.GROUP BY 和 ORDER BY 都能把NULL分到一起去。
所以當
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;
執行的時(shí)候 ON 語(yǔ)句把剛才獲取的28行記錄篩選成如下：
代碼：
SELECT c.customerid,c.city,o.orderid,o.customerid
FROM customers AS c
CROSS JOIN orders AS o
WHERE c.customerid=o.customerid
大家不要看到是WHERE就條件就忽略了 ON CROSS JOIN 不能使用 ON 語(yǔ)句作為連接條件我就用WHERE 模擬一下哈，要記住執行順序還是ON哦～。
結果如下：
FRNDO Madrid 1 FRNDO
FRNDO Madrid 2 FRNDO
KRLOS Madrid 3 KRLOS
KRLOS Madrid 4 KRLOS
KRLOS Madrid 5 KRLOS
MRPHS Zion 6 MRPHS
是不是發(fā)現不同customerid 和有NULL出現的記錄全部給PASS了，哇哈哈，不要激動(dòng)下一步。
（廁所中。。。）
3. 回來(lái)了，好的我們繼續，下一步就是步驟3，也就是傳說(shuō)中的<join_type> JOIN ，上一步我們把VT2該過(guò)濾的都已經(jīng)搞定了，所以要給他的外部鏈接OUTER JOIN 指定一種類(lèi)型
就我現在所學(xué)的知識大概就知道（LEFT,RIGHT,FULL之類(lèi)的），
其實(shí)說(shuō)起來(lái)很簡(jiǎn)單,這幾種連接很簡(jiǎn)單，通過(guò)上面的例子
LEFT JOIN 就是把左邊的表 也就是在我們這里的customers表的記錄作為保留表，就是把他的所有記錄又添加進(jìn)來(lái)，RIGHT JOIN 就是右邊的表 呵呵，FULL 就是全連接也就是把記錄又重新全部給添加進(jìn)來(lái)了 右邊和左邊的，步驟3返回VT2中的行以及保留表在步驟2被過(guò)濾的行，看如下：
SELECT c.customerid,c.city,o.orderid,o.customerid
FROM customers AS c
LEFT OUTER JOIN orders AS o
ON c.customerid=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
我對比 步驟2的返回結果和 步驟3的 發(fā)現只多出來(lái)一行
FISSA Madrid NULL NULL
看看是為什么呢？
步驟2中的左表是customers 一共有FRNDO，KRLOS，MRPHS這三個(gè)大哥，LEFT OUTER JOIN 毛了，所以他就把剩下的被T出去的FISSA 給拉了回來(lái)，也就是FISSA ，所以就有了這條記錄：
FISSA Madrid NULL NULL，
提示：只有在使用外部連接的時(shí)候才會(huì )執行步驟3，同時(shí)如果有多個(gè)OUTER JOIN 會(huì )一直重復步驟1，2，3知道所有表都被遍歷了，當然在使用GROUP BY ALL選項的時(shí)候有一些例外，精彩一會(huì )繼續。
4. 我們繼續步驟4，WHERE 大哥篩選器，也就是只有符合<where_condition>條件的記錄才會(huì )返回到虛擬表VT4，在這里我們又來(lái)說(shuō)說(shuō)幾個(gè)小技巧。
首先在數據還沒(méi)有分組的時(shí)候，我們不能是喲哦那個(gè)聚合篩選器，例如，不能使用WHERE orderdate=MAX(orderdate)。也不能引用SELECT 列表中的別名，因為SELECT列表這時(shí)還未被處理，SELECT 在步驟8呢，就像引用當然出錯，例如不能使用SELECT YEAR(orderdate) AS u WHERE u>2000。在這里的條件是WHERE c.city=’Madrid’，代碼如下：
SELECT c.customerid,c.city,o.orderid,o.customerid
FROM customers AS c
LEFT OUTER JOIN orders AS o
ON c.customerid=o.customerid
WHERE c.city='Madrid'
結果：
FISSA Madrid NULL NULL
FRNDO Madrid 1 FRNDO
FRNDO Madrid 2 FRNDO
KRLOS Madrid 3 KRLOS
KRLOS Madrid 4 KRLOS
KRLOS Madrid 5 KRLOS
發(fā)現所有的記錄城市這一列都是Madrid，所以生成的虛擬表VT4，就是這樣的
5. 步驟5，也就是執行接下來(lái)的GROUP BY 分組操作了，說(shuō)白了，就是把每個(gè)唯一的值組合在一組，而且只有一組哦，一組很關(guān)鍵，下面就知道了呵呵。如果我們認為簡(jiǎn)單就是執行
SELECT c.customerid,c.city,o.orderid,o.customerid
FROM customers AS c
LEFT OUTER JOIN orders AS o
ON c.customerid=o.customerid
WHERE c.city='Madrid'
GROUP BY c.customerid
我們就錯了，運行我們會(huì )得到
消息8120，級別16，狀態(tài)1，第1 行
選擇列表中的列'customers.city' 無(wú)效，因為該列沒(méi)有包含在聚合函數或GROUP BY 子句中。這樣的錯誤，
原因可想而知，我們先假設條件成立 返回結果是：
FISSA Madrid NULL NULL
FRNDO Madrid 1 FRNDO
FRNDO Madrid 2 FRNDO
KRLOS Madrid 3 KRLOS
KRLOS Madrid 4 KRLOS
KRLOS Madrid 5 KRLOS
也都分組了，可是我們發(fā)現
FRNDO Madrid 1 FRNDO
FRNDO Madrid 2 FRNDO
KRLOS Madrid 3 KRLOS
KRLOS Madrid 4 KRLOS
KRLOS Madrid 5 KRLOS
中都有同名的FRNDO和KRLOS 也就是說(shuō)，他們不能分在一組了，因為他們重名了，前面說(shuō)過(guò)每個(gè)唯一的值組合在一組，而且只有一組，可是現在出現了
FRNDO Madrid 1 FRNDO
FRNDO Madrid 2 FRNDO
和
KRLOS Madrid 3 KRLOS
KRLOS Madrid 4 KRLOS
KRLOS Madrid 5 KRLOS
這個(gè)情況，雖然我們能理解他們是一組，可SQL 不這么認為，因為記錄不唯一，所以我們不能這么做，GROUP BY 只能操作列表中的列或者聚合函數。
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
也就只有這樣做了，
FISSA 0
FRNDO 2
KRLOS 3
沒(méi)個(gè)記錄都唯一，也都是一組。
前面我們提到了 GROUP BY ALL，他是微軟的非標準遺留物，不怎么推薦使用噢，使用了 GROUP BY c.customerid 以后 在前面用WHERE 篩選的記錄有會(huì )重新被添加到虛擬表中VT5中來(lái)噢，也就是MRPHS 這條記錄，你可以自己嘗試一下。
6. 步驟6，我以后和大家說(shuō) CUBE和ROLLUP
7. 用HAVING 篩選器，只有符合條件<having_condition>的組才會(huì )添加到虛擬表VT6中來(lái)，HAVING是第一個(gè)也是唯一一個(gè)應用到已分組數據的篩選器哦，
HAVING COUNT(o.orderid)<3，我們查找 訂單數量小于3的所有記錄，因為
FISSA 0
FRNDO 2
KRLOS 3
中的 KRLOS 因為訂單數量=3 所以把他K了哦，返回虛擬表VT7。
這里同樣給大家提示，指定COUNT（o.orderid）而不是COUNT(*)是非常重要的,因此該鏈接是外部聯(lián)接,沒(méi)有訂單的消費者將作為外部行添加到結果集，所以無(wú)法準備統計FISSA的訂單數。COUNT（o.orderid）會(huì )準確統計每個(gè)消費者的訂單數。順便子查詢(xún)中不能用聚合函數的輸入，例如，HAVING SUM((SELECT …))>10。
8. 處理SELECT 了，這個(gè)太簡(jiǎn)單了，不用我多說(shuō)了吧，步驟8中，有個(gè)小技巧提示一下:
UPDATE dbo.t1 set c1=c2,c2=c1;是成立的，因為存儲引擎會(huì )瞬間操作，所以不用擔心，要使用一個(gè)中間變量來(lái)交換他們的值，呵呵。
9. 如果語(yǔ)句引用了DISTINCT子句，將從上一步返回的虛擬表中移除重復的行，生成虛擬表9了。在我們的示例查詢(xún)中，沒(méi)有使用，不過(guò)如果使用了GROUP BY ，再使用DISTINCT是多余的，因為GROUP BY 不可能會(huì )先記錄一樣的哦，他分組是唯一的，前面我們說(shuō)到了。
10. 應用到了ORDER BY ，這里就有很多知識了，這是唯一一個(gè)不生成虛擬表的操作步驟，他其實(shí)是返回一個(gè)游標。這一步也是唯一一個(gè)可以使用SELECT 列表中的列表名，因為他在SELECT后面發(fā)生，嘿嘿奸詐吧。ORDER BY 中 2000 數據庫和2005 有點(diǎn)區別，具體我就不細說(shuō)了，2005他實(shí)現ANSI:1999的規定，允許訪(fǎng)問(wèn)SELECT 階段的輸入虛擬表和輸出虛擬表，也就是，如果未指定DISTINCT，你可以在ORDER BY 中子句中使用任何在SELECT 中出現的表達式，也就是說(shuō)，你可以按最后結果集中不存在的表達式排序，例如一表有t1,t2，字段你就可以使用select t1 from 表 order by t2 ，t2沒(méi)有出現在select列表中，但是t2可以出現在t2 中，所以可以實(shí)現。但是指定了DISTINCT就不能訪(fǎng)問(wèn)未返回的表達式。
還有就是ORDER BY 子句的查詢(xún)不能用作表表達式，表表達式包括，視圖、內聯(lián)表值函數、子查詢(xún)、派生表、和共用表表達式(CTE)。
下面是錯誤的：
Select * from (select * from table order by date ) as d;
和
Create view dbo.err
As
Select * from table order by date desc。
11. 最后一步是TOP選項。好累啊。簡(jiǎn)單說(shuō)下，top 是T-SQL 特有的不屬于關(guān)系范疇。這一步根據物理查詢(xún)確定哪些行被優(yōu)先請求。
當ORDER BY 中是唯一的ORDER BY ID 只有一個(gè)子句，結果是確定的，多了就也許不確定噢，不過(guò)加了 TOP選項后面加了 WITH TIES 結果也就是唯一的啦。如果沒(méi)有指定ORDER BY 或者指定了不唯一的ORDER BY 而未制定WITH TIES 結果是不確定的 。所以只有指定了ORDER BY 才用TOP哦。
總結：這就是SQL SERVER 2005中的查詢(xún)邏輯處理階段，可是實(shí)際的物理查詢(xún)也有與此有區別的。我們會(huì )在下一個(gè)章節中提到。就這樣再見(jiàn)。
聯(lián)系客服
