oracle10g經(jīng)典實(shí)戰(zhàn)第3章-數(shù)據(jù)庫的操作

1、第3章數(shù)據(jù)庫的操作 3.1操作表數(shù)據(jù) 通過PL/SQL語句操作表數(shù)據(jù)比較方便、靈活。所有SQL命令將在SQL*Plus中編譯完成。進(jìn)入SQL*Plus，用戶名和密碼分別為：ADMIN和manager。 3.1.1插入表記錄 語法格式： INSERT INTO table_namecolumn_list VALUES(constant1,constant2,) 該語句的功能是向由table_name指定的表中加入一行，由VALUES指定的各列值。 (1) 在插入時(shí)，列值表必須與列名表順序和數(shù)據(jù)類型一致。 (2) VALUES中描述的值可以是一個(gè)常量、變量或一個(gè)表達(dá)式。 (3) 如果列值為空，則值

2、必須置為NULL。如果列值指定為該列的默認(rèn)值，則用DEFAULT。 (4) 在對(duì)表進(jìn)行插入行時(shí)，若新插入的行中所有可取空值的列值均取空值，則就可以在INSERT語句中通過列表指出插入的行值中所包含非空的列，而在VALUES中只要給出這些列的值即可。3.1.1插入入表記錄錄【例3.1】向XSCJ數(shù)數(shù)據(jù)庫的的表XS中插入入如下的的一行:061101王王林林計(jì)計(jì)算機(jī)男男1987020150可以使用用如下的的PL/SQL語句:INSERTINTOXS(XH,XM,ZYM,XB,CSSJ,ZXF) VALUES(061101,王林,計(jì)算機(jī),男男,TO_DATE(19860210,YYYYMMDD),50

3、);請(qǐng)讀者使使用INSERTINTO語句向向?qū)W生表表插入以以下記錄錄：學(xué)號(hào)姓姓名專專業(yè)名名性性別別出出生生時(shí)間總總學(xué)分備備注注061102王王平計(jì)計(jì)算機(jī)女女1986-09-0238NULL061103王王燕計(jì)計(jì)算機(jī)機(jī)女女1985-10-0640NULL061104韋韋嚴(yán)平平計(jì)計(jì)算機(jī)男男1986-08-2640NULL061105吳吳慶紅紅計(jì)算算機(jī)男男1985-12-3036有有兩門門不及格格061106李李方方方計(jì)計(jì)算機(jī)男男1986-11-2040NULL061107李李明計(jì)計(jì)算機(jī)機(jī)男男1986-05-0140NULL061108林林一帆帆計(jì)計(jì)算機(jī)男男1985-08-0542已提提前修完完一門

4、課課061109張張強(qiáng)民民計(jì)計(jì)算機(jī)男男1984-08-1140NULL061110張張蔚計(jì)計(jì)算機(jī)機(jī)女女1987-07-2240NULL061111趙趙琳計(jì)計(jì)算機(jī)機(jī)女女1986-03-1840NULL061112羅羅林琳琳計(jì)計(jì)算機(jī)女女1984-01-3040NULL3.1.1插入入表記錄錄用SELECT語句進(jìn)進(jìn)行查詢?cè)?，可以以發(fā)現(xiàn)表表中已經(jīng)經(jīng)增加的的記錄，如圖3.1所所示。圖3.1插入新新數(shù)據(jù)后后查詢的的結(jié)果3.1.1插入入表記錄錄【例3.2】建立了表表test。CREATETABLEtest(xmchar(20)NOT NULL,zyvarchar(30)DEFAULT(計(jì)計(jì)算機(jī)),njnum

5、berNOT NULL);用INSERT向test表表中插入入一條記記錄：INSERTINTOtest(xm,nj)VALUES(王林林,3);則插入到到test表中中的記錄錄為：王王林計(jì)計(jì)算機(jī)機(jī)3語法格式式：INSERTINTOtable_namederived_tablederived_table是一個(gè)個(gè)由SELECT語句句查詢所所得到的的結(jié)果集集。利用用該參數(shù)數(shù)，可把把一個(gè)表中中的部分分?jǐn)?shù)據(jù)插插入到表表table_name中。3.1.1插入入表記錄錄【例3.3】用如下下的CREATE語句句建立表表XS1：CREATETABLEXS1(numchar(6)NOT NULL,namechar

6、(8) NOTNULL,speialitychar(10)NULL);用如下的的INSERT語句向向XS1表中插插入數(shù)據(jù)據(jù)：INSERTINTOXS1SELECTXH,XM,ZYMFROM XSWHEREZYM=計(jì)算算機(jī);3.1.1插入入表記錄錄這條INSERT語句句將XS表中專專業(yè)名為為計(jì)算算機(jī)的的各記錄錄的學(xué)號(hào)號(hào)、姓名名和專業(yè)業(yè)名列的值插插入到XS1表表的各行行中。用用SELECT語句可可查看插插入結(jié)果果：numnamespeciality-061101王王林林計(jì)計(jì)算機(jī)機(jī)061102王王平平計(jì)計(jì)算機(jī)機(jī)061103王王燕燕計(jì)計(jì)算機(jī)機(jī)061104韋韋嚴(yán)嚴(yán)平計(jì)計(jì)算算機(jī)061105吳吳慶慶紅計(jì)計(jì)算

7、算機(jī)061106李李方方方計(jì)計(jì)算算機(jī)061107李李明明計(jì)計(jì)算機(jī)機(jī)061108林林一一帆計(jì)計(jì)算算機(jī)061109張張強(qiáng)強(qiáng)民計(jì)計(jì)算機(jī)機(jī)061110張張蔚蔚計(jì)計(jì)算機(jī)機(jī)061111趙趙琳琳計(jì)計(jì)算機(jī)機(jī)061112羅羅林林琳計(jì)計(jì)算機(jī)機(jī)從Oracle 10g開始始新增了了MERGE語語句，使使用這個(gè)個(gè)語句可可以實(shí)現(xiàn)現(xiàn)對(duì)表的的更新或或插入。3.1.1插入入表記錄錄語法格式式：MERGEINTO table_nameUSINGtable_name ON (join_condition)WHEN MATCHEDTHENUPDATESETWHEN NOTMATCHED THENINSERT() VALUES()Jo

8、ni_condition：連連接條件件?！纠?.4】 檢查查表XS_JSJ中的的數(shù)據(jù)是是否和表表XS的的數(shù)據(jù)相相匹配，如果匹匹配則使使用INSERT子子句執(zhí)行行插入數(shù)數(shù)據(jù)行。首先在表表XS_JSJ使用INSERT語語句添加加一行數(shù)數(shù)據(jù)：INSERTINTOXS_JSJ(XH,XM,ZYM,XB,CSSJ,ZXF)VALUES(101112,霍甲甲,計(jì)算機(jī)機(jī),女,TO_DATE(19860130,YYYYMMDD),36);使用MERGE語句XS_JSJ表表中新增增的數(shù)據(jù)據(jù)插入表表XS中中：MERGEINTO XS aUSINGXS_JSJbON(a.XH=b.XH)WHEN NOTMATCHE

9、D THENINSERTValues(b.xh,b.xm,b.zym,b.xb,b.cssj,b.zxf,b.bz);用SELECT語句進(jìn)進(jìn)行查詢?cè)?，可以以發(fā)現(xiàn)表表中已經(jīng)經(jīng)增加一一條姓名名為霍甲甲的學(xué)生生記錄。3.1.2刪除除表記錄錄1.使使用DELETE刪除數(shù)數(shù)據(jù)在PL/SQL語言中中，刪除除行可以以使用DELETE語語句。語法格式式：DELETEFROMtable_nameview_nameWHEREcondition該語句的的功能為為從table_name指指定的表表或view_name指定定的視圖圖中刪除除滿足condition查查詢條條件的行行，若省省略該條條件，表表示刪除除所有的的

10、行?！纠?.5】將XSCJ數(shù)數(shù)據(jù)庫的的XS表表中總學(xué)學(xué)分小于于39的的行刪除除，使用用如下的的PL/SQL語句。DELETEFROMXSWHEREZXF=0AND總學(xué)分分=0ANDCJ=100”，單擊“確定”按鈕，完成CHECK約約束的創(chuàng)創(chuàng)建，系系統(tǒng)返回回到圖3.19所示界界面，單單擊“應(yīng)應(yīng)用”保保存上述述操作。圖3.20添加加CHECK約約束條件件界面3.6.3域完完整性的的實(shí)現(xiàn)(2)利利用用SQL語句在在創(chuàng)建表表時(shí)創(chuàng)建建CHECK約約束語法格式式：CREATETABLEtable_name/*指定定表名*/(column_namedatatypeNOTNULL |NULLDEFAULTco

11、nstraint_expression/*默認(rèn)值值*/CONSTRAINT check_nameCHECK(check_expression),n)/*CHECK約束表表達(dá)式*/【例3.27】在 XSCJ數(shù)數(shù)據(jù)庫庫中創(chuàng)建建表books，其中中包含所所有的約約束定義義。CREATETABLEbooks(book_id number(10),book_namevarchar2(50) notnull,book_descvarchar2(50) DEFAULTNewbook,max_lvl number(3,2) notnull,trade_pricenumber(4,1)notnull,CONST

12、RAINT ch_costCHECK(max_lvl=250);3.6.3域完完整性的的實(shí)現(xiàn)(3)利利用用SQL語句在在修改表表時(shí)創(chuàng)建建CHECK約約束語法格式式：ALTERTABLEtable_nameADD( CONSTRAINTcheck_nameCHECK(check_expression)【例3.28】通過修改改XSCJ數(shù)據(jù)據(jù)庫的books表，增加批批發(fā)價(jià)字字段trade_price的CHECK約束束。ALTERTABLEbooksADD( CONSTRAINTch_priceCHECK(trade_price=250);(4)利利用用SQL語句刪刪除CHECK約束CHECK約束束的

13、刪除除可在OEM中中刪除，有興趣趣的讀者者可以自自己試一一試，在在此介紹如何利利用SQL命令令刪除。語法格式式：ALTERTABLEtable_nameDROP CONSTRAINTcheck_name功能：在在table_name指定定的表中中，刪除除名為check_name的約束束?！纠?.29】刪除XSCJ數(shù)數(shù)據(jù)庫中中books表表批發(fā)價(jià)價(jià)字段的的CHECK約約束。ALTERTABLEbooksDROP CONSTRAINTch_price;3.6.4實(shí)體體完整性性的實(shí)現(xiàn)現(xiàn)如前所述述，表中中應(yīng)有一一個(gè)列或或列的組組合，其其值能唯唯一地標(biāo)標(biāo)識(shí)表中中的每一一行，選選擇這樣的的一列或或多列作作

14、為主鍵鍵可實(shí)現(xiàn)現(xiàn)表的實(shí)實(shí)體完整整性。一個(gè)表只只能有一一個(gè)PRIMARY KEY約約束，而而且PRIMARY KEY約約束中的的列不能取空值值。由于于 PRIMARYKEY 約束束能確保保數(shù)據(jù)的的唯一，所以經(jīng)經(jīng)常用來來定義標(biāo)標(biāo)識(shí)列。當(dāng)當(dāng)為表定定義PRIMARYKEY約束時(shí)時(shí)，Oracle10g為為主鍵列列創(chuàng)建唯唯一索引引，實(shí)現(xiàn)數(shù)據(jù)據(jù)的唯一一性，在在查詢中中使用主主鍵時(shí)，該索引引可用來來對(duì)數(shù)據(jù)據(jù)進(jìn)行快快速訪問問。如果PRIMARY KEY約約束是由由多列組組合定義義的，則則某一列列的值可可以重復(fù)復(fù)，但PRIMARY KEY約約束定義義中所有有列的組組合值必必須唯一一。PRIMARY KEY約束束與

15、UNIQUE約束束的主要要區(qū)別如如下：(1)一一個(gè)個(gè)數(shù)據(jù)表表只能創(chuàng)創(chuàng)建一個(gè)個(gè)PRIMARYKEY約約束，但但一個(gè)表表中可根根據(jù)需要要對(duì)不同的列創(chuàng)創(chuàng)建若干干個(gè)UNIQUE約束束；(2)PRIMARYKEY字段的的值不允允許為NULL，而UNIQUE字字段的值值可取NULL；PRIMARY KEY約束束與UNIQUE約束束的相同同點(diǎn)在于于：二者者均不允允許表中中對(duì)應(yīng)字字段存在重復(fù)值值；在創(chuàng)創(chuàng)建PRIMARYKEY約束與與UNIQUE約束時(shí)時(shí)會(huì)自動(dòng)動(dòng)產(chǎn)生索索引。3.6.4實(shí)體體完整性性的實(shí)現(xiàn)現(xiàn)對(duì)于PRIMARYKEY約束與與UNIQUE約束來來說，都都是由索索引強(qiáng)制制實(shí)現(xiàn)。在實(shí)現(xiàn)現(xiàn)PRIMARY K

16、EY約束束與UNIQUE約束束時(shí)，Oracle按按照下面面過程來來實(shí)現(xiàn)：(1)如如果果禁止該該約束，則不創(chuàng)創(chuàng)建索引引。(2)如如果果約束是是允許的的，且約約束中的的列是某某個(gè)索引引的一部部分，則則該索引引用來強(qiáng)強(qiáng)制約束束。(3)如如果果約束是是允許的的，且約約束中的的列都不不是某個(gè)個(gè)索引的的一部分分，那么么按照下下面的規(guī)則則創(chuàng)建索索引：如如果約束束是可延延遲的，則在這這種約束束的列上上創(chuàng)建一一個(gè)非唯唯一性索索引。如如果約束束是非可可延遲的的，則創(chuàng)創(chuàng)建一個(gè)個(gè)唯一性性索引。1.利利用OEM創(chuàng)創(chuàng)建和刪刪除PRIMAYKEY或或UNIQUE約束利用OEM創(chuàng)建建和刪除除PRIMAY KEY或UNIQUE

17、約約束的操操作過程程同利用用OEM創(chuàng)建和刪除除CHECK約約束基本本相同，唯一的的區(qū)別是是在選擇擇約束類類型時(shí)選選擇PRIMAYKEY或或UNIQUE約束，詳細(xì)操操作請(qǐng)參參照利用用OEM創(chuàng)建和和刪除CHECK約束束。3.6.4實(shí)體體完整性性的實(shí)現(xiàn)現(xiàn)2.利利用SQL語句句創(chuàng)建和和刪除PRIMAYKEY及UNIQUE約束束(1)創(chuàng)創(chuàng)建建表的同同時(shí)創(chuàng)建建PRIMAY KEY或UNIQUE約約束語法格式式：CREATETABLEtable_name/*指指定表名名*/(column_namedatatype/*定義義字段*/CONSTRAINTconstraint_name/*定定義約束束名*/NOT

18、NULLPRIMARY KEY| UNIQUE/*定義約約束類型型*/,n)/*n表示示可定義義多個(gè)字字段*/在語法格格式中，通過關(guān)關(guān)鍵字PRIMARY KEY、UNIQUE說說明所創(chuàng)創(chuàng)建的約約束類型型。3.6.4實(shí)體體完整性性的實(shí)現(xiàn)現(xiàn)2.利利用SQL語語句創(chuàng)建建和刪除除PRIMAY KEY及UNIQUE約約束【例3.30】對(duì)XSCJ數(shù)據(jù)據(jù)庫中XS表的的學(xué)號(hào)字字段創(chuàng)建建PRIMARYKEY約約束，對(duì)對(duì)身份證號(hào)碼碼字段定定義UNIQUE約束束。CREATETABLEXS(XHchar(6) NOTNULLCONSTRAINT PK_XH PRIMARYKEY,XMchar(8) NOTNULL,

19、INDENTITYchar(20)CONSTRAINT UN_ID UNIQUE,ZYMchar(10)NULL,XBchar(2) NOTNULL,CSSJ ldate NOTNULL,ZXFnumber(2) NULL,BZvarchar2(100) NULL,RXSJ date);3.6.4實(shí)體體完整性性的實(shí)現(xiàn)現(xiàn)2.利利用SQL語語句創(chuàng)建建和刪除除PRIMAY KEY及UNIQUE約約束(2)利利用用修改表表創(chuàng)建PRIMAYKEY或UNIQUE約束束語法格式式：ALTERTABLEtable_nameADD( CONSTRAINTconstraint_name PRIMARYKEY(co

20、lumn_name,n)說明：這是創(chuàng)建建PRIMAY KEY約束束，ADDCONSTRAINT表示對(duì)對(duì)table_name表增增加一個(gè)個(gè)約束，約束名由constraint_name指指定，約約束類型型為PRIMARYKEY。索引引字段由由column_name參參數(shù)指定，可包含含一列或或多列。語法格式式：ALTERTABLEtable_nameADD(CONSTRAINTconstraint_nameUNIQUE(column_name,n)這是創(chuàng)建建UNIQUE約束。3.6.4實(shí)體體完整性性的實(shí)現(xiàn)現(xiàn)【例3.31】先在XSCJ數(shù)數(shù)據(jù)庫中中創(chuàng)建表表XS，然后通通過修改改表，對(duì)對(duì)學(xué)號(hào)字字段創(chuàng)建建P

21、RIMARY KEY約束束，對(duì)身身份證號(hào)號(hào)碼字段段定義UNIQUE約約束。CREATETABLEXS(XHchar(6) NOTNULL,XMchar(8) NOTNULL,INDENTITYchar(20),ZYMchar(10)NULL,XBchar(2) NOTNULL,CSSJ dateNOT NULL,ZXFnumber(2) NULL,BZvarchar2(100) NULL,RXSJ date);ALTERTABLEXSADD(CONSTRAINTPK_XSPRIMARY KEY(XH);ALTERTABLEXSADD(CONSTRAINTUN_XSUNIQUE(INDENTIT

22、Y);3.6.4實(shí)體體完整性性的實(shí)現(xiàn)現(xiàn)2.利利用SQL語語句創(chuàng)建建和刪除除PRIMAY KEY及UNIQUE約約束(3)刪刪除PRIMARY KEY或UNIQUE約約束語法格式式：ALTERTABLEtable_nameDROP CONSTRAINTconstraint_name,n;【例3.32】刪除創(chuàng)創(chuàng)建的UN_XS的UNIQUE約約束。ALTERTABLEXSDROP CONSTRAINTUN_XS;3.6.5參照照完整性性的實(shí)現(xiàn)現(xiàn)對(duì)兩個(gè)相相關(guān)聯(lián)的的表(主主表與從從表)進(jìn)進(jìn)行數(shù)據(jù)據(jù)插入和和刪除時(shí)時(shí)，通過過參照完完整性保保證它們之之間數(shù)據(jù)據(jù)的一致致性。利利用FOREIGNKEY定義從從表的

23、外外鍵，PRIMARYKEY約約束定義義主表中中的主鍵鍵(不允允許為空空)，可可實(shí)現(xiàn)主主表與從從表之間間的參照照完整性性。定義表間間參照關(guān)關(guān)系，可可先定義義主鍵，再對(duì)從從表定義義外鍵約約束（根根據(jù)查詢?cè)兊男枰上葘?duì)從從表的該該列創(chuàng)建建索引）。對(duì)于FOREIGNKEY約束來來說，在在創(chuàng)建時(shí)時(shí)應(yīng)該考考慮以下下因素：(1)在在刪刪除主表表之前，必須刪刪除FOREIGNKEY約束。(2)如如果果不刪除除或禁止止FOREIGNKEY約約束，則則不能刪刪除主表表。(3)在在刪刪除包含含主表的的表空間間之前，必須刪刪除FOREIGNKEY約束。3.6.5參照照完整性性的實(shí)現(xiàn)現(xiàn)1.利利用OEM定定義表之之間

24、的參參照關(guān)系系例如，要要建立XS表和和XS_KC表表之間的的參照完完整性，操作步步驟如下下：(1)按按照照前面所所介紹的的方法定定義主表表的主鍵鍵。在此此，定義義XS表表中的學(xué)學(xué)號(hào)字段段為主鍵。(2)如如圖圖3.21所示示，選擇擇“FOREIGN”約束條條件，單單擊“添添加”按按鈕，進(jìn)進(jìn)入“添添加FOREIGN約束條條件”界界面，如如圖3.22所所示。(3)在在名名稱欄輸輸入約束束名稱FK_XH。在在表列項(xiàng)項(xiàng)的“可可用列”列表選選擇“XH”字字段添加到所所選列列列表。在在引用表表列項(xiàng)的的引用表表選擇所所關(guān)聯(lián)表表；單擊擊“開始始”，引引入關(guān)聯(lián)表所有有字段到到可用列列列表，選擇“XH”字段添添加至

25、所所選列。單擊“確定”，系統(tǒng)統(tǒng)會(huì)自動(dòng)完成成創(chuàng)建工工作。圖3.21修改改表界面面圖3.22添加加FOREIGN約束束條件界界面3.6.5參照照完整性性的實(shí)現(xiàn)現(xiàn)2.利利用OEM刪刪除表間間的參照照關(guān)系利用OEM刪除除表間的的參照關(guān)關(guān)系和刪刪除CHECK約束的的方法一一樣，請(qǐng)請(qǐng)參照刪刪除CHECK約束束的方法法。3.利利用SQL命命令定義義表間的的參照關(guān)關(guān)系前面已介介紹了創(chuàng)創(chuàng)建主鍵鍵(PRMARYKEY約約束)的的方法，在此將將介紹通通過SQL命令創(chuàng)建建外鍵的的方法。(1)創(chuàng)創(chuàng)建建表時(shí)同同時(shí)定義義外鍵約約束語法格式式：CREATETABLEtable_name/*指指定表名名*/(column_namedatatypeFOREIGNKEYREFERENCES ref_table(ref_column),n/*n表表示可定定義多個(gè)個(gè)字段*/3.6.5參照照完整性性的