ORACLE 操作和管理表

巴3226

数据库对象:
:: 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语句，其默认值的处理方式将在 “操纵数据” 一课中讲述。

创建表
::创建表
CREATE TABLE dept
(deptno NUMBER(2),dname VARCHAR2(14),
loc VARCHAR2(13));
Table created.
::确认表的创建:
DESCRIBE dept

Oracle 数据库中的表
::用户表：
–由用户创建和维护的表的集合
–包含用户信息
::数据字典：
–由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。

数据类型
数据类型说明
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列。

日期时间数据类型.

数据类型说明
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 '';