| |
8-3
多重表格合併查詢
內容：
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_course、all_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 關聯圖
資料庫程式設計：
翻轉電子書系列：
|
