ORACLE 操作和管理表

数据库对象:
:: Table：存储数据
:: View：来自一个或多个表的数据的子集
:: Sequence：数字值发生器
:: Index：改善一些查询的性能
:: Synonym：给对象一个可替代的名字
	不需要指定表的大小，表的大小最终由作为一个整体分配给数据库的空间的数量定义。但是随着时间的过去一个表将使用多少空间是重要的。
	表结构可以被联机修改.
	当使用AS子查询子句时，列定义可以忽略。表在创建时没有数据，除非指定了一个查询。行通常用INSERT语句添加。

表命名和列命名规则

依照命名Oracle数据库对象的标准规则来命名数据库表和列：
:: 表名和列名必须由一个字母开始，长度在 1–30 之间。
:: 名字只能包含 A–Z, a–z, 0–9, _ (下划线)，$ 和 # (合法字符，但建议不要使用)。
:: 同一个Oracle服务器用户所拥有的对象名字不能重复。
:: 名字不能用Oracle服务器的保留字。
::名字是大小写不敏感的，例如， EMPLOYEES 与 eMPloyees 或 eMpLOYEES 作为同一个名字来处理

CREATE TABLE语句.
::必须有：/./././.
	–CREATE TABLE权限
	–一个存储区域
CREATE TABLE [schema.]table(column datatype [DEFAULT expr][, ...]);
DEFAULT expr 指定默认值，在INSERT语句省略值时使用

::指定：
	–表名
	–列名、列数据类型和列的大小
create table是DDL语句,DDL语句用来创建,修改,或删除oraclew数据库的结构.这些语句会立即作用于DB,并且他们还将信息记录在数据字典中.
数据库管理员用数据控制语言 (DCL) 语句，授予权限给用户。
			引用另一个用户的表
::表属于另一个用户，不在该用户的方案中
::在那些表名字的前面使用所有者的名字作为前缀

引用另一个用户的表
	方案 (schema) 是对象的集合，方案对象直接反映数据在数据库中的逻辑结构，方案对象包括表、视图、同义词、序列、存储过程、索引、集群和数据库链接。
	如果一个表不属于本用户，那么，其所有者的名字必须放在表名的前面，例如，如果一个方案命名为USER_B，并且USER_B有一个表EMPLOYEES，那么，其他用户用下面的语句从表中取回数据：
SELECT *
FROM user_b.employees;

DEFAULT选项
::在插入时，为一个列指定一个默认值
...hire_date DATE DEFAULT SYSDATE,...

::文字值、表达式或者SQL 函数都是合法的值	././././.
::不可以是另一个列名或者伪列的值(如,伪列nextval或currval,rownum)
::默认数据类型必须与列的数据类型匹配		/./././.

   一个列可以用DEFAULT选项给予一个默认值，列该选项防止插入时输入空值到列中。

   对于带有DEFAULT关键字的INSERT和UPDATE语句，其默认值的处理方式将在 “操纵数据” 一课中讲述。


[b]创建表[/b]
::创建表

	CREATE TABLE dept
	(deptno NUMBER(2),dname VARCHAR2(14),
	loc VARCHAR2(13));
Table created.

::确认表的创建:
DESCRIBE dept



[color=red]Oracle 数据库中的表[/color]
::用户表：
	–由用户创建和维护的表的集合
	–包含用户信息
::数据字典：
	–由Oracle 服务器创建和维护的表的集合
	–包含数据库信息

在Oracle数据库中有另一个表和视图的集合称为数据字典 (data dictionary)，该集合由Oracle服务器创建和维护，其中包含有关数据库的信息。

	全部数据字典表的所有者是用户SYS。数据字典表的基表很少被用户访问，因为其中的信息不容易理解，因此，用户一般是访问数据字典视图，因为视图中的信息是以容易理解的格式表示的。存储在数据字典中的信息包括Oracle服务器用户的名字，被授予用户的权限，数据库对象名，表结构和审计信息。

前缀		说明
USER_		这些视图包含关于用户所拥有的对象的信息。
ALL_		这些视图包含所有用户可访问的表 (对象表和相关的表) 的信息。
DBA_		这些视图是受限制的视图，它们只能被分配有 DBA 角色的用户所访问。
V$		这些视图是动态执行的视图，包含数据库服务器的性能、存储器和锁的信息。

查询数据字典
::查看本用户所拥有的表的名称
select table_name from user_tables;

::查看本用户所拥有的不同的对象类型
SELECT DISTINCT object_type FROM user_objects ;

查看本用户所拥有的表、视图、同义词和序列
select * from user_catalog;
	or
select * from cat;
查询数据字典:
	来看看你所拥有的各处数据库对象.
:user_tables
:user_objects
:user_catalog

	注：USER_CATALOG 有一个称为 CAT 的同义词，你可以在 SQL 语句中用该同义词代替 USER_CATALOG。


[color=red]数据类型[/color]

数据类型		说明
VARCHAR2(size)		可变长度的字符数据
CHAR(size) 		固定长度的字符数据
NUMBER(p,s)		可变长度的数字数据
DATE 			日期和时间值
LONG 			最大2G的可变长度字符数据
CLOB			最大4G的字符数据
RAW and LONG RAW	原始二进制数据
BLOB			最大4G的二进制数据
BFILE			最大4G的，存储在外部文件中的二进制数据
ROWID			一个64进制的数制系统，表示表中一行的唯一地址


VARCHAR2(size)	
可变长度字符数据（必须指定最大字符数：最小字符数是 1；最大字符数是 4000）

CHAR [(size)]
固定长度字符数据，长度的大小以字节为单位(默认和最小字符数为 1；最大字符数为 2000)

NUMBER [(p,s)]
数字，精度为p，小数为s (p是小数数字的总长度，s是小数点右边的数字长度；p的范围从1到38，  s的范围从-84到127)

DATE
日期和时间值，从公元前4712.1.1到公元9999.12.31

LONG
最大2G的可变长度字符数据

CLOB
最大4G的字符数据

RAW(size)
原始二进制数据 (必须指定最大长度，最大长度为 2000)

LONG RAW
可变长度原始二进制数据，最大2G

BLOB
二进制数据，最大4G

BFILE
二进制数据存储在一个外部文件中；最大到4G

ROWID
十六进制串，表示行在所在的表中唯一的行地址。该数据类型主要用于返回ROWID伪列



数据类型 (续)	./././.
:: 在用子查询创建表时，LONG列不会被复制。././././.
:: LONG 列不能包括在GROUP BY或ORDER BY子句中。
:: 在每个表中只能有一个LONG列。
:: 在LONG列上不能定义约束。
:: 通常用情况下使用CLOB列而不是LONG列。

	Oracle8引入了大对象 (LOB) 数据类型，它可以存储大的和非结构化的数据，例如文本、图象、视频和空间数据，最大4G。LONG列可以容易地移动到LOB列。


[b]日期时间数据类型.[/b]


数据类型		说明
TIMESTAMP		带小数秒的日期
INTERVAL YEAR TO MONTH	作为年和月的时间间隔存储
INTERVAL DAY TO SECOND	作为天、小时、分和秒的时间间隔存储

数据类型		说明
TIMESTAMP		允许带小数秒的时间被作为日期存储。有一些变异的数据类型。

INTERVAL YEAR TO	允许时间作为年和月的间隔被存储
MONTH

INTERVAL DAY TO		允许时间作为天、小时、分和秒的间隔被存储
SECOND

日期时间数据类型
::TIMESTAMP数据类型是DATE数据类型的一种扩展
::它存储DATE数据类型的年、月和日，加小时、分和秒值，以及秒的小数值

::TIMESTAMP数据类型被指定如下：
TIMESTAMP[(fractional_seconds_precision)]
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),
...);

start_date数据类型是TIMESTAMP，精度7指示小数秒的精度，如果不指定，小数秒的默认精度是6。

假定插入两行到NEW_EMPLOYEES表中，输出展示了显示的差异。
(DATE 数据类型以DD-MON-RR格式显示)：

SELECT start_date FROM new_employees;
17-JUN-87 12.00.00.0000000 AM
21-SEP-89 12.00.00.0000000 AM
(日-月-年 时.分.秒)


TIMESTAMP WITH TIME ZONE 数据类型
::TIMESTAMP WITH TIME ZONE是TIMESTAMP的一个变量，它对TIMESTAMP值进行一个时区转换

::在本地时间和UTC 之间，小时和分钟的时区转换是不同的

TIMESTAMP[(fractional_seconds_precision)]
WITH TIME ZONE

日期时间数据类型

UTC代表协调世界时—以前的格林尼治标准时间。如果两个TIMESTAMP WITH TIME ZONE在UTC中代表同一时刻，它们的值被认为是相同的，而不管存储在数据中的TIME ZONE偏移。
因为TIMESTAMP WITH TIME ZONE也可以存储时区信息，它特别适合记录那些必须组合或协调地理区域的日期信息。

例如，
TIMESTAMP '1999-04-15 8:00:00 -8:00'
与
TIMESTAMP '1999-04-15 11:00:00 -5:00'
是相同的。

美国西部标准时间 8:00 a.m. 和东部标准时间 11:00 a.m. 是相同的。
该时间也可以被指定为：
TIMESTAMP '1999-04-15 8:00:00 US/Pacific'

注：小数秒精度指定SECOND日期时间字段的小数部分数字的数目，其范围是0到9，默认是6。

TIMESTAMPWITHLOCALTIME数据类型
::TIMESTAMPWITHLOCALTIME ZONE是TIMESTAMP的另一个变量，它对TIMESTAMP值进行一个时区转换

::存储在数据库中的数据被格式化为数据库时区
::时区的转换不被作为列数据的一部分存储；Oracle 以本地会话时区返回数据

::TIMESTAMP WITH LOCAL TIME ZONE数据类型被如下指定：TIMESTAMP[(fractional_seconds_precision)]WITH LOCAL TIME ZONE


日期时间数据类型
	不像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
----------------------------
15-NOV-00 09.34.28.000000 AM

TIMESTAMP WITH LOCAL TIME ZONE类型适合于两层应用程序，在其中你可以用客户系统的时区显示日期和时间。

	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.


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。

用子查询语法创建表
::用子查询选项组合CREATE TABLE语句创建表并插入行匹配中指定的列数子查询的列数.

	CREATE TABLE table[(column, column...)] AS subquery;
subquery 是 SELECT 语句，用来定义将要被插入到新表中的行集

::用列名和默认值定义列

创建表的第二种方法是用AS subquery子句，该方法既可以创建表还可以将从子查询返回的行插入新创建的表中。


 原则:
:: 被创建的表要带指定的列名，并且由SELECT语句返回的行被插入到新表中。
:: 字段的定义只能包括列名和默认值。(如果原表中有其他的约束等,不会带过来.)
:: 如果给出了指定的列，列的数目必须等于子查询的SELECT列表的列数目。
:: 如果没有给出了指定的列，表的列名应和子查询中的列名是相同的。
:: 完整性规则不会被传递到新表中，仅列的数据类型被定义。/./././.

SQL> create table a(jobs,sals) tablespace users 
as select job,sal from emp;
表已创建。
SQL> desc emp;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- -------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 SAL                                                NUMBER(7,2)

SQL> desc a
 名称                       是否为空? 类型
 --------------------------- -------- ------------------
 JOBS                                 VARCHAR2(9)
 SALS                                 NUMBER(7,2)


SQL> alter table a
  2  add (name varchar2(10));

表已更改。

SQL> desc a
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ---------------
 JOBS                                               VARCHAR2(9)
 SALS                                               NUMBER(7,2)
 NAME                                               VARCHAR2(10)

SQL> alter table a
  2  modify (name varchar(10) default 'kjat')
  3  ;

表已更改。

SQL> insert into a
  2  values('KJ',1200,default);

已创建 1 行。


SQL> select * from a;

JOBS            SALS NAME
--------- ---------- ----------
CLERK            800
SALESMAN        1600
ANALYST         3000
CLERK           1300
KJ              1200 kjat

已选择15行。

SQL> alter table a			
  2  modify (name varchar2(10));	没修改的default还存在.

表已更改。

SQL> insert into a
  2  values ('KJS',5000,default);

已创建 1 行。

SQL> select * from a;

JOBS            SALS NAME
--------- ---------- ---------
CLERK            800
SALESMAN        1600
SALESMAN        1250
ANALYST         3000
CLERK           1300
KJ              1200 kjat
KJS             5000 kjat

为了用一个已存在的表的相同结构创建一个新表，而不用旧表的数据，用带WHERE子句的子查询，该子查询的永远是假，
例如：
CREATE TABLE COPY_TABLE AS
(SELECT *
FROM employees
WHERE 1 = 2);

	ALTER TABLE语句
  用ALTER TABLE语句来：(使用alter table改变表的结构)
	::添加一个新列
	::修改一个已存在的列
	:;为新列定义一个默认值
	::删除一个列

	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);
	or
ALTER TABLE table
DROP COLUMN column;

新列成为表的最后一列.
alter table dept80
add (job_id varchar2(9));
Table altered.

添加新列的原则:
:: 你可以添加或修改列。
:: 你不能指定新添加的列出的位置，新列将成为最后一列。
::如果一个表在添加新列时已包含有行，那么，所有行的新列被初始化为空。



		修改列
::可以改变列的数据类型、大小和默认值
alter table dept80
modify (last_name varchar2(30));
Table altered. 

::对默认值的改变只影响后来插入表中的数据


:::::::::列的修改包括修改列的数据类型，大小和默认值。
 原则
:: 你可以增加宽度或一个数字列的精度。
:: 你可以增加数字列或字符列的宽度。
:: 你可以减少一个列的宽度，但仅在列中只包含空值或表中没有行时。	/./././
:: 你可以改变数据类型，但仅在列中只包含空值时。			/././././
:: 你可以转换一个CHAR列到VARCHAR2数据类型或转换一个VARCHAR2列到 CHAR 数据类型仅当列中只     包含空值时，或者你不改变列的大小时。

:: 对默认值(default)的改变仅影响以后插入的列。


name列中最多是kjat有四个字符.所以改成>=4个就可以了.
SQL> alter table a
  2  modify(name varchar2(3));
modify(name varchar2(3))
       *
ERROR 位于第 2 行:
ORA-01441: 无法减小列长度, 因为一些值过大


SQL> alter table a
  2  modify(name varchar2(4));   /5/...

表已更改。


	删除列

用DROP COLUMN 子句从表中删除列
ALTER TABLE dept80
DROP COLUMN job_id; 
Table altered.

SQL> alter table a
  2  add (id number);

SQL> desc a;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ------------------
 JOBS                                               VARCHAR2(9)
 SALS                                               NUMBER(7,2)
 NAME                                               VARCHAR2(4)
 ID                                                 NUMBER
SQL> alter table a
  2  drop column id;

SQL> desc a;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ------------------
 JOBS                                               VARCHAR2(9)
 SALS                                               NUMBER(7,2)
 NAME                                               VARCHAR2(4)

SQL> alter table a
  2  add (id number);

SQL> alter table a
  2  drop (id);
表已更改。

SQL> desc a;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- --------------
 JOBS                                               VARCHAR2(9)
 SALS                                               NUMBER(7,2)
 NAME                                               VARCHAR2(4)



原则:
:: 列可以有也可以没有数据。	/./././.
:: 用ALTER TABLE语句，一次只能有一列被删除。/././.
:: 表被修改后必须至少保留一列。/./././
:: 一旦一列被删除，它不能再恢复。./././

   ./././././
   当一列从表中被删除时，该表中任何其他的被用SET UNUSED选项标记列也被删除。


	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;


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.


删除表

:: 在表中的所有数据和结构都被删除
:: 任何未决的事务都被提交
:: 所有的索引被删除		././././
:: 你不能 回退 DROP TABLE语句

DROP TABLE dept80;
Table dropped.


	DROP TABLE语句删除Oracle表定义，当你删除一个表时，数据库丢失表中所有的数据，并且所有与其相关的索引也被删除

语法:
drop table table;

  原则:
:: 所有的数据从表中删除。
:: 任何视图和同义词被保留但无效。	/./././
:: 任何未决的事务被提交。		/./././
:: 只有表的创建者或具有DROP ANY TABLE权限的用户才能 删除表。


DROP TABLE语句，一旦被执行，就不能撤回。当你发布DROP TABLE语句时，Oracle服务器不询问其行为，如果你拥有该表或有一个高级权限，那么，该表立即被删除。当使用所有DDL语句时，DROP TABLE被自动提交。



			改变一个对象的名字
::执行RENAME语句，改变一个表、视图、序列或同义词

	RENAME dept to detail_dept;
	Table renamed.
::你必须是对象的所有者

这个命令是DDL语句.

语法:
	rename old_name to new_name;


截断表

::TRUNCATE TABLE语句：
	–删除表中所有的行
	–释放该表所使用的存储空间
::不能回退用TRUNCATE删除的行		//DDL语句.
::作为选择，可以用DELETE语句删除行
::必须是表的所有者,或有DELETE TABLE　系统权限来截断表．

语法:	TRUNCATE TABLE table;

	DELETE语句也可以从表中删除所有的行，但它不能释放存储空间。TRUNCATE命令更快一些，用TRUNCATE语句删除行比用DELETE语句删除同样的行快一些，原因如下：

:: TRUNCATE语句是数据定义 (DDL) 语句。并且不产生回滚信息。
:: 截断一个表不触发表的删除触发器。
:: 如果表是一个引用完整性约束的父表，你不能截断该表，在发布TRUNCATE语句之前禁用约束。



添加注释到表中
:: 用COMMENT语句添加注释到一个表或列中
:: 注释能够通过数据字典视图查看：
	–ALL_COL_COMMENTS
	–USER_COL_COMMENTS
	–ALL_TAB_COMMENTS
	–USER_TAB_COMMENTS


添加注释到表中
你可以用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 '';