資料庫程式設計 – PHP+MySQL ：第 九章 混合查詢程式  上一頁      下一頁

 

9-2 複雜的混合查詢

內容：

  • 9-2-1 範例研討：查詢教師開課

  • 9-2-2 範例研討：列印教師授課名單

  • 9-2-3 範例研討：查詢班級開課課程

混合查詢的基本原理是將子查詢後的結果，再與其他資料表合併，如此就可減少資料量的產生，因此它結合了巢狀與合併兩種技巧。接下來我們用幾個較複雜的範例，才能顯現它的功能。

9-2-1範例研討：查詢教師所開課程

(A) 系統需求：Ex9_2

教務處需要一支可以查詢這學期教師開課網頁，希望能列印出班級名稱、課程名稱、必選修與學分數等，查詢網頁格式如下：

執行網頁的結果如下：

 (B) SQL 系統分析

此題目利用合併查詢法可以達成(用巢狀查詢較困難)，但他必須合併 3 張資料表。我們用混合查詢來試看看。如下圖所示，我們將由教師姓名查詢出他的 teacher_ID 資料表，再由查詢網頁來達成，查詢網頁傳遞所欲查詢教師的 teacher_ID 給執行網頁。執行網頁利用 teacher_ID 由 open_course(as A) 資料表查詢出該教師所開的課程(course_ID) 與開在哪一班級(class_ID)，並設定成表格 B {course_ID, class_ID}。接著 B 資料表再與 all_course (as C) 資料表合併，並篩選條件為 C.course_ID = B.course_ID，表示選取教師所開課程的資料， 擷取出 {C.course_ID, C.course_name, C.required, C.credit, D.class_ID}(as D)，其中較特殊的是擷取 C.class_ID，表示保留該課程在哪一個班級。接下來，資料表 D 再與 classes(as E) 合併，篩選條件是 (D.class_ID = E.class_ID)，則可選取該班級名稱。我們從中可以發現，經過子查詢所得到的資料(如 B 與 D)內容較小，再與原資料表(如 all_course 與 classes)合併，所產生的暫存表格也較小。再說，它是一層接一層合併，並非同時合併多個資料表，當然系統負荷會減少許多。

 SQL 語法：利用 AppServ 驗證 ($teacher_ID = 0108)

select E.class_name, D.course_name, D.required, D.credit

From classes as E, (select C.course_name, C.required, C.credit, B.class_ID

                from all_course as C, (select A.course_ID, A.class_ID

                                   from open_course as A

                                   where A.teacher_ID = '$teacher_ID') as B

                 where C.course_ID = B.course_ID) as D

where D.class_ID = E.class_ID

(C) 網頁製作技巧

由上述 SQL 分析，『表單網頁』由 teachers 表查詢以供下拉式選擇 teacher_ID，『執行表單』執行混合式查詢，如下：

(D) 表單網頁程式範例：Ex9_2-form.php

開啟 teachers資料表建立選單方式挑選教師姓名，程式範例如下：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

<?php

        $select_db = mysqli_connect("localhost", "root", "12345678", "course_db")

                or die("MySQL 伺服器連結失敗 <br>");

        // 查詢教師姓名與教師編碼

        $sql_query = "select teacher_ID, name

                  from teachers";

        $result = mysqli_query($select_db, $sql_query);

   echo "<p><font size='3'> 查詢教師開課課程與班級 </font></p>";

   echo "<form name='表單' method='post' action='Ex9_2-action.php'>";

        //選擇教師姓名

        echo "<select name='teacher_ID'>";

        while($row=mysqli_fetch_row($result)) {

            echo "<option value='$row[0]'> $row[1]($row[0])</option><BR>";

            }

        echo "</select>";

                echo "<BR><BR>";

                echo "<input type='submit' value='送出'>";

        echo "</form>";

    mysql_close($db_link);

?>

(E) 執行網頁程式範例：Ex9_2-action.php

我們將混合查詢程式植入，如下：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

<?php

        $teacher_ID = $_POST["teacher_ID"];

        $select_db = mysqli_connect("localhost", "root", "12345678", "course_db")

                or die("MySQL 伺服器連結失敗 <br>");

        // 查詢教師開課課程與班級

    $sql_query = "select E.class_name, D.course_name, D.required, D.credit

                from classes as E, (select C.course_name, C.required, C.credit, B.class_ID

                               from all_course as C, (select A.course_ID, A.class_ID

                                                 from open_course as A

                                            where A.teacher_ID = '$teacher_ID') as B

                               where C.course_ID = B.course_ID) as D

                where D.class_ID = E.class_ID";

        $result = mysqli_query($select_db, $sql_query);

        $items = array("班級名稱", "課程名稱", "必選修", "學分數");

        echo "<table border = '2'><tr align='center'>";

        // 利用 foreach 迴圈印出陣列 $item

        foreach ($items as $value) {

                echo "<td> $value </td>";

        }

        echo "</tr>";

        while($row=mysqli_fetch_row($result)) {

                echo "<tr>";

                for($j=0; $j<mysqli_num_fields($result); $j++) {

                        echo "<td>$row[$j]</td>";

                }

                echo "</tr>";

        }

        echo "</table>";

        mysql_close($db_link);

?>

9-2-2 範例研討：列印教師授課名單

(A) 系統功能：Ex9_3

教務處需要一支可列印教師授課學生名冊的網頁，期望查詢網頁如下：

執行網頁的結果如下：

 (B) SQL 系統分析

乍看起來，此題需合併 7 張資料表才能達成，為了簡化合併數量，我們將三張資料表的處理由查詢網頁來達成，由 teachere 資料表選擇授課教師(teacher_ID)、由 all_course 資料表選擇課程名稱(course_ID) 與由 classes 選擇開課班級(class_ID)，再將這三個訊息傳遞給執行網頁。執行網頁運作情形如下圖所示，它收到這三個訊息後，運作如下：在 SQL 規劃重點如下：

1.     由表單網頁收到 $course_ID、class_ID、$teacher_ID，可從 open_course 表查出該教師在哪一班與開哪一門課的 open_course_ID，並取名表格 A。

2.     表格 A 再與 select_course 合併查詢出那些學生修讀的 student_ID，並取名為表格 C。

3.     表格 C 再與 students 合併查詢出修讀學生的詳細資料，並取名為表格 E。

4.     表格 E 內僅記錄班級編碼，因此表格 E 再與 classes 表格並查詢出每位學生的班級名稱。

在 SQL 程式設計如下：可利用 AppServ 驗證 ( $teacher_ID = 0108、$course_ID = 90B252、$class_ID = 902113)

select F.class_name, E.student_ID, E.name, E.sex, E.tel, E.mailbox, E.address

from classes as F, (select D.*

               from students as D, (select student_ID

                                from select_course as B, (select open_course_ID

                                                  from open_course

                                                  where course_ID = '$course_ID'

                                                  And class_ID = '$class_ID'

                                                  And teacher_ID = '$teacher_ID') as A

                                where A.open_course_ID = B.open_course_ID) as C

               where C.student_ID = D.student_ID )as E

where E.class_ID = F.class_ID

(D) 網頁製作技巧

由上述 SQL 分析，網頁設計如下：

(D) 表單網頁程式範例：Ex9_3-form.php

開啟 teachers、all_course 與 classes 等 3 張資料表，建立選單方式挑選教師姓名、課程名稱與開課班級，程式範例如下：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

<?php

        $select_db = mysqli_connect("localhost", "root", "12345678", "course_db")

                or die("MySQL 伺服器連結失敗 <br>");

        // 查詢教師、班級與課程

        $sql_query1 = "select teacher_ID, name

                   from teachers";

        $result1 = mysqli_query($select_db, $sql_query1);

        $sql_query2 = "select class_ID, class_name

                   from classes";

        $result2 = mysqli_query($select_db, $sql_query2);

        $sql_query3 = "select course_ID, course_name

                  from all_course";

        $result3 = mysqli_query($select_db, $sql_query3);

   echo "<p><font size='3'> 查詢教師授課班名單 </font></p>";

   echo "<form name='表單' method='post' action='Ex9_3-action.php'>";

        //選擇教師

        echo "<select name='teacher_ID'>";

        while($row=mysqli_fetch_row($result1)) {

            echo "<option value='$row[0]'> $row[1]($row[0])</option><BR>";

            }

                echo "</select>";

                echo "<BR><BR>";

                //選擇班級

        echo "<select name='class_ID'>";

        while($row=mysqli_fetch_row($result2)) {

            echo "<option value='$row[0]'> $row[1]($row[0])</option><BR>";

            }

                echo "</select>";

                echo "<BR><BR>";

                //選擇授課科目

        echo "<select name='course_ID'>";

        while($row=mysqli_fetch_row($result3)) {

            echo "<option value='$row[0]'> $row[1]($row[0])</option><BR>";

            }

                echo "</select>";

                echo "<BR><BR>";

                echo "<input type='submit' value='送出'>";

        echo "</form>";

    mysql_close($db_link);

?>

(E) 執行網頁程式範例：Ex9_3-action.php

我們將混合查詢程式植入，如下：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

<?php

        $teacher_ID = $_POST["teacher_ID"];

        $class_ID = $_POST["class_ID"];

        $course_ID = $_POST["course_ID"];

        $select_db = mysqli_connect("localhost", "root", "12345678", "course_db")

                or die("MySQL 伺服器連結失敗 <br>");

        // 查詢教師開課課程與班級

    $sql_query =

          "select F.class_name, E.student_ID, E.name, E.sex, E.tel, E.mailbox, E.address

       from classes as F, (select D.*

                 from students as D, (select student_ID

                          from select_course as B,(select open_course_ID                                                                             

                                           from open_course

                                           where course_ID = '$course_ID'

                                           And class_ID = '$class_ID'

                                           And teacher_ID = '$teacher_ID') as A

                            where A.open_course_ID = B.open_course_ID) as C

                    where C.student_ID = D.student_ID )as E

        where E.class_ID = F.class_ID";

        $result = mysqli_query($select_db, $sql_query);

        $items = array("班級名稱", "學號", "姓名", "性別", "電話", "郵件", "地址");

        echo "<table border = '2'><tr align='center'>";

        // 利用 foreach 迴圈印出陣列 $item

        foreach ($items as $value) {

                echo "<td> $value </td>";

        }

        echo "</tr>";

        while($row=mysqli_fetch_row($result)) {

                echo "<tr>";

                for($j=0; $j<mysqli_num_fields($result); $j++) {

                        echo "<td>$row[$j]</td>";

                }

                echo "</tr>";

        }

        echo "</table>";

        mysql_close($db_link);

?>

9-2-3 範例研討：查詢班級開課課程

(A) 系統需求：Ex9_4

教務處需要一支可以查詢各班開課狀況的網頁，期望查詢網頁如下：

執行網頁的結果如下：

 (B) SQL 系統分析

我們利用混合技巧，由班級名稱查詢到 class_ID 的任務傳遞給查詢網頁來達成。執行網頁收到所欲查詢的 class_ID 後，由 open_course資料表查詢出該班所開的課程及老師 {course_ID, teacher_ID} (as A)，再與 all_course (as B) 合併，篩選條件為 B.course_ID = C.course_ID，表示選取該課程的相關資料，並擷取 {course_ID, teacher_ID, course_name, required, credit}等欄位 (as D)，再由它與 teachers 資料表合併，篩選條件為 D.teacher_ID = E.teacher_ID，表示選取該教師相關資料。

我們直接用 SQL 命令操作如下：

select D.name, C.course_ID, C.course_name, C.required, C.credit

from teachers as D, (select A.course_ID, A.teacher_ID, B.course_name, B.required, B.credit

                 From all_course as B, (select course_ID, teacher_ID

                                    From open_course

                                    where class_ID = ‘$class_ID’) as A

                 where A.course_ID = B.course_ID ) as C

where C.teacher_ID = D.teacher_ID;

(C) 網頁製作技巧

依照上述 SQL 系統分析，網頁設計如下：

(D) 查詢網頁的提示：Ex9_4-form.php

我們開啟 classes 資料表，再建立下拉式選單，讓使用者挑選班級，再傳遞班級代碼 (class_ID) 給執行網頁，程式提示如下：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

<?php

        $select_db = mysqli_connect("localhost", "root", "12345678", "course_db")

                or die("MySQL 伺服器連結失敗 <br>");

        // 查詢班級

        $sql_query = "select class_ID, class_name

                   from classes";

        $result = mysqli_query($select_db, $sql_query);

   echo "<p><font size='3'> 查詢班級開課課程 </font></p>";

   echo "<form name='表單' method='post' action='Ex9_4-action.php'>";

                //選擇班級

        echo "<select name='class_ID'>";

        while($row=mysqli_fetch_row($result)) {

            echo "<option value='$row[0]'> $row[1]($row[0])</option><BR>";

            }

                echo "</select>";

                echo "<BR><BR>";

                echo "<input type='submit' value='送出'>";

        echo "</form>";

    mysql_close($db_link);

?>

(D) 執行網頁的提示：Ex9_4-action.php

我們將混合查詢程式植入，如下：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

<?php

        $class_ID = $_POST["class_ID"];

        $select_db = mysqli_connect("localhost", "root", "12345678", "course_db")

                or die("MySQL 伺服器連結失敗 <br>");

        // 查詢教班級課課程

    $sql_query =

              "select D.name, C.course_ID, C.course_name, C.required, C.credit

           from teachers as D, (select A.course_ID, A.teacher_ID,

B.course_name, B.required, B.credit

                            From all_course as B, (select course_ID, teacher_ID

                                               From open_course

                                               where class_ID = '$class_ID') as A

                            where A.course_ID = B.course_ID ) as C

            where C.teacher_ID = D.teacher_ID";

        $result = mysqli_query($select_db, $sql_query);

        $items = array("教師姓名", "課程代碼", "課程名稱", "必選修", "學分數");

        echo "<table border = '2'><tr align='center'>";

        // 利用 foreach 迴圈印出陣列 $item

        foreach ($items as $value) {

                echo "<td> $value </td>";

        }

        echo "</tr>";

        while($row=mysqli_fetch_row($result)) {

                echo "<tr>";

                for($j=0; $j<mysqli_num_fields($result); $j++) {

                        echo "<td>$row[$j]</td>";

                }

                echo "</tr>";

        }

        echo "</table>";

        mysql_close($db_link);

?>

翻轉工作室：粘添壽

 

course_db 關聯圖

資料庫程式設計：

 

 

 

翻轉電子書系列：