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

翻轉工作室:粘添壽

 

第七章 混合查詢操作

7-1 子查詢與合併混合查詢

7-1-1 簡介

說實在的,由單一表格上查詢資料的機會並不多,原因是我們建立資料庫時,須考慮他的效益,大多會將一個表格分解成多個表格,再將表格之間建立關聯,才可節省記憶體空間,也能滿足真實環境的變遷。因此,每一個訊息的查詢大多會牽動到 2 個以上表格。到目前為止,對多表格的查詢我們介紹了多重子查詢(第四章),以及表格合併查詢(第五章),多重子查詢常常無法得到滿足的答案,除非訊息之間以串聯方式連接,用合併查詢最容易得到答案,但它所佔用的記憶體空間太大,對於資料量較大的資料庫系統根本無法可行。

兩查詢技巧都有它的優缺點,我們就擷取雙方的優點,思考一種混合式查詢技巧,也就是結合子查詢與合併查詢之功能。還未介紹之前,溫習一下重要的觀念,不管何種查詢技巧,所得到的結果都是一個『動態資料表』。我們的方法就是利用子查詢產生一個較小的『動態資料表』,再來與原資料表合併。或者,資料表合併之前,都先經過子查詢過濾掉一些無關的訊息,產生較精簡的資料表之後再來合併,如此就可以減少許多記憶體空間,執行效率也比較高。

我們再來回顧 SQL Select 查詢語句,如圖 7-1 所示。在select 命令行內( (1)),表示要擷取那些欄位,也可以利用某一子查詢命令將結果填寫於此。又在 From 命令行中( (2)),表示合併那些資料表,但也可以由某一子查詢得到動態資料表來取代。在 Where 命令行中,某一欄位內容如符合某一數值則篩選條件成立,但此數值也可以由某一子查詢得到( (3))。如果我們善加利用此特性,即可達到混合查詢的功能。

7-1 SQL Select 命令的特性

甚至我們可以建立暫存資料表,並將某些查詢後的結果存放於該表,再利用它與其它表格做合併處理,使用完後再將這些暫存表格刪除,如此不但可以提高執行速度,在編寫查詢程式時,也比較不會遇到困難,這方面我們將在下一章介紹。

 

7-1-2 範例研討:查詢系別課程設計

(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)程式範例:

Select dep_ID

From department

Where dep_name = "資訊管理系";

     

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;

7-1-3 自我挑戰:查詢班級名冊

(A) 程式功能:PM7_1

請查詢『進四資管二丙』班級學生名冊,請依照學號、姓名、性別、電話、郵件信箱之順序印出。

(B) 系統分析:

 

7-1-4 自我挑戰:查詢系所教師名冊

(A) 程式功能:PM7_2

請查詢『資訊管理系』教師名冊,請依照、姓名、性別、職務、電話、郵件信箱之順序印出。

(B) 系統分析:

 

7-1-5 範例研討:查詢學生修課成績

(A) 程式功能:Ex7_2

請查詢學號 90211302 學生這學期修了哪些課,以及成績。請印出課程名稱、分數。

(B) 系統分析:

讓學生查詢自己修課成績,看起來是稀鬆平常的題目,但要完成它並不容易,甚至僅利用多重子查詢無法做到(學生可自行做看看,做出來加分)。如果不要印出課程名稱,只印出課程代碼或開課代碼,那就非常容易。首先,我們來看它的資料流程如何?利用學號 90211302 select_course 表中查詢出該生這學期修了那些課(open_course_ID),以及成績。再由 open_course 表中查詢出課程代號(course_ID),再由 all_course 表中查詢出所修課程的名稱。

(C) 程式範例:

Select open_course_ID, final

From select_course

Where student_ID = "90211302";

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;

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} 的結果。之間都利用一個小表格與資料表合併,如此產生暫存表就不會很大,這就是混合查詢的優點。

7-1-6 範例研討:查詢教師開課與班級

(A) 程式功能:Ex7_3

請查詢選修『粘添壽』老師這學期開了那些課,請依照班級名稱、課程名稱、必選修、學分數等順序印出。

(B) 系統分析:

此題目我們在第六章做過(Ex6_6),它牽涉到四個資料表,如下圖所示。首先 teachers表中查詢出『粘添壽』老師的教師編碼(teacher_ID),再由 open_course 表查出他開了那些課,再經由 all_course classes 表查詢出課程名稱與班級名稱。

(C) 程式範例:

我們利用混合查詢法,一步接一步產生小表格合併,如下:

Select course_ID, class_ID

From open_course

Where teacher_ID = (Select teacher_ID

                                   From teachers

                                    Where name = "粘添壽");

上述程式產生了 {course_ID, class_ID},每筆資料表示開了某一課程,在某一班級開。

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} 暫時表格,表示哪一班級開了某一門課(代碼)

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;

7-2 專題研討

(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) 程式範例:

我們利用混合查詢法,一步接一步產生小表格合併,如下:

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 等三個表格,前兩個都僅有一筆資料,產生負荷不會很大。

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;

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 ;

    

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) 請查詢『進四資管二丙』這學期開了哪些課程,請依照課程名稱、必選修、學分數與授課教師之順序印出。