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 的列名您有资格修改，仅查询的行被锁定。