資料庫程式設計 – PHP+MySQL 第 七章 巢狀查詢程式  上一頁      下一頁

 

7-3 巢狀查詢:= 連結

內容:

  • 7-3-1 巢狀子查詢運作

  • 7-3-2 範例研討:查詢各系規劃課程

  • 7-3-3 範例研討:列印班級名單

當一個查詢敘述無法完成時,則需要結合多個 select 語句才能達成,接下來幾章將介紹這方面的技術,非常重要。我們先從子查詢語句連結開始。

7-3-1 巢狀子查詢運作

單一表格查詢很難滿足系統需求,大多情況皆須結合若干個表格資料,才能製造出所需的訊息。再說,我們想盡辦法讓資料庫內資料表之間產生關聯,就是希望能一個表格查詢到另一個表格的資料。也就是說,一個查詢子句並沒有給予一個完整的答案,則需要連結多個子句才能達成,這就是多重子句的查詢,其架構如下圖所示。

在主查詢語句中,也許需要多個訊息輸出,這些訊息並非一個查詢語句可以完成,此時就需要連結多個子查詢來完成。譬如上圖中,主查詢所要輸出的訊息_1(並非一個答案),它必須連結子查詢語句 AD E 來完成。另外,訊息_2 也許需要子查詢 B F 來達成;同樣的,訊息_3 也需要子查詢 C D 來完成。但它們執行步驟都是由下往上延伸。

多重子查詢的基本格式如下:

Select 欄位_1, 欄位_2, ..

From 表格

Where 欄位名稱 連結運算子 (Select 欄位

From 表格

Where 條件判斷);

其中『連結運算子』可以是:

(1) 一般比較運算:=><..

(2) 模糊比較:InNot InCaseExistsNot Exists

7-3-2 範例研討:查詢各系規劃的課程

(A) 系統需求:Ex7_5

教務處希望製作一個可以查詢某一系所規劃課程(全學年所有課程)的網頁,期望查詢網頁如下:

查詢結果的網頁如下:

 (B) SQL 製作技巧

全校所有課程都登錄於『課程資料表』(all_course),每學期再從這個表格裡挑選課程來開課,但它以系別代碼(dep_ID)欄位來辨識那一系所開的課程。但我們查詢時大多僅知道系別名稱,而不知道系別代碼,因此必須透過『系別資料表』(department) 查詢出系別代碼。

SQL 程式如下:

select *

from all_course

where dep_ID = (select dep_ID

              from department

where dep_name = ‘$dep_name’;

(C) 網頁製作技巧

依題目要求『表單網頁』需用下拉式選單,全校所有系別名稱都儲存於 department 資料表內,因此,需利用 SQL 查詢出所有系別名稱以供選擇。當『執行網頁』收到所欲查詢的『系別名稱』之後,再由 all_course 查出課程資料,如下:

 (D) 表單網頁的程式範例:Ex7_5-form.php

為了讓操作方便使用,系所名稱還是使用下拉式選單。系別代號與系別名稱儲存於 department 資料表,我們由此表取出各系名稱,範例如下:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

<?php

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

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

        // 取出所有系別名稱

        $sql_query = "select dep_name

                from department";

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

   echo "<p><font size='3'> 查詢各系課程資料 </font></p>";

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

        echo "選擇系別:";

        echo "<select name='dep_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) 查詢結果的網頁程式:Ex7_5-action.php

接收系別名稱($dep_name) 之後,我們由 department 查詢到它的代碼(dep_ID),再利用它由 all_course 表查詢該系所全年度所規劃的課程,範例如下:

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

        $dep_name = $_POST["dep_name"];

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

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

    $sql_query = "select '$dep_name',course_ID, course_name,

                             required, credit

                       from all_course

                       where dep_ID = (select dep_ID

                              from department

                                             where dep_name = '$dep_name')";

       

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

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

        while ( $meta = mysqli_fetch_field($result) ) {

                echo "<td> $meta->name </td>";

        }

        echo "</tr>";

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

                echo "<tr>";

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

                        //echo "<td> 'good' </td>";

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

                }

                echo "</tr>";

        }

        mysql_close($db_link);

?>

(F) 自我挑戰:

吾人修改 Ex7_5 的運作模式,既然『表單網頁』已由 department 全校各系名稱,何不順便取得系別編碼 dep_ID,並傳送給『處理網頁』,它就可以直接由 all_course 表查詢該系全年度課程資料,如下圖:(請自行演練)

7-3-3 範例研討:列印班級名冊

(A) 系統需求:Ex7_6

教務處希望製作一個可提供查詢班級名冊的網頁,期望查詢網頁如下:

查詢結果的網頁如下:

 (B) SQL 製作技巧

全校學生名單儲存於 students 表內,至於它是屬於哪一班同學,是由 class_ID 欄位標示,班別代碼(class_ID)對應到班別名稱(class_name),則須由 classes 資料表查出。因此他必須兩個查詢語句串接( = ),但本範例希望 class_ID 由查詢網頁處理,它傳 class_ID 給處理網頁,處理網頁再利用 class_ID students 表篩檢出該班級名單。

select *

from students

where class_ID = ‘$class_ID’;

(C) 網頁設計技巧

雖然 SQL 查詢命令需要兩個語句用『=』連結起來,但吾人可將第一個語句 (查詢 class_ID) 由『選單網頁』完成,它再將 class_ID 傳遞給『執行網頁』,再查詢該班級名冊,如下圖:

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

我們開啟 classes 表查詢 class_ID class_name,將它製作成下拉式選單,使用者點選後,傳遞 class_ID 給處理網頁。範例如下:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

<?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='Ex7_6-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) 執行網頁的程式:Ex7_6-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

<?php

 

        $class_ID = $_POST["class_ID"];

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

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

        // 查詢班級名冊

    $sql_query = "select student_ID, name, sex, tel, mailbox, address

                           from students

                           where class_ID = $class_ID";

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

        // 列印班級名冊

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

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

        for($i=0; $i<7; $i++) {

                echo "<td>".$items[$i]."</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>";

        }

        mysql_close($db_link);

?>

 

主講人:粘添壽博士

course_db 關聯圖

 

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