DML与事务控制

数据库操作语言DML

数据操作语言DML(Data Manipulation Language)用于操作数据库中的数据,包括:

出入新数据

修改已有的数据

删除不再需要的数据

数据合并

insert语句

insert语句用于向表中插入数据

语法:

insert into table[(column[,column...])]
values(value[,value...])
-----------------------------------------------------------------
insert into dept values(88,'市场部','北京');
insert into dept (deptno,dname) values(99,'技术部');

说明:

insert语句每次只能向表中插入一条记录

缺省字段名列表时,应为新插入记录中的每个字段设定新值

也可在insert语句中指定赋值字段列表只为部分字段显式设定新增,其余字段被缺省赋值为null

表间数据拷贝

可以在insert语句中使用子查询,实现表间数据拷贝:

insert into depta(id,name) select deptno,dname from dept;

说明:

此时不必再给出values子句

子查询中的值列表应与insert子句中的字段列表相对应

updata语句

updata语句用于更新表中数据

update   table
set column = value[,column=value,...]
[where condition];
--------------------------------------------------------------
update emp set sal=sal+88;
update emp set sal=sal+1000,comm=comm+0.7
where empno=7778;

说明:

update语句每次可更新多条记录

可以使用where子句限定要更新的记录,如果缺省where子句,则更新表中的所有记录

delete语句

delete语句用于从表中删除数据

delete [from] table
[where condition];
---------------------------------------------------
delete emp;
delete from emp;
delete emp where empno=7778;
 

说明:

delete语句每次可以删除多条记录

可使用where子句限定要删除的记录,如果缺省where子句,则删除表中的所有记录

merge语句

merge语句用于进行数据合并——根据条件在表中执行行数据的修改或插入操作,如果要插入的记录在目标表中已经存在,则执行更新操作,否则执行插入.

merge into table [alias]
using (table | view | sub_query) [alias]
on(join_condition)
when matched then
         update set col1=col1_val,col2=col2_val
when not matched then
         insert(column_list)values(column_values);

 

create table test1(
eid number(10),
name varchar2(20),
birth date,
salary number(8,2)
);
insert into test1 values(1000,'张三','20-5-76',3300);
insert into test1 values(1001,'李四','12-3-90',6200);

create table test2(eid number(10),name varchar2(20),birth date,salary number(8,2));

select * from test2;

merge into test2
using test1
on(test1.eid=test2.eid)
when matched then
update set name=test1.name,birth,salary=test1.salary
when not matched then
insert(eid,name,birth,salary)values(test1.eid,test1.name,test1.birth,test1.salary);

select * from test2;

事务控制 

事务(Transaction)

组成单个逻辑工作单元的一系列操作被称为事务。

数据库事务组成

0~多条DML语句

1条DDL(Data Define Language)语句

1条DCL(Data Control Language)语句

事务控制

通过将一组相关操作组合为一个要么全部成功,要么全部失败的逻辑工作单元,以简化错误恢复,提高应用程序的可靠性.

事务必须满足的ACID属性

原子性(Atomicity)

一致性(Consistency)

隔离性(Isolation)

持久性(Durability)

事务的开始和结束

事务的开始于第一条可执行语句

事务结束:

遇到commit或rollback语句

遇到DDL或DCL语句

用户会话结束

系统崩溃

事务的提交和回滚

显式的提交和回滚操作时为了更好地保证数据的一致性.

下述情况下事务会被自动提交

执行一个DDL语句

执行一个DCL语句

正常结束会话

会话异常终止/系统崩溃时事务会被自动回滚

SqlPlus的自动提交

Sql Plus执行sql语句时可以设置是否自动提交,缺省设置为非自动提交.

查看设置:

show autocommit;

     autocommit OFF

     autocommit IMMEDIATE

改变设置:

set autocommit on;

set autocommit off;

提交/回滚前数据状态

• 当前事务中数据状态的改变时可以恢复的
• 当前事务中的DML操作结果只对当前用户(会话)可见,其他用户(会话)看不到当前事务中数据状态的改变,直到当前事务结束.
• 当前事务中DML语句所涉及到的行辈锁定,其他用户(会话)不能对其进行修改操作.

事务提交后数据状态

• 数据的修改永久生效,不可再撤销
• 数据以前的状态永久性丢失,无法恢复
• 所有的用户(会话)都将看到操作后的结果
• 记录锁被释放,其他用户对这些进行修改操作
• 事务中的保存点(Savepoints)被清除

事务回滚后数据状态

• 数据的修改被撤销
• 数据恢复到修改前的状态
• 记录锁被释放

保存点

通过保存点在当前事务中创建标记,将来可回退到指定的标记(保存点)处,实现事务的部分回滚.

insert into dept values(12,'Peter','Beijing');
insert into dept values(22,'Mark','Shanghai');
savepoint p1;
insert into dept values(44,'Tom','Shenzhen');
------------------
select  * from dept;

rollback to p1;
select * from dept;