其他数据库对象
数据库对象种类
视图
视图介绍
视图的优点
限制数据访问
简化复杂查询
提供数据的相互独立
同样的数据,可以有不同的显示方式
创建视图语句
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];
修改视图语句
CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS SELECT employee_id, first_name || ' ' || last_name,
salary, department_id
FROM employees
WHERE department_id = 80;
屏蔽DML操作
删除视图
删除视图只是删除视图的定义,并不会删除基表的数据
DROP VIEW view;
视图示例
2 as
3 select empno,ename,sal,sal*12 annlsal ,sal*12+nvl(comm,0) income from emp;
SQL> show user
USER 为 "SCOTT"
SQL> /
视图已创建。
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
EMP10 TABLE
EMP20 TABLE
TESTSAVEPOINT TABLE
TESTDELETE TABLE
BIN$91xa7gtoQfiGlzbFlex5HQ==$0 TABLE
TEST3 TABLE
MYPERSON TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMPINCOMEVIEW VIEW
已选择12行。
SQL> desc EMPINCOMEVIEW
名称 是否为空? 类型
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
SAL NUMBER(7,2)
ANNLSAL NUMBER
INCOME NUMBER
SQL> select * from 名称 是否为空
2 ----------------------------------------------------------------------------------- -------
3 EMPNO NOT NUL
4 ENAME
5 SAL
6 ANNLSAL
7 INCOME
8 ;
EMPNO NOT NUL
*
第 3 行出现错误:
ORA-00933: SQL 命令未正确结束
SQL> select * from EMPINCOMEVIEW;
EMPNO ENAME SAL ANNLSAL INCOME
---------- ---------- ----- ---------- ----------
7369 SMITH 800 9600 9600
7499 ALLEN 1600 19200 19500
7521 WARD 1250 15000 15500
7566 JONES 2975 35700 35700
7654 MARTIN 1250 15000 16400
7698 BLAKE 2850 34200 34200
7782 CLARK 2450 29400 29400
7788 SCOTT 3000 36000 36000
7839 KING 5000 60000 60000
7844 TURNER 1500 18000 18000
7876 ADAMS 1100 13200 13200
EMPNO ENAME SAL ANNLSAL INCOME
---------- ---------- ----- ---------- ----------
7900 JAMES 950 11400 11400
7902 FORD 3000 36000 36000
7934 MILLER 1300 15600 15600
已选择14行。
SQL> host cls
序列
什么是序列?
序列: 可供多个用户用来产生唯一数值的数据库对象
定义序列语句
CREATE SEQUENCE sequence
[INCREMENTBY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
创建序列
CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;
查询序列
SELECT sequence_name, min_value, max_value,
increment_by, last_number
FROM user_sequences;
NEXTVAL和CURRVAL伪列
修改序列
修改序列的增量, 最大值, 最小值, 循环选项, 或是否装入内存
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;
修改序列注意事项
删除序列
序列语句实例
SQL> --序列
SQL> create sequence myseq;序列已创建。
SQL> create table testseq
2 (tid number,tname varchar2(20));
表已创建。
SQL> --insert into testseq values( ,'aaa');
SQL> select myseq.currval from dual;
select myseq.currval from dual
*
第 1 行出现错误:
ORA-08002: 序列 MYSEQ.CURRVAL 尚未在此会话中定义
SQL> select myseq.nextval from dual;
NEXTVAL
----------
1
已选择 1 行。
SQL> insert into testseq values(myseq.nextval ,'aaa');
已创建 1 行。
SQL> insert into testseq values(myseq.nextval ,'aaa');
已创建 1 行。
SQL> /
已创建 1 行。
SQL> select * from testseq;
TID TNAME
---------- --------------------
2 aaa
3 aaa
4 aaa
已选择3行。
SQL> select myseq.currval from dual;
CURRVAL
----------
4
已选择 1 行。
SQL> hostcls
SP2-0042: 未知命令 "hostcls" - 其余行忽略。
SQL> host cls
索引
索引:
创建索引
CREATE INDEXindex
ON table(column[,column]...);
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表经常更新
查询索引
SELECT ic.index_name, ic.column_name,
ic.column_positioncol_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 index;
DROP INDEX upper_last_name_idx;
索引语句示例
SQL> --索引
SQL> create index myindex on emp(deptno,job);索引已创建。
SQL> --同义词:别名
SQL> show user
USER 为 "SCOTT"
SQL> select count(*) from hr.EMPLOYEES;
select count(*) from hr.EMPLOYEES
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
SQL> /
COUNT(*)
----------
107
已选择 1 行。
同义词
为何使用同义词
使用同义词访问相同的对象:
l方便访问其它用户的对象
l缩短对象名字的长度
创建和删除同义词
CREATE SYNONYM d_sum
FOR dept_sum_vu;
DROP SYNONYM d_sum;
同义词语句实例
create synonym hremp for hr.EMPLOYEES
*
第 1 行出现错误:
ORA-01031: 权限不足
SQL> /
同义词已创建。
SQL> select count(*) from hremp;
COUNT(*)
----------
107
已选择 1 行。
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
EMP10 TABLE
EMP20 TABLE
TESTSAVEPOINT TABLE
TESTDELETE TABLE
BIN$91xa7gtoQfiGlzbFlex5HQ==$0 TABLE
TEST3 TABLE
MYPERSON TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMPINCOMEVIEW VIEW
TESTSEQ TABLE
HREMP SYNONYM
已选择14行。
SQL> spool off