資料庫系統概論 CH08 建立檢視表及 SQL 語法

1. 資料庫系統概論CH08 建立檢視表及SQL語法 鄧姚文

2. 大綱 檢視表的設計 SQL語法

3. 8-1檢視表的設計檢視表 • 檢視表的原名為View • 有視界或視野之意 • 可以由各種角度，取出儲存於資料表中的記錄 • 另一說法是查詢

4. 8-1檢視表的設計建立檢視表 • 指定來源(資料表) • 指定欄位 • 儲存及執行

6. 範例：查看客戶訂單 • 使用DaShang資料庫 • 使用 Orders 和 CustomerSupplier資料表 • 列出欄位： • OrderCode訂單編號 • OrderDate訂單日期 • WithTaxPrice含稅金額 • CustCnName客戶中文姓名 • 依訂單含稅金額從大到小排序 • 儲存成 [view客戶訂單]

8. 8-1檢視表的設計檢視表設計-排序 • 可以一或多個欄位資料為準，進行遞增或遞減排序 • 對應到 ORDER BY • 我們真的需要 TOP 100 PERCENT 嗎？ • 遞增 ASC (Ascending) • 遞減 DESC (Descending)

10. 8-1檢視表的設計檢視表設計-群組及計算 • 以一或多個做為Group的欄位，執行計算 • 對應到 GROUP BY

11. 範例：客戶訂單匯總 • 使用DaShang資料庫 • 使用 Orders 和 CustomerSupplier資料表 • 計算每一位客戶的訂單總金額 • 列出欄位 • SUM(WithTaxPrice) 訂單總金額 • CustCnName客戶中文姓名 • 依訂單總金額從大到小排序 • 儲存成 [view客戶銷售排名]

14. 8-1檢視表的設計設定準則 • 對應到 WHERE 或 HAVING • WHERE 作用在一開始（FROM 資料表） • HAVING 作用在分組（GROUP BY）之後 • 日期及數字 • 可使用>、<、=、>=、<=、Between…and…等 • 文字型態 • 預設不區分英文字母大小寫 • 可使用Like、Not Like等運算子 • 萬用字元 • % 代表零個或多個任意字 • _ 代表一個任意字 • 例如 Like N'%VIP%'

15. 範例：列出特定產品1 • 使用DaShang資料庫 • 使用 Product 資料表 • 列出欄位 • ProductCode產品代碼 • ProductChnName產品中文名稱 • ProductEngName產品英文名稱 • Spec 產品規格 • 列出英文名稱中有 conn 者（LIKE %conn%） • 儲存成 [view產品conn]

16. 範例：列出特定產品2 • 使用DaShang資料庫 • 使用 Product 資料表 • 列出欄位 • ProductCode產品代碼 • ProductChnName產品中文名稱 • ProductEngName產品英文名稱 • Spec 產品規格 • 列出英文名稱中，第2個字是a者（LIKE _a%） • 儲存成 [view產品2a]

17. 範例：找尋特定日期的採購單 • 使用DaShang資料庫 • 使用 CustomerSupplier和 Purchase 資料表 • Purchase.SupplierCode是外來鍵 • 以 CustomerSupplier.CustSupCode和 Purchase.SupplierCode建立關聯 • 列出欄位 • PurchaseCode採購單號 • PurchaseDate採購日期 • CustCnName供應商中文名稱 • 列出採購日期介於 2009年1月2日 和 2009年1月5日之間的採購單（BETWEEN '2009/1/2' AND '2009/1/5'） • 儲存成 [view可疑採購]

18. 8-2SQL語法SQL語法 • 全名是Structure Query Language • 起源於1970年代 • 所有的關聯式資料庫系統都採用SQL • 個別廠商之間實作稍有差異

19. SQL 分類 也有人把 SELECT 獨立出來稱為 DQL（Data Query Language）

20. 8-2SQL語法Select基本架構 SELECTCustSupCode, CustCnName, CountryCode FROMCustomerSupplier 或 SELECTCustomerSupplier.CustSupCode, CustomerSupplier.CustomerCnName, CustomerSupplier.CountryCode FROMCustomerSupplier • 查詢指令皆是以Select為首 • Select 欄位 From 資料表 • 取出資料表及部份欄位

21. 8-2SQL語法Select基本架構 SELECT * FROMCustomerSupplier 或 SELECT Members.* FROMCustomerSupplierAS Members 取出資料表及全部欄位

22. 8-2SQL語法Select基本架構 SELECTCS.CustSupCodeAS客戶或廠商編碼, CS.CustCnNameAS中文名稱, CS.CountryCodeAS國家或地區 FROMCustomerSupplier CS • 使用別名（Alias） • 資料表及欄位名稱在執行後可更改為另一名稱 • 欄位以 AS 定義別名 • 資料表別名可以直接寫在名稱後（省略AS）

23. 8-2SQL語法Select基本架構 SELECT DISTINCT CountryCode FROMCustomerSupplier • 唯一值 DISTINCT • 以取出的欄位為準，若其值重覆，只顯示一筆

24. 8-2SQL語法排序 SELECTOrderCode, OrderDate, WithTaxPrice FROM Orders ORDER BY OrderDateDESC, WithTaxPriceASC • 排序的語法在指令之末 • ORDER BY • ASC 從小排到大，預設 • DESC 從大排到小

25. 8-2SQL語法排序 SELECT TOP 5 WithTaxPrice FROM Orders ORDER BY WithTaxPriceDESC SELECT TOP 10 PERCENTWithTaxPrice FROM Orders ORDER BY WithTaxPriceDESC • TOP • 限制傳回查詢結果 • 配合排序，可製作類似排行榜的查詢 • 傳回前5筆 • 傳回前10%

26. 8-2SQL語法Where條件 SELECTNameChn, NameEng, PID FROM Employee WHERENameEng='NICK' SELECTNameChn, NameEng, PID FROM Employee WHERENameChnLIKEN'_明%' • 字串的完全比對 • 字串的部份比對及萬用字元

27. 模糊比對

28. 8-2SQL語法Where條件 SELECT * FROM Employee WHEREOutDateIS NULL SELECTOrderCode, OrderDate, WithTaxPrice FROM Orders WHERE NOT (WithTaxPriceBETWEEN 10000 AND 100000) • 空白及非空白 • 準則是Is Null或Is Not Null • 使用NOT之後是可以產生Boolean結果的運算式

29. 練習 在 DaShang資料庫之中，Employee 資料表儲存員工基本資料，其中 InDate紀錄員工到職日期，OutDate紀錄離職日期。 請列出目前還在職的員工 請列出已經離職的員工 請列出所有的男性員工 請列出所有的女性員工

30. 練習 在 DaShang資料庫之中，Orders 資料表儲存訂單基本資料，其中 NoneTaxPriceTW為不含稅訂單總金額，CurrencyCode為幣別，ExchangeRate為匯率。 請列出不含稅訂單總金額最低的訂單 請列出不含稅訂單總金額最高的前三名 請列出不含稅訂單總金額介於新台幣 10 萬到100萬元之間的訂單

31. 8-2SQL語法Where條件 SELECTEMPCode, NameChn FROM Employee WHEREEMPCodeIN ('E-001', 'E-002', 'E-003') • 使用 IN • 可以做為 OR 的替代設計 • 也可使用 NOT IN

32. 8-2SQL語法Where條件 SELECTProductCode, ProductChnName, ProductEngName, isFromSupplier, Spec, MaterialType FROM Product WHERE (ProductCodeLIKE '15%') AND (isFromSupplier = 0) OR (MaterialType = 3) • 多重準則 • 準則間的關係可分為 AND 及 OR 再加上括號

33. 8-2SQL語法多資料表查詢指令 SELECTProduct.ProductCode, Product.ProductChnName, OrderDetail.Quantity, OrderDetail.Price FROM Product INNER JOIN OrderDetailONProduct.ProductCode = OrderDetail.ProductCode • INNER JOIN • 取兩邊都有的

34. 8-2SQL語法多資料表查詢指令 SELECTProduct.ProductCode, Product.ProductChnName, OrderDetail.Quantity, OrderDetail.Price FROM Product LEFT OUTER JOIN OrderDetailONProduct.ProductCode = OrderDetail.ProductCode 列出所有的產品，以及這些產品的訂單明細 • LEFT及RIGHT JOIN • 以其中一方為準，取出所有記錄

35. 練習 • 請列出所有的客戶，以及這些客戶的訂單 • 列出欄位： • CustSupCode客戶代碼 • CustCnName客戶中文名稱 • OrderCode訂單編號 • OrderDate訂單日期 • WithTaxPriceTW含稅金額 • 請列出從來未曾下過訂單的客戶

36. 8-2SQL語法群組依據及計算 SELECTMAX(OrderDate) AS最後交易日 FROM Orders SELECTP.ProductCodeAS產品編號, SUM(O.Quantity) AS總銷售量, SUM(O.SubTotal) AS總銷售金額, AVG(O.Price) AS平均單價 FROM Product AS P INNER JOIN OrderDetailAS O ONP.ProductCode = O.ProductCode GROUP BY P.ProductCode • 使用彙總函數 • 針對欄位內資料在集合中的計算方式 • 群組依據

37. 8-2SQL語法WHERE及HAVING • 可以在群組及非群組欄位使用條件 • HAVING 針對分組的結果 • WHERE 針對尚未分組前的資料表

38. 列出 2000 年以來，各項繼電器相關產品的銷售總數量、總金額以及平均價格，依平均價格降冪排序 SELECTP.ProductCode, P.ProductChnName, SUM(D.Quantity) AS數量, SUM(D.SubTotal) AS金額, AVG(D.Price) AS均價 FROM Product AS P INNER JOIN OrderDetailAS D ONP.ProductCode = D.ProductCodeINNER JOIN Orders AS O OND.OrderCode = O.OrderCode WHERE (O.OrderDate>= '2000/01/01') AND (P.ProductChnNameLIKE N'%繼電器%') GROUP BY P.ProductCode, P.ProductChnName ORDER BY 均價DESC

39. 練習 • 製作銷售排名： • 列出每一位客戶的銷售狀況，包括銷售總金額（未稅金額，轉成新台幣）和銷售總量，依銷售總金額降冪排序 • 製作採購排名： • 列出每一位供應商的供貨狀況，包括採購總金額（未稅金額，轉成新台幣）和採購總量，依採購總金額降冪排序

40. 8-2SQL語法子查詢 • 子查詢通常位於欄位或Where條件中 • 以集合的方式進行查詢運算 • 在需要的時候，以SELECT產生一個新集合 • 比較的方式 • EXISTS：不是空集合 • NOT EXISTS：是空集合 • IN：在集合內 • NOT IN：不在集合內

41. 列出各項產品目前的庫存量 SELECTProductCode, ProductChnName, ( SELECT SUM(CurrentQty) FROM Stock S1 WHERE S1.ProductCode = P.ProductCode ) AS Qty FROM Product P WHERENOT ( SELECT SUM(CurrentQty) FROM Stock S2 WHERE S2.ProductCode = P.ProductCode ) IS NULL;

42. 8-2SQL語法子查詢 SELECTProductCode, ProductChnName FROM Product AS P WHEREEXISTS ( SELECTProductCode FROMOrderDetailAS D WHERE (ProductCode = P.ProductCode) ) 列出曾經出現在訂單中的產品 • EXISTS－結果不是空集合 • 傳回True或False

43. 列出曾經出現在訂單中的產品 SELECTProductCode, ProductChnName FROM Product AS P WHEREProductCodeIN ( SELECTProductCode FROMOrderDetail )

44. 練習 • 列出沒人買過的產品 • 從未出現在訂單中的產品 • 列出還沒買過東西的客戶

45. 8-2SQL語法子查詢 • SELECTProductCode, StandardPrice • FROM Product P • WHERE (StandardPrice >= ALL ( • SELECTPrice • FROMOrderDetail • WHERE(ProductCode = P.ProductCode)) • ) 列出定價不低於售價的產品---任何一筆交易都算數 • 結合運算子的比對 • All－之後通常會是範圍，之前則是>、<、=

46. 8-2SQL語法子查詢 • SOME 和 ANY 意義相同 • >= ANY(...) • 查詢結果以範圍內的最小值為準 • 顯示大於等於最小值的記錄

47. 練習 • 列出定價（建議售價）比實際售價低的產品 • 曾經有某一個業務員很厲害 • 如何找出是那一張訂單完成這筆交易？ • 如何找出是誰完成這筆交易？

48. 8-2SQL語法更改記錄的指令 SELECTC.CustSupCode, O.OrderCode, O.WithTaxPriceTW INTO CustomerOrdersLog FROMCustomerSupplier C INNER JOIN Orders O ON C.CustSupCode = O.CustCode • 產生資料表 • 以 SELECT 出來的資料建立新的 Table

49. 8-2SQL語法更改記錄的指令 INSERT INTO CustomerOrdersLog(CustSupCode, OrderCode, WithTaxPriceTW) SELECTC.CustSupCode, O.OrderCode, O.WithTaxPriceTW FROMCustomerSupplier C INNER JOIN Orders O ON C.CustSupCode= O.CustCode • 新增記錄 • 將 SELECT 出來的資料，插入現存的 Table

50. 8-2SQL語法更改記錄的指令 DELETE FROM Orders WHEREOrderDate <= '1/1/2009' 刪除記錄