SQL Server 查詢分解

pursuer.chen發表於2014-12-17
SQLServer

標籤：SQL SERVER/MSSQL SERVER/資料庫/DBA/查詢步驟

概述

      查詢步驟是很基礎也挺重要的一部分，但是我還是在周圍發現有些人雖然會語法，但是對於其中的步驟不是很清楚，這裡就來分解一下其中的步驟,在技術內幕系列裡面都會有講到。

 

目錄

流程圖

 (1)FROM <LEFT_TABLE> <JOIN_TYPE> JOIN <RIGHT_TABLE> ON <ON_PREDICATE>
         |<LEFT_TABLE> <APPLY_TYPE> APPLY <RIGHT_TABLE_EXPRESSION> AS <alias>
         |<LEFT_TABLE> pivot(<pivot_specification>) AS <alias>
         |<LEFT_TABLE> UNPIVOT(<unpivot_specification>) AS <alias>
 (2)WHERE<where_predicate>
 (3)GROUP BY<group_by_specification>
 (4)HAVING<having_predicate>
 (5)SELECT <DISTINCT> <TOP> <select_list>
 (6)ORDER BY<order_by_list>

步驟分解

測試資料

--建立測試表

--建立顧客表
CREATE TABLE Customers
(custid INT NOT NULL PRIMARY KEY,
city NVARCHAR(20) NOT NULL
)
go
INSERT INTO Customers VALUES(1,'深圳'),(2,'廣州'),(3,'武漢'),(4,'上海'),(5,'北京')

--建立訂單表
CREATE TABLE Orders
(orderid INT NOT NULL PRIMARY KEY IDENTITY(1000,1),
custid INT NOT NULL,
orderdate DATETIME NOT NULL
)
GO
INSERT INTO Orders(custid,orderdate)values(1,'2013-10-1 00:00:00'),(1,'2013-10-2 00:00:00'),(1,'2013-10-3 00:00:00'),(1,'2013-10-4 00:00:00'),(2,'2013-10-1 00:00:00'),(2,'2013-10-3 00:00:00'),(2,'2013-10-5 00:00:00'),(3,'2013-10-3 00:00:00'),(3,'2013-10-7 00:00:00'),(4,'2013-10-1 00:00:00')

--建立訂單明細表
CREATE TABLE [OrderDetails](
    [orderid] [int] NOT NULL,
    [productid] [int] NOT NULL,
    [unitprice] [money] NOT NULL,
    [qty] [smallint] NOT NULL
 CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED 
(
    [orderid] ASC,
    [productid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO OrderDetails VALUES(1000,10,5.00,1),(1000,14,6.00,2),(1001,10,5.31,3),(1001,11,5.22,1),(1001,12,3.20,3),(1001,13,4.10,2),(1002,11,7.00,1),(1003,12,8.00,5),(1004,13,8.41,1),(1004,11,6.65,1),(1005,18,7.41,1),(1006,17,10.00,1)


--查詢深圳、廣州每一個顧客每筆金額大於10的訂單,並按訂單價格倒序排序
SELECT TA.custid,TB.orderid,SUM(tc.unitprice*tc.qty) AS price FROM Customers TA LEFT JOIN Orders TB ON TA.custid=TB.custid LEFT JOIN OrderDetails TC ON TB.orderid=tc.orderid
WHERE TA.city IN('深圳','廣州')
GROUP BY TA.custid,TB.orderid
HAVING SUM(tc.unitprice*tc.qty)>10
ORDER BY price DESC

 

第一步:FROM階段

這一步是一個T-SQL語句的開始，一般緊接著FROM的這個表被稱作左表，例如a inner join b inner join c,首先a作為左表然後關聯b,a和b關聯的結果作為下一個運算的左表關聯c。在FROM階段涉及的表運算會有JOIN（LEFT JOIN,RIGHT JOIN,FULL JOIN),APPLY(CROSS APPLY,OUTER APPLY),PIVOT,UNPIVOT

對於上面的查詢例子：FROM Customers TA LEFT JOIN Orders TB ON TA.custid=TB.custid的左連線的分解是這樣的

第一步交叉連線、SELECT * FROM Customers TA CROSS JOIN Orders TB---首先進行交叉連線得到的行數是5*10=50行

第二步ON篩選、將TA.custid=TB.custid以外的結果排除,可以等價於SELECT * FROM Customers TA CROSS JOIN Orders TB WHERE TA.custid=TB.custid
第三步、將主表(左邊的表)不在第二步的行加上,可以等價於 SELECT * FROM Customers TA CROSS JOIN Orders TB WHERE TA.custid=TB.custid union all SELECT * FROM Customers TA LEFT JOIN Orders TB ON TA.custid=TB.custid WHERE TB.custid IS NULL

 所以其它幾個表運算只要大家知道怎麼使用就可以了，大家只要明白它在T-SQL語句中的位置就行。

這裡要注意一點：大家理解了JEFT JOIN的原理之後就明白"on"篩選對查詢的刪除不是最終的，在上面的第三步會把主表的一些行又新增上來，所以我們有時候寫LEFT JOIN的時候有的人不太明白為什麼ON 後面加AND和把AND放在WHERE裡面的得到的結果不一樣，就是這個原理了，WHERE操作對查詢的刪除才是最終的。

第二步：WHERE階段

當然後面的有些階段都是可選的也就是有的查詢不一定會用到，但是這裡為了講述整個過程，所以就一步一步的來講，在FROM 階段結束之後會生成一張虛擬表，進入第二階段也就是WHERE階段，在WHERE階段是對前一階段（FROM階段）結果返回行進行篩選，例如上面的查詢篩選城市是‘深圳’，‘廣州’的顧客

所以為什麼把select步驟裡面生成的列寫在where裡面無法識別就是因為where在select操作之前。

第三步：GROUP BY階段

 GROUP BY 操作是分組操作，確保進行分組的屬性集每一個組都是唯一的，GROUP BY 操作的資料是WHERE階段篩選之後的資料，例如上面的查詢例子是將custid,orderid作為一行來進行分組，上面的例子是每一個顧客每一筆訂單的消費金額。

第四步：HAVING階段

HAVING階段是在GOUP BY 階段返回TURE之後才會有這步操作，HAVING是對上一步的分組之後的資料進行篩選的步驟，例如篩選消費訂單金額大於10的顧客訂單

第五步：SELECT階段

select階段是返回上一步操作得到的虛擬表的資料列，所以也就是為什麼存在group by的分組查詢，select裡面的列跟group by 的分組列需要一致的原因了，聚會函式生成的列除外，因為select查詢的基礎列就是來源於前面的步驟，select階段會涉及到去重複distinct當然如果前面存在分組也就不存在重複了,TOP操作，還有一些欄位之間的演算法運算，子查詢等等。

第六步：ORDER BY階段

這一步是整個過程的最後一步操作，因為它在SELECT階段之後，所以對於SELECT裡面生成的欄位別名在ORDER BY 中可以使用別名，對於一張表，表代表的是集合，集合是沒有順序的，當一個查詢帶有ORDER BY時我們可以把它理解成遊標，遊標是有特定的排序，所以為什麼一個查詢加上ORDER BY 操作之後會變的很慢了，因為它需要進行排序操作。

---當查詢沒有排序時
SELECT * FROM Orders

---當查詢有排序時
SELECT * FROM Orders
ORDER BY CUSTID

TOP於ORDER BY的關係

order by 是保證結果排序順序，top是一個邏輯運算操作

對於一個沒有外部查詢的語句，order by 操作既能保證結果根據制定條件的排序，又能滿足TOP的邏輯運算（查詢最小的三個orderid）
SELECT TOP (3) * FROM Orders
ORDER BY ORDERID
對於存在外部查詢時,order by在作用僅僅是保證top的邏輯結果的正確輸出，而不能保證查詢結果的排序，雖然我們可能查詢出的結果是按照這個方式排序。
---當不指定TOP時報錯
SELECT * FROM(SELECT  custid,orderid,orderdate FROM Orders ORDER BY orderdate DESC) AS A
---當指定
SELECT * FROM(SELECT TOP (3) custid,orderid,orderdate FROM Orders ORDER BY orderdate DESC) AS A

 

總結

     理解完了整個查詢的過程，也就能能理解為什麼SQLServer這麼耗記憶體了，每一步的操作都是生成一張虛擬表進入下一步操作，理解了整個查詢過程 之後對我們理解T-SQL語法很有幫助，同時也有利於分析語句。

如果文章對大家有幫助，希望大家能給個贊，謝謝！！！

 

備註：

    作者：pursuer.chen

    部落格：http://www.cnblogs.com/chenmh

本站點所有隨筆都是原創，歡迎大家轉載；但轉載時必須註明文章來源，且在文章開頭明顯處給明連結，否則保留追究責任的權利。

《歡迎交流討論》

相關文章