第五章 数据库中的其他对象

第五章：数据库中的对象对象描述表基本的数据存储集合，由行和列组成视图从表中抽出的逻辑上相关的数据集合序列提供有规律的数值索引提高查询的效率同义词给对象起别名数据库中常见对象在数据库中最重要和最基本的对象就是“表”，之前所学习的所有操作：数据的增加、删除、修改、查询都是操作表进行实现的。回顾：表什么是视图？基于一张表或多张表或另外一个视图的逻辑表。基表（basetable)：

独立存在的实体，用于存储数据的基本结构。视图只是一种定义，对应一个查询语句。视图的数据来自基表。通过视图来查看表，就像从不同角度观察一个或多个表。视图一经定义便存储在数据库中，对视图的操作与对表的操作一样，可以对其进行查询、修改（有一定限制）和删除。优点：简化查询。经常使用的查询可以定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。控制数据访问。通过视图用户只能查询和修改他们所能见到的数据。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上。数据独立性。视图可帮助用户屏蔽真实表结构变化带来的影响。为什么使用视图特性简单视图复杂视图表的数量一个一个或多个函数没有有分组没有有DML操作可以有时可以简单视图和复杂视图创建视图在CREATEVIEW语句中嵌入子查询CREATE[ORREPLACE][FORCE|NOFORCE]VIEW[Schema.]view_name[(column_name…)]Asselect_statement[WITHCHECKOPTION][CONSTRAINT][WITHREADONLY][CONSTRAINT]子查询可以使复杂的SELECT语句创建视图举例CREATEVIEWemp10Asselectempno,ename,salFromempWheredeptno=10;描述视图结构DESCemp10创建视图创建视图时在子查询中给列定义别名CREATEVIEWemp20Asselectempnoempid,enamename,sal*12ann_salaryFromempWheredeptno=20;在选择视图中的列时应使用别名查询视图select*Fromemp20；查询视图select*Fromemp10；SQL*PLUSOracleServerUSER_VIEWSEmp10selectempno,ename,salFromempWheredeptno=10;emp修改视图使用CREATEORREPLACEVIEW子句修改视图CREATEORREPLACEVIEWemp10(empno,ename,sal,deptno)Asselectempno,ename||’salaryis‘||salary,deptnoFromempWheredeptno=10;CREATEVIEW子句中各列的别名应和子查询中各列相对应创建复杂视图复杂视图举例CREATEVIEWdept_sum_avg(ename,minsal,maxsal,avgsal)Asselectd.dname,MIN(e.sal),MAX(e.sal),AVG(e.sal)Fromempe,deptdWheree.deptno=d.deptnoGroupbyd.dname;创建复杂视图查询刚创建的视图视图中使用DML的规定可以在简单视图中执行DML操作当视图定义中包含以下元素之一时不能使用delete：组函数GROUPBY子句DISTINCT关键字ROWNUM伪列视图中使用DML的规定当视图定义中包含以下元素之一时不能使用update：组函数GROUPBY子句DISTINCT关键字ROWNUM伪列列的定义为表达式视图中使用DML的规定可以在简单视图中执行DML操作当视图定义中包含以下元素之一时不能使用insert：组函数GROUPBY子句DISTINCT关键字ROWNUM伪列列的定义为表达式表中非空的列在视图定义中未包括WITHCHECKOPTION子句CREATEORREPLACEVIEWempvu20ASSELECT*FROMempWHEREdeptno=20WITHCHECKOPTIONCONSTRAINTemp_ck;使用WITHCHECKOPTION子句确保DML只能在特定的范围内执行任何违反WITHCHECKOPTION约束的请求都会失败屏蔽DML操作任何DML操作都会返回一个ORACLESERVER错误可使用WITHREADONLY选项屏蔽对视图的DML操作CREATEORREPLACEVIEWempvu10

（emp_number,emp_name,job_title)ASSELECTempno,ename,jobFROMempWHEREdeptno=10WITHREADONLY;删除视图删除视图只是删除视图的定义，并不会删除基表的数据DROPVIEWview_name；DROPVIEWemp20；临时视图临时视图可以是嵌套在SQL语句中的子查询在FROM子句中的子查询是临时视图临时视图不是数据库对象Top-N分析Top-N分析查询一个列中最大或最小的n个值：销售量最高的十种产品是什么？销售量最差的十种产品是什么？最大和最小的值的集合是Top-N分析所关心的查询最大的几个值的Top-N分析SELECT[column_name],ROWNUMFROM(select[column_name]fromtable_nameorderbytop-n_column)WHEREROWNUM<=N;Top-N分析查询工资最高的三名员工：SELECTROWNUMasRANK,ename,salFROM(selectename,salfromemporderbysaldesc)WHEREROWNUM<=3;总结通过本章学习，您已了解视图的优点和基本应用：控制数据访问简化查询数据独立性删除时不删除数据子查询是临时视图的一种Top-N分析其他数据库对象通过本章学习，您将可以：创建，维护和使用序列创建和维护索引创建私有和公有同义词目标什么是序列序列号生成器，可以为表中的行自动生成序列号自动提供唯一的数值主要用于提供主键值代替应用代码将序列值装入内存可以提高访问效率目标CREATESEQUENCE语句CREATESEQUENCEsequence_name[INCREMENTByn]//定义序列步长，默认为1[STARTWITHn]]//定义序列初始值，默认为1[{MAXVALUEn|NOMAXVALUE}]]//定义序列生成器能产生的最大值，递增序列最大值为10的27次方[{MINVALUEn|NOMINVALUE}]//递减序列最小值为1[{CYCLE|NOCYCLE}]//序列达到限制后是否循环[{CACHEn|NOCACHE}];//定义存放序列的内存块大小，默认为20定义序列创建序列CREATESEQUENCEdept_deptid_seqINCREMENTBy10STARTWITH40MAXVALUE99NOCYCLENOCACHE;创建序列dept_deptid_seq为表dept提供主键不使用CYCLE选项查询序列SELECTsequence_name,min_value,max_value,increment_by,last_numberFROMuser_sequences;查询数据字典视图USER_SEQUENCES获取序列定义信息，不使用CYCLE选项如果指定NOCACHE选项，则列last_number显示序列中下一个有效的值NEXTVAL和CURRVAL伪列NEXTVAL返回序列中下一个有效的值，任何用户都可以引用CURRVAL中存放序列的当前值NEXTVAL应在CURRVAL之前指定，二者应同时有效序列应用举例INSERTINTOdept(deptno,dname,loc)VALUES(dept_deptid_seq.NEXTVAL,'support','beijing');SELECTdept_deptid_seq.CURRVALFROMdual;序列dept_deptid_seq的当前值使用序列将序列值装入内存可提高访问效率序列在下列情况下出现裂缝：回滚系统异常多个表同时使用同一序列如果不将序列的值装入内存（nocache），可使用表USER_SEQUENCES查看序列当前的有效值修改序列修改序列的增量，最大值，最小值，循环选项，或是否装入内存ALTERSEQUENCEdept_deptid_seqINCREMENTBY20MAXVALUE999//仅示例，超出范围NOCACHENOCYCLE;修改序列的注意事项必须是序列的拥有者或对序列有ALTER权限只有将来的序列值会被改变改变序列的初始值只能通过删除序列之后重建序列的方法实现删除序列使用DROPSEQUENCE语句删除序列删除之后，序列不能再次被引用DROPSEQUENCEdept_deptid_seq;索引一种数据库对象通过指针加速Oracle服务器的查询速度通过快速定位数据的方法，减少磁盘I/O索引与表互相独立Oracle服务器自动使用和维护索引创建索引自动创建：

在定义PRIMARYKEY或UNIQUE约束后系统自动在相应的列上创建唯一性索引手动创建：

用户可以在其他列上创建非唯一的索引，以加速查询创建索引在表EMP的列ENAME上创建索引

CREATEINDEXemp_ename_idxONemp(ename);什么时候创建索引以下情况可以创建索引

列中数据值分布范围很广列中包含大量空值列经常在WHERE子句或连接条件中出现表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%什么时候不要创建索引以下情况不要创建索引

表很小列不经常作为连接条件或出现在WHERE子句中查询的数据大于2%到4%表经常更新加索引的列包含在表达式中查询索引可以使用数据字典视图USER_INDEXES和USER_IND_COLUMNS查看索引的信息SELECTic.index_name,ic.column_name,ic.column_positioncol_pos,ix.uniquenessFROMuser_indexesix,user_ind_columnsicWHEREic.index_name=ix.index_nameANDic.table_name='emp';基于函数的索引基于函数的索引是一个基于表达式的索引索引表达式由列，常量，SQL函数和用户

自定的函数CREATEINDEXupper_dept_name_idxONdept(upper(dname));SELECT*FROMdeptWHEREupper(dname)='SALES';删除索引使用DROPINDEX命令删除索引

DROPINDEXindex_name;DROPINDEXUPPER_ENAME_IDX;删除索引UPPER_ENAME_IDX只有索引的拥有者或拥有DROPANYINDEX权限的用户才可以删除索引

同义词使