Oracle数据库操作（视图，序列，索引，同义词）

视图

视图的概念

视图是用来保存一个复杂的查询结果，化简查询操作和提高效率为主。

视图的作用

 可以提供各种数据的表现形式,以不同风格呈现给客户。
 隐藏数据的复杂性，简化查询操作，提高效率。
 有些操作必须用视图，例如在FROM子句嵌套子查询的SQL用的就是一个匿名的视图(临时视图)
 提供一些安全保证。
 简化数据的权限管理。

视图基本语法

首先要授权，否则出现错误，无法创建视图：

授权操作：

打开 sqlplus /nolog 命令行窗口，首先进行视图操作的授权：

 SQL> conn sys/orcl as sysdba
    已连接。
    SQL> grant create any view to scott;

    授权成功。

    SQL> conn scott/skye;
    已连接。
    SQL>

--将部门20的员工保存到一个视图中
create view emp_dept20 as select * FROM emp where deptno = 20;

-- 创建视图
create view emp_dept20 as select * from emp where deptno =20;

--视图和表使用同一个命名空间
select * from emp_dept20;

说明：

1. 视图就是一张逻辑表，可以像表一样的操作。
2. 所有针对视图的操都会影响到视图的基表。
3. 视图中的数据会随基表的更新而自动更新。

如果要更改视图，有以下两种方式：

1. 先删除原视图，再创建一个与原视图相同名称。

--删除视图
drop view emp_dept20;

1. 也可以使用以下的语法更新视图：

--更新视图
create or replace view emp_dept20 as select * from emp where deptno = 20;

说明：
 可以在建立视图时加上可选项WITH READ ONLY，来定义一个只读视图。

视图子查询语句涉及复杂查询所建立视图称为复杂视图。

Eg.将员工的姓名，工资，领导，部门名称的结果建立视图

create or replace view myview1(员工名称,员工工作,员工工资,领导姓名,部门名称)  AS(
SELECT e.ename,e.job,e.sal,m.ename,d.dname
FROM emp e,emp m,dept d
WHERE e.mgr=m.empno and e.deptno=d.deptno
)

 对于复杂的视图最好给查询出的每一项信息加别名。特别是查询中涉及到表达式或者函数的列。取别名时别名的个数要与select查询项一一对应。

索引（index）

索引的定义

用于提高查询效率的数据对象，可以通过他快速定位数据，减少磁盘I/O操作。

注意：索引信息建议与表信息独立存放（即索引最好放在与表不同的表空间）。

Oracle索引是由系统本身来使用和维护。
Oracle会在主键和唯一性约束字段上自动创建索引。

Oracle的索引支持最多32个字段

索引的分类与维护

 唯一性索引
 非唯一性索引
 反序索引
 位图索引
 函数索引

1. 建立索引：

   --创建索引
create index emp_ename_index on emp(ename);

1. 唯一索引：

CREATE UNIQUE INDEX　索外名称 ON 表(字段1，字段2)

create unique index emp_ename_index on emp(ename);

1. 非唯一索引：

   CREATE INDEX　索外名称 ON 表(字段1，字段2)

create index emp_ename_index on emp(ename);

1. 反序索引：

CREATE INDEX　索外名称 ON 表(字段1，字段2) reverse

--反序索引：
create index emp_ename_index on emp(ename);

1. 位图索引：

CREATE BITMAP INDEX　索外名称 ON 表(字段1，字段2)

--位图索引：
create bitmap index emp_ename_index on emp(ename);

1. 函数索引：

CREATE INDEX　索外名称 ON 表(函数())

--函数索引：
create index emp_ename_index on emp(substr(ename,1,2));
--创建一个带有substr函数的Oracle函数索引

1. 索引的删除：

CREATE INDEX　索外名称 ON 表(函数())

--索引的删除：
drop index emp_ename_index;

1. 索引的重命名：

ALTER INDEX　索引名称　TO 新名称

--索引的重命名：
--alter index emp_ename_index to emp_ename_index1;

1. 索引的重建：

ALTER INDEX 索引名 REBUILD

--索引的重建：
alter index emp_ename_index rebuild;

1. 清理的碎片：

ALTER INDEX 索引名 COALESCE

--清理的碎片
alter index emp_ename_index coalesce;

建立索引的注意事项

A. 在以下情况可以考虑建索引

 字段取值范围比较大。
 字段中包含大量空值。
 字段经常出现在WHERE子句的直接条件
 表经常被访问而且数据大，但每次访问返回的数据量小于总记录2%-4%

B. 不适合建立索引情况

 表的数据量小
 字段基本不会用在WHERE条件中的
 每次访问返回的数据量比较大情况下。
 表的更新比较频繁的
 被索引的字段用在了表达式中的

维护索引注意事项

 如果索引中包含损坏的数据块或有过多的碎片的，建议先删除再重建
 如果是ORACLE根据约束自动生成的索引，那么该索引只能通过约束的禁用或删除来删除该索引。
 如果删除了表，则关联此表的索引也会被删除。

序列

序列是Oracle自动生成，不重复的整数值。也是一种数据库对象，可以被多个用户共享。

作用:

 作为主键值
 代替应用程序编号
 对序列可以进行缓存，以提高效率

语法：

CREATE SEQUENCE 序列名称

[INCREMENT BY n] ——–递增值,默认1

[START WITH n] ——–序列的起始值，默认1

[MAXVALUE n] ——–序列的是大值 默认nomaxvalue

[MINVALUE n] ——–序列的最小值 默认nominvalue

[cycle|nocycle] ——–是否循环 默认nocycle

[cache n|nocache] ——–是否缓存 默认nocache

[order|noorder] ——–按请求顺序由小到大

序列的两个比较重要的属性：

1. currval : 序列的当前值；

2. nextval : 序列的下一个值；

给class表建立一个sequence,用它一生成主键

create sequence classseq
　　minvalue 1
　　maxvalue 999999999999
　　start with 1
　　increment by 1
　　nocache;

删除序列

drop sequenece   

使用上面的序列给表添加记录时生成评分

INSERT INTO class VALUES(classseq.nextval,'sta12','2009届12班','F04R01');

说明：
使用cache n可先项可提高访问SEQUENCE的效率。
序列在下列情况下可能出现不连续。
1. 回滚
2. 系统异常
3. 多个表使用同一个序列时
4. 使用nocache和order可先项都会降低效率。
5. 建议每张表建一个序列

同义词

相当于对象的别名，使用同义词可以方便访问其它用户的对象,缩短对象名字的长度。

创建同义词的语法

create synonym emp for scott emp;

删除的同义词语法

drop synonym emp;

win+r 快捷打开运行：sqlplus /nolog

在命令行窗口操作：

SQL> conn sys/orcl as sysdba;
已连接。
SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      8899 小明       程序猿          7788                       500        300
        20

      7369 SMITH      CLERK           7902 17-12月-80            800
        20

      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300
        30


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500
        30

      7566 JONES      MANAGER         7839 02-4月 -81           2975
        20

      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400
        30


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850
        30

      7782 CLARK      MANAGER         7839 09-6月 -81           2450
        10

      7788 SCOTT      ANALYST         7566 19-4月 -87           3000
        20


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7839 KING       PRESIDENT            17-11月-81           5000
        10

      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0
        30

      7876 ADAMS      CLERK           7788 23-5月 -87           1100
        20


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7900 JAMES      CLERK           7698 03-12月-81            950
        30

      7902 FORD       ANALYST         7566 03-12月-81           3000
        20

      7934 MILLER     CLERK           7782 23-1月 -82           1300
        10

已选择15行。

SQL> create synonym emp for scott emp;
create synonym emp for scott emp
*
第 1 行出现错误:
ORA-00933: SQL 命令未正确结束

SQL> create synonym emp for scott.emp;

同义词已创建。

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      8899 小明       程序猿          7788                       500        300
        20

      7369 SMITH      CLERK           7902 17-12月-80            800
        20

      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300
        30


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500
        30

      7566 JONES      MANAGER         7839 02-4月 -81           2975
        20

      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400
        30


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850
        30

      7782 CLARK      MANAGER         7839 09-6月 -81           2450
        10

      7788 SCOTT      ANALYST         7566 19-4月 -87           3000
        20


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7839 KING       PRESIDENT            17-11月-81           5000
        10

      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0
        30

      7876 ADAMS      CLERK           7788 23-5月 -87           1100
        20


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7900 JAMES      CLERK           7698 03-12月-81            950
        30

      7902 FORD       ANALYST         7566 03-12月-81           3000
        20

      7934 MILLER     CLERK           7782 23-1月 -82           1300
        10


已选择15行。

SQL> drop synonym emp;

同义词已删除。

SQL>

同义词建立好以后对于其它有访问权限用户可以直接使用同义词
如SYSTEM用户在访问scott的emp表时只需要如下命令

select * from emp;