數(shù)據(jù)表的創(chuàng)建及維護

1、 Oracle數(shù)據(jù)庫數(shù)據(jù)表的創(chuàng)建及維護 邢慧芬 第8章 數(shù)據(jù)表對象n8.1 數(shù)據(jù)表概述n8.2 創(chuàng)建數(shù)據(jù)表n8.3 維護數(shù)據(jù)表n8.4 數(shù)據(jù)完整性和約束性n8.5小結(jié)第8章 數(shù)據(jù)表對象n8.1 數(shù)據(jù)表概述n8.2 創(chuàng)建數(shù)據(jù)表n8.3 維護數(shù)據(jù)表n8.4 數(shù)據(jù)完整性和約束性n8.5小結(jié)8.1 數(shù)據(jù)表概述n數(shù)據(jù)表（通常簡稱表）是Oracle數(shù)據(jù)庫中主要的數(shù)據(jù)存儲容器，表中的數(shù)據(jù)被組織成行和列。表中的每個列均有一個名稱，并且每個列都具有一個指定的數(shù)據(jù)類型和大小，比如，VARCHAR(30)，TIMESTAMP(6)(一種時間類型，包括小數(shù)秒的信息)或NUMBER(12)。n在關(guān)系型數(shù)據(jù)庫中，表可以對

2、應(yīng)于現(xiàn)實世界中的實體（如，雇員、崗位等）或聯(lián)系（如，雇員工資）。在進行數(shù)據(jù)庫設(shè)計時，需要首先設(shè)計E-R圖（實體聯(lián)系圖），然后再將E-R圖轉(zhuǎn)變?yōu)閿?shù)據(jù)庫中的表。8.1 數(shù)據(jù)表概述n數(shù)據(jù)類型n常見基本類型（見課本）nROWID數(shù)據(jù)類型數(shù)據(jù)類型nROWID數(shù)據(jù)類型，稱為“偽列類型”，用于在oracle內(nèi)部保存表中的每條記錄的物理地址。通過ROWID來訪問數(shù)據(jù)記錄可以獲得最快的訪問速度。 ROWID字段是隱式的，使用必須顯字段是隱式的，使用必須顯示指定其名稱。示指定其名稱。第8章 數(shù)據(jù)表對象n8.1 數(shù)據(jù)表概述n8.2 創(chuàng)建數(shù)據(jù)表n8.3 維護數(shù)據(jù)表n8.4 數(shù)據(jù)完整性和約束性n8.5小結(jié)8.2 創(chuàng)建數(shù)

3、據(jù)表n8.2.1 數(shù)據(jù)表特性n8.2.2 創(chuàng)建數(shù)據(jù)表8.2.1 數(shù)據(jù)表特性n在Oracle中創(chuàng)建表時，表的特性將決定系統(tǒng)如何創(chuàng)建表、如何在磁盤上存儲表、以及表創(chuàng)建后使用時的最終執(zhí)行方式等，接下來詳細講解表中常用的4個特性及其設(shè)置說明。n存儲參數(shù)n數(shù)據(jù)塊管理參數(shù)n重做日志參數(shù)n緩存參數(shù)8.2.1 數(shù)據(jù)表特性n一、存儲參數(shù)n可以通過STORAGE子句指定INITIAL參數(shù)nINITIAL參數(shù)用于為表指定分配的第一個盤區(qū)大小，以KB或MB為單位。8.2.1 數(shù)據(jù)表特性n二、數(shù)據(jù)塊管理參數(shù)nPCTFREE參數(shù)n用于指定數(shù)據(jù)庫中必須保留的最小空閑空間比例nPCTUSED參數(shù)n用于設(shè)置數(shù)據(jù)塊是否可用的界限

4、。為了使數(shù)據(jù)塊能夠被再次使用，已經(jīng)占用的存儲空間必須低于PCTUSED設(shè)置的比例。n說明： PCTFREE參數(shù)與PCTUSED參數(shù)的和必須等于或小于100.一般而言，兩個參數(shù)的和與100相差越大，存儲效率就越高。8.2.1 數(shù)據(jù)表特性n二、數(shù)據(jù)塊管理參數(shù)n當UPDATE較多時：推薦設(shè)置nPCTFREE=20,PCTUSED=40n當INSERT和DELETE較多時：推薦設(shè)置 nPCTFREE=5,PCTUSED=60nINITRANS參數(shù)n用于指定一個數(shù)據(jù)塊所允許的并發(fā)事務(wù)數(shù)目n在oracle 11g 中，對于單個數(shù)據(jù)塊而言，oracle默認最大支持255個并發(fā)事務(wù)。8.2.1 數(shù)據(jù)表特性n三

5、、重做日志參數(shù)nNOLOGGING：DDL不會記錄到日志，但DML操作會記錄到日志nLOGGING：對表所有的操作都會記錄到日志中n四、緩存參數(shù)nCACHE：全表搜索時，使得讀入的緩存塊放置到LRU列表中最近最常使用的一端（不是默認的LRU列表中最近最少使用的一端），從而提高針對該表的查詢效率。8.2.2 創(chuàng)建數(shù)據(jù)表n基本語法格式：nCREATE TABLE （ ， ， ）指定表空間數(shù)據(jù)表特性；n：所要定義的基本表的名字n：組成該表的各個屬性（列）n：涉及相應(yīng)屬性列的完整性約束條件n：涉及一個或多個屬性列的完整性約束條件 -表的創(chuàng)建表的創(chuàng)建students表表CREATE TABLE stud

6、ents ( stuno number(10) not null,-學(xué)號學(xué)號 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) -指定為該表分配的第一個指定為該表分配的第一個盤區(qū)的大小盤區(qū)的大小pctfree

7、 20 -數(shù)據(jù)塊管理參數(shù)數(shù)據(jù)塊管理參數(shù)pctused 40 -數(shù)據(jù)塊管理參數(shù)數(shù)據(jù)塊管理參數(shù)initrans 10 -數(shù)據(jù)塊管理參數(shù)，數(shù)據(jù)塊管理參數(shù)，10個事務(wù)條目個事務(wù)條目nologging -對對DDL操作不產(chǎn)生日志操作不產(chǎn)生日志cache;-執(zhí)行全表搜索時，將讀入的數(shù)據(jù)塊放置到執(zhí)行全表搜索時，將讀入的數(shù)據(jù)塊放置到LRU中中最近最常使用的一端最近最常使用的一端8.2.2 創(chuàng)建數(shù)據(jù)表 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章 數(shù)據(jù)表對象n8.1 數(shù)據(jù)表概述n8.2 創(chuàng)建數(shù)據(jù)表n8.3 維護數(shù)據(jù)表n8.4 數(shù)據(jù)完整性和約束性n8.5小結(jié)8.3 維護數(shù)據(jù)表n在創(chuàng)建表后，如果發(fā)現(xiàn)對表的定義有不滿意的地方，還可以對表進行修改。這些修改操作包括增加或刪除表中的字段、改變表的存儲參數(shù)設(shè)置以及對表進行增加、刪除和重命名等操作。n增加和刪除字段n修改字段n重命名表n改變表空間和存儲參數(shù)n修改表的狀態(tài)8.3 維

9、護數(shù)據(jù)表n一、修改基本表n增加、刪除和修改字段的基本語法格式：ALTER TABLE ADD 完整性約束 DROP COLUMN MODIFY ；n：要修改的基本表nADD子句：增加新列和新的完整性約束條件nDROP子句：刪除指定的完整性約束條件nMODIFY子句：用于修改列名和數(shù)據(jù)類型8.3 維護數(shù)據(jù)表n二、刪除基本表nDROP TABLE casecade constraints; n基本表刪除后，數(shù)據(jù)、表上的觸發(fā)器、索引都刪除。n說明：在oracle中基本表刪除后，在表上的視圖或PL/SQL塊往往仍然保留，但處于不可用狀態(tài)，無法引用。8.3 維護數(shù)據(jù)表n二、刪除基本表n當表被刪除后，僅僅

10、是從數(shù)據(jù)字典中除名，可以用FLASHBACK TABLE語句（閃回技術(shù)）進行還原。n若用戶想在刪除表時立即釋放空間，并且不希望將其放置到回收站中，則可以在DROP TABLE語句中使用purge選項，這樣該表就被徹底刪除了8.3 維護數(shù)據(jù)表n三、重命名表n語法格式：nALTER TABLE table_old_name RENAME TO table_new_name;n例如：SQLalter table students to stu_tab;8.3 維護數(shù)據(jù)表n四、改變表空間和存儲參數(shù)n修改表空間nAlter table students move tablespace tbsp_2;n說

11、明：由于表空間對應(yīng)的數(shù)據(jù)文件不同，所以在移動表空間時會將數(shù)據(jù)在物理上移動到另一個數(shù)據(jù)文件中n修改存儲參數(shù)nalter table students pctfree 25 pctused 45;8.3 維護數(shù)據(jù)表n五、修改表的狀態(tài)（oracle11g新特性）nSQLalter table students read only;-只讀狀態(tài)nSQLalter table students read write;-可讀寫狀態(tài)第8章 數(shù)據(jù)表對象n8.1 數(shù)據(jù)表概述n8.2 創(chuàng)建數(shù)據(jù)表n8.3 維護數(shù)據(jù)表n8.4 數(shù)據(jù)完整性和約束性n8.5小結(jié)8.4 數(shù)據(jù)完整性和約束性n定義：n完整性約束是數(shù)據(jù)庫模式定義

12、時指定的約束條件，它限制某些數(shù)據(jù)的情形在數(shù)據(jù)庫中出現(xiàn)。nORACLE強制執(zhí)行完整性約束，保證只有合法的數(shù)據(jù)存儲于數(shù)據(jù)庫中。n指定和執(zhí)行完整性約束的時機n當DBA定義數(shù)據(jù)庫模式時n當數(shù)據(jù)庫執(zhí)行應(yīng)用程序時，DBMS檢查數(shù)據(jù)是否違法約束。8.4 數(shù)據(jù)完整性和約束性n約束類型n主碼約束n外碼約束n用戶定義的約束8.4 數(shù)據(jù)完整性和約束性n一、主碼約束n定義：主碼約束是指關(guān)系具有一個特定的最小字段集合，通過它可以唯一確定每條記錄。n其中包括兩個方面n對于合法關(guān)系實例中的兩條不同記錄，其碼的字段值不會相同。n不存在碼字段的真子集可以唯一確定每條記錄8.4 數(shù)據(jù)完整性和約束性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 ，當違反約束時，系統(tǒng)能夠返回約束名。n如果不指定約束命名cno_pk ，則可以省略constraint，將采用系統(tǒng)默認名稱8.4 數(shù)據(jù)完整性和約束性n聯(lián)合主鍵（兩個或兩個以上的列組成）nCREATE TABLE SC( Sno CHAR(5) , Cno CHAR(3) , Grade int,

14、 Primary key (Sno, Cno), );n違法操作包括n碼值為空值n碼值不唯一8.4 數(shù)據(jù)完整性和約束性n二、外碼約束n定義：存儲在某個表中的字段是其他表中的碼，并且需要保證相互數(shù)據(jù)的一致性。n其中包括兩個方面n表中某個字段在其他表中是碼n該字段的數(shù)據(jù)必須在后一個表中存在或者為null8.4 數(shù)據(jù)完整性和約束性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分別對應(yīng)S表、C表中的主碼sno、cno。n當產(chǎn)生可能導(dǎo)致兩個值不一致的操作時，系統(tǒng)可以通過刪除或者拒絕等相應(yīng)來維持完整性。8.4 數(shù)據(jù)完整性和約束性n三、用戶自定義約束n用戶可以自行定義滿足自己需要的約束n建表時直接定義n或者通過觸發(fā)起來定義用戶的完整性n常見的約束包括nNOT NULL約束nUNIQUE約束nCHECK約束8.4 數(shù)據(jù)完整性和約束性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 數(shù)據(jù)完整性和約束性n例如 Create table empl（ empno number， salary number(10) constraints sal_ck CHECK(salary

17、700), )n這里我們通過CHECK定義了保證salary大于700的約束，其中括號里 的內(nèi)容為布爾表達式。8.4 數(shù)據(jù)完整性和約束性nPRIMARY KEY與 UNIQUE的區(qū)別？n定義比主鍵約束弱化nPrimary key在建立的時候會默認地建立此field的索引，且此primary key可以作為作為另外的表的foreign key，n再者primary key跟unique得區(qū)別區(qū)別是Primary key 一定是not null，而unique則沒有此限制n比如：各種會員表的QQ、Email等列值是不允許重復(fù)的，但用戶可能不提供，這樣就必須允許為空值8.4 數(shù)據(jù)完整性和約束性n禁用

18、和激活約束n為什么要禁用約束？n因為約束的存在會降低插入和更改數(shù)據(jù)的效率，系統(tǒng)必須確認這些數(shù)據(jù)是否滿足定義的約束條件，為提高運行效率，就可以禁用這些約束。8.4 數(shù)據(jù)完整性和約束性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表示在刪除約束時，保留對應(yīng)的唯一索引*/n說明：n在禁用主鍵約束時，