数据库对象视图、序列、索引、同义词VIP

2020/6/1,.,1,第六节数据库对象视图、序列、索引、同义词,通过本节学习，你需要掌握：视图(view)序列(sequence)索引(index)同义词(synonym)最后修改时间：2011.4.16,2020/6/1,.,2,数据库对象,2020/6/1,.,3,视图(View),可以基于表格或者视图中的字段或者字段的运算表达式建立视图。视图是一种逻辑结构，自身不存储任何记录，但可以反映数据源表格（称为基表）的值，并可以进行修改操作。视图在数据字典中被当作一个SELECT语句存储。,2020/6/1,.,4,2020/6/1,.,5,使用视图的原因？,数据保护，可以有选择地挑选需要的字段使查询语句简单化为应用程序提供独立数据为相同的数据提供不同的视角,2020/6/1,.,6,简单视图和复杂视图,2020/6/1,.,7,创建视图(1),CREATEORREPLACEFORCE|NOFORCEVIEWview(alias,alias)ASsubqueryWITHCHECKOPTIONWITHREADONLY;ORREPLACE如果视图存在则更新FORCE即使数据源表格不存在也创建视图NOFORCE只有当源表格存在时才能创建，缺省值。view视图的名称alias视图中字段的名称，必须和子查询中的数量相同subquery一个完整的SELECT语句，可以对字段重命名WITHCHECKOPTION定义视图中的某些字段不可修改WITHREADONLY规定视图中不允许DML操作,2020/6/1,.,8,创建视图(2),创建视图EMPVU10，包含10部门所有员工的资料。CREATEVIEWempvu10ASSELECTempno,ename,jobFROMempWHEREdeptno=10;使用DESCRIBE指令查询视图的结构DESCempvu10NameNull?Type-EMPNONOTNULLNUMBER(4)ENAMEVARCHAR2(10)JOBVARCHAR2(9),2020/6/1,.,9,创建视图(3),创建视图的查询语句中可以使用复杂的SELECT语句，包括连接(join)、分组(groups)、子查询(subqueries)、排序(orderby)使用ORREPLACE选项可以在更新视图定义时，避免删除并重建的麻烦。,2020/6/1,.,10,创建视图(4),创建视图salvu30，包含30部门所有员工的员工号、姓名、工资，字段分别重命名为EMPLOYEE_NUMBER、NAME、SALARY。CREATEVIEWsalvu30ASSELECTempnoEMPLOYEE_NUMBER,enameNAME,salSALARYFROMempWHEREdeptno=30;CREATEVIEWsalvu30(EMPLOYEE_NUMBER,NAME,SALARY)ASSELECTempno,ename,salFROMempWHEREdeptno=30;,两种等价的写法,2020/6/1,.,11,从视图中查询数据(1),SELECT*FROMsalvu30;查询视图的语句与查询表格的语句相同,2020/6/1,.,12,从视图中查询数据(2),SQL*PLUSSELECT*FROMempvu10;7839KINGPRESIDENT7782CLARKMANAGER7934MILLERCLERK,EMP,USER_VIEWSEMPVU10SELECTempno,ename,jobFROMempWHEREdeptno=10;,2020/6/1,.,13,从视图中查询数据(3),当使用视图查询数据的时候，Oracle服务器执行以下操作：从USER_VIEWS中得到视图的定义验证用户是否有操作源表格的权限把针对视图的操作语句转换成针对基表的语句，从基表中得到数据或者更新数据。,2020/6/1,.,14,修改视图,使用ORREPLACE选项CREATEORREPLACEVIEWempvu10(employee_number,employee_name,job_title)ASSELECTempno,ename,jobFROMempWHEREdeptno=10;定义字段别名的时候，顺序和数量必须和查询语句中的相同。,2020/6/1,.,15,创建复杂视图,创建视图dept_sum_vu，包含部门名称、部门的最低、最高、平均工资。CREATEVIEWdept_sum_vu(name,minsal,maxsal,avgsal)ASSELECTd.dname,MIN(e.sal),MAX(e.sal),AVG(e.sal)FROMempe,deptdWHEREe.deptno=d.deptnoGROUPBYd.dname;,2020/6/1,.,16,当使用函数和表达式建立视图的时候，为每个字段建立别名是必要的。可以使用DESC语句查看视图结构，或者SELECT语句查看视图数据。SELECT*FROMdept_sum_vu,2020/6/1,.,17,在视图上执行DML操作的限制,可以在所有简单视图上执行DML操作。如果视图包含组函数（如SUM、AVG、MIN、MAX）、GROUPBY子句、DISTINCT，则不能删除记录。如果视图包含组函数、GROUPBY子句、DISTINCT、由表达式定义的字段（如salary*12）、ROWNUM伪字段，则不能修改记录。如果视图包含组函数、GROUPBY子句、DISTINCT、由表达式定义的字段（如salary*12）、ROWNUM伪字段、基表中存在某些NOTNULL约束的字段没有在视图中出现，则不能添加数据。,2020/6/1,.,18,WITHCHECKOPTION子句,定义视图中的某些字段如果修改后无法被SELECT语句选中，则修改操作将会失败。CREATEORREPLACEVIEWempvu20ASSELECT*FROMempWHEREdeptno=20WITHCHECKOPTION;由于WITHCHECKOPTION约束条件，任何企图改变视图中deptno字段取值的操作都会失败。,2020/6/1,.,19,DenyingDMLOperations,使用WITHREADONLY选项时，不允许对视图执行任何DML操作CREATEORREPLACEVIEWempvu10(employee_number,employee_name,job_title)ASSELECTempno,ename,jobFROMempWHEREdeptno=10WITHREADONLY;,2020/6/1,.,20,删除视图,DROPVIEWview;删除视图不会丢失任何数据，因为视图是逻辑上的概念，在数据库中是以SELECT语句形式存在的，自身不含任何数据。删除视图后，基于这个视图的数据库对象会失效(invalid)。只有视图的创建者或者有DROPANYVIEW权限的用户才能删除视图。,2020/6/1,.,21,内置视图(InlineViews),内置视图是由FROM子句中的子查询语句产生的。SELECTa.ename,a.sal,a.deptno,b.maxsalFROMempa,(SELECTdeptno,max(sal)maxsalFROMempGROUPBYdeptno)bWHEREa.deptno=b.deptnoANDa.salb.maxsal;,.,有限排序分析(1),当需要提取有限多个最好的或者最差的记录时，需要用到有限排序分析。语法：SELECTcolumn_list,ROWNUMFROM(SELECTcolumn_listFROMtableORDERBYTop-N_column)WHEREROWNUM=N;,ORDERBY子句确保内置视图中的记录按需要的顺序排列，从大到小时需要用DESC,ROWNUM伪字段对表格的第一行记录取值为1，第n行记录取值为n,比较符只能使用或=,2020/6/1,.,23,有限排序分析(2),输出EMP表中工资最高的3位员工的工资排位、姓名和工资。SELECTROWNUMasRANK,ename,salFROM(SELECTename,salFROMempORDERBYsalDESC)WHEREROWNUM=3;,2020/6/1,.,24,数据库对象,2020/6/1,.,25,序列(sequence),自动产生唯一的数字是一个可多个数据库用户共享的对象主要用于创建主键值由Oracle内部控制生成序列序列值的保存和生成与表格无关，因而相同的序列可以应用在多张表格中,2020/6/1,.,26,创建序列(1),CREATESEQUENCEsequenceINCREMENTBYnSTARTWITHnMAXVALUEn|NOMAXVALUEMINVALUEn|NOMINVALUECYCLE|NOCYCLECACHEn|NOCACHE;,2020/6/1,.,27,创建序列(1),sequence：序列的名称INCREMENTBYn：定义序列生成的数字的间隔为n。缺省情况下为1。STARTWITHn：定义序列生成的第一个数字是n。缺省情况下为1。MAXVALUEn：定义序列能生成的最大数字是n。NOMAXVALUE：定义对于升序序列，最大数字是1027，对于降序序列，最大数字是-1。这是缺省选项。MINVALUEn：定义序列能生成的最小数字是n。NOMINVALUE：定义对于升序序列，最小数字是1，对于降序序列，最小数字是-1026。这是缺省选项。CYCLE|NOCYCLE：定义当序列达到最大值或者最小值时是否继续生成数字(NOCYCLE是缺省选项)。CACHEn|NOCACHE：定义Oracle服务器预分配的序列数。缺省值为20。,2020/6/1,.,28,创建序列,创建序列DEPT_DEPTID_SEQ，用于生成DEPT表的主键，使用NOCYCLE选项。CREATESEQUENCEdept_deptid_seqINCREMENTBY10STARTWITH120MAXVALUE9999NOCACHENOCYCLE;Sequencecreated.,2020/6/1,.,29,使用序列的注意事项,不要使用CYCLE模式来生成主键值，除非有可靠的机制保证在生成下一轮数值时，旧的记录会被删除。序列独立存在，并非绑定在表格上。,2020/6/1,.,30,查询序列,使用数据字典USER_SEQUENCES查询序列SELECTsequence_name,min_value,max_value,increment_by,last_numberFROMuser_sequences;,NOCACHE模式下，last_number返回序列下一个可以使用的数值,2020/6/1,.,31,NEXTVAL和CURRVAL伪字段,在创建序列后，就可以使用序列生成唯一值插入表格中的相关字段。序列中可以使用NEXTVAL和CURRVAL两个伪字段，调用方法为：sequence.NEXTVAL和sequence.CURRVAL。NEXTVAL返回下一个可以使用的序列数，即使多个用户调用序列的NEXTVAL值，也可以保证每个用户得到的值是互不相同的。CURRVAL获取当前的序列值。对于一个序列而言，在没有调用NEXTVAL之前，CURRVAL是没有值的。,2020/6/1,.,32,NEXTVAL和CURRVAL的使用规则,可以在以下情况中使用：SELECT语句中的SELECT列表，但不能是在子查询中INSERT语句子查询中的SELECT列表INSERT语句中的VALUE子句UPDATE语句中的SET子句不可以在以下情况中使用：视图中的SELECT列表包含DISTINCT关键字的SELECT语句包含GROUPBY、ORDERBY、HAVING子句的SELECT语句SELECT、DELETE、UPDATE语句中的子查询使用DEFAULT表达式的CREATETABLE和ALTERTABLE语句,2020/6/1,.,33,使用序列,DEPT表中新增一个部门DEVELOPMENT，位于DETROITINSERTINTOdept(deptno,dname,loc)VALUES(dept_deptid_seq.NEXTVAL,DEVELOPMENT,DETROIT);观察当前的序列值SELECTdept_deptid_seq.CURRVALFROMdual;CURRVAL-120,2020/6/1,.,34,如果需要向上一条语句中新增的部门中添加一个新员工，可以考虑使用以下方法：INSERTINTOemp(empno,deptno,.)VALUES(employees_seq.NEXTVAL,dept_deptid_seq.CURRVAL,.);,2020/6/1,.,35,使用序列的注意事项,序列使用CACHE选项会加快其响应速度。序列生成的值可能会在以下情况时发生间断：ROLLBACK系统崩溃序列同时被其他表格使用如果序列使用NOCACHE选项，可以通过对USER_SEQUENCES表的访问，查看序列下一个生成的值,2020/6/1,.,36,修改序列,ALTERSEQUENCEdept_deptid_seqINCREMENTBY20MAXVALUE999999NOCACHENOCYCLE;Sequencealtered.,2020/6/1,.,37,修改序列的语法格式,ALTERSEQUENCEsequenceINCREMENTBYnMAXVALUEn|NOMAXVALUEMINVALUEn|NOMINVALUECYCLE|NOCYCLECACHEn|NOCACHE;STARTWITH选项不能修改,2020/6/1,.,38,修改序列的注意事项(1),必须是序列的所有者，或者具有修改序列的系统权限。只影响修改后生成的序列值，对之前已经生成的值没有影响。ALTERSEQUENCE语句不能改变STARTWITH选项。如果需要重新从初始值开始使用序列，需要删除并重建序列。,2020/6/1,.,39,修改序列的注意事项(2),ALTERSEQUENCE语句必须有效ALTERSEQUENCEdept_deptid_seqINCREMENTBY20MAXVALUE90NOCACHENOCYCLE;ALTERSEQUENCEdept_deptid_seq*ERRORatline1:ORA-04009:MAXVALUEcannotbemadetobelessthanthecurrentvalue,2020/6/1,.,40,删除序列,语法：DROPSEQUENCEsequence;使用DROPSEQUENCE语句从数据字典中删除序列。必须是序列的拥有者，或者具有删除序列的系统权限。DROPSEQUENCEdept_deptid_seq;Sequencedropped.,2020/6/1,.,41,数据库对象,2020/6/1,.,42,索引,什么是索引？索引是一个图表对象。索引可以显式建立，或者由服务器自动建立。索引加速ORACLE服务器定位数据，有效地降低I/O访问量。如果不使用索引，有可能需要全表扫描。一旦建立索引，索引的使用和维护都由服务器负责，用户不必考虑。索引与被索引的表格是相对独立的，因此增加、删除、修改索引不会影响表格数据和其他索引。当表格被删除时，相关索引自动被删除。,2020/6/1,.,43,索引的两种类型,自动建立：当创建表格时，如果有定义字段为PRIMARYKEY或者UNIQUE，相关的唯一索引会被自动创建。手工建立：用户可以创建非唯一的索引以加速数据定位。用户也可以创建唯一索引，不过建议采用定义字段为UNIQUE的方法。,2020/6/1,.,44,创建索引,语法：CREATEINDEXindexONtable(column,column.);加速对EMP表ename字段的查询CREATEINDEXemp_ename_idxONemp(ename);Indexcreated.,2020/6/1,.,45,索引的创建原则,MoreIsNotAlwaysBetter以下情况中应当建立索引字段的取值分布范围很广字段的取值包含了很多NULL值一个或多个字段经常被WHERE子句使用到表数据很大，但大多数查询语句只需要其中2%-4%左右的数据。注意如果需要定义唯一索引，应当在表格创建时就加上UNIQUE限制。,2020/6/1,.,46,不适宜创建索引的情况,小型表格字段不是经常在查询语句中作为判定条件出现大多数的查询语句返回的记录数超过表格总记录数的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;,2020/6/1,.,48,基于函数的索引,CREATEINDEXidx_upper_dept_nameONdept(UPPER(dname);Indexcreated.SELECT*FROMdeptWHEREUPPER(dname)=SALES;,2020/6/1,.,49,NULL值对于函数索引的影响,SELECT*FROMdeptORDERBYUPPER(dname);假设dname字段可能存在空值，那么以上语句很可能在执行的时候用到了全表扫描，即使UPPER(dname)已经被定义为索引。为了避免这种情况，使用如下语句：SELECT*FROMdeptWHEREUPPER(dname)ISNOTNULLORDERBYUPPER(dname);,2020/6/1,.,50,删除索引,语法：DROPINDEXindex;删除DEPT