第 9 章 T-SQL語法與參數的SQL查詢

1. 第9章 T-SQL語法與參數的SQL查詢

2. 第9章 T-SQL語法與參數的SQL查詢 • 9-1 SQL語言的基礎 • 9-2 SELECT敘述的基本查詢 • 9-3 建立參數的SQL查詢 • 9-4 新增、更新與刪除記錄

3. 9-1 SQL語言的基礎-說明 • 「SQL」（Structured Query Language）的全名是結構化查詢語言，本書簡稱SQL語言。SQL語言是「ANSI」（American National Standards Institute）制定的標準資料庫語言，其版本分為1989年的ANSI-SQL 89和1992年制定的ANSI-SQL 92，也稱為SQL 2。 • 1974年Chamberlin和Boyce建立SEQUEL語言，這是SQL的原型。IBM稍加修改後作為其關聯式資料庫管理系統的資料庫語言，稱為System R，1980年SQL的名稱正式誕生，從哪天開始，SQL逐漸壯大成為一種標準的關聯式資料庫語言。

4. 9-1 SQL語言的基礎-指令種類 • 「資料定義語言DDL」（Data Definition Language）：資料表建立和欄位定義的SQL語法。 • 「資料操作語言DML」（Data Manipulation Language）：資料表記錄插入、刪除和更新的INSERT、DELETE和UPDATE指令，和資料表查詢的SELECT指令。 • 「資料控制語言DCL」（Data Control Language）：資料庫安全設定和權限管理的相關指令。

5. 9-1 SQL語言的基礎-指令 • 在ASP.NET網頁資料庫的程式碼，或SqlDataSource控制項的SelectCommand、InsertCommand、UpdateCommand和DeleteCommand屬性，都可以使用SQL指令來執行資料庫操作和查詢，其基本指令如下表所示：

6. 9-2 SELECT敘述的基本查詢 • 9-2-1 SELECT子句 • 9-2-2 WHERE子句的比較運算子 • 9-2-3 WHERE子句的邏輯運算子 • 9-2-4 WHERE子句的算術運算子 • 9-2-5 聚合函數的摘要查詢 • 9-2-6 排序ORDER BY子句

7. 9-2 SELECT敘述的基本查詢-語法 • SELECT指令是DML指令中語法最複雜的一個，其基本語法如下所示： SELECT 欄位清單 FROM 資料表來源 [WHERE 搜尋條件] [ORDER BY 欄位清單]

8. 9-2 SELECT敘述的基本查詢-子句說明 • SELECT指令各子句的說明，如下表所示：

9. 9-2 SELECT敘述的基本查詢-查詢工具 • 在本節說明的SQL指令是使用ASP.NET網頁的SQL查詢工具來測試查詢結果。請啟動VWD開啟「範例網站\Ch09\Ch9-2」資料夾的ASP.NET網站，執行SQLQuery.aspx網頁，如下圖所示：

10. 9-2-1 SELECT子句-語法 • 在SELECT指令的SELECT子句可以指定查詢結果包含哪些欄位，其語法如下所示： SELECT [ALL | DISTINCT] 欄位名稱 [[AS] 欄位別名] [, 欄位名稱 [[AS] 欄位別名]] • 上述ALL是預設值可以顯示所有記錄的欄位值，DISTINCT只顯示不重複欄位值的記錄。

11. 9-2-1 SELECT子句-查詢資料表的部分欄位 • SELECT子句可以指明查詢結果所需的欄位清單，只查詢資料表中的部分欄位。例如：查詢Books資料表的圖書記錄，只顯示BookID、BookTitle和BookPrice三個欄位，如下所示： SELECT BookID, BookTitle, BookPrice FROM Books

12. 9-2-1 SELECT子句-查詢資料表的所有欄位 • 查詢結果如果需要顯示資料表的所有欄位，SELECT指令可以直接使用「*」符號代表資料表的所有欄位，而不用一一列出欄位清單。 • 例如：查詢Books資料表的所有圖書記錄且顯示所有欄位，如下所示： SELECT * FROM Books • 上述SELECT指令的執行結果顯示Books資料表的所有記錄和欄位。

13. 9-2-1 SELECT子句-欄位別名(說明) • SELECT指令預設使用資料表定義的欄位名稱來顯示查詢結果，基於需要，我們可以使用AS關鍵字指定欄位別名，其中AS關鍵字本身可有可無。

14. 9-2-1 SELECT子句-欄位別名(範例) • 例如：查詢Books資料表的BookID、BookTitle和BookPrice三個欄位資料，顯示欄位名稱為書號、書名和書價的中文欄位別名，如下所示： SELECT BookID AS 書號, BookTitle AS 書名, BookPrice AS 書價 FROM Books

15. 9-2-1 SELECT子句-刪除重複記錄 - ALL與DISTINCT • 如果資料表記錄的欄位有重複值，SELECT子句的預設值ALL是顯示所有欄位值，我們可以使用DISTINCT關鍵字刪除重複欄位值，一旦欄位擁有重複值，就只會顯示其中一筆記錄。 • 例如：查詢Books資料表的圖書資料擁有幾種不同的書價，如下所示： SELECT DISTINCT BookPrice FROM Books

16. 9-2-2 WHERE子句的比較運算子-語法 • SELECT指令和FROM字句是指出查詢哪個資料表的哪些欄位，WHERE子句的篩選條件才是真正的查詢條件，可以過濾記錄和找出符合條件的記錄資料，其基本語法如下所示： WHERE 搜尋條件 • 上述搜尋條件就是使用比較和邏輯運算子建立的過濾條件，查詢結果可以取回符合條件的記錄資料。

17. 9-2-2 WHERE子句的比較運算子-比較運算子 • T-SQL支援的比較運算子（Comparison Operators）說明，如下表所示：

18. 9-2-2 WHERE子句的比較運算子-條件值為字串 • WHERE子句的條件運算式可以使用比較運算子來執行字串比較，欄位條件的字串需要使用單引號括起。例如：在Books資料表查詢書號為'FS499'圖書的詳細資料，如下所示： SELECT * FROM Books WHERE BookID = 'FS499'

19. 9-2-2 WHERE子句的比較運算子-條件值為數值 • WHERE子句條件運算式的條件值如果是數值，數值不需使用單引號括起。例如：查詢Books資料表的BookPrice欄位小於600元的圖書記錄，如下所示： SELECT BookID, BookTitle, BookPrice FROM Books WHERE BookPrice < 600

20. 9-2-2 WHERE子句的比較運算子-條件值為日期/時間 • WHERE子句的條件運算式如果是日期/時間的比較，如同字串，也需要使用單引號括起。例如：查詢Books資料表的BookPubDate是2006-10-01的圖書記錄，如下所示： SELECT BookID, BookTitle, BookPrice FROM Books WHERE BookPubDate ='2006-10-01'

21. 9-2-3 WHERE子句的邏輯運算子-說明 • 在WHERE子句的搜尋條件可以使用邏輯運算子（Logical Operators）來連接條件運算式，以便建立複雜的搜尋條件。常用的邏輯運算子說明，如下表所示：

22. 9-2-3 WHERE子句的邏輯運算子- LIKE包含子字串運算子(說明) • WHERE子句的條件欄位可以使用LIKE運算子進行比較，LIKE運算子是子字串查詢，只需是子字串就符合條件。我們還可以配合萬用字元來進行範本字串的比對，如下表所示：

23. 9-2-3 WHERE子句的邏輯運算子- LIKE包含子字串運算子(範例1) • 例如：查詢Books資料表中，書名有Java的圖書記錄，如下所示： SELECT BookID, BookTitle, BookPrice FROM Books WHERE BookTitle LIKE '%Java%'

24. 9-2-3 WHERE子句的邏輯運算子- LIKE包含子字串運算子(範例2) • 例如：查詢Books資料表中，書號是P-S範圍字母開頭的圖書資料，如下所示： SELECT BookID, BookTitle, BookPrice FROM Books WHERE BookID LIKE '[P-S]%'

25. 9-2-3 WHERE子句的邏輯運算子- BETWEEN/AND範圍運算子 • BETWEEN/AND運算子可以定義欄位值需要符合的範圍，其範圍值可以是文字、數值或和日期/時間資料。例如：因為購書預算在590~620之間，所以我們可以查詢Books資料表看看有哪些書可以購買，如下所示： SELECT BookID, BookTitle, BookPrice FROM Books WHERE BookPrice BETWEEN 590 AND 620

26. 9-2-3 WHERE子句的邏輯運算子- IN運算子(說明) • IN運算子只需是清單其中之一即可，我們需要列出一串文字或數值清單作為條件，欄位值只需是其中之一，就符合條件。

27. 9-2-3 WHERE子句的邏輯運算子- IN運算子(範例) • 例如：我們準備購買書號FS499、F6472和P780三本書，所以查詢Books資料表關於這些書籍的詳細資料，如下所示： SELECT BookID, BookTitle, BookPrice FROM Books WHERE BookID IN ('FS499', 'F6472', 'P780')

28. 9-2-3 WHERE子句的邏輯運算子- NOT運算子(說明) • NOT運算子可以搭配邏輯運算子，取得與條件相反的查詢結果，如下表所示：

29. 9-2-3 WHERE子句的邏輯運算子- NOT運算子(範例) • 例如：我們已經買了FS499、F6472和P780三本書，所以準備查詢Books資料表，看看還有什麼圖書沒有購買，如下所示： SELECT BookID, BookTitle, BookPrice F ROM Books WHERE BookID NOT IN ('FS499', 'F6472', 'P780')

30. 9-2-3 WHERE子句的邏輯運算子- AND運算子 • AND運算子連接的前後運算式都必須同時為真，整個WHERE子句的條件才為真。例如：查詢Books資料表的BookID欄位包含'5'子字串，而且BookTitle有'程式'子字串，如下所示： SELECT BookID, BookTitle, BookPrice FROM Books WHERE BookID LIKE '%5%' AND BookTitle LIKE '%程式%'

31. 9-2-3 WHERE子句的邏輯運算子- OR運算子 • OR運算子連接的前後條件，只需任何一個條件為真，即為真。例如：查詢Books資料表的BookID欄位包含'5'子字串，或BookTitle有'程式'子字串，如下所示： SELECT BookID, BookTitle, BookPrice FROM Books WHERE BookID LIKE '%5%' OR BookTitle LIKE '%程式%'

32. 9-2-3 WHERE子句的邏輯運算子-連接多個條件與括號(說明) • 在WHERE子句的條件可以使用AND和OR來連接多個不同條件。其優先順序是位在括號中的運算式優先。

33. 9-2-3 WHERE子句的邏輯運算子-連接多個條件與括號(範例) • 例如：查詢Books資料表BookID欄位包含‘2’子字串，和BookTitle欄位有‘程式’子字串，或BookPrice大於等於600，後2個條件以括號括起，如下所示： SELECT BookID, BookTitle, BookPrice FROM Books WHERE BookID LIKE '%2%' AND (BookTitle LIKE '%程式%' OR BookPrice >=600)

34. 9-2-4 WHERE子句的算術運算子 • 在WHERE子句的運算式條件也支援算術運算子的加、減、乘、除和餘數，我們可以在WHERE子句的條件加上算術運算子。例如：查詢Books資料表的BookPrice在打八折後，售價大於500元的圖書記錄，如下所示： SELECT BookID, BookTitle, BookPrice FROM Books WHERE (BookPrice * 0.8) > 500

35. 9-2-5 聚合函數的摘要查詢-說明 • 「聚合函數」（Aggregate Functions）也稱為「欄位函數」（Column Functions），可以進行選取記錄欄位值的筆數、平均、範圍和統計函數，以便提供進一步欄位資料的分析結果。 • 一般來說，如果SELECT指令敘述擁有聚合函數，稱為「摘要查詢」（Summary Query）。

36. 9-2-5 聚合函數的摘要查詢-種類 • 常用的聚合函數說明，如下表所示：

37. 9-2-5 聚合函數的摘要查詢-COUNT()函數 • SQL指令可以配合COUNT()函數計算查詢的記錄數，「*」參數可以統計資料表的所有記錄數，或指定欄位來計算欄位不是空值的記錄數。 • 例如：查詢Books資料表的BookPrice高過600元的圖書種類，如下所示： SELECT COUNT(*) AS 圖書數 FROM Books WHERE BookPrice > 600

38. 9-2-5 聚合函數的摘要查詢-AVG()函數 • SQL指令只需配合AVG()函數，就可以計算指定欄位的平均值。例如：在Books資料表查詢BookID包含'5'子字串的圖書總數，和書價的平均值，如下所示： SELECT COUNT(*) AS 圖書總數, AVG(BookPrice) AS 書價平均值 FROM Books WHERE BookID LIKE '%5%'

39. 9-2-5 聚合函數的摘要查詢-MAX()函數 • SQL指令只需配合MAX()函數，就可以計算符合條件記錄的欄位最大值。例如：在Books資料表查詢BookID包含'5'子字串的最貴書價，如下所示： SELECT MAX(BookPrice) AS 最貴書價 FROM Books WHERE BookID LIKE '%5%'

40. 9-2-5 聚合函數的摘要查詢-MIN()函數 • SQL指令如果配合MIN()函數，就可以計算出符合條件記錄的欄位最小值。例如：在Books資料表查詢BookID包含'5'子字串的最低書價，如下所示： SELECT MIN(BookPrice) AS 最低書價 FROM Books WHERE BookID LIKE '%5%'

41. 9-2-5 聚合函數的摘要查詢-SUM()函數 • SQL指令配合SUM()函數，可以計算出符合條件記錄的欄位總和。例如：在Books資料表計算圖書書價的總和和平均，如下所示： SELECT SUM(BookPrice) AS 書價總額, SUM(BookPrice)/COUNT(*) AS 書價平均 FROM Books

42. 9-2-6 排序ORDER BY子句-語法 • SELECT指令可以使用ORDER BY子句依照欄位由小到大或由大到小進行排序，其語法如下所示： ORDER BY 運算式 [ASC | DESC] [, 運算式 [ASC | DESC] • 上述語法的排序方式預設是由小到大排序的ASC，如果希望由大至小，請使用DESC關鍵字。

43. 9-2-6 排序ORDER BY子句-範例 • 例如：在Books資料表查詢BookPrice小於等於600元的圖書記錄，並且使用BookPrice欄位進行由大至小排序，如下所示： SELECT BookID, BookTitle, BookPrice FROM Books WHERE BookPrice <= 600 ORDER BY BookPrice DESC

44. 9-3 建立參數的SQL查詢 • 9-3-1 從TextBox控制項取得參數值 • 9-3-2 從DropDownList控制項取得參數值 • 9-3-3 從URL參數取得SQL的參數值 • 9-3-4 改為使用Session變數取得SQL參數值

45. 9-3 建立參數的SQL查詢 • 在SqlDataSource控制項使用設定資料來源精靈建立的SQL指令敘述，就是指定SelectCommand屬性值，可以切換原始檔檢視來檢視SelectCommand屬性值的SQL指令敘述。 • WHERE子句的條件不只可以是常數，還可以使用其他控制項、Session變數或QueryString的URL參數值來建立過濾條件，即建立參數的SQL查詢，此時過濾條件的參數值不是常數值，而是從其他來源取得的參數值。

46. 9-3-1 從TextBox控制項取得參數值-說明 • 參數的SQL查詢可以從TextBox控制項取得參數值，例如：在TextBox控制項輸入書名的部分子字串，就可以在GridView控制項顯示符合條件的記錄資料。

47. 9-3-1 從TextBox控制項取得參數值-設定 • 在SQL陳述式的步驟，按【WHERE】鈕新增WHERE子句，可以看到「加入WHERE子句」對話方塊。 在【資料行】欄選欄位【BookTitle】，【運算子】欄位選【LIKE】，來源選【Control】後，在右上方【控制項ID】欄位選【txtTitle】，按【加入】鈕建立WHERE子句

48. 9-3-1 從TextBox控制項取得參數值-範例網站 ASP.NET網站：Ch9-3-1 • 在ASP.NET網頁建立TextBox控制項取得參數值，以便建立參數的SQL查詢，可以查詢符合書名條件的圖書記錄，如下圖所示：

49. 9-3-2 從DropDownList控制項取得參數值-說明 • 參數的SQL查詢除了可以從TextBox控制項取得參數值外，我們也可以從DropDownList控制項取得參數值，例如：在DropDownList控制項選取圖書種類後，就可以在下方GridView控制項顯示此類別的圖書資料。 • 因為ASP.NET網頁的DropDownList控制項本身也是從SqlDataSource控制項取得資料，換句話說，此ASP.NET網頁共有2個SqlDataSource控制項，分別對應DropDownList和GridView控制項。

50. 9-3-2 從DropDownList控制項取得參數值-設定 • 在SQL陳述式的步驟，按【WHERE】鈕新增WHERE子句，可以看到「加入WHERE子句」對話方塊。 在【資料行】欄選欄位【BookCatalog】，【運算子】欄位選【=】，來源選【Control】後，在右上方【控制項ID】欄位選【DropDownList1】，按【加入】鈕建立WHERE子句