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

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

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

3、ATE(19860210,YYYYMMDD),50);請讀者使用INSERT INTO語句向?qū)W生表插入以下記錄： 學號學號 姓名姓名 專業(yè)名專業(yè)名 性別性別 出生時間出生時間 總學分總學分 備注備注061102 王平 計算機 女 1986-09-02 38 NULL061103王燕 計算機 女1985-10-06 40NULL061104韋嚴平 計算機 男1986-08-26 40NULL061105吳慶紅計算機男1985-12-3036有兩門不及格061106李方方 計算機 男1986-11-20 40NULL061107李明 計算機 男1986-05-01 40NULL061108林一帆

4、計算機 男1985-08-05 42已提前修完一門課061109張強民 計算機 男1984-08-11 40NULL061110張蔚 計算機 女1987-07-22 40NULL061111趙琳 計算機 女1986-03-18 40NULL061112羅林琳 計算機 女1984-01-30 40NULL 3.1.1插入表記錄插入表記錄用SELECT語句進行查詢，可以發(fā)現(xiàn)表中已經(jīng)增加的記錄，如圖3.1所示。圖圖3.1插入新數(shù)據(jù)后查詢的結(jié)果插入新數(shù)據(jù)后查詢的結(jié)果3.1.1插入表記錄插入表記錄 【例例3.2】建立了表test。 CREATE TABLE test ( xm char(20) NOT

5、NULL, zy varchar(30) DEFAULT(計算機), nj number NOT NULL ); 用INSERT向test表中插入一條記錄： INSERT INTO test(xm,nj) VALUES(王林,3); 則插入到test表中的記錄為：王林 計算機 3 語法格式：語法格式： INSERT INTO table_name derived_table derived_table是一個由SELECT語句查詢所得到的結(jié)果集。利用該參數(shù)，可把一個表中的部分數(shù)據(jù)插入到表table_name中。3.1.1插入表記錄插入表記錄【例例3.3】用如下的CREATE語句建立表XS1： C

6、REATE TABLE XS1 ( num char(6) NOT NULL, name char(8) NOT NULL, speiality char(10) NULL );用如下的INSERT語句向XS1表中插入數(shù)據(jù)： INSERT INTO XS1 SELECT XH,XM,ZYM FROM XS WHERE ZYM=計算機;3.1.1插入表記錄插入表記錄 這條INSERT語句將XS表中專業(yè)名為計算機的各記錄的學號、姓名和專業(yè)名列的值插入到XS1表的各行中。用SELECT語句可查看插入結(jié)果： num name speciality - - - 061101 王林 計算機 061102

7、王平 計算機 061103 王燕 計算機 061104 韋嚴平 計算機 061105 吳慶紅 計算機 061106 李方方 計算機 061107 李明 計算機 061108 林一帆 計算機 061109 張強民 計算機 061110 張蔚 計算機 061111 趙琳 計算機 061112 羅林琳 計算機 從Oracle 10g開始新增了MERGE語句，使用這個語句可以實現(xiàn)對表的更新或插入。3.1.1插入表記錄插入表記錄 語法格式：語法格式： MERGE INTO table_name USING table_name ON (join_condition) WHEN MATCHED THEN

8、UPDATE SET WHEN NOT MATCHED THEN INSERT() VALUES() Joni_condition：連接條件。 【例例3.4】 檢查表XS_JSJ中的數(shù)據(jù)是否和表XS的數(shù)據(jù)相匹配，如果匹配則使用INSERT子句執(zhí)行插入數(shù)據(jù)行。 首先在表XS_JSJ使用INSERT語句添加一行數(shù)據(jù)： INSERT INTO XS_JSJ(XH,XM,ZYM,XB,CSSJ,ZXF) VALUES(101112,霍甲, 計算機,女,TO_DATE(19860130,YYYYMMDD),36); 使用MERGE語句XS_JSJ表中新增的數(shù)據(jù)插入表XS中： MERGE INTO XS

9、a USING XS_JSJ b ON(a.XH=b.XH) WHEN NOT MATCHED THEN INSERT Values(b.xh,b.xm,b.zym,b.xb,b.cssj,b.zxf,b.bz); 用SELECT語句進行查詢，可以發(fā)現(xiàn)表中已經(jīng)增加一條姓名為霍甲的學生記錄。 3.1.2刪除表記錄刪除表記錄 1. 使用使用 DELETE刪除數(shù)據(jù)刪除數(shù)據(jù) 在PL/SQL語言中，刪除行可以使用DELETE語句。 語法格式：語法格式： DELETE FROM table_name view_name WHERE condition 該語句的功能為從table_name指定的表或view

10、_name指定的視圖中刪除滿足condition 查詢條件的行，若省略該條件，表示刪除所有的行。 【例例3.5】將XSCJ數(shù)據(jù)庫的XS表中總學分小于39的行刪除，使用如下的PL/SQL語句。 DELETE FROM XS WHERE ZXF=0 AND總學分=0 AND CJ=100”，單擊“確定”按鈕，完成CHECK約束的創(chuàng)建，系統(tǒng)返回到圖3.19所示界面，單擊“應用”保存上述操作。 圖圖3.20添加添加CHECK約束條件界面約束條件界面3.6.3域完整性的實現(xiàn)域完整性的實現(xiàn) (2) 利用SQL語句在創(chuàng)建表時創(chuàng)建CHECK約束 語法格式：語法格式： CREATE TABLE table_na

11、me /*指定表名*/ ( column_name datatype NOT NULL | NULL DEFAULT constraint_expression /*默認值*/ CONSTRAINT check_name CHECK(check_expression),n) /*CHECK約束表達式*/ 【例例3.27】在 XSCJ 數(shù)據(jù)庫中創(chuàng)建表books，其中包含所有的約束定義。 CREATE TABLE books ( book_id number(10), book_name varchar2(50) not null, book_desc varchar2(50) DEFAULT N

12、ew book, max_lvl number(3,2) not null, trade_price number(4,1) not null, CONSTRAINT ch_cost CHECK(max_lvl=250);3.6.3域完整性的實現(xiàn)域完整性的實現(xiàn) (3) 利用SQL語句在修改表時創(chuàng)建CHECK約束 語法格式：語法格式： ALTER TABLE table_name ADD( CONSTRAINT check_name CHECK(check_expression) 【例例3.28】通過修改XSCJ數(shù)據(jù)庫的books表，增加批發(fā)價字段trade_price的CHECK約束。 ALT

13、ER TABLE books ADD( CONSTRAINT ch_price CHECK(trade_price=250); (4) 利用SQL語句刪除CHECK約束 CHECK約束的刪除可在OEM中刪除，有興趣的讀者可以自己試一試，在此介紹如何利用SQL命令刪除。 語法格式：語法格式： ALTER TABLE table_name DROP CONSTRAINT check_name 功能：在table_name指定的表中，刪除名為check_name的約束。 【例例3.29】刪除XSCJ數(shù)據(jù)庫中books表批發(fā)價字段的CHECK約束。 ALTER TABLE books DROP CON

14、STRAINT ch_price;3.6.4實體完整性的實現(xiàn)實體完整性的實現(xiàn) 如前所述，表中應有一個列或列的組合，其值能唯一地標識表中的每一行，選擇這樣的一列或多列作為主鍵可實現(xiàn)表的實體完整性。 一個表只能有一個 PRIMARY KEY 約束，而且 PRIMARY KEY 約束中的列不能取空值。由于 PRIMARY KEY 約束能確保數(shù)據(jù)的唯一，所以經(jīng)常用來定義標識列。當為表定義PRIMARY KEY約束時，Oracle 10g為主鍵列創(chuàng)建唯一索引，實現(xiàn)數(shù)據(jù)的唯一性，在查詢中使用主鍵時，該索引可用來對數(shù)據(jù)進行快速訪問。如果 PRIMARY KEY 約束是由多列組合定義的，則某一列的值可以重復，

15、但 PRIMARY KEY 約束定義中所有列的組合值必須唯一。 PRIMARY KEY約束與UNIQUE約束的主要區(qū)別如下： (1) 一個數(shù)據(jù)表只能創(chuàng)建一個PRIMARY KEY約束，但一個表中可根據(jù)需要對不同的列創(chuàng)建若干個UNIQUE約束； (2) PRIMARY KEY字段的值不允許為NULL，而UNIQUE字段的值可取NULL；PRIMARY KEY約束與UNIQUE約束的相同點在于：二者均不允許表中對應字段存在重復值；在創(chuàng)建PRIMARY KEY約束與UNIQUE約束時會自動產(chǎn)生索引。 3.6.4實體完整性的實現(xiàn)實體完整性的實現(xiàn) 對于PRIMARY KEY約束與UNIQUE約束來說，都

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

17、刪除CHECK約束基本相同，唯一的區(qū)別是在選擇約束類型時選擇PRIMAY KEY或UNIQUE約束，詳細操作請參照利用OEM創(chuàng)建和刪除CHECK約束。 3.6.4實體完整性的實現(xiàn)實體完整性的實現(xiàn)2. 利用利用SQL語句創(chuàng)建和刪除語句創(chuàng)建和刪除PRIMAY KEY及及UNIQUE約束約束(1) 創(chuàng)建表的同時創(chuàng)建PRIMAY KEY或UNIQUE約束語法格式：語法格式：CREATE TABLE table_name /*指定表名*/(column_name datatype /*定義字段*/CONSTRAINT constraint_name /*定義約束名*/NOT NULLPRIMARY KE

18、Y | UNIQUE /*定義約束類型*/,n) /*n表示可定義多個字段*/在語法格式中，通過關(guān)鍵字PRIMARY KEY、UNIQUE說明所創(chuàng)建的約束類型。 3.6.4實體完整性的實現(xiàn)實體完整性的實現(xiàn) 2. 利用利用SQL語句創(chuàng)建和刪除語句創(chuàng)建和刪除PRIMAY KEY及及UNIQUE約束約束 【例例3.30】對XSCJ數(shù)據(jù)庫中XS表的學號字段創(chuàng)建PRIMARY KEY約束，對身份證號碼字段定義UNIQUE約束。 CREATE TABLE XS ( XH char(6) NOT NULL CONSTRAINT PK_XH PRIMARY KEY, XM char(8) NOT NULL,

19、INDENTITY char(20) CONSTRAINT UN_ID UNIQUE, ZYM char(10) NULL, XB char(2) NOT NULL, CSSJ ldate NOT NULL, ZXF number(2) NULL, BZ varchar2(100) NULL, RXSJ date );3.6.4實體完整性的實現(xiàn)實體完整性的實現(xiàn)2. 利用利用SQL語句創(chuàng)建和刪除語句創(chuàng)建和刪除PRIMAY KEY及及UNIQUE約束約束 (2) 利用修改表創(chuàng)建PRIMAY KEY或UNIQUE約束 語法格式語法格式： ALTER TABLE table_name ADD( CON

20、STRAINT constraint_name PRIMARY KEY(column_name,n)說明：說明：這是創(chuàng)建PRIMAY KEY約束，ADD CONSTRAINT表示對table_name表增加一個約束，約束名由constraint_name指定，約束類型為PRIMARY KEY。索引字段由column_name參數(shù)指定，可包含一列或多列。語法格式：語法格式： ALTER TABLE table_name ADD(CONSTRAINT constraint_name UNIQUE(column_name,n)這是創(chuàng)建UNIQUE約束。 3.6.4實體完整性的實現(xiàn)實體完整性的實現(xiàn) 【

21、例例3.31】 先在XSCJ數(shù)據(jù)庫中創(chuàng)建表XS，然后通過修改表，對學號字段創(chuàng)建PRIMARY KEY約束，對身份證號碼字段定義UNIQUE約束。 CREATE TABLE XS ( XH char(6) NOT NULL, XM char(8) NOT NULL, INDENTITY char(20), ZYM char(10) NULL, XB char(2) NOT NULL, CSSJ date NOT NULL, ZXF number(2) NULL, BZ varchar2(100) NULL, RXSJ date ); ALTER TABLE XS ADD(CONSTRAINT P

22、K_XS PRIMARY KEY(XH); ALTER TABLE XS ADD(CONSTRAINT UN_XS UNIQUE(INDENTITY);3.6.4實體完整性的實現(xiàn)實體完整性的實現(xiàn)2. 利用利用SQL語句創(chuàng)建和刪除語句創(chuàng)建和刪除PRIMAY KEY及及UNIQUE約束約束(3) 刪除PRIMARY KEY或UNIQUE約束語法格式： ALTER TABLE table_name DROP CONSTRAINT constraint_name,n;【例3.32】刪除創(chuàng)建的UN_XS的UNIQUE約束。 ALTER TABLE XS DROP CONSTRAINT UN_XS;3.6

23、.5參照完整性的實現(xiàn)參照完整性的實現(xiàn) 對兩個相關(guān)聯(lián)的表(主表與從表)進行數(shù)據(jù)插入和刪除時，通過參照完整性保證它們之間數(shù)據(jù)的一致性。利用FOREIGN KEY定義從表的外鍵，PRIMARY KEY約束定義主表中的主鍵(不允許為空)，可實現(xiàn)主表與從表之間的參照完整性。 定義表間參照關(guān)系，可先定義主鍵，再對從表定義外鍵約束（根據(jù)查詢的需要可先對從表的該列創(chuàng)建索引）。 對于FOREIGN KEY約束來說，在創(chuàng)建時應該考慮以下因素： (1) 在刪除主表之前，必須刪除FOREIGN KEY約束。 (2) 如果不刪除或禁止FOREIGN KEY約束，則不能刪除主表。 (3) 在刪除包含主表的表空間之前，必須

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

25、選擇“XH”字段添加至所選列。單擊“確定”，系統(tǒng)會自動完成創(chuàng)建工作。圖圖3.21修改表界面修改表界面 圖圖3.22添加添加FOREIGN約束條件界面約束條件界面 3.6.5參照完整性的實現(xiàn)參照完整性的實現(xiàn) 2. 利用利用OEM刪除表間的參照關(guān)系刪除表間的參照關(guān)系利用OEM刪除表間的參照關(guān)系和刪除CHECK約束的方法一樣，請參照刪除CHECK約束的方法。 3. 利用利用SQL命令定義表間的參照關(guān)系命令定義表間的參照關(guān)系 前面已介紹了創(chuàng)建主鍵(PRMARY KEY約束)的方法，在此將介紹通過SQL命令創(chuàng)建外鍵的方法。 (1) 創(chuàng)建表時同時定義外鍵約束語法格式：語法格式：CREATE TABLE table_name /*指定表名*/(column_name datatype FOREIGN KEYREFERENCES ref_table(ref_column),n /*n表示可定義多個字段*/3.6.5參照完整性的實現(xiàn)參照完整性的實現(xiàn) 【例例3.33】在XSCJ數(shù)據(jù)庫中創(chuàng)建主表XS，XS.XH為主鍵，然后定