第5章 表.ppt

1、,分布式数据库管理与实现,任课教师：翟霞,第5章 表,哈尔滨华德学院,任课教师：翟霞,第5章 表,5.1 Oracle 数据库方案对象与数据类型 5.2 创建表的语法 5.3 基本关系表 5.4 按索引组织的表 5.5 簇表 5.6 分区表 5.7 外部表 5.8 对象表 5.9 嵌套表 5.10 管理表,表TABLE 索引INDEX 视图VIEW 序列SEQUENCE 用户USER 约束CONSTRAINT 表空间TABLESPACE 回退段ROLLBACK SEGMENT 同义词SYNONYM 数据库链接DATABASE LINK 聚簇CLUSTER 分区PARTITION 函数/过程/包

2、FUNCTION/PROCEDURE/PACKAGE 触发器TRIGGER 类型TYPE,5.1 Oracle数据库方案对象与数据类型,常用方案对象类型,包括如下几种表类型 关系表 临时表 按索引组织的表（IOT） 外部表 对象表与嵌套表,1.数据表类型,常用关系表、按索引组织的表、对象表与嵌套表存储用户永久数据,关系表可分为如下3种： 规则表 分区表 簇表,表结构中建议使用的数据类型 VARCHAR2可变长字符串类型，4KB DATE固定长度（7B）的日期型。注意存储与使用格式 TIMESTAMP时间戳类型，可更精确的存储与表示时间 NUMBER数字型，可存放实型和整型 BLOB大二进制对象

3、类型，4GB CLOB大字符串对象类型，4GB NCLOB多字节字符集的CLOB，4GB BFILE外部二进制文件类型，只读，大小与OS有关 ROWID存储记录行的物理地址（2进制表示），固定为10个字节,2.Oracle数据类型,表结构中不建议使用的数据类型 CHAR定长字符类型，2KB VARCHAR（同VARCHAR2）可变长字符串类型，4KB LONG可变长字符类型，2GB RAW可变长二进制数据类型，4KB LONG RAW可变长二进制数据类型，2GB,建议使用LOB类型代替LONG类型,2.Oracle数据类型,数据库中的每一条记录有唯一的ROWID值，表示了记录的具体位置，因此又

4、称它为记录的物理地址,TIMESTAMP 数据类型 (1) TIMESTAMP是DATE的扩展，可指定秒的计量精度至小数点后9位 (2) TIMESTAMP和TIMESTAMP WITH LOCAL TIME ZONE可用于主键，TIMESTAMP WITH TIME ZONE不能被用于主键,其他数据类型,2.Oracle数据类型,参照表类型 在一个基表的记录中存放被参照表中记录的指针,集合类型(Collection) VARRAY：表中元素个数有限制 TABLE ：表中元素个数没有限制,用户自定义数据类型,表命名规则：以字母开头，长度为1-30个字符，可使用A-Z，a-z，0-9，_ ，$和

5、#字符，不能与已经存在的当前用户的其他数据库对象同名，不能使用Oracle的保留字作为表名，表名不区分大小写,5.2 创建表的语法,可用SYSDATE、USER等作为列的缺省值,用户应获得CREATE TABLE系统特权,语法图符号 方框：保留关键字 圆圈：界限符 圆弧方框：需要由其他语法图说明的语法成分 箭头：连接指向下一个语法组成成分,创建表的语法图,CREATE GLOBAL TEMPORARY TABLE schema.table (relational_properties) ON COMMIT DELETE | PRESERVE ROWS physical_properties t

6、able_properties;,创建表的语法图,relational_properties语法图,创建表的语法图,physical_properties语法图,创建表的语法图,table_properties语法图,最简单的创建二维关系表的方法是不指定物理存储特性，也不指定分区属性，建立的表将使用系统默认的参数。,5.3 基本关系表,CREATE TABLE 表名 ( 列名 类型 NULL|NOT NULL PRIMARY KEY， 列名 类型 NULL|NOT NULL ，表级完整性约束 )；,操作用户需要有在相应表空间中创建对象的权限,例5.1 创建采用系统默认存储参数值的关系表。 EX

7、A_05_01.SQL,1.利用查询创建表,通过子查询创建表 用于保存原始数据以便于恢复 为了得到一个与源表一样结构的表,需要注意如下几点： (1)源表中的LONG型字段不会被复制 (2)源表中的完整性约束不会被复制 (3)通过查询创建的表自动继承源表的存储参数 (4)不能指定新表的列属性，但每列可以取不同于源表列的名称,1.利用查询创建表,语法如下：,CREATE TABLE table ( column , column . ) AS subquery ;,例5.2 为SCOTT的雇员表emp创建一个备份表emp_bak。 CONN scott/tigerorcl DROP TABLE e

8、mp_bak; CREATE TABLE emp_bak AS SELECT * FROM emp; DESC emp_bak SELECT * FROM emp_bak; EXA_05_02.SQL,2.存储参数,STORAGE子句指定区间存储参数 INITIAL 初始区间大小，以K（KB）或M（MB）为单位 NEXT 下一个区间大小 MINEXTENTS 最小区间数 MAXEXTENTS 最大区间数，用于限制用户的存储空间占用 PCTINCREASE 下一个区间相对于前一个区间大小的增量,Oracle建议PCTINCREASE取0以减少系统开销,2.存储参数,TABLESPACE 指定存储

9、表数据的表空间， PCTFREE 指定每个块用于保留给数据修改空间的百分比， 即保留该百分比的空间暂时不用，当修改数据时 存放扩张出来的数据 PCTUSED 行插入备选块的空间使用阈值的百分比，当数据块 的已用空间比例低于该值时可向其中插入数据 INITRANS 支持并发操作的初始事务量（需为它们保留部分块 空间），默认值为5 MAXTRANS 支持并发操作的最大事务量（需为它们保留部分块 空间），默认值为25,Oracle采取以空间换时间(性能)的存储策略,例5.3 创建表stu2，并指定它的存储参数。 EXA_05_03.SQL,3.完整性约束,约束的类型 NOT NULL：非空约束 UN

10、IQUE：唯一性约束，指定某列值不能重复 PRIMARY KEY：主键约束 FOREIGN KEY：外键约束，也叫参照完整性约束 CHECK：检查约束，用于限制该列的取值范围 REF：定义列对象的参照关系,完整性约束作为一类系统对象，为便于维护，应给约束起一个名字，典型命名规则为： 表名_约束类型_字段名1 _字段名2,完整性约束的系统命名规则：SYS_Cnnnnnn,3.完整性约束,在列中定义约束的语法图,3.完整性约束,在列之外定义约束的语法图,3.完整性约束,定义参照完整性的语法图,父记录被删除时，子记录的处理方法 ON DELETE CASCADE：删除父记录时级联删除子记录 ON D

11、ELETE SET NULL：置子记录的外键列值为NULL,3.完整性约束,完整性约束应用注意点 (1)组合关键字只能定义在表级 (2)系统自动对关键字列建立唯一性索引 (3)一个表只能指定一组关键字，但可以在不同列上建立多个数据唯一性约束 (4)外键(参照完整性)仅依赖于表间或表内的数据关系，而不依赖于物理存储或指针，是纯逻辑上的关系 (5)可在表定义时进行完整性约束定义，或在表定义后使用ALTER TABLE语句定义 (6)可以启用或禁用某个完整性约束,3.完整性约束,数据字典 USER_CONSTRAINTS USER_CONS_COLUMNS,约束类型列(constraint_type

12、)的含义 C：检查约束，包括CHECK 和 NOT NULL P：主键约束(PRIMARY KEY) R：参照完整性约束(REFERENCES) U：唯一性约束(UNIQUE),查看在哪些列上定义了约束 EXA_05_P90_user_cons_columns.SQL,查看用户定义的约束 EXA_05_P90_user_constraints.SQL,3.完整性约束,应用例子,例5.4 创建学生相关表，在列中定义约束。 EXA_05_04.SQL,例5.5 创建学生相关表，在列之外定义约束。 EXA_05_05.SQL,IOT将部分非键列同主键列一起存储在B-树索引中,5.4 按索引组织的表

13、(IOT),按索引组织的表必须有主键。,当执行必须基于主键的查询且相关的少量字段经常要一起获取的应用时，采用按索引组织的表特别有效,按索引组织的表（IOT）,创建按索引组织的表须提供如下信息 (1) ORGANIZATION INDEX 限定词，表示这是一个按索引组织的表 (2) 表的主键 (3) 可选的行溢出说明子句(OVERFLOW) (4) INCLUDING子句说明哪些非关键字列与关键字列一同存储 (5) PCTTHRESHOLD值，定义在索引数据块中为IOT保留空间的百分比,例5.6 创建学生相关表，在列之外定义约束。 EXA_05_06.SQL,一个簇由共享相同数据块的一组表组成,

14、5.5 簇表,创建簇之后可以基于簇创建表,(1) 减少磁盘I/O次数 (2) 改善了访问簇表的连结所花的时间 (3) 簇键是簇表所共有的，减少了所需存储空间,满足如下条件的表可使用簇 (1) 表主要用于查询，即表不总是被插入或更改 (2) 表中的记录经常被一起或连接查询,在将记录插入到簇表之前，必须创建簇索引 簇表上可创建其他索引,1.创建簇表,创建簇表需经历两个步骤 (1) 创建簇 (2) 基于簇创建簇表,例5.7 创建一个名为emp_dept 的簇，包含deptno列，然后基于该簇创建簇表。 EXA_05_07.SQL,创建簇的简要语法 CREATE CLUSTER schema.clus

15、ter ( column datatype , column datatype. ) ;,创建簇表 在表的物理存储部分指明使用某个簇 CLUSTER cluster ( column, column . ),2.创建簇索引,簇表只有在创建簇索引之后才可用,例5.8 为emp_dept 簇创建簇索引，并进行簇索引的使用测试。 EXA_05_08.SQL,创建簇索引语法 CREATE INDEX index_name ON CLUSTER cluster_name index_attributes ;,一般应为簇索引指定单独的表空间,3.删除簇,删除簇及其簇索引语法 DROP CLUSTER ta

16、ble INCLUDING TABLES ;,例5.8 创建一个名为emp_dept 的簇，包含deptno列，然后基于该簇创建簇表。 EXA_05_08.SQL,例： DROP CLUSTER emp_dept; DROP CLUSTER emp_dept INCLUDING TABLES;,簇表的删除同普通表的删除 簇索引的删除同普通索引的删除,实现快速存取有大量数据的表的有效方法是创建和使用分区表和分区索引,5.6 分区表,每个分区可以被单独管理,将分区存储到不同的表空间有如下好处 (1) 减少读写数据的I/O操作在多个分区中冲突的可能性 (2) 可以单独备份和恢复每个分区 (3) 控制

17、分区与磁盘驱动器之间的映射，平衡I/O 负载 (4) 改善可管理性、可用性和性能,分区操作标准DML语句来说是透明的，可在DML 中使用分区扩展表以利用分区的优点,范围、散列、列表和组合分区4种分区方法,1.范围分区表,将行映射到基于某列值范围的分区 当数据在整个范围中能被均等地划分成几个小区间时性能最好,创建范围分区时必须指定： (1) 分区方法：范围(by range) (2) 分区的列 (3) 标识分区边界的分区描述,范围区间用分区列的值从小到大分段表示 用MAXVALUE关键字表示可能的最大值,1.范围分区表,范围分区语法,指定范围区间的语法,例5.9 按雇员工资建立范围分区表part

18、_emp。 EXA_05_09.SQL,2.散列分区表,基于分区键的哈希散列值将行映射到分区中,创建散列分区时需要指定： (1) 分区方法：哈希散列(by hash) (2) 分区列 (3) 分区数量或单独的分区描述,HASH散列分区语法图,2.散列分区表,单独指定各散列分区存储属性,例5.10 创建散列分区雇员表hash_emp，分区列是雇员编号empno。该表4个分区的名字由系统产生，并将分区分别放到4个名为ts1ts4的表空间中 EXA_05_10.SQL,2.散列分区表,给定数量的方法指定散列分区,例5.11 创建部门哈希散列表。指定各个散列分区的名字及其所属的表空间。在表一级上明确地

19、为每个散列分区段指定初始磁盘区间的大小，并且所有分区都继承这个属性。 EXA_05_11.SQL,3.列表分区表,将无序的和不相关的数据集进行分组分区,创建列表分区时需要指定： (1) 分区方法：列表(by list)。 (2) 分区列。 (3) 分区描述。每个描述指定一串离散文字值,列表分区语法图,例5.12 创建一个按地区列表进行分区的销售记录表。 EXA_05_12.SQL,4.组合分区表,组合分区适合于历史数据和条块数据管理,创建组合分区时需要指定： (1) 分区方法：范围(by range) (2) 分区列 (3) 标识分区边界的分区描述 (4) 子分区方法：散列(by hash)或

20、列表(by list) (5) 子分区列 (6) 每个分区的子分区数量或子分区的描述,4.组合分区表,例5.13 假设某电子商务网站有超过100万种商品在线销售，售价从几元到数万元，顾客经常按价格区间查询。创建一个组合分区表，它包括5个按价格范围的分区，每个范围分区包含4 个散列子分区，分别存放到指定的4个表空间ts1ts4中EXA_05_13.SQL,组合分区语法图,外部表是指不在数据库中的表，数据以操作系统文件(一般为文本文件)的形式存在,5.7 外部表,Oracle提供访问驱动程序ORACLE_LOADER读取外部文件,创建外部表只是在数据字典中创建了元数据以便访问外部文件中的数据,定义

21、外部表需要指出外部数据特性 外部数据如何组织与被访问 对不符合访问规则的记录的数量进行限制,外部表,外部表定义语法,外部数据特性描述语法,例5.14 创建一个外部表然后将数据装载到数据库表中 EXA_05_14.SQL,类是具有相同属性和服务的对象的抽象，封装了表征对象的数据和行为方法,5.8 对象表,对象类型是Oracle数据库中的一个抽象数据类型,Oracle表中的列可以基于一个已定义的抽象数据类型进行定义,例：客户信息包括很多项内容 客户的联系信息可作为一个整体看待 可以考虑先建立联系信息对象，再在客户信息中引用该对象 EXA_05_P105_customers.SQL,对象表,首先，建

22、立客户联系地址对象类型 CONN scott/tigerorcl CREATE TYPE cust_address_typ AS OBJECT ( street_address VARCHAR2(40), postal_code VARCHAR2(10), city VARCHAR2(30), state_province VARCHAR2(10), country_id CHAR(2) ); /,对象表,在客户联系地址对象类型基础上创建客户信息表 CREATE TABLE customers ( customer_id NUMBER(6), cust_first_name VARCHAR2(

23、20) CONSTRAINT cust_fname_nn NOT NULL, cust_last_name VARCHAR2(20) CONSTRAINT cust_lname_nn NOT NULL, cust_address cust_address_typ );,对象表,向对象表插入记录时，对对象类型列要给出该列的类型才能插入 INSERT INTO customers VALUES (1001,乙己,孔, cust_address_typ(咸亨镇,100000,绍兴,浙江,01);,在更新和查询对象类型列时，要用连接运算符(圆点)将表名、对象字段名、对象属性连接起来，明确限定要更新或查

24、询的列,UPDATE customers c SET c.cust_address.postal_code =312000 WHERE c.cust_address.city = 绍兴; SELECT customer_id,cust_last_name|cust_first_name, c.cust_address.postal_code FROM customers c;,对象表,更复杂的对象表例子 对象中包含方法 对对象类型和类型主体的维护 引用对象属性及方法,程序：EXA_05_P107_data_typ.SQL,嵌套表通过表类型进行定义,5.9 嵌套表,例：一个部门有多个雇员，可以建

25、立一个人员信息对象类型，用于表示雇员信息。一个单位有多个部门，可以考虑建立一个人力资源信息表，其中包括部门编号、所在地编号、经理编号，该部门的所有雇员信息（存放在TABLE类型对象中）作为部门的一部分嵌套保存在部门信息中，由此形成一个嵌套表结构（相当于按部门管理人力资源信息）。 EXA_05_P108_hr_info.SQL,表的管理和维护操作 修改列定义 增加列 删除列 约束维护 修改存储参数 重命名表和列 ALTER TABLE语句,5.10 管理表,1.存储参数,在创建表之前，应考虑好如下几个问题： (1) 规范表的各字段类型 (2) 确定各字段的取值范围 (3) 确定是否可以节省空间

26、(4) 相关的表是否合适建立簇 (5) 确定数据块的空间分配,PCTFREE和PCTUSED：空间利用率有较大影响 INITRANS、MAXTRANS：系统性能有较大影响 修改存储参数的效果不会立即显现,1.存储参数,分散存储是Oracle数据库系统物理设计的灵魂 ALTER TABLE MOVE：将一个表从一个表空间迁移到另一个表空间,例：将STU01用户的表stu从USERS表空间迁移到ts1表空间。 EXA_05_P109_ALTER_TABLE_MOVE.SQL,CONN /orcl AS SYSDBA ALTER USER stu01 QUOTA UNLIMITED ON TS1;

27、CONN stu01/stu01pwdorcl ALTER TABLE stu MOVE TABLESPACE ts1 STORAGE (INITIAL 10k NEXT 20k MINEXTENTS 1 PCTINCREASE 0);,2.表结构维护,修改列定义 列的类型 宽度 缺省值定义,对有数据的列，修改后列宽度不能小于数据的长度 一个表只能有一个LONG型的列，不能增加或修改出第二个LONG型的列,例5.15 修改course表的列定义。 EXA_05_15.SQL,修改列定义语法 ALTER TABLE table MODIFY (column datatype DEFAULT ex

28、pr, . ) ;,2.表结构维护,增加列 新增加的列总是排在列定义的最后 对有记录的行增加新列，新列默认取空值,增加列语法 ALTER TABLE table ADD (column datatype DEFAULT expr, .) ;,例5.16 为学生表增加一列出生日期birthday，以系统当前时间sysdate作为其缺省值。 EXA_05_16.SQL,删除列 一次只能删除一列 列删除后不能恢复，只能重新建立 删除列语法:ALTER TABLE table DROP (column) ;,2.表结构维护,禁用列语法 ALTER TABLE table SET UNUSED (column , ) ; ALTER TABLE table SET UNUSED COLUMN column ; 数据字典USER_UNUSED_COL_TABS,被禁用列所占空间不会被释放，除非将该列删除 删除禁用列将删除所有的禁用列，语法如下： ALTER TABLE table DRO