oracle的序列、视图、同义词、索引、数据库备份、用户管理、范式整理

客官°小女子只卖身不卖艺 2022-04-25 08:46 361阅读 0赞

### 序列 ###

在许多数据库之中存在一种自动增长的概念。像是SQL Server、MySQL、DB2都提供有这样的方式。唯独在Oracle之中没有（Oracle 12C之后就提供了，之前没有），但是在Oracle之中提供的是一种手工的自动增长的方式。而这样的控制在Oracle之中使用序列对象完成。

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Create sequence 序列名称</p> <p style="margin-left:0cm;">[increment by 步长] [ start with 开始值 ]</p> <p style="margin-left:0cm;">[ maxvalue 最大值 ] [ nomaxvalue ]</p> <p style="margin-left:0cm;">[ minvalue&nbsp; 最小值] [ nominvalue ]</p> <p style="margin-left:0cm;">[ cycle | nocycle ]</p> <p style="margin-left:0cm;">[ cache 缓存大小| nocache ]</p> </td> 
  </tr> 
 </tbody> 
</table>

如果用户直接使用”create sequence 序列名称”创建的是一个默认序列，默认序列的数值的最小值从1开始，而且没有最大值，最小值为0。

Order:表示序列是否已经排好序

Cache\_size:序列的缓存数据

Last\_number:序列的最后一次增长值

当一个序列对象已经创建完成之后，那么下面就可以采用如下的两个伪列来进行序列的控制：

1.  取得序列下一个增长数据（每次调用序列值都增长指定的步长）：序列.nextval
2.  取得序列的当前数据（每次调用序列都不增长）:序列.currval;
3.  在使用currval之前一定要首先使用nextval，否则会出现”ORA-08002:序列MYSEQ.CURRVAL”

解释：关于user\_sequences数据字典之中LAST\_NUMBER和CACHE的作用

首先必须明确的是，LAST\_NUMBER并不是单签序列真正增长的数据。

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Select myseq.nextval from dual;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Select * from user_sequences;</p> </td> 
  </tr> 
 </tbody> 
</table>

实际上当用户每次进行数据增长控制的时候，LAST\_NUMBER都表示已经处理完的一批数据。当数值等于LAST\_NUMBER数据时，那么对自动在增加指定个“cache”大小的序列。

###      视图 ###

在之前接触过的sql之中，查询是最复杂的，尤其是编写复杂查询。而且在进行查询的时候，根据用户的查询需求，于是就有了这样一个问题，程序员人员重点是程序架构设计，但是如果程序员花时间去了解数据库的工作，这属于不正当的竞争，那么在数据库之中为了可以进行更好的工作分配，所以提供有视图的概念，就是包含了一条复杂的sql语句，而视图的创建语法如下。

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Create [ or replace ] view 视图名称</p> <p style="margin-left:0cm;">As</p> <p style="margin-left:0cm;">子查询</p> <p style="margin-left:0cm;">[ with check option ]&nbsp; [ with read only ]</p> </td> 
  </tr> 
 </tbody> 
</table>

注意：从Oracle 10g R2版本之后出现了一个创建视图的权限问题，在这之前，scott用户本身是具有创建视图的权限，但是之后就没有了，那么下面首先执行如下的语句，来实现权限的分配。

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Conn sys/change_on_install as sysdab;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Grant create view to scott;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Conn scott/tiger;</p> </td> 
  </tr> 
 </tbody> 
</table>

创建视图：

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Create view myview</p> <p style="margin-left:0cm;">As</p> <p style="margin-left:0cm;">Select d.deptno,d.dname,d.loc,temp.count,temp.avg</p> <p style="margin-left:0cm;">From detp d,(</p> <p style="margin-left:0cm;">&nbsp;Select deptno,count(empno) count,avg(sal) avg</p> <p style="margin-left:0cm;">&nbsp;From emp</p> <p style="margin-left:0cm;">&nbsp;Group by deptno) temp</p> <p style="margin-left:0cm;">Where d.deptno = temp.deptno(+);</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">查看视图：</p> <p style="margin-left:0cm;">Select * from user_views;</p> <p style="margin-left:0cm;">&nbsp;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">删除数据：</p> <p style="margin-left:0cm;">Delete from myview where empno = 7369;</p> <p style="margin-left:0cm;">删除数据的时候也会影响到原始数据，不过依然不合理。</p> <p style="margin-left:0cm;">那么如果说现在的视图记录是多少张表来的呢？例如，现在有如下的一个视图。</p> </td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Create or replace view myview</p> <p style="margin-left:0cm;">As</p> <p style="margin-left:0cm;">Select e.empno,e.ename,e.job,d.dname,d.loc</p> <p style="margin-left:0cm;">From emp e,dept d</p> <p style="margin-left:0cm;">Where e.deptno=d.deptno and e.deptno=20;</p> </td> 
  </tr> 
 </tbody> 
</table>

此时的视图除了雇员信息之外，还包含有部门的信息，那么下面继续执行增加操作。

1.  向myview表中增加一行新数据

<table style="margin-left:18pt;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Insert into myview(empno,ename,job)</p> <p style="margin-left:0cm;">Values(9999,’阿道夫’,’doctor’,’娱乐部’,’europe’)；</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">ORA-01776：无法通过联接视图修改多个基表</p> </td> 
  </tr> 
 </tbody> 
</table>

这个时候的数据是多张表的，在dept表中根本没有‘娱乐部门’这个数据，既然没有，发现是无法增加的。

在以后的工作中，记住一个原则，视图只能够用来查询，而不要用于任何更新操作。

在创建视图的时候还存在有两个创建的选项，这两个选项主要也是为了防止用户错误的更新操作。

1.  避免视图创建条件更新：with check option

问题分析：首先创建一张包含20部门雇员的视图

<table style="margin-left:18pt;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Create or replace view myview</p> <p style="margin-left:0cm;">As</p> <p style="margin-left:0cm;">Select * from emp where deptno=20;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Update myview set deptno=20 where empno=7369;</p> </td> 
  </tr> 
 </tbody> 
</table>

设置只读视图：with read only

使用with check option 子句只能够暴露视图的创造条件，但是其他的字段依然允许更新。

<table style="margin-left:18pt;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Update myview set ename=’史密斯’ where empno=7369;</p> </td> 
  </tr> 
 </tbody> 
</table>

视图不是真实的数据，而是真实数据的映射。所以这样的做法依然不合理。

### 同义词 ###

在system用户之中要想访问emp表，只能够加 scott.emp

前提：数据库之中，每张数据表的完整名称应该是‘用户名.表名称’;

经过一系列的查询，可以发现dual实际上是sys的用户表，那么可以发现不同用户的数据表需要加上用户名，但是为什么scott表访问不了sys.dual的时候没有加用户名呢？

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Create [public] synonym 同义词的名臣 for 用户名.表名称</p> </td> 
  </tr> 
 </tbody> 
</table>

范例：将scott.emp表定义一个myemp的同义词，由sys完成

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Conn/sys/change_on_install as sysdba;</p> <p style="margin-left:0cm;">Create synonym myemp for scott.emp;</p> </td> 
  </tr> 
 </tbody> 
</table>

创建完同义词后，下面就可以直接通过myemp来访问scott.emp数据表了。

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Create public synonym myemp for scott.emp;</p> </td> 
  </tr> 
 </tbody> 
</table>

### 索引 ###

Oracle的索引相当复杂，有几十种索引。索引的主要功能是进行数据查询优化的，提升数据库的性能问题。那么下面首先对于一些查询做一个分析。

范例：查询所有工资高于3000的雇员信息。

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Select * from emp where sal &gt;3000;</p> </td> 
  </tr> 
 </tbody> 
</table>

但是这个代码严格来讲性能是非常差的。如果想要观察性能问题，需要sys用户

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Conn sys/change_on_install as sysdba;</p> <p style="margin-left:0cm;">Set autotrace on;</p> <p style="margin-left:0cm;">Select * from myemp;</p> </td> 
  </tr> 
 </tbody> 
</table>

此时是为了观察出问题只能够在sys用户下观察查询的情况，可以发现此时查询时逐行扫描，全盘扫描的。

索引可以建立如下的树：小于放左边，大于放右边，提高查找的效率。

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Create index emp_sal_ind on emp(sal);&nbsp; --在emp表的sal字段建立索引。</p> </td> 
  </tr> 
 </tbody> 
</table>

此时索引已经创建完成。随后再进行sal查询的时候显示的不是权标扫描，而逝根据一个基数，查询可以明显的提升性能，而索引实现的关键是这棵树的维护。（树由oracle内部自行维护）那么此时修改了员工的工资呢？这棵树一定要发生变化，索引的树的数据都要重新排列，这样反而降低了性能。所以说索引是一种相对的手段。

### 数据库备份、导入和导出 ###

在工作范围之内，除非是那种单人工作环境，有可能就需要使用到数据库备份的概念。

本操作指的是将一个用户的所有数据进行导出，如果有需要执行导入，一般此类操作都会出现在项目中。例如：在项目之中有可能出现修改数据表，但是脚本没有更新的情况。所以金辉造成真是的数据库和部署的创建脚本不一致的情况。那么在这样的情况下就需要数据库的导入和导出。

数据库的导出操作：

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;"><img alt="" height="103" src="https://img-blog.csdnimg.cn/20190530203233913.png" width="554"></p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;"><img alt="" height="103" src="https://img-blog.csdnimg.cn/20190530203237686.png" width="554"></p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;"><img alt="" height="36" src="https://img-blog.csdnimg.cn/20190530203244850.png" width="554"></p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;"><img alt="" height="48" src="https://img-blog.csdnimg.cn/20190530203249985.png" width="554"></p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">但是如果想要执行冷备份，则必须有sys用户进行，而且备份的数据必须是完整的，包括：控制文件、日志文件、数据文件、数据库的核心配置pfile</p> </td> 
  </tr> 
 </tbody> 
</table>

在实际的工作之中，以上的这些文件都要分在不同的磁盘上的，已达到性能平衡。

一般的项目都要考虑到负载均衡，将其以上的文件都保存到不同的磁盘上。所有记录这些数据的位置就在数据字典上。

下面简单演示下关于用户的创建、权限的分配等功能。

1.  如果想进行用户的维护，那么首先一定是具备管理员权限的用户：sys、system

<table style="margin-left:18pt;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Conn sys/change_on_install;</p> </td> 
  </tr> 
 </tbody> 
</table>

1.  创建一个新用户

<table style="margin-left:18pt;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Create user 用户名 identified by 密码；</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Create user dog identified by wangwang;</p> </td> 
  </tr> 
 </tbody> 
</table>

但是这个时候也只是一个普通用户而已，它连登陆都做不了。此时登陆时提示缺少session权限。

1.  为dog用户授予连接数据库权限

<table style="margin-left:18pt;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Grant create session to dog;</p> </td> 
  </tr> 
 </tbody> 
</table>

1.  赋予创建表|序列的权限

<table style="margin-left:18pt;"> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Grant create table to dog;</p> <p style="margin-left:0cm;">Grant create sequence to dog;</p> </td> 
  </tr> 
 </tbody> 
</table>

那万一现在有很多对象要创建，例如：索引、视图、过程、函数等。这样的授权是非常麻烦的。在oracle里面提供了两个重要的角色（每个角色包含多个权限）：RESOURCE、CONNECT，将该角色赋予新用户即可。

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;"><img alt="" height="162" src="https://img-blog.csdnimg.cn/20190530203316164.png" width="554"></p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">该用户的密码在登录一次失效，强迫用户去修改密码：</p> <p style="margin-left:0cm;">Alter user dog password expire;</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;"><img alt="" height="329" src="https://img-blog.csdnimg.cn/20190530203323844.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3BzaGRoeA==,size_16,color_FFFFFF,t_70" width="554"></p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;"><img alt="" height="64" src="https://img-blog.csdnimg.cn/20190530203328735.png" width="554"></p> </td> 
  </tr> 
 </tbody> 
</table>

### 数据库设计范式 ###

所谓的范式指的是设计高效的方便扩充数据库的准则，但是实际之中也只是作为一个参考。实际的工作之中，对于数据库只有一个原则——根据业务尽可能的减少多表查询。

**第一范式：**数据表之中每一个字段都不可再分，简单的理解就是都使用标准数据类型。例如：一下的数据都是第一设计范式。

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Create table member(</p> <p style="margin-left:0cm;">&nbsp;编号 number primary key</p> <p style="margin-left:0cm;">&nbsp;姓名 varchar2(20)</p> <p style="margin-left:0cm;">&nbsp;地址 varchar2(200)</p> <p style="margin-left:0cm;">&nbsp;邮编 varchar2(6)</p> <p style="margin-left:0cm;">&nbsp;电话 varchar2(20)</p> <p style="margin-left:0cm;">&nbsp;Qq&nbsp; varchar(20)</p> <p style="margin-left:0cm;">);</p> </td> 
  </tr> 
 </tbody> 
</table>

此时的字段已经无法再分，所以此时的设计符合第一范式。

1.  如果系统在中国则名字就可以表示一个字段，如果在国外，需要两个字段
2.  在设计表的时候要使用标准被类型。（nember、varchar2、clob），千万不要将生日拆分为年月日三个字段

**第二范式：数据表之中不存在非关键字段对任意一候选关键字段的部分函数依赖。**

函数关系： 总价=商品单价\*数量，认为这就存在了一个函数关系。

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">Create table orders(</p> <p style="margin-left:0cm;">&nbsp;编号 number primary key</p> <p style="margin-left:0cm;">&nbsp;商品名称 varchar2(20)</p> <p style="margin-left:0cm;">&nbsp;单价 number</p> <p style="margin-left:0cm;">&nbsp;数量 number</p> <p style="margin-left:0cm;">&nbsp;<strong>总价 number</strong></p> <p style="margin-left:0cm;">);</p> </td> 
  </tr> 
  <tr> 
   <td style="vertical-align:top;width:414.8pt;"> <p style="margin-left:0cm;">一般用于多对多</p> </td> 
  </tr> 
 </tbody> 
</table>

**第三范式：**数据表之中不存在非关键字段对任意一候选关键字段的**传递函数依赖**。