ORACLE 操作和管理表
数据库对象::: Table：存储数据:: View：来自一个或多个表的数据的子集:: Sequence：数字值发生器:: Index：改善一些查询的性能:: Synonym：给对象一个可替代的名字不需要指定表的大小，表的大小最终由作为一个整体分配给数据库的空间的数量定义。但是随着时间的过去一个表将使用多少空间是重要的。表结构可以被联机修改.当使用AS子查询子句时，列定义可以忽略。表在创建时没有数据，除非指定了一个查询。行通常用INSERT语句添加。表命名和列命名规则依照命名Oracle数据库对象的标准规则来命名数据库表和列：:: 表名和列名必须由一个字母开始，长度在 130 之间。:: 名字只能包含 AZ, az, 09, _ (下划线)，$ 和 # (合法字符，但建议不要使用)。:: 同一个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;orselect * 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.31LONG最大2G的可变长度字符数据CLOB最大4G的字符数据RAW(size)原始二进制数据 (必须指定最大长度，最大长度为 2000)LONG RAW可变长度原始二进制数据，最大2GBLOB二进制数据，最大4GBFILE二进制数据存储在一个外部文件中；最大到4GROWID十六进制串，表示行在所在的表中唯一的行地址。该数据类型主要用于返回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允许时间作为年和月的间隔被存储MONTHINTERVAL 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 AM21-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 AMTIMESTAMP WITH LOCAL TIME ZONE类型适合于两层应用程序，在其中你可以用客户系统的时区显示日期和时间。INTERVAL YEAR TO MONTH数据类型::INTERVAL YEAR TO MONTH存储一个使用年和月时间域的时间段INTERVAL YEAR [(year_precision)] TO MONTHINTERVAL '123-2' YEAR(3) TO MONTHIndicates 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 800SALESMAN 1600ANALYST 3000CLERK 1300KJ 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 800SALESMAN 1600SALESMAN 1250ANALYST 3000CLERK 1300KJ 1200 kjatKJS 5000 kjat为了用一个已存在的表的相同结构创建一个新表，而不用旧表的数据，用带WHERE子句的子查询，该子查询的永远是假，例如：CREATE TABLE COPY_TABLE AS(SELECT *FROM employeesWHERE 1 = 2);ALTER TABLE语句 用ALTER TABLE语句来：(使用alter table改变表的结构)::添加一个新列::修改一个已存在的列:;为新列定义一个默认值::删除一个列ALTER TABLE语句用ALTER TABLE语句添加、修改或删除列ALTER TABLE tableADD(column datatype [DEFAULT expr][, column datatype]...);ALTER TABLE tableMODIFY (column datatype [DEFAULT expr][, column datatype]...);ALTER TABLE tableDROP (column);orALTER TABLE tableDROP COLUMN column;新列成为表的最后一列.alter table dept80add (job_id varchar2(9));Table altered.添加新列的原则::: 你可以添加或修改列。:: 你不能指定新添加的列出的位置，新列将成为最后一列。::如果一个表在添加新列时已包含有行，那么，所有行的新列被初始化为空。修改列::可以改变列的数据类型、大小和默认值alter table dept80modify (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 dept80DROP 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 NUMBERSQL> 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 tableSet UNUSED (column);orAlter Table tableSet UNUSED COLUMN column;Alter Table tableDROP 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 dept80SET UNUSED (last_name);Table altered.ALTER TABLE dept80DROP 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_COMMENTSUSER_COL_COMMENTSALL_TAB_COMMENTSUSER_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 '';