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

每天随机学两条SQL语句：

查询用户权限：select * from session_privs;
以管理员登录：sqlplus / as sysdba;

一。视图

特点	简单视图	复杂视图
表的数量	一个	一个或多个
约束函数	没有	有
是否包含分组	不包含	包含
可以对试图进行更新	可以	不一定

1.创建简单视图

①创建一个视图，包含部门号为10的雇员信息。
create or replace view empvu10
as select empno, ename, job
from emp
where deptno = 10;
②别名（查询语句中）
create or replace view empvu10
as select empno “员工编号”,ename “员工姓名”,sal “员工工资”
from emp
where deptno=10;
③别名2（查询语句外）
create or replace view mepvu20( emplyee_number, name, salary)
as
select empno, ename, sal
from emp
where deptno=20;

2.创建复杂视图

create or replace view dept_sal_vu(“部门名称”,“最大工资”,“最小工资”,“平均工资”)
as
select d.dname, max(e.sal),min(e.sal),avg(e.sal)
from emp e,dept d
where e.deptno= d.deptno
group by d.dname;

3.内联视图

就是临时表

select a.ename, a.sal, a.deptno, b.maxsal
from emp a,(select deptno, max(sal) maxsal from emp group by deptno)b
where a.deptno = b.deptno
and a.sal<b.maxsal;

4.更改视图数据

①对于简单视图，可以把视图当作一张表，更改其中的某条数据。
update empvu20 set name=‘lisi’ where emplyee_number = 6666;
②对于复杂视图，不可以更改数据。
比如：
含有聚合函数、含有group by、含有distinct、含有rownum伪列。
③对于简单视图，也能限制视图修改功能
create or replace view mepvu20( emplyee_number, name, salary)
as
select empno, ename, sal
from emp
where deptno=20
with read only;

5.删除视图：

drop view empvu;

二。序列

1.定义：

自动产生唯一的数字；
是一个可以共享的数据库对象；
典型的用于生成数据库的主键值，节省应用程序的代码；
当缓存在内存中时，能够提升存取效率。

2.语法：

create sequence 序列名
[increment by n]
[start with n]
[{maxvalue n | nomaxvalue}]
[{minvalue n |nominvalue}]
[{cycle | nocycle}]
[{cache | nocache}];

3.例子

create sequence dept_deptno
increment by 1
start with 91
maxvalue 100
nocache
nocycle;

4.使用

①nextval
返回下一个可用的序列值。每访问一次将产生一个唯一的新值，即使对不同的用户，其值也是唯一的。
注意：nextval是不能rollback的。
②currentval
返回当前的序列值。只有当nextval被访问之后，currentval伪列才能包含一个值。

insert into dept(deptno, dname, loc) 
	values(dept_deptno.nextval, 'Tring','南京');

5.使用序列应注意：

①将序列值缓存在内存中，是这些值访问起来更快。
②下列情况会使序列的值产生间隔：
回滚操作
系统崩溃
序列值同时也用于其他表
③如果一个序列以nocache选项建立，那么可以通过查询user_sequences表来看下一个可用的序列值，而不会序列的当前值增加。

6.修改序列

alter sequences dept_deptno
increment by 1
maxvalue 9999
nocache
…
nocache;

7.修改序列应注意：

①必须是序列的拥有者，或者拥有序列的修改权限。
②只有将来会用到的序列值才会受到影响。
③只有重建序列才能更改序列的start with 选项。
④序列的修改可能会带来冲突，例如maxvalue如果小于当前值就会冲突。

8.删除序列

drop sequence dept_deptno;

9.删除序列应注意：

①一旦序列被删除了，序列就不可以被访问了。
②删除以前增加的序列值，依旧在。

10.查看序列：

select * from user_sequences;

三。索引

1.定义：

一个数据库模式对象；
Oracle利用索引来加快对数据行的访问；
依靠索引来快速定位数据，从而减少了磁盘的io次数；
与其他表是相互独立的数据库对象；
Oracle服务器自动对索引进行维护和使用；

2.创建

①自动创建
如果建表的时候加了primary key或者unique，则Oracle自动创建索引。
②手动创建
create index emp_ename_idx
on emp(ename);

3.什么时候建索引

①经常出现在where子句中的列，或者连接条件中频繁使用的列。
②包含的不同值很多的列。
③包含大量空值的列。
④表中数据行数非常大，而且只有2%-4%的数据行被查出来的列；

4.什么时候不建索引

①空表
②查询条件中不经常使用的列；
③大多数基于该表的查询，所查询出来的数据量远多于2%-4%；
④表被频繁修改；

5.查询所有索引：

select INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes;

6.查询某张表的索引建在哪一个列上：

select INDEX_NAME,COLUMN_NAME from user_ind_columns where TABLE_NAME=‘DEPT’;

7.删除索引

drop index dept_index;

四。同义词

1.创建：

为视图创建一个简短的名字：
create synonym d_sum
for dept_sum_vu;

2.删除

drop synonym s_sum;

注意：
可以把同义词建成共有的，这样大家就都能访问了。
create public synonym d_sum
for dept_sum_vu;