SQL 基礎(chǔ)之DML 數(shù)據(jù)處理（十三）

數(shù)據(jù)操作語言

DML 可以在下列條件下執(zhí)行:

– 向表中插入數(shù)據(jù)

– 修改現(xiàn)存數(shù)據(jù)

– 刪除現(xiàn)存數(shù)據(jù)

事務(wù)是由完成若干項(xiàng)工作的DML語句組成的

表中添加新的數(shù)據(jù)– INSERT 語句

使用 INSERT 語句向表中插入數(shù)據(jù)

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

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

使用這種語法一次只能向表中插入一條數(shù)據(jù)

為每一列添加一個(gè)新值

• 按列的默認(rèn)順序列出各個(gè)列的值

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

• 字符和日期型數(shù)據(jù)應(yīng)包含在單引號(hào)中

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 函數(shù)記錄當(dāng)前系統(tǒng)的日期和時(shí)間。

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);

創(chuàng)建腳本

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

& 變量放在VALUES子句中。

insert into departments

(department_id, department_name, location_id)

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

從其它表中拷貝數(shù)據(jù)

不必書寫 VALUES 子句。

子查詢中的值列表應(yīng)于 INSERT 子句中的列名對(duì)應(yīng)。

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

select employee_id, last_name, salary, commission_pct

from employees

where job_id like '%REP%';

修改數(shù)據(jù)

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

update table

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

[where  condition];

使用 WHERE 子句指定需要更新的數(shù)據(jù):

update employees

set department_id = 50

where employee_id = 113;

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

update  copy_emp

set department_id = 110;

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

使用子查詢更新兩列

更新 113號(hào)員工的工作和工資使其與 205號(hào)員工相同

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;

基于另一張表更新數(shù)據(jù)

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

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);

從表中刪除數(shù)據(jù)

使用DELETE 語句從表中刪除數(shù)據(jù)

delete [from] table [where condition];

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

delete from departments  where department_name = 'finance';

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

delete from copy_emp;

基于另一張表刪除數(shù)據(jù)

delete from employees 

where department_id = (select department_id

from departments

where department_name

like '%public%');

TRUNCATE  語句

從表中刪除所有的行，保留了空表和完成的表結(jié)構(gòu)。

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

語法：

TRUNCATE TABLE table_name;

示例：

TRUNCATE TABLE copy_emp;

數(shù)據(jù)庫事務(wù)控制語句 COMMIT, ROLLBACK,  和 SAVEPOINT

數(shù)據(jù)庫事務(wù)由以下的部分組成:

一個(gè)或多個(gè)DML 語句

一個(gè) DDL 語句

一個(gè) DCL 語句

數(shù)據(jù)庫事務(wù)：開始和結(jié)束

以第一個(gè) DML 語句的執(zhí)行作為開始

以下面的其中之一作為結(jié)束:

– COMMIT 或 ROLLBACK 語句

– DDL 或 DCL 語句（自動(dòng)提交）

– SQL Developer or SQL*Plus用戶退出

– 系統(tǒng)崩潰

COMMIT 和ROLLBACK 語句的優(yōu)點(diǎn)

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

• 確保數(shù)據(jù)完整性。

• 數(shù)據(jù)改變被提交之前預(yù)覽。

• 將邏輯上相關(guān)的操作分組。

回滾到保存點(diǎn)

• 使用 SAVEPOINT 語句在當(dāng)前事務(wù)中創(chuàng)建保存點(diǎn)。

• 使用 ROLLBACK TO SAVEPOINT 語句回滾到創(chuàng)建的保存點(diǎn)。

UPDATE...

SAVEPOINT update_done;

INSERT...

ROLLBACK TO update_done;

隱式事務(wù)處理

自動(dòng)提交在以下情況中執(zhí)行:

– DDL 語句。

– DCL 語句。

– 不使用 COMMIT 或 ROLLBACK 語句提交或回滾，正常結(jié)束會(huì)話。

會(huì)話異常結(jié)束或系統(tǒng)異常會(huì)導(dǎo)致自動(dòng)回滾。

提交或回滾前的數(shù)據(jù)狀態(tài)

改變前的數(shù)據(jù)狀態(tài)是可以恢復(fù)的

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

其他用戶不能看到當(dāng)前用戶所做的改變，直到當(dāng)前用戶結(jié)束事務(wù)。

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

COMMIT 后數(shù)據(jù)的狀態(tài)

數(shù)據(jù)的改變被保存在數(shù)據(jù)庫中。

以前的數(shù)據(jù)被覆蓋。

所有用戶都可以查看結(jié)果。

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

所有保存點(diǎn)都將被刪除。

修改數(shù)據(jù)：

delete from employees

where employee_id = 99999;

insert into departments

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

提交修改：

commit;

回滾后數(shù)據(jù)狀態(tài)

使用 ROLLBACK 語句可使數(shù)據(jù)變化失效：

• 數(shù)據(jù)更改都會(huì)被撤消。

• 數(shù)據(jù)恢復(fù)到以前的狀態(tài)。

• 鎖被釋放。

delete from copy_emp;

rollback ;

回滾后數(shù)據(jù)狀態(tài)：示例

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.

語句級(jí)回滾

單獨(dú) DML 語句執(zhí)行失敗時(shí)，只有該語句被回滾。

Oracle 服務(wù)器自動(dòng)創(chuàng)建一個(gè)隱式的保留點(diǎn)。

其他數(shù)據(jù)改變?nèi)员槐Ａ簟?/p>

用戶應(yīng)執(zhí)行 COMMIT 或 ROLLBACK 語句結(jié)束事務(wù)。

讀一致性

讀一致性為數(shù)據(jù)提供一個(gè)一致的視圖

一個(gè)用戶的對(duì)數(shù)據(jù)的改變不會(huì)影響其他用戶的改變

對(duì)于相同的數(shù)據(jù)讀一致性保證:

– 查詢不等待修改。

– 修改不等待查詢。

– 修改等待修改。

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;

• 當(dāng)你發(fā)出 ROLLBACK 或 COMMIT 命令，鎖即被釋放。

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

FOR UPDATE  子句 示列

您可以在 SELECT 語句多表查詢時(shí)使用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 的列名您有資格修改，僅查詢的行被鎖定。