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

翻轉工作室:粘添壽     course_db E-R 關聯圖 <開啟>

 

6-2 巢狀查詢

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

6-2-1 巢狀子查詢運作

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

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

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

Select 欄位_1, 欄位_2, ..

From 表格

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

From 表格

Where 條件判斷);

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

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

(2) 模糊比較:InNot InCaseExistsNot Exists

 

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

(A) 系統需求:Ex6_3-form.php

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

查詢結果的網頁如下:

(B) 製作技巧

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

(C) SQL 語法:(查資訊管理系所開的課程)

SELECT *

from all_course

where dep_ID = (select dep_ID

              from department

              where dep_name = '資訊管理系');

 

(D) 查詢網頁的程式範例:Ex6_3-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

25

26

27

28

29

30

31

32

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>查詢各系課程資料</title>

</head>

<body>

<?php

        $db_link = mysql_connect("localhost", "root", "123456")

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

        $select_db = mysql_select_db("course_db");

        $sql_query = "select dep_ID, dep_name

                      from department";

  

        mysql_query("SET NAMES 'utf8'");

        $result = mysql_query($sql_query);

?>

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

   <form name="表單" method="post" action="Ex6_3-action.php">

<?php

           echo "選擇系別:";

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

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

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

               }

            echo "</select>";

            echo "<BR><BR>";  

            mysql_close($db_link);

?>

      <input type="submit" value="送出">

   </form>

</body>

</html>

(F) 查詢結果的網頁程式:Ex6_3-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

<! filename: Ex6_3-action.php>

<?php

        $dep_name = $_POST["dep_name"];

        $db_link = mysql_connect("localhost", "root", "123456")

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

        $select_db = mysql_select_db("course_db");

        mysql_query("SET NAMES 'utf8'");

        $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 = mysql_query($sql_query);

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

        for($i=0; $i<mysql_num_fields($result); $i++) {

                echo "<td>".mysql_field_name($result, $i)."</td>";

        }

        echo "</tr>";

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

                echo "<tr>";

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

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

                }

                echo "<tr/>";

        }

        mysql_close($db_link);

?>

備注:其實,查詢網頁(Ex6_3-form.php)可選擇傳遞系別代碼(dep_ID),則執行網頁就只查一個表格(all_course)就可以了。(請讀者自己改看看)

 

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

(A) 系統需求:Ex6_4

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

查詢結果的網頁如下:

(B) 製作技巧

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

(C) SQL 語法:(查進資管二丙班級名冊)

select *

from students

where class_ID = (select class_ID

                            from classes

                            where class_name = "進四資管二丙");

 

(D) 查詢網頁程式:Ex6_4-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

21

22

23

24

25

26

27

28

29

30

31

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>查詢各系課程資料</title>

</head>

<body>

<?php

        $db_link = mysql_connect("localhost", "root", "123456")

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

        $select_db = mysql_select_db("course_db");

        $sql_query = "select class_ID, class_name

                      from classes";

        mysql_query("SET NAMES 'utf8'");

        $result = mysql_query($sql_query);

?>

   <p><font size="3"> 查詢班級名冊 </font></p>

   <form name="表單" method="post" action="Ex6_4-action.php">

<?php

           echo "選擇班級:";

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

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

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

               }

            echo "</select>";

            echo "<BR><BR>";  

            mysql_close($db_link);

?>

      <input type="submit" value="送出">

   </form>

</body>

</html>

(E) 執行網頁的程式:Ex6_4-action.php

查詢網頁僅送來 class_ID 訊息,但系統要求列印出班級名稱,各欄位名稱以中文顯示,就不可以僅由資料表欄位名稱顯示。因此我們要有兩個查詢,一則利用 class_ID classes 表查出 class_name;另一則利用 class_ID students 表查出該班的同學。

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

<! filename: Ex6_4-action.php>

<?php

 

        $class_ID = $_POST["class_ID"];     

        $db_link = mysql_connect("localhost", "root", "123456")

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

        $select_db = mysql_select_db("course_db");

     mysql_query("SET NAMES 'utf8'");

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

                        from students

                      where class_ID = $class_ID";

    $sql_query_1="select class_name

                      from classes

                      where class_ID = $class_ID";

        $result = mysql_query($sql_query);

    $result_1 = mysql_query($sql_query_1);

 

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

        $row_1=mysql_fetch_row($result_1);

    echo "$row_1[0] 班級名冊 <BR>";

 

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

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

                echo "<td>".$items[$i]."</td>";

        }

        echo "</tr>";

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

                echo "<tr>";

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

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

                }

                echo "<tr/>";

        }

        mysql_close($db_link);

?>

 

6-2-4 自我挑戰:列印各系教師名冊

(A) 系統需求:PM6_3

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

執行結果的網頁如下:

(B) 製作技巧

同樣的,全校教師資料儲存於 teachers 表內,它利用 dep_ID 來辨識該教師是屬於哪一系。因此,我們須透過 department 表用系別名稱(dep_name),查出 dep_ID,需要串接查詢。

(C) 查詢網頁的提示:PM6_3-form.html

1

2

3

4

5

6

7

8

9

10

11

12

13

….

<?php

           echo "選擇系別:";

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

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

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

$row[1]($row[0])</option><BR>";

                }

            echo "</select>";

            echo "<BR><BR>";  

            mysql_close($db_link);

?>

….

(D) 執行網頁的提示:PM6_3-action.php

假設查詢網頁僅傳遞系別代碼 (dep_ID) 過來,但系統要求顯示系別名稱 (dep_name),因此,除了需要一個查詢敘述由 dep_ID 查詢出教師資料外( teachere),還需要一個查詢敘述利用 dep_ID,由 department 表查詢出系別名稱(dep_name),提示如下:

1

2

3

4

5

6

7

8

9

10

11

12

…..

       $sql_query = "select teacher_ID,name,tel,title, mailbox,address

                          from teachers

                          where dep_ID = $dep_ID";

 

       $sql_query_1="select dep_name

                    from department

                    where dep_ID = $dep_ID";

 

$result = mysql_query($sql_query);

        $result_1 = mysql_query($sql_query_1);

   ……

 

6-2-5 自我挑戰:查詢低於平均分數學生

(A) 系統需求:PM6_4

請製作一個可以用開課編號,查詢修該課程低於平均分數的同學,並列出其學號與成績,期望查詢網頁如下:

查詢結果的網頁如下:

(B) 系統分析

學生修課與成績都登錄於 select_course 資料表內,我們先篩選出該課程修課同學,並利用 avg() 函數計算平均值,再以同表格查詢低於平均值的學生,是屬於同表格連結查詢。

(C) SQL 程式設計:(假設查詢開課代號 1)

SELECT student_ID, final

from select_course

where open_course_ID = ‘1’

and final <= (select avg(final)

              from select_course

              where open_course_ID = ‘1’);

 

(D) 查詢網頁提示:

系統要求必須使用下拉式選單,開課代碼是記錄在 open_course 資料表內。因此,我們開啟該資料表,並做成選單方式。

1

2

3

4

5

6

7

<?php

….

        $sql_query = "select open_course_ID

                      from open_course";

        $result = mysql_query($sql_query);

….

?>

(E) 執行網頁提示:

我們將 SQL 查詢程式嵌入,其他大致上都相同,如下:

1

2

3

4

5

6

7

8

9

10

<?php

        $sql_query = "select student_ID, open_course_ID, final

                      from select_course

                      where open_course_ID = '$open_course_ID'

                      and final <= (select avg(final)

                                      from select_course

                                      where open_course_ID = '$open_course_ID')";

        …

?>

 

<GOTOP>