第4章 表的创建与管理

Oracle 数据库管理与应用
第4章 表的创建与管理
本章要点
1 2 3 4
基本数据类型
表的创建、修改和删除
插入、更新与删除表中的数据
数据完整性的实施方法
4.1 创建表 4.1.1 基本数据类型 1．字符数据类型
CHAR（n）：用于存储长度为n的定长字符串，最 大长度为2000字节，未指定长度时默认为1。 VARCHAR2（n）：用于存储长度为n的变长字符 串，最大长度为4000字节，该类型没有默认长度， 使用时必须指定。 NCHAR（n）：用于存储长度为n的定长的 Unicode字符集数据，最大长度为2000字节。国 家字符集的NCHAR类型的1位既可存储一个字母 （或其他符号）也可以存储一个汉字。 NVARCHAR2（n）：用于存储长度为n的变长的 Unicode字符集数据，最大长度为4000字节。
例4.1 在当前方案中创建一个名为student的 表，包括学号（studentID）、姓名（name ）、性别（sex）、出生日期（birthday） 四个字段。 CREATE TABLE student (studentID CHAR(6), name VARCHAR2(8), sex CHAR(2), birthday DATE) TABLESPACE users;
4.1Biblioteka Baidu2 使用OEM工具创建表
在OEM主界面中选择“管理”选项卡，出 现如图4-1所示的数据库管理界面，在此界 面上选择“方案”中的“表”选项 。
4.1.3 使用SQL命令创建表
CREATE TABLE [schema.]table_name (column_name datatype [DEFAULT expression][column_constraint],…n)
table_name：要插入数据的表名。 column_name：要插入数据的字段名。如果向表中的 所有字段插入数据，则字段列表可以省略。 expression：为相应位置的column_name字段指定值 的表达式。
如果向表中插入的是常量数据，那么需要 注意：
数值型常量，可以直接插入数据。 字符型和日期型常量插入时需要加单引号。 日期型常量的默认格式为：’dd-mm月-yyyy’ ，例如：’10-7月-2010’表示2010年7月10号 。
4．LOB数据类型 LOB（Large Object）数据类型存储非结构化 数据，比如二进制文件、图形文件，或其他外 部文件。LOB 可以存储到4G字节大小。数据 可以存储到数据库中也可以存储到外部数据文 件中。LOB数据类型有以下几种：
BLOB：存储二进制大对象，可以是图像、音频文 件以及视频文件。 CLOB：存储字符大对象。 BFILE：存储外部二进制文件，文件的大小由操作 系统决定。
2．插入多行数据
语法格式如下： INSERT INTO dest_table_name [ ( column_name [,…n] ) ] SELECT column_name [,…n] FROM source_table_name [ WHERE search_conditions ]
例4.8 创建employee1表，包含三个字段 empidID、empname和empjob。将例4.3雇员 表中的所有雇员信息插入到employee1表中。
2．TRUNCATE TABLE 命令 这个命令所做的修改不能回滚，对于已经删 除的记录不能恢复，语法格式如下： TRUNCATE TABLE table_name 例4.13 永久删除雇员表中的所有记录。 TRUNCATE TABLE 雇员;
4.3 修改表与删除表
4.3.1 修改表
1．使用OEM工具修改表结构 2．使用SQL命令修改表结构
4.1.4 基于已有的表创建新表
CREATE TABLE table_name [(column_name1,column_name2,…)] AS subquery 其中各参数的意义如下：
column_name：新表的字段名，可以省略。若 省略，则新表的字段名与查询结果集中包含的 字段同名。用户也可以修改新表中的字段名， 但不能修改字段的数据类型和宽度。 subquery：是指子查询的SELECT语句。
使用SQL命令修改表结构的语法格式如下： ALTER TABLE [schema]table_name ADD (column_name datatype [DEFAULT expression][column_constraint],…n) | DROP COLUMN column_name| DROP (coloumn_name1,column_name2,…n) | MODIFY (column_name new_datatype [DEFAULT expression][column_constraint],…n) | RENAME COLUMN column_name TO new_cloumn_name | RENAME TO new_table_name
例4.7 向student表中插入一条记录，只给 出部分字段值。 INSERT INTO student(studentID,name) VALUES('201002','李四'); 或 INSERT INTO student VALUES('201002','李四',default,null);
1．DELETE命令 DELETE语句可以用来删除表中数据，语法 格式如下： DELETE [ FROM ] table_name [ WHERE search_conditions] 其中各参数的意义如下：
table_name：要删除记录的表名。 search_conditions：删除表中符合 search_conditions条件的记录，缺省WHERE 子句时，则删除该表中的所有数据
其中各参数的意义如下：
table_name：要更新数据的表名。 column_name：要修改数据的字段名。 expression：更新后的数据值。 subquery：SELECT子查询作为字段的新值。 search_conditions：更新条件，只对表中满足 该条件的记录进行更新。省略该项时，将更新 表中所有的行。
4.1.6 使用DESCRIBE命令查看表结构
语法格式如下： DESC[RIBE] [schema.]object_name 例4.5 显示student表的表结构。 DESC student;
4.2 向表中插入、修改和删除数据
实现数据存储的前提是向表中插入数据， 没有数据的表只是一个空的表结构，没有 任何实际意义。向表中插入数据后，可以 根据用户的需要进行数据的修改和删除操 作。
4.2.3 删除数据
当表中的部分或全部数据无用时，可以使用删 除命令将它们从表中删除。常用的删除命令包 括DELETE和TRUNCATE TABLE，二者的区 别是：
DELETE命令是逻辑删除，只是将要删除的行加 上删除标记，被删除后可以使用ROLLBACK命令 回滚，删除操作时间较长；TRUNCATE TABLE 命令是物理删除，将表中的数据永久删除，不能 回滚，删除操作快。 DELETE命令包含WHERE子句，可以删除表中的 部分行；TRUNCATE TABLE命令只能删除表中 的所有行。
CREATE TABLE employee1 (empID NUMBER(4),empname VARCHAR2(10),empjob CHAR(9)); INSERT INTO employee1 SELECT * FROM 雇员;
4.2.2 修改数据
UPDATE table_name SET column_name1=expression1 [,column_name2=expression2]…| (column_name1[,column_name2,…])=sub query [ WHERE search_conditions ]
例4.9 将scott方案下的emp表中编号为 7369的雇员的工作修改为SALESMAN。 UPDATE scott.emp SET job='SALESMAN' WHERE empno=7369;
例4.10 将scott方案下的emp表中编号为 7369的雇员的工作改为与编号7902雇员的 工作相同。
2．数值数据类型
NUMBER（p，s）：其中p是精度，表示总的 有效数字的个数；s是小数位数。 NUMBER（p）：精度为p的整数。 NUMBER：如果没有指明精度和小数位数，则 表示精度为38的浮点数。
3．日期数据类型
DATE：用于存储日期和时间格式的数据。可 以使用函数SYSDATE获得当前的日期和时间 。通常为DD-MON-YY格式表示。 TIMESTAMP：时间戳类型，与DATE数据类型 不同。TIMESTAMP可以包含小数秒（ fractional second），带小数秒的TIMESTAMP 在小数点右边最多可以保留9位。
例4.6 向student表中插入一条完整的记录。 INSERT INTO student(studentID,name,sex,birthday) VALUES('201001','张三','男','11-5月-1990'); 或 INSERT INTO student VALUES('201001','张三','男','11-5月-1990');
例4.2 将scott方案下的emp表复制到当前方 案中的emp_new表。 CREATE TABLE emp_new AS SELECT * FROM scott.emp;
例4.3 在当前方案中创建新的雇员表，包 括雇员编号、姓名、工作三个字段，内容 来源于scott方案中的emp表的empno、 ename、job三个字段。 CREATE TABLE 雇员(编号,姓名,工作) AS SELECT empno,ename,job FROM scott.emp;
4.2.1 插入数据
1．插入单行数据 INSERT语句可以向表中插入数据，语法格式如 下：
INSERT INTO table_name[( column_name [,…n] ) ] VALUES ( expression | NULL | DEFAULT [,…n] )
其中各参数的意义如下：
DELETE FROM scott.emp WHERE job='SALESMAN'; --查询工作为SALESMAN的雇员记录。 SELECT * FROM scott.emp WHERE job='SALESMAN'; --看不到符合条件的记录 --使用ROLLBACK命令回滚删除操作，再执行以上 的查询语句 ROLLBACK; SELECT * FROM scott.emp WHERE job='SALESMAN'; --删除操作被撤销，数据被回滚
UPDATE scott.emp SET job=(SELECT job FROM scott.emp WHERE empno=7902) WHERE empno=7369;
例4.11 将scott方案下的emp表中工作为 SALESMAN的雇员工资都增加5%。 UPDATE scott.emp SET sal=sal+(sal*0.05) WHERE job='SALESMAN';
4.1.5 定义表中字段的默认值
在使用CREATE TABLE 命令创建新表时可 以指定字段的默认值。字段指定了默认值 后，当使用INSERT语句向表中插入新数据 时，若该字段未指定值，那么Oracle将自动 为该字段插入默认值。 每个字段只能设置一个默认值。
例4.4 重新创建student表，并将性别（sex ）字段设置默认值为‘男’。 CREATE TABLE student (studentID CHAR(6), name VARCHAR2(8), sex CHAR(2) DEFAULT '男', birthday DATE);