翻轉工作室:粘添壽
第八章 表格異動處理
SQL 語言除了提供資料查詢之外,也提供處理表格內容的相關命令,透過它們可以建立、刪除、變更表格,或插入、更新、移除、、等等處理資料表內容。這些命令我們在第三章有提到的 DDL 與 DML 語法,但沒有介紹到,這裡補充說明。另外,對於表格的異動處理,並非只限於一個表格。一個異動處理也許會牽涉到多個表格的內容變更,這方面比較困難,爾後較進階的課程再介紹,本章只介紹針對一個表格的資料異動處理。
8-1 建立表格 – Create table
看得出來,Create Table 是建立資料表的命令,可以指定幾個欄位、每個欄位的資料格式、指定該表格主鍵、或外來鍵等等,命令格式如下:
Create Table 資料表名稱 (欄位_1 資料型態 屬性, 欄位_2 資料型態 屬性, …, Primary Key (欄位_1, 欄位_2) ) |
其中資料型態,常用有:
字元:CHAR(長度),CHAR(8)。
整數:int(長度),int(8)。
日期:
另外,屬性有:
不可空白:Not null
Primary Key:指定主鍵,也許會包含兩個以上欄位。
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
當表格格式不滿足所需時,可以利用 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
將資料新增到表格則利用 Insert 命令來達成,格式如下:
Insert Into 資料表名稱 <欄位串列> Values (<資料串列>); |
8-3-2 範例研討:輸入監護人資料
(A) 程式功能:Ex8_3
請輸入監護人資料,如下:
學號:90211304 、監護人:劉媽媽、電話:0923111223、地址:高雄市苓雅區 20 號、關係:母女。
學號:90211305 、監護人:張先生、電話:0923111277、地址:高雄市苓雅區 30 號、關係:夫妻。
學號: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
新資料取代現有資料表的內容,稱之為『更新』(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
刪除資料表中某一筆或多筆記錄則用 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
當表格格式不需要時,可以利用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-2 範例研討:查詢學生修課成績
(A) 程式功能:Ex8_7
請查詢90211302 同學這學期修讀了幾門課,印出課程名稱與分數。
(B) 系統分析:
此題目在第七章(Ex7_2) 練習過,利用混合查詢的缺點是必須及時連結臨時表格,如果我們產生一些暫存資料表來承接臨時表格的結果,就能解決此問題。我們依照下圖資料的流程,一步一步將結果存入暫存資料表。
(C) 程式範例:
步驟 1:首先須由 select_course 表內查詢出該學生修了那些課,以及成績,並建立 temp1 = {open_course_ID, final},將查詢結果存入其中。
Create Table temp1 AS Select open_course_ID, final From select_course Where student_ID = "90211302"; Select * From temp1; |
步驟 2:接著,將 open_course 與 temp1 表格合併,查詢出該生修課的課程編碼(course_ID) 與成績,並產生 temp2 表將其結果存入,如下:
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; |
步驟 3:接著將 temp2 與 all_course 表格合併,查詢出課程名稱與修課成績。最後將 temp1 與 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 綜合練習
請利用暫時資料表與混合查詢之整合運用,製作下列題目:
請查詢『進四資二丙』這學期開了哪些課程,請依照課程名稱、必選修、學分數與授課教師之順序印出。
請查詢粘添壽老師這學期所開的課程,請依照班級名稱、課程名稱、必選修、學分數等順序印出。
請查詢資訊管理系這學期開了哪些課程,請依課程名稱、班級名稱、授課教師、必選修與學分數之順序印出。