第7章表的创建和管理

第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页SQLCREATETABLESys_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，要求创建的时候指明表空间和数据块管理参数。代码如下：SQLCREATETABLESys_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，代码如下。SQLALTERTABLESys_User2ADDUserTelVARCHAR2(15);表已更改。【例7-4】修改表Sys_User中新添加列UserTel（用户联系方式），将数据类型长度改为20，并且设置为不可为空，代码如下。SQLALTERTA