■表和视图不可以重名,但表可以和索引、约束同名。表名和列名都是不区分大小写的，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; ------溢出的数据存在myts01表空间
■通过查询从一个表创建另一个表
CREATE TABLE table_name AS SELECT <query>;
这个语法经常用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选项来避免生成重做日志文件记录,减少创建表时所需的时间
■更改表
◆ 添加或删除表中的列,或者修改表中列的定义(包括数据类型、长度、默认值，以及NOT NULL约束等)
◆ 对表进行重新命名
◆ 将表移动到其他数据段或表空间中，以便重新组织表。
◆ 添加、修改或删除表中的约束条件
◆ 激活或禁用表中的约束条件、触发器等。
■添加列
语法：ALTER TABLE [schema.]table_name ADD (column_definition);
新添加的列总是位于表的末尾。对于现有的行而言,新增列的值为NULL,即无值.column_definition部分包括列名、列的数据类型
和将具用的任何默认值.
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> /
