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

139次閱讀

沒有評論

共計 4193 個字符，預(yù)計需要花費 11 分鐘才能閱讀完成。

自動寫代碼機(jī)器人，免費開通

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

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

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

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

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

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

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

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

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

values (value [, value…]);

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

為每一列添加一個新值

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

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

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

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)的日期和時間。

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 子句中的列名對應(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 號員工的工作和工資使其與 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;

基于另一張表更新數(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ù)由以下的部分組成:

一個或多個 DML 語句

一個 DDL 語句

一個 DCL 語句

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

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

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

– COMMIT 或 ROLLBACK 語句

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

– SQL Developer or SQL*Plus 用戶退出

– 系統(tǒng)崩潰

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

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

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

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

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

回滾到保存點

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

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

UPDATE…

SAVEPOINT update_done;

INSERT…

ROLLBACK TO update_done;

隱式事務(wù)處理

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

– DDL 語句。

– DCL 語句。

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

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

提交或回滾前的數(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é)果。

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

所有保存點都將被刪除。

修改數(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ù)更改都會被撤消。

數(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.

語句級回滾

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

Oracle 服務(wù)器自動創(chuàng)建一個隱式的保留點。

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

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

讀一致性

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

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

對于相同的數(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 語句試圖鎖定被另一個用戶鎖定的行，那么數(shù)據(jù)庫將等待，直到該行可用，才返回 SELECT 語句的結(jié)果。

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 的列名您有資格修改，僅查詢的行被鎖定。

命令注釋 INSERT 插入行信息 UPDATE 更新信息 DELETE 刪除一行信息 TRUNCATE 刪除表中所有行內(nèi)容，但表結(jié)構(gòu)保存 COMMIT 將所有 pending 的狀態(tài)的都變成永久 SAVEPOINT 利用回滾創(chuàng)造的保存點 ROLLBACK 廢棄所有 pending 數(shù)據(jù)的該表
FOR UPDATE clause in SELECT 鎖住 select 查詢行，知道 select 結(jié)束才釋放

向 AI 問一下細(xì)節(jié)

丸趣 TV 網(wǎng) – 提供最優(yōu)質(zhì)的資源集合！

正文完