Oracle-007-10:Creating and Managing Table[创建和管理表]

目的
完成本课，可以作下列事情：
描述主要的数据库对象
创建表
定义表时可以使用的数据类型
变更表的定义
删除、重命名和截断表

不需要指定表的大小，大小会自动增加，会使用数据库的所有未分配空间。不过，估计一个表会占有多大空间也是很重要的。
标准命名的表名和列名必须以字母开始，表明中可以使用数字，特殊字符只能有_, $, #,且对字母的大小写不敏感，非标准命名的表以双引号包围起来,表明和列明不需小于等于30个字符，单表的列数不需小于等于1000。
要创建表，需有：创建表的权限 && 存储空间
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][, ...]);
需指定：表名、列名、列数据类型和宽度
schema是对象的集合，可以包括：表、视图、同义词、序列、存储过程、索引、clusters和数据库链接（database link）。
缺省值可以是字符串、表达式和SQL函数，但不能是另一列的名字或者伪列
数据库中的表
用 户表是用户创建的表。还有另一类表和视图的集合，称之为数据字典。这些集合由Oracle Server创建和维护，包含数据库的信息。所有的数据字典表都属于SYS用户。这些基表很少被用户使用，因为这里面的信息不易懂。用户一般使用数据字典 视图，这些视图使用了更易懂的格式来表达信息。数据字典包含了Oracle Server用户的名字、用户授予的权限、数据库对象的名字、表的约束和审计信息。
有四类数据字典视图，每一类使用不同的前缀，反应了其使用的意图。
USER_：这些视图包含了属于当前用户所拥有的对象的信息
ALL_：这些视图包含了可以被当前用户查看的对象的信息
DBA_：这些视图只能被拥有DBA角色的用户查看
V$：这些视图是数据库动态性能视图，包含数据库服务器的性能、内存和锁
常用下面三个视图：
USER_TABLES：属于该用户的表
USER_OBJECTS：属于该用户的所有数据库对象
USER_CATALOG：属于该用户的表，视图，同义词，序列；USER_CATALOG有一个同义词：CAT
数据类型
VARCHAR2(size)：变长的字符数据，长度1<=size<=4000
CHAR [(size)]：定长的字符数据（缺省宽度为1）,长度1<=size<=2000
NUMBER [(p,s)]：变长的数字,1<=p<=38,-84<=s<=127，p指的是有效位， 有效位从左边第一个不为0的值算起，不包括小数点。
P>0，对S分两种情况
s>0的两种情况：
number（5，2）：给出的值四舍五入后必须小于等于999.99，即<（5减2个9点2个9）
number（5，8）：给出的值四舍五入后必须小于0.001，即<(10的8减5次方)
S<0的情况：
number（4，-3）：给出的值四舍五入后小于等于9999000
LONG：变长的字符数据，最大2G
CLOB：字符数据，最大4G
RAW(size):RAW二进制数据，size必须指定，最大为2000
LONG RAW：可变长度的RAW二进制数据，最大2G
BLOB：二进制数据，最大4G
BFILE：外部文件存储的二进制数据，最大4G
ROWID：伪列返回的数据类型
1.当使用子查询创建表时，LONG列不拷贝
2.LONG列不能在GROUP BY子句和ORDER BY子句中使用
3.每个表只能使用一个LONG列
4.约束不能作用于LONG列
或许以CLOB代替LONG会更好
9i中的Datetime日期类型得到增强，引入了新的datetime日期类型，新的日期类型存储方式，同时对于时区(time zones)和本地时区(local time zone)也有增强。
interval year to month与interval day to second
1.year默认2位，day默认2位，范围为0-9；second默认6位，范围为0-9
2.限制：The leading field must be more significant than the trailing field. For example, INTERVAL '0-1' MONTH TO YEAR is not valid.
表设置字段类型为interval year(2) to month
/*正常执行*/
insert into aa values(interval '99' year(2))
/*正常执行*/
insert into aa values(interval '99-2' year(2) to month)
/*执行异常*/
insert into aa values(interval '99' year(3))
/*执行异常*/
insert into aa values(interval '99-2' year(2))
interval day to second用法同上
insert into aa values(interval ’4 5:12:10.222' DAY(2) TO SECOND(3))
TIMESTAMP[(fractional_seconds_precision)]
timestamp是对日期类型的扩展，存储年、月、日、小时、分、秒和微秒，并且可以指定微秒的精度，缺省为6位，范围为0-9位。
TIMESTAMP[(fractional_seconds_precision)] WITH TIME ZONE
考虑到时区，下面两个数据是相同的
TIMESTAMP '1999-04-15 8:00:00 -8:00'
TIMESTAMP '1999-04-15 11:00:00 -5:00'
前面的又可以写成：TIMESTAMP '1999-04-15 8:00:00 US/Pacific'
TIMESTAMP[(fractional_seconds_precision)] WITH LOCAL TIME ZONE
可以指定主键或唯一键的某一个列类型为timestamp with local time zone
这种类型数据输入时并不包含时区，服务器会自动包含用户本地会话中的信息
INSERT INTO time_example VALUES('15-NOV-00 09:34:28 AM');
使用子查询创建表
CREATE TABLE table
[(column, column...)]
AS subquery;
使用这种方法建表，列定义只包含列名和缺省值以及是否为空，完整性约束没有传递。对于表达式，必须在查询中给予一个别名。如果指定了列，那个列的数量必须和子查询中的相符。
改变表的定义：添加新列，编辑已存在的列，定义缺省值，删除列
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 (column);
ALTER TABLE table SET UNUSED [COLUMN] (column);
ALTER TABLE table DROP UNUSED COLUMNS;不能删除特定列，必须全部删除，unused后无法使用used重新使用该列
RENAME OBJECT TO NEW_OBJECT. 要求使用者必须是对象所有者（owner）
TRUNCATE TABLE table;
增加或修改列的情况：
1.新加入的列只会成为最后一列，不能修改列的显示顺序，初始值全部为NULL
2.只有当列中仅包含NULL（或者空行）时才可以减少列的宽度。
3.只有当列中全部为NULL时才可以更改数据类型。
4.能增加数据类型的宽度和精度
5.能增加字符类型的宽度
6.能将char转换为varchar2，如果不改变长度，或者char列的内容全部为空，你可以改变varchar2为char
7.对列缺省值的更改只影响后面插入的数据。
删除列的情况：
1.不论列有没有数据，该列都可以被删除
2.alter table drop 只能一次删除一个列
3.对表的删除至少要留一个列
4.一旦列被DROP，无法恢复。
SET UNUSED将某些列标记为不可用，此时对于用户来说这些列已经删除，已经可以添加同名的列。在适当的时候使用DROP UNUSED COLUMNS删除它们。SET UNUSED信息存储在USER_UNUSED_COL_TABS数据字典视图。
DROP TABLE table
DROP一个表后
1.一旦删除，无法恢复
2.所有数据被删除，包括索引；任何视图和同义词存在但非法；任何前面未提交的事务被提交

COMMENT ON {TABLE table | COLUMN table.column} IS 'text';
可以添加最长2000字节的注释
通过下列视图的COMMENTS列查看注释：
ALL_COL_COMMENTS,
USER_COL_COMMENTS,
ALL_TAB_COMMENTS,
USER_TAB_COMMENTS
通过添加空字符串('')来删除注释

练习（答案在卷2的308页）
1. create table dept
(id number(7), name varchar2(25));
2. insert into dept
select department_id, department_name
from departments;
3. create table emp
(id number(7), last_name varchar2(25), first_name varchar2(25), dept_id number(7));
4. alter table emp
modify (last_name varchar2(50));
5. select table_name from user_tables
6. create table employees2(id, first_name, last_name, salry, dept_id)
as
select employee_id, first_name, last_name, salary, department_id
from employees;
别忘记了AS关键字
7. drop table emp
8. rename employees2 to emp
9. comment on table dept is 'My department table'
comment on table emp is 'My employee table'
select comments,table_name from user_tab_comments
10. alter table emp
drop column first_name;
11. alter table emp
set unused column dept_id;
select * from user_unused_col_tabs
12. alter table emp
drop unused columns;

1、会话特有的临时表(会话过程数据有效)
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification> ) ON COMMIT PRESERVE ROWS；
2、事务特有的临时表 (commit后数据被truncate)
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification> ) ON COMMIT DELETE ROWS；