Oracle數(shù)據(jù)庫(kù)管理、開(kāi)發(fā)與實(shí)踐 教學(xué)課件 楊永健 劉尚毅SQL語(yǔ)言基礎(chǔ)及調(diào)優(yōu)

1、在線(xiàn)教務(wù)輔導(dǎo)網(wǎng)： :/教材其余課件及動(dòng)畫(huà)素材請(qǐng)查閱在線(xiàn)教務(wù)輔導(dǎo)網(wǎng)QQ:349134187 或者直接輸入下面地址：本章要求： 第5章 SQL語(yǔ)言基礎(chǔ)及調(diào)優(yōu)了解SQL語(yǔ)言的特點(diǎn)理解用戶(hù)模式和模式對(duì)象掌握檢索數(shù)據(jù)的方法掌握Oracle常用的系統(tǒng)函數(shù)掌握子查詢(xún)的用法掌握操作數(shù)據(jù)庫(kù)的3種方法了解如何進(jìn)行事務(wù)處理掌握常用SQL語(yǔ)句的優(yōu)化掌握表連接的優(yōu)化理解合理使用索引對(duì)象了解Oracle優(yōu)化器的使用了解SQL重演和Oracle系能顧問(wèn)主要內(nèi)容1. SQL語(yǔ)言簡(jiǎn)介2. 用戶(hù)模式3. 檢索數(shù)據(jù)4. Oracle常用系統(tǒng)函數(shù)5. 子查詢(xún)的用法6. 操作數(shù)據(jù)庫(kù)7. 事務(wù)處理8. 常規(guī)SQL語(yǔ)句優(yōu)化9. 表連接優(yōu)化

2、10.合理使用索引11.優(yōu)化器的使用12.數(shù)據(jù)庫(kù)和SQL重演的性能顧問(wèn)14.綜合實(shí)例查詢(xún)工資不小于WARD的員工信息第5章 SQL語(yǔ)言基礎(chǔ)及調(diào)優(yōu)5.1.1 SQL語(yǔ)言的特點(diǎn)5.1.2 SQL語(yǔ)言的分類(lèi)5.1.3 SQL語(yǔ)言的編寫(xiě)規(guī)則5.1 SQL語(yǔ)言簡(jiǎn)介 SQL語(yǔ)言結(jié)構(gòu)簡(jiǎn)潔，功能強(qiáng)大，簡(jiǎn)單易學(xué)，自從被國(guó)際標(biāo)準(zhǔn)化組織(ISO)采納為國(guó)際標(biāo)準(zhǔn)以后，SQL語(yǔ)言得到了廣泛的應(yīng)用，它主要有以下特點(diǎn)。（1）集合性 SQL可以在高層的數(shù)據(jù)結(jié)構(gòu)上進(jìn)行工作，工作時(shí)不是單條的處理記錄，而對(duì)數(shù)據(jù)進(jìn)程成組的處理。所有SQL語(yǔ)句都接受集合作為輸入，并且返回集合作為輸出，SQL的集合特性允許一條SQL語(yǔ)句的結(jié)果作為另一條

3、SQL語(yǔ)句的輸入。（2）統(tǒng)一性 SQL為許多任務(wù)提供了統(tǒng)一的命令，這樣方便用戶(hù)學(xué)習(xí)和使用，基本的SQL命令只需很少時(shí)間就能學(xué)會(huì)，甚至最高級(jí)的命令也可以在幾天內(nèi)掌握。數(shù)據(jù)庫(kù)的操作任務(wù)通常包括以下幾方面：查詢(xún)數(shù)據(jù)。在表中插入、修改和刪除記錄。建立、修改和刪除數(shù)據(jù)對(duì)象?？刂茖?duì)數(shù)據(jù)和數(shù)據(jù)對(duì)象的讀寫(xiě)。保證數(shù)據(jù)庫(kù)一致性和完整性。（3）易于移植性 由于所有主要的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)都支持SQL語(yǔ)言，這樣用戶(hù)就比較容易將原先使用SQL的技能從一個(gè)RDBMS轉(zhuǎn)到另一個(gè)，而且用兼容SQL標(biāo)準(zhǔn)編寫(xiě)的程序都是可以移植的。5.1.1 SQL語(yǔ)言的特點(diǎn)5.1.2 SQL語(yǔ)言的分類(lèi) SQL是關(guān)系型數(shù)據(jù)庫(kù)的基本操作語(yǔ)言，是數(shù)

4、據(jù)庫(kù)管理系統(tǒng)與數(shù)據(jù)庫(kù)進(jìn)行交互的接口。它將數(shù)據(jù)查詢(xún)、數(shù)據(jù)操縱、事務(wù)控制、數(shù)據(jù)定義和數(shù)據(jù)控制功能基于一身，而這些功能又分別對(duì)應(yīng)著各自的SQL語(yǔ)言，具體如下。（1）數(shù)據(jù)查詢(xún)語(yǔ)言（DQL）用于檢索數(shù)據(jù)庫(kù)中的數(shù)據(jù)，主要是SELECT語(yǔ)句，它在操作數(shù)據(jù)庫(kù)的過(guò)程中使用最為頻繁。（2）數(shù)據(jù)操縱語(yǔ)言（DML）用于改變數(shù)據(jù)庫(kù)中的數(shù)據(jù)，主要包括INSERT，UPDATE和DELETE三條語(yǔ)句。其中，INSERT語(yǔ)句用于將數(shù)據(jù)插入到數(shù)據(jù)庫(kù)中，UPDATE語(yǔ)句用于更新數(shù)據(jù)庫(kù)中已經(jīng)存在的數(shù)據(jù)，而DELETE語(yǔ)句則用于刪除數(shù)據(jù)庫(kù)中已經(jīng)存在的數(shù)據(jù)。（3）事務(wù)控制語(yǔ)言（TCL）用于維護(hù)數(shù)據(jù)的一致性，包括COMMIT、ROLLB

5、ACK和SAVEPOINT三條語(yǔ)句。其中，COMMIT語(yǔ)句用于提交對(duì)數(shù)據(jù)庫(kù)的更改，ROLLBACK語(yǔ)句用于取消對(duì)數(shù)據(jù)庫(kù)的更改，而SAVEPOINT語(yǔ)句則用于設(shè)置保存點(diǎn)。（4）數(shù)據(jù)定義語(yǔ)言（DDL）用于建立、修改和刪除數(shù)據(jù)庫(kù)對(duì)象。比如，可以使用CREATE TABLE語(yǔ)句創(chuàng)建表；使用ALTER TABlE語(yǔ)句修改表結(jié)構(gòu)；使用DROP TABLE語(yǔ)句刪除表，（5）數(shù)據(jù)控制語(yǔ)言（DCL）用于執(zhí)行權(quán)限授予和權(quán)限受賄操作，主要包括GRANT和REVOKE兩條命令。其中，GRANT命令用于給用戶(hù)或角色授予權(quán)限，而REVOKE命令則用于收回用戶(hù)或角色所具有的權(quán)限。5.1.3 SQL語(yǔ)言的編寫(xiě)規(guī)則SQL關(guān)鍵字

6、不區(qū)分大小寫(xiě)，既可以使用大寫(xiě)格式，也可以使用小寫(xiě)格式，或者大小寫(xiě)格式混用?！纠?-1】 編寫(xiě)以下3條語(yǔ)句，對(duì)關(guān)鍵字（SELECT和FROM）分別使用大寫(xiě)格式、小寫(xiě)格式或大小寫(xiě)混用格式，代碼如下。SQL select empno,ename,sal from scott.emp;SQL SELECT empno,ename,sal FROM scott.emp;SQL selECT empno,ename,sal frOM scott.emp;分別執(zhí)行這3條SELECT語(yǔ)句，會(huì)發(fā)現(xiàn)結(jié)果完全相同。對(duì)象名和列名不區(qū)分大小寫(xiě)，它們既可以使用大寫(xiě)格式，也可以使用小寫(xiě)格式，或者大小寫(xiě)格式混用?！纠?-2】

7、 編寫(xiě)以下3條語(yǔ)句，對(duì)“表名和列名”分別使用大寫(xiě)格式、小寫(xiě)格式或大小寫(xiě)混用格式，代碼如下。SQL select empno,ename,sal from scott.emp;SQL select EMPNO,ENAME,SAL from SCOTT.EMP;SQL select emPNO,ename,sAL from scott.EmP;分別執(zhí)行這3條SELECT語(yǔ)句，會(huì)發(fā)現(xiàn)結(jié)果完全相同。字符值區(qū)分大小寫(xiě)。當(dāng)在SQL語(yǔ)句中引用字符值時(shí)，必須要給出正確的大小寫(xiě)數(shù)據(jù)，否則，不能得到正確的查詢(xún)結(jié)果。 【例5-3】 編寫(xiě)以下兩條語(yǔ)句，查詢(xún)表中職位是“銷(xiāo)售員”的記錄，要求兩條語(yǔ)句的查詢(xún)條件分別為“SA

8、LESMAN”和“salesman”，代碼如下。SQL select * from scott.emp where job=SALESMAN;SQL select * from scott.emp where job=salesman; 分別執(zhí)行這兩條SELECT語(yǔ)句，會(huì)發(fā)現(xiàn)結(jié)果不相同，因?yàn)椴樵?xún)條件是不相同的。 在SQL*Plus環(huán)境編寫(xiě)SQL語(yǔ)句時(shí)，如果SQL語(yǔ)句較短，則可以將語(yǔ)句放在一行上顯示；如果SQL語(yǔ)句很長(zhǎng)，為了便于用戶(hù)閱讀，則可以將語(yǔ)句分行顯示（并且Oracle會(huì)在除第一行之外的每一行前面自動(dòng)加上行號(hào)），當(dāng)SQL語(yǔ)句輸入完畢，要以分號(hào)作為結(jié)束符。 【例5-4】 檢索表中職位是SAL

9、ESMAN（銷(xiāo)售員）的記錄，并且分行編寫(xiě)SQL語(yǔ)句，代碼如下。SQL select empno,ename,job 3 where job=SALESMANorder by empno;說(shuō)明： 在SQL*Plus環(huán)境中編寫(xiě)較長(zhǎng)的SQL語(yǔ)句時(shí)，敲回車(chē)即可實(shí)現(xiàn)換行。當(dāng)要注意，在敲回車(chē)之前不要輸入分號(hào)，因?yàn)榉痔?hào)表示SQL語(yǔ)句的結(jié)束。5.2 用戶(hù)模式5.2.1 模式與模式對(duì)象5.2.2 示例模式SCOTT5.2.1 模式與模式對(duì)象 模式是一個(gè)數(shù)據(jù)庫(kù)對(duì)象的集合。模式為一個(gè)數(shù)據(jù)庫(kù)用戶(hù)所有，并且具有與該用戶(hù)相同的名稱(chēng)，比如，SYSTEM模式、SCOTT模式等。在一個(gè)模式內(nèi)部不可以直接訪(fǎng)問(wèn)其它模式的數(shù)據(jù)庫(kù)對(duì)象

10、，即使在具有訪(fǎng)問(wèn)權(quán)限的情況下，也需要指定模式名稱(chēng)才可以訪(fǎng)問(wèn)其它模式的數(shù)據(jù)庫(kù)對(duì)象。 模式對(duì)象是由用戶(hù)創(chuàng)建的邏輯結(jié)構(gòu)，用以存儲(chǔ)或引用數(shù)據(jù)。例如，前面章節(jié)中所講過(guò)的段（比如，表、索引等），以及用戶(hù)所擁有的其它非段的數(shù)據(jù)庫(kù)對(duì)象。這些非段的數(shù)據(jù)庫(kù)對(duì)象通常包括：約束、視圖、同義詞、過(guò)程以及程序包等。 那么，簡(jiǎn)單的說(shuō)，模式與模式對(duì)象之間的關(guān)系就是擁有與被擁有的關(guān)系，即模式擁有模式對(duì)象；而模式對(duì)象被模式所擁有。注意： 一個(gè)不屬于某個(gè)用戶(hù)所擁有的數(shù)據(jù)庫(kù)對(duì)象就不能稱(chēng)之為模式對(duì)象，比如角色，表空間及目錄等數(shù)據(jù)庫(kù)對(duì)象。 為了便于后面章節(jié)的講解，這里介紹一個(gè)典型的示例模式SCOTT模式，因?yàn)樵撃Ｊ郊捌渌鶕碛械哪Ｊ綄?duì)象在

11、本書(shū)中經(jīng)常作為被示例。 Oracle提供的SCOTT模式的目的，就是為了給用戶(hù)提供一些示例表和數(shù)據(jù)來(lái)展示Oracle數(shù)據(jù)庫(kù)的一些特性。SCOTT模式擁有的模式對(duì)象（都是數(shù)據(jù)表）如圖5-1所示。圖5-1 SCOTT模式擁有的模式對(duì)象5.2.2 示例模式SCOTT 【例5-5】 在SCOTT模式下，通過(guò)檢索user_tables表來(lái)顯示SCOTT模式所擁有的4個(gè)數(shù)據(jù)表，代碼如下。SQL connect scott/tiger已連接。SQL select table_name from user_tables;本例運(yùn)行結(jié)果如圖5-2所示。圖5-2 顯示SCOTT模式中的表 另外，用戶(hù)也可以在SYST

12、EM模式下可以查詢(xún)SCOTT模式所擁有的數(shù)據(jù)表，但要求使用dba_tables數(shù)據(jù)表。 【例5-6】 在SYSTEM模式下，通過(guò)檢索dba_tables表來(lái)顯示SCOTT模式所擁有的4個(gè)數(shù)據(jù)表，代碼如下。SQL connect system/1qaz2wsx已連接。SQL select table_name from dba_tables where owner =SCOTT;5.3 檢索數(shù)據(jù)5.3.1 簡(jiǎn)單查詢(xún)5.3.2 篩選查詢(xún)5.3.3 分組查詢(xún)5.3.4 排序查詢(xún)5.3.5 多表關(guān)聯(lián)查詢(xún) 只包含SELECT子句和FROM子句的查詢(xún)就是簡(jiǎn)單查詢(xún)，SELECT子句和FROM子句是SELEC

13、T語(yǔ)句的必選項(xiàng)，也就是說(shuō)每個(gè)SELECT語(yǔ)句都必須包含這兩個(gè)子句。其中，SELECT子句用于選擇想要在查詢(xún)結(jié)果中顯示的列，對(duì)于這些要顯示的列，即可以使用列名來(lái)表示，也可以使用星號(hào)（*）來(lái)表示。在檢索數(shù)據(jù)時(shí)，數(shù)據(jù)將按照SELECT子句后面指定的列名的順序來(lái)顯示；如果使用星號(hào)（*），則表示檢索所有的列，這時(shí)數(shù)據(jù)將按照表結(jié)構(gòu)的自然順序來(lái)顯示。1檢索所有的列 如果要檢索指定數(shù)據(jù)表的所有列，可以在SELECT子句后面使用星號(hào)（*）來(lái)實(shí)現(xiàn)。在檢索一個(gè)數(shù)據(jù)表時(shí)，要注意該表所屬于的模式。如果在指定表所屬的模式內(nèi)部檢索數(shù)據(jù)，則可以直接使用表名；如果不在指定表所屬的模式內(nèi)部檢索數(shù)據(jù)，則不但要查看當(dāng)前模式是否具有查

14、詢(xún)的權(quán)限，而且還要在表名前面加上其所屬的模式名稱(chēng)。5.3.1 簡(jiǎn)單查詢(xún) 【例5-7】 在SCOTT模式下，在SELECT語(yǔ)句中使用星號(hào)（*）來(lái)檢索dept表中所有的數(shù)據(jù)，代碼如下。SQL connect scott/1qaz2wsx已連接。SQL select * from dept;本例運(yùn)行結(jié)果如圖5-3所示。圖5-3 檢索dept表中所有的數(shù)據(jù)說(shuō)明：上面的SELECT語(yǔ)句若要在SYSTEM模式下執(zhí)行，則需要在表dept前面加上scott，即“”。 在上面的例子中，from子句的后面只有一個(gè)數(shù)據(jù)表，實(shí)際上可以在from子句的后面指定多個(gè)數(shù)據(jù)表，每個(gè)數(shù)據(jù)表名之間使用逗號(hào)（，）分隔開(kāi)，其語(yǔ)法格式

15、如下：FROM table_name1, table_name2, table_name3table_namen 【例5-8】 在SCOTT模式下，在from子句中指定兩個(gè)數(shù)據(jù)表dept和salgrade，代碼如下。SQL select * from dept,salgrade;2檢索指定的列 用戶(hù)可以指定查詢(xún)表中的某些列而不是全部列，并且被指定列的順序不受限制，指定部分列也稱(chēng)作投影操作。這些列名緊跟在SELECT關(guān)鍵字的后面，每個(gè)列名之間用逗號(hào)隔開(kāi)。其語(yǔ)法格式如下：SELECT column_name1,column_name2,column_name3,column_namen說(shuō)明：利用S

16、ELECT指定列的好處就是可以改變列在查詢(xún)結(jié)果中的默認(rèn)顯示順序。 在SELECT語(yǔ)句中使用WHERE子句可以實(shí)現(xiàn)對(duì)數(shù)據(jù)行的篩選操作，只有滿(mǎn)足WHERE子句中判斷條件的行才會(huì)顯示在結(jié)果集中，而那些不滿(mǎn)足WHERE子句判斷條件的行則不包括在結(jié)果集中。這種篩選操作是非常有意義的，通過(guò)篩選數(shù)據(jù)，可以從大量的數(shù)據(jù)中得到用戶(hù)所需要的數(shù)據(jù)。在SELECT語(yǔ)句中，WHERE子句位于FROM子句之后，其語(yǔ)法格式如下：SELECT columns_listFROM table_nameWHERE conditional_expression參數(shù)說(shuō)明如下：columns_list：字段列表。table_name：表

17、名。conditional_expression：篩選條件表達(dá)式。接下來(lái)對(duì)幾種常用的篩選情況進(jìn)行詳細(xì)講解。1比較篩選 可以在WHERE子句中使用比較運(yùn)算符來(lái)篩選數(shù)據(jù)，這樣只有滿(mǎn)足篩選條件的數(shù)據(jù)行才會(huì)被檢索出來(lái)，不滿(mǎn)足比較條件的數(shù)據(jù)行則不會(huì)被檢索出來(lái)?；镜摹氨容^篩選”操作主要有以下6種情況。A=B：比較A與B是否相等。A!B或AB：比較A與B是否不相等。AB：比較A是否大于B。A=B：比較A是否大于或等于B。A select empno,ename,sal from emp where sal 1500;本例運(yùn)行結(jié)果如圖5-9所示。圖5-9 查詢(xún)工資大于1500的記錄 另外，除了基本的“比較篩

18、選”操作外，還有兩個(gè)特殊的“比較篩選”操作，具體如下：AoperatorANY(B)：表示A與B中的任何一個(gè)元素進(jìn)行operator運(yùn)算符的比較，只要有一個(gè)比較值為true，就返回?cái)?shù)據(jù)行。A=operatorALL(B)：表示A與B中的所有元素進(jìn)行operator運(yùn)算符的比較，只有與所有元素比較值都為true，才返回?cái)?shù)據(jù)行。 數(shù)據(jù)分組的目的是用來(lái)匯總數(shù)據(jù)或?yàn)檎麄€(gè)分組顯示單行的匯總信息，通常在查詢(xún)結(jié)果集中使用GROUP BY子句對(duì)記錄進(jìn)行分組。在SELECT語(yǔ)句中，GROUP BY子句位于FROM子句之后，其語(yǔ)法格式如下：SELECT columns_listFROM table_nameWHE

19、RE conditional_expressionGROUP BY columns_list參數(shù)說(shuō)明如下：columns_list：字段列表，這GROUP BY子句中也可以指定多個(gè)列分組。table_name：表名。conditional_expression：篩選條件表達(dá)式。 GROUP BY子句可以基于指定某一列的值將數(shù)據(jù)集合劃分為多個(gè)分組，同一組內(nèi)所有記錄在分組屬性上具有相同值，也可以基于指定多列的值將數(shù)據(jù)集合劃分為多個(gè)分組。5.3.3 分組查詢(xún) 【例5-25】 在emp表中，按照部門(mén)編號(hào)（deptno）和職務(wù)（job）列進(jìn)行分組，具體代碼如下。SQL select deptno,job

20、 from emp group by deptno,job order by deptno;本例運(yùn)行結(jié)果如圖5-19所示。圖5-19 分組顯示 GROUP BY子句是經(jīng)常與聚集函數(shù)一起使用。如果SELECT子句中包含聚集函數(shù)，則計(jì)算每組的匯總值，當(dāng)用戶(hù)指定GROUP BY時(shí)，選擇列表中任一非聚集表達(dá)式內(nèi)的所有列都應(yīng)包含在GROUP BY列表中，或者GROUP BY表達(dá)式必須與選擇列表表達(dá)式完全匹配。5.3.4 排序查詢(xún) 在檢索數(shù)據(jù)時(shí)，如果把數(shù)據(jù)從數(shù)據(jù)庫(kù)中直接讀取出來(lái)，這時(shí)查詢(xún)結(jié)果將按照默認(rèn)順序排列，但往往這種默認(rèn)排列順序并不是用戶(hù)所需要看到的。尤其返回?cái)?shù)據(jù)量較大時(shí)，用戶(hù)查看自己想要的信息非常不

21、方便，因此需要對(duì)檢索的結(jié)果集進(jìn)行排序。在SELECT語(yǔ)句中，可以使用ORDER BY子句對(duì)檢索的結(jié)果集進(jìn)行排序，該子句位于FROM子句之后，其語(yǔ)法格式如下：SELECT columns_listFROM table_nameWHERE conditional_expressionGROUP BY columns_listORDER BY order_by_expression ASC | DESC ,.n 參數(shù)說(shuō)明如下：columns_list：字段列表，這GROUP BY子句中也可以指定多個(gè)列分組。table_name：表名。conditional_expression：篩選條件表達(dá)式。or

22、der_by_expression：表示要排序的列名或表達(dá)式。關(guān)鍵字ASC表示按升序排列，這也是默認(rèn)的排序方式；關(guān)鍵字DESC表示按降序排列。 ORDER BY子句可以根據(jù)查詢(xún)結(jié)果中的一個(gè)列或多個(gè)列對(duì)查詢(xún)結(jié)果進(jìn)行排序，并且第一個(gè)排序項(xiàng)是主要的排序依據(jù)，其次那些是次要的排序依據(jù)。 【例5-28】 在SCOTT模式下，檢索emp表中的所有數(shù)據(jù)，并按照部門(mén)編號(hào)（deptno）、員工編號(hào)（empno）排序，具體代碼如下。SQL select deptno,empno,ename from emp order by deptno,empno;本例運(yùn)行結(jié)果如圖5-22所示。圖5-22 檢索emp表中的所有

23、數(shù)據(jù)5.3.5 多表關(guān)聯(lián)查詢(xún) 在實(shí)際的應(yīng)用系統(tǒng)開(kāi)發(fā)中會(huì)設(shè)計(jì)多個(gè)數(shù)據(jù)表，每個(gè)表的信息不是獨(dú)立存在的，而是若干個(gè)表之間的信息存在一定的關(guān)系，這樣當(dāng)用戶(hù)查詢(xún)某一個(gè)表的信息時(shí)，很可能需要查詢(xún)關(guān)聯(lián)數(shù)據(jù)表的信息，這就是多表關(guān)聯(lián)查詢(xún)。SELECT語(yǔ)句自身是支持多表關(guān)聯(lián)查詢(xún)的，多表關(guān)聯(lián)查詢(xún)要比單表查詢(xún)復(fù)雜得多。在進(jìn)行多表關(guān)聯(lián)查詢(xún)時(shí)，可能會(huì)涉及到表別名、內(nèi)連接、外連接、自然連接和交叉連接等概念，下面將對(duì)這些內(nèi)容進(jìn)行講解。1表的別名 在多表關(guān)聯(lián)查詢(xún)時(shí)，如果多個(gè)表之間存在同名的列，則必須使用表名來(lái)限定列的引用。例如，在SCOTT模式中，DEPT表和EMP表都有DEPTNO列，那么當(dāng)用戶(hù)使用該列關(guān)聯(lián)查詢(xún)兩個(gè)表時(shí)，就需

24、要通過(guò)指定表名來(lái)區(qū)分這兩個(gè)列的歸屬。但是，隨著查詢(xún)變得越來(lái)越復(fù)雜，語(yǔ)句就會(huì)因?yàn)槊看蜗薅斜仨気斎氡砻兊萌唛L(zhǎng)乏味。對(duì)于這種情況，SQL語(yǔ)言提供了設(shè)定表別名的機(jī)制，使用簡(jiǎn)短的表別名就可以替代原有較長(zhǎng)的表名稱(chēng)，這樣就可以大大縮減語(yǔ)句的長(zhǎng)度?！纠?-29】 在SCOTT模式下，通過(guò)DEPTNO（部門(mén)號(hào)）列來(lái)關(guān)聯(lián)emp表和dept表，并檢索這兩個(gè)表中相關(guān)字段的信息，代碼及運(yùn)行結(jié)果如下。SQL select e.empno as 員工編號(hào), e.ename as 員工名稱(chēng), d.dname as 部門(mén) 2 from emp e,dept d 4 and e.job=MANAGER; 員工編號(hào) 員工名稱(chēng)

25、 部門(mén)- - - 7782 CLARK ACCOUNTING 7566 JONES RESEARCH 7698 BLAKE SALES在上面的SELECT語(yǔ)句中，F(xiàn)ROM子句最先執(zhí)行，然后才是WHERE子句和SELECT子句，這樣當(dāng)在FROM子句中指定表的別名后，當(dāng)需要限定引用列時(shí)，其它所有子句都可以使用表的別名。 5.4 Oracle常用系統(tǒng)函數(shù)5.4.1 字符類(lèi)函數(shù)5.4.2 數(shù)字類(lèi)函數(shù)5.4.3 日期和時(shí)間類(lèi)函數(shù)5.4.4 轉(zhuǎn)換類(lèi)函數(shù)5.4.5 聚合類(lèi)函數(shù)5.4.1 字符類(lèi)函數(shù) 字符類(lèi)函數(shù)是專(zhuān)門(mén)用于字符處理的函數(shù)，處理的對(duì)象可以是字符或字符串常量，也可以是字符類(lèi)型的列，常用的字符類(lèi)函數(shù)有

26、如下幾種。（1）ASCII(c)函數(shù)和CHR(i)函數(shù) ASCII(c)函數(shù)用于返回一個(gè)字符的ASCII碼，其中參數(shù)c表示一個(gè)字符；CHR(i)函數(shù)用于返回給出ASCII碼值所對(duì)應(yīng)的字符，i表示一個(gè)ASCII碼值。從這兩個(gè)函數(shù)的功能中可以看出，它們二者之間具有互逆的關(guān)系。【例5-37】 分別求得字符“Z、H、D和空格”的ASCII值，具體代碼及運(yùn)行結(jié)果如下：SQL select ascii(Z) Z,ascii(H) H,ascii(D) D ,ascii( ) space 2 from dual; Z H D SPACE- - - - 90 72 68 32說(shuō)明： dual是Oracle系統(tǒng)

27、內(nèi)部提供的一個(gè)用于實(shí)現(xiàn)臨時(shí)數(shù)據(jù)計(jì)算的特殊表，它只有一個(gè)列DUMMY，類(lèi)型為VARCHAR2(1)，后續(xù)相關(guān)內(nèi)容若用到，將不再重復(fù)。 【例5-38】 對(duì)于上個(gè)例子中求得的ASCII值，使用chr函數(shù)再返回其對(duì)應(yīng)的字符，具體代碼及運(yùn)行結(jié)果如下：SQL select chr(90),chr(72),chr(68),(32) S from dual;C C C S- - - -Z H D 32（2）CONCAT（s1,s2）函數(shù) 該函數(shù)將字符串s2連接到字符串s1的后面，如果s1為null，則返回s2；如果s2為null，則返回s1；如果s1和s2都為空，則返回null?！纠?-39】 使用conca

28、t函數(shù)連接“Hello ”和“World”兩個(gè)字符串，具體代碼及運(yùn)行結(jié)果如下：SQL select concat(Hello ,World!) information from dual;INFORMATION-Hello World!（3）INITCAP（s）函數(shù) 該函數(shù)將字符串s的每個(gè)單詞的第一個(gè)字母大寫(xiě)，其它字母小寫(xiě)。單詞之間用空格、控制字符、標(biāo)點(diǎn)符號(hào)來(lái)區(qū)分。函 數(shù)說(shuō) 明ABS(n)返回n的絕對(duì)值CEIL(n)返回大于或等于數(shù)值n的最小整數(shù)COS(n)返回n的余弦值，n為弧度EXP(n)返回e的n次冪，e=2.71828183FLORR(n)返回小于或等于n的最大整數(shù)LOG(n1,n2)

29、返回以n1為底n2的對(duì)數(shù)MOD(n1,n2)返回n1除以n2的余數(shù)POWER(n1,n2)返回n1的n2次方ROUND(n1,n2)返回舍入小數(shù)點(diǎn)右邊n2位的n1的值，n2的默認(rèn)值為0，這會(huì)返回小數(shù)點(diǎn)最接近的整數(shù)。如果n2為負(fù)數(shù)，就舍入到小數(shù)點(diǎn)左邊相應(yīng)的位上，n2必須是整數(shù)SIGN(n)若n為負(fù)數(shù)，則返回-1，若n為正數(shù)，則返回1，若n=0，則返回0SIN(n)返回n的正弦值，n為弧度SQRT(n)返回n的平方根，n為弧度TRUNC(n1,n2)返回結(jié)尾到n2位小數(shù)的n1的值，n2默認(rèn)設(shè)置為0，當(dāng)n2為默認(rèn)設(shè)置時(shí)，會(huì)將n1截尾為整數(shù)，如果n2為負(fù)值，就截尾在小數(shù)點(diǎn)左邊相應(yīng)的位上 數(shù)字類(lèi)函數(shù)主要

30、用于執(zhí)行各種數(shù)據(jù)計(jì)算，所有的數(shù)字類(lèi)函數(shù)都有數(shù)字參數(shù)并返回?cái)?shù)字值。Oracle系統(tǒng)提供了大量的數(shù)字類(lèi)函數(shù)，這些函數(shù)大大增強(qiáng)了Oracle系統(tǒng)的科學(xué)計(jì)算能力。下面就列出Oracle系統(tǒng)中常見(jiàn)的數(shù)字類(lèi)函數(shù)，如表5-1所示。表5-1數(shù)字類(lèi)函數(shù)及其說(shuō)明5.4.2 數(shù)字類(lèi)函數(shù)（1）CEIL（n）函數(shù)該函數(shù)返回大于或等于數(shù)值n的最小整數(shù)，它適合一些比較運(yùn)算?！纠?-47】 使用ceil函數(shù)返回3個(gè)指定小數(shù)的整數(shù)值，具體代碼及運(yùn)行結(jié)果如下：SQL select ceil(7.3),ceil(7),ceil(-7.3) from dual; CEIL(7.3) CEIL(7) CEIL(-7.3)- - - 8

31、 7 -7（2）ROUND(n1,n2)函數(shù)該函數(shù)返回舍入小數(shù)點(diǎn)右邊n2位的n1的值，n2的默認(rèn)值為0，這會(huì)返回小數(shù)點(diǎn)最接近的整數(shù)。如果n2為負(fù)數(shù)，就舍入到小數(shù)點(diǎn)左邊相應(yīng)的位上，n2必須是整數(shù)。【例5-48】 使用round函數(shù)返回PI為兩位小數(shù)的值，具體代碼及運(yùn)行結(jié)果如下：SQL select round(3.1415926,2) from dual;ROUND(3.1415926,2)-（3）POWER(n1,n2)函數(shù)該函數(shù)返回n1的n2次方。其中n1和n2都為整數(shù)。【例5-49】 使用power函數(shù)計(jì)算2的3次方的值，具體代碼及運(yùn)行結(jié)果如下：SQL select power(2,3)

32、from dual;POWER(2,3)- 在Oracle 11g中，系統(tǒng)提供了許多用于處理日期和時(shí)間的函數(shù)，通過(guò)這些函數(shù)可以實(shí)現(xiàn)計(jì)算需要的特定日期和時(shí)間，常用的日期和時(shí)間函數(shù)如表5-2所示。表5-2日期和時(shí)間類(lèi)函數(shù) 日期類(lèi)型的默認(rèn)格式是“DD-MON-YY”，其中“DD”表示兩位數(shù)字的“日”，MON表示3位數(shù)字的“月份”。YY表示兩位數(shù)字的“年份”，例如，“01-10月-11”表示2011年10月1日。下面看幾個(gè)常用函數(shù)的具體應(yīng)用。5.4.3 日期和時(shí)間類(lèi)函數(shù)函 數(shù)說(shuō) 明ADD_MONTHS(d,i)返回日期d加上i個(gè)月之后的結(jié)果。其中，i為任意整數(shù)LAST_DAY(d)返回包含日期d月份的

33、最后一天MONTHS_BETWEEN(d1,d2)返回d1和d2之間的數(shù)目，若d1和d2的日期都相同，或者都是該月的最后一天，則返回一個(gè)整數(shù)，否則返回的結(jié)果將包含一個(gè)小數(shù)NEW_TIME(d1,t1,t2)其中，d1是一個(gè)日期數(shù)據(jù)類(lèi)型，當(dāng)時(shí)區(qū)t1中的日期和時(shí)間是d1時(shí)，返回時(shí)區(qū)t2中的日期和時(shí)間。t1和t2是字符串SYSDATE()返回系統(tǒng)當(dāng)前的日期（1）SYSDATE()函數(shù)該函數(shù)返回系統(tǒng)當(dāng)前的日期?！纠?-50】 使用sysdate函數(shù)返回當(dāng)期系統(tǒng)的日期，具體代碼及運(yùn)行結(jié)果如下：SQL select sysdate as 系統(tǒng)日期 from dual;系統(tǒng)日期-29-9月 -11（2）AD

34、D_MONTHS(d,i)函數(shù)該函數(shù)返回日期d加上i個(gè)月之后的結(jié)果。其中，i為任意整數(shù)?！纠?-51】 使用ADD_MONTHS函數(shù)在當(dāng)前日期下加上6個(gè)月，并顯示其值，具體代碼及運(yùn)行結(jié)果如下：SQL select ADD_MONTHS(sysdate,6) from dual;ADD_MONTHS(SYS-29-3月 -12 在操作表中的數(shù)據(jù)時(shí)，經(jīng)常需要將某個(gè)數(shù)據(jù)從一種類(lèi)型轉(zhuǎn)換為另外一種數(shù)據(jù)類(lèi)型，這時(shí)就需要轉(zhuǎn)換類(lèi)型函數(shù)。比如常見(jiàn)的，有把具有“特定格式”字符串轉(zhuǎn)換為日期、把數(shù)字轉(zhuǎn)換成字符等。常用的轉(zhuǎn)換函數(shù)如表5-3所示。表5-3轉(zhuǎn)換類(lèi)函數(shù)5.4.4 轉(zhuǎn)換類(lèi)函數(shù)函 數(shù)說(shuō) 明CHARTORWIDA(

35、s)該函數(shù)將字符串s轉(zhuǎn)換為RWID數(shù)據(jù)類(lèi)型CONVERT(s,aset,bset)該函數(shù)將字符串s由bset字符集轉(zhuǎn)換為aset字符集ROWIDTOCHAR()該函數(shù)將ROWID數(shù)據(jù)類(lèi)型轉(zhuǎn)換為CHAR類(lèi)型TO_CHAR(x,format)該函數(shù)實(shí)現(xiàn)將表達(dá)式轉(zhuǎn)換為字符串，format表示字符串格式TO_DATE(s,formatlan)該函數(shù)將字符串s轉(zhuǎn)換成date類(lèi)型，format表示字符串格式，lan表示所使用的語(yǔ)言TO_NUMBER(s,formatlan)該函數(shù)將返回字符串s代表的數(shù)字，返回值按照f(shuō)ormat格式進(jìn)行顯示，format表示字符串格式，lan表示所使用的語(yǔ)言下面來(lái)看幾個(gè)常用

36、轉(zhuǎn)換函數(shù)的具體應(yīng)用。（1）TO_CHAR()函數(shù)該函數(shù)實(shí)現(xiàn)將表達(dá)式轉(zhuǎn)換為字符串，format表示字符串格式【例5-52】 使用to_char函數(shù)轉(zhuǎn)換系統(tǒng)日期為“YYYY-MM-DD”格式，具體代碼及運(yùn)行結(jié)果如下。SQL select sysdate as 默認(rèn)格式日期, to_char(sysdate,YYYY-MM-DD) as 轉(zhuǎn)換后日期 from dual;默認(rèn)格式日期 轉(zhuǎn)換后日期-29-9月 -11 2011-09-29（2）TO_NUMBER(s,formatlan)函數(shù)該函數(shù)將返回字符串s代表的數(shù)字，返回值按照f(shuō)ormat格式進(jìn)行顯示，format表示字符串格式，lan表示所使用的

37、語(yǔ)言【例5-53】 使用to_number函數(shù)把16進(jìn)制數(shù)“18f”轉(zhuǎn)轉(zhuǎn)為10進(jìn)制數(shù)，具體代碼及運(yùn)行結(jié)果如下：SQL select to_number(18f, xxx) as 十進(jìn)制數(shù) from dual; 十進(jìn)制數(shù)- 339 使用聚合類(lèi)函數(shù)可以針對(duì)一組數(shù)據(jù)進(jìn)行計(jì)算，并得到相應(yīng)的結(jié)果。比如常有的操作有計(jì)算平均值、統(tǒng)計(jì)記錄數(shù)、計(jì)算最大值等。Oracle 11g所提供的主要聚合函數(shù)如表5-4所示。表5-4聚合函數(shù) 在實(shí)際的應(yīng)用系統(tǒng)開(kāi)發(fā)中，聚合函數(shù)應(yīng)用比較廣泛，比如統(tǒng)計(jì)平均值、記錄總數(shù)等。下面來(lái)看一個(gè)例子。5.4.5 聚合類(lèi)函數(shù)函 數(shù)說(shuō) 明AVG(xDISTINCT|ALL)計(jì)算選擇列表項(xiàng)的平均值

38、，列表項(xiàng)目可以是一個(gè)列或多個(gè)列的表達(dá)式COUNT(xDISTINCT|ALL)返回查詢(xún)結(jié)果中的記錄數(shù)MAX(xDISTINCT|ALL)返回選擇列表項(xiàng)目中的最大數(shù)，列表項(xiàng)目可以是一個(gè)列或多個(gè)列的表達(dá)式MIN(xDISTINCT|ALL)返回選擇列表項(xiàng)目中的最小數(shù)，列表項(xiàng)目可以是一個(gè)列或多個(gè)列的表達(dá)式SUM(xDISTINCT|ALL)返回選擇列表項(xiàng)目的數(shù)值總和，列表項(xiàng)目可以是一個(gè)列或多個(gè)列的表達(dá)式VARIANCE(xDISTINCT|ALL)返回選擇列表項(xiàng)目的統(tǒng)計(jì)方差，列表項(xiàng)目可以是一個(gè)列或多個(gè)列的表達(dá)式STDDEV(xDISTINCT|ALL)返回選擇列表項(xiàng)目的標(biāo)準(zhǔn)偏差，列表項(xiàng)目可以是一個(gè)列

39、或多個(gè)列的表達(dá)式 【例5-54】 在SCOTT模式下，使用count函數(shù)計(jì)算員工總數(shù)，使用avg函數(shù)計(jì)算平均工資，具體代碼及運(yùn)行結(jié)果如下：SQL select count(empno) as 員工總數(shù),round(avg(sal),2) as 平均工資 from emp; 員工總數(shù) 平均工資-5.5 子查詢(xún)的用法5.5.1 什么是子查詢(xún)5.5.2 單行子查詢(xún)5.5.3 多行子查詢(xún)5.5.4 關(guān)聯(lián)子查詢(xún) 子查詢(xún)是在SQL語(yǔ)句內(nèi)的另外一條SELECT語(yǔ)句，也被稱(chēng)為內(nèi)查詢(xún)或是內(nèi)SELECT語(yǔ)句。在SELECT、INSERT、UPDATE或DELETE命令中允許是一個(gè)表達(dá)式的地方都可以包含子查詢(xún)，子查

40、詢(xún)甚至可以包含在另外一個(gè)子查詢(xún)中。 【例5-55】 在SCOTT模式下，在emp表中查詢(xún)部門(mén)名稱(chēng)（dname）為“RESEARCH”的員工信息，具體代碼如下。SQL select empno,ename,job from emp 2 where deptno=(select deptno from dept 3 where dname=RESEARCH);本例運(yùn)行結(jié)果如圖5-32所示。圖5-32 子查詢(xún)5.5.1 什么是子查詢(xún) 在一般情況下，外查詢(xún)語(yǔ)句語(yǔ)句檢索一行，子查詢(xún)語(yǔ)句需要檢索一遍數(shù)據(jù)，然后判斷外查詢(xún)語(yǔ)句的條件是否滿(mǎn)足。如果條件滿(mǎn)足，則外查詢(xún)語(yǔ)句將檢索到的數(shù)據(jù)行添加到結(jié)果集中，如果條件不

41、滿(mǎn)足，則外查詢(xún)語(yǔ)句繼續(xù)檢索下一行數(shù)據(jù)，所以子查詢(xún)相對(duì)多表關(guān)聯(lián)查詢(xún)要慢一些。另外，在使用子查詢(xún)時(shí)，還應(yīng)注意以下規(guī)則：子查詢(xún)必須用括號(hào)“()”括起來(lái)。子查詢(xún)中不能包括ORDER BY子句。子查詢(xún)?cè)试S嵌套多層，但不能超過(guò)255層。 在Oracle 11g中，通常把子查詢(xún)?cè)偌?xì)化為單行子查詢(xún)、多行子查詢(xún)和關(guān)聯(lián)子查詢(xún)3種，下面對(duì)這些子查詢(xún)進(jìn)行詳細(xì)講解。 單行子查詢(xún)是指返回一行數(shù)據(jù)的子查詢(xún)語(yǔ)句。當(dāng)在WHERE子句中引用單行子查詢(xún)時(shí)，可以使用單行比較運(yùn)算符（=、=、=和）。【例5-56】 在emp表中，查詢(xún)出既不是最高工資，也不是最低工資的員工信息，具體代碼如下：SQL select empno,ename,

42、sal from emp 2 where sal (select min(sal) from emp) 3 and sal select empno,ename,job 2 from emp where deptno in 3 (select deptno from dept where dnameSALES);本例運(yùn)行結(jié)果如圖5-34所示。圖5-34 多行子查詢(xún)2使用ANY運(yùn)算符ANY運(yùn)算符必須與單行操作符結(jié)合使用，并且返回行只要匹配子查詢(xún)的任何一個(gè)結(jié)果即可。5.5.3 多行子查詢(xún) 【例5-58】 在emp表中，查詢(xún)工資大于部門(mén)編號(hào)為10的任意一個(gè)員工工資即可的其它部門(mén)的員工信息，具體代碼如

43、下：SQL select deptno,ename,sal from emp where sal any 2 (select sal from emp where deptno = 10) and deptno 10;本例運(yùn)行結(jié)果如圖5-35所示。圖5-35 ANY運(yùn)算符3使用ALL運(yùn)算符ALL運(yùn)算符必須與單行運(yùn)算符結(jié)合使用，并且返回行必須匹配所有子查詢(xún)結(jié)果。【例5-59】 在emp表中，查詢(xún)工資大于部門(mén)編號(hào)為10的所有員工工資的員工信息，具體代碼如下：SQL select deptno,ename,sal from emp where sal all 2 (select sal from e

44、mp where deptno = 30);本例運(yùn)行結(jié)果如圖5-36所示。圖5-36 ALL運(yùn)算符 在當(dāng)行子查詢(xún)和多行子查詢(xún)中，內(nèi)查詢(xún)和外查詢(xún)是分開(kāi)執(zhí)行的，也就是說(shuō)內(nèi)查詢(xún)的執(zhí)行與外查詢(xún)的執(zhí)行是沒(méi)有關(guān)系的，外查詢(xún)僅僅是使用內(nèi)查詢(xún)的最終結(jié)果。在一些特殊需求的子查詢(xún)中，內(nèi)查詢(xún)的執(zhí)行需要借助于外查詢(xún)，而外查詢(xún)的執(zhí)行又離不開(kāi)內(nèi)查詢(xún)的執(zhí)行，這時(shí)，內(nèi)查詢(xún)和外查詢(xún)是相互關(guān)聯(lián)的，這種子查詢(xún)就被稱(chēng)為關(guān)聯(lián)子查詢(xún)?！纠?-60】 在emp表中，使用“關(guān)聯(lián)子查詢(xún)”檢索工資大于同職位的平均工資的員工信息，具體代碼如下。SQL select empno,ename,sal 2 from emp f 3 where sal

45、(select avg(sal) from emp where job = f.job) 4 order by job;本例運(yùn)行結(jié)果如圖5-37所示。圖5-37 關(guān)聯(lián)子查詢(xún)5.5.4 關(guān)聯(lián)子查詢(xún)5.6 操作數(shù)據(jù)庫(kù)5.6.1 插入數(shù)據(jù)（INSERT 語(yǔ)句）5.6.2 更新數(shù)據(jù)（UPDATE語(yǔ)句）5.6.3 刪除數(shù)據(jù)（DELETE語(yǔ)句和TRUNCATE語(yǔ)句） 插入數(shù)據(jù)就是將數(shù)據(jù)記錄添加到已經(jīng)存在的數(shù)據(jù)表中，Oracle數(shù)據(jù)庫(kù)通過(guò)INSERT語(yǔ)句來(lái)實(shí)現(xiàn)插入數(shù)據(jù)記錄。該語(yǔ)句既可以實(shí)現(xiàn)向數(shù)據(jù)表中一次插入一條記錄，也可以使用SELECT子句將查詢(xún)結(jié)果集批量插入數(shù)據(jù)表。單條插入數(shù)據(jù)是INSERT語(yǔ)句最基本的

46、用法，其用法格式如下：INSERT INTO table_name (column_name1,column_name2)VALUES(express1,express2)參數(shù)說(shuō)明如下：table_name：表示要插入的表名。column_name1和column_name2：指定表的完全或部分列名稱(chēng)。express1和express2：表示要插入的值列表 在INSERT語(yǔ)句的幾種使用方式中，最常用的形式是在INSERT INTO子句中指定添加數(shù)據(jù)的列，并在VALUES子句中為各個(gè)列提供一個(gè)值。5.6.1 插入數(shù)據(jù)（INSERT 語(yǔ)句）【例5-61】 在dept表中，使用INSERT語(yǔ)句添加一

47、條記錄，具體代碼及運(yùn)行結(jié)果如下：SQL insert into dept(deptno,dname,loc) 2 values(88,design,beijing);已創(chuàng)建 1 行。 在上面的示例中，INSERT INTO子句中指定添加數(shù)據(jù)的列，既可以是數(shù)據(jù)表的全部列，也可以是部分列。在指定部分列時(shí)，需要注意不許為空（NOT NULL）的列必須被指定出來(lái)，并且在values子句中的對(duì)應(yīng)賦值也不許為NULl，否則系統(tǒng)顯示“無(wú)法將 NULL 插入”的錯(cuò)誤信息提示。例如，修改上面的例子，在INSERT INTO子句不指定deptno列（通過(guò)desc dept命令可以看到該列是NOT NULL的），將

48、出現(xiàn)如圖5-38所示的錯(cuò)誤提示。圖5-38 不許為空的錯(cuò)誤提示說(shuō)明： 在使用INSERT INTO子句指定為表的部分列添加數(shù)據(jù)時(shí)，為了防止產(chǎn)生不許為空值的錯(cuò)誤，可以使用DESC命令查看數(shù)據(jù)表中的哪些列不許為空。對(duì)于可以為空的列，用戶(hù)可以不指定其值。 在向表的所有列添加數(shù)據(jù)時(shí)，也可以省略INSERT INTO 子句后面的列表清單，使用這種方法時(shí)，必須根據(jù)表中定義的列的順序，為所有的列提供數(shù)據(jù)。用戶(hù)可以使用DESC命令來(lái)查看表中定義列的順序。 如果表中的數(shù)據(jù)不正確或不符合需求，那么就需要對(duì)其進(jìn)行修改。Oracle數(shù)據(jù)庫(kù)通過(guò)UPDATE語(yǔ)句來(lái)實(shí)現(xiàn)修改現(xiàn)有的數(shù)據(jù)記錄。 在更新數(shù)據(jù)時(shí)，更新的列數(shù)可以由用

49、戶(hù)自己指定，列于列之間用逗號(hào)（“,”）分隔；更新的條數(shù)可以通過(guò)WHERE子句來(lái)加以限制，使用WHERE子句時(shí)，系統(tǒng)只更新符合WHERE條件的記錄信息。UPDATE語(yǔ)句的語(yǔ)法格式如下：UPDATE table_nameSET column_name1=express1,column_name2=express2.| (column_name1,column_name2)=(selectSubquery)WHERE condition參數(shù)說(shuō)明如下：table_name：表示要修改的表名。column_name1和column_name2：表示指定要更新的列名。selectSubquery：任何合法

50、的SELECT語(yǔ)句，其所選列的個(gè)數(shù)和類(lèi)型要與語(yǔ)句中的column對(duì)應(yīng)。condition：篩選條件表達(dá)式，只有符合篩選條件的記錄才被更新。5.6.2 更新數(shù)據(jù)（UPDATE語(yǔ)句）【例5-62】 在SCOTT模式下，把emp表中職務(wù)是銷(xiāo)售員（SALESMAN）的工資上調(diào)20%，具體代碼及運(yùn)行結(jié)果如下：SQL update emp 3 where job=SALESMAN;已更新4行。 上面的代碼中，UPDATE語(yǔ)句更新記錄的數(shù)量通過(guò)WHERE子句實(shí)現(xiàn)控制的，這里限制只更新銷(xiāo)售員的工資，若取消WHERE子句的限制，則系統(tǒng)會(huì)將emp表中所有人員的工資都上調(diào)20%。另外，同INSERT語(yǔ)句一樣，UPD

51、ATE語(yǔ)句也可以與SELECT語(yǔ)句組合使用來(lái)達(dá)到更新數(shù)據(jù)的目的?！纠?-63】 在SCOTT模式下，把emp表中工資小于2000的雇員工資調(diào)整為管理者的平均工資水平，具體代碼及運(yùn)行結(jié)果如下：SQL update emp 2 set sal = (select avg(sal) 3 from emp where job = MANAGER) 4 where sal delete from scott.emp where empno = 7369;已刪除 1 行。上面的代碼中，DELETE語(yǔ)句刪除記錄的數(shù)量通過(guò)WHERE子句實(shí)現(xiàn)控制的，這里限制只刪除職務(wù)編號(hào)（job_id）是“PRO”記錄，若取消

52、WHERE子句的限制，則系統(tǒng)會(huì)將jobs表中所有人員的記錄都刪除。 2TRUNCATE語(yǔ)句 如果用戶(hù)確定要?jiǎng)h除表中的所有記錄，則除了可以使用DELETE語(yǔ)句之外，還可以使用TRUNCATE語(yǔ)句，而且Oracle本身也建議使用TRUNCATE語(yǔ)句。 使用TRUNCATE語(yǔ)句刪除表中的所有記錄要比DELETE語(yǔ)句快得多。這是因?yàn)槭褂肨RUNCATE語(yǔ)句刪除數(shù)據(jù)時(shí)，它不會(huì)產(chǎn)生回滾記錄。當(dāng)然，執(zhí)行了TRUNCATE語(yǔ)句的操作也就無(wú)法使用ROLLBACK語(yǔ)句撤銷(xiāo)。5.7 事務(wù)處理5.7.1 事務(wù)概述5.7.2 操作事務(wù)5.7.1 事務(wù)概述 當(dāng)使用事務(wù)修改多個(gè)數(shù)據(jù)表時(shí)，如果在處理的過(guò)程中出現(xiàn)了某種錯(cuò)誤，例

53、如系統(tǒng)死機(jī)或突然斷電等情況，則返回結(jié)果是數(shù)據(jù)全部沒(méi)有被保存。因?yàn)槭聞?wù)處理的結(jié)果只有兩種：一種是在事務(wù)處理的過(guò)程中，如果發(fā)生了某種錯(cuò)誤則整個(gè)事務(wù)全部回滾，使所有對(duì)數(shù)據(jù)的修改全部撤銷(xiāo)，事務(wù)對(duì)數(shù)據(jù)庫(kù)的操作是單步執(zhí)行的，當(dāng)遇到錯(cuò)誤時(shí)可以隨時(shí)地回滾；另一種是如果沒(méi)有發(fā)生任何錯(cuò)誤且每一步的執(zhí)行都成功，則整個(gè)事務(wù)全部被提交。從而可以看出，有效地使用事務(wù)不但可以提高數(shù)據(jù)的安全性，而且還可以增強(qiáng)數(shù)據(jù)的處理效率。 事務(wù)包含4種重要的屬性，被統(tǒng)稱(chēng)為ACID（原子性、一致性、隔離性和持久性），一個(gè)事務(wù)必須通過(guò)ACID。（1）原子性（Atomic）：事務(wù)是一個(gè)整體的工作單元，事務(wù)對(duì)數(shù)據(jù)庫(kù)所做的操作要么全部執(zhí)行，要么全部

54、取消。如果某條語(yǔ)句執(zhí)行失敗，則所有語(yǔ)句全部回滾。（2）一致性（ConDemoltent）：事務(wù)在完成時(shí)，必須使所有的數(shù)據(jù)都保持一致?tīng)顟B(tài)。在相關(guān)數(shù)據(jù)庫(kù)中，所有規(guī)則都必須應(yīng)用于事務(wù)的修改，以保持所有數(shù)據(jù)的完整性。如果事務(wù)成功，則所有數(shù)據(jù)將變?yōu)橐粋€(gè)新的狀態(tài)；如果事務(wù)失敗，則所有數(shù)據(jù)將處于開(kāi)始之前的狀態(tài)。（3）隔離性（Isolated）：由事務(wù)所作的修改必須與其他事務(wù)所作的修改隔離。事務(wù)查看數(shù)據(jù)時(shí)數(shù)據(jù)所處的狀態(tài)，要么是另一并發(fā)事務(wù)修改它之前的狀態(tài)，要么是另一事務(wù)修改它之后的狀態(tài)，事務(wù)不會(huì)查看中間狀態(tài)的數(shù)據(jù)。（4）持久性（Durability）：當(dāng)事務(wù)提交后，對(duì)數(shù)據(jù)庫(kù)所做的修改就會(huì)永久保存下來(lái)。5.7.

55、2 操作事務(wù) Oracle 11g中的事務(wù)是隱式自動(dòng)開(kāi)始的，它不需要用戶(hù)顯示地執(zhí)行開(kāi)始事務(wù)語(yǔ)句。但對(duì)于事務(wù)的結(jié)束處理，則需要用戶(hù)進(jìn)行指定的操作，通常在以下情況時(shí)，Oracle認(rèn)為一個(gè)事務(wù)結(jié)束了。（1）執(zhí)行COMMIT語(yǔ)句提交事務(wù)。（2）指定ROLLBACK語(yǔ)句撤銷(xiāo)事務(wù)。（3）執(zhí)行一條數(shù)據(jù)定義語(yǔ)句，比如，CREATE、DROP或ALTER等語(yǔ)句。如果該語(yǔ)句執(zhí)行成功，那么Oracle系統(tǒng)會(huì)自動(dòng)執(zhí)行COMMIT命令；否則，則Oracle系統(tǒng)會(huì)自動(dòng)執(zhí)行ROLLBACK命令。（4）執(zhí)行一個(gè)數(shù)據(jù)控制命令，比如，GRANT、REVOKE等控制命令，這種操作執(zhí)行完畢，Oracle系統(tǒng)會(huì)自動(dòng)執(zhí)行COMMIT命令

56、。（5）正常地?cái)嚅_(kāi)數(shù)據(jù)庫(kù)的連接、正常地退出SQL*Plus環(huán)境，則Oracle系統(tǒng)會(huì)自動(dòng)執(zhí)行COMMIT命令；否則，則Oracle系統(tǒng)會(huì)自動(dòng)執(zhí)行ROLLBACK命令。 綜合上面5種情況可知，Oracle結(jié)束一個(gè)是事務(wù)歸根結(jié)底要么執(zhí)行COMMIT語(yǔ)句，要么執(zhí)行ROLLBACK語(yǔ)句，下面對(duì)這兩種語(yǔ)句進(jìn)行介紹。1提交事務(wù)（COMMIT語(yǔ)句） 提交事務(wù)是指把對(duì)數(shù)據(jù)庫(kù)進(jìn)行的全部操作持久性地保存到數(shù)據(jù)庫(kù)中，這種操作通常使用COMMIT語(yǔ)句來(lái)完成。在使用該語(yǔ)句提交事務(wù)時(shí)，Oracle系統(tǒng)內(nèi)部會(huì)按照如下順序進(jìn)行處理。（1）首先在回滾段內(nèi)記錄當(dāng)前事務(wù)已提交，并且聲稱(chēng)一個(gè)唯一的系統(tǒng)該編號(hào)（SCN），以唯一標(biāo)識(shí)這個(gè)

57、事務(wù)。（2）然后啟動(dòng)后臺(tái)的日志寫(xiě)入進(jìn)程（LGWR），將SGA區(qū)的重做日志緩沖區(qū)中的數(shù)據(jù)和當(dāng)前事物的SCN寫(xiě)入重做日志文件中。（3）接著Oracle服務(wù)器開(kāi)始釋放事務(wù)處理所使用的系統(tǒng)資源。（4）最后顯示通知，告訴用戶(hù)事務(wù)已經(jīng)成功提交完畢?！纠?-65】 實(shí)現(xiàn)向scott模式下的emp表中添加數(shù)據(jù)，并提交事務(wù)，代碼即運(yùn)行結(jié)下：SQL insert into scott.emp(empno,ename,job) values(7901,LUCY,CLERK);已創(chuàng)建 1 行。SQL commit;提交完成。在上面的示例中，如果用戶(hù)不使用commit提交事務(wù)，此時(shí)，再開(kāi)啟一個(gè)SQL*Plus環(huán)境（但要

58、求當(dāng)前的SQL*Plus環(huán)境不退出，若退出，Oracle系統(tǒng)會(huì)自動(dòng)執(zhí)行commit語(yǔ)句提交數(shù)據(jù)庫(kù)），然后在HR模式下查詢(xún)jobs_temp表，會(huì)發(fā)現(xiàn)新增加的記錄不存在。若用戶(hù)使用commit語(yǔ)句提交事務(wù)，則在另一個(gè)SQL*Plus環(huán)境下就能夠查詢(xún)到新增加的記錄。2回滾事務(wù)（ROLLBACK語(yǔ)句）回退事務(wù)是指撤銷(xiāo)對(duì)數(shù)據(jù)庫(kù)進(jìn)行的全部操作，Oracle利用回退段來(lái)存儲(chǔ)修改前的數(shù)據(jù)，通過(guò)重做日志來(lái)記錄對(duì)數(shù)據(jù)所做的修改。如果要回退整個(gè)事務(wù)，Oracle系統(tǒng)內(nèi)部將會(huì)執(zhí)行如下操作過(guò)程。（1）首先使用回退段中的數(shù)據(jù)撤銷(xiāo)對(duì)數(shù)據(jù)庫(kù)所做的修改。（2）然后Oracle后臺(tái)服務(wù)進(jìn)程釋放掉事務(wù)所使用的系統(tǒng)資源。（3）最后

59、顯示通知，告訴用戶(hù)事務(wù)回退成功。Oracle不僅允許回退整個(gè)未提交的事務(wù)，還允許回退事務(wù)的一部分，這是可以通過(guò)“保存點(diǎn)”來(lái)完成。在事務(wù)的執(zhí)行過(guò)程中，用戶(hù)可以通過(guò)建立保存點(diǎn)將一個(gè)較長(zhǎng)的失誤分隔為幾部分。這樣用戶(hù)就可以有選擇性地回退到某個(gè)保存點(diǎn)，并且該保存點(diǎn)之后的操作都將被取消 【例5-66】 新建stu表，并向表中先后添加兩條數(shù)據(jù)，使用保存點(diǎn)（savepoint）回滾最后添加的記錄，代碼及運(yùn)行結(jié)果如下。SQL create table stu(sid number primary key,sname varchar(10),sage number);表已創(chuàng)建。SQL insert into st

60、u values(1,lili,25);已創(chuàng)建 1 行。SQL savepoint sp;保存點(diǎn)已創(chuàng)建。SQL insert into stu values(2,lucy,26);已創(chuàng)建 1 行。SQL select * from stu; SID SNAME SAGE- - - 1 lili 25 2 lucy 26SQL rollback to savepoint sp;回退已完成。SQL commit;提交完成。SQL select * from stu; SID SNAME SAGE- - - 1 lili 255.8 常規(guī)SQL語(yǔ)句優(yōu)化5.8.1 建議不用“*”來(lái)代替所有列名5.8.