1 / 72

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

第七章 結構化查詢語言SQL( 二 ) 資料庫系統理論與實務 [ 邏輯思維系列 ]. 7-1 簡介. 本章是延續前一章之 『 資料操作語言 』 (Data Manipulation Language ，簡稱 DML) 中的異動操作 (Insert 、 Delete 和 Update) 以及查詢 (Select) 語法. 新增 INSERT 之進階語法. 【 範例 7-1】. 【 範例 7-1】 從 『 客戶 』 中挑選出公司名稱為 『 日盛金樓 』 的客戶資料，新增至 『 供應商 』 資料表內，並將供應編號編為 S0006 【 說明 】

candice-hebert
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

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

  3. 新增INSERT之進階語法 3 /80

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

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

  6. 刪除DELETE之進階語法 6 /80

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

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

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

  10. 更新UPDATE之進階語法 10 /80

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

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

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

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

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

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

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

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

  19. 【範例7-7】內部合併的另一語法，使用JOIN • 【範例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來達成此合併動作。 續下頁 19 /80

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

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

  22. 圖7-8 範例7-8 之結果 22 /80

  23. 【範例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 員工.員工編號, 訂單.訂單編號, 產品編號 續下頁 23 /80

  24. 圖7-9 範例7-9 之結果 24 /80

  25. 【範例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 員工.員工編號, 訂單.訂單編號 續下頁 25 /80

  26. 圖7-10 範例7-10 之結果 26 /80

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

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

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

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

  31. 圖7-11 範例7-11 之結果 31 /80

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

More Related