課程參與度之評估方式

1. 課程參與度之評估方式 • 上課時必須專心聽講，跟上進度，參與討論 • 扣分項目 • 玩線上遊戲一次扣1分 • 玩手機一次扣1分 • 睡覺一次扣1分 • 聊天一次扣1分 • 無法回答老師提出的問題一次扣1分 • 加分項目 • 主動回答老師的問題一次加2分 • 找出老師程式中的錯誤一次加1分 • 修正老師程式中的錯誤一次加4分

2. MS SQL Server系統實務CH07 建立檢視表及SQL語法 鄧姚文

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

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

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

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

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

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

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

15. SQL 分類

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

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

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

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

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

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

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

24. 模糊比對

25. 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結果的運算式

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

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

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

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

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

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

32. 練習 請列出所有的客戶，以及這些客戶的訂單 請列出從來未曾下過訂單的客戶

33. 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 • 使用彙總函數 • 針對欄位內資料在集合中的計算方式 • 群組依據

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

35. 列出 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

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

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

38. 列出各項產品目前的庫存量 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;

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

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

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

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

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

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

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

46. 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

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

48. 8-2SQL語法更改記錄的指令 UPDATE Product SETStandardPrice= StandardPrice * 1.05 WHERESupplierCode = 'C-017' 更新記錄

49. 練習依照銷售狀況調整售價 • 列出平均售價（AVG(OrderDetail.Price)）高於標準售價（Product.StandardPrice）的產品，將這些產品的標準售價修改為平均售價*1.05 • 列出平均售價（AVG(OrderDetail.Price)）低於標準售價（Product.StandardPrice）高的產品，將這些產品的標準售價修改為平均售價

50. 練習校正客戶/供應商紀錄 • 設定 CustomerSupplier.Customer • 若該客戶有訂單(Orders)或報價單(Quote)CustomerSupplier.Customer=1 • 否則CustomerSupplier.Customer=0 • 設定 CustomerSupplier.Supplier • 若該客戶曾經下過採購單CustomerSupplier.Supplier=1 • 否則CustomerSupplier.Supplier=0