关闭

Oracle数据库操作(视图,序列,索引,同义词)

标签: oracle索引数据库
666人阅读 评论(0) 收藏 举报
分类:

视图

视图的概念

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

视图的作用

 可以提供各种数据的表现形式,以不同风格呈现给客户。
 隐藏数据的复杂性,简化查询操作,提高效率。
 有些操作必须用视图,例如在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;
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:7283次
    • 积分:198
    • 等级:
    • 排名:千里之外
    • 原创:11篇
    • 转载:0篇
    • 译文:2篇
    • 评论:0条