第八章檢視表及 SQL 語法 8-1 何謂檢視 8-1-1 建立檢視表 8-1-2 設定準則 8-2 SQL 語法 8-2-1 Select 基本架構 8-2-2 排序 8-2-3 Where 條件 8-2-4 多資料表查詢指令 8-2-5 群組依據及計算 8-2-6 更改記錄的指令 1
8-1 何謂檢視 檢視表的原名為 View, 有視界或視野之意, 也就是可以由各種角度, 取出儲存於資料表中的記錄, 故名為檢視表 檢視的功能一般是用來對焦 (Focus) 簡化和自訂每位使用者查看資料庫的角度 檢視也可以做為安全機制, 讓使用者只可透過檢視來存取資料, 而無法直接存取檢視的基底資料表 檢視可以用來提供回溯相容介面, 以模擬已存在但其結構描述已變更的資料表 當您將資料複製到 Microsoft SQL Server 2005 及從中複製資料, 也可以使用檢視, 來提高效能及分割資料 也就是查詢 2
檢視表設計視窗 1. 檢視表的來源 : 可以是一或多個資料表, 若有多個資料表, 則資料表之間必須有關聯, 不可有任一資料表是沒有關聯的 孤兒 2. 使用的欄位 : 此處必須是來源資料表的欄位, 只有輸入已打勾者, 才會顯示在結果中, 此處也是檢視表設計的主要操作位置 3. 自動產生的 SQL 語法 : 此處是由系統自動產生的 SQL 語法 4. 執行結果 : 按下執行 SQL 按鈕後獲得的執行結果 3
修改 : 編輯 View 開啟檢視 : 顯示結果 1 2 3 4 執行 加入群組依據驗證 SQL 語法 加入新的衍生資料表 加入資料表 4
8-1-1 建立檢視表 步驟 1 選取資料庫 >> 檢視 >> 右鍵 >> 新增檢視 步驟 2 指定資料表 步驟 3 指定欄位, 指定準則或排序 步驟 4 儲存檢視 步驟 5 按執行 SQL 顯示結果 5
指定排序 以一或多個欄位資料為準, 進行遞增或遞減排序 6
群組及計算 會有一或多個做為 GROUP 的欄位, 這些欄位的值, 在執行結果中必定不會重覆 查詢設計工具 >> 加入群組依據 7
8
9
Sum: 計算總計 Avg: 計算平均值 Min: 搜尋該欄位的最小數值 Max: 搜尋該欄位的最大數值 Count: 計算記錄筆數, 傳回值型態為 int CountBig: 與上項功能相同, 唯一差異是此項傳回值型態為 Bigint, 允許範圍為 -2^63 (- 9,223,372,036,854,775,808) 至 2^63-1 (9,223,372,036,854,775,807), 故若計算結果數值較大, 請使用 CountBig Checksum_Agg: 傳回加總檢查碼, 型態為 int, 加總檢查碼可用來檢查資料是否已變更, 一筆記錄在變更前後必有不同的加總檢查碼 Expression: 也就是運算式, 若使用此項, 必須在 資料行 輸入可產生資料的運算式 Where: 也就是準則, 使用準則的欄位將無法顯示在執行結果 Sum Distinct: 執行總計, 但只傳回唯一值的總和 Avg Distinct: 計算平均值, 但只傳回唯一值的平均 Min Distinct:DISTINCT 對 MIN 計算是沒有意義的, 只是用於 SQL-92 相容性 Max Distinct: 同上 Count Distinct: 計算筆數, 但會傳回唯一的項目個數 Count_Big Distinct: 同上, 但傳回值型態為 Bigint Checksum_Agg Distinct: 傳回唯一值的加總檢查碼 StDev: 計算數值欄位的標準差, 標準差是各數值與平均值差異平方和的平均數, 再開平方而得 標準差愈小, 表示各數值與整體平均值的差異愈小 StDevP: 傳回給定運算式中所有數值擴展的統計標準差, 此項是計算母體, 上項是計算樣本 Var: 計算變異數值, 其意與標準差類似, 變異數開平方後即為標準差 請記下 6 個常用彙總函數 VarP: 傳回在給定之運算式中所有值的群數統計變異數, 此項是計算母體, 上項是計算樣本 10
北風資料庫 : 補充篇 : 群組及計算範例 11
檢視設計視窗 ORDER BY 須配合 TOP ( ) 12
查詢視窗可以不用加上 TOP ( ) 13
補充篇 : ORDER BY 錯誤訊息 14
ORDER BY 指定 SELECT 陳述式傳回的資料行所用的排序順序 除非在檢視 內嵌函數 衍生資料表和子查詢中, 也指定了 TOP, 否則 ORDER BY 子句無效 新增查詢 : T-SQL 執行新增檢視 15
文字型態 8-1-2 設定準則 可使用 LIKE NOT LIKE 等運算子 16
日期及數字 設定準則 可使用 > < = >= <= BETWEEN AND 17
18
查詢視窗亦可執行 19
使用函數 設定準則 在準則內使用函數的目的是以函數產生的可變動資料, 獲得不同結果 > GETDATE( ) -30 20
8-2 SQL 語法 8-2-1 SQL 語法的由來 8-2-22 SELECT 基本架構 取出資料表及部份欄位 取出資料表及全部欄位 使用別名 唯一值 8-2-3 排序 加入排序 TOP 8-2-44 WHERE 條件及排序 字串的完全比對 字串的部份比對及萬元字元 空白 (NULL) 及非空白 多重準則 8-2-5 多資料表查詢指令 INNER JOIN LEFT JOIN 及 RIGHT JOIN 8-2-6 群組依據及計算 使用彙總函數 群組依據 子查詢 使用 EXISTS 使用 IN 8-2-7 更改記錄的指令 產生資料表 SELECT 新增記錄 INSERT INTO 刪除記錄 DELETE 更新記錄 UPDATE 21
8-2-1 SQL 語法的由來 SQL 的全名是 Standard Query Language 語言種類 說明 資料定義語言 (DDL) 管理資料庫物件 ( 資料表及欄位 ) 的語法 資料操作語言 (DML) 針對記錄的選取 新增新增 刪除刪除 更新等語法 DDL (Data Definition Language) 資料定義語言資料庫 / 資料表設計 DM (Data Manipulation Language) 資料操作語言開啟資料庫 / 資料表後的操作 DCL (Data Control Language) 資料控制語言如 : 權限控制 GRANT, DENY, REVOKE ( 課本第 15 章, p.15-43) 22
8-2-2 SELECT 基本架構 取出資料表及部份欄位 :, SELECT PID, JoinDate, EMail FROM Members 或 SELECT Members.PID, Members.JoinDate, Members.EMail FROM Members SELECT, FROM, WHERE, GROUP BY, ORDER BY 盡量以大寫英文為主. 23
補充篇 : SQL Server 保留關鍵字 約 179 個 24
取出資料表及全部欄位 以 * 表示所有欄位 SELECT * FROM Members 或 SELECT 基本架構 SELECT Members.* FROM Members 25
使用別名 : SELECT 基本架構 SELECT PID AS 身份證號, Email AS 電子郵件 FROM Members M 唯一值 : 重覆記錄在結果只顯示一筆 SELECT DISTINCT Orders.MemberID FROM Orders 26
加入排序 : 8-2-3 排序 SELECT Orders.MemberID, Orders. OrderDate, Orders.Totals FROM Orders ORDER BY Orders.OrderDate DESC, Orders.Totals ASC TOP: SELECT TOP 10 Orders.Totals FROM Orders ORDER BY Orders.Totals DESC DESC 遞減 descend ASC 遞增 ascend SELECT TOP 10 PERCEENT 27
8-2-4 WHERE 條件及排序 字串的完全 ( 部份 ) 比對, 萬元字元 SELECT Staff.StaffNameC, Staff.PID FROM Staff WHERE Staff.StaffNameC= 桂思強 萬用字元 % _ ( 底線 ) [ ] [^] 說明 任何含有零個或更多字元的字串 任何單一字元 在指定範圍 ([a-f]) 或集合 ([abcdef]) 中的任何單一字元 不在指定範圍 ([^a-f]) 或集合 ([^abcdef]) 中的任何單一字元 範例 WHERE title LIKE '% 景德鎮 %' 可找出 title 中含有 景德鎮 三個字的所有記錄 WHERE title LIKE ' 台北 _' 可找出所有以 台北 為首, 且總字數等於三個字的資料, 如台北市及台北縣 WHERE PID LIKE '[A-C]%', 可找出 PID 欄位以 A-C 為首的資料, 括號內的範圍只可以是數字或英文字母 WHERE PID LIKE 'A[^l]%' 可找出 PID 欄位以 A 為首, 並排除第二位是 1 的資料 LIKE %30\%% ESCAPE \ 28
WHERE 條件及排序 空白 (NULL) 及非空白 - IS NULL 或 IS NOT NULL SELECT * FROM Staff WHERE StaffNameE IS NULL 多重準則 - 關係可分為 AND 及 OR SELECT ProductName FROM Products WHERE Description Like '% 小說 %' AND MemberPrice > 100 29
T-SQL: LIKE 應用 思考 : 檢視 LIKE 應用 30
檢視 : AND 與 OR 應用 OR AND AND 同一行表示 AND, 不同行表示 OR. 31
T-SQL: AND 與 OR 應用 32
8-2-5 多資料表查詢指令 ( 內部聯結 ) INNER JOIN- 同一資料欄位必須同時存在於兩端之連結欄位 SELECT dbo.products.productid, dbo.products.productname, dbo.products.supplierid, dbo.products.memberprice, dbo.products.normalprice, dbo.suppliers.suppliername FROM dbo.products 資料表 1 INNER JOIN dbo.suppliers 資料表 2 ON dbo.products.supplierid = dbo.suppliers.id 資料表 1. 欄位名稱 一般使用等號 資料表 2. 欄位名稱 33
內部聯結 - INNER JOIN 34
補充篇 : 內部聯結 INNER JOIN 使用不相等運算子的聯結 (1/2) USE AdventureWorks; GO SELECT * FROM Production.Product -- 產品資料表 504 筆 GO SELECT * FROM Sales.SalesOrderDetail -- 銷售明細資料表 12317 筆 GO -- 全部 757 筆 ( 不加 AND) -- 等於 130 筆 ( 加 AND =) -- 3 筆 ( 限定不等於中找出產品編號為 718) SELECT DISTINCT p.productid, p.name, p.listprice, sd.unitprice AS 'Selling Price' FROM Sales.SalesOrderDetail AS sd JOIN Production.Product AS p ON sd.productid = p.productid AND sd.unitprice <> p.listprice WHERE p.productid = 718 GO 35
補充篇 : 內部聯結 INNER JOIN 使用不相等運算子的聯結 (2/2) 36
外部聯結 - 3 種類別 Microsoft SQL Server 2005 針對 FROM 子句中指定的外部聯結, 可使用這些 SQL-92 關鍵字 : LEFT OUTER JOIN 或 LEFT JOIN RIGHT OUTER JOIN 或 RIGHT JOIN FULL OUTER JOIN 或 FULL JOIN 37
LEFT 及 RIGHT JOIN- 以 Members 為準, 顯示所有資料 SELECT dbo.orders.orderid, dbo.orders.orderdate, dbo.orders.totals, dbo.members.memberid, dbo.members.sex, dbo.members.email FROM dbo.members LEFT OUTER JOIN dbo.orders ON dbo.members.memberid = dbo.orders.memberid 38
檢視 : 外部連結 39
檢視 : T-SQL 40
使用彙總函數 8-2-6 群組依據及計算 SELECT MAX(Orders.OrderDate) AS 最後交易日 FROM Orders 41
群組依據及計算 群組依據 SELECT dbo.members.memberid, COUNT(dbo.Orders.OrderID) AS 交易次數, SUM(dbo.Orders.Totals) AS 交易總額 FROM dbo.orders INNER JOIN dbo.members ON dbo.orders.memberid = dbo.members.memberid WHERE (dbo.orders.canceldate IS NULL) GROUP BY dbo.members.memberid 42
43
子查詢 -SELECT 中的 SELECT SELECT dbo.products.productname, dbo.orders.orderdate, SUM(dbo.OrderDetail.Quantity) AS Qty FROM dbo.products INNER JOIN dbo.orderdetail ON dbo.products. ProductID = dbo.orderdetail.productid INNER JOIN dbo.orders ON dbo.orderdetail.orderid = dbo.orders.orderid WHERE dbo.products.supplierid = 課本印刷有誤, 更正為投影片 45 或 46. (SELECT id FROM dbo.suppliers WHERE (SupplierName = ' 麥田 ')) 44
USE ebook GO WHERE 子查詢範例 -- 不含 WHERE 2915 筆 SELECT dbo.products.productname, dbo.orders.orderdate, dbo.orderdetail.quantity AS Qty FROM dbo.products INNER JOIN dbo.orderdetail ON dbo.products.productid = dbo.orderdetail.productid INNER JOIN dbo.orders ON dbo.orderdetail.orderid = dbo.orders.orderid GO -- [ 方法 1]: 加上 WHERE 找出供應商編號是 54 - 麥田 ' 查詢資料有 132 筆 SELECT dbo.products.productname, dbo.orders.orderdate, SUM(dbo.OrderDetail.Quantity) AS Qty FROM dbo.products INNER JOIN dbo.orderdetail ON dbo.products. ProductID = dbo.orderdetail.productid INNER JOIN dbo.orders ON dbo.orderdetail.orderid = dbo.orders.orderid WHERE ( dbo.products.supplierid = ( SELECT id FROM dbo.suppliers WHERE (SupplierName = ' 麥田 ') ) ) GROUP BY dbo.products.productname, dbo.orders.orderdate GO 45
子查詢範例 -- [ 方法 2]: 採用 HAVING 條件 ( 配合 GROUP BY), 結果亦是 132 筆 SELECT dbo.products.productname, dbo.orders.orderdate, SUM(dbo.OrderDetail.Quantity) AS Qty FROM dbo.products INNER JOIN dbo.orderdetail ON dbo.products.productid = dbo.orderdetail.productid INNER JOIN dbo.orders ON dbo.orderdetail.orderid = dbo.orders.orderid GROUP BY dbo.products.productname, dbo.orders.orderdate, dbo.products.supplierid HAVING ( dbo.products.supplierid = ( SELECT id FROM dbo.suppliers WHERE (SupplierName = ' 麥田 ') ) ) 子查詢傳回一個欄位 id 並配合 HAVING 使用 46
使用 EXISTS 使用 EXISTS 之後是子查詢, 查看主 / 子查詢的交集 SELECT ProductName FROM dbo.products WHERE EXISTS (SELECT ProductID, Writer, Translator, PublishDate, Period, ISBN, Pages, LangCategoryID FROM dbo.productbooks WHERE (dbo.products.productname = Writer)) 47
檢視 : 使用 EXISTS EXISTS: 因不存在二個欄位皆相同 ( 重複 ), 故結果為 NULL 如果改為 NOT EXISTS, 結果為 2350 筆 48
使用 IN 使用 IN- 其後可以是內含固定資料的小括號或子查詢 SELECT dbo.products.productname, dbo.suppliers.suppliername FROM dbo.products INNER JOIN dbo.suppliers ON dbo.products.supplierid = dbo.suppliers.id WHERE (dbo.suppliers.city IN (' 台北 ', ' 上海 ', ' 北京 ')) 49
檢視 : 使用 IN 50
8-2-7 更改記錄的指令 產生資料表 - 由 Members 及 Orders 等兩個資料表取出三個欄位, 再新增至 MemberOrders 資料表 : SELECT dbo.members.memberid, dbo.orders.orderid, dbo.orders.totals INTO MemberOrders FROM dbo.members INNER JOIN dbo.orders ON dbo.members.memberid = dbo.orders.memberid 51
查詢 : 產生資料表 INTO 52
更改記錄的指令 新增記錄 - 是新增記錄至現有資料表, 除須指定資料表外, 尚須為各欄位指定對應之欄位 INSERT INTO MemberOrders (MemberID,OrderID,Totals) SELECT dbo.members.memberid, dbo.orders.orderid, dbo.orders.totals FROM dbo.members INNER JOIN dbo.orders ON dbo.members.memberid = dbo.orders.memberid 只新增一 1 筆 : INSERT INTO 資料表 ( 欄位 1, 欄位 2, ) VALUES ( 欄位 1 資料, 欄位 2 資料, ) 53
查詢 : 新增記錄 INSERT INTO 54
8-2 SQL 語法 更改記錄的指令 刪除記錄 - 在單一資料表, 刪除符合準則的記錄 DELETE FROM Orders WHERE Orders.OrderDate <= 1/1/2005 更新記錄 - 更改符合準則的欄位內資料 UPDATE Products SET MemberPrice=NormalPrice*0.9 WHERE Closed is null 記得! 要加上 WHERE 條件 55
查詢 : 刪除記錄 DELETE 56
查詢 : 更新記錄 UPDATE SET 290 * 0.9 = 261 57