分类： Oracle
2007-03-28 07:35:36
第16章 管理表 2006.12.06
■表和视图不可以重名,但表可以和索引、约束同名。表名和列名都是不区分大小写的，Oracle会先把名字转换为大写，
再把它们存储在数据字典中。如果名字中用双引号("")括起来，它会在oracle数据字典中成为区分大小写的名字.
注意：
大对象数据类型的列不能出现在WHERE,GROUP BY或ORDER BY子句中。
不能在SQL*Plus等环境中查询、显示大对象类型的数据，也不能通过INSERT语句插入大对象类型的数据，否则会出错。
■如果将表的存储参数MAXEXTENTS设置为UNLIMITED,那么表就可以不受限制地自动获取所需要的存储空间,并且减少浪费
存储空间和产生存储碎片的可能.
■创建临时表(事务临时表/会话临时表)
如果创建临时表时没有使用ON COMMIT关键字，则默认创建的是事务临时表。通过指定ON COMMIT DELETE ROWS关键字，
也可以指定创建事务临时表。
■创建事务临时表
SQL> create global temporary table temp1
2 (id number(9) primary key,
3 name varchar2(20));
事务临时表的数据只在当前事务内可以查看，当使用COMMIT或ROLLBACK结束事务后，其临时数据会被自动清除。
■创建会话临时表
创建临时表时通过ON COMMIT PRESERVE ROWS关键字指定创建会话临时表。
SQL> create global temporary table temp2
2 (id number(9) primary key,
3 name varchar2(20)
4 )
5 on commit preserve rows;
区别：
事务临时表是指数据只在当前事务内有效的临时表。
会话临时表是指数据只在当前会话内有效的临时表。
■标准表与索引表的区别
标准表 索引表
使用ROWID来惟一标识一行记录，并不一定要指定主键 使用主键来惟一标识一行记录，必须指定主键
对记录的访问是基于ROWID的 对记录的访问是基于主键的，或逻辑ROWID
通过顺序扫描访问返回的记录 通过全索引扫描访问返回的记录
ROWID伪列中保存的是物理ROWID ROWID伪例中保存的是逻辑ROWIDE
■创建一个索引结构表
SQL> create table iot1
2 (ID number(9),
3 name varchar2(20),
4 address varchar2(30),
5 note varchar2(40),
6 constraint pk_id primary key (id) validate
7 )
8 organization index
9 pctthreshold 40
10 including address
11 overflow tablespace myts01;
■通过查询从一个表创建另一个表
CREATE TABLE table_name AS SELECT
这个语法经常用CTAS来表示.
在子查询中可以引用一个或多个表（或视图），查询结果集中包含的列即是新表中定义的列，并且查询到的记录都会插入到新表中.
在使用CTAS创建表时要注意如下几点：
◆ 可以修改新表中列的名称，但是不能修改列的数据类型和长度。新表中所有列的数据类型和长度都必须与查询列一致。
◆ SELECT语句中不能包含大对象数据类型和long数据类型
◆ 约束条件及列的默认值定义等都不会被复制
◆ 建议使用NOLOGGING选项。因为如果不使用NOLOGGING选项，则每插入一条记录都将会产生重做日志信息，占用了空间和时间。在决定是否使用
NOLOGGING选项时，必须综合考虑所获得的收益和风险。通常只需要在创建大表时才使用NOLOGGING子句，以获得较大的性能提升。
SQL> create table new_emp
2 as
3 select * from emp
4 noglogging;
使用CTAS创建表的时候不能指定表空间，否则会出错.
SQL> create table new_emp
2 as
3 select * from emp
4 nologging
5 tablespace users;
tablespace users
*
第 5 行出现错误:
ORA-00933: SQL 命令未正确结束
■只复制表的结构,而不复制数据.
SQL> create table new_emp_1
2 as
3 select * from emp
4 where 1=2;
■只复制表的几个列的结构和数据,并更改列名.使用NOLOGGING选项来避免生成重做日志文件记录,减少创建表时所需的时间
SQL> desc emp;
名称 是否为空? 类型
----------------------------------------------------------------- -------- --------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> create table new_emp_2
2 as
3 select empno,ename empname,mgr manager from emp
4 nologging;
表已创建。
SQL> desc new_emp_2
名称 是否为空? 类型
----------------------------------------------------------------- -------- ---------------------------
EMPNO NUMBER(4)
EMPNAME VARCHAR2(10)
MANAGER NUMBER(4)
SQL> select * from new_emp_2;
EMPNO EMPNAME MANAGER
---------- ---------- ----------
7369 SMITH 7902
7499 ALLEN 7698
7521 WARD 7698
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7782 CLARK 7839
7788 SCOTT 7566
7839 KING
7844 TURNER 7698
7876 ADAMS 7788
7900 JAMES 7698
7902 FORD 7566
7934 MILLER 7782
1234 JACKLU
已选择15行。
SQL>
■更改表
◆ 添加或删除表中的列,或者修改表中列的定义(包括数据类型、长度、默认值，以及NOT NULL约束等)
◆ 对表进行重新命名
◆ 将表移动到其他数据段或表空间中，以便重新组织表。
◆ 添加、修改或删除表中的约束条件
◆ 激活或禁用表中的约束条件、触发器等。
■添加列
语法：ALTER TABLE [schema.]table_name ADD (column_definition);
新添加的列总是位于表的末尾。对于现有的行而言,新增列的值为NULL,即无值.column_definition部分包括列名、列的数据类型
和将具用的任何默认值.
SQL> desc department
名称 是否为空? 类型
----------------------------------------- -------- -------------------
DEPTID NOT NULL NUMBER(9)
DEPTNO NOT NULL CHAR(2)
NAME VARCHAR2(20)
ADDRESS VARCHAR2(30)
SQL> select * from department;
DEPTID DE NAME ADDRESS
---------- -- -------------------- ------------------------------
5 5 航天航空系统 4 号楼
SQL> alter table department add
2 (
3 leader varchar2(20),
4 updatedate date default sysdate
5 );
表已更改。
SQL> desc department;
名称 是否为空? 类型
----------------------------------------- -------- --------------------
DEPTID NOT NULL NUMBER(9)
DEPTNO NOT NULL CHAR(2)
NAME VARCHAR2(20)
ADDRESS VARCHAR2(30)
LEADER VARCHAR2(20)
UPDATEDATE DATE
SQL> select * from department;
DEPTID DE NAME ADDRESS LEADER UPDATEDATE
---------- -- -------------------- ------------------------------ -------------------- --------------
5 5 航天航空系统 4 号楼 09-12月-06
在添加新列的时候,如果该表中已经有行记录了,那么就不能指定NOT NULL约束.
这时就需要用一个DEFAULT子句,再添加一个NOT NULL约束来完成这样的工作,如下:
SQL> alter table department add
2 (
3 node varchar2(40) not null
4 );
alter table department add
*
第 1 行出现错误:
ORA-01758: 要添加必需的 (NOT NULL) 列, 则表必须为空
SQL> alter table department add
2 (
3 node varchar2(40) default '备注' not null
4 );
表已更改。
■修改列
语法:ALTER TABLE [schema.]table_name MODIFY (column_name new_attributes);
SQL> desc department;
名称 是否为空? 类型
----------------------------------------------------------------- -------- -------------------------
DEPTID NOT NULL NUMBER(9)
DEPTNO NOT NULL CHAR(2)
NAME VARCHAR2(20)
ADDRESS VARCHAR2(30)
LEADER VARCHAR2(20)
UPDATEDATE DATE
NODE NOT NULL VARCHAR2(40)
SQL> alter table department modify
2 (
3 deptno char(6),
4 node varchar2(80)
5 );
表已更改。
SQL> desc department;
名称 是否为空? 类型
----------------------------------------------------------------- -------- --------------------------
DEPTID NOT NULL NUMBER(9)
DEPTNO NOT NULL CHAR(6)
NAME VARCHAR2(20)
ADDRESS VARCHAR2(30)
LEADER VARCHAR2(20)
UPDATEDATE DATE
NODE NOT NULL VARCHAR2(80)
■下面以删除department表中的updatedate列的sysdate默认值为例,介绍删除列的约束.
SQL> alter table department modify
2 (updatedate default null);
表已更改。
SQL> insert into department
2 (deptid,deptno,name,address)
3 values
4 (4,'04','航天航空系','4 号楼');
已创建 1 行。
SQL> select deptid,deptno,name,address,updatedate,node from department;
DEPTID DEPTNO NAME ADDRESS UPDATEDATE NODE
---------- ------ -------------------- ------------------------------ -------------- ----------------
5 5 航天航空系统 4 号楼 09-12月-06 备注
4 04 航天航空系 4 号楼 备注
■对列定义不是可以任意修改的,下面是用于修改列定义的一些原则
◆ 可以增大字符型列的长度和数据值型列的精度.如果表中有许多行,那么增大一个CHAR列的长度将需要许多资源,因为所有这些行的列数据
都需要添加空格以添补额外增大的长度.
◆ 如果相关列中所有的数据都可以符合新的长度,那么可以减小VARCHAR2列的长度,并减小数值型列的精度(precision),或者增大一个数值型
列的尺度(scale)
◆ 如果参数BLANK_TRIMMING被设置TRUE,那么可以降低一个非空CHAR列的长度.
◆ 为了更改数据类型,相关的列值必须为NULL.
◆ 如果不减小其长度,那么可以把数据类型从CHAR更改为VARCHAR2或反过来,即使相关的列不为空也可以.
■删除列
直接删除(全部删除)
语法:ALTER [schema.]table_name
DROP (colum_names) [CASCADE CONSTRAINTS];
可以在括号中使用多个列名,每个列名用逗号分隔.相关列的索引和约束也会被删除.如果删除的列是一个多列约束的组成部分,
那么就必须指定CASCADE CONSTRAINTS选项,这样才会删除相关的约束
SQL> alter table department
2 drop (updatedate,node)
3 cascade constraints;
SQL> desc department;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
DEPTID NOT NULL NUMBER(9)
DEPTNO NOT NULL CHAR(6)
NAME VARCHAR2(20)
ADDRESS VARCHAR2(30)
LEADER VARCHAR2(20)
■将其标记为UNUSED状态(部分删除)
如果要删除一个大表中的列,由于必须对每条记录进行处理,故删除操作可能会执行很长的时间.为了避免在数据库使用高峰期间由于执行删除列
的操作而占用过多的系统资源,可以暂时将删除的列的设置为UNUSED状态.
语法:ALTER TABLE [schema.]table_name
SET UNUSED (colum_names) [CASCADE CONSTRAINTS];
SQL> alter table department set unused
2 (address,leader)
3 cascade constraints;
从用户角度来看,被设置为UNUSED状态的列与被删除的列之间是没有区别的,都无法通过查询或在数据字典中看到,并且可以为表添加与UNUSED状态
的列具有相同名称的新列.但是实际上UNUSED状态的列仍然被保存在表中,它们所占用的存储空间并没有被释放.
表中被标记为UNUSED状态的列可以在以后适当的时候再删除,其语法:
ALTER TABLE [schema.]table_name DROP UNUSED COLUMNS;
SQL> alter table department
2 drop unused columns;
在数据字典视图USER_UNUSED_COL_TABLS,ALL_UNUSED_COL_TABS和DBA_UNUSED_COL_TABS中可以查看数据库中哪些表有几个被标记为UNUSED状态.
■给表或列添加注释
SQL> comment on table new_emp IS
2 '这是一个通过CTAS方法创建的表.
3 既创建了表的结构和表的记录';
如果要想显示关于表的注释,可以查询DBA_TAB_COMMENTS,ALL_TAB_COMMENTS或者USER_TAB_COMMENTS.
SQL> select table_name,table_type,comments
2 from user_tab_comments
3 where table_name='NEW_EMP';
TABLE_NAME TABLE_TYPE COMMENTS
------------------------------ ----------- -----------------------------------------------
NEW_EMP TABLE 这是一个通过CTAS方法创建的表.
■给列或列添加注释
SQL> comment on column new_emp.ename IS
2 '这是雇员的姓名';
如果要想显示关于列的注释,可以查询DBA_COL_COMMENTS,ALL_COL_COMMENTS或者USER_TAB_COMMENTS.
SQL> select table_name,column_name,comments
2 from user_col_comments
3 where table_name='NEW_EMP'
4* AND column_name='ENAME'
TABLE_NAME COLUMN_NAME COMMENTS
------------------------------ ------------------------------ ------------------------------
NEW_EMP ENAME 这是雇员的姓名
■重新命名表和重新组织表
当重新命名表时,oracle自动把旧表上的视图、对象权限和约束条件转换到新表名上，但oracle会使所有与旧表相关的对象（如视图、同义词、
存储过程、函数）失效，如果要使用，需要重新定义或编译。
■RENAME语句
语法： RENAME old_tablename TO new_tablename
■ALTER TABLE语句
SQL> ALTER TABLE new_emp RENAME TO new_emp_change;
■重新组织表
SQL> alter table new_emp MOVE;
■重新组织表并移到其它表空间中
SQL> alter table new_emp move
2 tablespace myts01;
使用ALTER TABLE ... MOVE 语句重新组织表时，有如下几个值得注意的地方。
◆ 直接到表被完全移到新的数据段中之后,Oracle才会删除原来的数据段.因此在进行移动时,必须保证表空间有足够的空闲空间.
◆ 执行ALTER TABLE ... MOVE语句重新组织表时,ROWID会发生改变,从而导致表的所有有索引转变为无效状态,所以在重新组织表
之后必须重新建立索引.
◆ 如果表中包含LOB列,这个语句可用于将表连同用户明确指定的LOB数据和LOB索引段(与该表相关的)一起移动.如果没有指定,则
默认不多动LOB数据和LOB索引段.
■删减表和删除表
■删减表(p530)
删减表就是删除表中所有的记录,使表成为一个只有结构而没有数据的一个空表.
在Oracle中,如果要删除表中所有的记录,可以使用如下三种方法之一
方法一:
SQL> delete from new_emp;
方法二:
SQL> drop table new_emp;
SQL> create table new_emp
AS select * from emp
nologging;
方法三:(最佳方法)
SQL> truncate table new_emp;
TRUNCATE语句提供了一种快速、高效、最低代价的删除表中所有记录的方法。TRUNCATE语句属于DDL语句，不会产生任何回退信息，
并且是被自动立即提交的。因此TRUNCATE操作也不能被回退。
在执行TRUNCATE语句时，不会影响到与被删减表相关的任何数据库对象和授权，也不会触发表中定义的触发器。此外，在对表进行
删减后，已经为表分配的存储空间将被回收。无论从内容上还是从结构上看，删减后的表都成为了一个空表。
TRUNCATE语句删减表中所有记录是最佳的方法。
■删除表
语法: drop table [schema.]table_name [cascade constraints]
SQL> drop table new_emp;
SQL> drop table new_emp CASCADE CONSTRAINTS;
■约束分类
- NOT NULL（非空）约束
- UNIQUE（唯一）约束
- CHECK（检查）约束
- PRIMARY KEY（主键）约束
- FOREIGN KEY（外键）约束
■约束的状态
-----------------------------------------------
分类方式 状态
-----------------------------------------------
检查新数据 激活 ENABLE
禁用 DISABLE
检查老数据 验证 VALIDATE
非验证 NOVALIDATE
两类状态的组合 激活验证 ENABLE VALIDATE
激活非验证 ENABLE NOVALIDATE
禁用验证 DISABLE VALIDATE
禁用非验证 DISABLE NOVALIDATE
■列级定义
语法：column [CONSTRAINT constraint_name] constraint_type [condition]
■表级定义
语法：[CONSTRAINT constraint_name] constraint_type ([col1,col2,...] | [condition])
注意：不能在具有大对象（CLOB，NCLOB，BLOB，BFILE）、二进制（LONG和LONG RAW）或
TIMESTAMP WITH TIMEZONE数据类型的列上定义约束。
SQL> create table student
2 (
3 stuid number(9) NOT NULL, ---> 列级定义
4 stuno char(10),
5 name varchar2(20) NOT NULL, ---> 列级定义
6 sex char(1),
7 birthday date,
8 photo blob,
9 deptid number(9),
10 CONSTRAINT pk_student PRIMARY KEY (stuid) VALIDATE, ---> 表级定义
11 CONSTRAINT chk_sex CHECK (sex in('1','0')) VALIDATE, ---> 表级定义
12 CONSTRAINT fk_deptid FOREIGN KEY (deptid) REFERENCES department(deptid) VALIDATE ---> 表级定义
13 )
SQL> /
