Oracle数据库教程

  • 五、视图及索引
  • 5.1 视图的作用及定义
  • 5.2 视图的操作
  • 5.2.1 创建视图
  • 5.2.2 查询视图
  • 5.2.3 更新视图
  • 5.2.4 删除视图
  • 5.2.5 建立只读视图
  • 5.2.6 复杂视图
  • 5.2.7 连接视图
  • 5.2.8 删除视图
  • 5.3 索引的定义
  • 5.4 索引的操作
  • 5.4.1 创建索引
  • 5.4.2 查看索引
  • 5.4.3 删除索引
  • 5.4.4 索引缺点


五、视图及索引

视图和表的区别:

  1. 视图是编译好的SQL语句, 而表不是
  2. 视图是虚拟的, 不占磁盘空间, 而表实际存在, 存储在磁盘空间
  3. 视图灵活自由, 可根据需求随时更改
  4. 视图可以不用知道表结构, 更安全
  5. 尽量不修改视图, 只做查询

5.1 视图的作用及定义

视图的作用:

  1. 简单
    用户完全不需要关心后面对应的表的结构、关联条件和筛选条件
  2. 安全
    数据授权, 通过视图, 用户只能查询和修改他们所能见到的数据
  3. 数据独立
    视图可帮助用户屏蔽真实表结构变化带来的影响

视图的功能: 封装了一条复杂的查询语句.

注意:数据库用户必须有create any view 的权限

授权分为3步:

SQL> conn system/manager;

SQL> show user;
USER 为 “SYSTEM”

SQL> grant create any view to scott;
授权成功。

SQL> revoke create view from scott;
解除授权

/*第一步conn system/manager;遇到的问题:
ERROR:
ORA-01017: invalid username/password; logon denied
解决:
*/
select username from dba_users;” --system用户存在,修改一下密码试试
alter user 用户名 identified by 密码;
--成功。
conn system/manager;
Connected.
SQL> show user;
USER is "SYSTEM"
SQL> grant create any view to scott;

Grant succeeded.

5.2 视图的操作

5.2.1 创建视图

语法:
CREATE [or replace] VIEW 视图名称 AS 查询语句

  • create or replace view的意思就是若数据库中已经存在这个名字的视图的话,就替代它,若没有则创建视图;
  • create则不进行判断,若数据库中已经存在的话,则报错,说对象已存在;

范例:
建立一个视图,包含全部的20部门的雇员信息(雇员编号,姓名,工作,
部门编号)

create view VIEW_EMP
AS 
SELECT empno,ename,job,deptno 
FROM emp 
WHERE deptno=20;

视图创建完成之后,就可以像查找表那样直接对视图进行操作.

5.2.2 查询视图

范例:查询视图

select * from View_EMP;

输出:

EMPNO ENAME      JOB	    DEPTNO
---------- ---------- --------- ----------
      7369 SMITH      CLERK		20
      7566 JONES      MANAGER		20
      7788 SCOTT      ANALYST		20
      7876 ADAMS      CLERK		20
      7902 FORD       ANALYST		20

此时,是通过视图找到的20部门的所有数据,也就是,可以使用视图包装需要的查询语句

例:向view_emp视图中插入一下数据

insert into View_EMP
values(9527,'东方','MANAGER',20);

输出:

EMPNO ENAME      JOB	    DEPTNO
---------- ---------- --------- ----------
      9527 东方     MANAGER		20
      7369 SMITH      CLERK		20
      7566 JONES      MANAGER		20
      7788 SCOTT      ANALYST		20
      7876 ADAMS      CLERK		20
      7902 FORD       ANALYST		20

5.2.3 更新视图

更新视图信息(update)

范例:修改视图中的9527的部门编号

UPDATE view_emp
SET ename='西方' 
WHERE empno=9527;

SELECT * FROM view_emp;

输出:

EMPNO ENAME      JOB	    DEPTNO
---------- ---------- --------- ----------
      9527 西方     MANAGER		20
      7369 SMITH      CLERK		20
      7566 JONES      MANAGER		20
      7788 SCOTT      ANALYST		20
      7876 ADAMS      CLERK		20
      7902 FORD       ANALYST		20

5.2.4 删除视图

删除视图中的信息(delete)
格式:delete from 视图名字 [where 条件]

例:删除9527的信息

delete from view_emp where empno=9527;

输出:

EMPNO ENAME      JOB	    DEPTNO
---------- ---------- --------- ----------
      7369 SMITH      CLERK		20
      7566 JONES      MANAGER		20
      7788 SCOTT      ANALYST		20
      7876 ADAMS      CLERK		20
      7902 FORD       ANALYST		20

5.2.5 建立只读视图

WITH READ ONLY: 创建的视图只读
定义只读视图后,数据库用户只能在该视图上执行select语句。

范例:创建只能读的视图

CREATE OR REPLACE VIEW view_emp_readonly 
AS
SELECT * FROM dept 
WHERE deptno!=88 WITH READ ONLY;

尝试:更新视图中所有部门位置为‘长春’的记录

update view_emp_readonly set loc='长春';

返回:

ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view

5.2.6 复杂视图

复杂视图是指包含函数,表达式,分组数据的视图,主要目的是为了简化操作,需要注意的是,当视图的查询包含函数或者表达式的时候,必须定义别名。

案例:创建一个视图,要求查询每个部门的工资情况。

create or replace view VIEW_DEPT 
as 
select deptno 部门编号,max(sal) 最高工资, min(sal) 最低工资,avg(sal) 平均工资
from emp 
group by deptno;

select * from VIEW_DEPT
order by 部门编号;

注:在有中文字符时,注意不是在中文模式下键入空格,否则会出现ORA-00911: invalid character错误。

5.2.7 连接视图

连接视图是基于多个表所建立的视图,使用连接视图的主要目的是为了简化连接查询。
注意:建立连接视图时,必须使用where子句中指定有效的连接条件

例:创建一个dept和emp表相互关联的视图,并要求该视图只能查询部门编号为20的记录信息

create or replace view VIEW_DE
as
select dept.deptno,dname,emp.ename,sal
from dept,emp
where dept.deptno=emp.deptno 
and dept.deptno=20;

输出:

DEPTNO DNAME	  ENAME 	    SAL
---------- -------------- ---------- ----------
	20 RESEARCH	  SMITH 	    800
	20 RESEARCH	  JONES 	   2975
	20 RESEARCH	  SCOTT 	   3000
	20 RESEARCH	  ADAMS 	   1100
	20 RESEARCH	  FORD		   3000

5.2.8 删除视图

格式:drop view 视图名字

例:删除视图view_de

drop view emp_view_de;

5.3 索引的定义

如果一个表中存在有海量的数据记录,当对表执行指定条件查询的时候,常规的查询方法是将所有的记录都会读取出来,然后再把读取的每一条记录与查询条件进行对比,最后返回满足条件的结果。这样进行的操作时间的开销和I/O开销很大,对于这种情况,可以考虑通过建立索引来减小系统开销。

如果要在表中查询指定的记录,在没有索引的情况下,必须遍历整个表,而有了索引之后,只需要在索引中找到符合查询条件的索引字段值,就可以通过保存在索引中的ROWID快速找到表中对应的记录

例如:如果将表看成一本书,索引的作用就是目录。
在没有目录的情况下,要找到指定的内容必须阅读全书,有了目录,可以通过目录快速找到知识点。

5.4 索引的操作

oracle中索引不像sql server分聚集和非聚集索引。

5.4.1 创建索引

建立索引的语法:
create index 索引名 on 表名(列名)

注意:在创建索引时候,要求用户具有create any index系统权限.如果没有权限先授权。

grant create any index to scott;

例:在scott模式下,为emp表的deptno列创建索引。

create index INDEX_DEPTNO on emp(deptno);

复合索引基于两个列或多个列的索引。在同一张表上可以有多个索引,但是要求列的组合必须不同,比如:

create index INDEX_JE on emp(job,ename);

5.4.2 查看索引

user_indexes: 系统视图,存放是索引的名称以及该索引是否是唯一索引等信息。

user_ind_columns: 系统视图,存放的是索引名称,对应的表和列等

5.4.3 删除索引

格式:drop index 索引名字

注意:需要有drop any index 权限(grant drop any index to scott)

drop index INDEX_JE;

5.4.4 索引缺点

1、建立索引,系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引。

2、更新数据的时候,系统必须要有额外的时间来同时对索引进行更新,以维持数据和索引的一致性。

实践表明,不恰当的索引不但于事无补,反而会降低系统性能。
因为大量的索引在进行插入,修改和删除操作时比没有索引花费更多的系统时间