视图:
视图是一种虚表,建立在已有表的基础上,视图赖以建立的这些表称为基表向视图提供数据内容的语句为select语句,可以将视图理解为存储起来的select语句.视图向用户提供基表数据的另一种表现形式
使用视图的原因:
控制数据访问
简化查询
避免重复访问相同的数据
创建视图
create [or replace] [force|noforce] view viewName[(alias[,alias]...)]
as subquery
[with check option[constraint constraintName]]
[ with read only[constraint constraintName]];
例子:
create view emp_view
as select * from employees;
修改视图
使用create or replace view子句修改视图
create or replace view empvu80
(id_number,name,sal,department_id)
as select employeee_id,first_name||''||last_name, salary,department_id
from employees
where department_id=80;
create view子句中各列的别名应和子查询中的各列相对应
视图中使用DML规定
可以在简单视图中执行DML操作
当试图定义中包含以下元素之一时不能使用delete
组函数
group by子句
distinct关键字
rownum伪列
当视图定义中包含以下原书之一时不能使用update:
组函数
group by子句
distinct关键字
rownum伪列
列的定义为表达式
当视图定义中包含以下元素之一时不能使用insert:
组函数
group by子句
distinct关键字
rownum伪列
列的定义为表达式
表中非空的列在视图定义中未包括\
屏蔽DML操作
使用with read only选项屏蔽对视图的DML操作
任何DML操作都会返回一个Oracle server错误
例子
create or replace view empvu10
( employee_number,employee_name,job_title)
as se;ect employee_id,last_name,job_id
from employees
where department_id=10
with read only;
删除视图
删除视图只是删除视图的定义,并不会删除基表的数据
drop view viewName;
top-N分析
Top-N分析查询一个列中最大或最小的n个值,最大和最小的值的集合是Top-N分析所关心的
查询最大的几个值的Top-N分析:
select[column_list], rownum
from (select[column_list]
from table
order by Top-N_column)
where rownum<=n;
例如:查询工资最高的三名员工:
select rownum as rank, last_name,salary
from (select last_name,salary,from employees
order by salary desc)
where rownum<=3;
---------
序列
序列是可供多个用户用来产生唯一数值的数据库对象
自动提供唯一的数值
共享对象
主要用于提供主键值
将序列值装入内存可以提高访问效率
create sequence语句
create sequence
[increment by n]
[start with n]
[{maxvalue n}|nomaxvalue]
[{minvalue n}|nominvalue]
[{cycle|nocycle}]
[{cache n|noche}];
创建序列dept_deptid_seq为表department提供主键
不使用cycle选项
create sequence dept_dept_id_seq
increment by 10每次增长10
start with 10从10开始
maxvalue 9999最大值为9999
nocache不缓存
nocycle;不循环
查询序列
查询数据字典视图user_sequence获取序列定义信息
select sequence_name,min_value,max_value, increment_by,last_number
from user_sequences;
如果指定nocache选项,则列last_number显示序列中下一个有效的值
nextval和currval伪列
nextval返回序列中下一个有效的值,任何用户都可以引用
currval中存放序列的当前值
nextval应在currval之前指定,二者应同时有效
序列应用举例
insert into departments(department_id,department_name,location_id)values(dept_deptid_seq.nextval,'suppt',2000);
使用序列
将序列值装入内存可提高访问效率
序列在下列情况下出现裂缝:
回滚
系统异常
多个表同时使用同一序列
如果不将序列的值装入内存(nocache),可使用表user_sequences查看序列当前的有效值
修改序列的增量,最大值,最小值,循环选项,或是否装入内存
例子:
alter sequence dept_deptid_seq
increment by 20
maxvalue 9999
nocache
nocycle;
修改序列的注意事项
必须是序列的拥有者或对序列有alter权限
只有奖励的序列值会被改变
改变序列的初始值只能通过删除序列之后重建序列的方法实现
删除序列
使用drop sequence语句删除序列
drop sequence sequenceName;
----------
索引
索引是一种独立于表的模式对象,可以存储在与表不同的磁盘或表空间
索引被删除或损坏,不会对表产生影响,起影响的只是查询的速度
索引一旦建立,Oracle管理系统会对其进行自动维护,而且由Oracle管理系统决定何时使用索引,用户不用再
查询语句中指定使用哪个索引
在删除一个表时,索引基于该表的索引会自动删除
通过指针加速oracle服务器的查询速度
通过快速定位数据的方法,减少磁盘I/O
创建索引
自动创建:在定义primary key或unique约束系统自动在相应的列上创建唯一性索引
手动创建:用户可以在其他列上创建非唯一性索引,以加速查询
create index indexname on table (column[,column]...);
在表employees的列last_name上创建索引
create index emp_last_name_idx
on employees(last_name);
什么时候创建索引
列中数据值分布范围很广
列经常在where子句或链接条件中出现
表经常被访问而且数据流很大,访问的数据大概占数据总量的2%-4%
什么时候不要创建索引
表很小
列不经常作为连接条件或出现where子句中
查询的数据大于2%到4%
表经常更新
查询索引
可以使用数据字典视图user_indexes和user_id_columns查看索引信息
select ic.index_name,ic.column_name,ic.column_position col_pos,ix.uniqueness
from user_index ix,user_ind_columns ic
where ic.index_name=ix.index_name
and ic.table_name='employees';
删除索引
drop index indexName;
----------
同义词
使用同义词访问相同的对象
方便访问其他用户的对象缩短了对象名字的长度
创建和删除同义词
create synonym d_sum
from dept_sum_vu;
drop synonym d_sum;