9.1 数据表概述
表是Oracle数据库中主要的存储容器,表中的数据被组织成行(记录)和列(字段)。
9.2 创建数据表
数据库中每一个表都被一个模式(用户)所拥有,因此表示一种典型的模式对象。在创建时,Oracle将在一个指定的表空间中为其分配存储空间。最初创建的表是一个空的逻辑结构,其中不包含任何数据记录。
9.2.1 数据表逻辑结构
字段的数据类型:
1、字符类型
细分为定长字符类型和变长字符类型两种,对应着char数据类型和varchar2数据类型。
(1)char数据类型
存储固定长度的字符串。当字段保存的字符长度小于规定长度时,空余部分Oracle会用空格自动填充;当字段保存的字符长度大于规定长度时,Oracle会产生错误信息。char数据类型长度范围为1-2000个字节。
(2)varchar2数据类型
存储可变长度的字符串。该字段长度将根据字符数据的长度自动调整。因此,大多数情况下,都会使用varchar2数据类型替换char数据类型。
2、数值数据类型
存储带符号的整数或浮点数。Oracle中number数据类型有精度(precision)和范围(scale)两个参数。精度指定所有数字位的个数,范围指定小数的位数。如果插入的字段数据超过指定的位数,Oracle将自动进行四舍五入。
3、日期时间数据类型
存储日期和时间的组合数据。使用date数据类型存储。最常用的获取日期值得方法是使用sysdate函数,调用该函数获取当前系统的日期值。还可以使用to_date函数将数值或字符串转换为date类型。Oracle默认的日期和时间格式由初始化参数NLS_DATE_FORMAT指定,一般为DD-MM-YY。
4、LOB数据类型
存储大型的、未被结构化的数据。lob类型的数据可以直接存储在数据库内部,也可以将数据存储在外部文件中,而将指向数据的指针存储在数据库中。细分为blob、clob和bfile等三种。
(1)blob数据类型
存储二进制对象。典型的是图像、音频和视频等。blob数据类型的字段中能够存储最大为128MB字节的二进制对象。
(2)clob数据类型
存储字符格式的大型对象。clob数据类型的字段能够存储最大为128MB的对象。Oracle先将数据转换为Unicode编码,再将它存储在数据库中。
(3)bfile数据类型
存储二进制格式的文件。bfile数据类型的字段能够存储最大为128MB的二进制文件作为操作系统文件存储在数据库外部,文件的大小不能超过操作系统的限制;bfile数据类型的字段中仅保存二进制文件的指针,并且该字段是只读的,不能通过数据库对其中进行修改。
(4)ROWID数据类型
被称为“伪列类型”,存储每条记录的物理地址。为了便于使用,Oracle自动为每一个表建立一个名称为ROWID的字段。由于ROWID字段是隐式的,用户检索表时不会看到该字段。如果要使用ROWID字段,必须显式地指定名称。
9.2.2 创建数据表分类
1、在登录的用户下创建数据表
CREATE TABLE tab_name (…);
2、在其他用户下创建数据表
CREATE TABLE username.tab_name (…);
3、使用嵌套子查询创建数据表
CREATE TABLE tab_name AS select_sentence;
9.2.3 数据表的特性
1、存储参数
数据表的存储参数默认采用其所属的表空间的默认存储参数。可以通过 STORAGE 子句设置存储参数,单独配置表中盘区的分配管理方式。
对于本地化管理的表空间(表空间有两种管理方式:数据字典管理、本地化管理;在创建表空间时指定。本地化管理的表空间可以设置每次盘区扩展的方式: AUTOALLOCATE(自动指定)或 UNIFORM SIZE number k|m(按照等同大小),按照等同大小时默认每次扩展为1MB,也可以自己设置):
如果表空间管理方式为 AUTOALLOCATE,则可以在 STORAGE 子句中指定 INITIAL、NEXT 和 MINEXTENTS 这3个存储参数,Oracle将根据存储参数的值为表分配数据段初始化盘区大小,以后盘区的分配由Oracle自动管理;
如果表空间管理方式为 UNIFORM,则不能为表指定 STORAGE 子句,盘区的大小将是统一的。
(1)INITIAL
用于指定分配的第一个盘区大小,以KB或MB为单位。当为已知大小的数据建立表时,可以将initial设置为一个所能容纳所有数据的值,这样可以将表中所有数据存储在一个盘区从而避免产生碎片。
(2)NEXT
用于指定分配的第二个盘区大小,该参数在字典管理的表空间中起作用,在本地化管理的表空间中不再起作用,因为随后分配的盘区将由Oracle自动决定其大小。
(3)MINEXTENTS
用于指定允许为表中的数据所分配的最小盘区数目,在本地化管理的表空间中不起作用。
因此,在本地化管理的表空间中创建数据表,主要是设置 INITIAL 参数。
2、数据块管理参数
对于一般不带有 LOB 类型的数据表,一个数据块可以存放表的多行记录,用户可以设置的数据块管理参数主要分为以下两类:
(1)PCTFREE 和 PCTUSED
用于控制数据块中空闲空间的使用方法。
对于本地化管理的表空间,如果使用 SEGMENT SPACE MANAGEMENT 子句设置段的管理方式为 AUTO,则Oracle会对数据块的空闲空间进行自动管理。不需要用户设置数据库管理参数 PCTFREE 和 PCTUSED。如果设置段的管理方式为 MANUAL,则用户可以通过 PCTFREE 和 PCTUSED 参数对数据块中的空闲空间手动管理。
PCTFREE:用于指定数据块中必须保留的最小空间比例。若 PCTFREE 为10,PCTUSED 为50。数据块可以使用至90(这是第一次使用时,会使用到90),当达到90时,该数据块被标记为不可用。当小于90且大于50时,该数据块可以继续使用(这指的是第一次用这个数据块),但不可以重复使用(该数据块被删除部分数据后,有空闲空间但不能写入数据,因为剩下空间大于50);当小于50时,该数据块可以继续使用(第一次使用该数据块时),也可以再次使用(该数据块被删除部分数据后,可以再次写入数据,直至达到90)。
PCTUSED:用于指定数据块可再次使用的最大空间比例。若 PCTFREE 为10,PCTUSED 为50。数据块可以使用至90,删除部分数据后数据块使用降至70,此时,该数据块不可再次使用(70>50)。再次删除部分数据后数据块降至40(40<50),此时,该数据块可以再次写入数据,如果写入完成后数据块使用达到65,那么下次写入时该数据块不可再次使用。当前空间比例只要不超过数据块可再次使用的最大空间比例即可再次使用。
以上两个参数使用文字介绍比较难理解。下面为数据块空间使用情况和 PCTFREE、PCTUSED 示意图。
深绿和浅绿为可用空间,红色为保留空间。由图中可看出,PCTFREE 和 PCTUSED 两个参数值的和必须小于等于100。一般而言,两个参数的和与100相差越大,存储效率就越高。
(2)INITRANS
用于指定一个数据块所允许的并发事务数目。当一个事务访问表中的一个数据块时,该事务会在数据块头部保存一个条目,以标识该事务正在使用这个数据块。当该事务结束时,它所对应的条目将被删除。
在创建表时,Oracle会在表的每个数据块头部分配可以存储 INITRANS 参数个事务条目的空间,这部分空间是永久的,只能用于存储事务条目。
如果设置的INITRANS参数值较大,则事务条目将占用过多的存储空间,从而减少用来存储实际数据的空间。只有当一个表有较多的事务同时访问时,才应当将其设置为较高的数值。
3、重做日志参数
用于指定是否将 DDL(Data Definition Language)操作(创建、删除、修改等)记录到日志中(DML(Data Manipulation Language)操作(INSERT、UPDATE、DELETE)一定会记录到日志中,不管使用 NOLOGGING 还是LOGGING 子句)。默认情况下使用 LOGGING 子句,使用 NOLOGGING 子句可以节省重做日志文件的存储空间。
4、缓存参数
LRU(Least Recently Used)最近最久未使用算法。Oracle对于在内存中但最近又不用的数据块放入 LRU 列表的最近最少使用的一端,Oracle会将 LRU 列表的最近最少使用的一端数据移出内存而腾出空间加载另外的数据。
默认使用 NOCACHE 子句建表,当在Oracle中执行全表搜索时,读入缓存中的数据块将会存储在 LRU 列表的最近最少使用的一端。若此时又进行查询操作,再向缓存中存入数据时,就会将存储在 LRU 列表的最近最少使用的一端的数据块移出缓存。
使用 CACHE 子句建表,当在Oracle中执行全表搜索时,读入缓存中的数据块将会存储在 LRU 列表的最近最常使用的一端。这样数据库缓存中利用 LRU 算法对缓存中的数据块进行调度时,就不会将该表立即换出,提高该表的查询效率。
对于比较小且经常查询的表,创建表时指定 CACHE 子句来提高查询效率。
9.2.4 总结
1、建表语句的语法格式
(|表示或者,{}表示其中内容不可分割,[]表示其中内容可选)
create table tab_name
(
--字段和约束的定义,详见第2点
)
[tablespace tablespace_name]--表空间参数,设置表存储到哪个表空间
[storage(initial number K|M)]--存储参数,本地化管理的表空间,且自动化管理盘区时需要设置
[pctfree number]--pctfree参数,自动段管理时系统自动设置,手动段管理时需要设置
[pctused number]--pctuesd参数,同pctfree的设置方式
[initrans number]--并发事务参数
[nologging|logging]--重做日志参数,默认为logging
[cache|nocache];--缓存参数,默认为nocache
2、字段约束的定义形式
(|表示或者,{}表示其中内容不可分割,[]表示其中内容可选)
(1)列级别约束添加(不指定约束名)
CREATE TABLE tab_name
(
col_name1 data_type [PRIMARY KEY],
col_name2 data_type [REFERENCES tab_name(primary_col_name)],
col_name3 data_type [NOT NULL],
col_name4 data_type [UNIQUE],
col_name5 data_type [CHECK (condition)],
col_name6 data_type [DEFAULT default_value]
);--当不指定键名、约束名时,会使用系统自动分配的名称
(2)列级别约束添加(指定约束名)
CREATE TABLE tab_name
(
col_name1 data_type [CONSTRAINT constraint_name1 PRIMARY KEY],
col_name2 data_type [CONSTRAINT constraint_name2 REFERENCES tab_name(primary_col_name)],
col_name3 data_type [CONSTRAINT constraint_name3 NOT NULL],
col_name4 data_type [CONSTRAINT constraint_name4 UNIQUE],
col_name5 data_type [CONSTRAINT constraint_name5 CHECK (condition)],
col_name6 data_type [DEFAULT default_value]
);
(3)表级别约束添加(指定约束名)
CREATE TABLE tab_name
(
col_name1 data_type,
col_name2 data_type,
col_name3 data_type [CONSTRAINT constraint_name1 NOT NULL],
col_name4 data_type,
col_name5 data_type,
col_name6 data_type [DEFAULT default_value],
[CONSTRAINT constraint_name2 PRIMARY KEY (col_name1),]
[CONSTRAINT constraint_name3 FOREIGN KEY (col_name2) REFERENCES tab_name(primary_col_name)
[ON UPDATE | DELETE {NO ACTION}|{SET NULL}|{SET DEFAULT}|CASCADE],]
--on update与on delete两个项目各有四个选项参数为no action、set null、set default、cascade
--四个选项参数详细介绍在外键约束章节中
[CONSTRAINT constraint_name4 UNIQUE (col_name4),]
[CONSTRAINT constraint_name5 CHECK (col_name5 condition)]
--注意最后一个是没有逗号的!
);
3、参数设置总结
9.3 维护数据表
9.3.1 增加和删除字段
语法格式
ALTER TABLE tab_name ADD(col_name1 col_type [DEFAULT default_value],col_name2 col_type,…);--增加字段
ALTER TABLE tab_name DROP COLUMN col_name;--删除单个字段
ALTER TABLE tab_name DROP(col_name1,col_name2,…);--删除多个字段
不能删除表中所有字段,也不能删除 SYS 模式(用户)中任何表的字段。
删除单个字段必须使用 COLUMN 关键字,删除多个字段不能使用 COLUMN 关键字。
9.3.2 修改字段
语法格式
ALTER TABLE tab_name MODIFY col_name col_type;
修改字段类型时不能随意更改,只能把数据长度从低向高改变,不能反向,否则将会出现数据溢出。
修改字段的默认值只对以后插入的数据有作用,对于先前插入的数据不起作用。
9.3.3 重命名表
语法格式
ALTER TABLE tab_name RENAME TO tab_name;
对表的重命名要非常谨慎,Oracle不能更新关于此表的存储过程等对象,会造成很大影响。
9.3.4 改变表空间和存储参数
1、修改表空间
语法格式
ALTER TABLE tab_name MOVE TABLESPACE tablespace_name;
由于表空间对应的数据文件不同,所以在移动表空间时数据会在物理上移动到另一个数据文件。
2、修改存储参数
主要是指修改 PCTFREE 和 PCTUSED。
语法格式
ALTER TABLE tab_name PCTFREE number PCTUSED number;
9.3.5 删除表
语法格式
DROP TABLE tab_name [CASCADE CONSTRAINTS] [PURGE];
CASCADE CONSTRAINTS:如果该表存在约束、关联的视图和触发器等,必须使用 CASCADE CONSTRAINTS 子句才能删除引用该表的约束、视图和触发器等。
PURGE:当表被删除时,实际上它并没有被彻底删除,而是放入了回收站(RECYCLEBIN)中。如果想彻底删除表,可以在 DROP 语句中使用 PURGE 选项,务必慎重使用该选项。
9.3.6 恢复表
在 RECYCLEBIN 数据字典视图中,字段 original_name 是已删除的表名。我们可以先查看已删除的表是否在 RECYCLEBIN 数据字典视图中,再使用闪回技术进行表的恢复。
select object_name,original_name from recyclebin where original_name='tab_name';
如果存在RECYCLEBIN 数据字典视图中那么就可以用闪回技术恢复。
语法格式
FLASHBACK TABLE tab_name TO BEFORE DROP;
9.3.7 修改表状态
Oracle 11g新的特性是用户可以单独将某个表标记为 READ ONLY 状态。在Oracle 11g之前,只能通过将整个表空间或数据库置于 READ ONLY 状态实现表的 READ ONLY 。可以通过user_tables数据字典的read_only字段查询某个表的状态。这是Oracle 11g的新特性,10g没法用。
语法格式
ALTER TABLE tab_name READ ONLY;--将表置于只读模式
ALTER TABLE tab_name READ WRITE;--将表置于可读写模式
9.4 数据完整性和约束性
9.4.1 非空约束
ALTER TABLE tab_name MODIFY col_name [CONSTRAINT constraint_name] NOT NULL;
--约束名可以省略,由系统自动分配
ALTER TABLE tab_name MODIFY col_name NULL;--使用 ALTER 语句变相删除非空约束
如果使用 ALTER 语句为表添加非空约束,并且该字段中已存在 NULL 值,则不能向该字段添加非空约束。
9.4.2 主键约束
在每个表中最多只能有一个主键约束,主键约束可以由若干字段组成,主键约束同时也具有非空约束的特性。与非空约束相同,当为表添加主键约束时,如果该字段中有相同值或存在 NULL 值,则不能添加主键约束。
ALTER TABLE tab_name ADD [CONSTRAINT constraint_name] PRIMARY KEY (col_name1[,col_name2,…]);
9.4.3 唯一性约束
唯一性约束强调所在字段不允许有相同值,但是它所在的字段允许 NULL 值。由于可以存在重复的 NULL 值,可以在唯一性约束字段再添加非空约束。
ALTER TABLE tab_name ADD [CONSTRAINT constraint_name] UNIQUE (col_name1[,col_name2,…]);
9.4.4 外键约束
被引用的字段必须具有主键约束或唯一性约束。
ALTER TABLE tab_name ADD CONSTRAINT constraint_name FOREIGN KEY (col_name) REFERENCES tab_name(primary_col_name)
[ON UPDATE|DELETE {NO ACTION}|{SET NULL}|{SET DEFAULT}|CASCADE],]
定义外键时可以通过 ON 指定引用行为的类型:UPDATE 和 DELETE。ON UPDATE 指主键表中被参考字段的值更新时,ON DELETE 指主键表中被参考字段删除一条记录时:ON UPDATE 和 ON DELETE 后面可以跟的词语有四个 NO ACTION、SET NULL、SET DEFAULT、CASCADE。
这是数据库外键定义的一个可选项,用来设置当主键表中的被参考列的数据发生变化时,外键表中响应字段的变换规则的。
NO ACTION:当更新或删除被引用表中被引用字段(主键表中被参考的字段)的数据违反外键约束时,该操作将被禁止执行;不违反约束时将执行。这也是默认的外键状态。推荐使用该关键字。
SET NULL:当更新或删除被引用表中被引用字段的数据时,外键表中相关联的外键字段值设置为 NULL,要使这个关键字起作用,外键字段必须支持 NULL 值(可为空)。
SET DEFAULT:当更新或删除被引用表中被引用字段的数据时,外键表中相关联的外键字段值设置为默认值,要使这个关键字起作用,外键字段必须有设置默认值。
CASCADE:当更新或删除被引用表中被引用字段的数据时,外键表中对应的数据也将更新或删除。就是说,如果主键表中被参考字段 UPDATE,外键表中级联的值也更新,如果主键表中被参考字段被 DELETE,外键表中级联该值的对应行也删除。级联更新时,依据的是之前匹配的数据,在主表更新关联的外键字段的值后,系统自动更新从表的相应外键字段的值,而不是其他未设置为主外键关联的字段,不是主外键关联的字段不受影响。详情查询级联更新与级联删除。注意这将会造成多个表的数据更新或删除,因此慎重使用该关键字创建外键。(级联更新)
9.4.5 禁用和激活约束
ALTER TABLE tab_name DISABLE CONSTRAINT constraint_name [CASCADE] [KEEP INDEX];
ALTER TABLE tab_name ENABLE [VALIDATE|NOVALIDATE] CONSTRAINT constraint_name;
CASCADE:禁用约束时的 CASCADE 关键字用来级联禁用约束:在禁用唯一性约束或主键约束时,若有外键约束正在引用该列,则无法禁用,这时可以先禁用外键再禁用唯一性或主键;或者使用 CASCADE 关键字可以级联禁用相应的外键。(级联禁用)
KEEP INDEX:在禁用主键时,Oracle会默认删除约束对应的唯一索引,在激活主键时,Oracle会重新建立唯一索引。若希望在删除约束时保留唯一索引,可以使用 KEEP INDEX 关键字。
VALIDATE|NOVALIDATE:默认为 VALIDATE 表示在激活约束时系统将验证表中的数据是否满足约束的定义;NOVALIDATE 则表示在激活约束时不验证已经存在的数据。
9.4.6 删除约束
ALTER TABLE tab_name DROP CONSTRAINT constraint_name [CASCADE];
--只有删除主键才可能会用到cascade关键字
CASCADE:当删除主键约束时,如果有外键约束正在引用该主键字段,则无法删除该主键约束。这时可以先删除外键约束再删除主键约束,也可以在删除主键约束时使用可选关键字 CASCADE,可以级联删除引用该主键字段的外键约束,一定要**慎重使用 CASCADE!(级联删除)
9.4.7 查询约束
SELECT * FROM USER_CONS_COLUMNS WHERE table_name='tab_name';
--查询某表的约束