翻轉電子書系列：資料庫系統概論(含邏輯設計)  

翻轉工作室：粘添壽

第五章 多重子查詢操作

5-1 多重子查詢簡介

5-1-1 多表格的迷失

也許您會發問為何需要那麼多表格，為何不要一個表格就好？沒錯許多情況下一個表格就可以達成，但往往會造成資料不一致性的問題，以 students 學生資料為例，如果如圖 5-1(a) 設計，將學生的資料詳細填入一個表格內(students)，雖然可以容易攫取資料，但容易產生不一致性的問題。譬如班級名稱每年都要變更，今年『四資二甲』，明年就須變更為『四資三甲』，則每年都需對每一位學生資料變更，萬一有位學生變更錯誤，就會產生不一致的問題。如果我們將學生的班級名稱，以代碼 (class_ID) 取代，再另外產生 classes 班級資料表，如圖 5-1(b) 所示，就可以解決此問題(當然還有其它原因，我們在第九章說明)。但當我們謹對 students 資料表擷取資料時，僅能顯示班級代碼，並無法顯示班級名稱。因此，需要再由 classes 表內再查詢出班級名稱。由此可見，須查詢兩個資料表才可以得到正確的訊息，如此則需要兩個查詢，需要子查詢來串接。

圖 5-1 表格之間關聯

當然，還有其他查詢技巧可以解決此問題，譬如合併查詢、、等等。但本章先介紹多重子查詢的技巧如何。

5-1-2 多重子查詢運作

許多情況，一個查詢子句並沒有給我們一個完整的答案，也需要連結多個子句才能達成，這就是多重子句的查詢，其架構如下圖所示。

圖 5-2 多重子查詢的運作程序

最上層子查詢是我們想得到結果，但它的過濾條件可能需要另一個子查詢來得到。由此可見，查詢動作是由下往上遞傳，子查詢(三)得到的結果，經過子查詢(二)的連結過濾，得到的結果，再經由子查詢(一)得到最後的結果。子查詢之間連結判斷可區分三大類：

(1) 單一判斷(=、>、!=、<)：下層子查詢僅輸出一個結果(select 敘述)，比較判斷是否符合條件。

(2) 多重輸出(In、No In、Exit、No Exit)：下層子查詢的結果超過一個輸出時，則：

• In ：表下層輸出元素，每個都執行一次。

• No In：下層子查詢沒有輸出，則執行一次。

• Exit：下層子查詢有任何相符輸出，則執行一次。

• No Exit：下層子查詢沒有相符輸出，則執行一次。

多重查詢的基本格式如下：

其中『比較運算子』可以是：

• 一般比較運算：=、>、<、..。

• 模糊比較：In、Not In、Case、Exists、Not Exists。

5-2 比較運算子連接

上層查詢語句是否執行是依照下層子句查詢結果之比較而決定，比較運算子有：等於(=)、大於(>)、小於(<)、、等等。

5-2-1範例研討：查詢系所規劃課程

(A)程式功能：Ex5_1

請查詢『資訊管理系』全學年規劃有哪些課程，結果如下：

(B)系統分析：

• 各系全學年所規劃課程皆紀錄於 all_course 表內，但它僅記錄系別代碼(Dep_ID)，如下：(擷取部分資料)

• 系別名稱與系別代碼是登錄於 department 表內，如下：(擷取部分資料)

• 所以我們必須由 department 表中查詢出『資訊管理系』的代碼如何，如下：

• 得到 Dep_ID = 11 後，再利用它由 all_course 查詢出資訊管理系所規劃之課程，如下：

(C)程式範例：

吾人將上述兩個子查詢串接起來即可，如下：

5-2-2範例研討：查詢低於平均分數同學

(A)程式功能：Ex5_2

請查詢修讀資料庫系統(開課代碼 open_course_ID = 1) 低於平均分數同學的學號(student_ID)與分數 (final)，結果如下：

(B)系統分析：

• 全校學生選課與成績都登錄於 select_course 表格內，我們先查出修讀 open_course_ID = 1 學生的平均分數，如下：

• 得到平均分數後(avg(final) = 83.83)，再利用它由同一表格(select_course)查詢修讀 open_course_ID = 1 課程低於或等於平均分數的同學，如下：(符合兩只條件者)

(C)程式範例：

吾人將上述兩個子查詢串接起來即可，如下：(同表格串接查詢)

5-2-3自我挑戰：列印班級名冊

(A)程式功能：PM5_1

請列印『進四資管二丙』班級名冊，請依照學號(student_ID)、姓名(name)、性別(sex) 與電話(tel)順序，結果如下：

(B)重點提示：

• 全校學生名冊都登錄於 students 表內，但須先由 classes 表內『進四資管二丙』的班級代碼(class_ID)，如下：

• 得班級代碼後(class_ID= 902113)，再利用它由 students 查詢出學生名冊，如下：

• 接著，將兩子查詢語句串接即可。

5-2-4自我挑戰：系別教師名冊

(A)程式功能：PM5_2

請列印『資訊管理系』教師名冊，請依照教師編號(teacher_ID)、姓名(name)、電話(tel) 與郵件地址 (mailbox) 順序，結果如下：

(B)重點提示：

• 第一個子查詢由 department 資料表內查詢出資訊管理系的 dep_ID 為何。

• 第二個子查詢再利用 dep_ID 由 teachers 表內查詢出教師資料。

5-3 多重子查詢 in 連結

5-3-1 子查詢 In 連結語法

如果第一次查詢出來的結果不只一個的話{如：value1, value2, …}，就必須利用 in 語句來連結，表示所有數值都要執行一次。語句格式如下：

5-3-2範例研討：列印選課名單

(A)程式功能：Ex5_3

請列印出修讀『資料庫管理系統』(開課代號 open_course_ID=1)學生名冊，結果如下：

(B)系統分析：

• 首先利用 open_course_ID 由 select_course 表中查詢出有哪些學生修讀此課程(學號  student_ID)，如下：

• 上述查詢得到修讀學生的學號，再利用這些學號(student_ID) 由 students 資料表中查詢學生資料。每一個學號(student_ID) 都必須查詢一次得到該學生的資料，因此必須用 in 連結，如下：

(C)程式範例：

吾人將上述兩個子查詢用 In 串接起來即可，如下：

5-3-3範例研討：查詢系所開課課程

(A)程式功能：Ex5_4

請查詢『資訊管理系』這學期開了那些課程(課程代碼 courase_ID)，結果如下：

(B)系統分析：

• 首先由 department 表中查出『資訊管理系』的系別代碼(Dep_ID)，如下：

• 再利用 dep_ID 由 teachers 表中查詢出該系有哪些老師(teacher_ID)，如下：

• 再利用每位老師的 teacher_ID 由 open_course 表中查詢出這學期他們開了那些課(course_ID)，如下：

(C)程式範例：

吾人將上述三個子查詢用串接起來即可，如下：

5-3-4自我挑戰：查詢系所開課詳細資料

(A)程式功能：PM5_3

延續 Ex5_4，查詢『資訊管理系』這學期開了那些課程，但須顯示各課程的詳細資料，結果如下：

(B)重點提示：

吾人將得到的 course_ID，再由 all_course (全校課程資料) 中查出課程資料即可。

5-3-5自我挑戰：查詢班級開課資料

(A)程式功能：PM5_4

請查詢『進四資管二丙』這學期開了哪些課程，結果如下：

(B) 重點提示：

• 步驟 1：利用『進四資管二丙』(class_name) 由 classes 表中查詢出該班的代碼 class_ID。

• 步驟 2：再利用 class_ID 由 open_course 表中查詢出該班這學期開了哪些課程的課程代碼 course_ID (多筆資料)。

• 步驟 3：將每一個 course_ID 由 all_course 表中查詢出該課程的詳細資料。

5-3-6自我挑戰：查詢課程教師

(A)程式功能：PM5_5

查詢這學期開『資料庫管理系統』課程有哪幾位老師的姓名，結果如下：

(B)重點提示：

• 步驟 1：利用『資料庫管理系統』( course_name) 由 all_course 表中查詢出該課程的代碼 course_ID。

• 步驟 2：再利用 course_ID 由 open_course 表中查詢出有哪些老師開此課程的教師代碼 teacher_ID(多筆資料)。

• 步驟 3：將每一個 teacher_ID 由 teachers 表中查詢出該教師的姓名(name)。

5-4 多重查詢 Exists 連結

5-4-1 Exists 連結語法

只要子查詢語句傳回來不是空值的話，表示條件成立，而且上層查詢語句的 Where 條件敘述，與子查詢語句的Where 條件相同，基本語句如下：

或條件不存在(not exists)，基本語法如下：

5-4-2範例研討：查詢已開課教師名單

(A) 程式功能：Ex5_5

 請查詢『資訊管理系』這學期已經排課的教師代碼與姓名，查詢結果下：

(B) 系統分析：

• 首先由 department 表中查出『資訊管理系』的系別代碼(Dep_ID)，如下：

• 再利用 dep_ID 由 teachers 表中查詢出該系有哪些老師(teacher_ID)，如下：

 (C) 程式範例：

欲找出已排課教師代號和姓名必須由 teachers 表格中查詢，但條件是該老師的單位是資訊管理系，並且在 open_course 表格內已登錄開課，程式如下：

5-4-3範例研討：全校未開課教師名單

(A) 程式功能：Ex5_6

 請全校這學期還未排課教師名單，查詢結果下：

(B) 系統分析：

這一題較簡單，只要找出沒有在 open_course 表格內的 teacher_ID，再由 teachers 表格找出相對應姓名即可。

(C) 程式範例：

5-4-4範例研討：查詢未選課學生名單

(A) 程式功能：Ex5_7

查詢這學期開『資訊管理系』尚未選課學生學號與姓名，結果如下： (請新增一位資訊管理系學生)

(B) 系統分析：

• 步驟 1：查詢資訊管理系的系別編碼(Dep_ID)

• 步驟 2：由系別編碼(Dep_ID) 查出資訊管理系有哪些班級(class_ID)。

• 步驟 3 ：利用 class_ID 由 students 表中查出有哪些學生，並且不在 select_course 表內，則印出其 student_ID 與 name。

(C) 程式範例：

5-4-5範例研討：查詢系老師開課代碼

(A) 程式功能：Ex5_8

查詢這學期開『資訊管理系』老師總共開了那些課程，如下：

(B) 系統分析：

• 步驟 1：由 department 表中查詢資訊管理系的系別編碼(Dep_ID)

• 步驟 2：利用 Dep_ID 由 teachers 查出有哪些老師，由 open_course 表中有開課教師，在印出課程代碼 (course_ID)。

(C) 程式範例：

5-4-6 自我挑戰：查詢系別教師開課名稱

(A) 程式功能：PM5_6

查詢這學期開『資訊管理系』老師總共開了那些課程的課程名稱(延續 5-3-5 題目)，如下：

5-4 專題研討

(A) 程式功能：Ex5_9

請列印出這學期修讀『粘添壽』老師（教師代碼=0108）所開的『資料庫管理系統』的學生名冊。

(B) 系統分析：

由下列幾個步驟來完成。

• 先由『all_course』找出『資料庫管理系統』的『course_ID』。

• 再利用『course_ID』與『teacher_ID』，由『open_course表』查出『open_course_ID』。

• 再利用『open_course_ID』由『select_course 表』，查出修讀該課程學生的學號(student_ID)。

• 再利用『student_ID』由『students 表』找出相關資料。

(C) 程式範例：

• 步驟 1：查出資料庫管理系統的課程代碼(course_ID)，如下：

• 步驟 2：再查出粘添壽老師(teacher_ID = 0108)所開資料庫管理系統的開課代碼(open_course_ID)，如下：

• 步驟 3：再由 open_course_ID 查出那些學生(student_ID)修讀該課程，如下：

• 步驟 4：利用 student_ID 由 students 表中查出相關資料，，如下：

• 步驟 5：插入查詢粘添壽老師的代碼程式，與擷取所需資料如下：

• 步驟 6：我們還是希望將班級名稱直接顯示，而不要代碼，如下：

5-5 綜合練習

(1)請查詢各系『系主任』這學期開了哪些課程，請列印出課程名稱、必選修與學分數。

(2)請查詢這學期『女』老師開了哪些課程，請列印出開課代碼、課程名稱、必選修。

(3)請查詢這學期『資訊管理系』，『女』老師開了哪些課程，請列印出開課代碼、課程名稱、必選修。

(4)請查詢出『資訊工程系』這學期開了哪些課程，請列印出課程名稱與授課教師。

(5)請列印出 102 學年度入學的『女』學生名冊。

(6)請列印出 102 學年度入學的『女』性，並姓『陳』同學的名冊。