Oracle數(shù)據(jù)定義語言DDL三

數(shù)據(jù)庫開發(fā)技術

Oracle數(shù)據(jù)定義語言（DDL）福建工程學院本章要點創(chuàng)建和管理Oracle表約束Oracle數(shù)據(jù)庫表的各種類型1創(chuàng)建和管理表

描述主要的數(shù)據(jù)庫對象表和列名的命名規(guī)則創(chuàng)建表描述可用于定義列的數(shù)據(jù)類型修改表定義刪除、更名、截斷表1.1數(shù)據(jù)庫對象對象 描述表 存儲數(shù)據(jù)的基本單元，由行和列組成視圖 來自一個或多個表的數(shù)據(jù)的子集序列 產(chǎn)生主關鍵字值索引 提高某些查詢的性能的數(shù)據(jù)結(jié)構同義詞 賦予對象另外的名字1.2命名規(guī)則和約定(表和列名)必須以字母開頭長度為1–30個字符只能包含A–Z,a–z,0–9,_,$和#對于同一個用戶不能使用和其他對象相同的名字不能是Oracle的保留字注：1.名字不區(qū)分大小寫，例如：EMP、eMP或eMp指的是同一個表。

2.在不同的表中命名相同的實體采用保持一致的原則，例如：部門編號在EMP和DEPT中都使用相同的名字DEPTNO。1.3CREATETABLE語句您必須具備:CREATETABLE的權限一定的存貯空間您需要指定:表名列名、列的數(shù)據(jù)類型及列的寬度CREATETABLE[schema.]table (column

datatype[DEFAULTexpr];1.3創(chuàng)建表(DDL)

SQL>CREATETABLEdept1

2 (deptno NUMBER(2),3 dname VARCHAR2(14),4 loc VARCHAR2(13));表已創(chuàng)建。創(chuàng)建表驗證是否創(chuàng)建成功(查看表結(jié)構)

SQL>DESCRIBEdept1

NameNull?Type--------------------------------------------DEPTNONOTNULLNUMBER(2)DNAMEVARCHAR2(14)LOCVARCHAR2(13)1.3創(chuàng)建表:DEFAULT選項指定在插入數(shù)據(jù)時，列的默認值…h(huán)iredateDATEDEFAULTSYSDATE,…

合法的值可以是字面值、表達式或SQL函數(shù)非法的值是另一個列的名稱或虛擬列默認值的數(shù)據(jù)類型必須和列的數(shù)據(jù)類型匹配1.4引用其他用戶的表屬于其他用戶而不屬于當前用戶的表需使用用戶名作為表的前綴1.5Oracle的數(shù)據(jù)類型以下是Oracle數(shù)據(jù)類型的類別：數(shù)據(jù)類型字符數(shù)值日期時間RAW/LONGRAWLOB字符符數(shù)數(shù)據(jù)據(jù)類類型型CHARVARCHAR2LONG當需需要要固固定定長長度度的的字字符符串串時時，，使使用用CHAR數(shù)據(jù)據(jù)類類型型。。CHAR數(shù)據(jù)據(jù)類類型型存存儲儲字字母母數(shù)數(shù)字字值值。。CHAR數(shù)據(jù)據(jù)類類型型的的列列長長度度可可以以是是1到2000個字字節(jié)節(jié)。。VARCHAR2數(shù)據(jù)據(jù)類類型型支支持持可可變變長長度度字字符符串串VARCHAR2數(shù)據(jù)類型存儲儲字母數(shù)字值值VARCHAR2數(shù)據(jù)類型的大大小在1至4000個字節(jié)范圍內(nèi)內(nèi)LONG數(shù)據(jù)類型存儲儲可變長度字字符數(shù)據(jù)LONG數(shù)據(jù)類型最多多能存儲2GB1.5Oracle的數(shù)據(jù)類型數(shù)值數(shù)據(jù)類型型可以存儲整數(shù)數(shù)、浮點數(shù)和和實數(shù)最高精度為38位數(shù)值數(shù)據(jù)類型型的聲明語法法：NUMBER[(p[,s])]P表示精度，S表示小數(shù)點的的位數(shù)日期時間數(shù)據(jù)據(jù)類型存儲日日期和時間值值，包括年、、月、日，小小時、分鐘、、秒主要的日期時時間類型有：：DATE-存儲日期和時時間部分，精精確到整個的的秒TIMESTAMP-存儲日期、時時間和時區(qū)信信息，秒值精精確到小數(shù)點點后6位1.5Oracle的數(shù)據(jù)類型RAW數(shù)據(jù)類型用于于存儲二進制制數(shù)據(jù)RAW數(shù)據(jù)類型最多多能存儲2000字節(jié)LONGRAW數(shù)據(jù)類型用于于存儲可變長長度的二進制制數(shù)據(jù)LONGRAW數(shù)據(jù)類型最多多能存儲2GBLOBLOB稱為“大對象象”數(shù)據(jù)類型型，可以存儲儲多達4GB的非結(jié)構化信信息，例如聲聲音剪輯和視視頻文件等LOB數(shù)據(jù)類型允許許對數(shù)據(jù)進行行高效、隨機機、分段的訪訪問BLOBCLOBBFILECLOB即CharacterLOB（字符LOB），它能夠存存儲大量字符符數(shù)據(jù)BLOB即BinaryLOB（二進制LOB），可以存儲儲較大的二進進制對象，如如圖形、視頻頻剪輯和聲音音文件BFILE即BinaryFile（二進制文件件），它用于于將二進制數(shù)數(shù)據(jù)存儲在數(shù)數(shù)據(jù)庫外部的的操作系統(tǒng)文文件中1.5Oracle的數(shù)據(jù)類型1.5數(shù)據(jù)類型P50類型 描述VARCHAR2(size)變長字字符串串(字符族族):1~4000個字符符CHAR(size)固定長長度字字符串串(字符族族)):1~2000個字符符NUMBER(p,s)定點小小數(shù)值值,p為精度,s為標度度(數(shù)字族族)DATE日期和和時間間值(日期族族)LONG可存放放高達達2GB的變長字字符數(shù)數(shù)據(jù)(字符族族)CLOB字符大大對象象可存放放高達達4GB的變長單單字節(jié)節(jié)字符數(shù)據(jù)RAWandLONGRAW原始二二進制制數(shù)據(jù)據(jù)BLOB二進制制大對象象可存放放高達達4GB二進制制數(shù)據(jù)據(jù)BFILE引用一一個外外部的的二進進制文文件;可存放放高達達4GB數(shù)據(jù)P50表3-11.6查詢數(shù)數(shù)據(jù)字字典列出當當前用用戶的的表列出當當前用用戶不不同的的對象象類型型查詢當當前用用戶的的表、、視圖圖、同同義詞詞及序序列SQL>SELECTtable_name2FROMuser_tables;SQL>SELECTDISTINCTobject_type2FROMuser_objects;SQL>SELECT*2FROMuser_catalog;1.7用子查查詢創(chuàng)創(chuàng)建表表--利用現(xiàn)現(xiàn)有的的表創(chuàng)創(chuàng)建表表在CREATETABLE語句的的AS子句中中使用用子查查詢可可以在在創(chuàng)建建表的的同時時插入入數(shù)據(jù)據(jù)所指定定列的的數(shù)量量和類類型必必須和和子查查詢中中列的的數(shù)量量和類類型相相匹配配由列名名和默默認值值定義義列CREATETABLEtable[column(,column...)]ASsubquery;1.7使用子子查詢詢創(chuàng)建建表NameNull?Type-----------------------------------------EMPNONOTNULLNUMBER(4)ENAMEVARCHAR2(10)ANNSALNUMBERHIREDATEDATESQL>DESCRIBEdept30SQL>CREATETABLEdept302AS3SELECTempno,ename,sal*12ANNSAL,hiredate4FROMemp5WHEREdeptno=30;表已創(chuàng)創(chuàng)建。。1.8ALTERTABLE語句使用ALTERTABLE語句可以：：添加或刪除除一個列修改已存在在的列為新列定義義默認值ALTERTABLEtableADD(columndatatype[DEFAULTexpr][,columndatatype]...);ALTERTABLEtableMODIFY(columndatatype[DEFAULTexpr][,columndatatype]...);添加新列DEPT30EMPNOENAMEANNSAL HIREDATE---------------- --------7698BLAKE3420001-MAY-817654MARTIN1500028-SEP-817499ALLEN1920020-FEB-817844TURNER1800008-SEP-81...“在DEPT30表中添加新列JOB”DEPT30EMPNOENAMEANNSAL HIREDATE---------------- --------7698BLAKE3420001-MAY-817654MARTIN1500028-SEP-817499ALLEN1920020-FEB-817844TURNER1800008-SEP-81...

JOBJOB新列添加新列使用ADD子句添加新新列columns.EMPNOENAMEANNSALHIREDATEJOB-----------------------------------------7698BLAKE3420001-MAY-817654MARTIN1500028-SEP-817499ALLEN1920020-FEB-817844TURNER1800008-SEP-81...6rowsselected.SQL>ALTERTABLEdept302ADD(jobVARCHAR2(9));表已更改。新列成為表的的最后一列修改列可以修改列的的數(shù)據(jù)類型、、長度及默認認值改變列的默認認值僅僅影響響后來插入的的新記錄ALTERTABLE dept30MODIFY (enameVARCHAR2(15));--原來是VARCHAR2(10)表已更改。刪除一列(Oracle8i以上版本)從數(shù)據(jù)表中刪刪除一列至少還必須剩剩下一列ALTERTABLE dept30DROPCOLUMNename;--一次只允許刪刪除一列表已更改。1.9刪除表表中的所有數(shù)數(shù)據(jù)和結(jié)構都都被刪除任何與該表相相關的視圖或或同義詞依然然存在，但是是卻是不可用用的任何懸而未決決的事務都將將被提交所有的索引引被刪除.不能回滾該該語句只有表的創(chuàng)創(chuàng)建者或具具有DROPANYTABLE權限的用戶戶才能刪除除表SQL>DROPTABLEdept30;表已刪除。。1.10修改對象的的名稱要修改表、、視圖、序序列或同義義詞的名稱稱，可以使使用RENAME語句您必須擁有有對象的所所有權.SQL>RENAMEdept1TOdepartment;表已重命名名。RENAMEold_nameTOnew_name;1.11截斷表TRUNCATETABLE語句：刪除表中所所有記錄釋放該表的的存儲空間間使用TRUNCATE移除后不能能回滾DELETE語句同樣可以刪除表中記錄，，但是它并并不釋放存存儲空間。。SQL>TRUNCATETABLEdepartment;表被截斷。。1.12給表或列添添加注釋使用COMMENT語句，可以以為表或列列添加注釋釋(不超過2000字節(jié))注釋可以通通過以下字字典表查詢詢ALL_COL_COMMENTSUSER_COL_COMMENTSALL_TAB_COMMENTSUSER_TAB_COMMENTSSQL>COMMENTONTABLEemp2IS'EmployeeInformation';注釋已創(chuàng)建建。2約束描述主要的的約束類型型建立表的同同時創(chuàng)建約約束對已存在的的表添加、、刪除約束束禁用、啟用用約束什么是約束束阻止無效數(shù)數(shù)據(jù)進入表表中約束在表一一級實施強制規(guī)則約束可以阻阻止刪除和和其它表具具有相關性性的表以下是Oracle中有效的約約束類型：：NOTNULLUNIQUEKeyCHECKPRIMARYKEYFOREIGNKEY值約束完整性約束束約束準則給約束命名名或者由Oreacle服務器產(chǎn)生生一個形式式為SYS_Cn的名稱創(chuàng)建約束的的方式:創(chuàng)建表的同同時便創(chuàng)建建約束表創(chuàng)建以后后再創(chuàng)建約約束在列或者表表一級定義義約束在數(shù)據(jù)字典典中可以查查詢約束命名約束對于一個帳帳號，每個個約束名必必須是惟一一的命名約束的的約定格式式:<tablename>_<columnname>_<constrainttype>如：emp_deptno_fk約束縮寫PRIMARYKEYpkFOREIGNKEYfkUNIQUEukCHECKck或ccNOTNULLnn圖普普遍采用的的約束的縮縮寫表示定義約束CREATETABLE[schema.]table (column

datatype[DEFAULTexpr] [column_constraint],

… [table_constraint]);CREATETABLEemp( empnoNUMBER(4), enameVARCHAR2(10),

… deptnoNUMBER(7,2)NOTNULL,

CONSTRAINTemp_empno_pk PRIMARYKEY(EMPNO)); 定義約束列約束級表約束級column[CONSTRAINTconstraint_name]constraint_type,column,...[CONSTRAINTconstraint_name]constraint_type(column,...),NOTNULL約束確保該列不不允許存放放空值EMPEMPNO ENAMEJOB...COMMDEPTNO7839KING PRESIDENT107698BLAKE MANAGER307782CLARK MANAGER107566JONES MANAGER20...NOTNULL約束(該列沒有記錄為NUILL)沒有NOTNULL約束(任何一個記錄該列可以為空值)NOTNULL約束NOTNULL約束束在列列一一級級上上定定義義約約束束SQL>CREATETABLEemployee(2empnoNUMBER(4),3enameVARCHAR2(10)NOTNULL,4jobVARCHAR2(9),5mgrNUMBER(4),6hiredateDATE,7salNUMBER(7,2),8commNUMBER(7,2),9deptnoNUMBER(7,2)NOTNULL);UNIQUE約束DEPTDEPTNODNAMELOC------------------------10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONS BOSTONUNIQUE約束

50 SALES DETROIT

60 BOSTONInsertinto不允許插入

(SALES

已經(jīng)存在)允許插入UNIQUE約束可以定義義在表或或列一級級SQL>CREATETABLEdept(2deptnoNUMBER(2),3 dnameVARCHAR2(14),4 locVARCHAR2(13),5 CONSTRAINTdept_dname_ukUNIQUE(dname));注:Oracle服務器是是通過隱隱含地在在唯一關關鍵字上上創(chuàng)建唯唯一索引引的方式式實施UNIQUE約束的。。PRIMARYKEY約束DEPTDEPTNODNAMELOC------------------------10ACCOUNTING NEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONS BOSTONPRIMARYKEYInsertinto

20 MARKETING DALLAS

FINANCE NEWYORK不允許(DEPTNO-20已經(jīng)存在)不允許

(DEPTNO不允許為空)PRIMARYKEY約束可以定義義在表或或列一級級SQL>CREATETABLEdept(2deptnoNUMBER(2),3 dnameVARCHAR2(14),4 locVARCHAR2(13),5 CONSTRAINTdept_dname_ukUNIQUE(dname),6 CONSTRAINTdept_deptno_pkPRIMARYKEY(deptno));注:Oracle服務器會自動動為主關鍵字字列創(chuàng)建一個個唯一索引。。FOREIGNKEY約束DEPTDEPTNODNAMELOC---------------- --------10 ACCOUNTING NEWYORK20 RESEARCH DALLAS...PRIMARY

KEYEMPEMPNOENAME JOB...COMMDEPTNO7839KINGPRESIDENT107698BLAKEMANAGER30...FOREIGN

KEY

7571 FORD MANAGER ...200 97571 FORD MANAGER ...200Insertinto不允許插入

(DEPTNO-9在DEPT表中不存在)允許

FOREIGNKEY約束可以定義在表或列一級SQL>CREATETABLEemployee(2 empnoNUMBER(4),3 ename VARCHAR2(10)NOTNULL,4 jobVARCHAR2(9),5 mgrNUMBER(4),6 hiredateDATE,7 salNUMBER(7,2),8 comm NUMBER(7,2),9 deptno NUMBER(7,2)NOTNULL,10 CONSTRAINTemp_deptno_fkFOREIGNKEY(deptno)11REFERENCESdept(deptno));FOREIGNKEY約束可以定義在表表或列一級SQL>CREATETABLEemployee(2 empnoNUMBER(4),3 ename VARCHAR2(10)NOTNULL,4 jobVARCHAR2(9),5 mgrNUMBER(4),6 hiredateDATE,7 salNUMBER(7,2),8 comm NUMBER(7,2),9 deptno NUMBER(7,2)10constraintemp_deptno_fkreferencesdept(deptno));FOREIGNKEY約束中的關鍵鍵字FOREIGNKEY在表或列一級級上定義外鍵鍵REFERENCES標識父表及相相應的列ONDELETECASCADE如果刪除父表表中的記錄則則允許級聯(lián)刪刪除子表中相相關連的記錄錄或用ONDELETESETNULLCHECK約束定義每條記錄錄都必須滿足足的條件表達式不允許許使用:對偽列的引用用，例如：CURRVAL,NEXTVAL,LEVEL及ROWNUM對其它記錄其其它值的查詢詢可以調(diào)用SYSDATE,UID,USER及USERENV函數(shù)...,deptno NUMBER(2),CONSTRAINTemp_deptno_ckCHECK(DEPTNOBETWEEN10AND99),...添加約束添加、刪除但但不是修改約約束啟用一個已經(jīng)經(jīng)禁用的約束束使用MODIFY子句增加NOTNULL約束ALTERTABLEtableADD[CONSTRAINTconstraint]type(column);添加約束為EMP表添加一個個FOREIGNKEY約束，指定定雇員的管管理員編號號必須是在在EMP表中已經(jīng)存存在的雇員員的雇員編編號。SQL>ALTERTABLEemp2ADDCONSTRAINTemp_mgr_fk3FOREIGNKEY(mgr)REFERENCESemp(empno);表已更改。。刪除約束從EMP表刪除管理理員約束SQL>ALTERTABLEemp2DROPCONSTRAINTemp_mgr_fk;表已更改。。從DEPT表刪除主鍵鍵約束PRIMARYKEY，同時刪除除也刪除了了與該約束束關聯(lián)的EMP表DEPTNO上的外鍵FOREIGNKEY約束。SQL>ALTERTABLEdept2DROPPRIMARYKEYCASCADE;表已更改。。禁用約束在ALTERTABLE語句中使用用DISABLE子句可以使使約束失效效使用CASCADE選項可以使使與該約束束是從屬關關系的約束束同時失效效SQL>ALTERTABLEdeptment2DISABLECONSTRAINTdept_deptno_pkCASCADE;表已更改改。啟用約束束使用ENABLE子句可以以激活已已經(jīng)禁用用的約束束如果您啟啟用唯一一UNIQUE或主鍵PRIMARYKEY約束，唯唯一UNIQUE或主鍵PRIMARYKEY索引便自自動創(chuàng)建建SQL>ALTERTABLEdeptm