oracle基础总结（十）

         视图、序列、同义词、索引

 

1、  视图

  视图是从一个表或多个表中导出的虚表,它与常规表不同之处在于它并不存储数据—它只是在一个存储的查询中定义.视图同真实表一样,也包含一系列带有名称的列和数据。

 

  视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。

  向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.

  视图向用户提供基表数据的另一种表现形式

 

 

 

视图分为简单视图还有复杂视图

 

所谓简单的视图是只引用一个表,并且它只从基础表中检索数据,不允许修改其中的数据；

复杂的视图是引用多个表,视图越复杂、视图内再查询的表达式就越多越复杂.

创建视图的语法：

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

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

 AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ ONLY [CONSTRAINT constraint]];

 

注意：在创建视图时应先赋予创建视图的权限，create view或create any view权限

//切换到管理员然后赋予权限

SQL> grant create any view to redarmy;

SQL> grant create view to redarmy;

 

 

为什么使用视图?

  控制数据访问:提供了一种安全机制,通过视图显示数据时,屏蔽用户不应该知道的数据.

  简化查询:select语句构造了一个复杂的查询语句,每次使用必须都得调用它,根据复杂查询的语句创建一个试图,来简化查询。

 

创建视图时在子查询中给列定义别名

SQL> create view usersview as select username name,password pass from users;

//查询视图

SQL> select * from usersview;

 

//检索视图的定义

SQL> select * from user_views;

 

复杂视图：

是通过多个表的连接查询获得的，又称为连接视图

SQL> create view empdept as (select e.*,d.dname,d.loc from emp e inner join dept d on e.deptno=d.deptno);

使用CREATE OR REPLACE VIEW 子句修改视图

SQL> create or replace view usersview as(select * from users);

//创建带有函数,groupby子句的视图

SQL> create or replace view gde as ( select deptno,count(*) cou from emp group by deptno);

//创建连接视图 带有函数 group by子句 order by 子句

SQL> create view gdeo as select e.deptno,count(*) cou from emp e inner join dept d on e.deptno=d.deptno group by e.deptno order by e.deptno;

//在上面的基础上带有having子句

SQL> create view gdeoh as select e.deptno,count(*) cou from emp e inner join dept d on e.deptno=d.deptno group by e.deptno having count(*)>3 order by e.deptno;

 

 

 删除视图的操作

   Drop view 视图名;

 

2、  序列

序列: 是oacle提供的用于产生一系列唯一数字的数据库对象。

l  自动提供唯一的数值

l  共享对象

l  主要用于提供主键值

l  将序列值装入内存可以提高访问效率

创建序列：

1、  要有创建序列的权限 create sequence 或 create any sequence

2、  创建序列的语法

CREATE SEQUENCE sequence  //创建序列名称

       [INCREMENT BY n]  //递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是1

       [START WITH n]    //开始的值,递增默认是minvalue 递减是maxvalue

       [{MAXVALUE n | NOMAXVALUE}] //最大值

       [{MINVALUE n | NOMINVALUE}] //最小值

       [{CYCLE | NOCYCLE}] //循环/不循环

       [{CACHE n | NOCACHE}];//分配并存入到内存中

 

  NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用

  CURRVAL 中存放序列的当前值

  NEXTVAL 应在 CURRVAL 之前指定 ，二者应同时有效

 

 

//修改序列的增量, 最大值, 最小值, 循环选项, 或是否装入内存

alter SEQUENCE sequence  //创建序列名称

       [INCREMENT BY n]  //递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是1

       [START WITH n]    //开始的值,递增默认是minvalue 递减是maxvalue

       [{MAXVALUE n | NOMAXVALUE}] //最大值

       [{MINVALUE n | NOMINVALUE}] //最小值

       [{CYCLE | NOCYCLE}] //循环/不循环

       [{CACHE n | NOCACHE}];//分配并存入到内存中

修改序列的注意事项：

l   必须是序列的拥有者或对序列有 ALTER 权限

l   只有将来的序列值会被改变

l   改变序列的初始值只能通过删除序列之后重建序列的方法实现

删除序列

l   使用DROP SEQUENCE 语句删除序列

l   删除之后，序列不能再次被引用

 

 

3 同义词

     同义词是表、索引、试图等模式对象的一个别名。同义词知识数据库对象的一个替代名，在使用同义词时，Oracle会将其翻译为对应的对象的名称。同义词只在Oracle数据库的数据字典中保存其定义描述，因此同义词并不占用任何实际的储存空间。

在开发数据库应用的程序时，应当尽量避免直接引用表、视图、或其他数据库对象的名称，而改用这些对象的同义词。这样可以避免当管理员对数据库对象作出修改和变动之后，必须重新编译应用程序。使用同义词后，即使引用的对象发生变化，也只需要在数据库中对同义词进行修改，而不必对引用程序做任何改动。

 Oracle中的同义词分为两种类型：公有同义词和私有同义词。公有同义词在数据库中所有的用户都可以使用；私有同义词由创建它的用户所拥有，用户可以控制其他用户是否有权使用属于自己的同义词。

创建和删除同义词：

  为视图DEPT_SUM_VU 创建同义词

    CREATE SYNONYM  d_sum

FOR  dept_sum_vu;

Synonym Created.

删除同义词

DROP SYNONYM d_sum;

Synonym dropped.

 

4 索引

 索引:

l   一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中

l   索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度

l   索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管            理系统决定何时使用索引. 用户不用在查询语句中指定使用哪个索引

l   在删除一个表时, 所有基于该表的索引会自动被删除

l   通过指针加速 Oracle 服务器的查询速度

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

 

创建索引

自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引

手动创建: 用户可以在其它列上创建非唯一的索引，以加速查询

l   在一个或多个列上创建索引

           CREATE INDEX index

ON table (column[, column]...);

在表 EMPLOYEES的列 LAST_NAME 上创建索引

CREATE INDEX        emp_last_name_idx

ON           employees(last_name);

Index created.

 

什么时候创建索引

        以下情况可以创建索引:

l 列中数据值分布范围很广

l 列经常在 WHERE 子句或连接条件中出现

l 表经常被访问而且数据量很大 ，访问的数据大概占数据总量的2%到4%

什么时候不要创建索引

       下列情况不要创建索引:

l 表很小

l 列不经常作为连接条件或出现在WHERE子句中

l 查询的数据大于2%到4%

l 表经常更新

查询索引

可以使用数据字典视图 USER_INDEXES 和 USER_IND_COLUMNS 查看索引的信息

        SELECT       ic.index_name, ic.column_name,

       ic.column_position col_pos,ix.uniqueness

FROM         user_indexes ix, user_ind_columns ic

WHERE       ic.index_name = ix.index_name

AND   ic.table_name = 'EMPLOYEES';

删除索引

使用DROP INDEX 命令删除索引

 DROP INDEX index;

删除索引UPPER_LAST_NAME_IDX

DROP INDEX upper_last_name_idx;

Index dropped.

只有索引的拥有者或拥有DROP ANY INDEX权限的用户才可以删除索引