SQL 合併查詢程式

8-2 合併查詢設計

內容：

8-2-1 合併&巢狀查詢比較

8-2-2 範例研習：查詢各系教師

8-2-3 範例研討：查詢系全學年課程

8-2-1 合併 & 巢狀查詢比較

如何靈活運用 SQL 語法，由資料庫中萃取知識，一直都是 IT 工程師最能表現能耐的地方。同一個資料庫(如 course_db)，有人可以幫它設計出各種方便操作使用的介面，有人卻一籌莫展無法運用它。尤其資訊領域學生應徵工作時，主考官最喜歡考這方面的概念，能力如何一試便知道。

由資料庫中萃取知識，利用巢狀或合併查詢都可以達到目的，到底用哪一種比較理想呢？簡單的比較如下：

(1) 利用巢狀查詢比較節省電腦資源，速度比較快，尤其對大量資料庫而言，因為他產生的暫時表格較小(子查詢的結果)。但有許多知識無法僅利用巢狀查詢得到，也就是說，巢狀查詢比較難達到目的。

(2) 利用合併查詢最容易得到所要的訊息，因為它比對所有可能出現的機會，也就是說，用它來查詢最容易達成目的。它的缺點是可能產生巨大無比的暫存資料表，譬如一萬筆記錄合併另一個一萬筆記錄的資料表，就有可能產生一億筆記錄的暫存檔。因此，使用合併查詢要特別留意。

8-2-2 範例研習：查詢各系教師

(A) 系統功能：Ex8_1

教務處需要一個可以查詢各系教師的網頁，期望查詢網頁如下：

執行網頁的結果如下：

(B) SQL 製作技巧

n 利用 SQL 巢狀查詢

全校所有教職員工都登錄於 teachers 表格內，並以 dep_ID 欄位標明該教師是屬於哪一系，又各系的系別代碼(dep_ID)與系別名稱(dep_name)登錄於 deparmentt 表格內。

利用巢狀查詢方法。首先利用欲查詢的系所(如資訊管理系)由 department 表查詢到該系的系別編碼(dep_ID)，再利用 dep_ID 由 teachers 表內搜尋出該系的老師資料，程式如下：

Select *

From teachers

Where dep_ID = (select dep_ID

From department

Where dep_name = “資訊管理系”);

n 利用 SQL 合併查詢

如果我們用合併查詢法，則將 teachers 與 department 兩表格交叉合併得到所有可能的組合(teachers 每一筆記錄對應到 department 每一筆記錄)，如下圖所示。假設我們要查詢資訊管理系老師，可以發現 depart.Dep_name = “資訊管理系”，同時 teacher.Dep_ID = depart.Dep_ID 兩條件都符合即可篩選出所要的資料。並以 department 表格內 dep_name 資料為所欲查詢的系所即可。

SQL 程式如下：(查資訊管理系所開的課程)

Select B.dep_name, A.name, A.tel, A.title, A.mailbox

From teachers as A, department as B

Where B.dep_name = "資訊管理系"

And A.dep_ID = B.dep_ID

此程式中，我們將 teachers 表格設定為 A( as A)，department 表格設定為 B( as B)，是用來方便擷取哪一個表格的欄位，譬如：B.dep_ID 是 department 表格的 dep_ID 欄位的意思。

(C) 網頁製作技巧

『表單網頁』由 department 表中查出所有系別名稱。以供下拉式選擇，並傳送 Dep_name 給『執行網頁』，由它利用合併查詢出結果並顯示於網頁上。

(D) 表單網頁的程式：Ex8_1-form.php

<?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='Ex8_1-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);

(D) 處理網頁程式範例：Ex8_1-action.php

<?php

$Dep_name = $_POST["Dep_name"];

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

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

$sql_query = "select dep_name, name, tel, title, mailbox

from teachers as A, department as B

where B.dep_name = '$Dep_name'

and A.dep_ID = B.dep_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 關聯圖分析，如果從巢狀查詢則需先利用所欲查詢的系別名稱 (dep_name)，由 department 表查詢出相對應的 dep_ID，再利用 = 連結 all_course 表內查詢出該系全學年所規劃的課程。如果用合併查詢的話，須將 department 與 all_course 表合併，如下：

兩表格合併後，成為一只較大的表格 (department * all_course)，再以 department.dep_ID = all_course.dep_ID 與 dep_name = "系所名稱" 為篩選條件，擷取出所需的資訊，如下圖所示：

『表單網頁』由 department 表中查出所有系別名稱。以供下拉式選擇，並傳送 Dep_name 給『執行網頁』，由它利用合併查詢出結果並顯示於網頁上。