翻轉電子書系列:資料庫系統概論(含邏輯設計)  

翻轉工作室:粘添壽

 

第八章 表格異動處理

SQL 語言除了提供資料查詢之外,也提供處理表格內容的相關命令,透過它們可以建立、刪除、變更表格,或插入、更新、移除、、等等處理資料表內容。這些命令我們在第三章有提到的 DDL DML 語法,但沒有介紹到,這裡補充說明。另外,對於表格的異動處理,並非只限於一個表格。一個異動處理也許會牽涉到多個表格的內容變更,這方面比較困難,爾後較進階的課程再介紹,本章只介紹針對一個表格的資料異動處理。

8-1 建立表格 – Create table

8-1-1 Create Table 語法

看得出來,Create Table 是建立資料表的命令,可以指定幾個欄位、每個欄位的資料格式、指定該表格主鍵、或外來鍵等等,命令格式如下:

Create Table 資料表名稱

  (欄位_1  資料型態 屬性,

   欄位_2  資料型態 屬性,

   …,

   Primary Key (欄位_1, 欄位_2)

)

其中資料型態,常用有:

另外,屬性有:

 

8-1-2 範例研討:建立監護人表格

(A) 程式功能:Ex8_1

請建立一個學生監護人 (guardian)資料表,包含有:學號(student_ID)、監護人(guardian)、電話(tel)、地址(address)

(B)系統分析:

學號表示每一位學生的識別,它應該是這表格唯一不可重複的,我們就定它為主鍵,每個欄位都可採用字元格式,但地址的長度較長一點就好。

(C)程式範例:

create table guardian (

              student_ID  char(10) not null,

              guardian  char(20),

              tel char(20),

              address char(50),

             primary key (student_ID)

);

          

吾人亦可觀察 quardian 表格之結構如何:

              

8-2 變更表格 – Alter Table

8-2-1 Alter Table 語法

當表格格式不滿足所需時,可以利用 Alter Table 變更型態,可針對現有表格作:增加欄位、修改欄位、刪除欄位,變更主鍵,以及定義屬性等等功能,格式如下:

Alter Table 資料表名稱

  [Alter] [欄位] [資料型態][Null | Not null]

  [Add | Drop] [欄位] [屬性][限制]

 

8-2-2 範例研討:增加關係欄位

(A) 程式功能:Ex8_2

請在監護人(guardian)資料表內增加關係 (relation) 欄位。

(B) 程式範例:

Alter Table guardian Add relation char(10);

吾人再觀察 quardian 表格之結構如何:

 

8-3 插入資料 – Insert Into

8-3-1 Insert Into 語法

將資料新增到表格則利用 Insert 命令來達成,格式如下:

Insert Into 資料表名稱 <欄位串列>

Values (<資料串列>);

 

8-3-2 範例研討:輸入監護人資料

(A) 程式功能:Ex8_3

請輸入監護人資料,如下:

    1. 學號:90211304 、監護人:劉媽媽、電話:0923111223、地址:高雄市苓雅區 20 號、關係:母女。

    2. 學號:90211305 、監護人:張先生、電話:0923111277、地址:高雄市苓雅區 30 號、關係:夫妻。

    3. 學號:90211306 、監護人:劉爸爸、電話:0923111266、地址:高雄市苓雅區 40 號、關係:父子。

(B) 程式範例:

Insert Into guardian

    values("90211304", "陳媽嗎", "0923111223", "高雄市苓雅區 20 ", "母女");

Insert Into guardian

    values("90211305", "張先生", "0923111277", "高雄市苓雅區 30 ", "夫妻");

Insert Into guardian

    values("90211306", "劉爸爸", "0923111266", "高雄市苓雅區 40 ", "父子");

吾人再觀察 quardian 表格的內容如何:

Select *

From quardian;

            

 

8-4 更新資料 – Update

8-4-1 Update 語法

新資料取代現有資料表的內容,稱之為『更新』(Update),但資料表內有多筆資料,到底要更新哪一筆(或稱紀錄)或多筆資料同時更新,因此需要有一個 Where 敘述句來決定更新哪一筆或哪些資料。格式如下:

Update 資料表名稱

Set [<欄位_1>=<數值_1>, …<欄位_2>=<數值_2>]

Where <條件敘述>;

上述語句中,只要某一筆滿足 Where 條件敘述,則更新它指定欄位的內容。

 

8-4-2 範例研討:變更監護人資料

(A) 程式功能:Ex8_4

請將學號:90211304監護人資料中將關係(relation)改為『姊妹』。

(B) 程式範例:

Update guardian

Set relation = "姊妹"

Where student_ID = "90211304"

吾人再觀察 student_ID 資料內的關係(relation) 是否被變更:

Select *

From guardian

Where student_ID = "90211304";

    

 

8-5 刪除資料 – Delete From

8-5-1 Delete From 語法

刪除資料表中某一筆或多筆記錄則用 Delete 命令,但也須利用 Where 條件敘述來選取哪些紀錄要被刪除。格式如下:

Delete From 資料表名稱

Where <條件敘述>;

上述語句中,滿足 Where 條件敘述的紀錄,其內容將被刪除。

8-5-2 範例研討:刪除監護人資料

(A) 程式功能:Ex8_5

請將學號:90211304監護人資料刪除。

(B) 程式範例:

Delete

From guardian

Where Student_ID = "90211304"

吾人再觀察 student_ID = 90211304 資料是否還在 guardian 表內:

Select *

From guardian;

 

8-6 刪除表格 – Drop Table

8-6-1 Drop Table 語法

當表格格式不需要時,可以利用Drop Table 命令刪除它,當然它裡面資料也會一併被刪除掉,格式如下:

Drop Table 資料表名稱 [Restrict | Cascade];

其中,Restrict Cascade 表示資料表之間的關聯也會被刪除。

8-6-2 範例研討:刪除監護人表

(A) 程式功能:Ex8_6

請刪除監護人(guardian) 表格。

(B) 程式範例:

Drop Table guardian

吾人再觀察 course_db 內是否存在 guardian 表:

Show Tables;

8-7 表格異動整合運用

8-7-1 暫存資料表的運用

在第六章我們介紹了『子查詢』與『合併查詢』的整合運用,但發現其中最大困難是合併後的『動態資料表』,必須立即連接子查詢或與其他資料表(或動態資料表)合併,有時候真的很難連結。如果我們能產生暫時資料表,並將查詢後的動態資料表儲存起來,對於查詢語句就會比較好編寫,尤其在資料庫程式設計方面就會簡單許多。當查詢運作完成後,再將這些暫存資料表刪除即可。

 

8-7-2 範例研討:查詢學生修課成績

(A) 程式功能:Ex8_7

請查詢90211302 同學這學期修讀了幾門課,印出課程名稱與分數。

(B) 系統分析:

此題目在第七章(Ex7_2) 練習過,利用混合查詢的缺點是必須及時連結臨時表格,如果我們產生一些暫存資料表來承接臨時表格的結果,就能解決此問題。我們依照下圖資料的流程,一步一步將結果存入暫存資料表。

(C) 程式範例:

Create Table temp1 AS

            Select open_course_ID, final

            From select_course

            Where student_ID = "90211302";

Select *

From temp1;

Create Table temp2 AS

             Select course_ID, final

              From temp1 as A, open_course as B

             Where A.open_course_ID =B.open_course_ID;

Select *

From temp2;

Select course_name, final

From temp2 as A, all_course as B

where A.course_ID = B.course_ID;

Drop table temp1;

Drop table temp2;

(D) 歸納完整程式:吾人將步驟 1 到步驟 3 的程式片段歸納如下:

create table temp1 as

       select open_course_ID, final

       from select_course

       where student_ID = "90211302";

      

create table temp2 as

       select A.course_ID, B.final

       from open_course as A, temp1 as B

       where A.open_course_ID = B.open_course_ID;

 

select course_name, final

from temp2 as C, all_course as D

where C.course_ID = D.course_ID;

 

drop table temp1;

drop table temp2;

 

8-8 綜合練習

請利用暫時資料表與混合查詢之整合運用,製作下列題目:

    1. 請查詢『進四資二丙』這學期開了哪些課程,請依照課程名稱、必選修、學分數與授課教師之順序印出。

    2. 請查詢粘添壽老師這學期所開的課程,請依照班級名稱、課程名稱、必選修、學分數等順序印出。

    3. 請查詢資訊管理系這學期開了哪些課程,請依課程名稱、班級名稱、授課教師、必選修與學分數之順序印出。