CH 資料 (Data) 的操作和管理 簡介新增一筆資料 (INSERT) 使用 SYSDATE 函數查詢一筆資料 (where) 使用預設值 (Default values) 使用替代變數 (&) 使用 To_date 函數新增多筆資料更正資料 (UPDATE) 刪除資料 (Delete) 資料庫的交易 (Commit and Rollback) 複製資料
Oracle 11g 資料庫最佳入門實用書 6-1 簡介 在企業的資訊管理系統中, 經常會用到資料庫的基本管理功能, 包含了新增資料 更正資料 刪除資料和查詢資料, 這些動作基本上都是針對資料在做操作和管理, 我們整理 SQL 敘述的 5 大類型和相對應的命令, 如下表 : SQL Statements 五大類型命令說明 Data retrieval 資料取回 SELECT 查詢一筆或多筆資料 Data manipulation language (DML) 資料操作語言 Data definition language (DDL) 資料定義語言 INSERT UPDATE DELETE CREATE ALTER DROP RENAME TRUNCATE COMMIT 新增一筆或多筆資料 更正一筆或多筆資料 刪除一筆或多筆資料 建立表格的結構 (structure) 更改表格的結構 丟棄 ( 刪除 ) 表格 更改表格的名稱 清空表格 確認命令 Transaction control 交易控制 ROLLBACK 拉回至前一次確認的命令或存取點 SAVEPOINT 設定存取點 Data control language(dcl) GRANT 授與權利 資料控制語言 REVOKE 取消權利 基本的資料操作和管理會使用到 SQL 5 大類型中的 DML 資料操作語言 Data retrieval( 資料取回 ) 和 Transaction Control( 交易控制 ), 我們在新增一筆資料時, 會使用 Insert 命令 ; 更正一筆資料時, 會使用 Update 命令 ; 刪除一筆資料時, 會使用 Delete 命令 ; 查詢一筆資料時, 會使用 Select 命令, 在確認寫入一筆資料時, 會使用 Commit 命令, 在交易過程中, 需要記錄點時, 會使用 Savepoint 命令, 交易未完成或想回復尚未確定的動作時, 可以使用 Rollback 命令, 在進行資料的操作和管理之前, 我們需要先建立一個員工的表格 EMP, 其語法如下 : 6-2
Chapter 6 資料 (Data) 的操作和管理 CREATE TABLE EMP (EmpID VARCHAR2(10), EmpName VARCHAR2(10), EmpAdd VARCHAR2(20), EmpPhone VARCHAR2(12), EMail VARCHAR2(20), MGR VARCHAR2(6), HireDate DATE, COMM NUMBER(7,2), DeptNo NUMBER(2)); 我們以 System 帳戶登入 SQL Plus, 輸入建立 EMP 表格的命令後, 執行的結果如下 : EMP 表格建立完成 在建立一個表格 EMP 後, 我們介紹新增一筆資料 新增多筆資料 更正資料 刪除資料 等等, 資料的基本管理如後 6-2 新增一筆資料 (INSERT) 新增一筆資料到表格的語法如下 : INSERT INTO table [(column [,column...])] VALUES (Value [,Value..]); table: 表格的名稱 column: 表格的欄位名稱 VALUE: 存入表格中欄位的值 6-3
Oracle 11g 資料庫最佳入門實用書 我們以剛剛建立的 EMP 表格為例, 新增一筆資料如下 : EMPID EMPName EMPAdd EmpPhone Email 00101 Abel 台北市中正區 (02)-111111 xxx@yahoo.com.tw MGR HireDate Comm DepNo Abel to_date('06/05/90','dd/mm/yy') 500 11 完整的語句如下 : Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo) values('00101','abel',' 台北市中正區 ','(02)- 111111','xxx@yahoo.com.tw','Abel',to_date('06/05/90','dd/mm/yy'),'500','11'); 執行的結果如下 : 注意 在輸入資料時, 若是欄位和資料未對齊 符號打錯 格式錯誤, 都會導致新增資料失敗, 務必小心輸入 6-4
Chapter 6 資料 (Data) 的操作和管理 查詢所有資料 Select * from table_name 由於我們只新增一筆資料, 所以查詢所有的資料和查同一筆資料的結果是一樣的 6-3 使用 SYSDATE 函數 新增一筆資料, 含有系統日期 (SYSDATE), 我們在許多管理系統中, 需要新增資料的日期時間, 不需以手動方式輸入, 可以使用 SYSDATE 功能, 輸入的資料如下 : Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo) values ('00102','Ivan',' 台中市清水路 ','(04)-111111','xxx@yahoo.com.tw','Abel',SYSDATE,'200','11'); 6-5
Oracle 11g 資料庫最佳入門實用書 我們在 SQL Plus 中執行的結果如下圖 : 系統的時間 新增一筆資料成功 6-4 查詢一筆資料 (where) 我們查詢一筆資料, 如下圖 : 由 system 輸入 我們想查詢一筆資料時, 會使用 where 條件子句 select * from emp where empname='ivan'; 的意思是將員工名字等於 Ivan 的資料查詢出來 6-6
Chapter 6 資料 (Data) 的操作和管理 6-5 使用預設值 (Default values) 我們使用 DEFAULT 這個關鍵字來設定某欄位的值為預設值, 且允許使用者在任何時間 地點控制並應用於資料上 預設值 (Default Value) 可被用在 INSERT 和 UPDATE 敘述句上, 我們分別整理如後 6-5-1 預設值用於 INSERT 我們新增一筆員工資料, 並將部門編號設定為 Default, 執行結果如下圖 : 注意 由於預設值為 NULL, 因此部門編號為 NULL 若是要自行設定預設值 ( 即不等於 NULL), 則必須在新增表格時就設定好了, 例如 :Create table table_name (... deptno number(2) default 99...); 6-5-2 預設值用於 UPDATE 我們將更正部門編號為預設值, 條件是部門編號等於 11 首先, 我們先查詢部門編號 11 的資料, 如下圖 : 6-7
Oracle 11g 資料庫最佳入門實用書 更正的語法如下圖 : 再次查詢部門編號 11 的資料, 由於皆被設定為預設值 NULL, 因此沒有任何資料被選取 6-6 使用替代變數 (&) 我們使用替代變數 & 來新增資料, 如下圖 : 替代變數 輸入資料 6-8
Chapter 6 資料 (Data) 的操作和管理 我們想用互動方式, 要求使用者輸入資料時, 可以使用 & 符號在需要輸入值的欄位上, 例如 :&empid &empname, 使用者需要輸入 2 個值, 執行的結果如上圖 6-7 使用 To_date 函數 我們想在新增一筆資料時, 指定需要的日期, 可以使用 to_date 函數, 例如, 我們想指定日期為 92 年 2 月 5 日, 則是使用 to_date ('05/02/92','dd/mm/yy'), 請輸入資料如下 : Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo) values ('00202','Henry',' 桃園縣中山路 ','(03)-111112','xxx@yahoo.com.tw','Alan',to_date('05/02/92','dd/mm/yy'), '600','12'); 執行的結果如下圖 : 日月年 指定日期 6-9
Oracle 11g 資料庫最佳入門實用書 6-8 新增多筆資料 我們也可以一次新增多筆資料, 每筆資料後都需要加上 ; 符號, 以確認執行的命令列, 輸入資料如下 : 執行 Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate, COMM,DeptNo)values ('00301','Bard',' 台中市大雅路 ','(035)-11112','xxx@yahoo.com.tw','Abel',to_date('26/03/91','dd/mm/yy'),null,'21'); Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate, COMM,DeptNo)values ('00302','Harry',' 台南縣永康鄉 ','(04)-111112','xxx@yahoo.com.tw','Bard',to_date('15/06/91','dd/mm/yy'),null,'21'); Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate, COMM,DeptNo)values ('00303','Bart',' 高雄市中正路 ','(07)-111111','xxx@yahoo.com.tw','Harry',to_date('15/12/91','dd/mm/yy'),'400','21'); Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate, COMM,DeptNo)values ('00304','Eden',' 高雄市中山路 ','(07)-111112','xxx@yahoo.com.tw','Harry',to_date('08/01/92','dd/mm/yy'),'500','21'); Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate, COMM,DeptNo)values ('00305','Beau',' 台北市中正區 ','(02)-111111','xxx@yahoo.com.tw','Harry',to_date('16/01/92','dd/mm/yy'),'300','21'); 執行的結果如下圖 : 6-10
Chapter 6 資料 (Data) 的操作和管理 我們完成一次輸入多筆資料了! 6-9 更正資料 (UPDATE) 在管理系統中, 我們常常需要修改或更正資料, 例如 : 員工 Beau 搬家了, 那麼, 他家的地址 電話 等等都需要更正 ; 即使在公司內部, 當員工從工程部門移動到業務部門, 也是需要更正員工資料 我們整理更正 (UPDATE) 資料的語法如下 : UPDATE SET [where table column=value[,column=value,...] condition] table: 表格的名稱 column: 欄位名稱 value: 欄位對應的值 condition: 設定更改的條件我們以員工 Beau 換電話為例, 輸入資料如下 : update emp set empphone = '(02)22222222' where empname = 'Beau'; 6-11
Oracle 11g 資料庫最佳入門實用書 這是將符合員工名字等於 Beau 的資料, 其電話更正為 (02)22222222, 其執行的結果如下 : 查看 Beau 的資料 舊的電話資料 當我們更正完成後, 要如何確認資料已經更正了呢? 很簡單, 只要再查詢一次資料就可以了, 執行結果如下 : 更正為新的電話號碼 再次查詢 更新的電話號碼 注意 在使用更新資料時, 務必儘可能地加上條件 (where), 否則會更正表格內所有的資料, 例如 : 輸入資料 update emp set empphone = '(02)22222222'; 則會更正 EMP 表格中所有的電話資料為 (02)22222222, 請小心使用 6-12
Chapter 6 資料 (Data) 的操作和管理 6-10 刪除資料 (Delete) 在管理系統中, 我們也常常需要刪除資料, 例如 : 材料的料號不對, 員工離職 等等, 都必須刪除不需要的資料, 我們整理刪除 (Delete) 資料的語法如下 : DELETE [WHERE [FROM] table condition] table: 表格的名稱 condition: 設定刪除的條件我們以刪除員工名字 Beau 為例, 輸入資料如下 : delete from emp where empname = 'Beau'; 執行的結果如下 : 刪除 Beau 資料 再次查詢找不到了 注意 在使用刪除資料時, 務必加上條件 (where), 否則會刪除表格內所有的資料, 例如 : 輸入資料 delete from emp; 6-13
Oracle 11g 資料庫最佳入門實用書 執行的結果如下圖 : 刪除 emp 表格內所有資料 重新查詢 emp 表格 已經沒有任何資料了 6-11 資料庫的交易 (Commit and Rollback) Oracle 資料庫使用交易機制來確保資料的一致性, 當使用者或系統發生問題時, 就需要交易機制來確認先前發生的狀態, 資料庫的交易機制是發生在第一個 SQL 敘述執行時, 結束於有 (1)commit 或 rollback 命令執行時,(2)DDL 或 DCL 敘述執行時,(3) 使用者正常離開時, 唯有一個交易機制完成結束後, 另一個可執行的 SQL 敘述才會開始 簡單地說, 我們在執行 SQL 敘述時, 所變動的資料都只是暫存著, 只有結束資料庫交易時, 才會真正的寫入資料庫, 也就是說, 資料庫交易尚未完成時, 我們都可以將資料拉回交易前的狀態, 若是已經完成交易, 則無法回復交易前的狀況了 6-14
Chapter 6 資料 (Data) 的操作和管理 我們以新增一筆資料後, 立即確認寫入為例, 執行的結果如下圖 : 新增一筆資料 確認寫入 在 commit 確認寫入後, 我們就無法拉回交易前的狀態了, 我們再增加第二筆資料 Ivan, 現在資料庫中應該有 2 筆資料, 執行拉回 (rollback) 命令, 由於第二筆資料並未確認寫入, 所以 rollback 後, 再查詢所有資料, 只有第一筆資料存在, 執行的結果如下圖 : 新增第二筆資料 拉回 再次查詢 只有一筆資料 控制交易在一個交易機制的處理過程中, 我們可能已經進行新增 更正和刪除某些資料, 當我們想回到先前某個狀態, 也就是想控制交易時, 就需要設定存取點 Savepoint, 我們以下面範例來說明控制交易 6-15
Oracle 11g 資料庫最佳入門實用書 insert 1 insert 2 delete all data commit savepoint firstdata savepoint seconddata rollback to seconddata rollback to firstdata 我們新增一筆資料, 設定存取點 firstdata, 再增加第二筆資料, 設定存取點 seconddata, 刪除所有資料, 再拉回到第一個存取點 firstdata, 結果只有一筆資料存在 執行的結果如下 : 清空資料表格 新增第一筆資料 設定第一個存取點 6-16
Chapter 6 資料 (Data) 的操作和管理 新增第二筆資料 設定第二個存取點 刪除所有資料 拉回到第一個存取點 查詢所有資料 只有第一筆資料存在 我們已經完成資料交易的實務操作了 6-17
Oracle 11g 資料庫最佳入門實用書 6-12 複製資料 若我們要從既有的表格複製資料到新表格 ( 即是複製 A 表格的資料到 B 表格 ) 時, 此時要特別注意兩表格的結構, 因為這有可能會導致資料無法順利複製完成 複製資料的語法如下 : INSERT INTO table [ column (, column) ] subquery; table: 表格的名稱 column: 表格的欄位名稱 subquery: 回傳資料列到表格再複製資料之前, 我們必須將 6-8 節的新增多筆資料再做一次, 完成後則可繼續往下 我們先建立一個表格為 manager, 再將員工表格的員工編號 姓名 佣金 部門編號複製到 manager 的表格內, 條件是符合開頭為 003 的員工編號, 其輸入命令與執行結果如下圖 : 建立 manager 的表格 我們再來查詢有哪些資料被複製到 manager 表格裡, 執行結果如下圖 : 查詢 manager 內的資料 6-18