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

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

Oracle數(shù)據(jù)定義語(yǔ)言（DDL）福建工程學(xué)院本章要點(diǎn)創(chuàng)建和管理Oracle表約束Oracle數(shù)據(jù)庫(kù)表的各種類(lèi)型1創(chuàng)建和管理表

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

2.在不同的表中命名相同的實(shí)體采用保持一致的原則，例如：部門(mén)編號(hào)在EMP和DEPT中都使用相同的名字DEPTNO。1.3CREATETABLE語(yǔ)句您必須具備:CREATETABLE的權(quán)限一定的存貯空間您需要指定:表名列名、列的數(shù)據(jù)類(lèi)型及列的寬度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)建表驗(yàn)證是否創(chuàng)建成功(查看表結(jié)構(gòu))

SQL>DESCRIBEdept1

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

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

datatype[DEFAULTexpr] [column_constraint],

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

… deptnoNUMBER(7,2)NOTNULL,

CONSTRAINTemp_empno_pk PRIMARYKEY(EMPNO)); 定義約束列約束級(jí)表約束級(jí)column[CONSTRAINTconstraint_name]constraint_type,column,...[CONSTRAINTconstraint_name]constraint_type(column,...),NOTNULL約束確保該列不不允許存放放空值EMPEMPNO ENAMEJOB...COMMDEPTNO7839KING PRESIDENT107698BLAKE MANAGER307782CLARK MANAGER107566JONES MANAGER20...NOTNULL約束(該列沒(méi)有記錄為NUILL)沒(méi)有NOTNULL約束(任何一個(gè)記錄該列可以為空值)NOTNULL約束NOTNULL約束束在列列一一級(jí)級(jí)上上定定義義約約束束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)存在)允許插入U(xiǎn)NIQUE約束可以定義義在表或或列一級(jí)級(jí)SQL>CREATETABLEdept(2deptnoNUMBER(2),3 dnameVARCHAR2(14),4 locVARCHAR2(13),5 CONSTRAINTdept_dname_ukUNIQUE(dname));注:Oracle服務(wù)器是是通過(guò)隱隱含地在在唯一關(guān)關(guān)鍵字上上創(chuàng)建唯唯一索引引的方式式實(shí)施UNIQUE約束的。。PRIMARYKEY約束DEPTDEPTNODNAMELOC------------------------10ACCOUNTING NEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONS BOSTONPRIMARYKEYInsertinto

20 MARKETING DALLAS

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

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