目录
权限小结:
中文名 | 名称 | 当前用户模式下创建 | 其他用户模式下(或其他身份)创建 |
---|---|---|---|
视图 | view | create view | create any view |
索引 | index | create index | create any index |
序列 | sequence | create sequence | create any sequence |
公有同义词 | public synonym | create public synonym | -----公有同义词所有用户均可访问--- |
私有同义词 | synonym | create synonym | create any synonym |
1.视图对象
1.1创建视图
视图,虚表,并不在数据库中存储数据值,只在数据字典中存储视图定义。
视图可以建立在关系表上,也可以建立在其他视图上,甚至可以同时建立在两者之上
create [or replace] view 视图名 [列别名1[,列别名2]...)]
as <视图对应的子查询语句>
[with check option] [constraint 约束名]
[with read only]with check option:指定视图上定义的check约束
with read only:定义只读视图若定义时不提供列别名则使用子查询的列名或列别名;若视图子查询包含函数或表达式,则必须定义列别名。
1)简单视图
简单视图就是基于但个表,无函数、表达式和分组数据的视图
eg:在scott模式下,创建一个查询部门编号为20的视图
create or replace view emp_view as select empno,ename,job,deptno from emp where deptno=20;
简单视图可以直接insert,update,delete
eg:在scott模式下,向视图 emp_view中插入一条记录,然后修改这条记录的ename字段值,接着查询,然后删除该记录insert into emp_view values(9527,'东方','MANAGER',20); update emp_view set ename='西方' where empno=9527; select * from emp_view; delete from emp_view where empno=9527; commit;
2)建立只读视图
with read only子句定义只读视图,只读视图只可以对其执行select,而禁止执行insert,update,delete
eg: 创建只读视图,获取部门编号不等于88的所有部门信息
create or replace view emp_view_readonly as select * from dept where deptno!=88 with read only;
测试:
select * from emp_view_readonly; update emp_view_readonly set loc='安庆' where deptno=60;
3)复杂视图
复杂视图是指包含函数、表达式或分组数据的视图。
使用复杂视图的主要操作是为了简化查询操作。
需要注意的是,当视图子查询包含函数或表达式时,必须为其定义列别名。
复杂视图主要用于执行查询操作。
eg:创建视图,能够查询每个部门的工资情况,最高工资、最低工资、平均工资
create or replace view emp_view_complex as select deptno 部门编号,max(sal) 最高工资,min(sal) 最低工资,avg(sal) 平均工资 from emp group by deptno;
测试:select * from emp_view_complex;
1.2管理视图
创建视图后,还可以对视图进行管理,主要包括:查看视图定义信息、修改视图定义、重新编译视图和删除视图
1)查看视图定义
查询数据字典user_views查询视图定义
desc user_views;
test列定义了视图的定义信息
通过数据字典user_views查看视图user_views的定义 (没有快捷键时就用upper函数 好技巧)
set long 2000; select text from user_views where view_name=upper('emp_view_complex');
查看视图所有信息
set long 2000; set linesize 2000; col text for a100; col type_text for a100; col oid_text for a100; col oid_text_length for a100; select * from user_views where view_name=upper('insurnce_view');
上面实质上是查看视图的select语句,而视图定义语句是:
select dbms_metadata.get_ddl('VIEW','大写视图名') from dual; 比较慢
select dbms_metadata.get_ddl('VIEW','YGBX_CARD_VIEW') from dual;
查询当前用户下所有表和视图
select * from tab;
查询当前用户下所有视图
select * from tab where tabtype='VIEW';
2)修改视图定义
eg:修改视图emp_view_union,使该视图实现查询部门编号为30的功能
(原来视图有定义了,是查询信息是部门编号为20的记录。所谓修改定义其实就是or replace 直接覆盖罢了)create or replace view emp_view_complex as select d.dname,d.loc,e.empno,e.ename from emp e,dept d where e.deptno=d.deptno and d.deptno=30;
3)重新编译视图
修改了视图依赖的基本表后,视图会被标记为无效状态。但是当用户访问视图时,oracle会自动重新编译视图。除此之外,用户也可以使用alter view 语句手动编译视图以节省下次访问时间
eg:手动方式重新编译视图emp_view_complex
alter view emp_view_complex compile;
4)删除视图
drop view emp_view_complex;
执行drop语句后视图的定义将被删除,但是这对视图内的所有数据没有影响,它们仍然存储在基本表中
2.索引
2.1索引概述
前面说过,oracle数据库每一行都有一个rowid来标识,找到rowid就找到了某一行记录,当oracle数据库存储海量数据时,服务器就是通过索引对象在表中快速找到rowid
没有索引时想查询记录,必须先读取所有记录然后比对内容返回匹配的,有了索引就可以通过保存在索引中的rowid快速找到对应的记录了。若将表看做一本书,索引就相当于书的目录,通过目录(索引)快速找到页码(rowid)
将索引和对应的表分别放在不同的硬盘的不同的表空间中能够提高查询速度,因为oracle能够并行读取不同硬盘的数据,这样查询可以避免产生I/O冲突
按照数据存储方式分:B树索引、位图索引、反向键索引、基于函数的索引
按照索引列的唯一性分:唯一索引、非唯一索引
按照索引列的个数分:单列索引、复合索引
2.2创建索引
建立索引时,oracle首先将要建立索引的字段进行排序,然后将排序后的字段值和对应记录的rowid存储在索引段中。
格式: create index 索引名称 on 表名(列名);
1)建立B树索引
oracle最常用的索引类型(也是默认的)
在B树中,无论用户要搜索的是哪个分支的叶块,都可以保证所经过的索引层次是相同的。oracle采用这种方式的索引可以确保无论索引条目位于何处,都只需要花费相同的I/O即可获取它。(数据都集中在叶子结点,类似于很均匀的二叉查找树,查找很快)
eg:为emp表的deptno列创建索引 默认是B树索引
create index emp_deptno_index on emp(deptno) pctfree 25 tablespace users;
2)建立位图索引
“基数低”的列建立B树索引不合适,(“基数低”表示在索引列中,所有取值的数量比表中行的数量少,比如性别,无论多少行也只有两种取值)
oracle推荐当一个列的基数小于1%时,这些列不再适合建立B树索引,而适合利用位图索引。
eg:为emp表的sal列创建位图索引
create bitmap index emp_sal on emp(sal);
3)建立反向键索引
一张表的主键默认被建立为B树索引,但对于递增的主键来说B树索引会导致叶子结点偏向某一个方向(每次插入都是大头插,不管前面有没有删除)
反向键索引是一种特殊的B树索引,在顺序递增列上建立索引时非常有用。反向键索引的存储结构与常规B树索引相同,只是输入记录时先将列键值反向,然后在反向后的新数据上进行索引。(例如:用户输入的列键值为2011,则反向后的为1102,进行反向键索引却是非递增的,使得新数据在值的范围上分布比较均匀了)
eg:为表emp的job列创建反向键索引
create index emp_job_reverse on emp(job) reverse tablespace users;
将普通B树索引改为反向键索引(重建索引)
eg:emp表的deptno列的B树索引(emp_deptno_index)改为反向键索引
alter index emp_deptno_index rebuild reverse;
查看指定表的索引名和类型
select table_name,index_name,index_type from user_indexes where table_name=upper('emp');
4)基于函数的索引
select * from emp where job=upper('manager');,此时oracle可能会被迫进行全表搜索,为遇到的各个行进行upper函数然后匹配
为了解决此问题,oracle提供了基于函数的索引
由于sql语句中经常使用小写字符串,所以为了加快数据访问速度,应基于lower函数建立函数索引
eg:为emp的job列创建函数索引
create index emp_job_fun on emp(lower(job));
注:在自己模式中创建基于函数的索引需要具有query rewrite 系统权限。若要在其他用户模式下创建索引,必须具有create any index和global query rewrite权限
2.3修改索引
建立索引后,随着对表的不断插入,删除,索引中会产生很多碎片,及其影响效率,此时需要消除碎片,oracle提供两种方式:
1.重建索引
2.合并索引
合并索引
合并索引只是将B树中叶子节点的存储碎片合并在一起,并不会改变索引的物理组织结构
eg:在scott模式下,对索引emp_deptno_index执行合并操作
alter index emp_deptno_index coalesce deallocate unused;
重建索引
一个rebuild重建索引 也可以消除碎片
eg:对索引emp_deptno_index执行进行重建
alter index emp_deptno_index rebuild;
重建索引同时修改表空间
eg:对索引emp_deptno_index执行进行重建,并重新指定该索引对象的表空间
alter index emp_deptno_index rebuild tablespace example;
2.4删除索引
格式:drop index 索引名;
drop index emp_job_fun;
2.5显示索引信息
1)显示表的所有索引
查询数据字典视图dba_indexes,显示数据库的所有索引
查询数据字典all_indexes,显示当前用户可访问的所有索引
查询数据字典视图user_indexes,可以显示当前用户的索引信息
eg:system模式下,在数据字典dba_indexes中查询所有者是scott的全部索引对象
conn system/password@orcl as sysdba; select index_name,index_type from dba_indexes where owner='SCOTT'; --直接这样更方便 select index_name,index_type from user_indexes;
normal:普通B树索引
rev:反向键索引
bitmap:位图索引
function:基于函数的索引uniqueness:用于标识索引的唯一性
owner:用于标识对象所有者
2)显示索引列
eg:在scott模式下,查询scott用户的emp_deptno_columns(此索引名上节查到),索引的列信息
column column_name for a30; select column_name,column_length from user_ind_columns where index_name=upper('emp_deptno_index');
column_name: 该索引名对应的索引列名称 column_length:该索引名对应的索引的索引列长度
3)显示索引段位置及其大小
建立索引时,oracle会为索引分配相应的索引字段,索引数据被存放在索引段中,并且段名和索引名完全相同。
eg:在scott模式下,查询索引段emp_deptno_index的位置、段类型和段大小
select tablespace_name,segment_type,bytes from user_segments where segment_name=upper('emp_deptno_index');
4)显示函数索引
eg:在scott模式下,查询函数索引emp_job_fun的表达式信息
select column_expression from user_ind_expressions where index_name=upper('emp_job_fun');
3.序列
序列是oracle提供的用于生成一系列唯一数字的数据库对象。序列会自动生成顺序递增的序列号,以实现自动提供唯一的主键值。序列可以在多用户并发环境中使用,并且可以为所有用户生成不重复的顺序数字,而不需要提供任何额外的I/O开销。
3.1创建序列
序列和视图一样,并不占用实际的存储空间,只是在数据字典中保存它的定义信息。
创建序列:
create sequence <序列名>
[start with n]
[increment by n]
[minvalue n | nominvalue]
[maxvalue n | nomaxvalue]
[cache n | nocycle]
[cycle | nocycle]
[order | noorder];可选参数说明:
increment:表示序列的增量。正数递增序列,负数递减序列
minvalue:序列生成的最小值
maxvalue:序列生成的最大值
start:序列开始位置。默认递增序列起始值为minvalue,默认递减序列起始值maxvalue
cache:决定是否产生序列号预分配,并存储在内存中
cycle:当序列达到边界(最大最小值),可以复位继续下去。若用no cycle,越界后直接报错
order:保证生成的序列值是按顺序产生的。noorder只保证序列值的唯一性,不保证产生序列值的顺序
可选参数若不指定,都有默认值!
eg1:创建序列empno_seq
create sequence empno_seq maxvalue 99999 start with 1000 increment by 100 cache 50;
eg2:在scott模式下,使用序列empno_seq为emp表的新纪录提供员工编号
insert into emp(empno,ename,deptno) values(empno_seq.nextval,'东方',20);
eg3:查询序列当前值
select empno_seq.currval from dual;
3.2管理序列
alter sequence 语句可以对序列进行修改。除了序列的起始值start with不能修改外,其他可以设置序列的任何子句和参数都可以被修改。若要修改序列的起始值,则必须先删除序列,然后重建该序列。
修改序列
修改序列empno_seq的最大值为100000,序列增量为200,缓存值为100
alter sequence empno_seq maxvalue 100000 increment by 200 cache 100;
查看序列
desc user_sequences; select * from user_sequences;
删除序列
drop sequence empno_seq;
4.同义词
表、索引、视图等模式对象的一个别名
oracle中的同义词分两种,公有同义词和私有同义词
公有同义词所有用户可以使用,私有同义词只能被创建它的用户(或者被授权的其他用户)所拥有。
创建共有同义词必须拥有create public sysnonym系统权限
创建私有同义词必须拥有create sysnonym系统权限
公有同义词
格式:create public synonym 同义词名 for 表名|对象名|视图名;
eg:sys模式下,为dept表创建一个public同义词
create public synonym public_dept for scott.dept;
私有同义词
eg:为dept表创建私有同义词private_dept,代码及其运行结果如下
create synonym private_dept for dept;
私有同义词,其他用户使用必须带模式名
删除同义词
删除公有同义词
drop synonym private_dept;
删除私有同义词
drop public synonym public_dept;
补充导入导出表,查看建表语句
本次实验
-------------------------------------------------------------------------------------------------
-----------------------------------索引、视图、同义词及序列操作----------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-----------------------------------------1.创建索引----------------------------------------------
--(1)利用企业管理器为医院表的医院名称创建索引,并以降序排列,索引名为"hospital_name_index"
--方案->索引->中间右边->创建->填写名称、表名->添加表达式->排序->顺序(填数字,表示该列的排序顺序)
create index hospital_name_index on hospital(hname desc);
drop index hospital_name_index;
--(2)利用sqlplus为员工表创建索引"staff_info_index" 员工姓名升序,性别降序,出生年月日降序
create index staff_info_index on staff(sname asc,ssex desc,sbirthday desc);
-----------------------------------------2.查看索引---------------------------------------------------------
--(1)利用企业管理器查看"ygbx_user"方案下有几个隶属于该方案的索引,有几个系统创建的索引,有几个用户创建的索引
--方案->索引->填写方案(用户名)->(不填写对象名(即表名))直接点开始,能查看han用户的所有索引
-- 共13个索引
-- 系统创建:11个
-- 用户创建:2个
--(2)利用sqlplus从dba_indexes数据字典中查看员工医疗保险系统所有索引的信息
-- set linesize 3000;
-- --cmd缓存区宽度改为3000
-- select * from dba_indexes where owner='HAN';
-- select index_name,index_type,table_name from dba_indexes where owner='HAN';
--直接user_indexes下查就行了
set linesize 3000;
--cmd缓存区改为3000
select * from user_indexes;
select index_name,index_type,table_name from user_indexes;
--(3)利用sql*plus在dba_indexes数据字典中查看"staff_info_index"索引的信息,并查看该索引列的顺序关系
select * from dba_indexes where owner='HAN' and index_name=upper('staff_info_index');
select * from user_indexes where index_name=upper('staff_info_index');
--查看顺序及状态 OEM很方便
--查看状态
select status from user_indexes where index_name=upper('staff_info_index');
--查看列 用OEM很方便 sql*plus的话找到3个表或视图
/*user_ind_columns
all_ind_columns
dba_ind_columns*/
desc user_ind_columns;
select * from user_ind_columns where index_name=upper('staff_info_index');
-----------------------------------------3.删除索引---------------------------------------------------------
--(1)利用企业管理器删除"hospital_name_index"索引
--先查到hospital的所有索引,然后选中左边的radioButton, 然后点击删除
-- 图
--(2)利用sqlplus 删除"staff_info_index"索引
drop index staff_info_index;
-----------------------------------------4.创建视图---------------------------------------------------------
--(1)利用企业管理器为实现显示医保卡信息创建视图,该视图中包括医保卡信息、医保卡所属人信息和所属人单位信息,视图名为"ygbx_card_view"
--方案->视图->创建->填写名称->查询文本里输入select语句
create view ygbx_card_view as
select consume.*,card.ctype,card.cmoney,insurance.idate,insurance.imoney,insurance.bno
from card,consume,insurance
where card.cno=consume.cno and card.cno=insurance.cno;
--验证
desc ygbx_card_view;
--(2)利用sql*plus为实现员工持医保卡到医院消费的功能创建视图,该视图中包括员工信息、医保卡信息、
--医保信息和消费等信息,视图名为"consume_view"
create or replace view consume_view as
select staff.*,card.ctype,card.cmoney,insurance.idate,insurance.imoney,
consume.hno,consume.csdate,consume.mname,consume.mnum,consume.csmoney
from card,staff,insurance,consume
where card.cno=consume.cno and card.cno=insurance.cno and card.cno=staff.cno;
--(3)利用sql*plus为企业医保缴费信息功能创建视图,该视图中包括医保缴费的企业信息,医保卡信息和
--企业医保缴费费用等信息,视图名为"insurnce_view"
create or replace view insurnce_view as
select business.*,card.ctype,card.cmoney,consume.hno,
consume.csdate,consume.mname,consume.mnum,consume.csmoney
from business,staff,card,consume
where business.bno=staff.bno and consume.cno=card.cno and staff.cno=card.cno;
--(4)利用sql*plus为企业表创建视图,视图名为"business_view"
create view business_view as select * from business;
-----------------------------------------5.查看视图---------------------------------------------------------
--(1)利用企业管理器查看"HAN"方案下的视图
--方案-》视图-》方案填"HAN"->开始-》查到了刚刚建立的4个视图
--(2)利用企业管理器查看"consume_view"视图信息
--过程同上 对象名不能为空,而填:consume_view
--(3)利用sql*plus查看"ygbx_card_view"视图的子查询语句
--方法1 其实是查看建立视图的语句 不好
select dbms_metadata.get_ddl('VIEW','YGBX_CARD_VIEW') from dual;
--方法2
set long 2000;
select text from user_views where view_name=upper('ygbx_card_view');
--(4)利用sql*plus显示"insurnce_view"视图信息
set long 2000;
set linesize 2000;
col text for a100;
col type_text for a100;
col oid_text for a100;
col oid_text_length for a100;
select * from user_views where view_name=upper('insurnce_view');
-----------------------------------------6.视图数据的更新---------------------------------------------------------
--(1)简单视图可以直接插入数据:利用sqlplus向"business_view"插入一个记录,企业编号为"B1997010287",企业名称为"格林制药",
--企业类型为"企业",企业地址为"鸭绿江街98号",联系号码为"84692315"
insert into business_view values('B19970102','格林制药','企业','鸭绿江街98号','84692315');
-- (2)利用sqlplus查看business表是否有变化
select * from business;
--有变化,多了一条数据
-----------------------------------------7.删除视图-----------------------------------------------------
--(1)利用企业管理器删除"business_view"视图
--先查到视图(方案:HAN,对象名:business_view)->左侧选中—>点击删除->是
--图
--(2)利用企业管理器删除"ygbx_card_view"
--操作同上
--图
--查询当前用户下所有视图
select * from tab where tabtype=upper('view');
--(3)利用sqlplus删除"consume_view"视图
drop view consume_view;
--(4)利用sqlplus删除"insurnce_view"
drop view insurnce_view;
-----------------------------------------8.创建同义词-----------------------------------------------------
--(1)利用企业管理器创建企业表同义词,名为"qyb"
--方案-》同义词->创建-》名称:qyb->类型:共用-》对象 han.business
--(2)利用sqlplus创建医保卡表的同义词,名为"ybk"
create public synonym ybk for card;
desc ybk;
-----------------------------------------9.查询同义词-----------------------------------------------------
--(1)利用企业管理器查询同义词qyb
--方案->同义词->方案填:han 对象名填:qyb ->开始
--(2)利用sqlplus查看同义词"ybk"
desc ybk;
select * from ybk;
-----------------------------------------10.删除同义词-----------------------------------------------------
--(1)利用企业管理器删除同义词"qyb"
--查到后选中左侧的radiobutton-》点上方的删除
--(2)利用sqlplus删除同义词
grant drop public synonym to han;
drop public synonym ybk;
-----------------------------------------11.创建序列-----------------------------------------------------
--(1)利用企业管理器创建序列,改序列最大值为"28000",最小值为"60",步长为"1",可循环,序列名为"ygbk_seq1"
--注意 一次nextval都没执行过时,不可以直接执行currval,会报错
select ygbx_seq1.nextval from dual;
select ygbx_seq1.currval from dual;
--(2)利用sqlplus创建序列 改序列最大值无限,最小值1,步长10,序列名"ygbx_seq2"
create sequence ygbx_seq2
minvalue 1
increment by 10;
-----------------------------------------12.查询序列-----------------------------------------------------
--(1) 利用企业管理器查看序列"ygbx_seq1"
--方案:han 对象名:ygbx_seq1
--(2) 利用sqlplus查看序列"ygbx_seq2"
select * from user_sequences where sequence_name=upper('ygbx_seq2');
select ygbx_seq2.nextval from dual;
select ygbx_seq2.currval from dual;
-----------------------------------------13.修改序列-----------------------------------------------------
--(1)利用企业管理器修改序列"ygbx_seq1",将该序列最大值设置为"82000",最小值设为"100",步长设为"5"
--查到后点击编辑即可
-- 最小值不能大于当前值,于是多执行几次(可以用plsql块的循环写)
--(2)利用sqlplus修改序列"ygbx_seq2",将序列最大值设为"1000"
alter sequence ygbx_seq2
maxvalue 1000;
-----------------------------------------14.删除序列-----------------------------------------------------
--(1)利用企业管理器删除序列"ygbx_seq1"
--查找到,选中左边radiobutton,然后直接点删除即可
--(2)利用sqlplus删除序列"ygbx_seq2"
drop sequence ygbx_seq2;