数据库基础及应用第章表的管理PPT课件.ppt

oracle数据库基础及应用 第十一章表的管理 2020 3 18 1 目录 11 1创建表11 2修改表11 3删除表11 4创建约束11 5使用企业管理器管理表11 6分区表11 7临时表 2020 3 18 2 新建数据库StuInfo 学生成绩管理 数据库中有5张表 系表DEPT教师表TEACHER学生表STUDENT课程表COURSE成绩表SCORE P271 本章代码见本页PPT备注 2020 3 18 3 2020 3 18 4 11 1创建表 表是成数据库最基本的单位 由行和列组 它是数据库存在的意义 在DDL语句中 使用CREATE语句创建表 其语法结构如下 CREATETABLE NULL NOTNULL NULL NOTNULL 约束条件 2020 3 18 5 11 1创建表 例创建系表DEPT 其表结构如下1CREATETABLEDEPT 2DnoVARCHAR2 6 3DnameVARCHAR2 50 NOTNULL 4DtelephoneVARCHAR2 12 5DdeanVARCHAR2 10 NOTNULL 6DaddressVARCHAR2 50 7CONSTRAINTSdept dno pkPRIMARYKEY dno 8CONSTRAINTSdept dname unkUNIQUE Dname 9 2020 3 18 6 5个表 2020 3 18 7 11 2修改表 11 2 1增加列11 2 2删除列11 2 3更新列11 2 4修改列名11 2 5重命名表11 2 6修改表的状态 2020 3 18 8 11 2修改表 如果对已经创建的表进行修改 则需要使用ALTRETABLE关键字进行修改 其语法结构如下 ALTERTABLEADD数据类型 增加列 DROPCOLUMN 删除列 MODIFY 数据类型 修改列 RENAMECOLUMNTO 重命名列 RENAMETO 重命名表 2020 3 18 9 11 2修改表 为了使结果更加清晰 我们以插入数据后的系表DEPT为例 系表中的数据如下图所示 2020 3 18 10 建立sys连接 修改SCOTT账号 2020 3 18 11 建立scott连接 口令stuinfo 2020 3 18 12 将原来的dept deptold 2020 3 18 13 建5个表 2020 3 18 14 给表DEPT添加数据 2020 3 18 15 11 2 1增加列 例修改系表DEPT 增加一列TEACH NUM表示该系的教师人数 数据类型为NUMBER使用代码为 ALTERTABLEdeptADDteach numNUMBER 运行结果如下所示 2020 3 18 16 ALTERTABLEdeptADDteach numNUMBER 2020 3 18 17 2020 3 18 18 11 2 1增加列 在增加新列的时候 如果表中已经存在行记录 那么就不能为新列指定NOTNULL约束 执行下面代码 出错 ALTERTABLEdeptADDteach phoneNUMBERnotnull 2020 3 18 19 11 2 1增加列 若要指定NOTNULL约束 则必须为新列设置默认值 执行下面一行代码 则不会出错 ALTERTABLEdeptADDteach addchar 12 default 山东省日照市 notnull 2020 3 18 20 select fromdept 2020 3 18 21 11 2 1增加列 在Oracle11g中 可以同时增加多列 例在系表DEPT中增加类型都为VARCHAR2 3 的两列A和B 代码如下所示 ALTERTABLEdeptADD Avarchar2 3 Bvarchar2 3 2020 3 18 22 11 2 2删除列 删除列使用ALTERTABLE的DROP语句 常与CASCADECONSTRAINTS一起使用 将与该列有关的所有约束一起删除 例修改系表DEPT 将列TEACH NUM删除 代码如下所示 ALTERTABLEdeptDROPCOLUMNteach numCASCADECONSTRAINTS 2020 3 18 23 11 2 3更新列 更新列时使用ALTERTABLE的MODIFY语句 例将系表DEPT的Dno列的数据类型更新为VARCHAR2 20 代码如下所示 ALTERTABLEdeptMODIFYDnoVARCHAR2 20 然后可以从USER TAB COLS中查看系表DEPT的各列的属性值 代码如下所示 SELECTcolumn name data type data lengthFROMUSER TAB COLSWHERETABLE NAME DEPT descdept 2020 3 18 24 11 2 4修改列名 修改列名指的是修改表中指定列的名字 修改列名时使用ALTERTABLE的RENAMECOLUMN语句 例修改系表DEPT的DTELEPHONE列的列名 新列名为DPHONENUMBER 代码如下 ALTERTABLEdeptRENAMECOLUMNDtelephoneTODphonenumber 2020 3 18 25 11 2 5重命名表 更新表名使用ALTERTABLE的RENAMETO命令 例重命名系表DEPT的表名 将系表表名DEPT更改为DEPARTMENT 其代码如下 ALTERTABLEdeptRENAMETOdepartment 重命名表也可以使用RENAME TO 语句 其语法结构如下 RENAMETO 本例修改表名 也可以采用下面的代码 RENAMEdeptTOdepartment 2020 3 18 26 11 2 6修改表的状态 删除列时若使用ALTERTABLE的DROP语句 在删除列的同时 也释放该列所占用的存储空间 但是 如果表较大 这种删除操将耗费很长时间 为了避免在数据库使用高峰期执行删除列的操作而占用过多的系统资源 我们可以使用修改表状态的方法 即将要删除的列暂时标记为不可用状态 然后等后来系统空闲时再进一步释放列空间 2020 3 18 27 11 2 6修改表的状态 标记列不可用使用关键字UNUSED 其语法结构为 ALTERTABLESETUNUSED CASCADECONSTRAINTS 例将系表DEPT的TEACH NUM列标注为UNUSED 其代码如下所示 ALTERTABLEdeptSETUNUSED teach num CASCADECONSTRAINTS 2020 3 18 28 11 2 6修改表的状态 释放不可用的列所占用的空间 也就是删除不可用的列 此时使用关键字DROP 其语法结构为 ALTERTABLEDROPUNUSEDCOLUMNS 例删除系表DEPT中所有状态为UNUSED的列 其代码如下所示 ALTERTABLEdeptDROPUNUSEDCOLUMNS 2020 3 18 29 11 3删除表 在数据库的使用过程中 经常会出现一些冗余的表 为了节约空间 这些表需要被删除 删除表使用DROPTABLE语句 其语法结构如下 DROPTABLE CASCADECONSTRAINTS 例删除表DEPTDROPTABLEdept DROPTABLEdeptCASCADECONSTRAINTS 2020 3 18 30 11 4创建约束 11 4 1主键约束11 4 2外键约束11 4 3唯一约束11 4 4检查约束11 4 5非空约束 2020 3 18 31 11 4 1主键约束 主键约束通过PRIMARYKEY定义 定义主键对行数据起到唯一标识的作用 其值不能为空 也不能重复 每个表只允许有一个主键 但主键可以由一个表的多个列构成 接下来以教师表为例 其表结构如下 2020 3 18 32 11 4 1主键约束 创建表时创建主键约束语法结构如下 CONSTRAINTSPRIMARYKEY 主键列名 例创建教师表时创建主键约束CREATETABLETEACHER TnoVARCHAR2 6 TnameVARCHAR2 10 NOTNULL TtitleVARCHAR2 8 CONSTRAINTSteacher tno fkPRIMARYKEY Tno 2020 3 18 33 11 4 1主键约束 在创建完表之后 使用ALTERTABLE添加主键约束 其语法结构如下 ALTERTABLEADDCONSTRAINTSPRIMARYKEY 主键列名 例创建教师表之后 添加主键约束CREATETABLETEACHER TnoVARCHAR2 6 TnameVARCHAR2 10 NOTNULL TtitleVARCHAR2 8 ALTERTABLEteacherADDCONSTRAINTSteacher tno fkPRIMARYKEY Tno 2020 3 18 34 11 4 1主键约束 删除主键约束 使用ALTERTABLE的DROP语句 其语法结构如下 ALTERTABLEDROPCONSTRAINTS 例删除教师表的主键约束teacher tno fkALTERTABLEteacherDROPCONSTRAINTSteacher tno fk 2020 3 18 35 11 4 2外键约束 外键约束用于保证指定表的强制引用完整性 要与主键约束一起使用 这样就可以保证使用外键约束的列与所引用的主键约束列的一致性 与主键约束不同 每个表可以有多个外键约束 接下来以课程表为例 其表结构如下 2020 3 18 36 11 4 2外键约束 创建表时创建外键约束创建外键的语法结构如下 CONSTRAINTSFOREIGNKEY 外键列名 REFERENCES 主键列名 ONDELETENOACTION ONDELETECASCADE ONDELETESETNULL ONDELETESETDEFAULT 外键所在的表 参照关系主键所在的表 被参照关系 目标关系 父表 2020 3 18 37 11 4 2外键约束 例创建课程表时创建外键约束CREATETABLECOURSE CnoVARCHAR2 20 CnameVARCHAR2 50 NOTNULL CprecnoVARCHAR2 10 CcreditNUMBER 2 TnoVARCHAR2 6 CONSTRAINTScource con pkPRIMARYKEY Cno CONSTRAINTScource tno fkFOREIGNKEY Tno REFERENCESTeacher Tno ONDELETECASCADE 2020 3 18 38 11 4 2外键约束 在创建完表之后 使用ALTERTABLE添加外键约束 其语法结构如下 ALTERTABLEADDCONSTRAINTSFOREIGNKEY 列名 REFERENCES 列名 ONDELETENOACTION ONDELETECASCADE ONDELETESETNULL ONDELETESETDEFAULT 2020 3 18 39 11 4 2外键约束 例创建课程表之后 添加外键约束CREATETABLECOURSE CnoVARCHAR2 20 CnameVARCHAR2 50 NOTNULL CprecnoVARCHAR2 10 CcreditNUMBER 2 TnoVARCHAR2 6 CONSTRAINTScource con pkPRIMARYKEY Cno ALTERTABLEcourseADDCONSTRAINTScource tno fkFOREIGNKEY Tno REFERENCESteacher Tno ONDELETECASCADE 2020 3 18 40 ALTERTABLEcourseADDCONSTRAINTScource tno fkFOREIGNKEY Tno REFERENCESteacher Tno ONDELETECASCADE 2020 3 18 41 11 4 2外键约束 删除外键约束删除外键约束与删除主键约束的方法相同例删除课程表的外键约束cource tno fk 其代码如下 ALTERTABLEcourseDROPCONSTRAINTScource tno fk 2020 3 18 42 11 4 3唯一约束 唯一维束可以保证表中数据的唯一性 它与主键约束的区别是 在一张表中唯一约束可以有多个 而主键约束只能有一个 唯一约束允许出现空值 而主键约束不允许有空值存在 对于同一张表或同一列 不能同时设置主键约束和唯一约束 接下来以系表为例介绍唯一约束 其表结构在前面已经介绍过 2020 3 18 43 11 4 3唯一约束 创建表时创建唯一约束其语法结构如下 CONSTRAINTSUNIQUE 列名 例创建系表时创建唯一约束CREATETABLEDEPT DnoVARCHAR2 6 DnameVARCHAR2 50 NOTNULL DtelephoneVARCHAR2 12 DdeanVARCHAR2 10 NOTNULL DaddressVARCHAR2 50 CONSTRAINTSdept dno pkPRIMARYKEY dno CONSTRAINTSdept dname unkUNIQUE Dname 系名唯一 2020 3 18 44 11 4 3唯一约束 创建完表之后 使用ALTERTABLE添加唯一约束 其语法结构如下 ALTERTABLEADDCONSTRAINTSUNIQUE 列名 例创建完系表后 添加唯一约束CREATETABLEDEPT DnoVARCHAR2 6 DnameVARCHAR2 50 NOTNULL DtelephoneVARCHAR2 12 DdeanVARCHAR2 10 NOTNULL DaddressVARCHAR2 50 CONSTRAINTSdept dno pkPRIMARYKEY dno ALTERTABLEdeptADDCONSTRAINTSdept dname unqUNIQUE Dname 2020 3 18 45 11 4 3唯一约束 删除唯一约束删除唯一约束与删除主键约束的方法也相同 例我们使用下面语句将唯一约束dept dname unq删除 ALTERTABLEdeptDROPCONSTRAINTSdept dname unq 2020 3 18 46 11 4 4检查约束 检查约束是限制表中某一列或者某些列中可接受的数据值或者数据格式 如 限制学生表中的学生性别必须为 F 或 M 成绩表中的考试类型必须为 期中 或 期末 接下来以学生表为例 其表结构如下 2020 3 18 47 11 4 4检查约束 创建表时创建检查约束创建检查约束的语法结构如下 CONSTRAINTSCHECK 条件 例创建学生表时创建检查约束CREATETABLESTUDENT SnoVARCHAR2 20 SnameVARCHAR2 10 NOTNULL SsexVARCHAR2 6 NOTNULL SageNUMBER 3 SentrydateDATENOTNULL DnoVARCHAR2 6 CONSTRAINTSstudent sno pkPRIMARYKEY Sno CONSTRAINTSstudent ssex checkCHECK Ssexin F M CONSTRAINTSstudent dno fkFOREIGNKEY Dno REFERENCESdept Dno ONDELETECASCADE 2020 3 18 48 11 4 4检查约束 创建完表之后 使用ALTERTABLE添加检查约束其语法结构如下 ALTERTABLEADDCONSTRAINTSCHECK 条件 例添加检查约束CREATETABLESTUDENT SnoVARCHAR2 20 SnameVARCHAR2 10 NOTNULL SsexVARCHAR2 6 NOTNULL SageNUMBER 3 SentrydateDATENOTNULL DnoVARCHAR2 6 CONSTRAINTSstudent sno pkPRIMARYKEY Sno CONSTRAINTSstudent dno fkFOREIGNKEY Dno REFERENCESdept Dno ONDELETECASCADE ALTERTABLEstudentADDCONSTRAINTSstudent ssex checkCHECK Ssexin F M 2020 3 18 49 11 4 4检查约束 删除检查约束删除检查约束与删除其它约束的方法相同 例删除学生表STUDENT的检查约束student ssex check 其代码如下所示 ALTERTABLEstudentDROPCONSTRAINTSstudent ssex check 2020 3 18 50 11 4 5非空约束 创建表时创建非空约束非空约束的创建是在字段定义的后面直接写上NOTNULL 字段定义与NOTNULL之间用空格隔开 如入学时间非空SentrydateDATENOTNULL 使用ALTERTABLE设置非空约束其语法结构如下 ALTERTABLEMODIFYNOTNULL NULL 2020 3 18 51 11 4 5非空约束 例将学生表的入学日期字段Sentrydate从NOTNULL修改为NULL其代码如下 ALTERTABLEstudentMODIFYSentrydateNULL 2020 3 18 52 11 5使用企业管理器管理表 11 5 1创建表11 5 2修改表11 5 3删除表11 5 4定义约束 这部分内容需要演示 2020 3 18 53 11 5 1创建表 1 打开企业管理器 2020 3 18 54 启动服务 2020 3 18 55 查看端口portlist ini 2020 3 18 56 https localhost 5501 em 2020 3 18 57 2 进入创建数据表界面 2020 3 18 58 3 创建表 标准表 索引表 2020 3 18 59 11 5 2修改表 2020 3 18 60 11 5 3删除表 2020 3 18 61 11 5 4创建约束 2020 3 18 62 2020 3 18 63 2020 3 18 64 11 6分区表 11 6 1什么是分区表11 6 2创建分区表11 6 3维护分区11 6 4查看分区信息 2020 3 18 65 11 6 1什么是分区表 表可以进行分区 被分区的表在逻辑上仍然是一张完整的表 但是在物理层面上 不同分区的数据被存储到了不同的表空间上 对于数据库管理员 分区表既可集体管理 也可单独管理 这使得表管理更加灵活 对于应用程序 表是否分区是感受不到的 2020 3 18 66 11 6 1什么是分区表 分区表有以下特点 改善查询性能 查询数据时可以只检索相应的分区 不必将整张表全部扫描 提高了检索的速度 增强健壮性 当一个分区损坏时 其它分区仍然可以正常使用 改善维护性 当一个分区损坏时 只需维护该分区 其它分区不受影响 2020 3 18 67 11 6 2创建分区表 我们在此介绍四种分区方法 范围分区列表分区散列分区复合分区 2020 3 18 68 11 6 2创建分区表 法1 范围分区范围分区就是对数据表中的某个值的范围进行分区 根据某个值的范围 决定将该数据存储在哪个分区上 如根据序号分区 根据业务记录的创建日期进行分区等 需求描述 有一个物料交易表 表名 material transactions 该表将来可能有千万级的数据记录数 要求在建该表的时候使用分区表 这时候我们可以使用序号分区三个区 每个区中预计存储三千万的数据 也可以使用日期分区 如每五年的数据存储在一个分区上 2020 3 18 69 范围分区是按照数据的取值范围划分分区 其语法结构如下 PARTITIONBYRANGE 列名 PARTITIONVALUESLESSTHAN 上界值1 TABLESPACE表空间名1 PARTITIONVALUESLESSTHAN 上界值2 TABLESPACE表空间名2 PARTITIONVALUESLESSTHAN 上界值3 TABLESPACE表空间名n 2020 3 18 70 11 6 2创建分区表 例以学生表为例 按学号创建范围分区CREATETABLESTUDENT SnoVARCHAR2 20 SnameVARCHAR2 10 NOTNULL SsexVARCHAR2 6 SageNUMBER 3 SentrydateDATENOTNULL DnoVARCHAR2 6 CONSTRAINTSstudent sno pkPRIMARYKEY Sno CONSTRAINTSstudent ssex checkCHECK Ssexin F M CONSTRAINTSstudent dno fkFOREIGNKEY Dno REFERENCESdept Dno ONDELETECASCADE PARTITIONBYRANGE Sno PARTITIONstu part1VALUESLESSTHAN 20087740347 TABLESPACEstu space01 PARTITIONstu part2VALUESLESSTHAN 20087790536 TABLESPACEstu space02 2020 3 18 71 创建表空间 CREATETABLESPACEstu space01DATAFILE D ORCLTBS4 1 DBF SIZE50MEXTENTMANAGEMENTLOCALUNIFORMSIZE512KSEGMENTSPACEMANAGEMENTMANUAL CREATETABLESPACEstu space02DATAFILE D ORCLTBS4 2 DBF SIZE50MEXTENTMANAGEMENTLOCALUNIFORMSIZE512KSEGMENTSPACEMANAGEMENTMANUAL 2020 3 18 72 11 6 2创建分区表 法2 列表分区对于列中数值只有几种的表 我们可以使用列表分区 其语法结构如下 PARTITIONBYLIST 列名 PARTITIONVALUES 数值 TABLESPACE表空间名1 PARTITIONVALUES 数值 TABLESPACE表空间名2 PARTITIONVALUES 数值 TABLESPACE表空间名n 2020 3 18 73 11 6 2创建分区表 例以学生表为例 按性别创建列表分区CREATETABLESTUDENT5 SnoVARCHAR2 20 SnameVARCHAR2 10 NOTNULL SsexVARCHAR2 6 SageNUMBER 3 SentrydateDATENOTNULL DnoVARCHAR2 6 CONSTRAINTSstudent sno pk5PRIMARYKEY Sno CONSTRAINTSstudent ssex check5CHECK Ssexin F M CONSTRAINTSstudent dno fk5FOREIGNKEY Dno REFERENCESdept Dno ONDELETECASCADE PARTITIONBYLIST Ssex PARTITIONstu part1VALUES F TABLESPACEstu space01 PARTITIONstu part2VALUES M TABLESPACEstu space02 2020 3 18 74 2020 3 18 75 11 6 2创建分区表 法3 散列分区这类分区是在散列的基础上使用散列算法 将行记录均匀地散列到各分区上 其语法结构如下 PARTITIONBYHASH 列名 PARTITIONTABLESPACE表空间1 PARTITIONTABLESPACE表空间2 PARTITIONTABLESPACE表空间n 2020 3 18 76 11 6 2创建分区表 例以学生表为例 根据学号创建散列分区CREATETABLESTUDENT SnoVARCHAR2 20 SnameVARCHAR2 10 NOTNULL SsexVARCHAR2 6 SageNUMBER 3 SentrydateDATENOTNULL DnoVARCHAR2 6 CONSTRAINTSstudent sno pkPRIMARYKEY Sno CONSTRAINTSstudent ssex checkCHECK Ssexin F M CONSTRAINTSstudent dno fkFOREIGNKEY Dno REFERENCESdept Dno ONDELETECASCADE PARTITIONBYHASH Sno PARTITIONstu part1TABLESPACEstu space01 PARTITIONstu part2TABLESPACEstu space02 PARTITIONstu part3TABLESPACEstu space03 2020 3 18 77 法4 复合分区有时候我们需要根据范围分区后 每个分区内的数据再散列地分布在几个表空间中 这样我们就要使用复合分区 复合分区是先使用范围分区 然后在每个分区内再使用散列分区的一种分区方法 如将物料交易的记录按时间分区 然后每个分区中的数据分三个子分区 将数据散列地存储在三个指定的表空间中 2020 3 18 78 11 6 3维护分区 维护分区包括 1 添加分区2 删除分区3 截断分区4 合并分区5 拆分分区6 重命名分区 2020 3 18 79 1 添加分区 添加分区使用ALTERTABLE的ADD语句 其语法结构如下 ALTERTABLEADDPARTITIONVALUES 数值 LESSTHAN 数值 若为范围分区 则VALUE之后选择LESSTHAN 数值 添加的分区界限应该高于最后一个分区界限 若为列表分区 则VALUE之后选择 数值 2020 3 18 80 添加分区 例为学生表STUDENT添加新分区stu part3 该分区要求学号小于 2008784347 添加分区的代码如下所示 ALTERTABLESTUDENTADDPARTITIONstu part3VALUESLESSTHAN 2008784347 2020 3 18 81 2 删除分区 删除分区使用ALTERTABLE的DROP语句 其语法结构如下 ALTERTABLEDROPPARTITION 例删除学生表STUDENT的分区stu part3 其代码如下所示 ALTERTABLESTUDENTDROPPARTITIONstu part3 2020 3 18 82 3 截断分区 截断分区指的是清空指定分区的值 但是不删除该分区 其语法结构如下 ALTERTABLETRUNCATEPARTITION 例清空学生表的分区stu part2中的数据 其代码如下所示 ALTERTABLESTUDENTTRUNCATEPARTITIONstu part2 2020 3 18 83 4 合并分区 合并分区是将相邻的分区合并成一个分区 合并后的分区以较高分区命名 其语法结构如下 ALTERTABLEMERGEPARTITIONS INTOPARTITION 例将学生表STUDENT的分区stu part1和stu part2合并 合并的代码如下 ALTERTABLESTUDENTMERGEPARTITIONSstu part1 stu part2INTOPARTITIONstu part2 2020 3 18 84 5 拆分分区 拆分分区是将一个分区拆分为两个新分区 其语法结构如下 ALTERTABLESPLITPARTITIONAT 数值 INTO PARTITION PARTITION 例将学生表STUDENT的分区stu part2在数值 20087740210 处划分为两个新分区stu part1和stu part2 其代码如下 ALTERTABLESTUDENTSPLITPARTITIONstu part2AT 20087740210 INTO PARTITIONstu part1 PARTITIONstu part2 2020 3 18 85 6 重命名表分区 重命名分区是为分区重新命名 其语法结构如下 ALTERTABLERENAMEPARTITIONTO 例将学生表STUDENT的分区stu part2命名为new stu part2 其代码如下所示 ALTERTABLESTUDENTRENAMEPARTITIONs