Oracle数据库教程(第五章 视图及索引)笔记

五、视图及索引

视图和表的区别：

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、更新数据的时候,系统必须要有额外的时间来同时对索引进行更新,以维持数据和索引的一致性。

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