翻轉工作室:粘添壽
第七章 混合查詢操作
7-1 子查詢與合併混合查詢
說實在的,由單一表格上查詢資料的機會並不多,原因是我們建立資料庫時,須考慮他的效益,大多會將一個表格分解成多個表格,再將表格之間建立關聯,才可節省記憶體空間,也能滿足真實環境的變遷。因此,每一個訊息的查詢大多會牽動到 2 個以上表格。到目前為止,對多表格的查詢我們介紹了多重子查詢(第四章),以及表格合併查詢(第五章),多重子查詢常常無法得到滿足的答案,除非訊息之間以串聯方式連接,用合併查詢最容易得到答案,但它所佔用的記憶體空間太大,對於資料量較大的資料庫系統根本無法可行。
兩查詢技巧都有它的優缺點,我們就擷取雙方的優點,思考一種混合式查詢技巧,也就是結合子查詢與合併查詢之功能。還未介紹之前,溫習一下重要的觀念,不管何種查詢技巧,所得到的結果都是一個『動態資料表』。我們的方法就是利用子查詢產生一個較小的『動態資料表』,再來與原資料表合併。或者,資料表合併之前,都先經過子查詢過濾掉一些無關的訊息,產生較精簡的資料表之後再來合併,如此就可以減少許多記憶體空間,執行效率也比較高。
我們再來回顧 SQL Select 查詢語句,如圖 7-1 所示。在select 命令行內(如 (1)),表示要擷取那些欄位,也可以利用某一子查詢命令將結果填寫於此。又在 From 命令行中(如 (2)),表示合併那些資料表,但也可以由某一子查詢得到動態資料表來取代。在 Where 命令行中,某一欄位內容如符合某一數值則篩選條件成立,但此數值也可以由某一子查詢得到(如 (3))。如果我們善加利用此特性,即可達到混合查詢的功能。
圖 7-1 SQL Select 命令的特性
甚至我們可以建立暫存資料表,並將某些查詢後的結果存放於該表,再利用它與其它表格做合併處理,使用完後再將這些暫存表格刪除,如此不但可以提高執行速度,在編寫查詢程式時,也比較不會遇到困難,這方面我們將在下一章介紹。
(A)程式功能:Ex7_1
請查詢『資訊管理系』全學年的課程,請依照課程代碼、課程名稱、必選修、學分數支順序印出。
(B)系統分析:
在我們用合併查詢當然可以得到答案,但必須合併 department 與 all_course 兩資料表,如此可能造成系統負荷,如下圖所示。
Select dep_name, course_ID, course_name, credit, required From department as A, all_course as B Where A.dep_ID = B.dep_ID And A.dep_name = "資訊管理系"; |
吾人可先由 department 表中查詢出『資訊管理系』的系別編碼 (dep_ID),它只有一筆資料的表格,再與 all_course 表格合併,不但可以找到結果,也不會造成系統負擔。
(C)程式範例:
步驟 1:由 department 表中找出資訊管理系的代碼 dep_ID。
Select dep_ID From department Where dep_name = "資訊管理系"; |
步驟 2:則將產生的暫存資料表(dep_ID) 與 all_course 表合併查詢,如下:
Select A.course_ID, A.course_name, A.required, A.credit From all_course as A, (Select dep_ID From department Where dep_name = "資訊管理系") as B Where A.dep_ID = B.dep_ID; |
(A) 程式功能:PM7_1
請查詢『進四資管二丙』班級學生名冊,請依照學號、姓名、性別、電話、郵件信箱之順序印出。
(B) 系統分析:
7-1-4 自我挑戰:查詢系所教師名冊
(A) 程式功能:PM7_2
請查詢『資訊管理系』教師名冊,請依照、姓名、性別、職務、電話、郵件信箱之順序印出。
(B) 系統分析:
(A) 程式功能:Ex7_2
請查詢學號 90211302 學生這學期修了哪些課,以及成績。請印出課程名稱、分數。
(B) 系統分析:
讓學生查詢自己修課成績,看起來是稀鬆平常的題目,但要完成它並不容易,甚至僅利用多重子查詢無法做到(學生可自行做看看,做出來加分)。如果不要印出課程名稱,只印出課程代碼或開課代碼,那就非常容易。首先,我們來看它的資料流程如何?利用學號 90211302 由 select_course 表中查詢出該生這學期修了那些課(open_course_ID),以及成績。再由 open_course 表中查詢出課程代號(course_ID),再由 all_course 表中查詢出所修課程的名稱。
(C) 程式範例:
步驟 1:首先利用學號 90211302 由 select_course 表中查詢出該生修了那些課程與成績,如下:
Select open_course_ID, final From select_course Where student_ID = "90211302"; |
步驟 2:得到上述表格後({open_course_ID, final},再與 open_course 表合併查詢,得到開課代碼的相對應課程代碼,以及其成績,如下:
Select B.course_ID, A.final From (Select open_course_ID, final From select_course Where student_ID = "90211302") as A, open_course as B Where A.open_course_ID = B.open_course_ID; |
步驟 3:得到上述表格後({course_ID, final},再與 all_course 表合併查詢,得到課程代碼對應的課程名稱,以及其成績,如下:
Select D.course_name, C.final From (Select B.course_ID, A.final From (Select open_course_ID, final From select_course Where student_ID = "90211302") as A, open_course as B Where A.open_course_ID = B.open_course_ID) as C, all_course as D Where C.course_ID = D.course_ID; |
由上述操作可以發現,我們先產生 {open_course_ID, final} as A 表格,再與 open_course as B表格合併,產生 {course_ID, final} as C 表格,再與 all_course as D表格合併,得到 {course_name, final} 的結果。之間都利用一個小表格與資料表合併,如此產生暫存表就不會很大,這就是混合查詢的優點。
(A) 程式功能:Ex7_3
請查詢選修『粘添壽』老師這學期開了那些課,請依照班級名稱、課程名稱、必選修、學分數等順序印出。
(B) 系統分析:
此題目我們在第六章做過(Ex6_6),它牽涉到四個資料表,如下圖所示。首先 teachers表中查詢出『粘添壽』老師的教師編碼(teacher_ID),再由 open_course 表查出他開了那些課,再經由 all_course 與 classes 表查詢出課程名稱與班級名稱。
(C) 程式範例:
我們利用混合查詢法,一步接一步產生小表格合併,如下:
步驟 1:首先由 teachers查詢出,『粘添壽』老師代碼,再由 open_course 查詢出他開了那些課,如下:(多重子查詢)
Select course_ID, class_ID From open_course Where teacher_ID = (Select teacher_ID From teachers Where name = "粘添壽"); |
上述程式產生了 {course_ID, class_ID},每筆資料表示開了某一課程,在某一班級開。
步驟 2:接著,我們利用 {course_ID, class_ID} 暫存表與 classes 表合併,找出班級名稱,如下:
select A.course_ID, B.class_name from (select course_ID, class_ID from open_course where teacher_ID = (select teacher_ID from teachers where name = "粘添壽")) as A, classes as B where A.class_ID = B.class_ID; |
上述產生了 {course_ID, class_name} 暫時表格,表示哪一班級開了某一門課(代碼)。
步驟 3:接著,再利用 {course_ID, class_name} 與 all_course 表合併查詢出課程名稱,如下:
Select C.class_name, D.course_name, D.required, D.credit From (select A.course_ID, B.class_name From (Select course_ID, class_ID From open_course Where teacher_ID = (select teacher_ID From teachers Where name = "粘添壽")) as A, classes as B Where A.class_ID = B.class_ID) as C, all_course as D Where C.course_ID = D.course_ID; |
(A) 程式功能:Ex7_4
請查詢選修『粘添壽』老師所開的『資料庫管理系統』學生的名冊,請依照班級名稱、姓名、性別,以及電話等順序印出。
(B) 系統分析:
此題目我們在第六章做過(Ex6_7),它牽涉到六個資料表,如下圖所示。首先必須由 all_course 與 teachers 兩表格中查詢到『資料庫管理系統』的課程代碼(course_ID),『粘添壽』老師的教師代碼(teacher_ID),再利用這兩個代碼由 open_course 表中查詢出開課代碼(open_course_ID)。接著,利用 open_course_ID 由 select_course 表中查詢出有哪些學生(student_ID)選修該課程,再利用選課學生的 stucent_ID,由 students 表中查詢出學生的相關資料。
(C) 程式範例:
我們利用混合查詢法,一步接一步產生小表格合併,如下:
步驟 1:首先由 teachers 與 all_course 兩表格中查詢出,『粘添壽』老師與資料庫管理系統』的代碼,再由 open_course 表格中,查出開課代碼 (open_course_ID),如下:
Select C.open_course_ID From (Select teacher_ID From teachers Where name = "粘添壽") as A, (Select course_ID From all_course Where course_name = "資料庫管理系統") as B, open_course as C where A.teacher_ID = C.teacher_ID And B.course_ID = C.course_ID |
上述程式合併了 {teacher_ID} as A, {course_ID} as B與 open_course 等三個表格,前兩個都僅有一筆資料,產生負荷不會很大。
步驟 2:找到開課代碼 open_course_ID後,再由 select_course 表中查詢出有哪些學生修課的 student_ID,如下:
Select E.student_ID From (Select C.open_course_ID From (Select teacher_ID From teachers Where name = "粘添壽") as A, (Select course_ID From all_course Where course_name = "資料庫管理系統") as B, open_course as C where A.teacher_ID = C.teacher_ID And B.course_ID = C.course_ID) as D, select_course as E Where D.open_course_ID = E.open_course_ID; |
步驟 3:找到修課學生的 student_ID後,再由 students 表中查詢出學生的相關資料,如下:
Select G.name, G.class_ID, G.sex, G.tel from (Select E.student_ID From (Select C.open_course_ID From (Select teacher_ID From teachers Where name = "粘添壽") as A, (Select course_ID From all_course Where course_name = "資料庫管理系統") as B, open_course as C where A.teacher_ID = C.teacher_ID And B.course_ID = C.course_ID) as D, select_course as E Where D.open_course_ID = E.open_course_ID) as F, students as G Where F.student_ID = G.student_ID ; |
步驟 4:接著,再與 classes 表合併找出班級名稱,如下:
Select I.class_name, H.name, H.sex, H.tel From (Select G.name, G.class_ID, G.sex, G.tel from (Select E.student_ID From (Select C.open_course_ID From (Select teacher_ID From teachers Where name = "粘添壽") as A, (Select course_ID From all_course Where course_name = "資料庫管理系統") as B, open_course as C where A.teacher_ID = C.teacher_ID And B.course_ID = C.course_ID) as D, select_course as E Where D.open_course_ID = E.open_course_ID) as F, students as G Where F.student_ID = G.student_ID) as H, classes as I Where H.class_ID = I.class_ID ; |
(D) 問題:
雖然混合查詢可以解決合併查詢所產生系統負荷的問題,但所產生的動態資料表很難與子查詢連結。在下一章介紹另一種更直接的方法,即是將所產生的動態資料表暫存起來,當合併完成之後,再將所產生的暫時資料表刪除,即可以解決此問題。
7-3 綜合練習
(A) 請查詢『進四資管二丙』這學期開了哪些課程,請依照課程名稱、必選修、學分數與授課教師之順序印出。