Oracle 创建和管理表

1. 命名规则

表名和列名

• 必须以字母开头
• 必须在1-30个字符之间
• 必须只能包含A-Z，a-z，0-9，_,$,和#
• 必须不能和用户定义的其他对象重名
• 必须不能是Oracle的保留字

2. 创建表语句 create table

sql>create tabel 必须具有创建权限，存储空间。
必须指定

• 表名
• 字段名称，字段数据类型和字段大小

2.1 引用其他用户的表

其他用户定义的表不在当前用户的方案中
应该使用用户名作为前缀，引用其他用户定义的对象

2.2 DEFAULT选项

• 在插入的过程中，为列指定一个默认值
• 字符串，算数表达式，或者sql函数都是合法的
• 其他列的列名和伪列都是非法的
• 默认值必须满足列得数据类型定义

3. 数据类型

4. 约束语句

• 约束为表级的强制规则
• 如果对表有依赖关系，约束能防止表的删除
• 以下时有效的约束类型
  • not null 非空
  • unique 唯一
  • primary key 主键
  • foreign key 附键
  • check

4.1 约束准则

• 如果不指定约束名Oracle server自动按照SYS_Cn的格式指定约束名
• 在什么时候创建约束:
  • 建表的同时
  • 建表之后
• 可以在表级或者列级定义约束
• 可以通过数据字典视图查看约束

语法规则

语法示例：

约束效果

uninque约束

RPIMARY KEY 约束

foreign key 约束

4.2 FOREIGN KEY 约束：关键字

FOREIGN KEY	在表级指定子表中的列
REFERENCES	表示在父表中的列
ON DELETE CASCADE	当父表中的列被删除时，字表中相对应的列也被删除
ON DELETE SET NULL	字表中相应的列表空

4.3 CHECK约束

• 定义每一行必须满足的条件
• 以下的表达式是不允许的
  • 出现CURRVAL,NEXTVAL,LEVEL,和ROWNUM伪列
  • 使用SYSDATE,UID,USER,和USERENV函数
  • 在查询中涉及到其他行的值

5. 使用子查询创建表

• 使用CREATE TABLE语句和AS subquery选项，将创建表和插入数据结合起来完成
  

• 指定的列和子查询中的列要一一对应

• 通过列名和默认值定义列

  使用子查询创建表
  
  

6. 更改表 ALTER TABELE

使用alter table

• 添加一个新列
• 修改现有的列定义
• 新的列定义默认值
• 删除一列
• 重命名列
• 将表更改为只读状态

6.1 增加⼀个新列

例如

alter table dept30 add (job varchar2(9));

6.2 更改⼀个现存的列

可更改⼀个现存列的数据类型、⻓度、以及缺
对于缺省值的修改只影响到以后新加的列
例如

ALTER TABLE dept30 modify (ename varchar2(15));

6.3 删除⼀列

使⽤DROP COLUMN 来删除表中将来不使⽤的列。

6.4 对表名或者列名重命名

rename column

ALTER TABLE table_name
RENAME COLUMN col_old TO col_newname;
ALTER TABLE t_old RENAME TO t_new;

6.5 改变⼀个对象的名称

更改表、视图、同义词、序列等对象的名称，可执⾏RENAME命令(操作用户必须是对象的拥有者)

ALTER TABLE t_old RENAME TO t_new;

7. 删除表 DROP TABLE

• 表移动到回收站，purge recyclebin
• 使用PURGE子句可彻底删除表
• 表上依赖的对象失效的同时删除对象权限、

8. SET UNUSED 标记列不可用

表中数据量⾮常⼤时，如果在业务⾼峰时间直接执⾏ alter table drop column 可能会收到如下错误
ORA-01562 - failed to extend rollback segment number string

Oracle 推荐使用 SET UNUSED 选项标记⼀列(或多列)，使该列不可用。 然后，当业务量下降后再使用 DROP UNUSED column 选项删除被被标记为不可用的列.

SETUNUSED COLUMNS 用于 drop 多列时效率更⾼，

SET UNUSED COLUMNS 方法系统开销比较小，速度较快。

8.1 用法

SET UNUSED 标记列不可用

alter table xxx set unused (col1);
alter table xxx drop unused col1;

无法删除属于sys的表中的列，将会报错ora-12988

9. 为⼀个表增加注释

可以使⽤COMMENT 语句为⼀个表，或表中的某⼀列增加注释

COMMENT ON TABLE tab_name IS ’this is data table’;
COMMENT ON COLUMN tab_name.col IS ‘is col’

可通过查询⼀下数据字典视图进⾏查询

• ALL_COL_COMMENTS
• USER_COL_COMMENTS
• ALL_TAB_COMMENTS
• USER_TAB_COMMENT