数据表的创建及维护

1、 Oracle数据库数据表的创建及维护 邢慧芬 第8章 数据表对象n8.1 数据表概述n8.2 创建数据表n8.3 维护数据表n8.4 数据完整性和约束性n8.5小结第8章 数据表对象n8.1 数据表概述n8.2 创建数据表n8.3 维护数据表n8.4 数据完整性和约束性n8.5小结8.1 数据表概述n数据表（通常简称表）是Oracle数据库中主要的数据存储容器，表中的数据被组织成行和列。表中的每个列均有一个名称，并且每个列都具有一个指定的数据类型和大小，比如，VARCHAR(30)，TIMESTAMP(6)(一种时间类型，包括小数秒的信息)或NUMBER(12)。n在关系型数据库中，表可以对

2、应于现实世界中的实体（如，雇员、岗位等）或联系（如，雇员工资）。在进行数据库设计时，需要首先设计E-R图（实体联系图），然后再将E-R图转变为数据库中的表。8.1 数据表概述n数据类型n常见基本类型（见课本）nROWID数据类型数据类型nROWID数据类型，称为“伪列类型”，用于在oracle内部保存表中的每条记录的物理地址。通过ROWID来访问数据记录可以获得最快的访问速度。 ROWID字段是隐式的，使用必须显字段是隐式的，使用必须显示指定其名称。示指定其名称。第8章 数据表对象n8.1 数据表概述n8.2 创建数据表n8.3 维护数据表n8.4 数据完整性和约束性n8.5小结8.2 创建数

3、据表n8.2.1 数据表特性n8.2.2 创建数据表8.2.1 数据表特性n在Oracle中创建表时，表的特性将决定系统如何创建表、如何在磁盘上存储表、以及表创建后使用时的最终执行方式等，接下来详细讲解表中常用的4个特性及其设置说明。n存储参数n数据块管理参数n重做日志参数n缓存参数8.2.1 数据表特性n一、存储参数n可以通过STORAGE子句指定INITIAL参数nINITIAL参数用于为表指定分配的第一个盘区大小，以KB或MB为单位。8.2.1 数据表特性n二、数据块管理参数nPCTFREE参数n用于指定数据库中必须保留的最小空闲空间比例nPCTUSED参数n用于设置数据块是否可用的界限

4、。为了使数据块能够被再次使用，已经占用的存储空间必须低于PCTUSED设置的比例。n说明： PCTFREE参数与PCTUSED参数的和必须等于或小于100.一般而言，两个参数的和与100相差越大，存储效率就越高。8.2.1 数据表特性n二、数据块管理参数n当UPDATE较多时：推荐设置nPCTFREE=20,PCTUSED=40n当INSERT和DELETE较多时：推荐设置 nPCTFREE=5,PCTUSED=60nINITRANS参数n用于指定一个数据块所允许的并发事务数目n在oracle 11g 中，对于单个数据块而言，oracle默认最大支持255个并发事务。8.2.1 数据表特性n三

5、、重做日志参数nNOLOGGING：DDL不会记录到日志，但DML操作会记录到日志nLOGGING：对表所有的操作都会记录到日志中n四、缓存参数nCACHE：全表搜索时，使得读入的缓存块放置到LRU列表中最近最常使用的一端（不是默认的LRU列表中最近最少使用的一端），从而提高针对该表的查询效率。8.2.2 创建数据表n基本语法格式：nCREATE TABLE （ ， ， ）指定表空间数据表特性；n：所要定义的基本表的名字n：组成该表的各个属性（列）n：涉及相应属性列的完整性约束条件n：涉及一个或多个属性列的完整性约束条件 -表的创建表的创建students表表CREATE TABLE stud

6、ents ( stuno number(10) not null,-学号学号 stuname varchar2(8),-姓名姓名 sex char(2),-性别性别 age int,-年龄年龄 departno varchar2(2) not null,-系别编号系别编号 classno varchar2(4) not null,-班级编号班级编号 regdate date default sysdate-建档日期建档日期)tablespace users -表空间表空间storage(initial 256k) -指定为该表分配的第一个指定为该表分配的第一个盘区的大小盘区的大小pctfree

7、 20 -数据块管理参数数据块管理参数pctused 40 -数据块管理参数数据块管理参数initrans 10 -数据块管理参数，数据块管理参数，10个事务条目个事务条目nologging -对对DDL操作不产生日志操作不产生日志cache;-执行全表搜索时，将读入的数据块放置到执行全表搜索时，将读入的数据块放置到LRU中中最近最常使用的一端最近最常使用的一端8.2.2 创建数据表 Create table newTable as select * from .；如：如： create table NewEmp as select * from emp;nNewEmp 除了没有键，其他的和除

8、了没有键，其他的和emp一样一样n【例】CREATE TABLE myemp(id,name,sal,job,deptno) AS SELECT empno,ename,sal,job deptno FROM emp;第8章 数据表对象n8.1 数据表概述n8.2 创建数据表n8.3 维护数据表n8.4 数据完整性和约束性n8.5小结8.3 维护数据表n在创建表后，如果发现对表的定义有不满意的地方，还可以对表进行修改。这些修改操作包括增加或删除表中的字段、改变表的存储参数设置以及对表进行增加、删除和重命名等操作。n增加和删除字段n修改字段n重命名表n改变表空间和存储参数n修改表的状态8.3 维

9、护数据表n一、修改基本表n增加、删除和修改字段的基本语法格式：ALTER TABLE ADD 完整性约束 DROP COLUMN MODIFY ；n：要修改的基本表nADD子句：增加新列和新的完整性约束条件nDROP子句：删除指定的完整性约束条件nMODIFY子句：用于修改列名和数据类型8.3 维护数据表n二、删除基本表nDROP TABLE casecade constraints; n基本表删除后，数据、表上的触发器、索引都删除。n说明：在oracle中基本表删除后，在表上的视图或PL/SQL块往往仍然保留，但处于不可用状态，无法引用。8.3 维护数据表n二、删除基本表n当表被删除后，仅仅

10、是从数据字典中除名，可以用FLASHBACK TABLE语句（闪回技术）进行还原。n若用户想在删除表时立即释放空间，并且不希望将其放置到回收站中，则可以在DROP TABLE语句中使用purge选项，这样该表就被彻底删除了8.3 维护数据表n三、重命名表n语法格式：nALTER TABLE table_old_name RENAME TO table_new_name;n例如：SQLalter table students to stu_tab;8.3 维护数据表n四、改变表空间和存储参数n修改表空间nAlter table students move tablespace tbsp_2;n说

11、明：由于表空间对应的数据文件不同，所以在移动表空间时会将数据在物理上移动到另一个数据文件中n修改存储参数nalter table students pctfree 25 pctused 45;8.3 维护数据表n五、修改表的状态（oracle11g新特性）nSQLalter table students read only;-只读状态nSQLalter table students read write;-可读写状态第8章 数据表对象n8.1 数据表概述n8.2 创建数据表n8.3 维护数据表n8.4 数据完整性和约束性n8.5小结8.4 数据完整性和约束性n定义：n完整性约束是数据库模式定义

12、时指定的约束条件，它限制某些数据的情形在数据库中出现。nORACLE强制执行完整性约束，保证只有合法的数据存储于数据库中。n指定和执行完整性约束的时机n当DBA定义数据库模式时n当数据库执行应用程序时，DBMS检查数据是否违法约束。8.4 数据完整性和约束性n约束类型n主码约束n外码约束n用户定义的约束8.4 数据完整性和约束性n一、主码约束n定义：主码约束是指关系具有一个特定的最小字段集合，通过它可以唯一确定每条记录。n其中包括两个方面n对于合法关系实例中的两条不同记录，其码的字段值不会相同。n不存在码字段的真子集可以唯一确定每条记录8.4 数据完整性和约束性n例如 CREATE TABLE

13、 COURSE( Cno CHAR(5) , Cname CHAR(10) , Teacher CHAR(10), Cpno CHAR (5), Ccredit INT constraint cno_pk PRIMARY KEY(Cno);n上面定义中，我们将cno作为码，并把这约束命名为cno_pk ，当违反约束时，系统能够返回约束名。n如果不指定约束命名cno_pk ，则可以省略constraint，将采用系统默认名称8.4 数据完整性和约束性n联合主键（两个或两个以上的列组成）nCREATE TABLE SC( Sno CHAR(5) , Cno CHAR(3) , Grade int,

14、 Primary key (Sno, Cno), );n违法操作包括n码值为空值n码值不唯一8.4 数据完整性和约束性n二、外码约束n定义：存储在某个表中的字段是其他表中的码，并且需要保证相互数据的一致性。n其中包括两个方面n表中某个字段在其他表中是码n该字段的数据必须在后一个表中存在或者为null8.4 数据完整性和约束性n例如： CREATE TABLE SC( Sno CHAR(5) , Cno CHAR(3) , Grade int, constraint union_pk Primary key (Sno, Cno), constraint sno_fk FOREIGN KEY(Sn

15、o) REFERENCES S(Sno), constraint cno_fk FOREIGN KEY(Cno) REFERENCES C(Cno);n这里SC的外码sno、cno分别对应S表、C表中的主码sno、cno。n当产生可能导致两个值不一致的操作时，系统可以通过删除或者拒绝等相应来维持完整性。8.4 数据完整性和约束性n三、用户自定义约束n用户可以自行定义满足自己需要的约束n建表时直接定义n或者通过触发起来定义用户的完整性n常见的约束包括nNOT NULL约束nUNIQUE约束nCHECK约束8.4 数据完整性和约束性n例如 Create table empl（ empno numb

16、er， ename varchar2(10) constraint u_ename UNIQUE, )；n这里我们通过UNIQUE定义了保证ename唯一性的约束。n例如 Create table empl（empno number， ejob varchar2(10) constraints nn_ejob NOT NULL, )；n这里我们通过NOT NULL定义了保证ename不会为空的约束。8.4 数据完整性和约束性n例如 Create table empl（ empno number， salary number(10) constraints sal_ck CHECK(salary

17、700), )n这里我们通过CHECK定义了保证salary大于700的约束，其中括号里 的内容为布尔表达式。8.4 数据完整性和约束性nPRIMARY KEY与 UNIQUE的区别？n定义比主键约束弱化nPrimary key在建立的时候会默认地建立此field的索引，且此primary key可以作为作为另外的表的foreign key，n再者primary key跟unique得区别区别是Primary key 一定是not null，而unique则没有此限制n比如：各种会员表的QQ、Email等列值是不允许重复的，但用户可能不提供，这样就必须允许为空值8.4 数据完整性和约束性n禁用

18、和激活约束n为什么要禁用约束？n因为约束的存在会降低插入和更改数据的效率，系统必须确认这些数据是否满足定义的约束条件，为提高运行效率，就可以禁用这些约束。8.4 数据完整性和约束性n禁用和激活约束n禁用约束n1、在定义约束时禁用n例 create table Student ( StuCode varchar2(4) not null, StuName varchar2(10) not null, Age int constraint Age_CK check (age 0 and age alter table course disable constraint cno_pk keep index;/*cno_pk为主键约束，keep index表示在删除约束时，保留对应的唯一索引*/n说明：n在禁用主键约束时，