A07_DDL与Oracle数据库对象.ppt

DDL与数据库对象 课程代码 AMPC0103V20 课程介绍 目的 了解DDL指令与常规数据库对象操作 内容 数据库表及相关DDL操作 使用约束 视图操作 索引操作 使用序列 使用同义词 重点 表 约束难点 索引 数据库对象 表约束视图索引序列同义词 常用数据库对象 命名规则 必须以字母开头可以包含字母 数字 和 同一方案 用户 下的对象不能重名不能使用Oracle的保留字 一 表 创建表修改表结构删除表重命名表数据字典 创建表 基本语法 举例 说明 在向表中添加记录时如未显式指定某个字段的值 则该字段会被赋为此缺省值 如果在定义表时未设置过该字段的缺省值 则会被赋值为NULL CREATETABLE schema table columndatatype DEFAULTexpr CREATETABLEscott test1 eidNUMBER 10 nameVARCHAR2 20 hiredateDATEDEFAULTSYSDATE salaryNUMBER 8 2 DEFAULT1500 使用子查询创建表 在创建表的同时 可以将子查询的结果直接插入其中 语法 举例 说明 子查询中查询字段列表中如果出现了表达式 则应指定其表达式别名 否则就必须显式指定新建表的字段名 CREATETABLE schema table column ASsubquery CREATETABLEmyemp 编号 姓名 年薪 ASSELECTempno ename sal 12FROMemp CREATETABLEmyemp2ASSELECTempno ename sal 12annsalFROMemp 修改表结构 使用ALTERTABLE语句可修改表的结构 包括 添加字段修改字段删除字段禁用字段ALTER语句为DDL指令 一经执行不可撤消 添加字段 在ALTERTABLE语句中 使用ADD子句指定新添加的字段列表 新字段只能被加到整个表的最后 语法 举例 ALTERTABLEtableADD columndatatype DEFALUTexpr columndatatype ALTERTABLEtest1ADD gradeNUMBER 3 phoneVARCHAR2 20 DEFAULT 无 修改字段 在ALTERTABLE语句中 使用MODIFY子句可修改现有字段属性 包括字段的数据类型 大小和默认值等 语法 举例 ALTERTABLEtableMODIFY columndatatype DEFALUTexpr columndatatype ALTERTABLEtest1MODIFY gradeNUMBER 2 phoneVARCHAR2 15 DEFAULT 010 12345678 删除字段 在ALTERTABLE语句中 使用DROP子句可删除现字段 并释放相应的存储空间 语法 举例 ALTERTABLEtableDROPCOLUMNcolumn ALTERTABLEtableDROP column column ALTERTABLEtest1DROPCOLUMNsalary ALTERTABLEtest1DROP grade phone 禁用字段 在业务高峰期 可使用SETUNUSED子句可以将表中字段设为 无用 状态 待稍后再做删除 语法 举例 ALTERTABLEtableSETUNUSEDCOLUMNcolumn ALTERTABLEtableSETUNUSED column ALTERTABLEtest1SETUNUSEDCOLUMNhiredate ALTERTABLEtest1SETUNUSED name salary 删除表 丢弃表 使用DROP语句 销毁表结构 释放空间语法 举例 清除表中数据 使用TRUNCATE语句 保留表结构语法 举例 DROPTABLEtable DROPTABLEtest1 TRUNCATETABLEtable TRUNCATETABLEmyemp1 重命名表 使用RENAME语句可以改变现有表的名称也可修改其他数据库对象 视图 序列 同义词等 的名称执行重命名操作的必须是对象的所有者为DDL语句 一经执行不可撤消语法 举例 RENAMEobject nameTOnew object name RENAMEmyemp2TOmm2 数据字典 Oracle数据库中的表可分为两类 用户表 由用户自己创建并维护 其中保存用户数据 数据字典表 由Oracle数据库自动创建并维护的一组表 其中保存的是数据库相关自身信息 由一系列只读的表和视图组成 数据字典内容 数据库的物理和逻辑结构对象的定义和空间分配完整性约束条件用户角色权限审计记录 数据字典分类及访问 数据字典主要可分为三类dba 所有方案包含的对象信息all 用户可以访问的对象信息user 用户方案的对象信息访问数据字典 查看当前用户拥有的所有表的名字SELECTtable nameFROMuser tables 查看当前用户可以访问的所有表的名字SELECTtable nameFROMall tables 查看当前用户拥有的所有对象的类型SELECTDISTINCTobject typeFROMuser objects 查看所有用户拥有的所有对象的类型 执行本指令需具备DBA身份 SELECTtable nameFROMdba tables 二 约束 约束概述非空约束唯一性约束主键约束外键约束检查约束约束管理 约束概述 约束 Constraint 是在表上强制执行的数据校验规则 用于保护数据的完整性 Oracle使用SYS Cn格式自动命名约束 也可以由用户命名 可在建表的同时创建约束 也可以在建表后单独添加约束 可以在表级或列级定义约束 可以通过数据字典视图查看约束 约束分类 notnull 非空 uniquekey 唯一键 primarykey 主键 foreignkey 外键 check 检查 建表的同时创建约束 语法 CREATETABLE schema table columndatatype DEFAULTexpr column constraints table constraints 非空约束 非空 NOTNULL 约束用于确保字段值不能为空 NULL 非空约束只能在字段级定义 举例 CREATETABLEstudent sidNUMBER 3 NOTNULL nameVARCHAR2 20 birthDATECONSTRAINTstudent birth nnNOTNULL 唯一性约束 唯一性 UNIQUE 约束用于确保其所约束的字段或字段组合不出现重复的值 唯一性约束的字段允许出现空值 Oracle会自动为唯一性约束创建对应的唯一性索引唯一性约束既可以在字段级定义 也可以在表级定义 CREATETABLEstudent sidNUMBER 3 UNIQUE nameVARCHAR2 20 CREATETABLEstudent sidNUMBER 3 nameVARCHAR2 20 CONSTRAINTstudent sid unUNIQUE sid 主键约束 主键 PRIMARYKEY 用于唯一标识表中的某一行记录 其功能上相当于非空且唯一 一个表中只允许一个主键 可以是单个字段或多字段的组合 Oracle会自动为主键字段创建对应的唯一性索引 主键约束既可以在字段级定义 也可以在表级定义 CREATETABLEstudent sidNUMBER 3 PRIMARYKEY nameVARCHAR2 20 CREATETABLEstudent sidNUMBER 3 nameVARCHAR2 20 CONSTRAINTstudent sid pkPRIMARYKEY sid 联合主键 由多个字段组合而成的主键称联合主键 联合主键中每一个字段都不能为空 联合主键字段组合的值不能出现重复 联合主键只能定义为表级约束 CREATETABLEscores sidNUMBER 3 subjectVARCHAR2 20 scoreNUMBER 3 CONSTRAINTscore sid subject pkPRIMARYKEY sid subject INSERTINTOscoresVALUES 101 语文 88 合法INSERTINTOscoresVALUES 101 数学 99 合法INSERTINTOscoresVALUES 101 语文 66 非法INSERTINTOscoresVALUES NULL 语文 77 非法 外键约束 外键 FOREIGNKEY 约束用于确保相关的两个字段之间的参照关系 子表外键字段的值必须在主表被参照字段值的范围内 或者为空值NULL 外键参照的必须是主表的主键或者唯一键 主表主键 唯一键值被子表参照时 主表相应记录不允许被删除 外键约束 续 CREATETABLEempinfo eidNUMBER 3 PRIMARYKEY enameVARCHAR2 20 jobVARCHAR2 20 birthDATE CREATETABLEsalary eidNUMBER 3 basic salaryNUMBER 8 2 job allowanceNUMBER 8 2 travelling allowanceNUMBER 8 2 personal income taxNUMBER 8 2 CONSTRAINTsalary eid fkFOREIGNKEY eid REFERENCESempinfo eid CREATETABLEsalary eidNUMBER 3 REFERENCESempinfo eid 外键约束 续 外键约束可以和主键约束同时使用 CREATETABLEsalary eidNUMBER 3 PRIMARYKEY CONSTRAINTsalary eid fkFOREIGNKEY eid REFERENCESempinfo eid CREATETABLEsalary eidNUMBER 3 PRIMARYKEYREFERENCESempinfo eid 外键约束 续 外键约束也可以构建于同一个表内部的两个字段之间 定义测试表 在其内部字段间定义参照完整性约束CREATETABLEmyemp empnoNUMBER 4 PRIMARYKEY enameVARCHAR2 20 jobVARCHAR2 9 mgrNUMBER 4 REFERENCESmyemp empno salNUMBER 7 2 插入测试数据 操作合法 符合完整约束条件 INSERTINTOmyemp empno ename mgr VALUES 101 Billy NULL INSERTINTOmyemp empno ename mgr VALUES 102 John 101 INSERTINTOmyemp empno ename mgr VALUES 103 Nancy 101 INSERTINTOmyemp empno ename mgr VALUES 104 Tom 102 操作非法 违反完整约束条件 未找到父项关键字 INSERTINTOmyemp empno ename mgr VALUES 106 Billy 108 检查约束 检查 CHECK 约束用于定义数据字段必须要满足的条件 包括数据的取值范围 文本内容格式等 具体以条件表达式的形式给出 检查约束的条件表达式中不允许出现如下内容 currval nextval level rownum等伪列sysdate uid user userenv等函数对其它字段值的引用 CREATETABLEtest1 nameVARCHAR2 20 ageNUMBER 3 CHECK age 0ANDage 120 查看约束 查询用户字典视图user constrains 可得到用户的所有约束 查询用户字典视图user cons columns 可获知约束建立在哪些字段上 SELECT FROMuser constraints SELECT FROMuser cons columns 建表后添加约束 语法 举例 ALTERTABLEtableADD CONSTRAINTconstraint name constraint type column CREATETABLEstudent sidNUMBER 10 nameVARCHAR2 20 ALTERTABLEstudentADDCONSTRAINTstudent sid pkPRIMARYKEY sid 删除约束 删除主键约束的另一种方式 ALTERTABLEtableDROPCONSTRAINTconstraint name CASCADE CREATETABLEstudent sidNUMBER 10 nameVARCHAR2 20 CONSTRAINTstudent name nnNOTNULL CONSTRAINTstudent sid pkPRIMARYKEY sid ALTERTABLEstudentDROPCONSTRAINTstudent sid pk ALTERTABLEstudentDROPCONSTRAINTstudent name nn ALTERTABLEtableDROPPRIMARYKEY ALTERTABLEstudentDROPPRIMARYKEY 删除级连约束 在删除约束时 如果还存在与该约束相关联的其它约束 则删除操作会失败 此时可使用CASCADE子句将其它关联约束一并删除 CREATETABLEempinfo eidNUMBER 3 CONSTRAINTempinfo eid pkPRIMARYKEY enameVARCHAR2 20 CREATETABLEsalary eidNUMBER 3 REFERENCESempinfo eid ALTERTABLEempinfoDROPCONSTRAINTempinfo eid pkCASCADE 删除级连约束 续 在删除表中字段时 如果该字段处于多字段联合约束条件 联合主键 联合唯一键 存在参照当前字段的外键 中 则删除会失败 此时可使用CASCADECONSTRAINT子句将与该字段相关的约束一并删除 CREATETABLErecord student idNUMBER 3 subject idVARCHAR2 20 recordNUMBER 3 CONSTRAINTrecord stuId subId pkPRIMARYKEY student id subject id ALTERTABLErecordDROP student id CASCADECONSTRAINT 禁用 启用约束 ALTERTABLE语句中可使用DISABLECONSTRAINT子句禁用已有约束 还可使用CASCADE选项将相关联的约束一并禁用 相应地 可以使用ENABLECONSTRAINT子句启用先前被禁用的约束 但此时无法再使用CASCADE选项一并启用相关联的其它约束 ALTERTABLEtableDISABLECONSTRAINTconstraint name CASCADE ALTERTABLEstudentDISABLECONSTRAINTstudent sid pkCASCADE ALTERTABLEstudentENABLECONSTRAINTstudent sid pk 三 视图 什么是视图基本视图操作创建视图查询视图删除视图重新编译视图强制创建视图创建复杂视图在视图上执行更新操作添加数据更新数据删除数据 什么是视图 视图 View 是从数据表 或者其它视图 中提取数据而成的一种虚拟表 视图优点及分类 视图优点 简化复杂数据查询提高运行效率屏蔽数据库表结构 实现数据逻辑独立性限制数据库访问在相同数据上提供不同的视图 便于数据共享视图分类 创建视图 使用嵌入子查询的CREATEVIEW语句创建视图 语法 举例 CREATE ORREPLACE VIEW schema view alias ASsubquery WITHREADONLY CREATEORREPLACEVIEWmyview1 编号 姓名 职位 工资 ASSELECTempno ename job salFROMempWHEREdeptno 20 查询视图 DESCmyview1 SELECT FROMmyview1 SELECT编号 工资FROMmyview1WHERE职位 ANALYST SELECT FROMuser views 删除视图 语法 举例 DROPVIEWview DROPVIEWmyview1 重新编译视图 如果视图的基表结构发生变化 比如添加或删除了字段 则视图状态会被标记为无效 此后当再有用户访问该视图时 Oracle会自动重新编译该视图 以避免可能的运行错误 此外 用户也可以执行ALTERVIEW语句手工编译视图 语法 举例 ALTERVIEWviewCOMPILE ALTERVIEWmyview1COMPILE 强制创建视图 如果视图定义中所涉及的基表不存在 或者字段有问题 则视图创建操作会失败 此时可使用FORCE选项强制创建视图 然后再定义基表或修改基表结构 语法 举例 CREATE ORREPLACE FORCE NOFORCE VIEW schema view alias ASsubquery CREATEORREPLACEFORCEVIEWmyview2ASSELECTempno ename job salFROMemp2WHEREdeptno 20 创建复杂视图 视图定义中涉及到了多个基表 或者在视图定义的子查询中使用了函数 复合表达式或数据分组等 CREATEORREPLACEVIEWv sal deptno maxsal minsal avgsal ASSELECTdeptno max sal min sal avg sal FROMempGROUPBYdeptno CREATEORREPLACEFORCEVIEWv emp 工号 姓名 职位 年薪 工龄 月 部门编号 部门名称 ASSELECTempno ename job sal 12 months between SYSDATE hiredate emp deptno dnameFROMemp deptWHEREemp deptno dept deptno 在视图上执行更新操作 在可更新视图上进行DML操作 可以修改基表中数据 在视图上执行数据更新操作 应遵循如下原则 用户还需要拥有对视图基表进行更新操作的权限 视图定义的子查询中不能使用分组函数 GROUPBY子句 DISTINCT关键字 rownum伪列 其查询字段不能为表达式 由两个以上基表中导出的视图上不允许进行DELETE操作 基表中非空的字段在视图定义中未包括 该字段也未曾设置过缺省值 则不可在视图上进行INSERT操作 在视图上进行DML操作 语法与在表上操作相同 四 索引 索引概述创建索引查看索引删除索引 索引概述 索引 Index 一种用于提升查询效率的数据库对象 使用索引可以快速定位数据 减少磁盘I O操作次数 常规索引分类唯一性索引和非唯一性索引单字段索引和联合索引普通索引和函数索引B树索引和位图索引创建索引的两种方式自动创建 在定义主键或唯一键约束时系统会自动在相应的字段上创建唯一性索引 手动创建 用户可在其它列上创建非唯一性索引以加速查询 创建索引 CREATE UNIQUE BITMAP INDEX schema indexONtable column column 创建测试数据表myempCREATETABLEmyempASSELECTempno ename job sal deptnoFROMemp 创建基于ename字段的普通索引CREATEINDEXmyindexONmyemp ename 创建基于deptno和sal字段的联合索引CREATEINDEXmyindex2ONmyemp deptno sal 创建基于empno字段的唯一性索引CREATEUNIQUEINDEXmyindex3ONmyemp empno 创建基于job字段的位图索引CREATEBITMAPINDEXmyindex4ONmyemp job 创建基于lower ename sal 12的函数索引CREATEINDEXmyindex5ONmyemp lower ename CREATEINDEXmyindex6ONmyemp sal 12 查看索引 查询用户字典视图user indexes 可获取索引名称 类型 所在表名 表拥有者 等信息 查询用户字典视图user ind columns 可获取索引具体建立在哪个字段上 SELECT FROMuser indexes SELECT FROMuser ind columnsWHEREtable name myemp 删除索引 索引一经创建 不可以修改 如果要修改则应先删除掉再重新创建 如果不再需要 可以手工删除索引 在删除数据表和字段时系统会自动删除相关索引 语法 举例 DROPINDEXindex name DROPINDEXmyindex1 索引优缺点 优点 提升查询效率缺点建立索引会增加物理区域开销插入 修改 删除数据时效率变低原则很少做查询条件的字段不要建索引逻辑字段 性别 不要建索引其他索引函数 全局 分区 唯一 非唯一 位图 反向 五 序列 创建序列使用序列修改序列删除序列 序列概述 序列 Sequence 实际上是一种 序列号 生成器 它可以自动生成一组唯一的整型数字 其典型用途是为数据表的主键字段赋值 代替应用程序编号 序列是用户创建的数据库对象 可以被多个用户共享 创建序列 Oracle数据库使用CREATESEQUENCE语句创建序列 语法 举例 CREATESEQUENCE schema sequence INCREMENTBYn STARTWITHn MAXVALUEn NOMAXVALUE MINVALUEn NOMINVALUE CYCLE NOCYCLE 是否循环 CACHEn NOCACHE 是否预设值 ORDER NOORDER 并发读取 CREATESEQUENCEmysequence1INCREMENTBY1STARTWITH1NOMAXVALUENOCYCLE CREATESEQUENCEmysequence2 使用序列 相关伪列 nextval伪列用于从指定的序列数值中取出下一个值currval伪列引用的是指定序列的 当前值 用法举例 SELECTmysequence1 currvalFROMdual SELECTmysequence1 nextvalFR