9 SELECT敘述的進階查詢 9-1 SQL的多資料表查詢 9-2 合併查詢 9-3 集合運算查詢 9-4 子查詢

Presentation on theme: "9 SELECT敘述的進階查詢 9-1 SQL的多資料表查詢 9-2 合併查詢 9-3 集合運算查詢 9-4 子查詢"— Presentation transcript:

1

2 9 SELECT敘述的進階查詢 9-1 SQL的多資料表查詢 9-2 合併查詢 9-3 集合運算查詢 9-4 子查詢
9-6 使用Management Studio設計SQL查詢

3 9-1 SQL的多資料表查詢 合併查詢（Join Query）：其主要目的是將正規化分割的資料表，還原成使用者習慣閱讀的資訊。因為正規化的目的是避免資料重複，擁有重複資料的資訊反而易於使用者閱讀和了解。 集合運算查詢（Set Operation Query）：SQL可以使用集合運算：聯集、交集或差集來執行兩個資料表的集合運算查詢。 子查詢（Subquery）：子查詢也屬於是一種多資料表的查詢，子查詢是指在SELECT指令（主查詢）中擁有其他SELECT指令（子查詢），也稱為巢狀查詢（Nested Query）。

4 9-2 合併查詢 9-2-1 合併查詢的種類 9-2-2 內部合併查詢 9-2-3 外部合併查詢 9-2-4 交叉合併查詢

5 9-2-1 合併查詢的種類-說明 合併查詢分為INNER、LEFT、RIGHT、FULL和CROSS JOIN的內部、外部和交叉合併查詢。
合併查詢是將儲存在多個資料表的欄位資料取出，使用合併條件合併成所需的查詢結果。 例如：【班級】資料表只有學號和教授編號，我們需要透過合併查詢，才能進一步取得學生和教授的相關資訊。 合併查詢通常是使用資料表間的關聯欄位來進行查詢，當然也可以不使用資料庫關聯性來建立合併查詢，這種關係稱為ad hoc關聯性。

6 9-2-1 合併查詢的種類- 內部合併查詢 內部合併查詢只取回多個資料表符合合併條件的記錄資料，即都存在合併欄位的記錄資料，如下圖所示：

7 9-2-1 合併查詢的種類- 外部合併查詢 外部合併查詢可以取回指定資料表的所有記錄，它和內部合併查詢的差異在於：查詢結果並不是兩個資料表都一定存在的記錄。 OUTER JOIN指令可以分成三種，如下所示： 左外部合併（LEFT JOIN） 右外部合併（RIGHT JOIN） 完全外部合併（FULL JOIN）

8 9-2-1 合併查詢的種類- 左外部合併查詢 左外部合併查詢（LEFT JOIN）：取回左邊資料表內的所有記錄，如下圖所示：

9 9-2-1 合併查詢的種類- 右外部合併查詢 右外部合併查詢（RIGHT JOIN）：取回右邊資料表內的所有記錄，如下圖所示：

10 9-2-1 合併查詢的種類- 完全外部合併 完全外部合併（FULL JOIN）：取回左、右邊資料表內的所有記錄，如下圖所示：

11 9-2-1 合併查詢的種類- 交叉合併查詢 交叉合併查詢就是關聯式代數的卡笛生乘積運算（Cartesian Product），其查詢結果的記錄數是兩個資料表記錄數的乘積。 交叉合併查詢是將一個資料表的每一筆記錄都和合併資料表的記錄合併成一筆新記錄，換句話說，如果兩個資料表的記錄數分別是5和4筆記錄，執行交叉合併查詢後的記錄數就是5 X 4 = 20筆記錄。

12 9-2-2 內部合併查詢-說明 內部合併（Inner Join）只取回合併資料表中符合合併條件的記錄資料，合併條件通常是使用資料庫關聯性的外來鍵。 我們可以使用明示語法或隱含語法來建立內部合併查詢。

13 9-2-2 內部合併查詢- 明示語法的內部合併查詢(語法)
在T-SQL語言建立明示語法的內部合併查詢是使用INNER JOIN指令，其基本語法如下所示： SELECT 欄位清單 FROM 資料表1 [INNER] JOIN 資料表2 ON 合併條件1 [ [INNER] JOIN 資料表3 ON 合併條件2]

14 9-2-2 內部合併查詢- 明示語法的內部合併查詢(範例1)
SQL指令碼檔：Ch9_2_2_01.sql 使用內部合併查詢從【學生】資料表取出學號與姓名欄位，【班級】資料表取出課程編號與教授編號欄位來顯示學生的上課資料，合併條件的欄位是學號，如下所示： SELECT 學生.學號,學生.姓名,班級.課程編號,班級.教授編號 FROM 學生 INNER JOIN 班級 ON 學生.學號 = 班級.學號

15 9-2-2 內部合併查詢- 明示語法的內部合併查詢(範例2)
SQL指令碼檔：Ch9_2_2_02.sql T-SQL語言可以擴充Ch9_2_2_01.sql的內部合併查詢，再次執行INNER JOIN合併查詢來取得【課程】資料表的詳細資料，如下所示： SELECT 學生.學號,學生.姓名,課程.*,班級.教授編號 FROM 課程 INNER JOIN (學生 INNER JOIN 班級 ON 學生.學號 = 班級.學號) ON 班級.課程編號 = 課程.課程編號

16 9-2-2 內部合併查詢- 明示語法的內部合併查詢(範例3)
SQL指令碼檔：Ch9_2_2_03.sql T-SQL語言可以再擴充Ch9_2_2_02.sql的內部合併查詢，再次INNER JOIN合併查詢【教授】資料表，以便取得教授的詳細資料，如下所示： SELECT 學生.學號, 學生.姓名, 課程.*, 教授.* FROM 教授 INNER JOIN (課程 INNER JOIN (學生 INNER JOIN 班級 ON 學生.學號 = 班級.學號) ON 班級.課程編號 = 課程.課程編號) ON 班級.教授編號 = 教授.教授編號

17 9-2-2 內部合併查詢- 隱含語法的內部合併查詢
隱含語法的內部合併查詢不需要INNER JOIN指令，只需在WHERE子句指定合併條件，也稱為自然合併查詢。 SQL指令碼檔：Ch9_2_2_04.sql 從【學生】資料表取出學號與姓名欄位，和在【班級】資料表取出課程編號與教授編號欄位來顯示學生的上課資料，合併條件是在WHERE子句指定，如下所示： SELECT 學生.學號,學生.姓名,班級.課程編號,班級.教授編號 FROM 學生, 班級 WHERE 學生.學號 = 班級.學號

18 9-2-2 內部合併查詢-相互關聯名稱(說明) 相互關聯名稱（Correlation Names）是在FROM子句指定資料表的暫時名稱，可以用來簡化複雜且容易混淆的欄位名稱。也稱為資料表別名（Table Alias）。

19 9-2-2 內部合併查詢-相互關聯名稱(語法) 如同欄位別名，資料表別名可以更清楚建立多資料表的合併查詢，其語法如下所示：
SELECT 欄位清單 FROM 資料表1 [AS] 別名1 [INNER] JOIN 資料表2 [AS] 別名2 ON 別名1.欄位名稱 運算子 別名2.欄位名稱 [[INNER] JOIN 資料表3 [AS] 別名3 ON 別名2.欄位名稱 運算子 別名3.欄位名稱]

20 9-2-2 內部合併查詢-相互關聯名稱(範例) SQL指令碼檔：Ch9_2_2_05.sql
使用內部合併查詢從【學生】資料表取出學號與姓名欄位，和從【班級】資料表取出課程編號與教授編號欄位來顯示學生的上課資料，合併條件是學號欄位，並且指定【班級】資料表的別名【上課】，如下所示： SELECT 學生.學號,學生.姓名,上課.課程編號,上課.教授編號 FROM 學生 INNER JOIN 班級 AS 上課 ON 學生.學號 = 上課.學號

21 9-2-2 內部合併查詢-自身合併查詢 (說明) 自身合併查詢（Self-join）屬於內部合併查詢的一種特殊情況，因為合併的資料表就是自己。因為自身合併查詢是合併本身的資料表，所以需要使用前述相互關聯名稱來指定資料表別名。 不只如此，自身合併查詢通常需要使用DISTINCT關鍵字來刪除重複欄位值的記錄資料。

22 9-2-2 內部合併查詢-自身合併查詢 (範例) SQL指令碼檔：Ch9_2_2_06.sql
9-2-2 內部合併查詢-自身合併查詢 (範例) SQL指令碼檔：Ch9_2_2_06.sql 使用自身合併查詢從【員工】資料表找出同一個城巿有其他員工存在的清單，如下所示： SELECT DISTINCT 員工.姓名, 員工.城市, 員工.街道 FROM 員工 INNER JOIN 員工 AS 員工1 ON ( 員工.城市 = 員工1.城市 AND 員工.身份證字號 <> 員工1.身份證字號 ) ORDER BY 員工.城市

23 9-2-3 外部合併查詢- LEFT JOIN左外部合併查詢(說明)
T-SQL語言的OUTER JOIN是外部合併查詢指令，可以取回指定資料表的所有記錄，其語法和INNER JOIN內部合併查詢相似，主要的差異在於：查詢結果並不是兩個資料表都一定存在的記錄。 左外部合併查詢是在合併的兩個資料表中，取回左邊資料表內的所有記錄，而不論是否在右邊資料表存在合併欄位值。

24 9-2-3 外部合併查詢- LEFT JOIN左外部合併查詢(範例)
SQL指令碼檔：Ch9_2_3_01.sql 使用左外部合併查詢來查詢【教授】和【員工】資料表，合併條件的欄位是身份證字號，可以顯示【教授】資料表的所有記錄，如下所示： SELECT 教授.教授編號, 員工.姓名, 教授.職稱, 員工.薪水 FROM 教授 LEFT JOIN 員工 ON 教授.身份證字號 = 員工.身份證字號

25 9-2-3 外部合併查詢- RIGHT JOIN右外部合併查詢(範例1)
右外部合併查詢可以取回右邊資料表內的所有記錄，而不論是否在左邊資料表存在合併欄位值。 SQL指令碼檔：Ch9_2_3_02.sql 使用右外部合併查詢來查詢【教授】和【員工】資料表，合併條件的欄位是身份證字號，可以顯示【員工】資料表的所有記錄，如下所示： SELECT 教授.教授編號, 員工.姓名, 教授.職稱, 員工.薪水 FROM 教授 RIGHT JOIN 員工 ON 教授.身份證字號 = 員工.身份證字號

26 9-2-3 外部合併查詢- RIGHT JOIN右外部合併查詢(範例2)
SQL指令碼檔：Ch9_2_3_03.sql 使用多種JOIN指令來合併【學生】、【課程】和【班級】資料表，如下所示： SELECT 學生.學號, 學生.姓名, 課程.*, 班級.教授編號 FROM 課程 RIGHT JOIN (學生 INNER JOIN 班級 ON 學生.學號 = 班級.學號) ON 班級.課程編號 = 課程.課程編號

27 9-2-3 外部合併查詢- FULL JOIN完全外部合併查詢
SQL指令碼檔：Ch9_2_3_04.sql 使用完全外部合併查詢來查詢【教授】和【員工】資料表，合併條件的欄位是身份證字號，可以顯示【教授】和【員工】資料表的所有記錄，如下所示： SELECT 教授.教授編號, 員工.姓名, 教授.職稱, 員工.薪水 FROM 教授 FULL JOIN 員工 ON 教授.身份證字號 = 員工.身份證字號

28 9-2-4 交叉合併查詢-範例1 交叉合併查詢的CROSS JOIN指令就是關聯式代數的卡笛生乘積運算（Cartesian Product），其查詢結果的記錄數是兩個資料表記錄數的乘積。 SQL指令碼檔：Ch9_2_4_01.sql 使用交叉合併查詢從【學生】資料表取出學號與姓名欄位，和【班級】資料表的課程編號與教授編號欄位，如下所示： SELECT 學生.學號,學生.姓名,班級.課程編號,班級.教授編號 FROM 學生 CROSS JOIN 班級

29 9-2-4 交叉合併查詢-範例2 SQL指令碼檔：Ch9_2_4_02.sql
使用交叉合併查詢配合WHERE子句，找出【學生】和【班級】資料表各位學生的上課記錄，條件是兩個資料表的學號相等，如下所示： SELECT 學生.學號, 學生.姓名, 班級.課程編號, 班級.教授編號 FROM 學生 CROSS JOIN 班級 WHERE 學生.學號 = 班級.學號

30 9-3 集合運算查詢 9-3-1 集合運算查詢的種類 9-3-2 UNION聯集查詢 9-3-3 INTERSECT交集查詢
9-3-4 EXCEPT差集查詢

31 9-3 集合運算查詢 在執行多資料表查詢時，除了可以使用INNER JOIN和OUTER JOIN執行合併查詢外，我們也可以使用集合運算：聯集、交集或差集來執行兩個資料表的集合運算查詢。 在T-SQL執行集合運算查詢的限制條件說明，如下所示： 兩個資料表的欄位數需相同。 資料表欄位的資料類型需要是相容型別。

32 9-3-1 集合運算查詢的種類-聯集UNION 聯集UNION：將兩個資料表的記錄都全部結合在一起，如果有重複記錄，只顯示其中一筆，如果加上ALL關鍵字，就會顯示所有重複的記錄，其基本語法如下所示： SELECT 欄位清單 FROM 資料表1 UNION [ALL] SELECT 欄位清單 FROM 資料表2 [UNION [ALL] SELECT 欄位清單 FROM 資料表3 ] [ORDER BY 欄位清單]

33 9-3-1 集合運算查詢的種類- 交集INTERSECT
SELECT 欄位清單 FROM 資料表1 INTERSECT SELECT 欄位清單 FROM 資料表2 [ORDER BY 欄位清單]

34 9-3-1 集合運算查詢的種類-差集EXCEPT
差集EXCEPT：只取出存在第1列SELECT指令的記錄，但是不存在第2列SELECT指令的記錄，其基本語法如下所示： SELECT 欄位清單 FROM 資料表1 EXCEPT SELECT 欄位清單 FROM 資料表2 [ORDER BY 欄位清單]

35 9-3-2 UNION聯集查詢 UNION聯集查詢指令可以將兩個資料表的記錄執行聯集運算，將所有記錄都顯示出來。
SQL指令碼檔：Ch9_3_2.sql 將【學生】和【員工】兩個資料表的【姓名】欄位，使用聯集運算取出所有學生和員工姓名，如下所示： SELECT 姓名 FROM 學生 UNION SELECT 姓名 FROM 員工

36 9-3-3 INTERSECT交集查詢 INTERSECT交集查詢指令可以從兩個資料表取出同時存在的記錄資料。
SQL指令碼檔：Ch9_3_3.sql 將【學生】和【員工】兩個資料表的【姓名】欄位使用交集運算取出存在兩個資料表的學生和員工姓名，如下所示： SELECT 姓名 FROM 學生 INTERSECT SELECT 姓名 FROM 員工

37 9-3-4 EXCEPT差集查詢 EXCEPT差集查詢指令可以取出存在其中一個資料表，而不存在另一個資料表的記錄資料。
SQL指令碼檔：Ch9_3_4.sql 將【學生】和【員工】兩個資料表的【姓名】欄位使用差集運算取出存在【學生】資料表，但不存在【員工】資料表的姓名資料，如下所示： SELECT 姓名 FROM 學生 EXCEPT SELECT 姓名 FROM 員工

38 9-4 子查詢 9-4-1 子查詢的基礎 9-4-2 比較運算子的子查詢 9-4-3 邏輯運算子的子查詢

39 9-4-1 子查詢的基礎-說明 子查詢（Subquery）也屬於多資料表的查詢，子查詢是指在SELECT指令中擁有其他SELECT指令，也稱為巢狀查詢（Nested Query）。 子查詢是附屬在SQL查詢指令，通常是位在主查詢SELECT指令的WHERE子句，以便透過子查詢取得所需的查詢條件。 事實上，子查詢本身也是一個SELECT指令，如果在SELECT指令擁有子查詢，首先處理的是子查詢，然後才依子查詢的條件來處理主查詢，以便取得最後的查詢結果。

40 9-4-1 子查詢的基礎-FROM子句的子查詢 在FROM子句也可以使用子查詢來取得暫存資料表，此時需要使用資料表別名來指定暫存資料表的名稱。 SQL指令碼檔：Ch9_4_1.sql 使用【員工】資料表的子查詢來建立FROM子句名為【高薪員工】的暫存資料表，然後顯示【高薪員工】資料表的記錄資料，如下所示： SELECT 高薪員工.姓名, 高薪員工.電話, 高薪員工.薪水 FROM (SELECT 身份證字號, 姓名, 電話, 薪水 FROM 員工 WHERE 薪水>50000) AS 高薪員工

41 9-4-1 子查詢的基礎- WHERE和HAVING子句的子查詢(語法)
子查詢最常是使用在SELECT指令的WHERE子句或HAVING子句，它是使用在搜尋條件的邏輯或比較運算子的運算式。子查詢的基本語法，如下所示： SELECT 欄位清單 FROM 資料表1 WHERE 欄位 = (SELECT 欄位 FROM 資料表2 WHERE 搜尋條件)

42 9-4-1 子查詢的基礎-WHERE和HAVING 子句的子查詢(注意事項)
子查詢是位在SQL指令的括號中。 通常子查詢的SELECT指令只會取得單一欄位值，以便與主查詢的欄位進行比較運算。 如果需要排序，主查詢可以使用ORDER BY子句，不過，子查詢並不能使用ORDER BY子句，只能使用GROUP BY子句來代替。 如果子查詢取得多筆記錄，此時在主查詢需使用IN邏輯運算子。 BETWEEN/AND邏輯運算子不能使用在主查詢，但可以使用在子查詢。

43 9-4-2 比較運算子的子查詢-範例1 在主查詢SELECT指令的WHERE子句可以使用子查詢來取得其他資料表記錄的欄位值，其主要目的是建立WHERE子句所需的條件運算式。 SQL指令碼檔：Ch9_4_2_01.sql 在【學生】資料表使用姓名欄位取得學號後，查詢【班級】資料表的學生陳會安共上幾門課，如下所示： SELECT COUNT(*) AS 上課數 FROM 班級 WHERE 學號 = (SELECT 學號 FROM 學生 WHERE 姓名='陳會安')

44 9-4-2 比較運算子的子查詢-範例2 SQL指令碼檔：Ch9_4_2_02.sql
在【員工】資料表找出員工薪水高於平均薪水的員工資料，如下所示： SELECT 身份證字號, 姓名, 電話, 薪水 FROM 員工 WHERE 薪水 >= (SELECT AVG(薪水) FROM 員工)

45 9-4-3 邏輯運算子的子查詢- EXISTS運算子(範例1)
在SELECT指令的WHERE子句可以使用EXISTS邏輯運算子檢查子查詢的結果是否有傳回資料。 SQL指令碼檔：Ch9_4_3_01.sql 在【學生】資料表顯示【班級】資料表有上CS222課程編號的學生資料，如下所示： SELECT * FROM 學生 WHERE EXISTS (SELECT * FROM 班級 WHERE 課程編號 = 'CS222' AND 學生.學號 = 班級.學號)

46 9-4-3 邏輯運算子的子查詢- EXISTS運算子(範例2)
SQL指令碼檔：Ch sql 從【班級】和【課程】資料表取出所有在221-S和100-M教室上課的課程資料，如下所示： SELECT * FROM 課程 WHERE EXISTS (SELECT * FROM 班級 WHERE (教室=‘221-S’ OR 教室=‘100-M’) AND 課程.課程編號=班級.課程編號)

47 9-4-3 邏輯運算子的子查詢- EXISTS運算子(範例3)
SQL指令碼檔：Ch9_4_3_03.sql 改用合併查詢來取得與Ch9_4_3_02.sql擁有相同的查詢結果，如下所示： SELECT DISTINCT 課程.* FROM 課程, 班級 WHERE (班級.教室='221-S' OR 班級.教室='100-M') AND 課程.課程編號=班級.課程編號

48 9-4-3 邏輯運算子的子查詢- IN運算子(範例1)
SELECT指令的WHERE子句可以使用IN邏輯運算子，檢查是否存在子查詢取得的記錄資料之中。 SQL指令碼檔：Ch9_4_3_04.sql 從【課程】和【班級】資料表取出學號S004沒有上的課程清單，如下所示： SELECT * FROM 課程 WHERE 課程編號 NOT IN (SELECT 課程編號 FROM 班級 WHERE 學號='S004')

49 9-4-3 邏輯運算子的子查詢- IN運算子(範例2)
SQL指令碼檔：Ch9_4_3_05.sql 使用三層巢狀查詢從【學生】、【班級】和【教授】資料表，找出學生【江小魚】上了哪些教授的哪些課程，如下所示： SELECT * FROM 教授 WHERE 教授編號 IN (SELECT 教授編號 FROM 班級 WHERE 學號=(SELECT 學號 FROM 學生 WHERE 姓名='江小魚'))

50 9-4-3 邏輯運算子的子查詢-ALL運算子 ALL運算子是指父查詢的條件需要滿足子查詢的所有結果。
SQL指令碼檔：Ch9_4_3_06.sql 使用子查詢取出【員工】資料表城巿是台北的薪水資料，然後在父查詢查詢所有薪水大於等於子查詢薪水的記錄資料，如下所示： SELECT 姓名, 薪水 FROM 員工 WHERE 薪水 >= ALL (SELECT 薪水 FROM 員工 WHERE 城市='台北')

51 9-4-3 邏輯運算子的子查詢- ANY和SOME運算子
ANY和SOME（此為ANSI-SQL標準的運算子）運算子的父查詢只需要滿足子查詢的任一結果即可。 SQL指令碼檔：Ch9_4_3_07.sql 使用子查詢取出【員工】資料表城巿是台北的薪水資料，然後在父查詢查詢只需大於等於子查詢任一薪水的記錄資料，如下所示： SELECT 姓名, 薪水 FROM 員工 WHERE 薪水 >= ANY (SELECT 薪水 FROM 員工 WHERE 城市='台北')

52 9-5 T-SQL進階查詢技巧 9-5-1 OFFSET和FETCH NEXT的分頁查詢 9-5-2 NULL空值的處理
9-5-3 CTE一般資料表運算式

53 9-5-1 OFFSET和FETCH NEXT的分頁查詢-OFFSET子句(語法)
OFFSET 整數常數或運算式 ROW | ROWS 上述語法的位移量可以是整數常數，例如：5或10等，或一個傳回大於0整數值的運算式，最後的ROW或ROWS關鍵字是同義詞，請任選一個使用，其目的是為了和ANSI相容。

54 9-5-1 OFFSET和FETCH NEXT的分頁查詢-OFFSET子句(範例)
SQL指令碼檔：Ch9_5_1_02.sql 請查詢【員工】資料表的員工記錄，不過，我們並不是從第1筆開始查詢，而是位移3筆，傳回第4筆之後的員工資料，如下所示： SELECT 身份證字號, 姓名, 薪水 FROM 員工 ORDER BY 身份證字號 OFFSET 3 ROWS

55 9-5-1 OFFSET和FETCH NEXT的分頁查詢-FETCH NEXT子句(語法)
FETCH NEXT子句是位在OFFSET子句之後，可以指定傳回位移之後的幾筆記錄，其語法如下所示： FETCH FIRST | NEXT 整數常數或運算式 ROW | ROWS ONLY 上述語法的FIRST和NEXT是同義詞，可以任選一個使用，傳回的筆數是整數常數、運算式或子查詢，ROW或ROWS關鍵字也是同義詞，請任選一個使用。

56 9-5-1 OFFSET和FETCH NEXT的分頁查詢-FETCH NEXT子句(範例)
SQL指令碼檔：Ch9_5_1_03.sql 請查詢【員工】資料表的員工記錄，在位移3筆後，傳回第4筆開始的5筆員工資料，如下所示： SELECT 身份證字號, 姓名, 薪水 FROM 員工 ORDER BY 身份證字號 OFFSET 3 ROWS FETCH NEXT 5 ROWS ONLY

57 9-5-2 NULL空值的處理-IS NULL運算子
SQL指令碼檔：Ch9_5_2_01.sql 查詢【學生】資料表沒有生日資料的學生記錄，即生日欄位是空值的記錄資料，如下所示： SELECT * FROM 學生 WHERE 生日 IS NULL

58 9-5-2 NULL空值的處理- ISNULL()函數(語法)
上述語法的檢查運算式可以檢查運算式是否為NULL空值，如果是，就以替代值輸出。請注意！函數的兩個參數類型需相同，如果不同，請使用CAST(欄位名稱 AS 類型)函數來進行轉換，也就是將欄位轉換成AS後的類型。

59 9-5-2 NULL空值的處理- ISNULL()函數(範例)
SQL指令碼檔：Ch9_5_2_02.sql 查詢【員工】資料表的電話欄位如果是空值，就輸出成'無電話'，如下所示： SELECT 身份證字號, 姓名, ISNULL(電話, '無電話') AS 電話 FROM 員工

60 9-5-3 CTE一般資料表運算式-說明 CTE（Common Table Expression）一般資料表運算式可以預先建立一至多個暫存資料表，以便在之後的SELECT查詢使用，或是建立遞迴查詢。

61 9-5-3 CTE一般資料表運算式-語法 一般資料表運算式CTE可以建立一至多個暫存資料表，其基本語法如下所示：
WITH 暫存資料表名稱1 [(欄位名稱清單)] AS ( SELECT指令敘述 ) [, 暫存資料表名稱2 [(欄位名稱清單)] AS (SELECT指令敘述) ] …..

62 9-5-3 CTE一般資料表運算式-範例 SQL指令碼檔：Ch9_5_3_01.sql
WITH 教授_員工 AS ( SELECT 教授.*, 員工.姓名 FROM 教授 INNER JOIN 員工 ON 教授.身份證字號 = 員工.身份證字號 ) SELECT 學生.學號, 學生.姓名, 課程.*, 教授_員工.* FROM 教授_員工 INNER JOIN (課程 INNER JOIN (學生 INNER JOIN 班級 ON 學生.學號 = 班級.學號) ON 班級.課程編號 = 課程.課程編號) ON 班級.教授編號 = 教授_員工.教授編號

63 9-5-3 CTE一般資料表運算式- 使用CTE執行遞迴查詢(語法)
「遞迴查詢」（Recursive Query）屬於一種特殊的SQL查詢，它是重複查詢資料表的查詢結果來取得最後的查詢結果，簡單的說，就是重複執行自己查詢自己。 因為CTE可以建立一至多個暫存資料表，所以可以活用CTE來執行遞迴查詢，稱為「遞迴CTE」（Recursive CTE），其基本語法如下所示： WITH 暫存資料表名稱 [(欄位名稱清單)] AS ( SELECT指令敘述1 UNION ALL SELECT指令敘述2 )

64 9-5-3 CTE一般資料表運算式- 使用CTE執行遞迴查詢(範例)
SQL指令碼檔：Ch9_5_3_03.sql 使用遞迴CTE建立【主管】資料表的遞迴查詢，可以顯示每位員工其上層主管的階層數，如下所示： WITH 主管_遞迴 AS ( SELECT 員工字號, 姓名, 1 AS 階層 FROM 主管 WHERE 主管字號 IS NULL UNION ALL SELECT 主管.員工字號, 主管.姓名, 階層+1 FROM 主管 JOIN 主管_遞迴 ON 主管.主管字號 = 主管_遞迴.員工字號 ) SELECT * FROM 主管_遞迴 ORDER BY 階層, 員工字號

65 9-6 使用Management Studio設計 SQL查詢
9-6-1 使用查詢設計工具 9-6-2 編寫資料表的指令碼

66 9-6-1 使用查詢設計工具-說明 在Management Studio啟動查詢設計工具，就可以幫助我們建立所需的SQL查詢，請在「物件總管」視窗展開資料庫後，選【教務系統】資料庫，按上方工具列的【新增查詢】鈕新增查詢。

67 9-6-1 使用查詢設計工具-圖例

68 9-6-2 編寫資料表的指令碼 在Management Studio選資料表物件，就可以使用【編寫資料表的指令碼為】指令來建立T-SQL指令碼，支援產生CREATE、DROP、SELECT、INSERT、UPDATE和DELETE指令。