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在禁用主鍵約束時，