資料庫程式設計 – PHP+MySQL 第 十章 善用 View 視界查詢  上一頁     下一頁

 

10-3 視界的運用

內容:

  • 10-3-1 還原資料表的運用

  • 10-3-2 範例研討:查詢教師授課名冊

瞭解視界的產生方法之後,接下來我們用幾個範例來說明視界的運用。

10-3-1 還原資料表的運用

(A) 視界 simple_students

我們用 students classes 兩張資料表來說明資料庫邏輯設計的問題。如下圖所示,原來記錄學生資料只要如同 org_students 表,裡面登錄了該學生在哪一班(class_name),在哪一系(dep_ID)。但班級名稱每年要修改,譬如今年四資三甲,明年就變成四資四甲,所以每年必須變更每一位學生的資料,這樣設計好像不理想。另外,學生可能轉系或轉班,又必須修改 class_name dep_ID,如果僅修改一個地方也是發生錯誤。因此,為了此正規化原則,我們將它拆解成 students classes 兩張資料表,每年只要修改 classes 內的 class_name,則全校學生資料隨之修改。此設計好像不錯,但延伸了新的問題,我們查詢學生資料時,大多以班級大多以代碼表示,但如要顯示班級名稱時,又要結合 classes 表格才能達成。再思考下一個問題,一般處理學生選課事務時,很少用到地址、電子郵件與電話,何不建立一個 view,來回復原來 org_students 的需求就好,如圖中的 simple_students

在本書 course_db 已建立了此資料表,其結構 {class_ID, student_ID, name, sex}SQL 命令如下:(輸入之前,必須先把原有的刪除)

Create view simple_students as

Select B.class_name, A.student_ID, A.name, A.sex

From students as A, classes as B

Where A.class_ID = B.class_ID;

(B) 視界 simple_teachers

同樣的問題也發生在 teachers department 資料表。如下圖所示,我們將它們回復成 simple_teachers 視界,不但能方便運用,也不會破壞原來的設計原則。

在本書 course_db 已建立了此資料表,其結構 {dep_name, teacher_ID, name}SQL 命令如下:(輸入之前,必須先把原有的刪除)

Create view simple_teachers as

Select B.dep_name, A.teacher_ID, A.name

From teachers as A, department as B

Where A.dep_ID = B.dep_ID

10-3-2 範例研討 -查詢教師授課名冊

(A) 系統功能:Ex10_1

教務處需要一個可查詢教師授課課程的名冊,查詢網頁如下:(可供輸入教師姓名與課程名稱)

執行網頁的結果,如下:

 (B) SQL 系統分析

此題目,我們用原資料表做過,現使用 simple_teachers simple_students 兩個 view 表格,來處理看看,下圖是資料表關連圖。

將上圖的關連,以 SQL 程式如下:

select C.class_name, C.student_ID, C.name, B.final

from open_course as A,

    select_course as B,

    simple_students as C

where A.teacher_ID = ‘$teacher_ID

and A.course_ID = ‘$course_ID

and A.open_course_ID = B.open_course_ID

and B.student_ID = C.student_ID;

(C) 網頁製作技巧

依照上述 SQL 系統分析,網頁設計如下:

(D) 查詢網頁的程式範例:Ex10_1-form.html

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

<?php

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

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

        // 查詢教師與課程

        $sql_query1 = "select teacher_ID, name

                   from teachers";

        $result1 = mysqli_query($select_db, $sql_query1);

       

        $sql_query2 = "select course_ID, course_name

                  from all_course";

        $result2 = mysqli_query($select_db, $sql_query2);

   echo "<p><font size='3'> 查詢教師授課名單與成績 </font></p>";

   echo "<form name='表單' method='post' action='Ex10_1-action.php'>";

        //選擇教師

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

        while($row=mysqli_fetch_row($result1)) {

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

            }

                echo "</select>";

                echo "<BR><BR>";

                //選擇授課科目

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

        while($row=mysqli_fetch_row($result2)) {

            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) 處理網頁:Ex10_1-action.php

編寫程式之前,須先建立simple_teachers simple_students 兩個 view 表格。

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

<?php

        $teacher_ID = $_POST["teacher_ID"];

        $course_ID = $_POST["course_ID"];

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

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

        // 查詢授課學生與成績

    $sql_query = "select C.class_name, C.student_ID, C.name, B.final

                  from open_course as A,

                       select_course as B,

                       simple_students as C

                  where A.teacher_ID = '$teacher_ID'

                  and A.course_ID = '$course_ID'

                  and A.open_course_ID = B.open_course_ID

                  and B.student_ID = C.student_ID";

        $result = mysqli_query($select_db, $sql_query);

       

        $items = array("班級名稱", "學號", "姓名", "成績");

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

        // 利用 foreach 迴圈印出陣列 $item

        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 關聯圖

 

資料庫程式設計 - PHP+MySQL: