Oracle 复习 视图，索引，序列，同义词

概念: 从一个或多个表或视图中导出的虚表，其结构和数据是建立在对表的查询基础上
实质：一条查询语句

语法: create [ or replace ] [ force ] view [schema.]view_name
       [ (column1,column2,...) ]
        as 
       select ...
    [ with check option ]                      
    [ constraint constraint_name ]
    [ with read only ];

tips:

1 or replace: 如果存在同名的视图, 则使用新视图"替代"已有的视图
2 force:“强制"创建视图,不考虑基表是否存在,也不考虑是否具有使用基表的权限
3 column1,column2,…：视图的列名,列名的个数必须与select查询中列的个数相同; 如果select查询包含函数或表达式, 则必须为其定义列名.此时,
既可以用column1, column2指定列名, 也可以在select查询中指定列名.
4 with check option:指定对视图执行的dml操作必须满足“视图子查询”的条件即,对通过视图进行的增删改操作进行"检查”,要求增删改操作的数据,
必须是select查询所能查询到的数据,否则不允许操作并返回错误提示. 默认情况下,
在增删改之前"并不会检查"这些行是否能被select查询检索到.
5 with read only：创建的视图只能用于查询数据,而不能用于更改数据.

创建视图:

Create view v_emp as 
select * from emp where sal>2000

提示1：用户需要有create view的权限

grant create view to c##scott;

提示2：视图中如果对字段进行了函数或数学计算，则必须为该字段定义别名。

查看视图

desc 视图名//查看视图结构
select * from 视图名//查看视图内容
select * from user_views;//查看视图定义

DML操作
对视图进行DML操作，实际上就是对视图的基表中的字段执行DML操作。
Oracle会自动判断哪些视图可以更新

desc user_updatable_columns;

对简单视图进行DML操作

SQL> insert into vw_emp values(1,'a','aa','05-JUN-88',10);
SQL> update vw_emp set ename='cc' where ename='KING';
SQL> delete vw_emp where ename='cc';
SQL> select * from vw_emp where deptno=10;

1. 若基表的数据发生变化，视图里的数据是否会跟着变化？
会！
2. 若修改了视图的数据，表中数据是否会随之变化？
会改变，修改视图就是修改基表。 若修改不存在的数据，可能不会改变
3. 能否基于一个视图，再创建视图？
可以，但好像没必要。

创建只读视图

create view vw_emp_readonly 
as select empno,ename,job,hiredate,deptno from emp 
with read only;

创建检查约束视图with check option

create view vw_emp_check 
as select empno,ename,job,hiredate,deptno from emp where deptno=10 
with check option;

索引：

是一个单独的、物理的数据库对象。
用于存放表中每一条记录的位置的对象。
在创建索引时，先要对索引字段进行排序。
索引由Oracle自动维护
优点：提高查询速度
缺点：创建和维护索引需要时间；索引需要物理空间，随着数据量的增大而增大；

索引的建立原则

1.索引应该建立在WHERE子句中经常使用的列上。如果某个大表经常使用某个字段进行查询，并且检索的啊行数小于总表行数的5%，则应该考虑在该列上建立索引。
2.对于两个表连接的字段，应该建立索引。
3.如果经常在某表的一个字段上进行Order By的话，则也应该在这个列上建立索引。
4.不应该在小表上建立索引。

自动创建：通过约束，系统创建
手工创建:

create [unique] [bitmap] index index_name on table_name(column_name)
[reverse] [tablespace tablespace_name]

创建B树索引

create index dname_dept on dept(dname);

索引的创建语句非常简单。

CREATE INDEX 索引名 ON 表名(列名);

除了单列索引，还可以创建包含多个列的复合索引。

CREATE INDEX 索引名 ON 表名(列名1, 列名2, 列名3, ...);

删除索引也非常简单。

DROP INDEX 索引名;

查看某个表中的所有索引也同样简单。

SELECT * FROM ALL_INDEXES WHERE TABLE_NAME = '表名'

还可以查看某个表中建立了索引的所有列。

SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME = '表名'

默认情况下，创建的索引是不唯一索引
创建基于函数的索引

create index ename_lower_index 
on emp(lower(ename));

select * from emp 
where lower(ename)='smith';

使用数据字典user_indexes，查看当前用户下所建立的索引。

select index_name, index_type , table_name, tablespace_name　from user_indexes;

重命名索引

alter  index　index_name rename to new index_name;
alter index dept_deptno_pk rename to pk_deptno_dept;

序列：

创建序列：

CREATE SEQUENCE 序列名
	[INCREMENT BY n]
	[START WITH n]
	[{MAXVALUE n | NOMAXVALUE}]
	[{MINVALUE n | NOMINVALUE}]
	[{CYCLE | NOCYCLE}]
	[{CACHE n | NOCACHE}];

create sequence dept_seq 
  increment by 10
  start with 50
  maxvalue 100
  cycle
  nocache; 

修改序列:

ALTER SEQUENCE dept_deptno
  INCREMENT BY 1
  MAXVALUE 999999
  NOCACHE
  NOCYCLE;

删除序列：

drop sequence dept_seq;

使用视图 USER_SEQUENCES 可以查到序列信息.

desc user_sequences;

select sequence_name,
min_value,max_value,cache_size 
from user_sequences;

使用序列:
NEXTVAL 返回下一个有效的序列值.
每执行一次会返回一个新的数值。
CURRVAL返回当前的序列值.

insert into dept values(myseq.nextval,' MARKETING ',SHANDONG');

同义词：

同义词是数据库对象的别名。
通过同义词可以简化对象的访问。

创建同义词：

CREATE [PUBLIC] SYNONYM 同义词名
FOR    对象名;

public 选项表示所有用户均可使用.

删除同义词：

DROP SYNONYM dept_seq;