数据操作与事物控制

一、数据操作语言

• Date Manipulation Language，简称DML，主要用来实现对数据库表中的数据进行操作。
• 数据操作语言主要包括如下几种：

• 增加行数据：使用INSERT语句实现
• 修改行数据：使用UPDATE语句实现
• 删除行数据：使用DELETE语句实现
• 合并行数据：按照指定条件合并两个表的数据，使用MERGE语句实现

二、插入数据

• 使用INSERT语句向表中插入数据，语法结构如下：

INSERT INTO table [(column [, column...])] 
VALUES (value [, value...]);

• 采用这种语法一次只能追加一条记录；
• column部分叫做列名列表，value部分叫做值列表，列名列表和值列表必须在个数及数据类型上保持一致；
• 列名列表部分可以省略，如果省略，默认包括该表的所有列，列的顺序为使用 desc 表名 命令所查看的顺序；
• 列名列表部分也可以指定部分非空的列，注意值列表必须和列名列表对应；
• 字符和日期型数据必须要用单引号括起来。

• 插入多行数据

INSERT INTO表名[(列名1[,列名2，…，列名n])] 子查询 ；

• 不必书写values子句
• INSERT子句和数据类型必须和子查询中的列的数量和类型相匹配中列的数量

三、修改数据

• 修改数据使用UPDATE子句完成，语法

UPDATE table 
SET column = value [, column = value] 
[WHERE condition];

• WHERE子句用来限定修改哪些行，当WHERE子句忽略时修改所有行
• SET子句用来限定修改哪些列

• 嵌入子查询修改

• 把部门编号为10的员工，部门编号调整为20，工资在原有的基础上，增加所有人的平均工资

UPDATE emp 
SET deptno = 20,sal=sal+(select avg(sal）from emp)
WHERE deptno = 10;

• 修改记录时不能违反完整性约束

UPDATE emp 
SET deptno = 55 
WHERE deptno = 10;
//注意：违反完整性约束，55号部门根本不存在

• 相关UPDATE
  用一个相关子查询来更新在一个表中的行，该表中的行基于另一个表中的行

• 在emp表中增加一个列dname，来存储部门名称

ALTER TABLE emp 
ADD (dname VARCHAR2(14));

• 使用相关子查询更新dname列为正确的部门名称

UPDATE emp e 
SET dname =  
           (SELECT dname  
            FROM dept d 
            WHERE deptno = e.deptno);

四、删除数据

• 删除数据主要用来按照指定条件从表中删除某些行

1.使用DELETE语句删除表中满足条件的行记录

• 删除选中记录

DELETE  
FROM emp
WHERE job  = 'CLERK';  

• 删除全部记录

DELETE   
FROM emp;

2.在DELETE语句中使用子查询可以基于另一个表删除本表记录

DELETE FROM emp 
WHERE deptno =  
              (SELECT   deptno 
               FROM     dept 
               WHERE    dname ='SALES');

3.使用相关DELETE，你可以使用相关子查询来只删除那些已经存在于另一张表中的行

DELETE FROM dept d 
WHERE not exists 
                (SELECT empno 
                 FROM emp  
                 WHERE deptno = d.deptno);

五、事务处理语言

• 事务：也称工作单元，是由一个或多个SQL语句所组成的操作序列，这些SQL语句作为一个完整的工作单元，要么全部执行成功，要么全部执行失败。在数据库中，通过事务来保证数据的一致性。
• 事务处理语言：Transaction Process Language ,简称TPL，主要用来对组成事务的DML语句的操作结果进行确认或取消。确认也就是使DML操作生效，使用提交(COMMIT)命令实现；取消也就是使DML操作失效，使用回滚(ROLLBACK)命令实现。
• 通过事务的使用，能防止数据库中出现数据不一致现象。如两个银行账户进行转账，涉及到两条更新操作，这两条更新操作只允许全部成功或失败，否则数据会出现不一致的现象。

六、事务组成

• 在数据库中，事务是由一组相关的DML或SELECT语句，加上一个TPL语句（COMMIT、ROLLBACK）或一个DDL语句（CREAT、ALTER、DROP、TRUNCATE等）或一个DCL（CRANT、REVOKE）语句。

• 例如：
  如下语句组成两个事务。
  INSERT...
  UPDATE…
  DELETE…
  SELECT…
  INSERT…
  COMMIT; -- 前6条语句，组成第1个事务
  UPDATE…
  DELETE…
  CREATE….; --后3条语句，组成第2个事务

七、事务特性

• 事务特性可以用四个字母的缩写表示：即ACID

• 原子性（Atomicity）
  事务就像“原子”一样，不可被分割，组成事务的DML操作语句要么全成功，要么全失败，不可能出现部分成功部分失败的情况。
• 一致性（Consistency）
  一旦事务完成，不管是成功的，还是失败的，整个系统处于数据一致的状态。
• 隔离性（Isolation）
  一个事务的执行不会被另一个事务所干扰。比如两个人同时从一个账户从取钱，通过事务的隔离性确保账户余额的正确性。
• 持久性（Durability）
  也称为永久性，指事务一旦提交，对数据的改变就是永久的，不可以再被回滚。

八、事务结束

• 显示结束

• 提交（COMMIT）：使用COMMIT命令实现，以成功的方式结束事
  务，组成事务的DML语句操作全部生效。
• 回滚（ROLLBACK）：使用ROLLBACK命令实现，以失败的方式结束事
  务，组成事务的DML语句操作全部被取消。

• 隐式结束

隐式提交：当下列任意一种情况发生时，会发生隐式提交

• 执行一个DDL(CREATE、ALTER、DROP、TRUNCATE、RENAME）语句；
• 执行一个DCL(GRANT、REVOKE)语句；
• 从SQL*Plus正常退出（即使用EXIT或QUIT命令退出）；

隐式回滚：当下列任意一种情况发生时，会发生隐式回滚

• 从SQL*Plus中强行退出
• 客户端连接到服务器端异常中断
• 系统崩溃

九、事务开启

• 事务自动开启于上一个事务结束后，执行的第一个DML语句。

• 例如：
  COMMIT; --结束一个事务；
  SELECT…
  INSERT… --开启一个事务
  UPDATE...
  DELETE...
  SELECT...
  INSERT…
  COMMIT; --结束一个事务
  UPDATE… --开启一个事务
  DELETE…
  ROLLBACK? ;--结束一个事务

十、设置保存点

• 如果在一个事务内，想要回滚到指定的位置，不是回滚到事务的起始点，可以通过保存点（SAVEPOINT）来实现。

• SAVEPOINT savepointname 定义一个保存点语句
• ROLLBACK TO savepointname 回滚到指定的保存点
  注意：如上两条语句不结束事务的执行。

十一、锁

• 锁的概念

• 锁用来在多用户并发访问和操作数据库时，保证数据的一致性的一种机制
• 锁由oracle自动管理，如一个DML操作，ORACLE默认的机制是在DML操作影响的行记录上自动加锁
• 锁在被相关的操作申请并持有后，会一直保持到数据的结束，事务结束后，所才会被释放
• 查询语句不会被锁定任何记录，如果在查询语句后面加FORUPDARE子句会锁定查询所影响的行记录

十二、提交或回滚前数据状态

• 提交（COMMIT）或回滚（ROLLBACK）前的数据状态

• 数据变化前的状态可以被恢复
• 当前会话可以使用SELECT语句来验证DML操作后的结果
• 其他会话不能查看由当前用户的DML操作结果
• 受影响记录被锁定，也就是其他用户不能改变受影响记录中的数据

十三、提交后数据状态

• 提交（COMMIT）后的数据状态

• 在数据库中数据变化成永久性的，先前的数据状态永久性的消失
• 所有用户/会话都可以查询到提交COMMIT后的结果
• 锁定的记录被释放，可以有效的被其他用户操作
• 所有的存储节点被清除

十四、回滚后数据状态

• 回滚（ROLLBACK）后的数据状态

• 先前的数据状态被恢复
• 锁定的记录被释放
• 所有的存储节点被清除