第7章表的创建和管理

第7章表的创建和管理

表是最常用的数据库对象之一，也是最基本和最重要的数据库对象之一。由于表是存储数据库数据的主要手段，因此对表的管理也是非常重要的。表的管理涉及表的结构、表的创建、修改、删除与设置完整性约束等操作，以及如何向表中添加数据、更新数据和删除数据。在本章中，将对表和表中数据的管理操作进行详细介绍。本章的学习目标：

●了解表的相关概念

●掌握如何使用命令行方式创建表

●掌握修改表和删除表的操作

●掌握表中约束的定义和管理

●掌握使用OEM工具创建表和管理表的方法

●掌握如何管理表中的数据1第一页，共四十八页。7.1表概述

表是数据库中最基本的对象，用户的数据在数据库中是以表的形式存储的。数据库中每一个表都被一个用户模式所拥有，因此表是一种典型的模式对象。7.1.1表的构成

表是组成数据库的基本元素，主要用来存储系统或用户的数据。表中的数据是按照行和列的二维结构组成。每一行用来保存关系数据库关系的元组，也称数据行或记录。每一列用来保存关系的属性，也称字段。行的顺序一般是按照写入的先后顺序存放，各列的顺序是根据创建表时定义的先后顺序存放。每个列具有列名、列数据类型、列长度、约束条件、默认值等，这些内容在创建表时确定。2第二页，共四十八页。

在逻辑结构上，表位于某个表空间。当创建一个表时，将同时创建一个段，专门用来存放表中的数据。在物理结构上，表中的数据都存放在数据块中，因而在数据块中存放的是一行的数据。表的结构主要由列组成，包括列名称、数据类型、数据长度、比例（针对数值型的数据）、约束等信息。在Oracle数据库中根据不同用途有多种类型的表，如堆表、临时表、索引组织表和分区表等。1.堆表(HeapOrganizedTable)

“堆”是一个经典的数据结构，它的存储分配原则是随机的。具体到数据表中，也就是说表中插入的每一行数据的空间分配是随机的。堆表中的数据数据按堆组织，以无序的方式存放在单独的表段中。这种表是最基础、最常用的数据库表类型，是默认情况下创建的表。

3第三页，共四十八页。2.临时表(TemporaryTable)临时表是一种特殊类型的表，表中的数据并不永久保存，而是一些临时数据。这些临时数据只在当前事务或当前会话中有效，当事务或会话结束时，这些临时数据将被全部删除。

3.索引组织表(IndexOrganizedTable)

索引组织表是一种特殊类型的表，它把表中的数据和表的索引存放在同一个段中，并以B*树的方式组织在一起，从而加快表的查询速度。在索引组织表中必须有一个主键，表中的数据按照主键进行排序，在B*树的叶块中同时存储主键列和非主键列。4.分区表 为了提高大型表的访问效率，Oracle提供了一种分区技术，利用这种技术可以把表、索引等数据库对象中的数据分割成小的单位，分别存放在一个个单独的段中，用户对表的访问变转化为对较小段的访问。4第四页，共四十八页。Oracle数据库中表和列的命名规则如下：●长度30个字节之内。●必须以字母开头。●由字母、数字、_、#、$组成。●不能使用保留字。●非法的命名，可以用“”括起来。●一个表内列名要唯一。●表名在名称空间内必须唯一。对数据库设计人员、开发和管理人员，在创建表之前都要做好表的设计工作，包括如下：●为表中各个字段选择合适的数据类型；●确定表的完整性约束类型●根据需求决定创建的表类型5第五页，共四十八页。7.1.2表的数据类型

Oracle11g的数据类型很丰富，包含标量（SCALAR）、复合（COMPOSITE）、引用（REFFERENCE）和LOB4类数据类型。合法的标量类型与数据库的列所使用的类型相同，此外它还有一些扩展。Oracle11g提供的23种数据类型中，选择最常用的，分为字符型、数值型、日期型和其他数据类型4类进行讲解。表7-1列出了较为常用的数据类型。不同的数据类型可以为表的每个列限定取值范围，实现数据的域完整性。6第六页，共四十八页。7第七页，共四十八页。7.1.3表的约束类型Oracle的表定义支持以下完整性约束。1.实体完整性实体完整性将定义为特定表的唯一实体，要求主属性非空。可以通过设置主键（PrimaryKey）、唯一值约束（Unique）和非空值约束（NOTNULL）等多种方法实现。2.参照完整性参照完整性是用来维护相关数据表之间数据一致性的手段，主要使用外键(ForeignKey)来实现。3.用户自定义完整性用户自定义完整性由用户根据实际应用中的需要自行定义。例如：要求某列的值能取空值；某一列的值在表中是唯一的；某一列的值要在某个范围中等。可以使用数据类型（DataType）、默认值(Default)、检查约束（Check）、空或非空（NULL|NOTNULL）等多种约束方法实现。稍微复杂一点的也可以使用触发器（Trigger）、存储过程（Procedure）和函数（Function）等来实现。8第八页，共四十八页。7.2创建表创建表的方法有基于命令行的创建方法和使用OEM工具创建两种。本节重点介绍如何使用这两种方法完成普通表的创建。7.2.1使用命令行创建表

使用命令行创建表用的关键字是CREATETABLE，其简单的语法格式如下：CREATE

TABLEtable_name(column_nametype[CONSTRAINTconstraint_defDEFAULTdefault_exp][,column_nametype[CONSTRAINTconstraint_defDEFAULTdefault_exp]...])9第九页，共四十八页。语法中的各选项说明：●table_name指定要分配给该表的名称。●column_name指定要分配给某个列的名称。●type指定某个列的类型。●constraint_def指定对某个列的约束。●default_exp指定一个表达式，用来为某个列赋予默认值。●ONCOMMIT控制临时表中行的有效期。DELETE说明这些行在事务的末尾要被删除。PRESERVE说明这些行在用户会话的末尾要被删除。如果对临时表没有指定ONCOMMIT选项，那么默认值是DELETE。【例7-1】一般数据库应用程序中都会有一个用户表，用来记录应用程序的用户的信息。按照表7-2所示的结构创建用户表Sys_User，代码如下。10第十页，共四十八页。SQL>CREATETABLESys_User2(UserIDNumberPrimaryKey,3UserNameVarchar2(20)NOTNULL,4UserTypeNumber(1),5UserPwdVarchar2(40),6RemarkVarchar2(200)7);表已创建。11第十一页，共四十八页。

表创建成功后，可以通过DESCRIBE命令查看表的描述，如图7-1所示。图7-1表Sys_User的描述7.2.2创建表的相关子句

当创建表时，Oracle会自动在用户默认表空间中为新建的表创建一个数据段，以便为表提供存储数据的空间。除此之外，用户也可以在创建表时指定所处的表空间，当然用户还可以在创建表的同时设置数据块管理参数。下面以示例7-2说明创建表的其他相关子句。12第十二页，共四十八页。【例7-2】重新创建表Sys_User，要求创建的时候指明表空间和数据块管理参数。代码如下：SQL>CREATETABLESys_User2(UserIDNumberPrimaryKey,3UserNameVarchar2(20)NOTNULL,4UserTypeNumber(1),5UserPwdVarchar2(40),6RemarkVarchar2(200)7);8TABLESPACETBSONE9PCTFREE2010PCTUSED4011INITRANS1012MAXTRANS200;表已创建。13第十三页，共四十八页。例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用于指定最大的并发事务数量。除了上述子句以外，还有一些子句。14第十四页，共四十八页。

●CACHE：用于指定将表中的数量放在数据库高速缓存中，并保留一段时间。如果在创建表是指定了该参数，那么在用户第一次访问表中的数据时，这个表将整个被读到数据库高速缓存中，并保留较长一段时间，这样用户以后再访问该表时，可直接访问数据库高速缓存中的数据，从而提高访问效率。●LOGGING：在默认情况下，用户在表上执行DDL和DML命令时，服务器进程都会产生重做日志。如果不希望产生重做日志，可以在创建表时指定NOLOGGING子句。使用了NOLOGGING子句可以减少处理时间，由于不写重做日志，也可节约了重做日志的存储空间，同时在以并行方式向表中写入大量数据时提高了效率。但是，由于没有重做日志，当表被破坏时，将无法进行恢复。●COMPRESS：如果使用了该子句，则一个数据块中两行完全相同的数据将被压缩为一行，并存储在数据块的开始，在数据块中本应存储这两行数据的地方只存储该行数据的应用。使用表的压缩功能可以减少表所占用的存储空间和数据库高速缓存空间，并且可以提高查询速度。15第十五页，共四十八页。7.2.3使用OEM工具创建表

使用管理工具OEM可以很直观的创建表，具体步骤如下：(1)首先启动并登录到OEM，选择“方案”页面，在该页面的“数据库对象”一栏可以看到用户可以管理的所有数据库对象，如表、视图、索引等，如图7-2所示。在这里选择“表”，进入表管理的页面。图7-2OEM管理界面16第十六页，共四十八页。(2)在如图7-3所示的管理表页面，单击创建按钮。图7-3OEM管理界面17第十七页，共四十八页。

(3)单击创建按钮后进入到创建表的第一步，选择创建表的类型，如图7-4所示。表的类型可以是标准表（堆表）、临时表和索引表。图7-4创建表的组织形式18第十八页，共四十八页。

(4)选择标准表，单击“继续”按钮，进入如图7-5所示的“表一般信息”页面。输入新建表的名称，在“表空间”文本框输入新表的存入位置。在表的下半部分输入表的列名称、数据类型和默认值等。图7-5创建表界面19第十九页，共四十八页。

(5)输入完成后，单击“确定”按钮，返回表管理页面，新建的表已经出现在列表中。如果列表中的表对象太多，可以通过搜索方式查找需要的表。用户也可单击“显示SQL”按钮，查看系统生成的SQL语句。需要注意的是，在创建表时，如果单击“约束条件”超链接进入“表约束条件”页面，为表添加约束条件。同样，在存储选择页面，用户可以为表设置存储参数。7.3修改表

对于创建好的表如果需要修改，使用ALTERTABLE语句进行修改。对表的修改主要涉及表的结构的修改。本节主要介绍修改表的方法。20第二十页，共四十八页。7.3.1修改表的结构修改表的基本语法如下：ALTERTABLEtable_nameADDcolumn_name|MODIFYcolumn_name|DROPCOLUMNcolumn_name语法中的各选项说明：●ADD：向表中添加列。●MODIFY：修改表中已经存在的列的信息。●DROPCOLUMN：删除表中的列，在删除表中的列时如果加上CASCADECONSTRAINTS表示把与该列有关的约束也一并删除。21第二十一页，共四十八页。【例7-3】例7-1创建的表Sys_User中添加列，UserTel（用户联系方式）字段类型varchar2，代码如下。SQL>ALTERTABLESys_User2ADDUserTelVARCHAR2(15);表已更改。

【例7-4】修改表Sys_User中新添加列UserTel（用户联系方式），将数据类型长度改为20，并且设置为不可为空，代码如下。SQL>ALTERTABLESys_User2MODIFYUserTelVARCHAR2(20)NOTNULL;表已更改。22第二十二页，共四十八页。【例7-5】删除表Sys_User中新修改的列UserTel（用户联系方式），代码如下。SQL>ALTERTABLESys_User2DROPCOLUMNUserTel;表已更改。注意：如果要在条语句中删除多个列，则需要将删除的字段名放在括号中，相互之间用逗号隔开，并且不能使用COLUMN关键字。当在数据库使用高峰期执行删除字段的操作时，系统会因为删除列操作占用资源而降低响应率。为此，可以通过在ALTERTABLE语句中使用SETUNUSED子句可以将列设置为不可用。从用户角度看，被标记为不可用状态的列与被删除的列没有区别，都无法通过查询获取其数据，并且还可以添加与UNUSED状态的列同名的字段。但是，UNUSED的列在物理上仍然保存在数据库中，它所占用的存储空间并没有被释放。23第二十三页，共四十八页。【例7-6】设置表Sys_User中Remark列为不可用，然后再删除该表中所有不可用的列，代码如下。SQL>ALTERTABLESys_User2SETUNUSED(RemarK);表已更改。SQL>ALTERTABLESys_User2DROPUNUSEDCOLUMNS;表已更改。7.3.2重命名表在创建表后，如果想要修改表的名称，可以对表进行重命名。【例7-7】将表Sys_User重命名为用户信息。SQL>ALTERTABLESys_User2RENAMETO用户信息;表已更改。24第二十四页，共四十八页。

需要注意的是，对表进行重命名要谨慎。虽然Oracle可以自动更新数据字典中的外键、约束定义、以及表关系，但是它还不能够更新数据库中的存储代码、客户应用，以及依赖于该对象的其他对象。7.3.3删除表

用户经常需要删除一些不需要的表，可使用删除表语句DROPTABLE来完成，语法如下：DROPTABLEtable_name【例7-8】将用户信息表删除。SQL>DROPTABLE用户信息;表已更改。25第二十五页，共四十八页。7.2.4使用OEM工具修改、删除表

使用OEM可以对表进行修改操作，包括修改表名、添加列、删除列、修改列属性等。首先搜索要要修改的表，单击“编辑”按钮，打开编辑表页面，如图7-6所示。图7-6修改表界面26第二十六页，共四十八页。

在修改表页面中，可以直接在界面中输入新的表名、列名，以及列的数据类型、大小和比例等信息。单击“删除”按钮可以删除选定的列，单击“插入”可以添加新的列。如果需要删除表，则需选中要删除的表，如图7-7所示。图7-7删除表界面

单击“使用选项删除”按钮，打开删除选项窗口，选择删除选项。可以选择删除表定义，其中所有数据和从属对象；仅删除数据；仅删除不支持回退的数据。在这里可以选择删除表定义选项，然后单击“是”即可删除指定的表。27第二十七页，共四十八页。7.4完整性约束

数据库不仅仅存储数据，它也必须保证所保存的数据的正确性和相容性，也就是数据完整性。数据完整性是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入/输出造成无效操作或错误信息而提出的。约束是在数据库中保证数据库里表中数据完整性的手段。本节重点介绍数据库完整性的概念及其在Oracle11g中的实现方法。数据库的完整性一般分为四类：实体完整性、域完整性、参照完整性和用户定义的完整性。28第二十八页，共四十八页。实体完整性：要求表中的每一行在表中是唯一的实体，既不能为空也不能取重复的值。域完整性：指数据库表中的字段必须满足某种特定的数据类型或约束。参照完整性：保证数据库中相关联的表里面数据的正确性。

根据约束的作用域，约束可以分为表级约束和列级约束两种。列级约束是列定义的一部分，只能够应用在一个列上；而表级的约束定义在独立的列上，它可以应用于一个表中的多个列。在Oracle系统中定义约束时，可以通过CONSTRAINT关键字为约束命名。如果用户没有为约束指定名称，Oracle将自动为约束建立默认的名称。29第二十九页，共四十八页。在Oracle中，根据不同的用途提供了多种约束，分别为：主键约束（PRIMARYKEY）：主键约束在每个数据表中有且仅有一个。如果一个主键约束由多个列组成，通常把由多个列组成的主键叫做复合主键。主键约束可以保证主键列的数据没有重复值且值不为空，即能够唯一地标识表中的每条记录。该约束主要实现实体完整性。外键约束（FOREIGNKEY）：外键约束保证了数据库的参照完整性。它主要用作把一个表中的数据和另一个表中的数据进行关联。唯一约束（UNIQUE）：唯一约束和主键约束都是设置表中的列不能取重复值，与主键约束不同的是，一个表可以有多个唯一约束。非空约束（NOTNULL）：非空约束是用来约束表中的列不允许取空值的。NOTNULL约束只能在列级别上定义。在一个表中可以定义多个NOTNULL约束。30第三十页，共四十八页。检查约束（CHECK）：CHECK约束通过检查输入到表中的数据值来维护域的完整性，只有符合输入条件的数据才允许输入到表中。默认值约束（DEFAULT）：用于对指定的列设置默认值。其中，唯一约束、非空约束、检查约束和默认值约束可以实现数据库的域完整性。 对约束的定义既可以创建表的同时可以指定相应的约束（如例7-9），也可以通过修改表的结构添加约束。【例7-9】重新创建用户表Sys_User，要求UserID是主键，UserName列只能取唯一值，不得重复，UserType字段取值大于0小于3，UserPwd的默认值为111111，代码如下。31第三十一页，共四十八页。SQL>CREATETABLESys_User2(UserIDNumberPrimaryKey,3UserNameVarchar2(20)NOTNULLUNIQUE,4UserTypeNumber(1)CHECK(UserType>0ANDUserType<3),5UserPwdVarchar2(40)DEFAULT(‘111111’),6RemarkVarchar2(200)7);表已创建。以高校选课管理系统为例，经分析得知，学生和课程的关系是多对多关系，需要创建学生表、课程表和选课表。学生表的结构如下表所示。32第三十二页，共四十八页。创建学生表的代码如下：SQL>CREATETABLEStudents2(Student_IDchar(11),3SnameVarchar2(8),4SsexVarchar2(2),5Sbirthdate,6NationalityVarchar2(40),7Dept_IDchar(3),8Class_IDchar(5)9);表已创建。33第三十三页，共四十八页。创建课程表的代码如下：SQL>CREATETABLECourses2(Course_IDVarchar2(4),3CnameVarchar2(20),4CreditsNUMBER);表已创建。34第三十四页，共四十八页。创建选课表的代码如下：SQL>CREATETABLESC2(Student_IDVarchar2(11),3Course_IDVarchar2(4),4GradeNUMBER,5CONSTRAINTfk_sidFOREIGNKEY(Student_ID)REFERENCESStudents(Student_ID)6ONDELETECASCADE);表已创建。35第三十五页，共四十八页。1.主键约束使用ALTERTABLE语句设置主键约束的语法格式如下：ALTERTABLEtable_nameADDCONSTRAINTconstraint_namePRIMARYKEY(column_name)【例7-10】为学生表添加主键约束。SQL>ALTERTABLEStudents2ADDCONSTRAINTpk_sidPRIMARYKEY(Student_ID);表已更改。36第三十六页，共四十八页。2.唯一约束使用ALTERTABLE语句设置唯一约束的语法格式如下：ALTERTABLEtable_nameADDCONSTRAINTSconstraint_nameUNIQUE(column_name)【例7-11】为课程表的课程名称设置唯一值约束。SQL>ALTERTABLECourses2ADDCONSTRAINTuq_cnUNIQUE(Course_name);表已更改。37第三十七页，共四十八页。3.非空约束使用ALTERTABLE语句设置非空约束的语法格式如下：ALTERTABLEtable_nameMODIFYcolumn_nameNOTNULL【例7-12】为学生表的学生姓名设置非空约束。SQL>ALTERTABLEStudents2MODIFYSnameNOTNULL;表已更改。如果要取消某个列非空的约束，直接使用MODIFY语句把该列的非空约束写成NULL即可。38第三十八页，共四十八页。4.检查约束使用ALTERTABLE语句设置检查约束的语法格式如下：ALTERTABLEtable_nameADDCONSTRAINTSconstraint_nameCHECK(condition)【例7-13】选修表的成绩列取值要求在0~100之间。SQL>ALTERTABLESC2ADDCONSTRAINTchk_gradeCHECK(Grade>0andGrade<100);表已更改。39第三十九页，共四十八页。5.外键约束外键约束可以保证使用外键约束的数据库列于所引用的主键约束的数据列一致，外键约束在一个数据表中可以有多个。外键约束是建立在两张表中的约束，添加外键约束的语法格式如下：ALTERTABLEtable_nameADDCONSTRAINTSconstraint_nameFOREIGNKEY(column_name)REFERENCEtable_name(column_name)ONDELETECASCADE●REFERENCE：要引用的表名（列名）。●ONDELETECASCADE：级联删除，当被参照表的主键字段被删除时，参照表中的外键所对应的字段也同时被删除。【例7-14】选修表中的学生要参考学生表的信息，课程号需要参考课程表的信息。由于在创建选修表时已经设置了学号为外键，此处为选修表的课程号设置外键。SQL>ALTERTABLESC2ADDCONSTRAINTfk_cidFOREIGNKEY(Course_ID)REFERENCESCourses(Course_ID)3ONDELETECASCADE);表已更改。40第四十页，共四十八页。6.删除约束删除约束的语法格式：ALTERTABLEtable_nameDROPCONSTRAINTSconstraint_name【例7-15】删除例7-11创建的约束。SQL>ALTERTABLECourses2DROPCONSTRAINTuq_cn;表已更改。7.5表中数据的操作

一个表创建以后，其中并不包含任何记录，需要向表中输入数据。本节重点介绍如何向表中添加数据、更新数据以及删除数据。41第四十一页，共四十八页。7.5.1插入数据使用INSERT语句可向指定表中插入数据。INSERT语法的基本结构如下：INSERTINTO<table_name>（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子句后的列表清单，但需要注意的是，必须根据表中定义的列的顺序，为所有列提供数据。42第四十二页，共四十八页。【例7-17】向表Sys_User中插入数据。SQL>INSERTINTOSys_User2VALUES(‘2’,‘Zhang’,’2’,‘Zhang’,null);已创建1行。另外，有一种简单的插入多行数据的方法。这种方法是使用SELECT语句查询出的结果代替VALUES子句。这种方法的语法结构如下：INSERTINTO<table_name>（column_name1,column_name2…,column_namen）SELECTcolumn_name[,…n]FROMtable_nameWHEREsearch_conditions

43第四十三页，共四十八页。【例7-18】向Courses表输入数据，创建“Courses”表的一个副本Courses1，将Courses表的全部数据添加到Courses1表中。SQL>INSERTINTOCoursesVALUES(‘0001’,’大学语文’,4);SQL>INSERTINTOCoursesVALUES(‘0002’,’高等数学’,4);SQL>INSERTINTOCoursesVALUES(‘0003’,’计算机基础’,4);SQL>INSERTINTOCoursesVALUES(‘0004’,’数据库概论’,4);SQL>INSERTINTOCourses12SELECTCourse_ID,Cname,CreditsFROMCourses;已创建4行。44第四十四页，共四十八页。7.5.2更新数据使用UPDATE命令修改表中的数据。UPDATE<table_name>SETcolumn_name1=values1,column_name2=values2,