資料庫程式設計 – PHP+MySQL :第六章 巢狀查詢程式 上一頁 下一頁
翻轉工作室:粘添壽 course_db E-R 關聯圖 <開啟>
6-2 巢狀查詢
當一個查詢敘述無法完成時,則需要結合多個 select 語句才能達成,接下來幾章將介紹這方面的技術,非常重要。我們先從子查詢語句連結開始。
6-2-1 巢狀子查詢運作
單一表格查詢很難滿足系統需求,大多情況皆須結合若干個表格資料,才能製造出所需的訊息。再說,我們想盡辦法讓資料庫內資料表之間產生關聯,就是希望能一個表格查詢到另一個表格的資料。也就是說,一個查詢子句並沒有給予一個完整的答案,則需要連結多個子句才能達成,這就是多重子句的查詢,其架構如下圖所示。
在主查詢語句中,也許需要多個訊息輸出,這些訊息並非一個查詢語句可以完成,此時就需要連結多個子查詢來完成。譬如上圖中,主查詢所要輸出的訊息_1(並非一個答案),它必須連結子查詢語句 A、D 與 E 來完成。另外,訊息_2 也許需要子查詢 B 和 F 來達成;同樣的,訊息_3 也需要子查詢 C 與 D 來完成。但它們執行步驟都是由下往上延伸。
多重子查詢的基本格式如下:
Select 欄位_1, 欄位_2, .. From 表格 Where 欄位名稱 『連結運算子』 (Select 欄位 From 表格 Where 條件判斷); |
其中『連結運算子』可以是:
(1) 一般比較運算:=、>、<、..。
(2) 模糊比較:In、Not In、Case、Exists、Not 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')"; … ?> |