第7章表的创建和管理.ppt

第1页 第7章表的创建和管理 表是最常用的数据库对象之一 也是最基本和最重要的数据库对象之一 由于表是存储数据库数据的主要手段 因此对表的管理也是非常重要的 表的管理涉及表的结构 表的创建 修改 删除与设置完整性约束等操作 以及如何向表中添加数据 更新数据和删除数据 在本章中 将对表和表中数据的管理操作进行详细介绍 本章的学习目标 了解表的相关概念 掌握如何使用命令行方式创建表 掌握修改表和删除表的操作 掌握表中约束的定义和管理 掌握使用OEM工具创建表和管理表的方法 掌握如何管理表中的数据 第2页 7 1表概述 表是数据库中最基本的对象 用户的数据在数据库中是以表的形式存储的 数据库中每一个表都被一个用户模式所拥有 因此表是一种典型的模式对象 7 1 1表的构成 表是组成数据库的基本元素 主要用来存储系统或用户的数据 表中的数据是按照行和列的二维结构组成 每一行用来保存关系数据库关系的元组 也称数据行或记录 每一列用来保存关系的属性 也称字段 行的顺序一般是按照写入的先后顺序存放 各列的顺序是根据创建表时定义的先后顺序存放 每个列具有列名 列数据类型 列长度 约束条件 默认值等 这些内容在创建表时确定 第3页 在逻辑结构上 表位于某个表空间 当创建一个表时 将同时创建一个段 专门用来存放表中的数据 在物理结构上 表中的数据都存放在数据块中 因而在数据块中存放的是一行的数据 表的结构主要由列组成 包括列名称 数据类型 数据长度 比例 针对数值型的数据 约束等信息 在Oracle数据库中根据不同用途有多种类型的表 如堆表 临时表 索引组织表和分区表等 1 堆表 HeapOrganizedTable 堆 是一个经典的数据结构 它的存储分配原则是随机的 具体到数据表中 也就是说表中插入的每一行数据的空间分配是随机的 堆表中的数据数据按堆组织 以无序的方式存放在单独的表段中 这种表是最基础 最常用的数据库表类型 是默认情况下创建的表 第4页 2 临时表 TemporaryTable 临时表是一种特殊类型的表 表中的数据并不永久保存 而是一些临时数据 这些临时数据只在当前事务或当前会话中有效 当事务或会话结束时 这些临时数据将被全部删除 3 索引组织表 IndexOrganizedTable 索引组织表是一种特殊类型的表 它把表中的数据和表的索引存放在同一个段中 并以B 树的方式组织在一起 从而加快表的查询速度 在索引组织表中必须有一个主键 表中的数据按照主键进行排序 在B 树的叶块中同时存储主键列和非主键列 4 分区表为了提高大型表的访问效率 Oracle提供了一种分区技术 利用这种技术可以把表 索引等数据库对象中的数据分割成小的单位 分别存放在一个个单独的段中 用户对表的访问变转化为对较小段的访问 第5页 Oracle数据库中表和列的命名规则如下 长度30个字节之内 必须以字母开头 由字母 数字 组成 不能使用保留字 非法的命名 可以用 括起来 一个表内列名要唯一 表名在名称空间内必须唯一 对数据库设计人员 开发和管理人员 在创建表之前都要做好表的设计工作 包括如下 为表中各个字段选择合适的数据类型 确定表的完整性约束类型 根据需求决定创建的表类型 第6页 7 1 2表的数据类型 Oracle11g的数据类型很丰富 包含标量 SCALAR 复合 COMPOSITE 引用 REFFERENCE 和LOB4类数据类型 合法的标量类型与数据库的列所使用的类型相同 此外它还有一些扩展 Oracle11g提供的23种数据类型中 选择最常用的 分为字符型 数值型 日期型和其他数据类型4类进行讲解 表7 1列出了较为常用的数据类型 不同的数据类型可以为表的每个列限定取值范围 实现数据的域完整性 第7页 第8页 7 1 3表的约束类型 Oracle的表定义支持以下完整性约束 1 实体完整性实体完整性将定义为特定表的唯一实体 要求主属性非空 可以通过设置主键 PrimaryKey 唯一值约束 Unique 和非空值约束 NOTNULL 等多种方法实现 2 参照完整性参照完整性是用来维护相关数据表之间数据一致性的手段 主要使用外键 ForeignKey 来实现 3 用户自定义完整性用户自定义完整性由用户根据实际应用中的需要自行定义 例如 要求某列的值能取空值 某一列的值在表中是唯一的 某一列的值要在某个范围中等 可以使用数据类型 DataType 默认值 Default 检查约束 Check 空或非空 NULL NOTNULL 等多种约束方法实现 稍微复杂一点的也可以使用触发器 Trigger 存储过程 Procedure 和函数 Function 等来实现 第9页 7 2创建表 创建表的方法有基于命令行的创建方法和使用OEM工具创建两种 本节重点介绍如何使用这两种方法完成普通表的创建 7 2 1使用命令行创建表 使用命令行创建表用的关键字是CREATETABLE 其简单的语法格式如下 CREATETABLEtable name column nametype CONSTRAINTconstraint defDEFAULTdefault exp column nametype CONSTRAINTconstraint defDEFAULTdefault exp 第10页 语法中的各选项说明 table name指定要分配给该表的名称 column name指定要分配给某个列的名称 type指定某个列的类型 constraint def指定对某个列的约束 default exp指定一个表达式 用来为某个列赋予默认值 ONCOMMIT控制临时表中行的有效期 DELETE说明这些行在事务的末尾要被删除 PRESERVE说明这些行在用户会话的末尾要被删除 如果对临时表没有指定ONCOMMIT选项 那么默认值是DELETE 例7 1 一般数据库应用程序中都会有一个用户表 用来记录应用程序的用户的信息 按照表7 2所示的结构创建用户表Sys User 代码如下 第11页 SQL CREATETABLESys User2 UserIDNumberPrimaryKey 3UserNameVarchar2 20 NOTNULL 4UserTypeNumber 1 5UserPwdVarchar2 40 6RemarkVarchar2 200 7 表已创建 第12页 表创建成功后 可以通过DESCRIBE命令查看表的描述 如图7 1所示 图7 1表Sys User的描述 7 2 2创建表的相关子句 当创建表时 Oracle会自动在用户默认表空间中为新建的表创建一个数据段 以便为表提供存储数据的空间 除此之外 用户也可以在创建表时指定所处的表空间 当然用户还可以在创建表的同时设置数据块管理参数 下面以示例7 2说明创建表的其他相关子句 第13页 例7 2 重新创建表Sys User 要求创建的时候指明表空间和数据块管理参数 代码如下 SQL CREATETABLESys User2 UserIDNumberPrimaryKey 3UserNameVarchar2 20 NOTNULL 4UserTypeNumber 1 5UserPwdVarchar2 40 6RemarkVarchar2 200 7 8TABLESPACETBSONE9PCTFREE2010PCTUSED4011INITRANS1012MAXTRANS200 表已创建 第14页 例7 2中各参数的相关说明如下 TABLESPACE 指定将表创建在哪个表空间上 如果不指定该子句 用户将在自己的默认表空间上创建表 PCTFREE与PCTUSED 用于控制数据块中空闲空间的使用方法 PCTFREE和PCTUSED的值必须小于或等于100 PCTFREE和PCTUSED之和越接近100 数据块的利用率越高 如果执行UPDATE操作比较多 可以将PCTFREE设置的尽量大一点 推荐设置PCTFREE为20 PCTUSED为40 如果在一个表上执行INSERT和DELETE操作比较多 并且UPDATE操作不会增加记录的大小时 可以将PCTFREE设置的比较小 推荐设置PCTFREE为5 PCTUSED为60 INITRANS与MAXTRANS 用于控制一个数据块上的并发事务数量 其中INITRANS用于指定初始的事务数量 MAXTRANS用于指定最大的并发事务数量 除了上述子句以外 还有一些子句 第15页 CACHE 用于指定将表中的数量放在数据库高速缓存中 并保留一段时间 如果在创建表是指定了该参数 那么在用户第一次访问表中的数据时 这个表将整个被读到数据库高速缓存中 并保留较长一段时间 这样用户以后再访问该表时 可直接访问数据库高速缓存中的数据 从而提高访问效率 LOGGING 在默认情况下 用户在表上执行DDL和DML命令时 服务器进程都会产生重做日志 如果不希望产生重做日志 可以在创建表时指定NOLOGGING子句 使用了NOLOGGING子句可以减少处理时间 由于不写重做日志 也可节约了重做日志的存储空间 同时在以并行方式向表中写入大量数据时提高了效率 但是 由于没有重做日志 当表被破坏时 将无法进行恢复 COMPRESS 如果使用了该子句 则一个数据块中两行完全相同的数据将被压缩为一行 并存储在数据块的开始 在数据块中本应存储这两行数据的地方只存储该行数据的应用 使用表的压缩功能可以减少表所占用的存储空间和数据库高速缓存空间 并且可以提高查询速度 第16页 7 2 3使用OEM工具创建表 使用管理工具OEM可以很直观的创建表 具体步骤如下 1 首先启动并登录到OEM 选择 方案 页面 在该页面的 数据库对象 一栏可以看到用户可以管理的所有数据库对象 如表 视图 索引等 如图7 2所示 在这里选择 表 进入表管理的页面 图7 2OEM管理界面 第17页 2 在如图7 3所示的管理表页面 单击创建按钮 图7 3OEM管理界面 第18页 3 单击创建按钮后进入到创建表的第一步 选择创建表的类型 如图7 4所示 表的类型可以是标准表 堆表 临时表和索引表 图7 4创建表的组织形式 第19页 4 选择标准表 单击 继续 按钮 进入如图7 5所示的 表一般信息 页面 输入新建表的名称 在 表空间 文本框输入新表的存入位置 在表的下半部分输入表的列名称 数据类型和默认值等 图7 5创建表界面 第20页 5 输入完成后 单击 确定 按钮 返回表管理页面 新建的表已经出现在列表中 如果列表中的表对象太多 可以通过搜索方式查找需要的表 用户也可单击 显示SQL 按钮 查看系统生成的SQL语句 需要注意的是 在创建表时 如果单击 约束条件 超链接进入 表约束条件 页面 为表添加约束条件 同样 在存储选择页面 用户可以为表设置存储参数 7 3修改表 对于创建好的表如果需要修改 使用ALTERTABLE语句进行修改 对表的修改主要涉及表的结构的修改 本节主要介绍修改表的方法 第21页 7 3 1修改表的结构 修改表的基本语法如下 ALTERTABLEtable nameADDcolumn name MODIFYcolumn name DROPCOLUMNcolumn name语法中的各选项说明 ADD 向表中添加列 MODIFY 修改表中已经存在的列的信息 DROPCOLUMN 删除表中的列 在删除表中的列时如果加上CASCADECONSTRAINTS表示把与该列有关的约束也一并删除 第22页 例7 3 例7 1创建的表Sys User中添加列 UserTel 用户联系方式 字段类型varchar2 代码如下 SQL ALTERTABLESys User2ADDUserTelVARCHAR2 15 表已更改 例7 4 修改表Sys User中新添加列UserTel 用户联系方式 将数据类型长度改为20 并且设置为不可为空 代码如下 SQL ALTERTABLESys User2MODIFYUserTelVARCHAR2 20 NOTNULL 表已更改 第23页 例7 5 删除表Sys User中新修改的列UserTel 用户联系方式 代码如下 SQL ALTERTABLESys User2DROPCOLUMNUserTel 表已更改 注意 如果要在条语句中删除多个列 则需要将删除的字段名放在括号中 相互之间用逗号隔开 并且不能使用COLUMN关键字 当在数据库使用高峰期执行删除字段的操作时 系统会因为删除列操作占用资源而降低响应率 为此 可以通过在ALTERTABLE语句中使用SETUNUSED子句可以将列设置为不可用 从用户角度看 被标记为不可用状态的列与被删除的列没有区别 都无法通过查询获取其数据 并且还可以添加与UNUSED状态的列同名的字段 但是 UNUSED的列在物理上仍然保存在数据库中 它所占用的存储空间并没有被释放 第24页 例7 6 设置表Sys User中Remark列为不可用 然后再删除该表中所有不可用的列 代码如下 SQL ALTERTABLESys User2SETUNUSED RemarK 表已更改 SQL ALTERTABLESys User2DROPUNUSEDCOLUMNS 表已更改 7 3 2重命名表 在创建表后 如果想要修改表的名称 可以对表进行重命名 例7 7 将表Sys User重命名为用户信息 SQL ALTERTABLESys User2RENAMETO用户信息 表已更改 第25页 需要注意的是 对表进行重命名要谨慎 虽然Oracle可以自动更新数据字典中的外键 约束定义 以及表关系 但是它还不能够更新数据库中的存储代码 客户应用 以及依赖于该对象的其他对象 7 3 3删除表 用户经常需要删除一些不需要的表 可使用删除表语句DROPTABLE来完成 语法如下 DROPTABLEtable name 例7 8 将用户信息表删除 SQL DROPTABLE用户信息 表已更改 第26页 7 2 4使用OEM工具修改 删除表 使用OEM可以对表进行修改操作 包括修改表名 添加列 删除列 修改列属性等 首先搜索要要修改的表 单击 编辑 按钮 打开编辑表页面 如图7 6所示 图7 6修改表界面 第27页 在修改表页面中 可以直接在界面中输入新的表名 列名 以及列的数据类型 大小和比例等信息 单击 删除 按钮可以删除选定的列 单击 插入 可以添加新的列 如果需要删除表 则需选中要删除的表 如图7 7所示 图7 7删除表界面 单击 使用选项删除 按钮 打开删除选项窗口 选择删除选项 可以选择删除表定义 其中所有数据和从属对象 仅删除数据 仅删除不支持回退的数据 在这里可以选择删除表定义选项 然后单击 是 即可删除指定的表 第28页 7 4完整性约束 数据库不仅仅存储数据 它也必须保证所保存的数据的正确性和相容性 也就是数据完整性 数据完整性是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入 输出造成无效操作或错误信息而提出的 约束是在数据库中保证数据库里表中数据完整性的手段 本节重点介绍数据库完整性的概念及其在Oracle11g中的实现方法 数据库的完整性一般分为四类 实体完整性 域完整性 参照完整性和用户定义的完整性 第29页 实体完整性 要求表中的每一行在表中是唯一的实体 既不能为空也不能取重复的值 域完整性 指数据库表中的字段必须满足某种特定的数据类型或约束 参照完整性 保证数据库中相关联的表里面数据的正确性 根据约束的作用域 约束可以分为表级约束和列级约束两种 列级约束是列定义的一部分 只能够应用在一个列上 而表级的约束定义在独立的列上 它可以应用于一个表中的多个列 在Oracle系统中定义约束时 可以通过CONSTRAINT关键字为约束命名 如果用户没有为约束指定名称 Oracle将自动为约束建立默认的名称 第30页 在Oracle中 根据不同的用途提供了多种约束 分别为 主键约束 PRIMARYKEY 主键约束在每个数据表中有且仅有一个 如果一个主键约束由多个列组成 通常把由多个列组成的主键叫做复合主键 主键约束可以保证主键列的数据没有重复值且值不为空 即能够唯一地标识表中的每条记录 该约束主要实现实体完整性 外键约束 FOREIGNKEY 外键约束保证了数据库的参照完整性 它主要用作把一个表中的数据和另一个表中的数据进行关联 唯一约束 UNIQUE 唯一约束和主键约束都是设置表中的列不能取重复值 与主键约束不同的是 一个表可以有多个唯一约束 非空约束 NOTNULL 非空约束是用来约束表中的列不允许取空值的 NOTNULL约束只能在列级别上定义 在一个表中可以定义多个NOTNULL约束 第31页 检查约束 CHECK CHECK约束通过检查输入到表中的数据值来维护域的完整性 只有符合输入条件的数据才允许输入到表中 默认值约束 DEFAULT 用于对指定的列设置默认值 其中 唯一约束 非空约束 检查约束和默认值约束可以实现数据库的域完整性 对约束的定义既可以创建表的同时可以指定相应的约束 如例7 9 也可以通过修改表的结构添加约束 例7 9 重新创建用户表Sys User 要求UserID是主键 UserName列只能取唯一值 不得重复 UserType字段取值大于0小于3 UserPwd的默认值为111111 代码如下 第32页 SQL CREATETABLESys User2 UserIDNumberPrimaryKey 3UserNameVarchar2 20 NOTNULLUNIQUE 4UserTypeNumber 1 CHECK UserType 0ANDUserType 3 5UserPwdVarchar2 40 DEFAULT 111111 6RemarkVarchar2 200 7 表已创建 以高校选课管理系统为例 经分析得知 学生和课程的关系是多对多关系 需要创建学生表 课程表和选课表 学生表的结构如下表所示 第33页 创建学生表的代码如下 SQL CREATETABLEStudents2 Student IDchar 11 3SnameVarchar2 8 4SsexVarchar2 2 5Sbirthdate 6NationalityVarchar2 40 7Dept IDchar 3 8Class IDchar 5 9 表已创建 第34页 创建课程表的代码如下 SQL CREATETABLECourses2 Course IDVarchar2 4 3CnameVarchar2 20 4CreditsNUMBER 表已创建 第35页 创建选课表的代码如下 SQL CREATETABLESC2 Student IDVarchar2 11 3Course IDVarchar2 4 4GradeNUMBER 5CONSTRAINTfk sidFOREIGNKEY Student ID REFERENCESStudents Student ID 6ONDELETECASCADE 表已创建 第36页 1 主键约束使用ALTERTABLE语句设置主键约束的语法格式如下 ALTERTABLEtable nameADDCONSTRAINTconstraint namePRIMARYKEY column name 例7 10 为学生表添加主键约束 SQL ALTERTABLEStudents2ADDCONSTRAINTpk sidPRIMARYKEY Student ID 表已更改 第37页 2 唯一约束使用ALTERTABLE语句设置唯一约束的语法格式如下 ALTERTABLEtable nameADDCONSTRAINTSconstraint nameUNIQUE column name 例7 11 为课程表的课程名称设置唯一值约束 SQL ALTERTABLECourses2ADDCONSTRAINTuq cnUNIQUE Course name 表已更改 第38页 3 非空约束使用ALTERTABLE语句设置非空约束的语法格式如下 ALTERTABLEtable nameMODIFYcolumn nameNOTNULL 例7 12 为学生表的学生姓名设置非空约束 SQL ALTERTABLEStudents2MODIFYSnameNOTNULL 表已更改 如果要取消某个列非空的约束 直接使用MODIFY语句把该列的非空约束写成NULL即可 第39页 4 检查约束使用ALTERTABLE语句设置检查约束的语法格式如下 ALTERTABLEtable nameADDCONSTRAINTSconstraint nameCHECK condition 例7 13 选修表的成绩列取值要求在0 100之间 SQL ALTERTABLESC2ADDCONSTRAINTchk gradeCHECK Grade 0andGrade 100 表已更改 第40页 5 外键约束外键约束可以保证使用外键约束的数据库列于所引用的主键约束的数据列一致 外键约束在一个数据表中可以有多个 外键约束是建立在两张表中的约束 添加外键约束的语法格式如下 ALTERTABLEtable nameADDCONSTRAINTSconstraint nameFOREIGNKEY column name REFERENCEtable name column name ONDELETECASCADE REFERENCE 要引用的表名 列名 ONDELETECASCADE 级联删除 当被参照表的主键字段被删除时 参照表中的外键所对应的字段也同时被删除 例7 14 选修表中的学生要参考学生表的信息 课程号需要参考课程表的信息 由于在创建选修表时已经设置了学号为外键 此处为选修表的课程号设置外键 SQL ALTERTABLESC2ADDCONSTRAINTfk cidFOREIGNKEY Course ID REFERENCESCourses Course ID 3ONDELETECASCADE 表已更改 第41页 6 删除约束删除约束的语法格式 ALTERTABLEtable nameDROPCONSTRAINTSconstraint name 例7 15 删除例7 11创建的约束 SQL ALTERTABLECourses2DROPCONSTRAINTuq cn 表已更改 7 5表中数据的操作 一个表创建以后 其中并不包含任何记录 需要向表中输入数据 本节重点介绍如何向表中添加数据 更新数据以及删除数据 第42页 7 5 1插入数据 使用INSERT语句可向指定表中插入数据 INSERT语法的基本结构如下 INSERTINTO column name1 column name2 column namen VALUES values1 values2 valuesn 其中 column name1 column name2 column namen必须是指定表名中定义的列 而且必须和VALUES子句中的值values1 values2 valuesn一一对应 且数据类型相同 例7 16 向表Sys User中插入数据 SQL INSERTINTOSys User UserID UserName UserType UserPwd Remark 2VALUES 1 Admin 1 Admin null 已创建1行 向表的所有列添加数据时 也可省略INSERTINTO子句后的列表清单 但需要注意的是 必须根据表中定义的列的顺序 为所有列提供数据 第43页 例7 17 向表Sys User中插入数据 SQL INSERTINTOSys User2VALUES 2 Zhang 2 Zhang null 已创建1行 另外 有一种简单的插入多行数据的方法 这种方法是使用SELECT语句查询出的结果代替VALUES子句 这种方法的语法结构如下 INSERTINTO column name1 column name2 column namen SELECTcolumn name n FROMtable nameWHEREsearch conditions 第44页 例7 18 向Courses表输入数据 创建 Courses 表的一个副本Courses1 将Courses表的全部数据添加到Courses1表中 SQL INSERTINTOCoursesVALUES 0001 大学语文 4 SQL INSERTINTOCoursesVALUES 0002 高