子查詢 in 連結

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

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

6-3 子查詢 in 連結

6-3-1 In 連結語法

當子查詢的結果超過一個的時候，則需用 in 連結，基本語法如下：

Select 欄位_1, 欄位_2, .. // 父查詢敘述

From 資料表_1

Where 欄位 in (select item // 子查詢敘述

From 資料表_2

Where 條件敘述);

譬如，子查詢的結果有 3 個(A、B、C)，則一個(A)接一個(B)進入父查詢執行，總共執行 3 次。

6-3-2 範例研討：查詢選課名單

(A) 系統需求：Ex6_5

請製作一個可以用開課編號，查詢修該課程學生的名冊，期望查詢網頁如下：

查詢結果的網頁如下：

(B) 系統分析

學生修課與成績都登錄於 select_course 資料表內，我們先篩選出該課程修課同學(取得 student_ID)，再利用student_ID 由 students 資料表內篩選出學生資料，但修該門課的同學不止一位，因此需要用 in 連結。

(C) SQL 語法：(查詢開課代碼 = 1)

SELECT *

from students

where student_ID in (select student_ID

from select_course

where open_course_ID = "1");

(D) 查詢網頁：Ex6_5-form.php

系統要求使用下拉式選單，我們開啟 open_course 資料表，並選擇 open_course_ID 做成選單方式，如下：

<head>

<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 open_course_ID

from open_course";

mysql_query("SET NAMES 'utf8'");

$result = mysql_query($sql_query);

<p><font size="3"> 以開課編號查詢選課名單 </font></p>

<?php

echo "選擇開課編號：";

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

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

echo "<option> $row[0]</option><BR>";

}

echo "</select>";

echo "<BR><BR>";

mysql_close($db_link);

</form>

</body>

</html>

(F) 執行網頁的範例：Ex6_5-action.php

<! filename: Ex6_5-action.php>

<?php

$open_course_ID = $_POST["open_course_ID"];

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

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

$select_db = mysql_select_db("course_db");

$sql_query = "SELECT *

FROM students

WHERE student_ID IN (SELECT student_ID

FROM select_course

WHERE open_course_ID ='$open_course_ID')";

mysql_query("SET NAMES 'utf8'");

$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);

6-3-3 範例研討：查詢系所開課

(A) 系統需求：Ex6_6

請製作一個可以查詢某一系這學期開了哪些課程(僅列出代碼)。期望查詢網頁如下：

查詢結果的網頁如下：

(B) 系統分析

全校這學期所開的課全部登錄在 open_course 資料表內，對於開課情況有些混亂。譬如，某一系的老師不一定僅在該系開課，也許會被邀請到其他系開課。另外，同一門課也可能在不同班級開課，也不一定是同一老師開課。因此，open_course 僅記錄哪一位老師(teacher_ID)、開哪一門課(course_ID)、以及課開在哪一班(class_ID)，並沒有記錄開在哪一系。照理講，我們僅將 teacher_ID、class_ID 與 course_ID 三個欄位可組合一個主鍵(Primary key)，但為了方便操作，還是多增加一個 open_course_ID 欄位為主鍵，並將它設定為自動增加數字，但不可重複。

系統要求某一系的老師開了哪些課，因此，我們必須由department 表內查詢到該系的識別碼 (dep_ID)，再利用 dep_ID 到 teachers 表查詢到該系有哪些老師(超過一位)，再由這些老師的 teacher_ID 查詢 open_course 表內所開的課程，因為超過一位老師，因此需要用『=』連結，如下圖所示：

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

SELECT *

from open_course as A

where teacher_ID in (select teacher_ID

from teachers

where Dep_ID = (select Dep_ID

from department

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

(D) 查詢網頁：Ex6_6-form.php

系統要求用下拉式選單，因此，我們開啟 department 資料表 select 選項，但傳遞選擇系別名稱的 dep_ID，處理網頁就可以減少一個步驟，程式範例如下：

<head>

<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>

<?php

echo "選擇系所名稱：";

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

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

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

}

echo "</select>";

echo "<BR><BR>";

mysql_close($db_link);

</form>

</body>

</html>

(F) 執行結果的網頁：Ex6_6-action.php

接收查詢網頁所傳遞的系別代碼($dep_ID)後，則利用它查詢有哪些老師，每位老師開了哪些課，範例如下：

<!filename:Ex6_6-action.php>

<?php

$dep_ID = $_POST["dep_ID"];

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

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

$select_db = mysql_select_db("course_db");

$sql_query = "SELECT *

from open_course as A

where teacher_ID in (select teacher_ID

from teachers

where Dep_ID = $dep_ID)";

mysql_query("SET NAMES 'utf8'");

$result = mysql_query($sql_query);

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

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

echo "<td>".mysql_fetch_field($result, $i)->name."</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-3-4 自我挑戰：查詢系所老師的開課課程

(A) 系統需求：PM6_4

請製作一個可以查詢某一系這學期開了哪些課程(列出課程名稱，與相關資料)。期望查詢網頁如下：

查詢結果的網頁如下：

(B) 系統分析

這是 Ex6_6 的延伸，假如我僅希望知道開課內容，而不用知道它是開在哪一班或哪一位老師授課，則這個題目就能滿足。如果還要知道開在哪一班的班級名稱，可還要動腦筋才能做到(爾後有範例)。延伸 Ex6_6 得到了所開課的課程代碼 (course_ID)，再利用它由 all_course (課程資料表)內查詢到該課程的相關資料，但它不僅一個，所以要用in 連結。

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

Select *

Form all_course

Where course_ID IN (

SELECT course_ID

from open_course as A

where teacher_ID in (

select teacher_ID

from teachers

where Dep_ID = (

select Dep_ID

from department

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

(D) 查詢傳送網頁：PM6_4-form.php (與 Ex6_6-form.php 相同)

(E) 接收與處理網頁：PM6_4-action.php (重點提示)

….

$sql_query = "select *

from all_course

Where course_ID in (

SELECT course_ID

from open_course as A

where teacher_ID in (

select teacher_ID

from teachers

where dep_ID = $dep_ID))";

$result = mysql_query($sql_query);

….

<GOTOP>