Oracle数据库-02 操作数据库

悠悠 2022-02-28 00:58 982阅读 0赞

## 一、创建表空间、自定义用户管理 ##

**1、创建表空间**

**（1）什么是表空间**

Oracle数据库包含物理结构和逻辑结构。

数据库的物理结构是指构成数据库的一组操作系统文件。

数据库的逻辑结构是指描述数据组织方式的一组逻辑概念及它们之间的关系。

表空间是数据库逻辑结构的一个重要组件。表空间可以存放各种应用对象，如表、索引。而每一个表空间由一个或多个数据文件组成。

*一个表空间由一个或多个数据文件组成，一个数据文件只属于一个表空间。*  
*一个数据文件中可以有一个或多个表，一个表可以属于不同的数据文件。*

**（2）表空间的分类**

*  **永久性表空间：** 一般用于保存表、视图、过程和索引等数据。  
    SYSTEM 、SYSAUX、 USERS、 EXAMPLE表空间是默认安装的。
 *  **临时性表空间：** 只用于保存系统中短期活动的数据，如排序数据等
 *  **撤销表空间：** 用来帮助回退未提交的事务数据，已提交了的数据在这里是不可以恢复的。

注：一般不需要创建临时性表空间和撤销表空间，除非把他们转移到其他磁盘中以提高性能。

**（3）为什么使用表空间**

*  对不同的用户分配不同的表空间，对不同的模式对象分配不同的表空间，方便对用户数据的操作，以及对模式对象的管理。
 *  可以将不同数据文件创建到不同的磁盘中，有利于管理磁盘空间、提高 I/O性能，以及备份和恢复数据等。
 *  一般在完成Oracle系统的安装并创建Oracle实例后，Oracle系统会自动建立多个表空间。

**（4）如何使用表空间**

*创建表空间语法：*

create talespace tablespacename	
    	datafile 'filename'[SIZE integer [ K|M] ]
    	[autoextend [ off | on ] ];

**其中：**

*  tablespacename：需要创建的表空间名称
 *  datafile：指定组成表空间的一个或多个数据文件，当有多个数据文件时使用逗号分隔。
 *  filename：数据文件的路径和名称
 *  size：指定文件的大小，用K指定千字节大小，M指定兆字节大小
 *  autoextend：用来启用或禁用数据文件的自动扩展，设置on则空间使用完毕会自动扩展，设置off则很容易出现表空间剩余容量为0的情况，使用数据不能存储到数据库中。

**例如：**

/*创建一个自动增长的表空间worktbs*/
    	create tablespace worktbs
    	datafile 'D:\Oracle\oradata\APTECH\WORKTBS01.DBF'
    	size 10M autoextend on;

**（5）删除表空间**

drop tablespace tablespacename;

**2、创建数据库用户**

当创建一个新数据库时，Oracle将创建一些默认数据库用户，如Sys，System、Scott等。Sys和System、都是Oracle的系统用户，而Scott用户是Oracle数据库的一个示例账户。

**（1）Sys用户**  
Sys用户是Oracle数据库中的一个超级用户。数据库中的所有数据字典和视图都存储在SYS模式中。数据字典存储了用来管理数据库对象的所有信息，是Oracle数据库中非常重要的系统信息。Sys用户只能以SYSOPER或SYSDBA角色登录系统。

**（2）System用户**  
System用户是Oracle数据库中默认的系统管理员，它拥有DBA权限。System用户拥有Oracle管理工具使用的内部表和视图。通常通过System用户管理Oracle数据库的用户、权限和存储等、但不建议在System模式中创建用户表。System用户不能以SYSOPER或SYSDBA角色登录系统，只能以默认方式登录。

**（3）Scott用户**  
Scott用户是Oracle数据库的一个示例用户，一般在数据库安装时创建。  
Scott用户使用USERS表空间存储模式对象。

**Oracle中的create user命令用于创建新用户。每个用户都有一个默认表空间和一个临时表空间。如果没有指定，那么Oracle就将USERS设为默认表空间，将TEMP设为临时表空间。**

**创建用户语法：**

CREATE USER user
    	identified by password
    	[default tablespace tablespacename]
    	[temporary tablespace tablespacename]

**其中：**

*  user是用户名，用户名必须是一个标识符。
 *  password是用户口令，口令必须是一个标识符，且不区分大小写。
 *  default 或 temporary 为用户确定默认表空间或临时表空间。

**例：**

/*创建一个名称为martin的用户，其口令为martinpwd，
    	   默认表空间worktbs，临时表空间为temp*/
    	create user martin
    	identified by martinpwd
    	default tablespace worktbs
    	temporary tablespace temp;
    
    
    	/*将martin用户的口令修改为mpwd*/
    	alter user martin
    	identified by mpwd;

**删除用户：** 当用户拥有模式对象时则无法直接删除用户，必须使用cascade选项删除用户和用户模式对象。

drop user 用户名 cascade

**3、给用户授予权限**  
在Oracle数据库中，根据系统管理方式的不同，可将权限分为系统权限和对象权限两类。

**（1）系统权限**  
系统权限是指被授权用户是否可以连接到数据库上及在数据库中可以执行哪些系统操作。系统权限是在数据库中执行某种系统级别的操作或针对某一类的对象执行某种操作的权利。例如：在数据库中创建表空间的权利，在数据库中创建表的权利，这些都属于系统权限。

**常见的系统权限如下:**

*  create session ：连接到数据库
 *  create table：创建表
 *  create view：创建视图
 *  create sequence：创建序列

**（2）对象权限**

对象权限是指用户对数据库中具体对象所拥有的权限。对象权限是针对某个特定的模式对象执行操作的权利，只能针对模式对象来设置和管理对象权限。如数据库中的表、视图、序列、存储过程、存储函数等。

**Oracle数据库用户有两种途径获得权限：**

*  管理员直接向用户授予权限
 *  管理员将权限授予角色，然后将角色授予一个或多个用户（通常使用此种方式），在Oracle数据库系统中预定义了很多角色，较常用的有 *connect* 角色、*resource* 角色、*DBA* 角色，一般程序使用的用户只要授予 *connect* 和 *resource* 角色即可。

**（1）CONNECT：** 需要连接上数据库的用户，特别是那些不需要创建表的用户，通常会授予该角色。

**（2）RESOURCE：** 更为可靠和正式的数据库用户可以授予该角色，可以创建表、触发器、过程等。

**（3）DBA：** 数据库管理员角色，拥有管理数据库的最高权限。一个具有DBA角色的用户可以撤销任何其他用户甚至其他DBA的权限，此操作较危险，不可轻易授予该角色。

**授予权限语法：**  
`grant 权限|角色 to 用户名；`

**撤销权限语法：**  
`revoke 权限|角色 from 用户名；`

**数据库用户安全设计原则如下：**

*  数据库用户权限授予按照最小分配原则
 *  数据库用户分为管理、应用、维护、备份4类
 *  不允许使用Sys和System用户建立数据库应用对象
 *  禁止 GRANR dba TO user

--------------------

## 二、创建、访问、修改、删除、使用序列 ##

**1、序列：** 是用来生成唯一、连续的整数的数据库对象。序列通常用来自动生成主键或唯一键的值。序列可以按升序排列，也可以按降序排列。

**2、创建序列**

**语法：**

create sequence sequence_name	
    	[start with integer]
    	[increment by integer]
    	[maxvalue integer | nomaxvalue]
    	[minvalue integer | nominvalue]
    	[cycle | nocycle]
    	[cache integer | nointeger]

**其中：**

*  **start with：** 指定要生成的第一个序列号。对于升序序列，其默认设置为序列的最小值；对于降序序列，其默认值为序列的最大值。
 *  **increment by**：用于指定序列号之间的间隔，其默认值为1。如果n为正值，则生成的序列将按正序排列；如果n为负值，则生成的序列将按降序排列。
 *  **maxvalue：** 指定序列可以生成的最大值。
 *  **nomaxvalue：** 如果指定了nomaxvalue，Oracle将升序序列的最大值设置为1\*27，将降序序列的最大值设为-1。这是默认选项。
 *  **minvalue：** 指定序列的最小值。minvalue必须小于或等于start with的值，并且必须小于maxvalue。
 *  **nomaxvalue：** 如果指定了nominvalue，Oracle将升序序列的最小值设为1，  
    将降序序列的最小值设为-10\*26。这是默认选项。
 *  **cycle：** 指定序列在达到最大值或最小值后，将继续从头开始生成值。
 *  **nocycle：** 指定序列在达到最大值或最小值后，将不能再继续生成值。这是默认选项。
 *  **cache：** 使用cache选项可以预先分配一组序列号，并将其保留在内存中，这样可以更快的访问序列号。当用完缓存中的所有序列号时，Oracle将生成另一组数值，并将其保留在缓存中。
 *  **nocache：** 使用nocache选项，则不会为加快访问速度而预先分配序列号。如果在创建序列时忽略了cache和nocache选项，默认缓存20个序列号。

**例：**

/*创建序列：从序号10开始，每次增加1，最大为2000，
    	不循环，再增加则会报错，缓存30个*/
    	create sequence seq1
           		start with 10
           		increment by 1
           		maxvalue 2000
           		nocycle  
           		cache 30;

**3、访问序列的方式**

通过nextval和currval伪列来访问序列的值。  
可以从伪列中选择值，但不能操纵他们的值。

*  nextval：创建序列后第一次使用nextval时，将返回该序列的初始值。以后再引用nextval时，将使用increment by 子句来增加序列值，并返回这个新值。
 *  currval：返回序列的当前值，即最后一次引用nextval时返回的值。

**例：**

/*创建玩具表*/
    create table toys(
           toyid number(6) primary key,
           toyname varchar2(20) not null,
           toyprice number(4,2)not null
    );
    
    /*使用序列向玩具表中插入数据*/
    Insert into toys(toyid,toyname,toyprice)
           values(seq1.NEXTVAL,'TWENTY',25);
    Insert into toys(toyid,toyname,toyprice)
           values(seq1.NEXTVAL,'Magic Pencil',75);
    
    
    /*查询玩具表*/
    select * from toys;
    
    /*查看序列当前值：即最后一次引用nextval返回的值*/
    select seq1.currval from dual;

**4、修改序列**  
alter sequence命令用于修改序列的定义.如果执行下列操作,则会修改序列。

*  设置或删除maxvalue或minvalue
 *  修改增量值
 *  修改缓存中序列号的数目

**更改序列的语法：**

alter sqquence[schema.]sequence_name
    		[increment by integer]
    		[maxvalue integer | nomaxvalue]
    		[minvalue integer | nominvalue]
    		[cycle | nocycle]
    		[cache integer | nointeger]

**注：** 不能修改序列的start with参数。在修改序列时，应注意升序序列的最小值必须小于最大值。

**5、删除序列**  
drop sequence命令用于删除序列。  
还可以使用此语句重新开始一个序列，方法是先删除序列，再重新创建序列。例如：一个序列的当前值为100，现在想用值25重新开始此序列。可以先删除此序列，然后再以相同的名称创建它，并将start with参数设为25。

**删除序列语法：**

drop sequence [schema.]sequence_name;

**6、序列应用场景**

*  可以使用序列设置Oracle主关键字，所得值为从给定的起点开始的一系列整数值。序列所生成的数字只能保证在单个实例里是唯一的，这就不适合将它用作并行或者远程环境里的主关键字。因为各自环境里的序列可能会生成相同的数字，从而导致冲突的发生，所以在不要并行的环境中，可选择使用序列作为主关键字。
 *  还可以使用SYS\_GUID函数生成32位的唯一编码作为主键。它原自不需要对数据库进行访问的时间戳和机器标识符，这会保证创建的标识符在每个数据库里都是唯一的。但管理SYS\_GUID函数生成的值比较困难，所以除非是一个在并行的环境里或者希望避免使用序列的情况下，才可使用该函数设置关键字。

**例：**

select SYS_GUID() from dual;

--------------------

## 三、同义词 ##

**1、同义词的作用**

*  简化sql语句
 *  隐藏对象的名称和所有者
 *  为分布式数据库的远程对象提供了位置透明性
 *  提供对对象的公共访问

**2、同义词的分类**

**（1）私有同义词**

私有同义词只能被当前模式的用户访问，且名称不可与当前模式的对象名称相同。  
要在当前模式下创建私有同义词，用户必须拥有create synonym系统权限；要在其他用户模式下创建私有同义词，用户必须拥有create any synonym系统权限。

**创建私有同义词语法如下：**

create [or replace] synonym [schema.]synonym_name
    	for [schema.]object_name;

**其中：**

*  or replace：表示在同义词存在的情况下替换该同义词
 *  synonym\_name：表示要创建的同义词的名称
 *  obejct\_name：指定要为值创建同义词的对象的名称

**例：**

/*授予A_oe用户创建synonym权限*/
    	grant create synonym to A_oe
    
    	/*在A_oe模式下创建私有同义词访问A_hr模式下的employee表*/
    	/*1.创建同义词*/
    	create synonym sy_emp for A_hr.Employee;
    
    	/*2.访问同义词*/
    	select * from sy_emp

**（2）公有同义词**  
公有同义词可以被所有数据库用户访问，可以隐藏数据库对象的所有者和名称，  
并降低sql语句的复杂性。要创建公有同义词，用户必须拥有`create publiv synonym` 系统权限。

**创建公有同义词语法如下：**

create [or replace] public synonym synonym_name
    	for [schema.]object_name;

**（3）私有同义词和公有同义词的区别**

*  私有同义词只能在当前模式下访问，且不能与当前模式的对象同名。
 *  公有同义词可被所有的数据库用户能访问。

**注：**

*  使用同义词前，要获得同义词对应对象的访问权限。
 *  对象（如表）、私有同义词、公有同义词是否可以三者同名？对象与私有同义词不能同名；当对象和公有同义词同名时，数据库优先选择对象作为目标；当私有同义词和公有同义词同名时，数据库优先选择私有同义词作为目标。

**3、删除同义词**  
drop synonym语句用于从数据库中删除同义词。  
要删除同义词，用户必须拥有相应的权限。

**删除同义词语法如下：**

drop [public] synonym [schema.]synonym_name;

**例：**

/*删除私有同义词*/
    	drop synonym A_oe.sy_emp;
    
    	/*删除公有同义词*/
    	drop public synonym public_sy_emp;

--------------------

## 四、索引 ##

**1、认识索引**

索引是与表关联的可选结构，是一种快速访问数据的途径，可以提高数据库性能。  
数据库可以明确的创建索引，以加快对表执行sql语句的速度。当将索引键作为查询条件时，该索引将直接指向包含这些值的行的位置。即便删除索引，也无需修改任何sql语句的定义。

**2、创建合适的索引**  
索引分类如图：  
![在这里插入图片描述][20190323191200342.png]  
**（1）B树索引**

B树索引通常也称为标准索引。索引的顶部为根，其中包含指向索引中下一级的  
选项。下一级为分支块，分支快又指向索引中下一级的块。最低一级为叶节点，其中包含指向表行的索引项。叶块为双向链接，有助于按关键字值的升序和降序扫描索引。

**创建普通索引语法如下：**

create [unique] index index_name on table_name(column_list)
    	[tablespace tablespace_name]

**其中：**

*  unique：用于指定唯一索引，默认情况下为非唯一索引
 *  index\_name：所创建的索引名称
 *  table\_name：表示为之创建索引的表名
 *  column\_list：在其上创建索引的列名的列表，可以基于多列创建索引，列之间用逗号分隔。
 *  tablespace\_name：为索引指定表空间

**（2）唯一索引和非唯一索引**

*  唯一索引：定义索引的列中任何两行都没有重复值。唯一索引中的索引关键字只能指向表中的一行。在创建主键约束和唯一约束时都会创建一个与之对应的唯一索引。
 *  非唯一索引：单个关键字可以有多个与其关联的行。

**例：**

--在薪水级别表Scott用户下的salgrade表中，为级别编号（grade）列创建唯一索引
    	create unique index index_unique_grade on Scott.salgrade(grade);

**（3）反向键索引**

*  与常规B树索引相反，反向键索引在保持列顺序的同时反转索引列的字节。  
    反向键索引通过反转索引键的数据值来实现。其优点是对于连续增长的索引列，反转索引列可以将索引数据分散在多个索引块之间，减少I/O瓶颈的发生。
 *  反向键索引通常建立在一些值连续增长的列上，如系统生成的员工编号，但不能执行搜索范围。

**语法：**

--为emp表的empno列创建反向键索引
    	create index index_reverse_empno on emp(empno) reverse;

**（4）位图索引**

位图索引的优点在于，它最适用于低基数数列（即该列的值是有上限的，理论上不会是无穷大）。例如，员工表中的工种(job)列，即便有几百万条员工记录，工种也是可计算的，故工种列可以作为位图索引。

**位图索引优点如下：**

*  对于大批即时查询，可以减少响应时间。
 *  相比其他索引技术，占用空间明显减少。
 *  即时是在配置很低的终端硬件上，也能获得显著的性能。

注：位图索引不应当用在频繁发生insert、update和delete操作的表上，因为这些  
DML操作在性能方面的代价很高。位图索引最适合于数据仓库和决策支持系统。

**例：**

--在emp表中，为job列创建位图索引
    	create bitmap index index_bit_job on emp(job);

**（5）其他索引**

*  组合索引：在表内多列上创建索引。索引中的列不必与表中的列顺程序一致，也不必相互邻接，类似于mysql中的复合索引，如员工表中部门列和职务列上的索引，组合索引最多32列。
 *  基于函数的索引：若使用的函数或表达式涉及正在建立索引的表中的一列或多列，则创建基于函数的索引。可以将基于函数的索引创建为B树索引或位图索引。

**例:**

--在emp表中，为ename列创建大写函数索引
    	create index index_ename on emp(UPPER(ename));

**注：** 创建组合索引时请将唯一性高（该列上存储的大部分数据是唯一的）的列放在第一位。

**3、创建索引注意事项**

*  （1）频繁搜索的列可作为索引。
 *  （2）经常排序、分组的列可作为索引。
 *  （3）经常用作连接的列（主键、外键）可作为索引。
 *  （4）将索引放在一个单独的表空间中，不要放在有回退段，临时段和表的表空间中。
 *  （5）对大型索引而言，可考虑使用nologging子句创建大型索引。
 *  （6）根据业务数据发生的频率，定期重新生成或重新组织索引，并进行碎片整理。
 *  （7）仅包含几个不同值的列不可以创建为B树索引，但可根据需要创建位图索引。
 *  （8）不要在仅包含几行的表中创建索引。

**4、使用命令删除索引**

*  **drop index 语句用于删除索引**

--删除emp表中的index_bit_job位图索引
    	drop index index_bit_job;

*  **何时应删除索引**

（1）应用程序不再需要索引。

（2）：执行批量加载前。大量加载数据前应先删除索引，加载后再重建索引有以下好处：

*  提高加载性能
 *  更有效的使用索引空间

（3）：索引已损坏。

**5、重建索引应用场景**

**（1） alter index—rebuild 语句用于重建索引**

--将反向键索引更改为正常的B树索引
    	alter index index_reversse_empno rebuild noreverse;

**（2）何时应重建索引**

*  用户表被移动到新的表空间后，表上的索引不会自动转移，此时需将索引移到指定表空间。  
    **语法：**

alter index index_name rebuild tablespace tablespace_name;

*  索引中包含很多已删除的项。当对表进行频繁删除，造成索引空间浪费时，可重建索引。
 *  需将现有的正常索引转换成反向键索引。

--------------------

## 五、创建分区表 ##

**1、认识分区表**

Oracle允许用户把一个表中的所有行分为几个部分，并将这些部分存储在  
不同的位置。被分区的表称为分区表，分成的每个部分称为一个分区。  
对于包含大量数据的表来说，分区很有用，优点如下：

*  （1）**改善表的查询性能** ，在对表进行分区后，用户执行sql查询时可以只访问表中的特定分区而非整个表。
 *  （2）**表更容易管理** ，因为分区表的数据存储在多个部分，按分区加载和删除数据比在表中加载和删除更容易。
 *  （3）**便于备份和恢复**，可以独立的备份和恢复每个分区。
 *  （4）**提高数据安全性**，将不同的分区分布在不同的磁盘，可以减小所有分区的数据同时损坏的可能性。

**2、符合以下条件的表可以建成分区表**

*  数据量大于2GB
 *  已有的数据和新添加的数据有明显的界限划分。

表分区对用户来说是透明的，即应用程序可以不知道表已被分区，在更新和查询分区表时当做普通表来操作，但Oracle优化程序知道表已被分区。  
注意要分区的表不能具有long和long raw数据类型的列。

**3、Oracle提供的分区方法**

Oracle提供的分区方法有以下几种：范围分区、列表分区、散列分区、复合分区、  
间隔分区和虚拟列分区等。其中，间隔分区和虚拟列分区是Oracle 11g的新增特性。

**（1）范围分区**  
范围分区（range）是应用比较广的表分区方式，它以列的值的范围作为分区的  
划分条件，将记录存放到列值所在的range分区中。

**例：**

/*创建销售信息表*/
    	create table sale1(
          	sales_id number not null,        --销售流水号
           	product_id varchar2(5) not null, --产品id
           	sales_date date not null,        --销售日期
           	sales_cost number(10),           --销售金额
           	areacode varchar2(25)            --销售区域         
    	)
    	/*创建分区*/
    	partition by range(sales_date)(
             		partition p1 values less than(to_date('2018-03-01','yyyy-mm-dd')),
             		partition p2 values less than(to_date('2018-06-01','yyyy-mm-dd')),
              		partition p3 values less than(to_date('2018-09-01','yyyy-mm-dd')),
              		partition p4 values less than(to_date('2018-12-01','yyyy-mm-dd')),
              		partition p5 values less than(maxvalue)
    	);
    
    	/*查看第三季度数据*/
    	select * from sale1 partition(p1);
    	select * from sale1 partition(p2);
    	select * from sale1 partition(p3);
    	select * from sale1 partition(p4);
    	select * from sale1 partition(p5);
    
    	/*删除第三季度的数据*/
    	delete from sale1 partition(p3);

**注：**

*  一般创建范围分区时，都会将最后一个分区设置为maxvalue，以使其他数据落入此分区。一旦需要某一数据时，可以利用拆分分区的技术将需要的数据从最后一个分区中分离出来，单独形成一个分区。如果没有创建足够大的分区，则插入的数据超出范围就会报错。
 *  如果插入的数据就是分区键上的值，则该数据落入下一个分区。  
    例如插入数据是 ‘2018-09-01’，则数据会落入p4分区。

**（2）间隔分区**

间隔分区（interval）是Oracle 11g版本新引入的分区方法，是范围分区的一种  
增强，可以实现范围分区的自动化。其优点是在不需要创建表时就将所有分区划分清楚。间隔分区随着数据的增加会划分更多的分区，并自动创建新的分区。

**例：**

/*间隔分区*/
    
    	/*创建销售信息表2*/
    	create table sale2(
           	sales_id number not null,        --销售流水号
           	product_id varchar2(5) not null, --产品id
           	sales_date date not null,        --销售日期
           	sales_cost number(10),           --销售金额
           	areacode varchar2(25)            --销售区域         
    	)
    	partition by range(sales_date)
    	interval(numtoyminterval(3,'MONTH'))
    	(partition p1 values less than (to_date('2013-04-1','yyyy/mm/dd')));
    
    	--获得分区情况
    	--查询输出结果，系统自动根据输入数据情况创建新分区 sys_p21
    	select table_name,partition_name
    	from user_tab_partitions
    	where table_name=UPPER('sale2');
    
    	--查询分区数据
    	select * from sale2 partition(sys_p21);

**说明：**

*  （1）只需创建第一个开始分区，如上面demo中p1
 *  （2）`interval(numtoyminterval(3,'MONTH'))` 语句中，interval代表“间隔”，即按照后面括号中的定义间隔添加分区。
 *  （3）`numtoyminterval(3,'MONTH')`表示每3个月为一个分区。  
    `numtoyminterval(n,'interval_unit')`函数用于将n转换成interval\_unit所指定的值。 interval\_unit 可以为year或month。  
    **例：**

numtoyminterval(1,'YEAR')：每1年为一个分区
    	numtoyminterval(1,'MONTH')：每1个月为一个分区

与该类型相关的函数还有numtodsinterval(n,‘interval\_unit’) 用于将n转换成interval\_unit所指定的值。

**注：** interval\_unit可以为DAY、 HOUR、 MINUTE、 SECOND；该函数不支持YEAR和 MONTH。

（4）系统会根据自动创建分区

--------------------

## 六、创建视图、创建数据库链 ##

**1、视图的基本概念**

视图就是一个虚拟的表，是经过查询操作后形成的一个结果，其输出形式类似于一个表。视图不再数据库中存储数据值。用户可以通过触发器对视图所对应的表进行插入、更新和删除操作。与此对应的是，如果对真实表的数据进行修改，则修改结果将会在视图中体现和反映。

**2、视图的应用**

**（1）创建视图**  
最简单的视图实际上就是对一个真实表的引用，但这种引用只是从真实表中检索  
数据，而不允许对数据进行修改。  
**语法：**

create [or replace] view view_name as
    	<select statements> [with check option]

**（2）删除视图**

**语法：**

drop view view_name;

**例：**

--创建视图
    	create or replace view v_employee as
    	select * from emp;
    
    	--从视图中检索数据
    	select * from v_employee;
    
    	--删除视图
    	drop view v_employee;

**3、创建数据库链**

数据库链（Database Link）用来更方便的从一个数据库访问另一个数据库（可以是本地和远程），是在本地建立的一个路径。简单的说，就是通过创建数据库链，能够实现不同数据库之间的通信，即在A数据库中可以使用对B数据库中数据的访问。

*注：在数据库中数据库链会被看做本地数据库的一个使用对象。*

**数据库链的应用**

**（1）数据库链的创建**

创建数据库链时，要求数据库链的名字与链所指向的数据库的全名相同。

**语法：**

create [public] database link link_name
    	connect to username identified by password
    	using 'servername/serverurl';

**其中：**

*  public：使用该关键字表示创建公有的数据库链。
 *  link\_name：表示的是数据库链的名称，该名称通常为xxx.xxx.xxx形式。
 *  username/password：表示远程数据库的用户账户和密码。
 *  servername/serverurl：表示在连接时使用的服务名或者包含服务完整信息的路径。

**例：**

create database link orcl
    connect to A_hr identified by 123456
    using '(description=
          (address_list=
            (address=(protocol=TCP)(HOST=DESKTOP-P96UDBK)(PORT=1521))
          )
          (CONNECT_DATA=
            (SERVICE_NAME=orcl) 
           )
    )';

**（2） 通过数据库链实现远程数据库的访问**

select * from tablename@link_name

注：通常情况下创建数据库链时，可以使用远程数据库的服务名，如using"TEST"，但是这种使用会受到编译环境的影响，出现无法解析的情况，因此建议使用完整路径的方式。  
**完整路径形式及内容如下：**

using '(description=
        		  (address_list=
           		 (address=(protocol=TCP)(HOST=DESKTOP-P96UDBK)(PORT=1521))
         	         )
          	        (CONNECT_DATA=
            	        	(SERVICE_NAME=orcl) 
           	        )
    	)';

--------------------

## 七、从Oracle数据库中导入导出数据 ##

**数据备份：** 数据的备份与恢复是保证数据库安全运行的一项重要内容。当数据库因为意外情况而无法正常运行时，可以利用事先做好的备份进行恢复，将损失减少到最小。导入导出可以通过Oracle的exp和imp命令实现，也可以通过PL/SQL提供的图形界面方式实现。

**1、使用Oracle工具imp和exp导入导出数据**

**（1）使用exp导出数据**

exp是Oracle提供的一个导出工具，它是操作系统下的一个可执行文件，存放  
目录为\\ORACLE\_HOME\\BIN。在命令提示符窗口SQL PLUS输入exp即可启动数据的导出。

**主要步骤如下：**

*  按照提示输入用户名和密码进行登录
 *  登录成功后，提示输入数组提取缓冲区大小，如果采用默认值，直接按enter  
    提示输入导出文件的路径和文件名，默认为export.dmp，如果采用默认值，则enter。
 *  提示选择导出方式
 *  提示是否导出权限、导出表数据，是否对导出数据进行压缩，采用默认选项即可。
 *  开始导出数据，导出完毕后提示成功，终止导出。

**（2）使用imp导入数据**

impl是Oracle提供的一个导入工具，它也是操作系统下的一个可执行文件。  
存放目录与exp相同。使用exp导出数据后，可以再使用imp将数据导入数据库。

**步骤如下：**

*  选择导入数据所在盘符，输入imp命令
 *  按照提示输入用户名和密码进行登录
 *  提示输入插入缓冲区大小，如果采用默认值，则enter
 *  提示是否只列出导入文件的内容，默认则enter
 *  提示对象已存在，是否忽略创建错误，默认为no，输入yes
 *  提示是否导入权限、是否导入数据，采用默认
 *  提示是否导入整个导出文件，默认no，此处输入yes
 *  开始导入数据，导入完毕提示成功

**2、使用第三方工具PL/SQL Developer导入导出数据**  
**（1）使用PL/SQL Developer导出数据**

登录PL/SQL-----》tools----》export tables

三种导出方式：Oracle Export、SQL Inserts、PL/SQL Developer

**说明如下：**

*  **Oracle Export**  
    使用的是emp命令，导出为.dmp文件格式。.dmp文件是二进制的，无法查看，但可以跨平台，效率高且使用最广。
 *  **SQL Inserts**  
    导出为.sql文件格式，可以使用记事本等文本编辑器查看，效率不如第一种，适合小数据量导入导出。如果表中含有blob、clob等字段，则不能采用此种方式
 *  **PL/SQL Developer：** 导出为.pde文件格式，它是PL/SQL Developer的自有文件格式，只能使用该软件来导入导出。

**（2）使用PL/SQL Developer导入数据**

*  登录PL/SQL-----》tools----》export tables
 *  三种导入方式：Oracle Export、SQL Inserts、PL/SQL Developer
 *  在窗口下侧指定导入文件的路径和文件名，点击import按钮即可导入。

--------------------

## 八、优化sql语句 ##

**1、查询优化产生背景**

**（1）谁进行优化**

和数据库有关的人员，包括以下几类：

*  应用程序设计者
 *  应用程序开发者
 *  数据库管理员
 *  系统管理员

**（2）为何要优化**

*  数据库优化的最好方法是认真设计系统和应用程序。
 *  提高性能主要是通过优化应用程序获得的。

**如果满足下列条件，则设计的系统很少会出现性能问题。**

*  硬件能够满足用户的需求
 *  数据库是经过认真设计的
 *  应用程序开发人员编写了高效的sql程序

**（3）优化到什么程度**  
在开始优化时，应该对试图达到的目标有一个明确的概念，即尽可能精确的量化目标。  
**例如：**

*  每天处理10000个订单
 *  在月末的一个晚上制作2000000份账单报表
    
    总之，sql性能的指标就是时间。  
    随着查询速度的不断提升，也就意味着sql执行时间的不断缩短。  
    优化是一个反复的过程，而不是一个只执行一次的活动。

**（4）优化的目标**

优化Oracle服务器的基本目标是确信以下内容。

*  sql语句访问尽可能少的Oracle块
 *  如果需要某个块，那么它一定是被高速缓存在内存中
 *  多个用户共享相同的代码
 *  当需要代码时，它一定是被高速缓存在内存中
 *  在不能避免读写操作的地方，尽可能快的速度完成这些操作
 *  用户从来不必等待其他用户所占有的资源
 *  能够以尽可能快的速度进行备份和处理其他必要的任务

**（5）优化步骤谁先谁后**

按照以下次序实施优化：

*  设计
 *  应用程序
 *  内训
 *  输入/输出（I/O）
 *  争用
 *  操作系统

**2、如何编写高性能的sql语句**

**（1）sql语句与优化的关系**

*  对数据库（数据）进行操作的唯一途径
 *  消耗了70%~90%的数据库资源
 *  独立于程序设计逻辑，相比于对程序源代码的优化，对sql语句的优化在时间成本和风险上的代价都很低。
 *  可以有不同的写法
 *  易学，难精通

**（2）需要优化的sql**

*  运行时间较长的sql
 *  逻辑读较高的sql
 *  逻辑读较高的sql

**（3）sql优化的途径**

*  选择合适的Oracle优化器
 *  选择恰当的扫描方式
 *  善于利用共享sql语句
 *  高质量的sql语句

**（4）一般优化技巧**

*  在select子句中避免使用`*`代替所有列名  
    Oracle在解析过程中，会将 \* 依次替换成所有的列名，这个工作是通过查询数据字典完成的，意味着将耗费更多的时间。
 *  用 truncate 代替 delete 删除所有数据
 *  用exists代替in、用 not exists代替not in

**例：**

--使用in关键字查询所有拥有部门的员工的名称
    	select e.ename from emp e where e.deptno
    	in(select deptno from dept);
    	
    	--exists优化
    	select e.ename from employee e where exists
    	(select 'x' from dept where deptno=e.deptno);

*  用exists代替distinct  
    **例：**

--查询在员工表中出现的不同的员工编号
    	--distinct方式
    	select distinct e.deptno from employee e where e.deptno is not null;
    	
    	--exists优化
    	select d.deptno from dept d where exists
    	(select 'x' from employee e where e.deptno=d.deptno);

*  驱动表的选择  
    （1）from后面靠右的表是驱动表（注：所有表都没有索引的情况下）  
    （2）在有索引的情况下，没索引的表为驱动表  
    （3）驱动表要选择小表（注：所谓的小标是指过滤后的数据量小）
 *  where子句的连接顺序  
    （1）表连接关系放在前面  
    （2）过滤数据越多的条件子句应放置在后面

[20190323191200342.png]: /images/20220228/8f0afe6b6b634af6a7adefb8371f3450.png