oracle的数据更新、数据伪列、事务处理

朴灿烈づ我的快乐病毒、 2022-04-25 08:02 180阅读 0赞

### 1、数据更新 ###

如果要修改表之前的数据，则可以采用如下的语法完成：

<table style="width:568px;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">Update 表名称 set 字段=值,字段=值,...[where 更新条件(s)];</p> </td> 
  </tr> 
 </tbody> 
</table>

如果在编写update语句的时候，没有编写任何的更新条件，则可以表示更新表中的全部数据。

范例：要求将smith的工资修改为5500，佣金修改为5000；

<table style="width:568px;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">Update myemp set sal = 5500,comm=5000 where ename=’smith’;</p> </td> 
  </tr> 
 </tbody> 
</table>

范例：将所有的销售人员的工资统一修改为2000；

<table style="width:568px;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">Update myemp set sal =2000 where job = ‘salesman’;</p> </td> 
  </tr> 
 </tbody> 
</table>

实际上在进行所有数据更新的时候，都会返回数据的更新条数，如果是增加，则会显示影响的条数。在以后编写程序的时候，就可以通过是否有更新行数来判断某一个更新操作是否成功。

范例：将最早雇佣的员工的工资提升10%；

<table style="width:568px;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">Update myemp set sal=sal*1.1</p> <p style="margin-left:0cm;">Where hiredate=(select min(hiredate) from myemp);</p> </td> 
  </tr> 
 </tbody> 
</table>

范例：将公司工资最低的雇佣的工资修改为公司的平均工资；

1、知道公司的平均工资

<table style="width:568px;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">Select avg(sal) from myemp;</p> </td> 
  </tr> 
 </tbody> 
</table>

1.  以上返回的数值作为sal的内容

<table style="width:568px;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">Update myemp set sal=(select avg(sal) from myemp where sal=(select min(sal) from myemp));</p> </td> 
  </tr> 
 </tbody> 
</table>

数据删除

当表中的数据行不再需要的时候，就可以直接利用如下的语法删除：

<table style="width:568px;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">Delete from 表名称 [where 删除条件(s)];</p> </td> 
  </tr> 
 </tbody> 
</table>

范例：删除掉公司工资最高的员工

<table style="width:568px;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">Delete from myemp</p> <p style="margin-left:0cm;">Where sal=(select max(sal) from myemp);</p> </td> 
  </tr> 
 </tbody> 
</table>

范例：删除掉那些没有领导的员工

<table style="width:568px;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">Delete from emp where mgr is null;</p> </td> 
  </tr> 
 </tbody> 
</table>

范例：所有员工删除

<table style="width:568px;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">Delete from myemp;</p> </td> 
  </tr> 
 </tbody> 
</table>

与修改删除一样，很少有人干这样的事情，而且多提示一句，在实际的工作之中，很少有人去执行这样的物理删除。

说明：关于数据的删除操作：

·在实际的工作之中，对于有用的真实数据还是需要保留的，所以对于数据轻易不会删除；

·如果真的存在删除数据的一个功能，那么往往有两种方式：

|- 数据的物理删除：直接执行delete语句，删除所有的数据；

|- 数据的逻辑删除：增加一个列，用来对要删除的列打上一个删除的标记，通常使用is\_delete字段标示行记录是不是被删除，在逻辑上的数据删除是被删除的，但是数据的本身是存在的。

### 2、数据伪列 ###

两个数据伪列指的是不存在却又可直接使用的列，则在oracle里边除了sysdate外还存在ROWNUM和ROWID两个与行记录有关的伪列。

行号：rownum

在进行数据的显示时，用户可以通过rownum这个伪列，动态的生成行的编号。

范例：观察rownum

<table style="width:568px;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">Select rownum ,empno,ename,job,hiredate from emp;</p> </td> 
  </tr> 
 </tbody> 
</table>

范例：查询第一行数据

<table style="width:568px;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">Select rownum,empno,ename,job,hiredate from emp</p> <p style="margin-left:0cm;">Where deptno =10 and rownum = 1;</p> </td> 
  </tr> 
 </tbody> 
</table>

范例：查询前n行记录

除了到行和列的前N行之外，所有的操作都是无法使用的，但是既然它已经可以取得了前N行记录，之中就可以利用rownum实现数据的分页的操作。

范例：数据的分页显示格式

<table style="width:568px;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">Select * from (</p> <p style="margin-left:0cm;">Select 列[别名],列[别名].....rownum rn</p> <p style="margin-left:0cm;">From 表名称[别名]</p> <p style="margin-left:0cm;">Where rownum&lt;=currentPage* lineSize temp</p> <p style="margin-left:0cm;">Where temp.rn&gt;(currentPage-1)*lineSize;</p> <p style="margin-left:0cm;">)</p> </td> 
  </tr> 
 </tbody> 
</table>

范例：取出emp表中的1-5行记录（currentPage=1，linesize=5）

<table style="width:568px;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">Select * from (</p> <p style="margin-left:0cm;">&nbsp; Select empno,ename,job,rownum rn</p> <p style="margin-left:0cm;">&nbsp; From emp</p> <p style="margin-left:0cm;">&nbsp; Where rownum&lt;=5) temp</p> <p style="margin-left:0cm;">Where temp.rn&gt;0;</p> <p style="margin-left:0cm;">)</p> </td> 
  </tr> 
 </tbody> 
</table>

行ID：rowid

现在每一行的记录都发现自己的数据列，而除了这些数据列之外，还存在有每一行的数据的唯一的一个物理地址通过rowid取得。

那么每一个rowid的数据都是包含存储数据的，以：”AAAR3qAAEAAAACHAAC”为例：

AAAR3q：数据的对象编号

AAE：数据保存的相对文件的编号

AAAACH：数据保存的块号

AAC：保存的数据行号

数据库中所有的数据都是保存在磁盘中。所以会根据不同的数据分配不同的空间。而rowid就是记录着这些空间信息。

面试题：现在有一张数据表，由于设计的时候缺少些限制，同时后期使用的过程中出现了大量的重复数据删除掉，只保留最原始增加的数据。

准备过程：

1.  为了方便观察问题，首先将dept表复制为mydept表；
2.  观察现在的mydept表之中的数据和rowid（这个时候就是最重要的保存数据）

<table style="width:568px;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">Create table mydetp as select * from dept;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">Select rowid,deptno,dname,loc from mydept;</p> </td> 
  </tr> 
 </tbody> 
</table>

1.  向mydept表中增加重复数据

<table style="width:568px;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">Insert into mydept(deptno,dname,job) values(10,’accounting’,’new york’);</p> <p style="margin-left:0cm;">Insert into mydept(deptno,dname,job) values(10,’accounting’,’new york’);</p> <p style="margin-left:0cm;">Insert into mydept(deptno,dname,job) values(30,’sales’,’chicago’);</p> <p style="margin-left:0cm;">Insert into mydept(deptno,dname,job) values(30,’sales’,’chicago’);</p> <p style="margin-left:0cm;">Insert into mydept(deptno,dname,job) values(30,’sales’,’chicago’);</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:426.1pt;"> <p style="margin-left:0cm;">Delete form mydept where rowid not in(</p> <p style="margin-left:0cm;">&nbsp; Select min(rowid)</p> <p style="margin-left:0cm;">&nbsp; From mydept</p> <p style="margin-left:0cm;">&nbsp; Group by deptno,dname,loc</p> <p style="margin-left:0cm;">)</p> </td> 
  </tr> 
 </tbody> 
</table>

在以后的分析中，rowid依然会出现，只需要记住，rowid就像是身份证一样，是作为一行具体数据标记出现的。

### 3、事务处理 ###

A同学准备给B同学打款100，去银行汇款的过程中

第一步：将A同学的账户减少100；

第二步：在B同学的账户增加100；

第三步：支付本次操作打款所需要的服务费50。

但是如果本次操作之中第三步A同学拒绝执行，那么银行会认为之前所有的操作都应该回到最原始的状态。这三步操作同时成功或者同时失败，这样的控制行为在数据库中成为事务。

在用户进行数据更新操作事务一定会起作用。保护数据的完整性、一致性。

·事务的提交：commit-是真正想数据库之中发出更新指令；

·事务的回滚：rollback-回滚到最初的状态；

一旦用户发出了提交事务的操作指令，那么所有的数据将被真实性的执行了更新操作。

在oracle之中，每一个连接到数据库上的用户，都是用一个session的概念表示，对每一个用户的操作，显示数据的隔离性。

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Update myemp set sal=9999 where empno=7499;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">第一个session执行此语句的时候可以正常完成，同时，此时并没有进行事务的提交。</p> <p style="margin-left:0cm;">第二个session执行如下语句：</p> <p style="margin-left:0cm;">Update myemp set sal=9999 where empn7499;</p> <p style="margin-left:0cm;">可以发现，此时的界面进入了等待的状态，此为数据的隔离性。表示：第一个session事务未处理完成之前，其事务进入了一个锁的状态。</p> </td> 
  </tr> 
 </tbody> 
</table>

## ##