幻灯片 1

Size: px

Start display at page:

Download "幻灯片 1"

劳辛韦
5 years ago
Views:

2 目标完成本课后, 您应当能够执行下列操作 : 描述主要数据库对象创建表描述列定义时可用的数据类型改变表的定义删除改名和截断表 9-2 Copyright Oracle Corporation, All rights reserved. 课程目标在本课中, 你将学习关于表主要数据库对象, 以及他们之间的关系你还将学习怎样创建修改和删除表中国科学院西安网络中心 2005 Oracle SQL 入门 9-2

3 数据库对象对象表说明基本存储单元 ; 由行和列组成视图逻辑地从一个或多个表中表示数据子集序列索引同义词数字值发生器改善一些查询的性能给对象可选择的名字 9-3 Copyright Oracle Corporation, All rights reserved. 数据库对象 Oracle 数据库能够包含多种数据结构每一种结构应该在数据设计中描述, 以使它能够在数据库开发阶段被创建 Table: 存储数据 View: 来自一个或多个表的数据的子集 Sequence: 数字值发生器 Index: 改善一些查询的性能 Synonym: 给对象一个可替代的名字 Oracle9i 表结构表可以在任何时间被创建, 即使用户正在使用数据库你不需要指定表的大小, 表的大小最终由作为一个整体分配给数据库的空间的数量定义但是随着时间的过去一个表将使用多少空间是重要的表结构能够被联机修改注 : 除了幻灯片中介绍的数据库对象, 还有一些其他的数据库对象, 但没有包括在本课程中教师注释表能够有多达 1,000 个列, 并且必须符合标准的数据库对象命名约定当使用 AS 子查询子句时, 列定义可以忽略表在创建时没有数据, 除非指定了一个查询行通常用 INSERT 语句添加中国科学院西安网络中心 2005 Oracle SQL 入门 9-3

4 命名规则表命名和列命名 : 必须以字母开始必须是 1 30 个字符长度只能包含 A Z, a z, 0 9, _, $, 和 # 同一个用户所拥有的对象之间不能重名不能用 Oracle 服务器的保留字 9-4 Copyright Oracle Corporation, All rights reserved. 命名规则依照命名 Oracle 数据库对象的标准规则来命名数据库表和列 : 表名和列名必须由一个字母开始, 长度在 1 30 之间名字只能包含 A Z, a z, 0 9, _ ( 下划线 ),$ 和 # ( 合法字符, 但建议不要使用 ) 同一个 Oracle 服务器用户所拥有的对象名字不能重复名字不能用 Oracle 服务器的保留字命名原则使用描述性的名字为表和其他数据库对象命名注 : 名字是大小写不敏感的, 例如, EMPLOYEES 与 employees 或 employees 作为同一个名字来处理更多信息, 见 Oracle9i SQL Reference, 对象名字与限定中国科学院西安网络中心 2005 Oracle SQL 入门 9-4

5 CREATE TABLE 语句必须有 : CREATE TABLE 权限一个存储区域 CREATE TABLE [schema.]table (column datatype [DEFAULT expr][,...]); 指定 : 表名列名列数据类型和列的大小 9-5 Copyright Oracle Corporation, All rights reserved. CREATE TABLE 语句用 SQL 的 CREATE TABLE 语句创建表以存储数据, 该语句是数据定义语言 (DDL) 语句之一, 其它的 (DDL) 语句将在后面讲述 DDL 语句是 SQL 语句的一个子集, 用来创建修改或删除 Oracle9i 数据库的结构这些语句会立即作用于数据库, 并且他们还将信息记录在数据字典中为了创建表, 用户必须有 CREATE TABLE 权限和用于创建对象的存储区域数据库管理员用数据控制语言 (DCL) 语句,DCL 语句将在后面讲述, 授予权限给用户在语法中 : schema 与所有者的名字一样 table 表的名字 DEFAULT expr 指定默认值, 在 NSERT 语句省略值时使用 column 列的名字 datatype 列的数据类型和长度教师注释请读 9-37 页的教师注释中国科学院西安网络中心 2005 Oracle SQL 入门 9-5

6 引用另一个用户的表表属于另一个用户, 不在该用户的方案中在那些表名字的前面使用所有者的名字作为前缀 9-6 Copyright Oracle Corporation, All rights reserved. 引用另一个用户的表方案 (schema) 是对象的集合, 方案对象直接反映数据在数据库中的逻辑结构, 方案对象包括表视图同义词序列存储过程索引集群和数据库链接如果一个表不属于本用户, 那么, 其所有者的名字必须放在表名的前面, 例如, 如果一个方案命名为 USER_B, 并且 USER_B 有一个表 EMPLOYEES, 那么, 其他用户用下面的语句从表中取回数据 : SELECT * FROM user_b.employees; 中国科学院西安网络中心 2005 Oracle SQL 入门 9-6

7 DEFAULT 选项在插入时, 为一个列指定一个默认值... hire_date DATE DEFAULT SYSDATE,... 文字值表达式或者 SQL 函数都是合法的值另一个列名或者伪列是不合法的值默认数据类型必须与列的数据类型匹配 9-7 Copyright Oracle Corporation, All rights reserved. DEFAULT 选项一个列可以用 DEFAULT 选项给予一个默认值, 列该选项防止插入时输入空值到列中默认值可以是文字表达式或 SQL 函数, 例如用 SYSDATE 和 USER 但默认值不能是另一个列的名字或伪列, 例如 NEXTVAL 或 CURRVAL 默认表达式必须与列的数据类型相匹配注 :CURRVAL 和 NEXTVAL 在后面的章节中说明教师注释这里是一个伪列的例子, 对于一个查询返回的每一行,ROWNUM 伪列返回一个数来指示 Oracle 服务器从一个表中选择的行顺序被选择的第一行的 ROWNUM 为 1, 第二行为 2, 等等对于带有 DEFAULT 关键字的 INSERT 和 UPDATE 语句, 其默认值的处理方式将在操纵数据一课中讲述中国科学院西安网络中心 2005 Oracle SQL 入门 9-7

8 创建表创建表 CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13)); Table created. 确认表的创建 DESCRIBE dept 9-8 Copyright Oracle Corporation, All rights reserved. 创建表幻灯片内中的例子创建 DEPT 表, 该表有三个列 :DEPTNO DNAME 和 LOC 用 DESCRIBE 命令来确认表的创建因为创建表是一个 DDL 语句, 当该语句执行时将会发生一个自动提交教师注释解释 CREATE TABLE 的附加语法可以包括约束等对于 CREATE TABLE 语法的更多信息, 请参考 :Oracle9i SQL Reference, 创建表中国科学院西安网络中心 2005 Oracle SQL 入门 9-8

9 Oracle 数据库中的表用户表 : 由用户创建和维护的表的集合包含用户信息数据字典 : 由 Oracle 服务器创建和维护的表的集合包含数据库信息 9-9 Copyright Oracle Corporation, All rights reserved. Oracle 数据库中的表用户表由用户创建, 例如 EMPLOYEES 在 Oracle 数据库中有另一个表和视图的集合称为数据字典 (data dictionary), 该集合由 Oracle 服务器创建和维护, 其中包含有关数据库的信息全部数据字典表的所有者是用户 SYS 数据字典表的基表很少被用户访问, 因为其中的信息不容易理解, 因此, 用户一般是访问数据字典视图, 因为视图中的信息是以容易理解的格式表示的存储在数据字典中的信息包括 Oracle 服务器用户的名字, 被授予用户的权限, 数据库对象名, 表结构和审计信息有四种数据字典视图, 每一种有一个特定的前缀来反映其不同的目的 USER_ ALL_ DBA_ 前缀说明这些视图包含关于用户所拥有的对象的信息这些视图包含所有用户可访问的表 ( 对象表和相关的表 ) 的信息这些视图是受限制的视图, 它们只能被分配有 DBA 角色的用户所访问 V$ 这些视图是动态执行的视图, 包含数据库服务器的性能存储器和锁的信息中国科学院西安网络中心 2005 Oracle SQL 入门 9-9

10 查询数据字典查看本用户所拥有的表的名称 SELECT table_name FROM user_tables ; 查看本用户所拥有的不同的对象类型 SELECT DISTINCT object_type FROM user_objects ; 查看本用户所拥有的表视图同义词和序列 SELECT * FROM user_catalog ; 9-10 Copyright Oracle Corporation, All rights reserved. 查询数据字典你可以查询数据字典表来查看你所拥有的各种数据库对象常用的数据字典表有 : USER_TABLES USER_OBJECTS USER_CATALOG 注 :USER_CATALOG 有一个称为 CAT 的同义词, 你可以在 SQL 语句中用该同义词代替 USER_CATALOG SELECT * FROM CAT; 中国科学院西安网络中心 2005 Oracle SQL 入门 9-10

11 数据类型数据类型 VARCHAR2(size) CHAR(size) NUMBER(p,s) DATE LONG CLOB RAW and LONG RAW BLOB BFILE ROWID 说明可变长度的字符数据固定长度的字符数据可变长度的数字数据日期和时间值最大 2G 的可变长度字符数据最大 4G 的字符数据原始二进制数据最大 4G 的二进制数据最大 4G 的, 存储在外部文件中的二进制数据一个 64 进制的数制系统, 表示表中一行的唯一地址 9-11 Copyright Oracle Corporation, All rights reserved. 数据类型数据类型说明 VARCHAR2(size) 可变长度字符数据 ( 必须指定最大字符数 : 最小字符数是 1; 最大字符数是 4000) CHAR [(size)] 固定长度字符数据, 长度的大小以字节为单位 ( 默认和最小字符数为 1; 最大字符数为 2000) NUMBER [(p,s)] 数字, 精度为 p, 小数为 s (p 是小数数字的总长度,s 是小数点右边的数字长度 ;p 的范围从 1 到 38,s 的范围从 -84 到 127) DATE 日期和时间值, 从公元前到公元 LONG 最大 2G 的可变长度字符数据 CLOB 最大 4G 的字符数据 RAW(size) 原始二进制数据 ( 必须指定最大长度, 最大长度为 2000) LONG RAW BLOB BFILE ROWID 可变长度原始二进制数据, 最大 2G 二进制数据, 最大 4G 二进制数据存储在一个外部文件中 ; 最大到 4G 十六进制串, 表示行在所在的表中唯一的行地址该数据类型主要用于返回 ROWID 伪列中国科学院西安网络中心 2005 Oracle SQL 入门 9-11

12 数据类型 ( 续 ) 在用子查询创建表时,LONG 列不会被复制 LONG 列不能包括在 GROUP BY 或 ORDER BY 子句中在每个表中只能有一个 LONG 列在 LONG 列上不能定义约束通常用情况下使用 CLOB 列而不是 LONG 列教师注释 Oracle8 引入了大对象 (LOB) 数据类型, 它可以存储大的和非结构化的数据, 例如文本图象视频和空间数据, 最大 4G LONG 列可以容易地移动到 LOB 列学生可参考 Oracle9i Migration Release Guide 教师注释 (9-13 页 ) 在下一页显示的日期和时间数据类型是 Oracle9i 新发布的中国科学院西安网络中心 2005 Oracle SQL 入门 9-12

13 日期时间数据类型 Oracle9i 对日期时间的增强 : 引入了新的日期时间数据类型可用新数据类型存储对时区和本地时区的增强数据类型 TIMESTAMP INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND 说明带小数秒的日期作为年和月的时间间隔存储作为天小时分和秒的时间间隔存储 9-13 Copyright Oracle Corporation, All rights reserved. 其他日期时间数据类型数据类型 TIMESTAMP INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND 说明允许带小数秒的时间被作为日期存储有一些变异的数据类型允许时间作为年和月的间隔被存储允许时间作为天小时分和秒的间隔被存储中国科学院西安网络中心 2005 Oracle SQL 入门 9-13

14 日期时间数据类型 TIMESTAMP 数据类型是 DATE 数据类型的一种扩展它存储 DATE 数据类型的年月和日, 加小时分和秒值, 以及秒的小数值 TIMESTAMP 数据类型被指定如下 : TIMESTAMP[(fractional_seconds_precision)] 9-14 Copyright Oracle Corporation, All rights reserved. 日期时间数据类型 fractional_seconds_precision 在秒日期时间域的小数部分随意地指定 0 到 9 个数字, 默认是 6 例 CREATE TABLE new_employees (employee_id NUMBER, first_name VARCHAR2(15), last_name VARCHAR2(15),... start_date TIMESTAMP(7),...); 在上面的例子中, 我们创建了一个表 NEW_EMPLOYEES, 其中字段 start_date 的数据类型是 TIMESTAMP, 精度 7 指示小数秒的精度, 如果不指定, 小数秒的默认精度是 6 假定插入两行到 NEW_EMPLOYEES 表中, 输出展示了显示的差异 (DATE 数据类型以 DD-MON-RR 格式显示 ): SELECT start_date FROM new_employees; 17-JUN AM 21-SEP AM 中国科学院西安网络中心 2005 Oracle SQL 入门 9-14

15 TIMESTAMP WITH TIME ZONE 数据类型 TIMESTAMP WITH TIME ZONE 是 TIMESTAMP 的一个变量, 它对 TIMESTAMP 值进行一个时区转换在本地时间和 UTC 之间, 小时和分钟的时区转换是不同的 TIMESTAMP[(fractional_seconds_precision)] WITH TIME ZONE 9-15 Copyright Oracle Corporation, All rights reserved. 日期时间数据类型 UTC 代表协调世界时以前的格林尼治标准时间如果两个 TIMESTAMP WITH TIME ZONE 在 UTC 中代表同一时刻, 它们的值被认为是相同的, 而不管存储在数据中的 TIME ZONE 偏移因为 TIMESTAMP WITH TIME ZONE 也可以存储时区信息, 它特别适合记录那些必须组合或协调地理区域的日期信息例如, TIMESTAMP ' :00:00-8:00' 与 TIMESTAMP ' :00:00-5:00' 是相同的美国西部标准时间 8:00 a.m. 和东部标准时间 11:00 a.m. 是相同的该时间也可以被指定为 : TIMESTAMP ' :00:00 US/Pacific' 注 : 小数秒精度指定 SECOND 日期时间字段的小数部分数字的数目, 其范围是 0 到 9, 默认是 6 中国科学院西安网络中心 2005 Oracle SQL 入门 9-15

16 TIMESTAMP WITH LOCAL TIME 数据类型 TIMESTAMP WITH LOCAL TIME ZONE 是 TIMESTAMP 的另一个变量, 它对 TIMESTAMP 值进行一个时区转换存储在数据库中的数据被格式化为数据库时区时区的转换不被作为列数据的一部分存储 ;Oracle 以本地会话时区返回数据 TIMESTAMP WITH LOCAL TIME ZONE 数据类型被如下指定 : TIMESTAMP[(fractional_seconds_precision)] WITH LOCAL TIME ZONE 9-16 Copyright Oracle Corporation, All rights reserved. 日期时间数据类型不像 TIMESTAMP WITH TIME ZONE, 你可以指定 TIMESTAMP WITH LOCAL TIME ZONE 类型作为一个主键或唯一键的一部分在本地时间和 UTC 之间的时区转换 ( 小时或分钟 ) 是不同的, 对于 TIMESTAMP WITH LOCAL TIME ZONE 是非文字的注 : 小数秒精度指定 SECOND 日期时间字段的小数部分数字的数目, 其范围是 0 到 9, 默认是 6 例 CREATE TABLE time_example (order_date TIMESTAMP WITH LOCAL TIME ZONE); INSERT INTO time_example VALUES('15-NOV-00 09:34:28 AM'); SELECT * FROM time_example; order_date NOV AM TIMESTAMP WITH LOCAL TIME ZONE 类型适合于两层应用程序, 在其中你可以用客户系统的时区显示日期和时间中国科学院西安网络中心 2005 Oracle SQL 入门 9-16

17 INTERVAL YEAR TO MONTH 数据类型 INTERVAL YEAR TO MONTH 存储一个使用年和月时间域的时间段 INTERVAL YEAR [(year_precision)] TO MONTH INTERVAL '123-2' YEAR(3) TO MONTH Indicates an interval of 123 years, 2 months. INTERVAL '123' YEAR(3) Indicates an interval of 123 years 0 months. INTERVAL '300' MONTH(3) Indicates an interval of 300 months. INTERVAL '123' YEAR Returns an error, because the default precision is 2, and '123' has 3 digits Copyright Oracle Corporation, All rights reserved. INTERVAL YEAR TO MONTH 数据类型 INTERVAL YEAR TO MONTH 用年和月日期时间字段存储一段时间用 INTERVAL YEAR TO MONTH 表示两个日期时间值的差, 该差值只有年和月的部分例如, 你可能用该值设置一个往后 120 个月的提醒日期, 或检查是否从某个特定的日期后 6 月已过去指定 INTERVAL YEAR TO MONTH 如下 : INTERVAL YEAR [(year_precision)] TO MONTH 例在语法中 : year_precision 是在 YEAR 日期时间字段中数字的数目, 年精度的默认值是 2 CREATE TABLE time_example2 (loan_duration INTERVAL YEAR (3) TO MONTH); INSERT INTO time_example2 (loan_duration) VALUES (INTERVAL '120' MONTH(3)); SELECT TO_CHAR( sysdate+loan_duration, 'dd-mon-yyyy') FROM time_example2; --today s date is 26-Sep-2001 限制前面的部分要大于后面的部分, 例如 :INTERVAL '0-1' MONTH TO YEAR 是无效的, 必须写成 :INTERVAL '0-1' YEAR TO MONTH 中国科学院西安网络中心 2005 Oracle SQL 入门 9-17

18 INTERVAL DAY TO SECOND 数据类型 INTERVAL DAY TO SECOND 按照天小时分和秒存储一段时间 INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)] INTERVAL '4 5:12:10.222' DAY TO SECOND(3) Indicates 4 days, 5 hours, 12 minutes, 10 seconds, and 222 thousandths of a second.interval '123' YEAR(3). INTERVAL '7' DAY Indicates 7 days. INTERVAL '180' DAY(3) Indicates 180 days Copyright Oracle Corporation, All rights reserved. INTERVAL DAY TO SECOND 数据类型 INTERVAL DAY TO SECOND 根据天小时分和秒存储一段时间用 INTERVAL DAY TO SECOND 来表示两个日期时间值精确的差例如, 你可能用该值设置一个往后 36 个小时的提醒, 或记录一个赛跑的开始和结束之间的时间为了表示很长的时间跨度, 包括很多年, 用很高的精度, 你可以用一个很大的值表示天的一部分指定 INTERVAL DAY TO SECOND 如下 : INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)] 在语法中 : day_precision fractional_seconds_precision 是在 DAY 日期时间字段中数字的数目, 可接受的值的范围是 0 到 9, 默认是 2 是在 SECOND 日期时间字段中数字的数目, 可接受的值的范围是 0 到 9, 默认是 6 中国科学院西安网络中心 2005 Oracle SQL 入门 9-18

19 INTERVAL DAY TO SECOND 数据类型 INTERVAL DAY TO SECOND 按照天小时分和秒存储一段时间 INTERVAL '4 5:12:10.222' DAY TO SECOND(3) Indicates 4 days, 5 hours, 12 minutes, 10 seconds, and 222 thousandths of a second. INTERVAL '4 5:12' DAY TO MINUTE Indicates 4 days, 5 hours and 12 minutes. INTERVAL '400 5' DAY(3) TO HOUR Indicates 400 days 5 hours. INTERVAL '11:12: ' HOUR TO SECOND(7) indicates 11 hours, 12 minutes, and seconds Copyright Oracle Corporation, All rights reserved. INTERVAL DAY TO SECOND 数据类型例 CREATE TABLE time_example3 (day_duration INTERVAL DAY (3) TO SECOND); INSERT INTO time_example3 (day_duration) VALUES (INTERVAL '180' DAY(3)); SELECT sysdate + day_duration "Half Year" FROM time_example3; -- 今天的日期是 26-Sep-2001 中国科学院西安网络中心 2005 Oracle SQL 入门 9-19

20 用子查询语法创建表用子查询选项组合 CREATE TABLE 语句创建表并插入行 CREATE TABLE table [(column, column...)] AS subquery; 匹配表中指定的列数和子查询的列数用列名和默认值定义列 9-20 Copyright Oracle Corporation, All rights reserved. 用另一个表的行创建表创建表的第二种方法是用 AS subquery 子句, 该方法既可以创建表还可以将从子查询返回的行插入新创建的表中在语法中 : table 是表的名字 column 是列的名字, 默认值和完整性约束 subquery 是 SELECT 语句, 用来定义将要被插入到新表中的行集原则被创建的表要带指定的列名, 并且由 SELECT 语句返回的行被插入到新表中字段的定义只能包括列名和默认值如果给出了指定的列, 列的数目必须等于子查询的 SELECT 列表的列数目如果没有给出了指定的列, 表的列名应和子查询中的列名是相同的完整性规则不会被传递到新表中, 仅列的数据类型被定义中国科学院西安网络中心 2005 Oracle SQL 入门 9-20

21 用子查询创建表 CREATE TABLE dept80 AS SELECT employee_id, last_name, salary*12 ANNSAL, hire_date FROM employees WHERE department_id = 80; Table created. DESCRIBE dept Copyright Oracle Corporation, All rights reserved. 用另一个表的行创建表 ( 续 ) 幻灯片的例子创建一个名为 DEPT80 的表, 该表包含所有工作在部门 80 的雇员的详细资料, 注意 DEPT80 表的数据来自 EMPLOYEES 表你可以检验一个数据库表的存在, 并且用 isql*plus DESCRIBE 命令检查列的定义对于在 SELECT 列表中的表达式给出别名在幻灯片中表达式 SALARY*12 被给予别名 ANNSAL 如果没有别名会产生错误 : ERROR at line 3: ORA-00998: must name this expression with a column alias 教师注释为了用一个已存在的表的相同结构创建一个新表, 而不用旧表的数据, 用带 WHERE 子句的子查询, 该子查询的永远是假, 例如 : CREATE TABLE COPY_TABLE AS (SELECT * FROM employees WHERE 1 = 2); 中国科学院西安网络中心 2005 Oracle SQL 入门 9-21

22 ALTER TABLE 语句用 ALTER TABLE 语句来 : 添加一个新列修改一个已存在的列为新列定义一个默认值删除一个列 9-22 Copyright Oracle Corporation, All rights reserved. ALTER TABLE 语句在你创建一个表后, 你可能需要改变表的结构, 因为, 你可能遗漏了一个列, 或者列的定义需要改变, 或者某些列需要删除, 你可以用 ALTER TABLE 语句来做这些改变中国科学院西安网络中心 2005 Oracle SQL 入门 9-22

23 ALTER TABLE 语句用 ALTER TABLE 语句添加修改或删除列 ALTER TABLE table ADD (column datatype [DEFAULT expr] [, column datatype]...); ALTER TABLE table MODIFY (column datatype [DEFAULT expr] [, column datatype]...); ALTER TABLE table DROP (column); 9-23 Copyright Oracle Corporation, All rights reserved. ALTER TABLE 语句 ( 续 ) 你可以用 ALTER TABLE 语句添加修改和删除表中的列在语法中 : table 是表的名字 ADD MODIFY DROP 是修改类型 column 是新列的名字 datatype 是新列的数据类型和长度 DEFAULT expr 为一个新行指定默认值注 : 幻灯片给出的是删减的 ALTER TABLE 语法更多有关 ALTER TABLE 的内容在后面的课程中教师注释在 Oracle8i 和以后的版本中, 有一些 ALTER TABLE 命令的新选项, 包括从表中删除列的能力, 这些内容包含在本课程的后面中国科学院西安网络中心 2005 Oracle SQL 入门 9-23

25 添加新列用 ADD 字句添加列 ALTER TABLE dept80 ADD (job_id VARCHAR2(9)); Table altered. 新列成为最后的列 9-25 Copyright Oracle Corporation, All rights reserved. 添加新列的原则你可以添加或修改列你不能指定新添加的列出的位置, 新列将成为最后一列幻灯片中例子添加一个名为 JOB_ID 的新列到 DEPT80 表中,JOB_ID 列成为表中的最后一列注 : 如果一个表在添加新列时已包含有行, 那么, 所有行的新列被初始化为空中国科学院西安网络中心 2005 Oracle SQL 入门 9-25

26 修改列可以改变列的数据类型大小和默认值 ALTER TABLE dept80 MODIFY (last_name VARCHAR2(30)); Table altered. 对默认值的改变只影响后来插入表中的数据 9-26 Copyright Oracle Corporation, All rights reserved. 修改列你可以用带 MODIFY 子句的 ALTER TABLE 语句修改一个列, 列的修改包括修改列的数据类型, 大小和默认值原则你可以增加宽度或一个数字列的精度你可以增加数字列或字符列的宽度你可以减少一个列的宽度, 但仅在列中只包含空值或表中没有行时你可以改变数据类型, 但仅在列中只包含空值时你可以转换一个 CHAR 列到 VARCHAR2 数据类型或转换一个 VARCHAR2 列到 CHAR 数据类型仅当列中只包含空值时, 或者你不改变列的大小时对默认值的改变仅影响以后插入的列中国科学院西安网络中心 2005 Oracle SQL 入门 9-26

27 删除列用 DROP COLUMN 子句从表中删除列 ALTER TABLE dept80 DROP COLUMN job_id; Table altered Copyright Oracle Corporation, All rights reserved. 删除列你可以用带 DROP COLUMN 子句的 ALTER TABLE 语句从表中删除列, 该特性在 Oracle8i 及以后的版本中可用原则列可以有也可以没有数据用 ALTER TABLE 语句, 一次只能有一列被删除表被修改后必须至少保留一列一旦一列被删除, 它不能再恢复教师注释当一列从表中被删除时, 该表中任何其他的被用 SET UNUSED 选项标记列也被删除中国科学院西安网络中心 2005 Oracle SQL 入门 9-27

28 SET UNUSED 选项用 SET UNUSED 选项标记一个或多个未使用的列用 DROP UNUSED COLUMNS 选项删除被标记为未使用的列 ALTER TABLE table SET UNUSED (column); OR ALTER TABLE table SET UNUSED COLUMN column; ALTER TABLE table DROP UNUSED COLUMNS; 9-28 Copyright Oracle Corporation, All rights reserved. SET UNUSED 选项 SET UNUSED 选项标记一个或多个列作为不使用的, 所以, 当需求的系统资源较低时他们可以被删除, 该特性在 Oracle8i 和以后的版本中有效指定该子句不会真的从表的每一行中删除目标列 ( 即, 它不会恢复这些列所使用的磁盘空间 ), 因此,SET UNUSED 选项标记的执行响应时间会比执行 DROP 子句快一些不使用的列就好象它被删除了一样的被处理, 即使他们的列数据还保留在表的行中在一列已经被标记为不使用后, 你就不能访问该列了一个 SELECT * 查询不会从标记为不使用的列返回数据另外, 在使用 DESCRIBE 命令时, 被标记为不使用的列的名字和类型将不再显示, 并且你可以用一个与不使用列相同的名字添加一个新列到表中 SET UNUSED 信息被存储在 USER_UNUSED_COL_TABS 字典视图中 DROP UNUSED COLUMNS 选项 DROP UNUSED COLUMNS 从表中删除当前所有被标记为不使用的列, 当你想要从表中的不使用列回收额外的磁盘空间时你可以用该语句, 如果表中不包含不使用列, 该语句不返回错误 ALTER TABLE dept80 SET UNUSED (last_name); Table altered. ALTER TABLE dept80 DROP UNUSED COLUMNS; Table altered. 中国科学院西安网络中心 2005 Oracle SQL 入门 9-28

29 删除表在表中的所有数据和结构都被删除任何未决的事务都被提交所有的索引被删除你不能回退 DROP TABLE 语句 DROP TABLE dept80; Table dropped Copyright Oracle Corporation, All rights reserved. 删除表 DROP TABLE 语句删除 Oracle 表定义, 当你删除一个表时, 数据库丢失表中所有的数据, 并且所有与其相关的索引也被删除语法 DROP TABLE table 在语法中 : table 是表的名字原则所有的数据从表中删除任何视图和同义词被保留但无效任何未决的事务被提交只有表的创建者或具有 DROP ANY TABLE 权限的用户才能删除表注 :DROP TABLE 语句, 一旦被执行, 就不能撤回当你发布 DROP TABLE 语句时, Oracle 服务器不询问其行为, 如果你拥有该表或有一个高级权限, 那么, 该表立即被删除当使用所有 DDL 语句时,DROP TABLE 被自动提交中国科学院西安网络中心 2005 Oracle SQL 入门 9-29

30 改变一个对象的名字执行 RENAME 语句, 改变一个表视图序列或同义词 RENAME dept TO detail_dept; Table renamed. 你必须是对象的所有者 9-30 Copyright Oracle Corporation, All rights reserved. 重命名表另外的 DDL 语句包括 RENAME 语句, 该语句被用于改变表视图序列或同义词的名字语法 RENAME old_name TO new_name; 在语法中 : old_name 是表视图序列或同义词的旧名字 new_name 是表视图序列或同义词的新名字你必须是你重命名的对象的所有者中国科学院西安网络中心 2005 Oracle SQL 入门 9-30

31 截断表 TRUNCATE TABLE 语句 : 删除表中所有的行释放该表所使用的存储空间 TRUNCATE TABLE detail_dept; Table truncated. 不能回退用 TRUNCATE 删除的行作为选择, 可以用 DELETE 语句删除行 9-31 Copyright Oracle Corporation, All rights reserved. 截断表另一个 DDL 语句是 TRUNCATE TABLE 语句, 该语句被用于从表中删除所有的行, 并且释放该表所使用的存储空间在使用 TRUNCATE TABLE 语句时, 你不能回退已删除的行语法 TRUNCATE TABLE table; 在语法中 : table 是表的名字你必须是表的所有者, 或者有 DELETE TABLE 系统权限来截断表 DELETE 语句也可以从表中删除所有的行, 但它不能释放存储空间 TRUNCATE 命令更快一些, 用 TRUNCATE 语句删除行比用 DELETE 语句删除同样的行快一些, 原因如下 : TRUNCATE 语句是数据定义 (DDL) 语句并且不产生回滚信息截断一个表不触发表的删除触发器如果表是一个引用完整性约束的父表, 你不能截断该表, 在发布 TRUNCATE 语句之前禁用约束中国科学院西安网络中心 2005 Oracle SQL 入门 9-31

32 添加注释到表中用 COMMENT 语句添加注释到一个表或列中 COMMENT ON TABLE employees IS 'Employee Information'; Comment created. 注释能够通过数据字典视图查看 : ALL_COL_COMMENTS USER_COL_COMMENTS ALL_TAB_COMMENTS USER_TAB_COMMENTS 9-32 Copyright Oracle Corporation, All rights reserved. 添加注释到表中你可以用 COMMENT 语句给一个列表视图或快照添加一个最多 2K 字节的注释注释被存储在数据字典中, 并且可以通过下面的数据字典视图查看 COMMENTS 列 : ALL_COL_COMMENTS USER_COL_COMMENTS ALL_TAB_COMMENTS USER_TAB_COMMENTS 语法 COMMENT ON TABLE table COLUMN table.column IS 'text'; 在语法中 : table 是表的名字 column 是表中列的名字 text 是注释的文本你可以用设置注释为空串 ('') 的办法从数据库中删除一个注释 : COMMENT ON TABLE employees IS ''; 中国科学院西安网络中心 2005 Oracle SQL 入门 9-32

33 小结在本课中, 您应该已经学会如何使用 DDL 语句创建改变删除和重命名表语句 CREATE TABLE ALTER TABLE DROP TABLE RENAME TRUNCATE COMMENT 说明创建表修改表结构删除行和表结构改变表视图序列或同义词的名字从表中删除所有行并且释放存储空间添加注释到表或视图中 9-33 Copyright Oracle Corporation, All rights reserved. 小结在本课中, 您应该已经学会如何使用 DDL 命令来创建修改删除和重命名表你也学会了怎样截断表和添加注释到一个表创建表创建表用子查询基于另一个表创建表修改表修改表结构修改列宽, 改变列数据类型和添加列删除表删除行和表结构一旦执行, 该语句不能回滚重命名重命名一个表视图序列或同义词截断从表中删除所有行, 并且释放该表已使用的存储空间 DELETE 语句只删除行注释添加注释到表或列查询数据字典来查看注释中国科学院西安网络中心 2005 Oracle SQL 入门 9-33

34 练习 9 概览本章练习包括下面的主题 : 创建新表用 CREATE TABLE AS 语法创建新表修改列定义验证已经存在的表添加注释到表中删除表改变表 9-34 Copyright Oracle Corporation, All rights reserved. 练习 9 概览用 CREATE TABLE 语句创建新表, 确认新表被添加到数据库中在命令文件中的创建语句, 然后执行命令文件来创建表教师注释解释什么是表的实例图表, 告诉学生怎样解释一个表实例图表, 说明他们需要查看的条目有列名数据类型和域的长度其他的条目是可选的, 并且如果这些条目存在, 他们需要作为表定义的一部分被强制为一体对学生指出, 对于本课程, 练习是基于专门创建的表的他们需要小心, 不要修改方案中的其他表中国科学院西安网络中心 2005 Oracle SQL 入门 9-34

35 练习 9 1. 按下面的要求创建 DEPT 表, 将创建命令存入脚本文件 lab9_1.sql, 然后执行该脚本中的语句来创建表, 确认表已被创建列名 ID NAME 键类型空 / 唯一性 FK 表 FK 列数据类型 Number VARCHAR2 长度 7 25 CREATE TABLE dept (id NUMBER(7), name VARCHAR2(25)); DESCRIBE dept 2. 从 DEPARTMENTS 取数据填充的 DEPT 表, 只包括你需要的行 INSERT INTO dept SELECT department_id, department_name FROM departments; 3. 根据下面的表创建 EMP 表, 将语句保存到脚本文件 lab9_3.sql 中, 然后执行脚本中的语句创建表, 确认表已被创建列名 ID LAST_NAME FIRST_NAME DEPT_ID 键类型空 / 唯一性 FK 表 FK 列数据类型 Number VARCHAR2 VARCHAR2 Number 长度 CREATE TABLE emp 中国科学院西安网络中心 2005 Oracle SQL 入门 9-35

36 (id NUMBER(7), last_name VARCHAR2(25), first_name VARCHAR2(25), dept_id NUMBER(7)); DESCRIBE emp 第九章创建和管理表 4. 修改 EMP 表允许更长的雇员名字 (last names), 确认你的修改 ALTER TABLE emp MODIFY (last_name VARCHAR2(50)); DESCRIBE emp 5. 确认 DEPT 表和 EMP 表被存储在数据字典中 ( 提示 :USER_TABLES) SELECT table_name FROM user_tables WHERE table_name IN ('DEPT', 'EMP'); 6. 基于 EMPLOYEES 表的结构创建 EMPLOYEES2 表, 只包括 EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY 和 DEPARTMENT_ID 列, 分别将新表中的列命名为 ID, FIRST_NAME, LAST_NAME, SALARY 和 DEPT_ID CREATE TABLE employees2 AS SELECT employee_id id, first_name, last_name, salary, department_id dept_id FROM employees; 7. 删除 EMP 表 DROP TABLE emp; 8. 重命名 EMPLOYEES2 表为 EMP 中国科学院西安网络中心 2005 Oracle SQL 入门 9-36

37 RENAME employees2 TO emp; 第九章创建和管理表 9. 添加一个列到 DEPT 和 EMP 表定义, 用来描述表确认你添加的列在数据字典中 COMMENT ON TABLE emp IS 'Employee Information'; COMMENT ON TABLE dept IS 'Department Information'; SELECT * FROM user_tab_comments WHERE table_name = 'DEPT' OR table_name = 'EMP'; 10. 从 EMP 表中删除 FIRST_NAME 列, 检查表的定义来确认你的修改 ALTER TABLE emp DROP COLUMN FIRST_NAME; DESCRIBE emp 11. 在 EMP 表中, 将 DEPT_ID column 列标记为 UNUSED, 检查表定义以确认你的修改 ALTER TABLE emp SET UNUSED (dept_id); DESCRIBE emp 12. 从 EMP 表中删除所有 UNUSED 列, 检查表的定义以确认你的修改 ALTER TABLE emp DROP UNUSED COLUMNS; DESCRIBE emp 中国科学院西安网络中心 2005 Oracle SQL 入门 9-37

38 教师注释 ( 9-5 页 ) 有一个选项,CREATE GLOBAL TEMPORARY TABLE, 定义一个表为临时表, 并且对所有的会话可见, 在临时表中的数据仅对插入数据到表中的会话是可见临时表可以象常规表那样有一个持久化的定义, 而且它既可以包含会话指定也可以包含事务指定的数据, 用 ON COMMIT 关键字确定数据是会话指定的还是事务指定的临时表使用临时段, 不象永久表, 临时表和及其索引在它们被创建时不会自动分配段, 而是在执行第一个 INSERT 语句或 CREATE TABLE AS SELECT 语句时段被分配, 这意味者在第一个 INSERT 之前, 如果有一个 SELECT,UPDATE 或 DELETE 语句被执行, 那么, 表看起来是空的只有当没有会话被绑定到一个临时表上的时候, 你可以在该临时表上执行 DDL 命令 (ALTER TABLE,DROP TABLE,CREATE INDEX, 等等 ) 当一个 INSERT 在临时表上被执行时, 一个会话开始绑定到一个临时表, 该会话被一个 TRUNCATE, 在会话终止时, 或对一个事务指定的临时表做 COMMIT 或 ABORT 操作时解除绑定对于事务指定的临时表, 临时段在事务结束时被释放 ; 对于会话指定的临时表, 临时段在会话结束时被释放关于临时表和 CREATE TABLE 的更多信息, 查阅 Oracle9i Concepts, 临时表中国科学院西安网络中心 2005 Oracle SQL 入门 9-38

教学目标描述主要数据库对象创建表描述列定义时可用的数据类型改变表的定义删除改名和截断表描述每个 DML 语句插入行到表中更新表中的行从表中删除行描述约束创建

教学目标描述主要数据库对象创建表描述列定义时可用的数据类型改变表的定义删除改名和截断表描述每个 DML 语句插入行到表中更新表中的行从表中删除行描述约束创建 2 第二讲创建和管理表与简单记录操作 Copyright SWFC-CISC, 2013. All rights reserved. 教学目标描述主要数据库对象创建表描述列定义时可用的数据类型改变表的定义删除改名和截断表描述每个 DML 语句插入行到表中更新表