第七章 結構化查詢語言SQL(二) 資料庫系統理論與實務 [邏輯思維系列]

Presentation on theme: "第七章 結構化查詢語言SQL(二) 資料庫系統理論與實務 [邏輯思維系列]"— Presentation transcript:

1 第七章 結構化查詢語言SQL(二) 資料庫系統理論與實務 [邏輯思維系列]

2 本章在架構中的位置 MS SQL Server 2005 理論與實作(一) (08)
My SQL Server 2005 理論與實作(二)(13) 回復技術(11) 結構化查詢語言 SQL(一)(06) 結構化查詢語言 SQL(二)(07) 與管理(12) 資料庫安全 關聯式代數(05) 並行控制(10) 關聯式模型(03) (正規化) 合併理論(04) 交易處理(09) 資料模型(02) 資料庫系統簡介(01) 邏輯與思維 2 /80

3 本章內容 7-1簡介 7-2進階異動操作 7-3進階查詢操作 7-4聚合函數(Aggregate Functions)
新增操作(Inert Operation) 刪除操作(Delete Operation) 更新操作(Update Operation) 7-3進階查詢操作 7-4聚合函數(Aggregate Functions) 7-5查詢語法SELECT之剖析整理 3 /80

4 7-1 簡介 本章是延續前一章之『資料操作語言』 (Data Manipulation Language，簡稱DML)中的異動操作(Insert、Delete和Update)以及查詢(Select)語法 4 /80

5 本章內容 7-1簡介 7-2進階異動操作 7-3進階查詢操作 7-4聚合函數(Aggregate Functions)
新增操作(Inert Operation) 刪除操作(Delete Operation) 更新操作(Update Operation) 7-3進階查詢操作 7-4聚合函數(Aggregate Functions) 7-5查詢語法SELECT之剖析整理 5 /80

6 新增INSERT之進階語法 6 /80

7 【範例7-1】 【範例7-1】 從『客戶』中挑選出公司名稱為『日盛金樓』的客戶資料，新增至『供應商』資料表內，並將供應編號編為S0006 【說明】 此範例是利用DML中的SELECT語法，從『客戶』資料表中挑選出所要的資料，新增至『供應商』資料表內，如圖7-1所示。 續下頁 7 /80

8 【語法】 INSERT INTO 供應商 (供應商編號, 供應商, 聯絡人, 聯絡人職稱, 聯絡人性別, 郵遞區號, 地址, 電話) SELECT ‘S0006’, 公司名稱, 聯絡人, 聯絡人職稱, 聯絡人性別, 郵遞區號, 地址, 電話 FROM 客戶 WHERE 公司名稱=’日盛金樓’ 客戶 SELECT … 供應商 INSERT … 日盛金樓 圖7-1 範例7-1之語意示意圖 8 /80

9 本章內容 7-1簡介 7-2進階異動操作 7-3進階查詢操作 7-4聚合函數(Aggregate Functions)
新增操作(Inert Operation) 刪除操作(Delete Operation) 更新操作(Update Operation) 7-3進階查詢操作 7-4聚合函數(Aggregate Functions) 7-5查詢語法SELECT之剖析整理 9 /80

10 刪除DELETE之進階語法 10 /80

11 【範例7-2】 【範例7-2】 【說明】 刪除客戶的公司名稱為『丁泉』的所有『訂單』和『訂單明細』資料。
此範例必須先刪除『訂單明細』資料表內的相關資料，再刪除『訂單』資料表內的相關資料，並透過參考相關的資料表，尤其是『客戶』資料表中的公司名稱為『丁泉』。如圖7-2之示意圖 續下頁 11 /80

12 【語法】 DELETE FROM 訂單明細 FROM 訂單, 客戶 WHERE 訂單.客戶編號 = 客戶.客戶編號 AND 訂單.訂單編號 = 訂單明細.訂單編號 AND 公司名稱 = '丁泉' 客戶 訂單明細 訂單 丁泉 被刪除的部份 圖7-2 範例7-2之語意示意圖 (a) 刪除『訂單明細』 12 /80

13 【語法】 DELETE FROM 訂單 FROM 客戶 WHERE 訂單.客戶編號 = 客戶.客戶編號 AND 公司名稱 = '丁泉'
被刪除的部份 客戶 訂單 丁泉 圖7-2 範例7-2之語意示意圖 (b) 刪除『訂單』 13 /80

14 本章內容 7-1簡介 7-2進階異動操作 7-3進階查詢操作 7-4聚合函數(Aggregate Functions)
新增操作(Inert Operation) 刪除操作(Delete Operation) 更新操作(Update Operation) 7-3進階查詢操作 7-4聚合函數(Aggregate Functions) 7-5查詢語法SELECT之剖析整理 14 /80

15 更新UPDATE之進階語法 15 /80

16 【範例7-3】 【範例7-3】 【說明】 續下頁 『客戶』資料表中，公司名稱為『丁泉』訂單的付款方式，全部改成現金。
由於『訂單』資料表中，只有客戶編號的屬性，並沒有客戶的公司名稱屬性，所以必須要參考『客戶』資料表才能得知哪些訂單是『丁泉』公司所下的訂單，故以合併方式來更新訂單中的付款方式，如圖7-3之示意圖。 但由於兩資料表有具有相同屬性名稱客戶編號，為避免資料庫管理系統會產生混淆，所以必須使用『點表示法』 (Dot Notation)，也就是在相同屬性的屬性名稱之前加上該資料表的名稱，並於中間加上一個『點』 續下頁 16 /80

17 【語法】 UPDATE 訂單 SET 付款方式 = ‘現金’ FROM 客戶 WHERE 訂單.客戶編號 = 客戶.客戶編號 AND 公司名稱 = ‘丁泉’ 被更新的部份(付款方式) 客戶 訂單 丁泉 17 /80

18 【範例7-4】 【範例7-4】 將『供應商』資料表和『客戶』資料表中，公司名稱相同的地址，依據『客戶』資料表中的地址，更新至『供應商』。 【說明】 此範例是以客戶資料表的地址來更新供應商資料表中的地址，如圖7-4之示意圖，依據『客戶』中的地址更新『供應商』中的地址。 續下頁 18 /80

19 【語法】 UPDATE 供應商 SET 供應商.地址 = 客戶.地址 FROM 客戶 WHERE 供應商.供應商 = 客戶.公司名稱
被更新的部份(地址) 客戶 供應商 依據『客戶』中的地址更新『供應商』中的地址 圖7-3 範例7-3之語意示意圖 19 /80

20 本章內容 7-1簡介 7-2進階異動操作 7-3進階查詢操作 7-4聚合函數(Aggregate Functions)
新增操作(Inert Operation) 刪除操作(Delete Operation) 更新操作(Update Operation) 7-3進階查詢操作 7-4聚合函數(Aggregate Functions) 7-5查詢語法SELECT之剖析整理 20 /80

21 【範例7-5】內部合併的另一語法，使用JOIN
同【範例6-19】查詢出每一位員工所承接的訂單資料，輸出屬性包括員工編號、姓名、訂單編號、訂貨日期，並依員工編號和訂單編號遞增排序。改寫成另一語法 tableName1 INNER JOIN tableName2 ON … 【說明】 使用此JOIN的語法要特別注意到JOIN的過程，如圖7-5代表【範例6-19】的合併過程，此範例只有兩個資料表(員工和訂單)，就直接合併。 續下頁 21 /80

22 【語法】 SELECT 員工.員工編號, 姓名, 訂單編號, 訂貨日期 FROM (員工 INNER JOIN 訂單 ON 員工.員工編號=訂單.員工編號) ORDER BY 員工.員工編號, 訂單編號 JOIN 22 /80

23 【範例7-6】內部合併的另一語法，使用JOIN
同【範例6-20】查詢出每一位員工所承接的訂單資料，輸出屬性包括員工編號、姓名、訂單編號、訂貨日期和產品編號，並依員工編號、訂單編號和產品編號三個屬性遞增排序。改寫成另一語法 tableName1 INNER JOIN tableName2 ON … INNER JOIN tableName3 ON … 【說明】 使用此JOIN的語法要特別注意到JOIN的過程，如圖7-6代表【範例6-20】的合併過程，此範例由於有三個資料表(員工、訂單以及訂單明細)，所必須先將兩個資料表先合併後，當成一個虛擬資料表，再與另一資料表做第二次合併。 在上述中是以分解合併說明，但在實作上，卻是透過一個敘述(statement)中，包括多次的INNER JOIN來達成此合併動作。 續下頁 23 /80

24 【語法】 SELECT 員工.員工編號, 姓名, 訂單.訂單編號, 訂貨日期, 產品編號 FROM (員工 INNER JOIN 訂單 ON 員工.員工編號=訂單.員工編號) INNER JOIN 訂單明細 ON 訂單.訂單編號=訂單明細.訂單編號 ORDER BY 員工.員工編號, 訂單.訂單編號, 產品編號 (1) JOIN (2) JOIN 24 /80

25 【範例7-7】內部合併的另一語法，使用JOIN
同【範例6-21】查詢出每一位員工所承接的訂單資料，輸出屬性包括員工編號、姓名、訂單編號、訂貨日期和產品名稱，並依員工編號、訂單編號二個屬性遞增排序。改寫成另一語法。 tableName1 INNER JOIN tableName2 ON … INNER JOIN tableName3 ON … INNER JOIN tableName4 ON … 【說明】 使用此JOIN的語法要特別注意到JOIN的過程，如圖7-7【範例6-21】的合併過程，此範例共需要到四個資料表(員工、訂單、訂單明細以及產品資料)，所以先將『員工』資料表和『訂單』資料表合併後，當成一個虛擬資料表，再與『訂單明細』資料表合併，再當成另一虛擬資料表，再與『產品資料』合併。 在上述中是以分解合併說明，但在實作上，卻是透過一個敘述(statement)中，包括多次的INNER JOIN來達成此合併動作。 續下頁 25 /80

26 【語法】 SELECT 員工.員工編號, 姓名, 訂單.訂單編號, 訂貨日期, 產品名稱 FROM ((員工 INNER JOIN 訂單 ON 員工.員工編號=訂單.員工編號) INNER JOIN 訂單明細 ON 訂單.訂單編號=訂單明細.訂單編號) INNER JOIN 產品資料 ON 訂單明細.產品編號=產品資料.產品編號 ORDER BY 員工.員工編號, 訂單.訂單編號 (1) JOIN (2) JOIN (3) JOIN 26 /80

27 【範例7-8】外部合併 【範例7-8】外部合併 【說明】 【語法】 續下頁
請依【範例7-5】改寫成外部合併，也就是不論員工是否有承接訂單皆必須顯示出來。 【說明】 如同【範例7-5】但所使用的語法為 leftTableName LEFT OUTER JOIN rightTableName ON … 【語法】 SELECT 員工.員工編號, 姓名, 訂單編號, 訂貨日期 FROM (員工 LEFT OUTER JOIN 訂單 ON 員工.員工編號=訂單.員工編號) ORDER BY 員工.員工編號, 訂單編號 續下頁 27 /80

28 圖7-8 範例7-8 之結果 28 /80

29 續下頁 【範例7-9】外部合併 【說明】 【語法】 請依【範例7-6】改寫成外部合併，也就是不論員工是否有承接訂單皆必須顯示出來。
如同【範例7-6】，但所使用的語法為 leftTableName LEFT OUTER JOIN rightTableName1 ON … LEFT OUTER JOIN rightTableName2 ON … 【語法】 SELECT 員工.員工編號, 姓名, 訂單.訂單編號, 訂貨日期, 產品編號 FROM (員工 LEFT OUTER JOIN 訂單 ON 員工.員工編號=訂單.員工編號) LEFT OUTER JOIN 訂單明細 ON 訂單.訂單編號=訂單明細.訂單編號 ORDER BY 員工.員工編號, 訂單.訂單編號, 產品編號 續下頁 29 /80

30 圖7-9 範例7-9 之結果 30 /80

31 【範例7-10】外部合併 續下頁 【範例7-10】外部合併 【說明】 【語法】
依【範例7-7】改寫成外部合併，也就是不論員工是否有承接訂單皆必須顯示出來。 【說明】 如同【範例7-7】之說明，但所使用的語法為 leftTableName LEFT OUTER JOIN rightTableName1 ON … LEFT OUTER JOIN rightTableName2 ON … LEFT OUTER JOIN rightTableName3 ON … 【語法】 SELECT 員工.員工編號, 姓名, 訂單.訂單編號, 訂貨日期, 產品名稱 FROM ((員工 LEFT OUTER JOIN 訂單 ON 員工.員工編號=訂單.員工編號) LEFT OUTER JOIN 訂單明細 ON 訂單.訂單編號=訂單明細.訂單編號) LEFT OUTER JOIN 產品資料 ON 訂單明細.產品編號=產品資料.產品編號 ORDER BY 員工.員工編號, 訂單.訂單編號 續下頁 31 /80

32 圖7-10 範例7-10 之結果 32 /80

33 【範例7-11】合併處理與條件篩選 續下頁 【範例7-11】合併處理與條件篩選 【說明】
請依【範例6-21】的情形，再將輸出的資料做一篩選，僅要查詢出員工之姓名為”劉逸萍”的相關訂單資料，如同【範例6-21】的輸出屬性。 【說明】 此範例將以兩種語法來表示出此語法，而此範例的主要目的是將語法中的合併操作與一般的條件篩選分離，以協助初學者容易記憶生硬的語法和增加熟悉度。 可將以下語法(一)中，在WHERE後面的條件解釋如下 續下頁 33 /80

34 【範例7-11】合併處理與條件篩選 『員工.員工編號 = 訂單.員工編號』： 表示『員工』和『訂單』資料表之間的關聯性
『訂單.訂單編號 = 訂單明細.訂單編號』： 表示『訂單』和『訂單明細』資料表之間的關聯性 『訂單明細.產品編號 = 產品資料.產品編號』： 表示『訂單明細』和『產品資料』資料表之間的關聯性 『姓名 = ‘劉逸萍’』： 為一般紀錄的條件篩選 由以上1~3的關聯性而言，可視為員工、訂單、訂單明細和產品資料等四個資料表的合併關係。 續下頁 34 /80

35 【語法】 (語法一) 續下頁 SELECT 員工.員工編號, 姓名, 訂單.訂單編號, 訂貨日期, 產品名稱
FROM 員工, 訂單, 訂單明細, 產品資料 WHERE 員工.員工編號 = 訂單.員工編號 AND 訂單.訂單編號 = 訂單明細.訂單編號 AND 訂單明細.產品編號 = 產品資料.產品編號 AND 姓名 = ‘劉逸萍’ ORDER BY 員工.員工編號, 訂單.訂單編號 合併 條件篩選 續下頁 35 /80

36 【語法】 (語法二) 續下頁 SELECT 員工.員工編號, 姓名, 訂單.訂單編號, 訂貨日期, 產品名稱
FROM ((員工 INNER JOIN 訂單 ON 員工.員工編號=訂單.員工編號) INNER JOIN 訂單明細 ON 訂單.訂單編號=訂單明細.訂單編號) INNER JOIN 產品資料 ON 訂單明細.產品編號=產品資料.產品編號 WHERE 姓名 = ‘劉逸萍’ ORDER BY 員工.員工編號, 訂單.訂單編號 合併 條件篩選 續下頁 36 /80

37 圖7-11 範例7-11 之結果 37 /80

38 【範例7-12】使用子字串的比對方式 續下頁 【範例7-12】使用子字串的比對方式 【說明】
請查詢員工資料表中，員工地址住在台北(縣)市，以及員工編號中，第三碼數字為1的員工相關資料，包括員工編號、姓名、職稱和地址。 【說明】 此範例中所使用到的比對關係並非使用完全比對，而是比對字串中的部份字串，如第一個條件中，限制員工地址住在台北縣或台北市的員工，所以在WHERE條件中，可以使用LIKE來做部份比對，所使用的是萬用字元的方式，而此條件可以使用”%”來比對，”%”所代表的是任何長度（任意個字元數）的任何字元，所以在此限制可使用 地址 LIKE ‘台北%’ 另一條件卻規定了員工編號的第三碼為1，此條件已限制了1的前面有兩個固定字元數，所以此條件可使用”_”來比對，”_”所代表的是單一個長度（單一個字元）的任何字元，所以在此限制可使用 員工編號 LIKE ‘_ _ 1 _ _ _ _’ 不同資料庫管理系統的產品，所使用的萬用字元會有所不同 續下頁 38 /80

39 【語法】 SELECT 員工編號, 姓名, 職稱, 地址 FROM 員工 WHERE 地址 LIKE ‘台北%’ AND 員工編號 LIKE ‘_ _1 _ _ _ _’ 圖7-12 範例7-12 之結果 39 /80

40 【範例7-13】使用屬於關係篩選資料 【範例7-13】使用屬於關係篩選資料 【說明】 續下頁
查詢出有哪些的訂單中有產品編號為1,6,10中的任一項，只要輸出訂單編號，並依此編號遞增排序，以及將重複資料只顯示一筆。 【說明】 此查詢可使用最前述的邏輯比較(OR)方式，如下 產品編號=1 OR 產品編號=6 OR 產品編號=10 完整語法如(語法一)。 但此處將使用另一種表答方式，也就是集合論中的屬於關係，表示方式如下所示 產品編號 IN (1, 6, 10) 完整語法如(語法二)。 續下頁 40 /80

41 【語法】 (語法一) SELECT DISTINCT 訂單編號 FROM 訂單明細 WHERE 產品編號 1 OR 產品編號 = 6 OR 產品編號 = 10 ORDER BY 訂單編號 (語法二) SELECT DISTINCT 訂單編號 FROM 訂單明細 WHERE 產品編號 IN (1, 6, 10) ORDER BY 訂單編號 圖7-13 範例7-13 之結果 41 /80

42 【範例7-14】具有『子查詢』的查詢 – 『獨立子查詢』
【範例7-14】具有『子查詢』的查詢 – 『獨立子查詢』 【範例7-14】具有『子查詢』的查詢 – 『獨立子查詢』 查詢出在客戶資料表中，也具有供應商身份的資料，輸出屬性包括客戶的客戶編號、公司名稱，並依客戶編號遞增排序。 【說明】 在此範例所要查詢的資料，如同是『客戶』和『供應商』資料表兩邊皆有的共同資料，所以可以使用前述的『INNER JOIN』的方式來合併出兩邊共同的紀錄，如同(語法一)的寫法。 另外，亦可使用子查詢的方式，也就是先將供應商的供應商屬性值皆挑選出來形成一個集合，再用IN的屬於關係查詢出客戶的公司名稱有在供應商集合中的資料，如同(語法二)的寫法。 在此查詢中使用到兩種寫法，在(語法二)的寫法中，使用到了查詢中亦包含查詢，在內部的查詢即稱為『子查詢』，而此處的子查詢又可獨立地被執行，並查詢出供應商的相關資料，所以也稱之為『獨立子查詢』。 續下頁 42 /80

43 【語法】 (語法一) SELECT 客戶編號, 公司名稱 FROM 客戶, 供應商 WHERE 公司名稱 = 供應商 ORDER BY 客戶編號 (語法二) SELECT 客戶編號, 公司名稱 FROM 客戶 WHERE 公司名稱 IN ( SELECT 供應商 FROM 供應商) ORDER BY 客戶編號 圖7-14 範例7-14之結果 43 /80

44 【範例7-15】具有『子查詢』的查詢 – 『相依子查詢』
查詢出在所有員工資料表中，有承接訂單的員工資料，輸出資料包括員工編號和姓名，並以員工編號排序。 【說明】 以此範例所有表示的語法，可分為兩種語法來思維，如(語法一)是先將兩個資料表先進行合併處理(INNER JOIN)，也就是合併出兩者共同的紀錄，再選取出員工編號和姓名，如圖7-15(a)。 另一種語法，如(語法二)採用子查詢方式，先將兩者資料表合併後，選出兩者共同的員工編號，再選取出在此員工編號集合中的員工編號和姓名，如圖7-15(b)。 續下頁 44 /80

45 【語法】 (語法一) SELECT DISTINCT E.員工編號, 姓名 FROM 員工 AS E , 訂單 AS O WHERE E.員工編號 = O.員工編號 ORDER BY E.員工編號 員工 訂單 員工編號 姓名 (a) 合併後的結果，再選取虛線內的員工編號和姓名 45 /80

46 【語法】 (語法二) SELECT 員工編號, 姓名 FROM 員工 WHERE 員工編號 IN ( SELECT 員工編號 FROM 訂單 WHERE 員工編號 = 員工.員工編號) ORDER BY 員工編號 員工 訂單 員工編號 姓名 (b) 存在此處的員工編號，再選取此處的員工編號和姓名 46 /80

47 【範例7-16】存在性的測試EXISTS 【範例7-16】存在性的測試EXISTS 【說明】 題目如【範例7-15】
如語法中所表示的是，選取出員工編號和姓名，存在於兩者資料表的合併(INNER JOIN)結果中的相關資料。 續下頁 47 /80

48 【語法】 SELECT 員工編號, 姓名 FROM 員工 WHERE EXISTS ( SELECT * FROM 訂單 WHERE 員工編號 = 員工.員工編號) ORDER BY 員工編號 員工 訂單 員工編號 姓名 『存在』於合併後的結果之員工，再從虛線框中選取出員工編號和姓名 48 /80

49 【範例7-17】NOT 續下頁 【範例7-17】NOT 【說明】
題目如【範例715】，但所要查詢的輸出資料剛好完全相反，也就是在所有員工資料表中，沒有承接任何一筆訂單的員工資料，輸出資料包括員工編號和姓名，並以員工編號排序。 【說明】 此範例使用了兩種語法來表示，(語法一)是使用相依子查詢的方式，先將兩個資料表做INNER JOIN的結果，並挑選出員工編號，而此處員工編號的集合是屬於有承接訂單的員工；反之，要查詢出沒有承接任何一筆訂單之員工資料，應該是挑選出不屬於此集合的員工資料，也就是圖中所標示的『目標區域』，如圖7-18(a)所示。 (語法二)是使用存在性測試的方式，先找出存在於員工和訂單資料表之間INNER JOIN後之虛擬資料表，再找出不屬於此資料表中之員工資料，亦就是其他不屬於此處的員工資料，也就是圖中所標示的『目標區域』，如圖7-18(b)所示。 續下頁 49 /80

50 【語法】 (語法一) SELECT 員工編號, 姓名 FROM 員工 WHERE 員工編號 NOT IN ( SELECT 員工編號 FROM 訂單 WHERE 員工編號 = 員工.員工編號) ORDER BY 員工編號 目標區域 員工 訂單 選取出不屬於此集合中的員工編號，也就是在目標區域中的員工資料 續下頁 (a) 50 /80

51 【語法】 (語法二) SELECT 員工編號, 姓名 FROM 員工 WHERE NOT EXISTS ( SELECT 員工編號 FROM 訂單 WHERE 員工編號 = 員工.員工編號) ORDER BY 員工編號 目標區域 員工 訂單 選取虛線外的員工編號和姓名，亦就是在目標區域內的資料 (b) 51 /80

52 【範例7-18】自我合併(Self-Join)-INNER JOIN查詢
從員工資料表中查詢出有主管的員工和所屬主管資料，輸出屬性包括員工編號、員工姓名、主管編號和主管姓名，並依員工編號遞增排序。 【說明】 在員工資料表中，由於屬性『報告人』是參考『員工編號』，此種關係可稱為自我參考，但在實作上並無法僅使用一個資料表來表達和實作，所以必須使用兩個員工資料表，並扮演兩個不同的角色，如圖7-20所示，可透過別名的方式，將其一扮演『職員』，另一扮演成『主管』，此處形同兩個完全不相同的資料表來看待，在實作上，亦當成兩個獨立資料表來處理。 續下頁 52 /80

53 一個員工資料表同時扮演職員和主管兩個角色
53 /80

54 【語法】 (語法一) SELECT 職員.員工編號, 職員.姓名 AS 員工姓名, 主管.員工編號 AS 主管編號, 主管.姓名 AS 主管姓名 FROM 員工 AS 職員, 員工 AS 主管 WHERE 職員.報告人=主管.員工編號 ORDER BY職員.員工編號 (語法二) SELECT 職員.員工編號, 職員.姓名 AS 員工姓名, 主管.員工編號 AS 主管編號, 主管.姓名 AS 主管姓名 FROM 員工 AS 職員 INNER JOIN 員工 AS 主管 ON 職員.報告人=主管.員工編號 ORDER BY 職員.員工編號 54 /80

55 圖7-21 範例7-18 之結果 55 /80

56 【範例7-19】自我合併(Self-Join)-OUTER JOIN查詢
此範例的條件類似【範例7-18】，但從員工資料表是要查詢出所有員工和所屬主管資料，輸出屬性包括員工編號、員工姓名、主管編號和主管姓名。 【說明】 如同【範例7-18】之說明，唯此範例必須使用外部合併，方能將所有員工和所屬主管的資料查詢出。 續下頁 56 /80

57 【語法】 SELECT 職員.員工編號, 職員.姓名 AS 員工姓名, 主管.員工編號 AS 主管編號, 主管.姓名 AS 主管姓名 FROM 員工 AS 職員 LEFT OUTER JOIN 員工 AS 主管 ON 職員.報告人=主管.員工編號 ORDER BY 職員.員工編號 圖7-22 範例7-19之結果 57 /80

58 本章內容 7-1簡介 7-2進階異動操作 7-3進階查詢操作 7-4聚合函數(Aggregate Functions)
新增操作(Inert Operation) 刪除操作(Delete Operation) 更新操作(Update Operation) 7-3進階查詢操作 7-4聚合函數(Aggregate Functions) 7-5查詢語法SELECT之剖析整理 58 /80

59 7-4 聚合函數 COUNT()：依據輸入的項目，計算出總筆數。 SUM()：依據輸入的項目，將其加總。
MAX()：依據輸入的項目，找出最大值的項目。 MIN()：依據輸入的項目，找出最小值的項目。 AVERAGE()：依據輸入的項目，計算出平均值。 零至多個輸入參數 一個輸出值 計算 59 /80

60 【範例7-20】利用聚合函數COUNT計算筆數的查詢
請計算出員工總人數。 【說明】 此範例主要是計算出『員工』資料表內的總筆數，因此使用聚合函數中的COUNT函數。以下的兩種語法個別說明如下 (語法一) 使用COUNT(*)，在聚合函數內的輸入為星號*，表示萬用字元，也就是指所有的屬性，意指所有屬性的屬性值皆非空值(Not Null Value)者，將會計數一次。 【語法】 (語法一) SELECT COUNT(*) AS 員工總人數 FROM 員工 圖7-24 範例7-20之結果 續下頁 60 /80

61 【說明】 (語法二) 使用COUNT(員工編號)，在聚合函數內的輸入為『員工編號』，意指計算出員工編號非空值(Not Null Value)的數量。由於此處所使用的『員工編號』為員工資料表內的主要鍵(Primary Key)，不可能為空值，故如此的寫法將不會有任何的問題發生。倘若寫成COUNT(職稱)，可能將會造成問題，因為『職稱』的屬性，在此資料表內並未限制為不可為空值，如果有員工的職稱剛好為空值時，該筆記錄將不會被計數，而造成筆數上的不正確。 【語法】 (語法二) SELECT COUNT(員工編號) AS 員工總人數 FROM 員工 圖7-24 範例7-20之結果 61 /80

62 【範例7-21】利用聚合函數COUNT計算筆數的查詢
請分別計算員工中，男性員工和女性員工的個別總人數。 【說明】 此範例可以使用WHERE，先過濾出符合條件的資料，再進行COUNT的計算，所以分別計算兩次，如下。 【語法】 SELECT COUNT(*) AS 男性員工總人數 FROM 員工 WHERE 性別 = ‘男’ SELECT COUNT(*) AS 女性員工總人數 FROM 員工 WHERE 性別 = ‘女’ 圖7-25範例7-21之結果 62 /80

63 【範例7-22】利用聚合函數COUNT以及分群組 (GROUP BY) 方式，計算出不同群組的筆數查詢
請分別計算員工中，男性員工和女性員工的個別總人數。 【說明】 此範例雖與【範例7-21】相同，但所使用的語法會有所不同，也就是加入群組的概念，並簡化繁重和複雜的計算。 【語法】 SELECT 性別, COUNT(*) AS 人數 FROM 員工 GROUP BY 性別 圖7-26範例7-22之結果 63 /80

64 【範例7-23】Group By – COUNT()
計算出有承接訂單的每位員工所承接訂單的筆數。 【說明】 本範例中有承接訂單的每位員工表示要使用INNER JOIN來挑選出員工的相關訂單資料，又因為條件中所要查詢的資料為員工承接訂單的筆數，所以可以使用GROUP BY 的語法，並使用COUNT函數來計算， 以下使用兩個語法，其中的差異只在於COUNT()函數內是使用萬用字元”*”和員工編號。 員工 訂單 reference 合併後再以員工編號為群組之依據計算筆數 合併 員工 訂單 64 /80

65 【語法】 (語法一) SELECT E.員工編號, 姓名, COUNT(*) AS 訂單筆數 FROM 員工 AS E, 訂單 AS O WHERE E.員工編號 = O.員工編號 GROUP BY E.員工編號, 姓名 ORDER BY E.員工編號 (語法二) SELECT E.員工編號, 姓名, COUNT(員工編號) AS 訂單筆數 FROM 員工 AS E, 訂單 AS O WHERE E.員工編號 = O.員工編號 GROUP BY E.員工編號, 姓名 ORDER BY E.員工編號 圖7-28 範例7-23 之結果 65 /80

66 【範例7-24】Group By – SUM() 【範例7-24】Group By – SUM() 【說明】 【語法】
計算出每一筆訂單的總價，也就是將訂單明細之中，屬於同一筆訂單的(實際單價 × 數量)相加總。 【說明】 如同範例7-23之說明，除了將函數改為SUM()， 其餘皆相同。 【語法】 SELECT O.訂單編號, SUM(實際單價*數量) AS 合計總價 FROM 訂單 AS O, 訂單明細 AS OD WHERE O.訂單編號 = OD.訂單編號 GROUP BY O.訂單編號 ORDER BY O.訂單編號 圖7-29 範例7-24 之結果 66 /80

67 【範例7-25】Group By … Having 【範例7-25】Group By … Having 【說明】
題目如【範例7-23】，但只挑選出訂單筆數累計有超過三筆的員工資料。 【說明】 此範例主要是針對GROUP BY的計算之後，再將聚合函數所計算出的資料做條件篩選，如圖7-30所示。 員工 訂單 reference 對聚合函數結果做條件篩選 合併後再以員工編號為群組之依據計算筆數 合併 員工 訂單 67 /80

68 【語法】 (語法一) SELECT E.員工編號, 姓名, COUNT(*) AS 訂單筆數 FROM 員工 AS E, 訂單 AS O WHERE E.員工編號 = O.員工編號 GROUP BY E.員工編號, 姓名 HAVING COUNT(*) > 3 ORDER BY E.員工編號 (語法二) SELECT E.員工編號, 姓名, COUNT(*) AS 訂單筆數 FROM 員工 AS E, 訂單 AS O WHERE E.員工編號 = O.員工編號 GROUP BY E.員工編號, 姓名 HAVING 訂單筆數 > 3 ORDER BY E.員工編號 圖7-31 範例7-25 之結果 68 /80

69 【範例7-26】Group By … Having 【範例7-26】Group By … Having 【說明】 【語法】
題目如【範例7-24】，但只挑選出訂單的總計金額超過1,000的資料。 【說明】 如【範例7-25】之說明。 【語法】 (語法一) SELECT O.訂單編號, SUM(實際單價*數量) AS 合計總價 FROM 訂單 AS O, 訂單明細 AS OD WHERE O.訂單編號 = OD.訂單編號 GROUP BY O.訂單編號 HAVING SUM(實際單價*數量) > 1000 ORDER BY O.訂單編號 69 /80

70 【範例7-26】Group By … Having 【語法】
(語法二) SELECT O.訂單編號, SUM(實際單價*數量) AS 合計總價 FROM 訂單 AS O, 訂單明細 AS OD WHERE O.訂單編號 = OD.訂單編號 GROUP BY O.訂單編號 HAVING 合計總價 > 1000 ORDER BY O.訂單編號 圖7-32 範例7-26 之結果 70 /80

71 本章內容 7-1簡介 7-2進階異動操作 7-3進階查詢操作 7-4聚合函數(Aggregate Functions)
新增操作(Inert Operation) 刪除操作(Delete Operation) 更新操作(Update Operation) 7-3進階查詢操作 7-4聚合函數(Aggregate Functions) 7-5查詢語法SELECT之剖析整理 71 /80

72 7-5查詢語法SELECT之剖析整理 資料來源 查詢結果 SELECT FROM WHERE GROUP BY HAVING
(1) FROM WHERE GROUP BY HAVING ORDER BY (2) (3) (4) (5) (6) 72 /80

73 【範例7-27】執行過程 【範例7-27】執行過程 本範例最後的目的是要計算出，提供產品數量大於一個以上的供應商所提供的產品數量，並將輸出結果依據供應商排序。將語法的撰寫過程分為以下六個演進過程，用以說明查詢語法的運作過程。 73 /80

74 【範例7-27】執行過程 『供應商』與『產品資料』的『卡式積』。
對第1項卡式積的結果，再做『投影操作』(PROJECT Operation)，也就是挑選數個屬性。 利用WHERE條件，挑選出『供應商的供應商編號』與『產品資料的供應商編號』的屬性值相等者，所以演進至此，可以當成是以第2項的結果再做『選取操作』(SELECT Operation)，亦也可以當成是做供應商與產品資料的『內部合併』。 依據第3項內部合併的結過進行『分群組』(GROUP BY)以及聚合函數COUNT(*)的計算，也就是利用GROUP BY和COUNT(*)函數。 針對第4項所計算出來的COUNT(*)的結果再篩選數量大於一筆以上的供應商資料，也就是利用HAVING。 針對第5項的結果再進行排序，也就是利用ORDER BY。 74 /80

75 【過程語法1】 SELECT * FROM 供應商, 產品資料 圖7-34 範例7-27過程結果(1) 續下頁 75 /80

76 續下頁 【過程語法2】 SELECT 供應商.供應商編號, 供應商, 產品編號, 產品名稱 FROM 供應商, 產品資料
圖7-35 範例7-27過程結果(2) 續下頁 76 /80

77 【過程語法3】 SELECT 供應商.供應商編號, 供應商, 產品編號, 產品名稱 FROM 供應商, 產品資料 WHERE 供應商.供應商編號 = 產品資料.供應商編號 圖7-36 範例7-27過程結果(3) 續下頁 77 /80

78 【過程語法4】 SELECT 供應商.供應商編號, 供應商, COUNT(*) AS 數量 FROM 供應商, 產品資料 WHERE 供應商.供應商編號 = 產品資料.供應商編號 GROUP BY 供應商.供應商編號, 供應商 圖7-37 範例7-27過程結果(4) 續下頁 78 /80

79 【過程語法5】 SELECT 供應商.供應商編號, 供應商, COUNT(*) AS 數量 FROM 供應商, 產品資料 WHERE 供應商.供應商編號 = 產品資料.供應商編號 GROUP BY 供應商.供應商編號, 供應商 HAVING COUNT(*) > 1 圖7-38 範例7-27過程結果(5) 續下頁 79 /80

80 【過程語法6】 SELECT 供應商.供應商編號, 供應商, COUNT(*) AS 數量 FROM 供應商, 產品資料 WHERE 供應商.供應商編號 = 產品資料.供應商編號 GROUP BY 供應商.供應商編號, 供應商 HAVING COUNT(*) > 1 ORDER BY 供應商 圖7-39 範例7-27過程結果(6) 80 /80