首页 > 代码库 > 视图、序列、索引、同义词
视图、序列、索引、同义词
一、视图
语法：
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY];
1、简单视图：
创建视图：SQL> create view test_view as select * from emp where deptno=10;查询视图：SQL> select * from test_view;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ----------7782 CLARK MANAGER 7839 09-6月 -81 2450 107839 KING PRESIDENT 17-11月-81 5000 107934 MILLER CLERK 7782 23-1月 -82 1300 10查看视图结构：
SQL> DESC TEST_VIEW;
2、创建复杂视图
SQL> CREATE VIEW AVG_SAL_COMM AS SELECT DNAME 部门名称,D.DEPTNO 部门编号,COUNT(ENAME) 部门总人数,ROUND(AVG(NVL(SAL,0)),2) 部门平均工资,ROUND(AVG(NVL(COMM,0)),1) 部门平均资金 FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO=D.DEPTNO GROUP BY DNAME,D.DEPTNOORDER BY D.DEPTNO; SQL> SELECT * FROM AVG_SAL_COMM;部门名称 部门编号 部门总人数 部门平均工资 部门平均资金-------------- ---------- ---------- ------------ ------------ACCOUNTING 10 3 2916.67 0RESEARCH 20 5 2175 0SALES 30 6 1566.67 366.7OPERATIONS 40 0 0 0testSEQ 94 0 0 0
3、在视图定义中，可以使用WITH READ ONLY选项来保证该视图上不能进行DML操作.
4、删除视图
DROP VIEW VIEW_NAME;
二、序列
1、创建序列
--创建一个名称为 DEPT_DEPTNO的序列值，以用于DEPT表.不要设置 CYCLE 选项.
CREATE SEQUENCE DEPT_DEPTNOINCREMENT BY 1START WITH 91MAXVALUE 100NOCACHE --CACHE(缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。NOCYCLE;/*NEXTVAL 返回下一个可用的序列值,每访问一次，将产生一个新的值。. CURRVAL 返回当前的序列值.只有当NEXTVAL被访问之后，CURRVAL伪列才能包含一个值.所以刚创建好的序列，第一次访问CURRVAL时报错。必须先访问NEXTVAL再访问CURRVAL。*/
2、序列的使用及查询
SQL> SELECT DEPT_DEPTNO.CURRVAL FROM DUAL;SELECT DEPT_DEPTNO.CURRVAL FROM DUAL*第 1 行出现错误:ORA-08002: 序列 DEPT_DEPTNO.CURRVAL 尚未在此会话中定义SQL> SELECT DEPT_DEPTNO.NEXTVAL FROM DUAL;NEXTVAL----------91SQL> SELECT DEPT_DEPTNO.CURRVAL FROM DUAL;CURRVAL----------91SQL>--使用序列向表DEPT中插入数据SQL> insert into dept values(dept_deptno.nextval,‘testSEQ‘,‘testLOC‘);已创建 1 行。--如果一个序列是以 NOCACHE选项建立的, 那么可以通过查询USER_SEQUENCES 表来查看下一个可用的序列值，而不会使序列的当前值增加.SQL> SELECT * FROM USER_SEQUENCES;SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER------------------------------ ---------- ---------- ------------ - - ---------- -----------DEPT_DEPTNO 1 100 1 N N 0 95SQL>=========SELECT * FROM ALL_SEQUENCES;SELECT * FROM DBA_SEQUENCES;
3、修改序列
可以更改序列的增量值、最大值、最小值、循环或者缓存选项。不能修改序列的初始值，否则会报错：ORA-02283: 无法变更启动序列号
ALTER SEQUENCE DEPT_DEPTNO INCREMENT BY 2;
ALTER SEQUENCE DEPT_DEPTNO MAXVALUE 200;
4、删除序列
DROP SEQUENCE SEQUENCE_NAME;
三、索引
1、创建索引
自动创建:当在创建表时，如果指定了 PRIMARY KEY或者 UNIQUE约束，那么将自动创建索引.
手动创建:用户可以在某个列上建立非唯一的索引，以加快基于该行的查询.
CREATE INDEX index_name
ON table (column[, column]...);
--创建索引，以提高对表EMP的ENAME列的访问速度.
CREATE INDEX EMP2_ENAME_IDX ON EMP2(ENAME);
--什么时候创建索引
欲创建索引的列在 WHERE子句或者连接条件中频繁使用.
该列所包含的不同值很多.
该列包含大量的空值.
表中的数据行数非常大，而且只有 2–4% 数据行被查询出来.
--什么时候没必要创建索引
表是空的.
列在查询条件中不经常使用.
大多数基于该表的查询，所查询出的数据量远多于2–4% 行.
表被频繁修改.
2、查看索引
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 = ‘EMP2‘;
3、基于函数的索引
基于函数的索引也就是基于表达式的索引.
索引表达式由表的列、常量、 SQL函数或者用户自定义函数组成.
SQL> CREATE TABLE test (col1 NUMBER);
SQL> CREATE INDEX test_index on test(col1,col1+10);
SQL> SELECT col1+10 FROM test;
4、删除索引
要删除一个索引，必须是索引的拥有者，或者具有 DROP ANY INDEX的权限.
从数据字典中删除 EMP_ENAME_IDX 索引.
SQL> DROP INDEX EMP2_ENAME_IDX;
索引已删除。
四、同义词
通过创建一个同义词 (对象的另一个名字)来简化对数据库中对象的存取. 缩短了对象的名字长度.
CREATE [PUBLIC] SYNONYM synonym
FOR object;
例：为视图QUERY_TABSPACE创建一个简短的名字Q_SPACE;
创建视图：CREATE VIEW QUERY_TABSPACE AS (SELECT /*+NO_MERGE(A) NO_MERGE(B)*/B.TABLESPACE_NAME 表空间名称, ROUND((B.BYTES/1024)/1024,2) 总空间大小MB,NVL2(A.BYTES,ROUND((B.BYTES-NVL(A.BYTES,0))/1024/1024,2),B.BYTES) 已使用大小MB,NVL2(A.BYTES,ROUND(NVL(A.BYTES,0)/1024/1024,2),0) 未使用大小MB,NVL2(A.BYTES,TO_CHAR(ROUND(((B.BYTES-NVL(A.BYTES,0))/B.BYTES)*100,2),‘990.0‘),‘100‘)||‘%‘ 已使用率FROM (SELECT TABLESPACE_NAME,SUM(BYTES) BYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME)A,(SELECT TABLESPACE_NAME,SUM(BYTES) BYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) BWHERE B.TABLESPACE_NAME=A.TABLESPACE_NAME(+))查询视图：SELECT * FROM QUERY_TABSPACE;创建同义词：CREATE SYNONYM Q_SPACE FOR QUERY_TABSPACE;查询同义词：SELECT * FROM Q_SPACE;删除同义词DROP SYNONYM Q_SPACE;
视图、序列、索引、同义词