- 数据库对象:
- :: 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 '';
ORACLE 操作和管理表
最新推荐文章于 2024-05-29 10:47:58 发布