第 9 章 T-SQL 語法與參數的 SQL 查詢
大綱 SQL 語言的基礎 SELECT 敘述的基本查詢 建立參數的 SQL 查詢 新增 更新與刪除記錄 2
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 逐漸壯大成為一種標準的關聯式資料庫語言 3
指令種類 資料定義語言 DDL (Data Definition Language): 資料表建立和欄位定義的 SQL 語法 資料操作語言 DML (Data Manipulation Language): 資料表記錄插入 刪除和更新的 INSERT DELETE 和 UPDATE 指令, 和資料表查詢的 SELECT 指令 資料控制語言 DCL (Data Control Language): 資料庫安全設定和權限管理的相關指令 4
指令 在 ASP.NET 網頁資料庫的程式碼, 或 SqlDataSource 控制項的 SelectCommand InsertCommand UpdateCommand 和 DeleteCommand 屬性, 都可以使用 SQL 指令來執行資料庫操作和查詢, 其基本指令如下表所示 : SQL 指令 INSERT UPDATE DELETE SELECT 說明在資料表插入一筆新記錄更新資料表已經存在的記錄資料刪除資料表的記錄資料查詢資料表的記錄資料 5
SELECT 敘述的基本查詢 SELECT 子句 WHERE 子句的比較運算子 WHERE 子句的邏輯運算子 WHERE 子句的算術運算子 聚合函數的摘要查詢 排序 ORDER BY 子句 6
語法 SELECT 指令是 DML 指令中語法最複雜的一個, 其基本語法如下所示 : SELECT 欄位清單 FROM 資料表來源 [WHERE 搜尋條件 ] [ORDER BY 欄位清單 ] 7
子句說明 SELECT 指令各子句的說明, 如下表所示 : 子句 SELECT FROM WHERE ORDER BY 說明 指定查詢結果包含哪些欄位 指定查詢的資料來源是哪些資料表 過濾查詢結果的條件, 可以從資料表來源取得符合條件的查詢結果 指定查詢結果的排序欄位 8
查詢工具 SQL 指令是使用 ASP.NET 網頁的 SQL 查詢工具來測試查詢結果 請啟動 VWD 開啟 範例網站 \Ch09\Ch9-2 資料夾的 ASP.NET 網站, 執行 Default.aspx 網頁, 如右圖所示 : 9
SELECT 子句語法 在 SELECT 指令的 SELECT 子句可以指定查詢結果包含哪些欄位, 其語法如下所示 : SELECT [ALL DISTINCT] 欄位名稱 [[AS] 欄位別名 ] [, 欄位名稱 [[AS] 欄位別名 ]] 上述 ALL 是預設值可以顯示所有記錄的欄位值,DISTINCT 只顯示不重複欄位值的記錄 10
查詢資料表的部分欄位 SELECT 子句可以指明查詢結果所需的欄位清單, 換句話說, 我們可以只查詢資料表中的部分欄位 例如 : 查詢 Students 資料表的學生記錄, 只顯示 sid name 和 tel 三個欄位, 如下所示 : SELECT sid, name, tel FROM Students 11
查詢資料表的所有欄位 查詢結果如果需要顯示資料表的所有欄位, SELECT 指令可以直接使用 * 符號代表資料表的所有欄位, 而不用一一列出欄位清單 例如 : 查詢 Students 資料表的所有學生記錄且顯示所有欄位, 如下所示 : SELECT * FROM Students 上述 SELECT 指令的執行結果顯示 Students 資料表的所有記錄和欄位 12
欄位別名 SELECT 指令預設使用資料表定義的欄位名稱來顯示查詢結果, 基於需要, 我們可以使用 AS 關鍵字指定欄位別名, 其中 AS 關鍵字本身可有可無 13
刪除重複記錄 - ALL 與 DISTINCT 資料表記錄的欄位如果有重複值,SELECT 子句的預設值 ALL 是顯示所有欄位值, 我們可以使用 DISTINCT 關鍵字刪除重複欄位值, 一旦欄位擁有重複值, 只會顯示其中一筆記錄 例如 : 查詢 Courses 資料表的課程資料擁有幾種不同的學分數, 如下所示 : SELECT DISTINCT credits FROM Courses 14
WHERE 子句的比較運算子語法 SELECT 指令和 FROM 字句是指出查詢哪個資料表的哪些欄位,WHERE 子句的篩選條件才是真正的查詢條件, 可以過濾記錄和找出符合條件的記錄資料, 其基本語法如下所示 : WHERE 搜尋條件 上述搜尋條件就是使用比較和邏輯運算子建立的過濾條件, 查詢結果可以取回符合條件的記錄資料 15
WHERE 子句的比較運算子 T-SQL 支援的比較運算子 (Comparison Operators) 說明, 如下表所示 : 運算子說明 = 相等 <>!= 不相等 > 大於 >= 大於等於 < 小於 <= 小於等於!< 不小於!> 不大於 16
WHERE 子句的比較運算子條件值為 字串 WHERE 子句的條件運算式可以使用比較運算子來執行字串比較, 欄位條件的字串需要使用單引號括起 例如 : 在 Students 資料表查詢學號為 'S003' 學生的詳細資料, 如下所示 : SELECT * FROM Students WHERE sid = 'S003' 17
WHERE 子句的比較運算子條件值為 數值 WHERE 子句條件運算式的條件值如果是數值, 數值不需使用單引號括起 例如 : 查詢 Courses 資料表的 credits 欄位學分小於 4 的課程記錄, 如下所示 : SELECT * FROM Courses WHERE credits < 4 18
WHERE 子句的比較運算子條件值為 日期 / 時間 WHERE 子句的條件運算式如果是日期 / 時間的比較, 如同字串, 也需要使用單引號括起 例如 : 查詢 Students 資料表的 birthday 是 1967-09-03 的學生記錄, 如下所示 : SELECT * FROM Students WHERE birthday ='1967-09-03' 19
WHERE 子句的邏輯運算子 在 WHERE 子句的搜尋條件可以使用邏輯運算子 (Logical Operators) 來連接條件運算式, 以便建立複雜的搜尋條件 常用的邏輯運算子說明, 如下表所示 : 運算子 LIKE BETWEEN/AND IN NOT AND OR 說明包含, 只需子字串即符合條件在一個範圍之內屬於清單其中之一非, 可以否定運算式的結果且, 需要連接的 2 個運算子都為真, 才是真或, 只需其中一個運算子為真, 即為真 20
LIKE 字串運算子 WHERE 子句的條件欄位可以使用 LIKE 運算子進行比較,LIKE 運算子是子字串查詢, 只需是子字串就符合條件 我們還可以配合萬用字元來進行範本字串的比對, 如下表所示 : 萬用字元說明 % 代表 0 或更多字元任易長度的任何字串 _ 代表一個字元長度的任何字元 [ ] 符合括號內字元清單的任何一個字元, 例如 :[EO] [ - ] 符合括號內 - 字元範圍的任何一個字元, 例如 :[A-J] [ ^ ] 符合不在括號內字元清單的字元, 例如 :[^K-Y] 21
BETWEEN/AND 範圍運算子 BETWEEN/AND 運算子可以定義欄位值需要符合的範圍, 其範圍值可以是文字 數值或和日期 / 時間資料 例如 : 因為選課少了 2~3 個學分, 我們可以查詢 Courses 資料表看看有哪些課程可以選, 如下所示 : SELECT * FROM Courses WHERE credits BETWEEN 2 AND 3 22
IN 運算子 IN 運算子只需是清單其中之一即可, 我們需要列出一串文字或數值清單作為條件, 欄位值只需是其中之一, 就符合條件 例如 : 查詢 Students 資料表學號 S003 S005 和 S001 三位學生的詳細資料, 如下所示 : SELECT * FROM Students WHERE sid IN ('S003', 'S005', 'S001') 23
NOT 運算子 NOT 運算子可以搭配邏輯運算子, 取得與條件相反的查詢結果, 如下表所示 : 運算子 NOT LIKE NOT BETWEEN NOT IN 說明否定 LIKE 運算式否定 BETWEEN/AND 運算式否定 IN 運算式 24
AND 運算子 AND 運算子連接的前後運算式都必須同時為真, 整個 WHERE 子句的條件才為真 例如 : 查詢 Courses 資料表的 c_no 欄位包含 '1' 子字串, 而且 title 有 ' 程式 ' 子字串, 如下所示 : SELECT * FROM Courses WHERE c_no LIKE '%1%' AND title LIKE '% 程式 %' 25
OR 運算子 OR 運算子連接的前後條件, 只需任何一個條件為真, 即為真 例如 : 查詢 Courses 資料表的 c_no 欄位包含 '1' 子字串, 或 title 有 ' 程式 ' 子字串, 如下所示 : SELECT * FROM Courses WHERE c_no LIKE '%1%' OR title LIKE '% 程式 %' 26
連接多個條件與括號 在 WHERE 子句的條件可以使用 AND 和 OR 來連接多個不同條件 其優先順序是位在括號中的運算式優先 27
WHERE 子句的算術運算子 在 WHERE 子句的運算式條件也支援算術運算子 (Arithmetic Operators) 的加 減 乘 除和餘數, 我們可以在 WHERE 子句的條件加上算術運算子 例如 : 查詢 Classes 資料表的 grade 學生成績加分 10% 後, 成績大於 80 分的記錄資料, 如下所示 : SELECT * FROM Classes WHERE (grade * 1.1) > 80 28
聚合函數的摘要查詢 聚合函數 (Aggregate Functions) 也稱為 欄位函數 (Column Functions), 可以進行選取記錄欄位值的筆數 平均 範圍和統計函數, 以便提供進一步欄位資料的分析結果 一般來說, 如果 SELECT 指令敘述擁有聚合函數, 稱為 摘要查詢 (Summary Query) 29
聚合函數的摘要查詢種類 常用的聚合函數說明, 如下表所示 : 函數 COUNT( 運算式 ) AVG( 運算式 ) MAX( 運算式 ) MIN( 運算式 ) SUM( 運算式 ) 說明計算記錄筆數計算欄位平均值取得記錄欄位的最大值取得記錄欄位的最小值取得記錄欄位的總計 30
COUNT() 函數 SQL 指令可以配合 COUNT() 函數計算查詢的記錄數, * 參數可以統計資料表的所有記錄數, 或指定欄位來計算欄位不是 Null 空值的記錄數 例如 : 查詢 Courses 資料表的 credits 高過 3 的課程種類, 如下所示 : SELECT COUNT(*) AS 課程數 FROM Courses WHERE credits > 3 31
AVG() 函數 SQL 指令只需配合 AVG() 函數, 就可以計算指定欄位的平均值 例如 : 在 Courses 資料表查詢 c_no 包含 '2' 子字串的課程總數, 和學分的平均值, 如下所示 : SELECT COUNT(*) AS 課程總數, AVG(credits) AS 學分平均值 FROM Courses WHERE c_no LIKE '%2%' 32
MAX() 函數 SQL 指令只需配合 MAX() 函數, 就可以計算符合條件記錄的欄位最大值 例如 : 在 Courses 資料表查詢 c_no 包含 '2' 子字串的最高學分數, 如下所示 : SELECT MAX(credits) AS 最高學分 FROM Courses WHERE c_no LIKE '%2%' 33
MIN() 函數 SQL 指令如果配合 MIN() 函數, 就可以計算出符合條件記錄的欄位最小值 例如 : 在 Courses 資料表查詢 c_no 包含 '2' 子字串的最低學分數, 如下所示 : SELECT MIN(credits) AS 最低學分 FROM Courses WHERE c_no LIKE '%2%' 34
SUM() 函數 SQL 指令配合 SUM() 函數, 可以計算出符合條件記錄的欄位總和 例如 : 在 Courses 資料表計算學分的總和和平均, 如下所示 : SELECT SUM(credits) AS 學分總和, SUM(credits)/COUNT(*) AS 學分平均 FROM Courses 35
排序 ORDER BY 子句 SELECT 指令可以使用 ORDER BY 子句依照欄位由小到大或由大到小進行排序, 其語法如下所示 : ORDER BY 運算式 [ASC DESC] [, 運算式 [ASC DESC] 上述語法的排序方式預設是由小到大排序的 ASC, 如果希望由大至小, 請使用 DESC 關鍵字 36
建立參數的 SQL 查詢 從 TextBox 控制項取得參數值 從 DropDownList 控制項取得參數值 從 URL 參數取得 SQL 的參數值 改為使用 Session 變數取得 SQL 參數值 37
建立參數的 SQL 查詢 在 SqlDataSource 控制項使用設定資料來源精靈建立的 SQL 指令敘述, 就是指定 SelectCommand 屬性值, 讀者可以切換原始檔檢視來檢視 SelectCommand 屬性值的 SQL 指令敘述 WHERE 子句的條件不只可以是常數, 還可以使用其他控制項 Session 變數或 QueryString 的 URL 參數值來建立過濾條件, 即建立參數的 SQL 查詢, 此時過濾條件的參數值不是常數值, 而是從其他來源取得的參數值 38
從 TextBox 控制項取得參數值 參數的 SQL 查詢可以從 TextBox 控制項取得參數值, 例如 : 在 TextBox 控制項輸入課程名稱的部分子字串, 就可以在 GridView 控制項顯示符合條件的記錄資料 39
從 TextBox 控制項取得參數值設定 在 SQL 陳述式的步驟, 按 WHERE 鈕新增 WHERE 子句, 可以看到 加入 WHERE 子句 對話方塊 在 資料行資料行 欄選欄位 tite, 運算子 欄位選 LIKE, 來源選 Control 後, 在右上方 控制項 ID 欄位選 txttitle, 按 加入加入 鈕建立 WHERE 子句 40
從 DropDownList 控制項取得參數值 參數的 SQL 查詢除了可以從 TextBox 控制項取得參數值外, 也可以從 DropDownList 控制項取得參數值 因為 ASP.NET 網頁的 DropDownList 控制項也是從 SqlDataSource 控制項取得資料, 換句話說, 在 ASP.NET 網頁共有 2 個 SqlDataSource 控制項, 分別對應 DropDownList 和 GridView 控制項 41
從 DropDownList 控制項取得參數值 在 SQL 陳述式的步驟, 按 WHERE 鈕新增 WHERE 子句, 可以看到 加入 WHERE 子句 對話方塊 在 資料行資料行 欄選欄位 major, 運算子 欄位選欄位選 =, 來源選 Control 後, 在右上方 控制項 ID 欄位選 ddlmajors, 按 加入加入 鈕建立 WHERE 子句 42
從 URL 參數取得 SQL 的參數值 對於不同 ASP.NET 網頁來說, 我們可以使用 Session 變數 URL 參數或 Cookie 來取得其他 ASP.NET 網頁傳入的參數值 43
從 URL 參數取得 SQL 的參數值 在 SQL 陳述式的步驟, 按 WHERE 鈕新增 WHERE 子句, 可以看到 加入 WHERE 子句 對話方塊 在 資料行資料行 欄選欄位 title, 運算子 欄位選 LIKE, 來源選 QueryString 後, 在右上方 QueryString 欄位 輸入 URL 參數 Title, 按 加入 鈕建立 WHERE 子句 44
改為使用 Session 變數取得 SQL 參數值 在 參數參數 框選框選 title 後, 在右上方將 參數來源參數來源 欄改選 Session, 然後在 SessionField 欄輸入 Session 變數名稱 Title 45
新增 更新與刪除記錄 SQL 語言的資料操作指令 ADO.NET 的資料操作 SqlDataSource 控制項的資料操作 46
INSERT 指令 SQL 插入記錄 INSERT 指令可以新增一筆記錄到資料表 其基本語法如下所示 : INSERT INTO table (column1,column2,..) VALUES ('value1', 'value2 ', ) 上述 SQL 指令的 table 是準備插入記錄的資料表名稱,column1~n 為資料表內的欄位名稱 ( 不需全部欄位 ),value1~n 是對應的欄位值 47
INSERT 指令注意事項 不論是欄位或值清單, 都需要使用逗號分隔 INSERT 指令 VALUES 的值, 數值不用引號包圍, 字元與日期 / 時間需要使用引號包圍 INSERT 指令的欄位清單不需和資料表定義的欄位數目或順序相同, 只需選擇需要新增的欄位 ( 非 NULL 欄位 ) 即可, 但是括號內的欄位名稱順序需要和 VALUES 值的順序相同 48
UPDATE 指令 SQL 更新記錄 UPDATE 指令可以將資料表內符合條件的記錄, 更新欄位內容 基本語法如下所示 : UPDATE table SET column1 = 'value1' WHERE conditions 上述指令的 table 是資料表,SET 子句 column1 是資料表的欄位名稱, 不用全部只需指定要更新的欄位, value1 是更新欄位值, 如果更新欄位不只一個, 請使用逗號分隔, 如下所示 : UPDATE table SET column1 = 'value1', column2 = 'value2' WHERE conditions 49
UPDATE 指令注意事項 WHERE 條件子句是必要元素, 如果沒有此條件, 資料表內所有記錄欄位都會被更新 更新欄位值如為數值不用引號包圍, 字元與日期 / 時間需要使用引號包圍 50
DELETE 指令 SQL 刪除記錄 DELETE 指令是將資料表內符合條件的記錄都刪除掉 其基本語法如下所示 : DELETE FROM table WHERE conditions 上述指令的 table 是資料表,WHERE 子句 conditions 為刪除記錄條件, 口語來說就是 將符合 conditions 條件的記錄刪除掉 51
DELETE 指令注意事項 WHERE 條件子句是 DELETE 指令的必要元素, 如果沒有此條件, 資料表內的所有記錄都會被刪除掉 WHERE 條件能夠使用 = <> > <= 和 >= 運算子 WHERE 條件可以不只一個, 如果擁有多個條件, 請使用邏輯運算子 AND 或 OR 運算子連接 52
ADO.NET 的資料操作 在 ASP.NET 網頁執行資料表插入 刪除和更新記錄操作可以使用 Command 物件來執行 SQL 指令, 使用的是 ExecuteNonQuery() 方法, 如下所示 : count = objcmd.executenonquery() 上述程式碼左邊的變數 count 可以取得影響的記錄數, 此方法可以執行 SQL 指令, 但不會傳回任何記錄資料, 傳回的是影響的記錄數 53
SqlDataSource 控制項的資料操作 在建立 SqlDataSource 控制項連接 School.mdf 資料庫的 Students 資料表, 並且讓控制項自動產生 INSERT UPDATE 和 DELETE 指令後, 以 DELETE 指令為例, 其自動產生的 SQL 指令, 即控制項的 DeleteCommand 屬性值, 如下所示 : DELETE FROM [Students] WHERE [sid] = @sid 上述 DELETE 指令的 @sid 是 WHERE 刪除條件的參數 ( 參數名稱預設是欄位名稱 ), 即學號 54
指定參數值 我們可以使用程式碼來指定此參數的值, 如下所示 : SqlDataSource2.DeleteParameters("sid"). DefaultValue = txtid.text SqlDataSource2.Delete() 上述 SqlDataSource 控制項使用 DeleteParameters 集合物件來指定參數 sid 的值, 即 DefaultValue 屬性值, 以此例是指定成 TextBox 控制項輸入的學號, 然後執行 Delete() 方法來刪除記錄 55
進階 SQL 選項 在 SQL 陳述式步驟, 按右下方的 進階 鈕, 可以看到 進階 SQL 產生選項 對話方塊 勾選 產生 INSERT UPDATE 和 DELETE 陳述式, 按 確定確定 鈕即可自動產生新增 更新和刪除的 SQL 指令 56