《其他数据库对象》PPT课件.ppt

OracleSQL开发基础 课程结构 第十章其他数据库对象 目标 本章旨在向学员介绍 1 视图2 序列3 索引4 同义词 时间 2 5学时教学方法 讲授ppt 上机练习 本章要点 视图序列索引同义词 第十章其他数据库对象 其他数据库对象 10 1视图10 2分页10 3序列10 4索引10 5同义词 10 1 1视图的概念1 3 视图的概念视图是虚表 是一个命名的查询 用于改变基表数据的显示 简化查询 视图的访问方式与表的访问方式相同 视图的好处 可以限制对基表数据的访问 只允许用户通过视图看到表中的一部分数据可以使复杂的查询变的简单提供了数据的独立性 用户并不知道数据来自于何处提供了对相同数据的不同显示 10 1 1视图的概念2 3 简单视图和复杂视图简单视图 只涉及到一个表 而且SELECT子句中不包含函数表达式列 包括单行函数和分组函数 复杂视图 涉及到一个或多个表 SELECT子句中包含函数表达式列 单行函数或分组函数 特征简单视图复杂视图基表数量一个一个或多个包含函数没有有包含数据组没有有通过视图实现DML操作可以不一定 最基本差别在DML操作上 10 1 1视图的概念3 3 简单视图和复杂视图 10 1 2视图的管理1 11 创建视图在子查询中可以加入复杂的SELECT 子查询中能使用ORDERBY子句 如果想排序 可以在执行查询视图时使用 CREATE ORREPLACE FORCE NOFORCE VIEWview alias alias ASsubquery WITHCHECKOPTION CONSTRAINTconstraint WITHREADONLY CONSTRAINTconstraint 10 1 2视图的管理2 11 创建视图ORREPLACE 如果所创建的视图已经存在 Oracle会自动重建该试图FORCE 不管基表是否存在 Oracle都会创建该视图NOFORCE 只有基表都存在 Oracle才会创建该视图view 视图的名称alias 为视图所产生的列定义别名 别名的数量必须和视图所产生列的数量相等subquery 一条完整的SELECT语句 在该语句中同样可以定义别名WITHCHECKOPTION 通过视图做DML操作时 必须要保证所插入或修改或删除的数据行必须满足视图所定义的约束 也就是说插入或更新后的结果必须仍然可以通过该视图查询得到 constraint CHECKOPTION中的约束名WITHREADONLY 确保在该视图上不能进行任何DML操作 默认可以通过视图执行DML操作 10 1 2视图的管理3 11 创建视图示例例10 3查询50部门的员工的年薪的视图 CREATEORREPLACEVIEWsalvu50ASSELECTemployee idID NUMBER last nameNAME salary 12ANN SALARYFROMemployeesWHEREdepartment id 50 DESCsalvu50 SELECT FROMsalvu50 10 1 2视图的管理4 11 通过视图执行DML操作创建一个测试用表EMP DML创建视图v emp1 是个简单视图 通过视图进行DML操作 CREATETABLEemp dmlASSELECTemployee id last name salaryFROMemployeesWHEREdepartment id 50 CREATEORREPLACEVIEWv emp1ASSELECTemployee id salaryFROMemp dml UPDATEv emp1SETsalary salary 100 10 1 2视图的管理5 11 不能通过视图删除记录的条件视图中包含分组函数视图中含有GROUPBY子句视图中含有DISTINCT关键字视图中包含伪列ROWNUM 10 1 2视图的管理6 11 不能通过视图修改记录的条件视图中包含分组函数视图中含有GROUPBY子句视图中含有DISTINCT关键字视图中包含伪列ROWNUM视图中要修改的列包含表达式 10 1 2视图的管理7 11 不能通过视图添加记录的条件视图中包含分组函数视图中含有GROUPBY子句视图中含有DISTINCT关键字视图中包含伪列ROWNUM视图中要修改的列包含表达式视图中没有表的NOTNULL列 10 1 2视图的管理8 11 WITHCHECKOPTIONWITHCHECKOPTION实质是给视图加一个 CHECK 约束 该CHECK约束的条件就是视图中的子查询的WHERE条件 以后如果想通过该视图执行DML操作 不允许违反该CHECK约束 10 1 2视图的管理9 11 WITHCHECKOPTION示例例10 6WITHCHECKOPTION例子该约束的条件为视图中WHERE条件 即 employee id 141 如视图v emp3想执行DML操作 不能把记录的employee id字段值改成其他编号 只能是141 如果违反了 执行出错 会出现错误提示 CREATEORREPLACEVIEWv emp3ASSELECTemployee id salaryFROMemp dmlWHEREemployee id 141WITHCHECKOPTIONCONSTRAINTv emp3 ck 10 1 2视图的管理10 11 WITHREADONLYWITHREADONLY的视图是只读的 不允许通过该视图执行DML语句 例10 7WITHREADONLY例子 创建一个视图v emp4通过该视图进行更新操作 UPDATEv emp4SETsalary salary 100 CREATEORREPLACEVIEWv emp4ASSELECTemployee id salaryFROMemp dmlWITHREADONLY 10 1 2视图的管理11 11 删除视图删除视图的语法 DROPVIEWview 10 1 3内联视图 内联视图内联视图 InlineView 是一个在SQL语句内可以使用的子查询的别名 是一个命名的SQL语句 但不是真正的数据库的视图对象 最常见的内联视图的例子就是主查询中的FROM子句中 包含的是一个命名的子查询 例10 8内联视图的例子 SELECTlast name department nameFROMDEPARTMENTSa SELECTlast name department idFROMEMPLOYEES bWHEREa department id b department id 第十章其他数据库对象 其他数据库对象 10 1视图10 2分页10 3序列10 4索引10 5同义词 10 2 1ROWNUM及其特性1 2 ROWNUM介绍ROWNUM是一个伪列 伪列是使用上类似于表中的列 而实际并没有存储在表中的特殊对象 ROWNUM的功能是在每次查询时 返回结果集的顺序数 这个顺序数是在记录输出时才一步一步产生的 第一行显示为1 第二行为2 以此类推 例 生成员工表记录序号SELECTROWNUM employee id first name hire dateFROMemployees 10 2 1ROWNUM及其特性2 2 ROWNUM使用的注意点 1 ROWNUM和ORDERBY一起使用序号会混乱2 对ROWNUM只能执行 或一个区间运算Between And等 例1 ROWNUM和ORDERBY一起使用的结果SELECTROWNUM employee id first name hire dateFROMemployeesORDERBYfirst name 10 2 2TOP N分页1 2 分页 Top N Top N分页查询主要是实现查找表中最大或最小的N条记录功能 Top N分析语法 SELECT 列名 ROWNUMFROM SELECT 列名 FROM表名ORDERBYTop N操作的列 WHEREROWNUM N 10 2 2TOP N分页2 2 分页 Top N 按照员工工资从高到低的分页SQL语句 SELECT FROM SELECT FROM SELECT FROMEMPLOYEESORDERBYSALARYDESC WHEREROWNUM 每页的记录数 目标页数ORDERBYSALARY WHEREROWNUM 每页的记录数ORDERBYSALARYDESC 10 2 3ROWNUM分页1 2 分页 ROWNUM ROWNUM主要是利用ROWNUM生成的序号进行分页查询 语法分析 利用列别名将ROWNUM列转化为实际列 SELECT 列名 ROWNUMasRNFROM SELECT 列名 FROM表名ORDERBYTop N操作的列 10 2 3ROWNUM分页2 2 分页 ROWNUM 按照员工工资从高到低的分页SQL语句 SELECT FROM SELECTROWNUMASrn t FROM SELECT FROMEMPLOYEESORDERBYSALARYDESC t WHERErnBETWEEN每页的记录数 目标页数 1 1AND每页的记录数 目标页数 第十章其他数据库对象 其他数据库对象 10 1视图10 2分页10 3序列10 4索引10 5同义词 10 3 1序列的概念 序列是一种用于产生唯一数字列值的数据库对象 一般使用序列自动地生成主码值或唯一键值 序列可以是升序或降序 序列特点 可以为表中的记录自动产生唯一序列值 由用户创建并且可被多个用户共享 典型应用是生成主键值 用于标识记录的唯一性 允许同时生成多个序列号 而每一个序列号是唯一的 可替代应用程序中使用到的序列号 使用缓存加速序列的访问速度 10 3 2创建序列 创建序列的语法 创建序列 必须有CREATESEQUENCE或CREATEANYSEQUENCE权限 序列被创建后 可以通过查询数据字典视图USER SEQUENCES查看序列信息 CREATESEQUENCE schema 序列名 INCREMENTBYn STARTWITHn MAXVALUEn NOMAXVALUE MINVALUEn NOMINVALUE CYCLE NOCYCLE CACHEn NOCACHE 10 3 2创建序列 语法中 sequence序列产生器的名称INCREMENTBYn指定序列值之间的间隔n 如果省略该项间隔为1STARTWITHn指定起始序列值n 如果该项省略起始值为1MAXVALUEn指定最大序列值NOMAXVALUE指定最大序列值为10的27次方 而降序的最大值为 1 这是一默认选项MINVALUEn指定最小序列值NOMINVALUE指定升序最小序列值为1 而降序最小序列值为负10的26次方 这是一默认选项CYCLE NOCYCLE指定序列值在达到最大值或最小值之后继续产生序列值 NOCYLE表示不再产生 NOCYLE是默认选项 CACHEn NOCACHE指定序列值被Oracle服务器预先分配并存储在内存中 NOCACHE表示不预先分配并存储 CACHE20是默认选项 10 3 2创建序列 当全部缺省时 则该序列为上升序列 由1开始 增量为1 没有上限 缓存中序列值个数为20 INCREMENTBYn n可为正的或负的整数 但不可为0 默认值为1 STARTWITHn 指定生成的第一个序列号 默认为1 对于升序 默认为序列的最小值 对于降序 默认为序列的最大值 MAXVALUEn 指定n为序列可生成的最大值 NOMAXVALUE 为默认情况 指定升序最大值为1027 指定降序指定最大值为 1 10 3 2创建序列 MINVALUEn 指定n为序列的最小值 NOMINVALUE 为默认情况 指定升序默认最小值为1 指定降序默认最小值为 1026 CYCLE 指定序列使用循环 即序列达到了最大值 则返回最小值重新开始 默认为NOCYCLE CACHEn 定义n个序列值保存在缓存中 默认值为20个 10 3 2创建序列 例10 12创建序列test seq CREATESEQUENCEtest seqSTARTWITH10 序列从10开始INCREMENTBY2 序列每次增加2MAXVALUE100 序列最大值100MINVALUE9 序列最小值9CYCLE 序列循环 每次增加2 一直到100后回到9从新开始CACHE10 缓存中序列值个数为10 10 3 3NEXTVAL和CURRVAL伪列 可用语句sequence name CURRVAL和sequence name NEXTVAL来访问序列 CURRVAL当前序列正被分配的序列值 NEXTVAL在序列中增加新值并返回此值 CURRVAL和NEXTVAL都返回NUMBER值 NEXTVAL应在CURRVAL之前指定 二者应同时有效 10 3 3NEXTVAL和CURRVAL伪列 您可以在下列情况使用NEXTVAL和CURRVAL SELECT语句的SELECT列表中 但不包括子查询中的SELECT语句INSERT语句中的子查询SELECT列表中INSERT语句的VALUES子句中UPDATE语句的SET子句中下列情况不能使用NEXTVAL和CURRVAL 在视图的SELECT列表中包含DISTINCT关键字的SELECT语句中含有GROUPBY HAVING ORDERBY子句的SELECT语句中SELECT DELETE UPDATE语句的子查询中含有DEFAULT表达式的CREATETABLE ALTERTABLE语句中 10 3 3NEXTVAL和CURRVAL伪列 创建序列student seq 使用序列student seq生成student表中sid列插入值 查看student seq序列当前值 SELECTstudent seq CURRVALFROMdual INSERTINTOstudentVALUES student seq NEXTVAL Scott ComputerScience 11 CREATESEQUENCEstudent seqSTARTWITH10000INCREMENTBY1 10 3 4修改序列 修改序列的语法如下 必须是序列的所有者 或者有ALTERANYSEQUENCE权限才能修改序列 修改序列的语法除没有STARTWITH子句外 ALTERSEQUENCE schema 序列名 INCREMENTBYn MAXVALUEn NOMAXVALUE MINVALUEn NOMINVALUE CYCLE NOCYCLE CACHEn NOCACHE 10 3 4修改序列 正确修改错误修改 ALTERSEQUENCEtest seqINCREMENTBY4MAXVALUE100 最大值100小于已经分配的序列值200NOCACHE ALTERSEQUENCEtest seqINCREMENTBY4 序列每次增加4MAXVALUE1000 序列最大值1000NOCACHE 不设定缓存 10 3 5删除序列 对序列的删除必须是序列的所有者或者具有DROPANYSEQUENCE权限的用户才可以完成 删除序列的语法如下 例10 15删除序列student seq删除序列student seq DROPSEQUENCEstudent seq DROPSEQUENCE schema 序列名 第十章其他数据库对象 其他数据库对象 10 1视图10 2分页10 3序列10 4索引10 5同义词 10 4 1索引的概念 索引是 方案 schema 中的一个数据库对象在Oracle数据库中用来加速对表的查询速度通过使用快速路径访问方法快速定位数据 减少了磁盘的I O与表独立存放 但需要依附于表 是在表的基础上创建的由Oracle数据库自动维护 10 4 1索引的概念 10 4 2创建索引 创建索引有两种方式 自动或者手动自动 当在表上定义一个PRIMARYKEY或者UNIQUE约束条件时 Oracle数据库自动创建一个对应的唯一索引 手动 用户可以创建索引以加速查询 在需要创建索引的字段上创建需要的索引 10 4 2创建索引 在一列或者多列上创建索引 下面的索引将会提高对EMPLOYEES表基于LAST NAME字段的查询速度 CREATEINDEXindexnameONtable column column CREATEINDEXemp last name idxONemployees last name Indexcreated 10 4 3测试索引 创建测试环境表 测试无索引检索时间 CREATETABLEe1ASSELECT FROMemployees INSERTINTOe1SELECT FROMe1 多次运行UPDATEe1SETemployee id ROWNUM 更新所有记录的employee id 以使其数值唯一commit 提交 settimingon 设置环境变量timingSELECTlast name salaryFROMe1WHEREemployee id 210000 没有索引的情况下做测试已用时间 00 00 06 05 没有索引的时候用时6秒 10 4 3测试索引 测试建索引后检索时间 CREATEINDEXe1 idONe1 employee id 创建索引SELECTlast name salaryFROMe1WHEREemployee id 210000 再次在有索引的情况下做测试已用时间 00 00 00 00 测试结果 几乎没有消耗什么时间settimingoff 取消时间统计 10 4 4索引分析 适合创建索引情况查询列的数据范围很广泛查询列中包含大量的NULL值WHERE条件中的列或者多表连接的列适合创建索引欲查询的表数据量很大 而且大多数的查询得到结果集的数量占总记录量的2 4 10 4 4索引分析 不适合创建索引的情况很小数据量的表在查询中不常用来作为查询条件的列查询最终得到的结果集很大频繁更新的表 索引对于DML操作是有部分负面影响的 索引列作为表达式的一部分被使用时 比如常查询的条件是SALARY 12 此时在SALARY列上创建索引是没有效果的 10 4 5删除索引 删除索引的语法删除索引后 索引中的数据及定义被删除 索引所占的数据空间被释放 但表中的数据仍然存在 常用与索引相关的数据字典视图有 USER INDEXES 用户的索引对象的定义