Transcript Ch07 結構化查詢語言SQL(二)

資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
第七章 結構化查詢語言SQL(二)
資料庫系統理論與實務 [邏輯思維系列]
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
7-1 簡介
• 本章是延續前一章之『資料操作語言』
(Data Manipulation Language，簡稱DML)中
的異動操作(Insert、Delete和Update)以及查
詢(Select)語法
第七章 結構化查詢語言SQL(二)
2 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
新增INSERT之進階語法
陳
祥
輝
著
第七章 結構化查詢語言SQL(二)
3 /80
©2007版權所有，翻印必究
【範例7-1】
資
料
庫
系
統
理
論
與
實
務
• 【範例7-1】
– 從『客戶』中挑選出公司名稱為『日盛金樓』的客戶資料，
新增至『供應商』資料表內，並將供應編號編為S0006
• 【說明】
陳
祥
輝
著
– 此範例是利用DML中的SELECT語法，從『客戶』資料表
中挑選出所要的資料，新增至『供應商』資料表內，如圖
7-1所示。
續
下
頁
第七章 結構化查詢語言SQL(二)
4 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
• 【語法】
– INSERT INTO 供應商
(供應商編號, 供應商, 聯絡人, 聯絡人職稱, 聯絡人性別,
郵遞區號,
地址, 電話)
SELECT ‘S0006’, 公司名稱, 聯絡人, 聯絡人職稱, 聯絡人性別,
郵遞區號, 地址, 電話
FROM 客戶
WHERE 公司名稱=’日盛金樓’
供應商
日盛金樓
圖7-1 範例7-1之語意示意圖
INSERT …
第七章 結構化查詢語言SQL(二)
客戶
5 /80
SELECT …
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
刪除DELETE之進階語法
陳
祥
輝
著
第七章 結構化查詢語言SQL(二)
6 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
【範例7-2】
陳
祥
輝
著
• 【說明】
• 【範例7-2】
– 刪除客戶的公司名稱為『丁泉』的所有『訂單』
和『訂單明細』資料。
– 此範例必須先刪除『訂單明細』資料表內的相
關資料，再刪除『訂單』資料表內的相關資料，
並透過參考相關的資料表，尤其是『客戶』資
料表中的公司名稱為『丁泉』。如圖7-2之示意
圖
續
下
頁
第七章 結構化查詢語言SQL(二)
7 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
• 【語法】
– DELETE FROM 訂單明細
FROM 訂單, 客戶
WHERE 訂單.客戶編號 = 客戶.客戶編號 AND
訂單.訂單編號 = 訂單明細.訂單編號 AND
公司名稱 = '丁泉'
訂單明細
客戶
丁泉
圖7-2 範例7-2之語意示意圖
被刪除的
部份
訂單
(a) 刪除『訂單明細』
第七章 結構化查詢語言SQL(二)
8 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
• 【語法】
– DELETE FROM 訂單
FROM 客戶
WHERE 訂單.客戶編號 = 客戶.客戶編號 AND
公司名稱 = '丁泉'
陳
祥
輝
著
客戶
被刪除的
部份
丁泉
訂單
圖7-2 範例7-2之語意示意圖
(b) 刪除『訂單』
第七章 結構化查詢語言SQL(二)
9 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
更新UPDATE之進階語法
陳
祥
輝
著
第七章 結構化查詢語言SQL(二)
10 /80
©2007版權所有，翻印必究
【範例7-3】
資
料
庫
系
統
理
論
與
實
務
• 【範例7-3】
– 『客戶』資料表中，公司名稱為『丁泉』訂單的付款方式，
全部改成現金。
• 【說明】
陳
祥
輝
著
續
– 由於『訂單』資料表中，只有客戶編號的屬性，並沒有客
戶的公司名稱屬性，所以必須要參考『客戶』資料表才能
得知哪些訂單是『丁泉』公司所下的訂單，故以合併方式
來更新訂單中的付款方式，如圖7-3之示意圖。
– 但由於兩資料表有具有相同屬性名稱客戶編號，為避免資
料庫管理系統會產生混淆，所以必須使用『點表示法』
(Dot Notation)，也就是在相同屬性的屬性名稱之前加上該
資料表的名稱，並於中間加上一個『點』
下
頁
第七章 結構化查詢語言SQL(二)
11 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
• 【語法】
– UPDATE 訂單
SET 付款方式 = ‘現金’
FROM 客戶
WHERE 訂單.客戶編號 = 客戶.客戶編號 AND
公司名稱 = ‘丁泉’
客戶
被更新的部份
(付款方式)
丁泉
訂單
第七章 結構化查詢語言SQL(二)
12 /80
©2007版權所有，翻印必究
【範例7-4】
資
料
庫
系
統
理
論
與
實
務
• 【範例7-4】
– 將『供應商』資料表和『客戶』資料表中，公
司名稱相同的地址，依據『客戶』資料表中的
地址，更新至『供應商』。
陳
祥
輝
著
• 【說明】
– 此範例是以客戶資料表的地址來更新供應商資
料表中的地址，如圖7-4之示意圖，依據『客戶』
中的地址更新『供應商』中的地址。
續
下
頁
第七章 結構化查詢語言SQL(二)
13 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
• 【語法】
– UPDATE 供應商
SET 供應商.地址 = 客戶.地址
FROM 客戶
WHERE 供應商.供應商 = 客戶.公司名稱
客戶
圖7-3 範例7-3之語意示意圖
第七章 結構化查詢語言SQL(二)
依據『客戶』
中的地址更新
『供應商』中
的地址
14 /80
被更新的
部份(地址)
供應商
©2007版權所有，翻印必究
【範例7-5】內部合併的另一語法，使用JOIN
資
料
庫
系
統
理
論
與
實
務
• 【範例7-5】內部合併的另一語法，使用JOIN
– 同【範例6-19】查詢出每一位員工所承接的訂單資料，輸
出屬性包括員工編號、姓名、訂單編號、訂貨日期，並依
員工編號和訂單編號遞增排序。改寫成另一語法
tableName1 INNER JOIN tableName2 ON …
陳
祥
輝
著
• 【說明】
– 使用此JOIN的語法要特別注意到JOIN的過程，如圖7-5代
表【範例6-19】的合併過程，此範例只有兩個資料表(員
工和訂單)，就直接合併。
續
下
頁
第七章 結構化查詢語言SQL(二)
15 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
• 【語法】
– SELECT 員工.員工編號, 姓名, 訂單編號, 訂貨日期
FROM (員工 INNER JOIN 訂單 ON 員工.員工編號=訂單.員工編號)
ORDER BY 員工.員工編號, 訂單編號
JOIN
陳
祥
輝
著
第七章 結構化查詢語言SQL(二)
16 /80
©2007版權所有，翻印必究
【範例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來達成此合併動作。
續
下
頁
第七章 結構化查詢語言SQL(二)
17 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
• 【語法】
– SELECT 員工.員工編號, 姓名, 訂單.訂單編號, 訂貨日期, 產品編號
FROM (員工 INNER JOIN 訂單 ON 員工.員工編號=訂單.員工編號)
INNER JOIN 訂單明細 ON 訂單.訂單編號=訂單明細.訂單編號
ORDER BY 員工.員工編號, 訂單.訂單編號, 產品編號
(1) JOIN
(2) JOIN
陳
祥
輝
著
第七章 結構化查詢語言SQL(二)
18 /80
©2007版權所有，翻印必究
【範例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【範例621】的合併過程，此範例共需要到四個資料表(員工、訂單、訂
單明細以及產品資料)，所以先將『員工』資料表和『訂單』資
料表合併後，當成一個虛擬資料表，再與『訂單明細』資料表
合併，再當成另一虛擬資料表，再與『產品資料』合併。
– 在上述中是以分解合併說明，但在實作上，卻是透過一個敘述
(statement)中，包括多次的INNER JOIN來達成此合併動作。
下
頁
第七章 結構化查詢語言SQL(二)
19 /80
©2007版權所有，翻印必究
• 【語法】
資
料
庫
系
統
理
論
與
實
務
– SELECT 員工.員工編號, 姓名, 訂單.訂單編號, 訂貨日期, 產品名稱
FROM ((員工 INNER JOIN 訂單 ON 員工.員工編號=訂單.員工編號)
INNER JOIN 訂單明細 ON 訂單.訂單編號=訂單明細.訂單編號)
INNER JOIN 產品資料 ON 訂單明細.產品編號=產品資料.產品編號
ORDER BY 員工.員工編號, 訂單.訂單編號
(1) JOIN
(2) JOIN
陳
祥
輝
著
(3) JOIN
第七章 結構化查詢語言SQL(二)
20 /80
©2007版權所有，翻印必究
【範例7-8】外部合併
資
料
庫
系
統
理
論
與
實
務
• 【範例7-8】外部合併
– 請依【範例7-5】改寫成外部合併，也就是不論員工是否
有承接訂單皆必須顯示出來。
• 【說明】
陳
祥
輝
著
– 如同【範例7-5】但所使用的語法為
leftTableName LEFT OUTER JOIN rightTableName ON …
• 【語法】
– SELECT 員工.員工編號, 姓名, 訂單編號, 訂貨日期
FROM (員工 LEFT OUTER JOIN 訂單 ON
員工.員工編號=訂單.員工編號)
ORDER BY 員工.員工編號, 訂單編號
續
下
頁
第七章 結構化查詢語言SQL(二)
21 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
圖7-8 範例7-8 之結果
第七章 結構化查詢語言SQL(二)
22 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
• 【範例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 員工.員工編號, 訂單.訂單編號, 產品編號
下
頁
第七章 結構化查詢語言SQL(二)
23 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
圖7-9 範例7-9 之結果
第七章 結構化查詢語言SQL(二)
24 /80
©2007版權所有，翻印必究
【範例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 員工.員工編號, 訂單.訂單編號
下
頁
第七章 結構化查詢語言SQL(二)
25 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
圖7-10 範例7-10 之結果
第七章 結構化查詢語言SQL(二)
26 /80
©2007版權所有，翻印必究
【範例7-11】合併處理與條件篩選
資
料
庫
系
統
理
論
與
實
務
• 【範例7-11】合併處理與條件篩選
– 請依【範例6-21】的情形，再將輸出的資料做一篩選，僅要查詢
出員工之姓名為”劉逸萍”的相關訂單資料，如同【範例6-21】
的輸出屬性。
• 【說明】
陳
祥
輝
著
– 此範例將以兩種語法來表示出此語法，而此範例的主要目的是
將語法中的合併操作與一般的條件篩選分離，以協助初學者容
易記憶生硬的語法和增加熟悉度。
– 可將以下語法(一)中，在WHERE後面的條件解釋如下
續
下
頁
第七章 結構化查詢語言SQL(二)
27 /80
©2007版權所有，翻印必究
【範例7-11】合併處理與條件篩選
資
料
庫
系
統
理
論
與
實
務
1. 『員工.員工編號 = 訂單.員工編號』：
表示『員工』和『訂單』資料表之間的關聯性
2. 『訂單.訂單編號 = 訂單明細.訂單編號』：
表示『訂單』和『訂單明細』資料表之間的關聯性
3. 『訂單明細.產品編號 = 產品資料.產品編號』：
表示『訂單明細』和『產品資料』資料表之間的關
聯性
4. 『姓名 = ‘劉逸萍’』：
為一般紀錄的條件篩選
陳
祥
輝
著
由以上1~3的關聯性而言，可視為員工、訂單、訂
單明細和產品資料等四個資料表的合併關係。
續
下
頁
第七章 結構化查詢語言SQL(二)
28 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
• 【語法】
– (語法一)
陳
祥
輝
著
合併
條件篩選
SELECT 員工.員工編號, 姓名, 訂單.訂單編號, 訂貨日期, 產品名稱
FROM 員工, 訂單, 訂單明細, 產品資料
WHERE 員工.員工編號 = 訂單.員工編號 AND
訂單.訂單編號 = 訂單明細.訂單編號 AND
訂單明細.產品編號 = 產品資料.產品編號 AND
姓名 = ‘劉逸萍’
ORDER BY 員工.員工編號, 訂單.訂單編號
續
下
頁
第七章 結構化查詢語言SQL(二)
29 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
• 【語法】
– (語法二)
陳
祥
輝
著
合併
條件篩選
SELECT 員工.員工編號, 姓名, 訂單.訂單編號, 訂貨日期, 產品名稱
FROM ((員工 INNER JOIN 訂單 ON 員工.員工編號=訂單.員工編號)
INNER JOIN 訂單明細 ON
訂單.訂單編號=訂單明細.訂單編號)
INNER JOIN 產品資料 ON
訂單明細.產品編號=產品資料.產品編號
WHERE 姓名 = ‘劉逸萍’
ORDER BY 員工.員工編號, 訂單.訂單編號
續
下
頁
第七章 結構化查詢語言SQL(二)
30 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
圖7-11 範例7-11 之結果
第七章 結構化查詢語言SQL(二)
31 /80
©2007版權所有，翻印必究
【範例7-12】使用子字串的比對方式
資
料
庫
系
統
理
論
與
實
務
• 【範例7-12】使用子字串的比對方式
– 請查詢員工資料表中，員工地址住在台北(縣)市，以及員工編號
中，第三碼數字為1的員工相關資料，包括員工編號、姓名、職
稱和地址。
• 【說明】
– 此範例中所使用到的比對關係並非使用完全比對，而是比對字
串中的部份字串，如第一個條件中，限制員工地址住在台北縣
或台北市的員工，所以在WHERE條件中，可以使用LIKE來做部
份比對，所使用的是萬用字元的方式，而此條件可以使用”%”
來比對，”%”所代表的是任何長度（任意個字元數）的任何字
元，所以在此限制可使用
地址 LIKE ‘台北%’
– 另一條件卻規定了員工編號的第三碼為1，此條件已限制了1的
前面有兩個固定字元數，所以此條件可使用”_”來比對，”_”所
代表的是單一個長度（單一個字元）的任何字元，所以在此限
制可使用
員工編號 LIKE ‘_ _ 1 _ _ _ _’
陳
祥
輝
著
續
• 不同資料庫管理系統的產品，所使用的萬用字元會有所不同
下
頁
第七章 結構化查詢語言SQL(二)
32 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
• 【語法】
– SELECT 員工編號, 姓名, 職稱, 地址
FROM 員工
WHERE 地址 LIKE ‘台北%’ AND
員工編號 LIKE ‘_ _1 _ _ _ _’
圖7-12 範例7-12 之結果
第七章 結構化查詢語言SQL(二)
33 /80
©2007版權所有，翻印必究
【範例7-13】使用屬於關係篩選資料
資
料
庫
系
統
理
論
與
實
務
• 【範例7-13】使用屬於關係篩選資料
– 查詢出有哪些的訂單中有產品編號為1,6,10中的任一項，
只要輸出訂單編號，並依此編號遞增排序，以及將重複資
料只顯示一筆。
陳
祥
輝
著
• 【說明】
續
– 此查詢可使用最前述的邏輯比較(OR)方式，如下
產品編號=1 OR 產品編號=6 OR 產品編號=10
完整語法如(語法一)。
– 但此處將使用另一種表答方式，也就是集合論中的屬於關
係，表示方式如下所示
產品編號 IN (1, 6, 10)
完整語法如(語法二)。
下
頁
第七章 結構化查詢語言SQL(二)
34 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
• 【語法】
– (語法一)
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 之結果
第七章 結構化查詢語言SQL(二)
35 /80
©2007版權所有，翻印必究
【範例7-14】具有『子查詢』的查詢 –
『獨立子查詢』
資
料
庫
系
統
理
論
與
實
務
• 【範例7-14】具有『子查詢』的查詢 – 『獨立子查詢』
– 查詢出在客戶資料表中，也具有供應商身份的資料，輸出屬性
包括客戶的客戶編號、公司名稱，並依客戶編號遞增排序。
• 【說明】
– 在此範例所要查詢的資料，如同是『客戶』和『供應商』資料
表兩邊皆有的共同資料，所以可以使用前述的『INNER JOIN』
的方式來合併出兩邊共同的紀錄，如同(語法一)的寫法。
– 另外，亦可使用子查詢的方式，也就是先將供應商的供應商屬
性值皆挑選出來形成一個集合，再用IN的屬於關係查詢出客戶
的公司名稱有在供應商集合中的資料，如同(語法二)的寫法。
– 在此查詢中使用到兩種寫法，在(語法二)的寫法中，使用到了查
詢中亦包含查詢，在內部的查詢即稱為『子查詢』，而此處的
子查詢又可獨立地被執行，並查詢出供應商的相關資料，所以
也稱之為『獨立子查詢』。
陳
祥
輝
著
續
下
頁
第七章 結構化查詢語言SQL(二)
36 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
• 【語法】
– (語法一)
SELECT 客戶編號, 公司名稱
FROM 客戶, 供應商
WHERE 公司名稱 = 供應商
ORDER BY 客戶編號
圖7-14 範例7-14之結果
– (語法二)
SELECT 客戶編號, 公司名稱
FROM 客戶
WHERE 公司名稱 IN ( SELECT 供應商 FROM 供應商)
ORDER BY 客戶編號
第七章 結構化查詢語言SQL(二)
37 /80
©2007版權所有，翻印必究
【範例7-15】具有『子查詢』的查詢 –
『相依子查詢』
資
料
庫
系
統
理
論
與
實
務
• 【範例7-15】具有『子查詢』的查詢 – 『相依子查詢』
– 查詢出在所有員工資料表中，有承接訂單的員工資料，輸出資
料包括員工編號和姓名，並以員工編號排序。
• 【說明】
陳
祥
輝
著
– 以此範例所有表示的語法，可分為兩種語法來思維，如(語法一)
是先將兩個資料表先進行合併處理(INNER JOIN)，也就是合併
出兩者共同的紀錄，再選取出員工編號和姓名，如圖7-15(a)。
– 另一種語法，如(語法二)採用子查詢方式，先將兩者資料表合併
後，選出兩者共同的員工編號，再選取出在此員工編號集合中
的員工編號和姓名，如圖7-15(b)。
續
下
頁
第七章 結構化查詢語言SQL(二)
38 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
• 【語法】
– (語法一)
SELECT DISTINCT E.員工編號, 姓名
FROM 員工 AS E , 訂單 AS O
WHERE E.員工編號 = O.員工編號
ORDER BY E.員工編號
員工
姓
名
員
工
編
號
合併後的結果，
再選取虛線內
的員工編號和
姓名
第七章 結構化查詢語言SQL(二)
39 /80
訂單
(a)
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
• 【語法】
– (語法二)
SELECT 員工編號, 姓名
FROM 員工
WHERE 員工編號 IN ( SELECT 員工編號
FROM 訂單
WHERE 員工編號 = 員工.員工編號)
ORDER BY 員工編號
員工
姓
名
存在此處的員工
編號，再選取此
處的員工編號和
姓名
第七章 結構化查詢語言SQL(二)
40 /80
員
工
編
號
訂單
(b)
©2007版權所有，翻印必究
【範例7-16】存在性的測試EXISTS
資
料
庫
系
統
理
論
與
實
務
• 【範例7-16】存在性的測試EXISTS
– 題目如【範例7-15】
• 【說明】
陳
祥
輝
著
– 如語法中所表示的是，選取出員工編號和姓名，
存在於兩者資料表的合併(INNER JOIN)結果中
的相關資料。
續
下
頁
第七章 結構化查詢語言SQL(二)
41 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
• 【語法】
– SELECT 員工編號, 姓名
FROM 員工
WHERE EXISTS ( SELECT * FROM 訂單
WHERE 員工編號 = 員工.員工編號)
ORDER BY 員工編號
員工
姓
名
『存在』於合併後
員
工
編
號
訂單
的結果之員工，再
從虛線框中選取出
員工編號和姓名
第七章 結構化查詢語言SQL(二)
42 /80
©2007版權所有，翻印必究
【範例7-17】NOT
資
料
庫
系
統
理
論
與
實
務
• 【範例7-17】NOT
– 題目如【範例715】，但所要查詢的輸出資料剛好完全相反，也
就是在所有員工資料表中，沒有承接任何一筆訂單的員工資料，
輸出資料包括員工編號和姓名，並以員工編號排序。
• 【說明】
陳
祥
輝
著
續
– 此範例使用了兩種語法來表示，(語法一)是使用相依子查詢的方
式，先將兩個資料表做INNER JOIN的結果，並挑選出員工編號，
而此處員工編號的集合是屬於有承接訂單的員工；反之，要查
詢出沒有承接任何一筆訂單之員工資料，應該是挑選出不屬於
此集合的員工資料，也就是圖中所標示的『目標區域』，如圖718(a)所示。
– (語法二)是使用存在性測試的方式，先找出存在於員工和訂單資
料表之間INNER JOIN後之虛擬資料表，再找出不屬於此資料表
中之員工資料，亦就是其他不屬於此處的員工資料，也就是圖
中所標示的『目標區域』，如圖7-18(b)所示。
下
頁
第七章 結構化查詢語言SQL(二)
43 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
• 【語法】
– (語法一)
SELECT 員工編號, 姓名
FROM 員工
WHERE 員工編號 NOT IN ( SELECT 員工編號 FROM 訂單
WHERE 員工編號 = 員工.員工編號)
ORDER BY 員工編號
目標
陳
祥
輝
著
員工
續
下
頁
第七章 結構化查詢語言SQL(二)
選取出不屬於
此集合中的員
工編號，也就
是在目標區域
中的員工資料
44 /80
區域
訂單
(a)
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
• 【語法】
– (語法二)
SELECT 員工編號, 姓名
FROM 員工
WHERE NOT EXISTS ( SELECT * FROM 訂單
WHERE 員工編號 = 員工.員工編號)
ORDER BY 員工編號
目標
員工
選取虛線外的員
工編號和姓名，
亦就是在目標區
域內的資料
第七章 結構化查詢語言SQL(二)
45 /80
區域
訂單
(b)
©2007版權所有，翻印必究
【範例7-18】自我合併(Self-Join)-INNER
JOIN查詢
資
料
庫
系
統
理
論
與
實
務
• 【範例7-18】自我合併(Self-Join)-INNER JOIN查詢
– 從員工資料表中查詢出有主管的員工和所屬主管資料，輸
出屬性包括員工編號、員工姓名、主管編號和主管姓名，
並依員工編號遞增排序。
陳
祥
輝
著
• 【說明】
續
– 在員工資料表中，由於屬性『報告人』是參考『員工編
號』，此種關係可稱為自我參考，但在實作上並無法僅使
用一個資料表來表達和實作，所以必須使用兩個員工資料
表，並扮演兩個不同的角色，如圖7-20所示，可透過別名
的方式，將其一扮演『職員』，另一扮演成『主管』，此
處形同兩個完全不相同的資料表來看待，在實作上，亦當
成兩個獨立資料表來處理。
下
頁
第七章 結構化查詢語言SQL(二)
46 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
職員
主管
陳
祥
輝
著
一個員工資
料表同時扮
演職員和主
管兩個角色
第七章 結構化查詢語言SQL(二)
47 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
• 【語法】
– (語法一)
SELECT 職員.員工編號, 職員.姓名 AS 員工姓名,
主管.員工編號 AS 主管編號, 主管.姓名 AS 主管姓名
FROM 員工 AS 職員, 員工 AS 主管
WHERE 職員.報告人=主管.員工編號
ORDER BY職員.員工編號
– (語法二)
SELECT 職員.員工編號, 職員.姓名 AS 員工姓名,
主管.員工編號 AS 主管編號, 主管.姓名 AS 主管姓名
FROM 員工 AS 職員 INNER JOIN 員工 AS 主管 ON
職員.報告人=主管.員工編號
ORDER BY 職員.員工編號
第七章 結構化查詢語言SQL(二)
48 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
圖7-21 範例7-18 之結果
第七章 結構化查詢語言SQL(二)
49 /80
©2007版權所有，翻印必究
【範例7-19】自我合併(Self-Join)OUTER JOIN查詢
資
料
庫
系
統
理
論
與
實
務
• 【範例7-19】自我合併(Self-Join)-OUTER JOIN查詢
– 此範例的條件類似【範例7-18】，但從員工資料表是要查詢出所
有員工和所屬主管資料，輸出屬性包括員工編號、員工姓名、
主管編號和主管姓名。
• 【說明】
陳
祥
輝
著
– 如同【範例7-18】之說明，唯此範例必須使用外部合併，方能將
所有員工和所屬主管的資料查詢出。
續
下
頁
第七章 結構化查詢語言SQL(二)
50 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
• 【語法】
– SELECT 職員.員工編號, 職員.姓名 AS 員工姓名,
主管.員工編號 AS 主管編號, 主管.姓名 AS 主管姓名
FROM 員工 AS 職員 LEFT OUTER JOIN 員工 AS 主管 ON
職員.報告人=主管.員工編號
ORDER BY 職員.員工編號
圖7-22 範例7-19之結果
第七章 結構化查詢語言SQL(二)
51 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
7-4 聚合函數
•
•
•
•
COUNT()：依據輸入的項目，計算出總筆數。
SUM()：依據輸入的項目，將其加總。
MAX()：依據輸入的項目，找出最大值的項目。
MIN()：依據輸入的項目，找出最小值的項目。
• AVERAGE()：依據輸入的項目，計算出平均值。
零至多個
輸入參數
一個輸
出值
計算
第七章 結構化查詢語言SQL(二)
52 /80
©2007版權所有，翻印必究
【範例7-20】利用聚合函數COUNT計算
筆數的查詢
資
料
庫
系
統
理
論
與
實
務
• 【範例7-20】利用聚合函數COUNT計算筆數的查詢
– 請計算出員工總人數。
• 【說明】
– 此範例主要是計算出『員工』資料表內的總筆數，因此使用聚
合函數中的COUNT函數。以下的兩種語法個別說明如下
– (語法一)
使用COUNT(*)，在聚合函數內的輸入為星號*，表示萬用字元，
也就是指所有的屬性，意指所有屬性的屬性值皆非空值(Not Null
Value)者，將會計數一次。
陳
祥
輝
著
• 【語法】
– (語法一)
SELECT COUNT(*) AS 員工總人數
FROM 員工
圖7-24 範例7-20之結果
續
下
頁
第七章 結構化查詢語言SQL(二)
53 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
• 【說明】
– (語法二)
使用COUNT(員工編號)，在聚合函數內的輸入為『員工編號』，
意指計算出員工編號非空值(Not Null Value)的數量。由於此處所
使用的『員工編號』為員工資料表內的主要鍵(Primary Key)，不
可能為空值，故如此的寫法將不會有任何的問題發生。倘若寫
成COUNT(職稱)，可能將會造成問題，因為『職稱』的屬性，
在此資料表內並未限制為不可為空值，如果有員工的職稱剛好
為空值時，該筆記錄將不會被計數，而造成筆數上的不正確。
• 【語法】
– (語法二)
SELECT COUNT(員工編號) AS 員工總人數
FROM 員工
圖7-24 範例7-20之結果
第七章 結構化查詢語言SQL(二)
54 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
【範例7-21】利用聚合函數COUNT計算
筆數的查詢
• 【範例7-21】利用聚合函數COUNT計算筆數的查詢
– 請分別計算員工中，男性員工和女性員工的個別總人數。
• 【說明】
– 此範例可以使用WHERE，先過濾出符合條件的資料，再進行
COUNT的計算，所以分別計算兩次，如下。
• 【語法】
– SELECT COUNT(*) AS 男性員工總人數
FROM 員工
WHERE 性別 = ‘男’
– SELECT COUNT(*) AS 女性員工總人數
FROM 員工
WHERE 性別 = ‘女’
第七章 結構化查詢語言SQL(二)
55 /80
圖7-25範例7-21之結果
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
【範例7-22】利用聚合函數COUNT以及分群組
(GROUP BY) 方式，計算出不同群組的筆數查詢
• 【範例7-22】利用聚合函數COUNT以及分群組 (GROUP BY)
方式，計算出不同群組的筆數查詢
– 請分別計算員工中，男性員工和女性員工的個別總人數。
• 【說明】
– 此範例雖與【範例7-21】相同，但所使用的語法會有所不同，也
就是加入群組的概念，並簡化繁重和複雜的計算。
• 【語法】
– SELECT 性別, COUNT(*) AS 人數
FROM 員工
GROUP BY 性別
圖7-26範例7-22之結果
第七章 結構化查詢語言SQL(二)
56 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
【範例7-23】Group By – COUNT()
• 【範例7-23】Group By – COUNT()
– 計算出有承接訂單的每位員工所承接訂單的筆數。
• 【說明】
– 本範例中有承接訂單的每位員工表示要使用INNER JOIN來挑選
出員工的相關訂單資料，又因為條件中所要查詢的資料為員工
承接訂單的筆數，所以可以使用GROUP BY 的語法，並使用
COUNT函數來計算，
– 以下使用兩個語法，其中的差異只在於COUNT()函數內是使用
萬用字元”*”和員工編號。
員工
合併
reference
合併後再以員工編號為
群組之依據計算筆數
員工
訂單
訂單
第七章 結構化查詢語言SQL(二)
57 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
• 【語法】
– (語法一)
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 之結果
第七章 結構化查詢語言SQL(二)
58 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
【範例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 之結果
第七章 結構化查詢語言SQL(二)
59 /80
©2007版權所有，翻印必究
【範例7-25】Group By … Having
資
料
庫
系
統
理
論
與
實
務
• 【範例7-25】Group By … Having
– 題目如【範例7-23】，但只挑選出訂單筆數累計有超過三筆的員
工資料。
• 【說明】
陳
祥
輝
著
– 此範例主要是針對GROUP BY的計算之後，再將聚合函數所計
算出的資料做條件篩選，如圖7-30所示。
員工
reference
合併
合併後再以員工編號為
群組之依據計算筆數
員工
對聚合函
數結果做
條件篩選
訂單
訂單
第七章 結構化查詢語言SQL(二)
60 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
• 【語法】
– (語法一)
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 之結果
第七章 結構化查詢語言SQL(二)
61 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
【範例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.訂單編號
第七章 結構化查詢語言SQL(二)
62 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
【範例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 之結果
第七章 結構化查詢語言SQL(二)
63 /80
©2007版權所有，翻印必究
7-5查詢語法SELECT之剖析整理
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
SELECT FROM WHERE GROUP BY HAVING ORDER BY
(1)
資料
來源
查詢
結果
(2)
(3)
(4)
(5)
(6)
第七章 結構化查詢語言SQL(二)
64 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
【範例7-27】執行過程
• 【範例7-27】執行過程
– 本範例最後的目的是要計算出，提供產品數量
大於一個以上的供應商所提供的產品數量，並
將輸出結果依據供應商排序。將語法的撰寫過
程分為以下六個演進過程，用以說明查詢語法
的運作過程。
第七章 結構化查詢語言SQL(二)
65 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
【範例7-27】執行過程
1. 『供應商』與『產品資料』的『卡式積』。
2. 對第1項卡式積的結果，再做『投影操作』(PROJECT
Operation)，也就是挑選數個屬性。
3. 利用WHERE條件，挑選出『供應商的供應商編號』與
『產品資料的供應商編號』的屬性值相等者，所以演進
至此，可以當成是以第2項的結果再做『選取操作』
(SELECT Operation)，亦也可以當成是做供應商與產品資
料的『內部合併』。
4. 依據第3項內部合併的結過進行『分群組』(GROUP BY)
以及聚合函數COUNT(*)的計算，也就是利用GROUP BY
和COUNT(*)函數。
5. 針對第4項所計算出來的COUNT(*)的結果再篩選數量大
於一筆以上的供應商資料，也就是利用HAVING。
6. 針對第5項的結果再進行排序，也就是利用ORDER BY。
第七章 結構化查詢語言SQL(二)
66 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
• 【過程語法1】
– SELECT *
FROM 供應商, 產品資料
陳
祥
輝
著
續
圖7-34 範例7-27過程結果(1)
下
頁
第七章 結構化查詢語言SQL(二)
67 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
• 【過程語法2】
– SELECT 供應商.供應商編號, 供應商,
產品編號, 產品名稱
FROM 供應商, 產品資料
陳
祥
輝
著
續
圖7-35 範例7-27過程結果(2)
下
頁
第七章 結構化查詢語言SQL(二)
68 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
• 【過程語法3】
– SELECT 供應商.供應商編號, 供應商, 產品編號, 產品名稱
FROM 供應商, 產品資料
WHERE 供應商.供應商編號 = 產品資料.供應商編號
陳
祥
輝
著
續
圖7-36 範例7-27過程結果(3)
下
頁
第七章 結構化查詢語言SQL(二)
69 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
• 【過程語法4】
– SELECT 供應商.供應商編號, 供應商, COUNT(*) AS 數量
FROM 供應商, 產品資料
WHERE 供應商.供應商編號 = 產品資料.供應商編號
GROUP BY 供應商.供應商編號, 供應商
陳
祥
輝
著
續
圖7-37 範例7-27過程結果(4)
下
頁
第七章 結構化查詢語言SQL(二)
70 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
• 【過程語法5】
– SELECT 供應商.供應商編號, 供應商, COUNT(*) AS 數量
FROM 供應商, 產品資料
WHERE 供應商.供應商編號 = 產品資料.供應商編號
GROUP BY 供應商.供應商編號, 供應商
HAVING COUNT(*) > 1
陳
祥
輝
著
續
圖7-38 範例7-27過程結果(5)
下
頁
第七章 結構化查詢語言SQL(二)
71 /80
©2007版權所有，翻印必究
資
料
庫
系
統
理
論
與
實
務
陳
祥
輝
著
• 【過程語法6】
– SELECT 供應商.供應商編號, 供應商, COUNT(*) AS 數量
FROM 供應商, 產品資料
WHERE 供應商.供應商編號 = 產品資料.供應商編號
GROUP BY 供應商.供應商編號, 供應商
HAVING COUNT(*) > 1
ORDER BY 供應商
圖7-39 範例7-27過程結果(6)
第七章 結構化查詢語言SQL(二)
72 /80
©2007版權所有，翻印必究