6、数据库对象-视图、序列、索引、同义词ppt课件.ppt

第六节数据库对象视图 序列 索引 同义词 通过本节学习 你需要掌握 视图 view 序列 sequence 索引 index 同义词 synonym 最后修改时间 2011 4 16 2020年4月7日1时3分 1 厦门大学计算机系工程硕士教材 数据库对象 2020年4月7日1时3分 2 厦门大学计算机系工程硕士教材 视图 View 可以基于表格或者视图中的字段或者字段的运算表达式建立视图 视图是一种逻辑结构 自身不存储任何记录 但可以反映数据源表格 称为基表 的值 并可以进行修改操作 视图在数据字典中被当作一个SELECT语句存储 2020年4月7日1时3分 3 厦门大学计算机系工程硕士教材 2020年4月7日1时3分 4 厦门大学计算机系工程硕士教材 使用视图的原因 数据保护 可以有选择地挑选需要的字段使查询语句简单化为应用程序提供独立数据为相同的数据提供不同的视角 2020年4月7日1时3分 5 厦门大学计算机系工程硕士教材 简单视图和复杂视图 2020年4月7日1时3分 6 厦门大学计算机系工程硕士教材 创建视图 1 CREATE ORREPLACE FORCE NOFORCE VIEWview alias alias ASsubquery WITHCHECKOPTION WITHREADONLY ORREPLACE如果视图存在则更新FORCE即使数据源表格不存在也创建视图NOFORCE只有当源表格存在时才能创建 缺省值 view视图的名称alias视图中字段的名称 必须和子查询中的数量相同subquery一个完整的SELECT语句 可以对字段重命名WITHCHECKOPTION定义视图中的某些字段不可修改WITHREADONLY规定视图中不允许DML操作 2020年4月7日1时3分 7 厦门大学计算机系工程硕士教材 创建视图 2 创建视图EMPVU10 包含10部门所有员工的资料 CREATEVIEWempvu10ASSELECTempno ename jobFROMempWHEREdeptno 10 使用DESCRIBE指令查询视图的结构DESCempvu10NameNull Type EMPNONOTNULLNUMBER 4 ENAMEVARCHAR2 10 JOBVARCHAR2 9 2020年4月7日1时3分 厦门大学计算机系工程硕士教材 8 创建视图 3 创建视图的查询语句中可以使用复杂的SELECT语句 包括连接 join 分组 groups 子查询 subqueries 排序 orderby 使用ORREPLACE选项可以在更新视图定义时 避免删除并重建的麻烦 2020年4月7日1时3分 厦门大学计算机系工程硕士教材 9 创建视图 4 创建视图salvu30 包含30部门所有员工的员工号 姓名 工资 字段分别重命名为EMPLOYEE NUMBER NAME SALARY CREATEVIEWsalvu30ASSELECTempnoEMPLOYEE NUMBER enameNAME salSALARYFROMempWHEREdeptno 30 CREATEVIEWsalvu30 EMPLOYEE NUMBER NAME SALARY ASSELECTempno ename salFROMempWHEREdeptno 30 两种等价的写法 2020年4月7日1时3分 10 厦门大学计算机系工程硕士教材 从视图中查询数据 1 SELECT FROMsalvu30 查询视图的语句与查询表格的语句相同 2020年4月7日1时3分 11 厦门大学计算机系工程硕士教材 从视图中查询数据 2 SQL PLUSSELECT FROMempvu10 7839KINGPRESIDENT7782CLARKMANAGER7934MILLERCLERK EMP USER VIEWSEMPVU10SELECTempno ename jobFROMempWHEREdeptno 10 2020年4月7日1时3分 12 厦门大学计算机系工程硕士教材 从视图中查询数据 3 当使用视图查询数据的时候 Oracle服务器执行以下操作 从USER VIEWS中得到视图的定义验证用户是否有操作源表格的权限把针对视图的操作语句转换成针对基表的语句 从基表中得到数据或者更新数据 2020年4月7日1时3分 厦门大学计算机系工程硕士教材 13 修改视图 使用ORREPLACE选项CREATEORREPLACEVIEWempvu10 employee number employee name job title ASSELECTempno ename jobFROMempWHEREdeptno 10 定义字段别名的时候 顺序和数量必须和查询语句中的相同 2020年4月7日1时3分 14 厦门大学计算机系工程硕士教材 创建复杂视图 创建视图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年4月7日1时3分 15 厦门大学计算机系工程硕士教材 当使用函数和表达式建立视图的时候 为每个字段建立别名是必要的 可以使用DESC语句查看视图结构 或者SELECT语句查看视图数据 SELECT FROMdept sum vu 2020年4月7日1时3分 16 厦门大学计算机系工程硕士教材 在视图上执行DML操作的限制 可以在所有简单视图上执行DML操作 如果视图包含组函数 如SUM AVG MIN MAX GROUPBY子句 DISTINCT 则不能删除记录 如果视图包含组函数 GROUPBY子句 DISTINCT 由表达式定义的字段 如salary 12 ROWNUM伪字段 则不能修改记录 如果视图包含组函数 GROUPBY子句 DISTINCT 由表达式定义的字段 如salary 12 ROWNUM伪字段 基表中存在某些NOTNULL约束的字段没有在视图中出现 则不能添加数据 2020年4月7日1时3分 17 厦门大学计算机系工程硕士教材 WITHCHECKOPTION子句 定义视图中的某些字段如果修改后无法被SELECT语句选中 则修改操作将会失败 CREATEORREPLACEVIEWempvu20ASSELECT FROMempWHEREdeptno 20WITHCHECKOPTION 由于WITHCHECKOPTION约束条件 任何企图改变视图中deptno字段取值的操作都会失败 2020年4月7日1时3分 18 厦门大学计算机系工程硕士教材 DenyingDMLOperations 使用WITHREADONLY选项时 不允许对视图执行任何DML操作CREATEORREPLACEVIEWempvu10 employee number employee name job title ASSELECTempno ename jobFROMempWHEREdeptno 10WITHREADONLY 2020年4月7日1时3分 19 厦门大学计算机系工程硕士教材 删除视图 DROPVIEWview 删除视图不会丢失任何数据 因为视图是逻辑上的概念 在数据库中是以SELECT语句形式存在的 自身不含任何数据 删除视图后 基于这个视图的数据库对象会失效 invalid 只有视图的创建者或者有DROPANYVIEW权限的用户才能删除视图 2020年4月7日1时3分 20 厦门大学计算机系工程硕士教材 内置视图 InlineViews 内置视图是由FROM子句中的子查询语句产生的 SELECTa ename a sal a deptno b maxsalFROMempa SELECTdeptno max sal maxsalFROMempGROUPBYdeptno bWHEREa deptno b deptnoANDa sal b maxsal 2020年4月7日1时3分 21 厦门大学计算机系工程硕士教材 有限排序分析 1 当需要提取有限多个最好的或者最差的记录时 需要用到有限排序分析 语法 SELECT column list ROWNUMFROM SELECT column list FROMtableORDERBYTop N column WHEREROWNUM N ORDERBY子句确保内置视图中的记录按需要的顺序排列 从大到小时需要用DESC ROWNUM伪字段对表格的第一行记录取值为1 第n行记录取值为n 比较符只能使用 或 厦门大学计算机系工程硕士教材 22 有限排序分析 2 输出EMP表中工资最高的3位员工的工资排位 姓名和工资 SELECTROWNUMasRANK ename salFROM SELECTename salFROMempORDERBYsalDESC WHEREROWNUM 3 2020年4月7日1时3分 23 厦门大学计算机系工程硕士教材 数据库对象 2020年4月7日1时3分 24 厦门大学计算机系工程硕士教材 序列 sequence 自动产生唯一的数字是一个可多个数据库用户共享的对象主要用于创建主键值由Oracle内部控制生成序列序列值的保存和生成与表格无关 因而相同的序列可以应用在多张表格中 2020年4月7日1时3分 25 厦门大学计算机系工程硕士教材 创建序列 1 CREATESEQUENCEsequence INCREMENTBYn STARTWITHn MAXVALUEn NOMAXVALUE MINVALUEn NOMINVALUE CYCLE NOCYCLE CACHEn NOCACHE 2020年4月7日1时3分 26 厦门大学计算机系工程硕士教材 创建序列 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年4月7日1时3分 厦门大学计算机系工程硕士教材 27 创建序列 创建序列DEPT DEPTID SEQ 用于生成DEPT表的主键 使用NOCYCLE选项 CREATESEQUENCEdept deptid seqINCREMENTBY10STARTWITH120MAXVALUE9999NOCACHENOCYCLE Sequencecreated 2020年4月7日1时3分 28 厦门大学计算机系工程硕士教材 使用序列的注意事项 不要使用CYCLE模式来生成主键值 除非有可靠的机制保证在生成下一轮数值时 旧的记录会被删除 序列独立存在 并非绑定在表格上 2020年4月7日1时3分 29 厦门大学计算机系工程硕士教材 查询序列 使用数据字典USER SEQUENCES查询序列SELECTsequence name min value max value increment by last numberFROMuser sequences NOCACHE模式下 last number返回序列下一个可以使用的数值 2020年4月7日1时3分 30 厦门大学计算机系工程硕士教材 NEXTVAL和CURRVAL伪字段 在创建序列后 就可以使用序列生成唯一值插入表格中的相关字段 序列中可以使用NEXTVAL和CURRVAL两个伪字段 调用方法为 sequence NEXTVAL和sequence CURRVAL NEXTVAL返回下一个可以使用的序列数 即使多个用户调用序列的NEXTVAL值 也可以保证每个用户得到的值是互不相同的 CURRVAL获取当前的序列值 对于一个序列而言 在没有调用NEXTVAL之前 CURRVAL是没有值的 2020年4月7日1时3分 31 厦门大学计算机系工程硕士教材 NEXTVAL和CURRVAL的使用规则 可以在以下情况中使用 SELECT语句中的SELECT列表 但不能是在子查询中INSERT语句子查询中的SELECT列表INSERT语句中的VALUE子句UPDATE语句中的SET子句不可以在以下情况中使用 视图中的SELECT列表包含DISTINCT关键字的SELECT语句包含GROUPBY ORDERBY HAVING子句的SELECT语句SELECT DELETE UPDATE语句中的子查询使用DEFAULT表达式的CREATETABLE和ALTERTABLE语句 2020年4月7日1时3分 32 厦门大学计算机系工程硕士教材 使用序列 DEPT表中新增一个部门DEVELOPMENT 位于DETROITINSERTINTOdept deptno dname loc VALUES dept deptid seq NEXTVAL DEVELOPMENT DETROIT 观察当前的序列值SELECTdept deptid seq CURRVALFROMdual CURRVAL 120 2020年4月7日1时3分 33 厦门大学计算机系工程硕士教材 如果需要向上一条语句中新增的部门中添加一个新员工 可以考虑使用以下方法 INSERTINTOemp empno deptno VALUES employees seq NEXTVAL dept deptid seq CURRVAL 2020年4月7日1时3分 34 厦门大学计算机系工程硕士教材 使用序列的注意事项 序列使用CACHE选项会加快其响应速度 序列生成的值可能会在以下情况时发生间断 ROLLBACK系统崩溃序列同时被其他表格使用如果序列使用NOCACHE选项 可以通过对USER SEQUENCES表的访问 查看序列下一个生成的值 2020年4月7日1时3分 35 厦门大学计算机系工程硕士教材 修改序列 ALTERSEQUENCEdept deptid seqINCREMENTBY20MAXVALUE999999NOCACHENOCYCLE Sequencealtered 2020年4月7日1时3分 36 厦门大学计算机系工程硕士教材 修改序列的语法格式 ALTERSEQUENCEsequence INCREMENTBYn MAXVALUEn NOMAXVALUE MINVALUEn NOMINVALUE CYCLE NOCYCLE CACHEn NOCACHE STARTWITH选项不能修改 2020年4月7日1时3分 37 厦门大学计算机系工程硕士教材 修改序列的注意事项 1 必须是序列的所有者 或者具有修改序列的系统权限 只影响修改后生成的序列值 对之前已经生成的值没有影响 ALTERSEQUENCE语句不能改变STARTWITH选项 如果需要重新从初始值开始使用序列 需要删除并重建序列 2020年4月7日1时3分 38 厦门大学计算机系工程硕士教材 修改序列的注意事项 2 ALTERSEQUENCE语句必须有效ALTERSEQUENCEdept deptid seqINCREMENTBY20MAXVALUE90NOCACHENOCYCLE ALTERSEQUENCEdept deptid seq ERRORatline1 ORA 04009 MAXVALUEcannotbemadetobelessthanthecurrentvalue 2020年4月7日1时3分 39 厦门大学计算机系工程硕士教材 删除序列 语法 DROPSEQUENCEsequence 使用DROPSEQUENCE语句从数据字典中删除序列 必须是序列的拥有者 或者具有删除序列的系统权限 DROPSEQUENCEdept deptid seq Sequencedropped 2020年4月7日1时3分 40 厦门大学计算机系工程硕士教材 数据库对象 2020年4月7日1时3分 41 厦门大学计算机系工程硕士教材 索引 什么是索引 索引是一个图表对象 索引可以显式建立 或者由服务器自动建立 索引加速ORACLE服务器定位数据 有效地降低I O访问量 如果不使用索引 有可能需要全表扫描 一旦建立索引 索引的使用和维护都由服务器负责 用户不必考虑 索引与被索引的表格是相对独立的 因此增加 删除 修改索引不会影响表格数据和其他索引 当表格被删除时 相关索引自动被删除 2020年4月7日1时3分 42 厦门大学计算机系工程硕士教材 索引的两种类型 自动建立 当创建表格时 如果有定义字段为PRIMARYKEY或者UNIQUE 相关的唯一索引会被自动创建 手工建立 用户可以创建非唯一的索引以加速数据定位 用户也可以创建唯一索引 不过建议采用定义字段为UNIQUE的方法 2020年4月7日1时3分 43 厦门大学计算机系工程硕士教材 创建索引 语法 CREATEINDEXindexONtable column column 加速对EMP表ename字段的查询CREATEINDEXemp ename idxONemp ename Indexcreated 2020年4月7日1时3分 44 厦门大学计算机系工程硕士教材 索引的创建原则 MoreIsNotAlwaysBetter以下情况中应当建立索引字段的取值分布范围很广字段的取值包含了很多NULL值一个或多个字段经常被WHERE子句使用到表数据很大 但大多数查询语句只需要其中2 4 左右的数据 注意如果需要定义唯一索引 应当在表格创建时就加上UNIQUE限制 2020年4月7日1时3分 45 厦门大学计算机系工程硕士教材 不适宜创建索引的情况 小型表格字段不是经常在查询语句中作为判定条件出现大多数的查询语句返回的记录数超过表格总记录数的2 到4 表格经常被更新被索引字段用于表达式中 2020年4月7日1时3分 46 厦门大学计算机系工程硕士教材 验证索引 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 47 基于函数的索引 CREATEINDEXidx upper dept nameONdept UPPER dname Indexcreated SELECT FROMdeptWHEREUPPER dname SALES 2020年4月7日1时3分 48 厦门大学计算机系工程硕士教材 NULL值对于函数索引的影响 SELECT FROMdeptORDERBYUPPER dname 假设dname字段可能存在空值 那么以上语句很可能在执行的时候用到了全表扫描 即使UPPER dname 已经被定义为索引 为了避免这种情况 使用如下语句 SELECT FROMdeptWHEREUPPER dname ISNOTNULLORDERBYUPPER dname 2020年4月7日1时3分 49 厦门大学计算机系工程硕士教材 删除索引 语法 DROPINDEXindex 删除DEPT表中的idx upper dept name索引DROPINDEXidx upper d