SQL Server 查詢分解

pursuer.chen發表於2014-12-17

標籤: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

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

《歡迎交流討論》

相關文章