Oracle--使用DML语句处理数据及控制事务

Love The Way You Lie 2021-08-13 20:27 355阅读 0赞

### 数据操作语言 ###

DML（Data Manipulation Language - 数据操作语言）可以在下列条件执行：

*  向表中插入数据
 *  修改现存数据
 *  删除现存数据

事务是由完成若干项工作的DML语句组成的

##### 插入数据 #####

insert语句语法：

insert into table [(column [, column...]}]
    values (value [, value...]);

使用这种语法一次只能向表中插入 一 条 \\color\{red\}\{一条\} 一条数据。

*  为每一列添加一个新值。
 *  按列的默认顺序列出各个列的值。
 *  在insert子句中随意列出列名和它们的值。
 *   字 符 和 日 期 型 数 据 应 包 含 在 单 引 号 中 。 \\color\{red\}\{字符和日期型数据应包含在单引号中。\} 字符和日期型数据应包含在单引号中。

insert into departments(department_id, department_name, manager_id, location_id)
    values(17, 'Public Relations', 100, 1700);
    
    1 row created.

###### 向表中插入空值 ######

*  隐式方式：在列名表中省略该列的值。

insert into department (department_id, department_name)
    values (30, 'Purchasing');
    
    1 row created.

*  显示方式：在VALUES子句中指定空值。

insert into departments
    values (100, 'Finance', NULL, NULL);
    
    1 row created.

###### 插入指定的值 ######

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

加入新成员

insert into employees
    values (114, 'Den', 'Raphealy', 'DRAPHEAL', '515.127.4561', TO_DATE('FEB 3,1999', 'MON DD, YYYY'), 'AC_ACCOUNT', 11000, NULL, 100, 30);
    
    1 row created.

###### 创建脚本 ######

*  在SQL语句中使用 & 变量指定列值。
 *  & 变量放在VALUES子句中。

insert into departments (department_id, department_name, location_id)
    values(&department_id, '&department_name', &location);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]

###### 从其它表中拷贝数据 ######

*  在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%';
    
    4 rows created.

*  不必书写VALUES子句
 *  子查询中的值列表应与INSERT子句中的列名对应

###### 更新数据 ######

*  使用UPDATE语句更新数据

update table
    set column = value[, column = value, ...]
    [where condition];

*  可以一次更新多条数据
 *  使用where子句指定需要更新的数据

update employees
    set department id = 70
    where employee_id = 113;
    
    1 row updated.

*  如果省略where子句，则表中的所有数据都将被更新

update copy_emp
    set department_id = 110;
    
    22 rows updated.

###### 在update语句使用子查询 ######

示例：更新114号员工的工作和工资使其与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 = 114;
    
    1 row updated.

###### 在update语句中使用子查询 ######

*  在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);
    
    1 row updated.

###### 更新中的数据完整性错误 ######

update employees
    set department_id = 55
    where department_id = 110;

![在这里插入图片描述][20190913173644203.png]  
不存在55号部门

###### 使用DELETE语句从表中删除数据 ######

delete [from] table
    [where condition];

*  使用where子句指定删除的记录

delete from departments
    where department_name = 'Finace';
    
    1 row deleted.

*  如果省略where子句，则表中的全部数据将被删除

delete from copy_emp;
    
    22 rows deleted.

###### 在DELETE中使用子查询 ######

*  在delete中使用子查询，使删除基于另一个表中的数据

delete from employees
    where department_id = (select department_id
    	from departments
    	where department_name like '%Public%');
    
    1 row deleted.

###### 删除中的数据完整性错误 ######

delete from departments
    where department_id = 60;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 1]

#### Delete 和 Truncate ####

*  都是删除表中的数据
 *  Delete操作可以rollback，可以闪回
 *  Delete操作可能产生碎片，并且不释放空间
 *  Truncate：清空表

#### 控制事务 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 2]

###### 回滚到保留点 ######

*  使用  S A V E P O I N T \\color\{red\}\{SAVEPOINT\} SAVEPOINT 语句在当前事务中创建保存点
 *  使用 R O L L B A C K \\color\{red\}\{ROLLBACK\} ROLLBACK  T O \\color\{red\}\{TO\} TO  S A V E P O I N T \\color\{red\}\{SAVEPOINT\} SAVEPOINT 语句回滚到创建的保存点

update...
    savepoint update done;
    
    Savepoint created.
    
    insert...
    rollback to update_done;
    
    Rollback complete.

###### 数据库的隔离级别 ######

*  对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题：
    
     *  脏读：  
        对于两个事务T1，T2， T1读取了已经被T2更新但还没有提交的字段。之后，若T2回滚，T1读取的内容就是临时且无效的。
     *  不可重复读：  
        对于两个事务T1， T2， T1读取了一个字段，然后T2更新了该字段。之后，T1再次读取同一个字段，值就不同了。
     *  幻读：  
        对于两个事务T1，T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行。之后，如果T1再次读取同一个表，就会多出几行。
 *  数据库事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力，是他们不会相互影响，避免各种并发问题。
 *  一个事务与其它事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性越好，但并发性越弱。

###### 数据库提供的4种事务隔离级别 ######

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 3]

*  Oracle支持的2种事务隔离级别：

1.  READ COMMITED
2.  SERIALIZABLE

Oracle默认的事务隔离级别为：READ COMMITED  
MySQL默认的事务隔离级别为：REPEATABLE READ

#### 总结 ####

<table> 
 <thead> 
  <tr> 
   <th align="center">语句</th> 
   <th align="center">功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">INSERT</td> 
   <td align="center">插入</td> 
  </tr> 
  <tr> 
   <td align="center">UPDATE</td> 
   <td align="center">修正</td> 
  </tr> 
  <tr> 
   <td align="center">DELETE</td> 
   <td align="center">删除</td> 
  </tr> 
  <tr> 
   <td align="center">COMMIT</td> 
   <td align="center">提交</td> 
  </tr> 
  <tr> 
   <td align="center">SAVEPOINT</td> 
   <td align="center">保存点</td> 
  </tr> 
  <tr> 
   <td align="center">ROLLBACK</td> 
   <td align="center">回滚</td> 
  </tr> 
 </tbody> 
</table>

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]: /images/20210813/62fcf341a29f47928f71ce133b143657.png
[20190913173644203.png]: /images/20210813/2d3014ff61e0463d92efdd01d5155cc6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 1]: /images/20210813/2fab77ba0e4b431da4e032d42fbbee6b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 2]: /images/20210813/0e32f1a447344ccd96f29647ef8a3020.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 3]: /images/20210813/b2c93ab217f54252a6d85edfde9f91ce.png