數據庫開發技術

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

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

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

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

SQL>CREATETABLEdept1

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

SQL>DESCRIBEdept1

NameNull?Type--------------------------------------------DEPTNONOTNULLNUMBER(2)DNAMEVARCHAR2(14)LOCVARCHAR2(13)1.3創建表:DEFAULT選項指定在插入數據時，列的默認值…hiredateDATEDEFAULTSYSDATE,…

合法的值可以是字面值、表達式或SQL函數非法的值是另一個列的名稱或虛擬列默認值的數據類型必須和列的數據類型匹配1.4引用其他用戶的表屬于其他用戶而不屬于當前用戶的表需使用用戶名作為表的前綴1.5Oracle的數據類型以下是Oracle數據類型的類別：數據類型字符數值日期時間RAW/LONGRAWLOB字符符數數據據類類型型CHARVARCHAR2LONG當需需要要固固定定長長度度的的字字符符串串時時，，使使用用CHAR數據據類類型型。。CHAR數據據類類型型存存儲儲字字母母數數字字值值。。CHAR數據據類類型型的的列列長長度度可可以以是是1到2000個字字節節。。VARCHAR2數據據類類型型支支持持可可變變長長度度字字符符串串VARCHAR2數據類型存儲儲字母數字值值VARCHAR2數據類型的大大小在1至4000個字節范圍內內LONG數據類型存儲儲可變長度字字符數據LONG數據類型最多多能存儲2GB1.5Oracle的數據類型數值數據類型型可以存儲整數數、浮點數和和實數最高精度為38位數值數據類型型的聲明語法法：NUMBER[(p[,s])]P表示精度，S表示小數點的的位數日期時間數據據類型存儲日日期和時間值值，包括年、、月、日，小小時、分鐘、、秒主要的日期時時間類型有：：DATE-存儲日期和時時間部分，精精確到整個的的秒TIMESTAMP-存儲日期、時時間和時區信信息，秒值精精確到小數點點后6位1.5Oracle的數據類型RAW數據類型用于于存儲二進制制數據RAW數據類型最多多能存儲2000字節LONGRAW數據類型用于于存儲可變長長度的二進制制數據LONGRAW數據類型最多多能存儲2GBLOBLOB稱為“大對象象”數據類型型，可以存儲儲多達4GB的非結構化信信息，例如聲聲音剪輯和視視頻文件等LOB數據類型允許許對數據進行行高效、隨機機、分段的訪訪問BLOBCLOBBFILECLOB即CharacterLOB（字符LOB），它能夠存存儲大量字符符數據BLOB即BinaryLOB（二進制LOB），可以存儲儲較大的二進進制對象，如如圖形、視頻頻剪輯和聲音音文件BFILE即BinaryFile（二進制文件件），它用于于將二進制數數據存儲在數數據庫外部的的操作系統文文件中1.5Oracle的數據類型1.5數據類型P50類型 描述VARCHAR2(size)變長字字符串串(字符族族):1~4000個字符符CHAR(size)固定長長度字字符串串(字符族族)):1~2000個字符符NUMBER(p,s)定點小小數值值,p為精度,s為標度度(數字族族)DATE日期和和時間間值(日期族族)LONG可存放放高達達2GB的變長字字符數數據(字符族族)CLOB字符大大對象象可存放放高達達4GB的變長單單字節節字符數據RAWandLONGRAW原始二二進制制數據據BLOB二進制制大對象象可存放放高達達4GB二進制制數據據BFILE引用一一個外外部的的二進進制文文件;可存放放高達達4GB數據P50表3-11.6查詢數數據字字典列出當當前用用戶的的表列出當當前用用戶不不同的的對象象類型型查詢當當前用用戶的的表、、視圖圖、同同義詞詞及序序列SQL>SELECTtable_name2FROMuser_tables;SQL>SELECTDISTINCTobject_type2FROMuser_objects;SQL>SELECT*2FROMuser_catalog;1.7用子查查詢創創建表表--利用現現有的的表創創建表表在CREATETABLE語句的的AS子句中中使用用子查查詢可可以在在創建建表的的同時時插入入數據據所指定定列的的數量量和類類型必必須和和子查查詢中中列的的數量量和類類型相相匹配配由列名名和默默認值值定義義列CREATETABLEtable[column(,column...)]ASsubquery;1.7使用子子查詢詢創建建表NameNull?Type-----------------------------------------EMPNONOTNULLNUMBER(4)ENAMEVARCHAR2(10)ANNSALNUMBERHIREDATEDATESQL>DESCRIBEdept30SQL>CREATETABLEdept302AS3SELECTempno,ename,sal*12ANNSAL,hiredate4FROMemp5WHEREdeptno=30;表已創創建。。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));表已更改。新列成為表的的最后一列修改列可以修改列的的數據類型、、長度及默認認值改變列的默認認值僅僅影響響后來插入的的新記錄ALTERTABLE dept30MODIFY (enameVARCHAR2(15));--原來是VARCHAR2(10)表已更改。刪除一列(Oracle8i以上版本)從數據表中刪刪除一列至少還必須剩剩下一列ALTERTABLE dept30DROPCOLUMNename;--一次只允許刪刪除一列表已更改。1.9刪除表表中的所有數數據和結構都都被刪除任何與該表相相關的視圖或或同義詞依然然存在，但是是卻是不可用用的任何懸而未決決的事務都將將被提交所有的索引引被刪除.不能回滾該該語句只有表的創創建者或具具有DROPANYTABLE權限的用戶戶才能刪除除表SQL>DROPTABLEdept30;表已刪除。。1.10修改對象的的名稱要修改表、、視圖、序序列或同義義詞的名稱稱，可以使使用RENAME語句您必須擁有有對象的所所有權.SQL>RENAMEdept1TOdepartment;表已重命名名。RENAMEold_nameTOnew_name;1.11截斷表TRUNCATETABLE語句：刪除表中所所有記錄釋放該表的的存儲空間間使用TRUNCATE移除后不能能回滾DELETE語句同樣可以刪除表中記錄，，但是它并并不釋放存存儲空間。。SQL>TRUNCATETABLEdepartment;表被截斷。。1.12給表或列添添加注釋使用COMMENT語句，可以以為表或列列添加注釋釋(不超過2000字節)注釋可以通通過以下字字典表查詢詢ALL_COL_COMMENTSUSER_COL_COMMENTSALL_TAB_COMMENTSUSER_TAB_COMMENTSSQL>COMMENTONTABLEemp2IS'EmployeeInformation';注釋已創建建。2約束描述主要的的約束類型型建立表的同同時創建約約束對已存在的的表添加、、刪除約束束禁用、啟用用約束什么是約束束阻止無效數數據進入表表中約束在表一一級實施強制規則約束可以阻阻止刪除和和其它表具具有相關性性的表以下是Oracle中有效的約約束類型：：NOTNULLUNIQUEKeyCHECKPRIMARYKEYFOREIGNKEY值約束完整性約束束約束準則給約束命名名或者由Oreacle服務器產生生一個形式式為SYS_Cn的名稱創建約束的的方式:創建表的同同時便創建建約束表創建以后后再創建約約束在列或者表表一級定義義約束在數據字典典中可以查查詢約束命名約束對于一個帳帳號，每個個約束名必必須是惟一一的命名約束的的約定格式式:<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

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

20 MARKETING DALLAS

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

(DEPTNO不允許為空)PRIMARYKEY約束可以定義義在表或或列一級級SQL>CREATETABLEdept(2deptnoNUMBER(2),3 dnameVARCHAR2(14),4 locVARCHAR2(13),5 CONSTRAINTdept_dname_ukUNIQUE(dname),6 CONSTRAINTdept_deptno_pkPRIMARYKEY(deptno));注:Oracle服務器會自動動為主關鍵字字列創建一個個唯一索引。。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如果刪除父表表中的記錄則則允許級聯刪刪除子表中相相關連的記錄錄或用ONDELETESETNULLCHECK約束定義每條記錄錄都必須滿足足的條件表達式不允許許使用:對偽列的引用用，例如：CURRVAL,NEXTVAL,LEVEL及ROWNUM對其它記錄其其它值的查詢詢可以調用SYSDATE,UID,USER及USERENV函數...,deptno NUMBER(2),CONSTRAINTemp_deptno_ckCHECK(DEPTNOBETWEEN10AND99),...添加約束添加、刪除但但不是修改約約束啟用一個已經經禁用的約束束使用MODIFY子句增加NOTNULL約束ALTERTABLEtableADD[CONSTRAINTconstraint]type(column);添加約束為EMP表添加一個個FOREIGNKEY約束，指定定雇員的管管理員編號號必須是在在EMP表中已經存存在的雇員員的雇員編編號。SQL>ALTERTABLEemp2ADDCONSTRAINTemp_mgr_fk3FOREIGNKEY(mgr)REFERENCESemp(empno);表已更改。。刪除約束從EMP表刪除管理理員約束SQL>ALTERTABLEemp2DROPCONSTRAINTemp_mgr_fk;表已更改。。從DEPT表刪除主鍵鍵約束PRIMARYKEY，同時刪除除也刪除了了與該約束束關聯的EMP表DEPTNO上的外鍵FOREIGNKEY約束。SQL>ALTERTABLEdept2DROPPRIMARYKEYCASCADE;表已更改。。禁用約束在ALTERTABLE語句中使用用DISABLE子句可以使使約束失效效使用CASCADE選項可以使使與該約束束是從屬關關系的約束束同時失效效SQL>ALTERTABLEdeptment2DISABLECONSTRAINTdept_deptno_pkCASCADE;表已更改改。啟用約束束使用ENABLE子句可以以激活已已經禁用用的約束束如果您啟啟用唯一一UNIQUE或主鍵PRIMARYKEY約束，唯唯一UNIQUE或主鍵PRIMARYKEY索引便自自動創建建SQL>ALTERTABLEdeptm