Microsoft PowerPoint - sql2005_ch08.ppt

Size: px

Start display at page:

Download "Microsoft PowerPoint - sql2005_ch08.ppt"

住缪
7 years ago
Views:

1 第八章檢視表及 SQL 語法 8-1 何謂檢視建立檢視表設定準則 8-2 SQL 語法 Select 基本架構排序 Where 條件多資料表查詢指令群組依據及計算更改記錄的指令 1

2 8-1 何謂檢視檢視表的原名為 View, 有視界或視野之意, 也就是可以由各種角度, 取出儲存於資料表中的記錄, 故名為檢視表檢視的功能一般是用來對焦 (Focus) 簡化和自訂每位使用者查看資料庫的角度檢視也可以做為安全機制, 讓使用者只可透過檢視來存取資料, 而無法直接存取檢視的基底資料表檢視可以用來提供回溯相容介面, 以模擬已存在但其結構描述已變更的資料表當您將資料複製到 Microsoft SQL Server 2005 及從中複製資料, 也可以使用檢視, 來提高效能及分割資料也就是查詢 2

3 檢視表設計視窗 1. 檢視表的來源 : 可以是一或多個資料表, 若有多個資料表, 則資料表之間必須有關聯, 不可有任一資料表是沒有關聯的孤兒 2. 使用的欄位 : 此處必須是來源資料表的欄位, 只有輸入已打勾者, 才會顯示在結果中, 此處也是檢視表設計的主要操作位置 3. 自動產生的 SQL 語法 : 此處是由系統自動產生的 SQL 語法 4. 執行結果 : 按下執行 SQL 按鈕後獲得的執行結果 3

4 修改 : 編輯 View 開啟檢視 : 顯示結果執行加入群組依據驗證 SQL 語法加入新的衍生資料表加入資料表 4

5 8-1-1 建立檢視表步驟 1 選取資料庫 >> 檢視 >> 右鍵 >> 新增檢視步驟 2 指定資料表步驟 3 指定欄位, 指定準則或排序步驟 4 儲存檢視步驟 5 按執行 SQL 顯示結果 5

6 指定排序以一或多個欄位資料為準, 進行遞增或遞減排序 6

7 群組及計算會有一或多個做為 GROUP 的欄位, 這些欄位的值, 在執行結果中必定不會重覆查詢設計工具 >> 加入群組依據 7

8 8

9 9

10 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 北風資料庫 : 補充篇 : 群組及計算範例 11

12 檢視設計視窗 ORDER BY 須配合 TOP ( ) 12

13 查詢視窗可以不用加上 TOP ( ) 13

14 補充篇 : ORDER BY 錯誤訊息 14

15 ORDER BY 指定 SELECT 陳述式傳回的資料行所用的排序順序除非在檢視內嵌函數衍生資料表和子查詢中, 也指定了 TOP, 否則 ORDER BY 子句無效新增查詢 : T-SQL 執行新增檢視 15

16 文字型態設定準則可使用 LIKE NOT LIKE 等運算子 16

17 日期及數字設定準則可使用 > < = >= <= BETWEEN AND 17

18 18

19 查詢視窗亦可執行 19

20 使用函數設定準則在準則內使用函數的目的是以函數產生的可變動資料, 獲得不同結果 > GETDATE( )

21 8-2 SQL 語法 SQL 語法的由來 SELECT 基本架構取出資料表及部份欄位取出資料表及全部欄位使用別名唯一值排序加入排序 TOP WHERE 條件及排序字串的完全比對字串的部份比對及萬元字元空白 (NULL) 及非空白多重準則多資料表查詢指令 INNER JOIN LEFT JOIN 及 RIGHT JOIN 群組依據及計算使用彙總函數群組依據子查詢使用 EXISTS 使用 IN 更改記錄的指令產生資料表 SELECT 新增記錄 INSERT INTO 刪除記錄 DELETE 更新記錄 UPDATE 21

22 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

23 8-2-2 SELECT 基本架構取出資料表及部份欄位 :, SELECT PID, JoinDate, FROM Members 或 SELECT Members.PID, Members.JoinDate, Members. FROM Members SELECT, FROM, WHERE, GROUP BY, ORDER BY 盡量以大寫英文為主. 23

24 補充篇 : SQL Server 保留關鍵字約 179 個 24

25 取出資料表及全部欄位以 * 表示所有欄位 SELECT * FROM Members 或 SELECT 基本架構 SELECT Members.* FROM Members 25

26 使用別名 : SELECT 基本架構 SELECT PID AS 身份證號, AS 電子郵件 FROM Members M 唯一值 : 重覆記錄在結果只顯示一筆 SELECT DISTINCT Orders.MemberID FROM Orders 26

27 加入排序 : 排序 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

28 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

29 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 >

30 T-SQL: LIKE 應用思考 : 檢視 LIKE 應用 30

31 檢視 : AND 與 OR 應用 OR AND AND 同一行表示 AND, 不同行表示 OR. 31

32 T-SQL: AND 與 OR 應用 32

33 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

34 內部聯結 - INNER JOIN 34

35 補充篇 : 內部聯結 INNER JOIN 使用不相等運算子的聯結 (1/2) USE AdventureWorks; GO SELECT * FROM Production.Product -- 產品資料表 504 筆 GO SELECT * FROM Sales.SalesOrderDetail -- 銷售明細資料表筆 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

36 補充篇 : 內部聯結 INNER JOIN 使用不相等運算子的聯結 (2/2) 36

37 外部聯結 - 3 種類別 Microsoft SQL Server 2005 針對 FROM 子句中指定的外部聯結, 可使用這些 SQL-92 關鍵字 : LEFT OUTER JOIN 或 LEFT JOIN RIGHT OUTER JOIN 或 RIGHT JOIN FULL OUTER JOIN 或 FULL JOIN 37

38 LEFT 及 RIGHT JOIN- 以 Members 為準, 顯示所有資料 SELECT dbo.orders.orderid, dbo.orders.orderdate, dbo.orders.totals, dbo.members.memberid, dbo.members.sex, dbo.members. FROM dbo.members LEFT OUTER JOIN dbo.orders ON dbo.members.memberid = dbo.orders.memberid 38

39 檢視 : 外部連結 39

40 檢視 : T-SQL 40

41 使用彙總函數群組依據及計算 SELECT MAX(Orders.OrderDate) AS 最後交易日 FROM Orders 41

42 群組依據及計算群組依據 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 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.

44 子查詢 -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

45 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

46 子查詢範例 -- [ 方法 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

47 使用 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

48 檢視 : 使用 EXISTS EXISTS: 因不存在二個欄位皆相同 ( 重複 ), 故結果為 NULL 如果改為 NOT EXISTS, 結果為 2350 筆 48

49 使用 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

50 檢視 : 使用 IN 50

51 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

52 查詢 : 產生資料表 INTO 52

53 更改記錄的指令新增記錄 - 是新增記錄至現有資料表, 除須指定資料表外, 尚須為各欄位指定對應之欄位 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

54 查詢 : 新增記錄 INSERT INTO 54

55 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

56 查詢 : 刪除記錄 DELETE 56

57 查詢 : 更新記錄 UPDATE SET 290 * 0.9 =

目錄 C ontents Chapter MTA Chapter Chapter

目錄 C ontents Chapter MTA Chapter Chapter 目錄 C ontents Chapter 01 1-1 MTA...1-2 1-2...1-3 1-3...1-5 1-4...1-10 Chapter 02 2-1...2-2 2-2...2-3 2-3...2-7 2-4...2-11...2-16 Chapter 03 3-1...3-2 3-2...3-8 3-3 views...3-16 3-4...3-24...3-33 Chapter