oracle学习（6）

其他数据库对象

视图：

常见数据库对象——视图：从表中抽出的逻辑上相关的数据集合。

所以：1. 视图基于表。2. 视图是逻辑概念。3. 视图本身没有数据。

创建视图

创建语法与创建表类似，只需要将table → view即可:

SQL> create view empincomeview  

as

select e.empno, e.ename, e.sal, e.sal*12 annualsal, e.sal*12+nvl(comm, 0) income, d.dname

from emp e, dept d

where e.deptno = d.deptno   

出错提示：权限不足。因为创建视图需要“create view”的权限。默认scott用户没有该种权限。加之！

添加步骤：

1. 使用管理员登陆：sqlplus / as sysdba

2. 给scott用户增加权限： SQL>  grant create view to scott;  

3. 执行“/”可成功创建视图empincomeview。  

4. 视图的操作和表的操作完全一样。 SQL>  select * from empincomeview;

视图的优点：

视图的优点  

1. 简化复杂查询： 原来分组、多表、子查询等可以用一条select * from xxxview代替。

视图可以看做是表的复杂的SQL一种封装。

2. 限制数据访问： 只看视图的结构和数据是无法清楚视图是怎样得来的。可以限制数据的访问。例如：

银行项目，所谓的各个“表”都是“视图”，并有可能只是“只读视图”

注意：1. 视图不能提高性能 2. 不建议通过视图对表进行修改。

创建视图细节：

使用下面的语法格式创建视图：

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view

   [(alias[, alias]...)]

  AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ ONLY [CONSTRAINT constraint]];

1. 视图只能创建、删除、替换。（不能修改，修改即替换replace）

如：刚刚创建的empincomeview，其他语句不变，将create一行改写成：

SQL> create or replace view empincomeview 视图不存在则创建、存在则替换。

  as

      select…… from…..where…..

      with read only          可以将视图设为只读视图。

2. 别名：可以写在子查询select各个列的后面，也可以写在视图的名字后面。

3. with read only  表示该视图为只读视图。

4. with check option 了解即可， 举例：

SQL>  create view testview

as

select * from emp where deptno=10

with check option;   

SQL> insert into testview values(******, 10); 不建议向视图插入，但可以做。向视图插入10号员工。

SQL> insert into testview values(******, 20); 因为创建视图时加了“with check option”，所以失败。

视图中使用DML的规定：     

一：

当视图定义中包含以下元素之一时不能使用delete:

1. 组函数
2. GROUP BY 子句
3. DISTINCT 关键字
4. ROWNUM 伪列

二：

当视图定义中包含以下元素之一时不能使用update :

1. 组函数
2. GROUP BY子句
3. DISTINCT 关键字
4. ROWNUM 伪列
5. 列的定义为表达式

三：

当视图定义中包含以下元素之一时不能使用insert :

1. 组函数
2. GROUP BY 子句
3. DISTINCT 关键字
4. ROWNUM 伪列
5. 列的定义为表达式
6. 表中非空的列在视图定义中未包括

总结一句话：不通过视图做insert、update、delete操作。因为视图提供的目的就是为了简化查询。

删除视图：SQL> drop view testview  不加“purge”关键字。

1. 1. 序列：

可以理解成数组：默认，从[1]开始，长度[20] [1, 2, 3, 4, 5, 6, …, 20] 在内存中。

          *

由于序列是被保存在内存中，访问内存的速率要高于访问硬盘的速率。所以序列可以提高效率。

​​​​​​​序列的使用：

1. 初始状态下：指针*指向1前面的位置。欲取出第一个值，应该将*向后移动。每取出一个值指针都向后移。

2. 常常用序列来指定表中的主键。

3. 创建序列：create sequence myseq  来创建一个序列。

创建序列：

CREATE SEQUENCE sequence

       [INCREMENT BY n]

       [START WITH n]

       [{MAXVALUE n | NOMAXVALUE}]

       [{MINVALUE n | NOMINVALUE}]

       [{CYCLE | NOCYCLE}]

       [{CACHE n | NOCACHE}];

NOCACHE表示没有缓存，一次不产生20个，而只产生一个。

​​​​​​​创建序列

创建序列、表，以备后续测试使用：

SQL> create sequence myseq  按默认属性创建一个序列。

SQL> create table tableA     

  (tid number, tname varchar2(20))   tid作为主键，准备使用序列来向表中插入值。

​​​​​​​序列的属性：

每个序列都有两个属性  

NextVal 必须在CurrVal之前被指定。因为初始状态下，CurrVal指向1前面的位置，无值

对于新创建的序列使用SQL>  select myseq.currval from dual    得到出错。

  但SQL>  select myseq.nextval from dual    可以得到序列的第一值1.

  此时再执行SQL>  select myseq.currval from dual        currval的值也得到1

使用序列给tableA表创建主键tid：

SQL>  insert into tableA values(myseq.nextval, ‘aaa’)         

只有nextval取完会向后移动，使用currval不会移动。

SQL>  insert into tableA values(myseq.nextval, ‘bbb’)         

继续使用nextval向表中添加主键tid

……

SQL>  insert into tableA values(myseq.nextval, &name)   

可以使用“&”和“/”来指定名字。

SQL> select * from tableA;  

由于前面测试currval和nextval关系时调用过nextval，所以tableA的tid起始从2开始。

查询序列的属性：SQL> select * from user_sequences;   user_sequences为数据字典视图。

修改序列：

1. 必须是序列的拥有者或对序列有 ALTER 权限
2. 只有将来的序列值会被改变
3. 改变序列的初始值只能通过删除序列之后重建序列的方法实现

删除序列：SQL> drop sequence myseq;  

​​​​​​​使用序列需要注意的问题：

1. 序列是公有对象，所以多张表同时使用序列，会造成主键不连续。 如：[1, 2, 3, 4, 5, …, 20]

tableA: 1 2 4

tableB: 3 5 A、B表有可能主键不连续。

2. 回滚也可能造成主键不连续。 如：多次调用insert操作使用序列创建主键。但是当执行了rollback后再次使用insert借助序列创建主键的时候，nextval不会随着回滚操作回退。

3. 掉电等原因，也可能造成不连续。由于代表序列的数组保存在内存中，断电的时候内存的内容丢失。恢复供电时候，序列直接从21开始。

​​​​​​​索引：

索引，相当于书的目录，提高数据检索速度。提高效率（视图不可以提高效率）

1. 一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中
2. 索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度
3. 索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引. 用户不用在查询语句中指定使用哪个索引
4. 在删除一个表时, 所有基于该表的索引会自动被删除
5. 通过指针加速 Oracle 服务器的查询速度

通过快速定位数据的方法，减少磁盘 I/O。

上图中：

1. emp表中保存数据，其中包含部门号列。有10号部门，有20部门员工

2. 当 select * from emp where deptno=10 的时候。由于10号部门员工不连续，没规律。

为了提高访问速度，可以在数据库中，依照rowid给deptno列建立索引

SQL> create index myindex on emp(deptno)   

这样就建立了“索引表”可以通过rowid保存的行地址快速的找到表中数据。即使表中数据不连续。

3. 建立了索引以后，如果再执行select语句的时候，会先检查表上是否有索引表。如果有，可以通过有规律 的rowid找到不连续的数据。

4. Oracle的数据库中，索引有 B树索引（默认）和 位图索引两种。

5. 使用create index 索引表名 on 表名（列名1， 列名2…）;来创建索引表。由数据库自动进行维护。 使用主键查询数据最快速，因为主键本身就是“索引”，所以检索比较快。   

索引使用的场景：

以下情况可以创建索引:

1. 列中数据值分布范围很广
2. 列经常在 WHERE 子句或连接条件中出现
3. 表经常被访问而且数据量很大 ，访问的数据大概占数据总量的2%到4%

下列情况不要创建索引:

1. 表很小
2. 列不经常作为连接条件或出现在WHERE子句中
3. 查询的数据大于2%到4%
4. 表经常更新

删除索引：SQL> drop index myindex;

1. 1. synonym同义词：

就是指表的别名。

如：scott用户想访问hr用户下的表employees。默认是不能访问的。需要hr用户为scott用户授权：

SQL>  sqplus hr/11 或 conn hr/11（已登录界面， 切换登陆）

SQL>  grant select on employees to scott      hr用户为scott用户开放了employees表的查询权限。

  这时scott用户就可以使用select语句，来查询hr用户下的employees表的信息了。

SQL>  select count(*) from hr. employees   (若用户名叫zhangsanfeng则zhangsanfeng.employees)

hr.employees名字过长，为了方便操作，scott用户为它重设别名：

SQL>  create synonym hremp for hr.employees;   为hr.employees创建了同义词。

如有权限限制，那么切换管理员登录，给scott用户添加设置同义词权限。

SQL>  conn / as sysdba

SQL>  grant create synonym to scott    

SQL>  select count(*) from hremp    使用同义词进行表查询操作。

 ——同义词、视图 等用法在数据保密要求较高的机构使用广泛，如银行机构。好处是既不影响对数据的操作，同时又能保证数据的安全。

OCA

OCP

OCM 认证