管理Oracle表.doc

5.1 表的设计在Oracle数据库中，用于存储数据的数据库对象包括基本表(简称表)、分区表、簇、索引表等，这里只介绍基本表的创建和管理方法。5.1.1 表的结构创建表之前需要根据表中将要存入的数据的特点对表进行精心的设计，包括表中包含哪些列，列的数据类型、列中是否允许有空值等。如图5-1和5-2所示是Oracle系统中存放的两个表，从中可以看出，每个表都由以下几部分组成。图5-1 scott.dept表中的数据图5-2 scott.emp表中的数据1) 表名在数据库的同一模式(用户)中，每一个表都应该有一个唯一的表名。表名长度最多为30个字符，并且必须以字母开头；除字母以外，表名中还可以包含数字、下划线或井号(不能包含空格)；表名不能使用Oracle 9i的保留字。在图5-2中，表名表示为scott.emp，其中scott是模式(用户)名，而emp是表的名字。2) 列名每个表由若干个列组成，在同一个表中每个列的名字应该是唯一的，列的顺序是任意的。列名的命名规则同表名，一个表中最多可以包含1000列。在图5-2所示的emp表中，共包含8个列。3) 列的数据类型表中的每个列都要定义一个数据类型。定义数据类型时需要慎重考虑，如果定义的范围太小，可能会造成无法存放某些数据，定义的范围太大可能会造成存储空间的浪费。存储空间的增加将增加系统的I/O操作量，从而降低系统的使用效率。4) 列中是否允许有空值表中的某些列中可能严格禁止出现空值，例如作为主键的列不能为空，某些列虽然不是主键，但根据企业实际业务需要可能也不允许有空值。相反，某些列就需要定义成允许有空值，例如图5-2所示的emp表中的COMM列就允许有空值。需要说明的是，空不等于0，也不等于空字符串，空反映的是“不知道”或“不确定”的信息，空字符串和0都是具体的值，而“空”是一种状态。5.1.2 主键关系数据库表的一个基本要求是表中不能存在完全相同的两个行，这可以通过实体完整性控制来实现。实体完整性要求表中的主键列中不能有空值。可以在创建表的同时创建主键约束，也可以在创建表以后通过修改表的结构的方式来创建主键约束，这里只介绍第一种情况。另外，主键分为列级主键和表级主键。如果主键只包含一个列，那么既可以创建列级主键，也可以创建表级主键；如果主键中包含1个以上的列，那么只能创建表级主键。5.1.3 数据类型Oracle可以使用的数据类型主要包括以下几种。(1) VARCHAR2(n)：可变长字符型数据，最多可以存放n个字符。n的最小值和默认值都是1；n的最大值为4000。(2) CHAR(n)：固定长度字符型数据，最多可以存放n个字符。n的最小值和默认值都是1，最大值是2000。当该数据类型的变量中存放的字符数小于n时，将通过在末尾补充空格的方式使数据长度为n。(3) NUMBER(p, s)：数值型数据。其中p代表数据的总为数，最小值为1，最大值为38；s代表小数位数，最小值为-84，最大值为124。如果省略p和s，则自动设置p为38。(4) DATE：用于存放日期时间型数据，范围是公元前4712年1月1日到公元9999年12月31日。5.2 表的定义通过使用SQL语言中的数据定义语言可以实现表的定义，包括创建表、修改表、截断表和删除表。5.2.1 创建表创建表的语法格式如下所示。CREATE TABLE table_name( col_name datatype NOT NULL PRIMARY KEY, PRIMARY KEY (col_name, );例5-1 创建一个如表5-1所示的PROJ表，保存各项工程的信息。表5-1 PROJ表PROJNONUMBER(4) PRIMARY KEYPNAMEVARCHAR2(50) NOT NULLBEGINDATEDATE NOT NULLENDDATEDATE1001RAINBOW GARDEN BUILDING23-5月 -8723-5月 -881002BLUE BRIDGE2-6月 -8831-12月 -881003NORTH HIGHWAY13-8月 -881004CRYTAL MALL25-12月 -88(1) 首先启动SQL*Plus工具，使用scott身份连接到Oracle服务器。(2) 输入如下命令并执行。CREATE TABLE proj(projno NUMBER(4) PRIMARY KEY, pname VARCHAR2(50) NOT NULL, begindate DATE NOT NULL, enddate DATE);例5-2 创建一个如表5-2所示的WORK表，保存员工参与项目的情况。表5-1 WORK表EMPNONUMBER(4) NOT NULL PKPROJNONUMBER(4) NOT NULL PKJOBVARCHAR2(9)73691001CLERK76981001MANAGER75661002MANAGER78761002CLERK77821003MANAGER79001003CLERK73691004CLERK76981004MANAGER输入如下命令并执行。CREATE TABLE work(empno NUMBER(4) NOT NULL, projno NUMBER(4) NOT NULL, job VARCHAR2(9), PRIMARY KEY(empno, projno);5.2.2 修改表如果表的定义不能满足要求，则可以使用ALTER TABLE命令进行修改，包括增加新列、修改一个列的定义、删除列等。1. 增加新列增加新列的命令格式如下所示。ALTER TABLE table_nameADD (column_name datatype);例5-3 下面的语句将在表PROJ中增加一个新列LOC，表示工程的地点。ALTER TABLE projADD (loc VARCHAR2(13);需要注意的是，如果表中没有数据，则新增加的列既可以定义为允许空，也可以定义为不允许空；如果表中有数据，则新增加的列可以定义为允许空，或者在定义了包含默认值的情况下可以不允许空。2. 修改已经存在的列修改已经存在的列的语法格式如下所示。ALTER TABLE table_nameMODIFY (column_name datatype, DEFAULT);修改表时需要注意以下几点：q 列的宽度必须满足该列中已经存在的数据的实际宽度。q 如果一个NUMBER列中已经存在数据，则该列中的p和s都不能减小。例5-4 下面的语句将新增加的列LOC设置为不允许空。ALTER TABLE projMODIFY(loc VARCHAR2(13) NOT NULL);3. 删除列删除列的语法格式如下所示。ALTER TABLE table_name DROP COLUMN column_name;删除列时需要注意以下几点。q 使用该命令将永久删除一列。q 使用该命令不能删除表中唯一的列。例5-5 下面的命令将删除JOB表中的JOB列。ALTER TABLE work DROP COLUMN job;5.2.3 删除表对表的整体操作包括重命名表、截断表、删除表等。1. 重命名表可以通过下面的方法重命名表。RENAME old_table_name TO new_table_name;例5-6 下面的命令将表JOB重命名为ASSIGN。RENAME work TO assign;2. 截断表通过截断表的操作可以快速删除表中的全部数据，使表被重置。截断表的语法格式为。TRUNCATE TABLE table_name;例5-7 下面的语句将截断表ASSIGN。TRUNCATE TABLE assign;需要注意的是，截断表的功能是删除表中全部数据，但是TRUNCATE语句不是数据操作语言的语句，而是数据定义语言的语句。3. 删除表删除表的语法格式为。DROP TABLE table_name;例5-8 下面的语句将删除表ASSIGN。DROP TABLE assign;5.3 管理表中的数据刚刚创建完的表是空表，表中没有数据。可以使用数据操纵语言的语句来操作表中的数据，如INSERT、UPDATE、DELETE等。操纵表中数据之前，必须要了解表的结构，包括表中列的数据类型、是否允许空、是否是主键列等。在向表中插入数据记录时需要注意以下几点。(1) 主键所在的列不能为空，也不允许出现重复值。(2) 必须向定义为NOT NULL的列中输入值。更新表中数据时需要注意：同插入数据记录时的情形相同，不能将定义为NOT NULL的列更新为空，更新主键列中的值时，不能违反主键约束。删除表中数据记录时需要注意：删除操作的单位是记录，也就是说每一次删除操作都会删除整条数据记录。1. 插入数据可以使用INSERT命令向表中插入数据，该命令的语法格式如下所示。INSERT INTO table_name (column_name, )VALUES (data_value, );例5-9 首先插入第一行数据。这里需要注意的是，使用TO_DATE函数将字符型数据转换为日期型数据以后插入到相应字段中。INSERT INTO proj(projno, pname, begindate, enddate)VALUES(1001, RAINBOW GARDEN BUILDING, TO_DATE(1987.05.23, yyyy.mm.dd), TO_DATE(1988.05.23, yyyy.mm.dd);例5-10 插入第二行。INSERT INTO proj(projno, pname, begindate, enddate)VALUES(1002, BLUE BRIDGE, TO_DATE(1988.06.02, yyyy.mm.dd), TO_DATE(1988.12.31, yyyy.mm.dd);例5-11 插入第三行。第三行记录的特点是没有提供END列的值。INSERT INTO proj(projno, pname, begindate)VALUES(1003, NORTH HIGHWAY, TO_DATE(1988.08.13, yyyy.mm.dd);例5-12 插入第四行。为了省略列列表，也可以向没有值的列提供NULL，表示该字段的状态为空。INSERT INTO proj(projno, pname, begindate, enddate)VALUES(1004, CRYTAL MALL, TO_DATE(1988.12.25, yyyy.mm.dd), NULL);2. 更新数据可以使用UPDATE语句更新表中的数据。UPDATE语句的命令格式如下所示。UPDATE tablenameSET columnname=new_datavalue, WHERE condition;如果省略了WHERE子句，则将表中所有的记录全部更新。例5-13 将工程号为1003的工程的结束日期更新为1989年1月1日。UPDATE projSET enddate=TO_DATE(1989.01.01, yyyy.mm.dd)WHERE projno=1003;3. 事务控制语句在Oracle中，事务（Transaction）是一系列已经执行但尚未提交的SQL语句。数据操纵语句的语句执行后事务不能自动提交。可以使用显示或隐式的方式提交事务或回滚事务。n 显示方式提交事务：显示提交事务的语句是COMMIT。n 显示方式回滚事务：隐式回滚事务的语句是ROLLBACK。例5-14 提交事务。刚刚执行的4条插入语句和一条更新语句构成一个事务，这个事务尚未提交，如果数据库系统崩溃，他们的操作将丢失。下面使用COMMIT语句提交事务，使插入和更新的数据永久保存在外存储器上。COMMIT;4. 删除数据可以使用DELETE语句删除表中的数据。DELE