SQL 基礎之DML 數據處理（十三）

數據操作語言

DML 可以在下列條件下執行:

– 向表中插入數據

– 修改現存數據

– 刪除現存數據

事務是由完成若干項工作的DML語句組成的

表中添加新的數據– INSERT 語句

使用 INSERT 語句向表中插入數據

insert into table [(column [, column...])]

values (value [, value...]);

使用這種語法一次只能向表中插入一條數據

為每一列添加一個新值

• 按列的默認順序列出各個列的值

• 在 INSERT 子句中隨意列出列名和他們的值

• 字符和日期型數據應包含在單引號中

insert into departments(department_id,department_name,manager_id,location_id) values(304,'System Control',202,1900);

隱式方式: 在列名表中省略該列的值。

insert into departments (department_id,department_name) values (30, 'purchasing');

顯示方式: 在VALUES 子句中指定空值。

insert into departments values (100, 'finance', null, null);

SYSDATE 函數記錄當前系統的日期和時間。

INSERT INTO employees (employee_id,

first_name, last_name,

email, phone_number,

hire_date, job_id, salary,

commission_pct, manager_id,

department_id)

VALUES (113,

'Louis', 'Popp',

'LPOPP', '515.124.4567',

SYSDATE, 'AC_ACCOUNT', 6900,

NULL, 205, 110);

添加新員工

INSERT INTO employees

VALUES (114,

'Den', 'Raphealy',

'DRAPHEAL', '515.127.4561',

TO_DATE('FEB 3, 1999', 'MON DD, YYYY'),

'SA_REP', 11000, 0.2, 100, 60);

創建腳本

在SQL 語句中使用 & 變量指定列值。

& 變量放在VALUES子句中。

insert into departments

(department_id, department_name, location_id)

values (&department_id, '&department_name',&location);

從其它表中拷貝數據

不必書寫 VALUES 子句。

子查詢中的值列表應于 INSERT 子句中的列名對應。

insert into sales_reps (id, name, salary, commission_pct)

select employee_id, last_name, salary, commission_pct

from employees

where job_id like '%REP%';

修改數據

使用 UPDATE 語句更新數據,可以一次更新多條數據（如果有需求）。

update table

set column = value [, column = value, ...]

[where  condition];

使用 WHERE 子句指定需要更新的數據:

update employees

set department_id = 50

where employee_id = 113;

如果省略WHERE子句，則表中的所有數據都將被更新:

update  copy_emp

set department_id = 110;

指定 column_name= NULL 更新一列的值為 NULL.

使用子查詢更新兩列

更新 113號員工的工作和工資使其與 205號員工相同

update employees

set job_id = (select job_id

from employees

where employee_id = 205),

salary = (select salary

from employees

where employee_id = 205)

where employee_id = 113;

基于另一張表更新數據

使用UPDATE 子查詢，更新為基于另一張表中的數據

update copy_emp

set department_id = (select department_id

from employees

where employee_id = 100)

where job_id = (select job_id

from employees

where employee_id = 200);

從表中刪除數據

使用DELETE 語句從表中刪除數據

delete [from] table [where condition];

使用WHERE 子句指定刪除的記錄

delete from departments  where department_name = 'finance';

如果省略WHERE子句，則表中的全部數據將被刪除：

delete from copy_emp;

基于另一張表刪除數據

delete from employees 

where department_id = (select department_id

from departments

where department_name

like '%public%');

TRUNCATE  語句

從表中刪除所有的行，保留了空表和完成的表結構。

數據定義語言 (DDL) ，不是DML語句，不能使用撤銷

語法：

TRUNCATE TABLE table_name;

示例：

TRUNCATE TABLE copy_emp;

數據庫事務控制語句 COMMIT, ROLLBACK,  和 SAVEPOINT

數據庫事務由以下的部分組成:

一個或多個DML 語句

一個 DDL 語句

一個 DCL 語句

數據庫事務：開始和結束

以第一個 DML 語句的執行作為開始

以下面的其中之一作為結束:

– COMMIT 或 ROLLBACK 語句

– DDL 或 DCL 語句（自動提交）

– SQL Developer or SQL*Plus用戶退出

– 系統崩潰

COMMIT 和ROLLBACK 語句的優點

使用COMMIT 和 ROLLBACK語句,我們可以:

• 確保數據完整性。

• 數據改變被提交之前預覽。

• 將邏輯上相關的操作分組。

回滾到保存點

• 使用 SAVEPOINT 語句在當前事務中創建保存點。

• 使用 ROLLBACK TO SAVEPOINT 語句回滾到創建的保存點。

UPDATE...

SAVEPOINT update_done;

INSERT...

ROLLBACK TO update_done;

隱式事務處理

自動提交在以下情況中執行:

– DDL 語句。

– DCL 語句。

– 不使用 COMMIT 或 ROLLBACK 語句提交或回滾，正常結束會話。

會話異常結束或系統異常會導致自動回滾。

提交或回滾前的數據狀態

改變前的數據狀態是可以恢復的

執行 DML 操作的用戶可以通過 SELECT 語句查詢之前的修正

其他用戶不能看到當前用戶所做的改變，直到當前用戶結束事務。

DML語句所涉及到的行被鎖定， 其他用戶不能操作。

COMMIT 后數據的狀態

數據的改變被保存在數據庫中。

以前的數據被覆蓋。

所有用戶都可以查看結果。

受影響的行上的鎖被釋放，可供其他用戶來操作的那些行。

所有保存點都將被刪除。

修改數據：

delete from employees

where employee_id = 99999;

insert into departments

values (290, 'corporate tax', null, 1700);

提交修改：

commit;

回滾后數據狀態

使用 ROLLBACK 語句可使數據變化失效：

• 數據更改都會被撤消。

• 數據恢復到以前的狀態。

• 鎖被釋放。

delete from copy_emp;

rollback ;

回滾后數據狀態：示例

delete from test;

25,000 rows deleted.

rollback;

rollback complete.

delete from test where id = 100;

1 row deleted.

select * from test where id = 100;

no rows selected.

commit;

commit complete.

語句級回滾

單獨 DML 語句執行失敗時，只有該語句被回滾。

Oracle 服務器自動創建一個隱式的保留點。

其他數據改變仍被保留。

用戶應執行 COMMIT 或 ROLLBACK 語句結束事務。

讀一致性

讀一致性為數據提供一個一致的視圖

一個用戶的對數據的改變不會影響其他用戶的改變

對于相同的數據讀一致性保證:

– 查詢不等待修改。

– 修改不等待查詢。

– 修改等待修改。

SELECT 語句中的 FOR UPDATE  子句

• 鎖定 EMPLOYEES 表中 job_id 為 SA_REP 的行。

select employee_id, salary, commission_pct, job_id

from employees

where job_id = 'SA_REP'

for update

order by employee_id;

• 當你發出 ROLLBACK 或 COMMIT 命令，鎖即被釋放。

• 如果 SELECT 語句試圖鎖定被另一個用戶鎖定的行，那么數據庫將等待，直到該行可用，才返回 SELECT 語句的結果。

FOR UPDATE  子句 示列

您可以在 SELECT 語句多表查詢時使用FOR UPDATE 子句。

select e.employee_id, e.salary, e.commission_pct

from employees e join departments d

using (department_id)

where job_id like 'st_clerk'

and location_id = 1500

for update

order by e.employee_id;

表 EMPLOYEES 和表 DEPARTMENTS 的行都被鎖定。

使用 FOR UPDATE 的列名您有資格修改，僅查詢的行被鎖定。