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

 

8-3 多重表格合併查詢

內容:

  • 8-3-1 合併查詢簡述

  • 8-3-2 範例研討:查詢教師開課

  • 8-3-3 範例研討:查詢老師開課及班級

  • 8-3-4 範例研討:查詢班級開課及教師

8-3-1 合併查詢簡述

無論巢狀或合併查詢技巧,僅利用兩個表格的處理能力太低了,許多訊息大多需結合 3 個以上表格才能得到。合併查詢是多重表格查詢的能手,但它造成的系統負荷太大,我們先來玩看看,再尋求改進的方法。假設 students [2000*8] (前者為記錄筆數,後者為欄位數)classes [50*4]open_course [40*5],三個表格合併後得到 4 百萬(2000 * 50 * 40)筆資料、每筆 17(8 + 4 + 5) 個欄位,由此可見合併的系統負荷有多大。

8-3-2 範例研習:查詢老師開課

(A) 系統功能:Ex8_3

學務處需要一個用老師姓名,可以查詢到他這學期所開的課,期望查詢網頁如下:

執行網頁的結果如下:

 (B) SQL 製作技巧

每學期全校所開的課程登錄於 open_course 表內,其中用 teacher_ID 註明哪一位老師開的,但它是教師代碼,必須透過 teachers 表查詢被查詢老師的識別碼。另外,系統要求顯示課程資料,但 open_course 僅利用 course_ID 標示課程,還是需要由 all_course 查詢出該課程的相關資料。如果使用巢狀查詢技巧,則先由 teachers 查詢到老師的 teacher_ID,再由 open_course 表中查詢到該教師開了哪些課( in 連結),再利用 course_ID all_course 表查詢到該課程的相關資料。

如果使用合併查詢技巧的話,3 個表格合併後每筆記錄有 17 個欄位(8 + 4 + 5、各表格欄位數,資料筆數更大) 的大型暫時資料表,再由此暫時資料表來過濾 A.name=教師姓名、A.teacher_ID = B.teacher_ID B.course_ID = C.course_ID 等三條件條件,再選取 C 表格上欄位內容即可,如下圖所示:

SQL 語法如下:

Select C.course_ID, C.course_name, C.required, C.credit

From teachers as A, open_course as B, all_course as C

Where A.name = "粘添壽"

And A.teacher_ID = B.teacher_ID

And B.course_ID = C.course_ID;

(C) 網頁製作技巧

在『表單網頁』上由 teachers 表查詢出所有教師姓名以供下拉式選擇,取得教師姓名後,傳送給『處理表單』,查出所要的資訊,如下:

 

(D) 查詢傳送網頁:Ex8_3-form.php

系統要求利用下拉式來選擇老師,則查詢網頁開啟 teachers 資料表,並選取教師姓名,如下:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

<?php

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

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

        // 查詢系所編碼

        $sql_query = "select name

                  from teachers";

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

   echo "<p><font size='3'> 查詢教師授課科目 </font></p>";

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

        echo "選擇教師姓名:";

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

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

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

            }

        echo "</select>";

        echo "<BR><BR>";

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

        echo "</form>";

    mysql_close($db_link);

?>

(E) 執行結果網頁:Ex8_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

<?php

        $name = $_POST["name"];

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

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

    $sql_query = "select C.course_ID, C.course_name, C.required, C.credit

                  from teachers as A, open_course as B, all_course as C

                  where A.name = '$name'

                  and A.teacher_ID = B.teacher_ID

                  And B.course_ID = C.course_ID";

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

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

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

        // 利用 foreach 迴圈印出陣列

        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);

?>

 (F) 自我挑戰:另類做法 (Ex8_3_1)

既然 Ex7_3-form.php 已開啟 teachers 資料表,使用者即可點選進入,哪何不直接傳送 teacher_ID?則 Ex7_3-action.php 就不需要合併該資料表,僅合併 open_course all_course 兩張資料表即可,請讀者自行修改看看,如下圖:

8-3-3 範例研討:查詢老師開課及班級

(A) 系統功能 Ex8_4

教務處需要一個可以由教師姓名查詢他所開的課,並且註明開在哪一班級,期望查詢網頁如下:

執行網頁的結果如下:

 (B) SQL 製作技巧:(備註:利用巢狀查詢很難達成)

這個題目看起來很容易,但要實現出來不容易。如果用巢狀技巧的話,我們由下圖來分析相關資料之間的關聯,首先利用教師姓名由 teachers 表中查詢到他的 teacher_ID,再由 open_course 表查出他所開的課程及班級,接著同時由 course_ID class_ID 查詢課程資料與班級名稱是很難達成的 (需要一點技巧)

因此,我們將查詢 teachers 工作由『表單網頁』完成,其餘三個表格由『執行網頁』用合併查詢法,得到 13 (4+5+4) 個欄位的暫存表,如下圖:

合併查詢的SQL 程式如下:(查詢粘添壽老師所開的課程)

Select C.class_name, B.course_ID, B.course_name, B.required, B.credit

From open_course as A, all_course as B, classes as C

Where (A.teacher_ID = "$teacher_ID")

And (A.course_ID = B.course_ID)

And (A.class_ID = C.class_ID)

(C) 網頁製作技巧:

依照 SQL 查詢規劃,網頁製作如下圖:

(D) 查詢傳送網頁:Ex8_4-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='Ex8_4-action.php'>";

        echo "選擇教師姓名:";

        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) 執行結果網頁:Ex8_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

<?php

        $teacher_ID = $_POST["teacher_ID"];

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

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

        // 查詢教師授課班級與科目

    $sql_query = "Select C.class_name, B.course_ID, B.course_name,

                             B.required, B.credit

                                  From open_course as A, all_course as B, classes as C

                  Where (A.teacher_ID = '$teacher_ID')

                  And (A.course_ID = B.course_ID)

                  And (A.class_ID = C.class_ID)";

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

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

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

        // 利用 foreach 迴圈印出陣列

        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);

?>

8-3-4 範例研討:查詢班級開課及教師

(A) 系統功能 Ex8_5

教務處需要一個可以由班級名稱查詢該班課程及授課教師,期望查詢網頁如下:

執行網頁的結果如下:

 (B) SQL 製作技巧

course_db 關係圖分析起來,此題目利用合併查詢的相關資料表的關係如下圖:

我們將班級名稱與 class_ID 對照由『表單網頁』實現,它將傳遞 class_ID 給執行網頁。執行網頁將 open_courseall_course teachers 3 張資料表合併後,再由下列條件篩選出所要的訊息:

(a) A.class_ID = “傳遞 $class_ID”

(b) A.course_ID = B.course_ID:該課程的詳細資料。

(c) A.class_ID = C.class_D:班級名稱。

『執行網頁』的SQL 程式如下:

Select C.name , B.course_ID, B.course_name, B.required, B.credit

From open_course as A, all_course as B, teachers as C

Where A.class_ID = "$class_ID"

And A.course_ID = B.course_ID

And A.teacher_ID = C.teacher_ID

(C) 網頁製作技巧

依照 SQL 程式規劃,網頁製作如下:

(D) 表單網頁的程式:Ex8_5-form.php

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='Ex8_5-action.php'>";

        echo "選擇班級名稱:";

        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);

?>

(E) 執行網頁的程式:Ex8_5-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

<?php

        $class_ID = $_POST["class_ID"];

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

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

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

    $sql_query = "Select C.name, B.course_ID, B.course_name, B.required, B.credit

                  From open_course as A, all_course as B, teachers as C

                  Where A.class_ID = '$class_ID'

                  And A.course_ID = B.course_ID

                  And A.teacher_ID = C.teacher_ID";

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

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

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

        // 利用 foreach 迴圈印出陣列

        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 關聯圖

 

資料庫程式設計 - PHP+MySQL:

 

 

 

翻轉電子書系列: