Oracle第章SQL語言基礎

第13章SQL語言基礎本章內(nèi)容SQL語句分類數(shù)據(jù)查詢（SELECT）數(shù)據(jù)操縱（INSERT、UPDATE、DELETE）事務控制SQL函數(shù)本章要求掌握數(shù)據(jù)查詢的各種應用掌握數(shù)據(jù)操縱的各種應用掌握事務處理了解SQL函數(shù)應用13.1SQL語言概述SQL語言介紹SQL語言的分類SQL語言的特點13.1.1SQL語言介紹SQL（Structured

Query

Language）語言是1974年由Boyce

和Chamberlin提出的。SQL語言是關(guān)系數(shù)據(jù)庫操作的基礎語言，將數(shù)據(jù)查詢、數(shù)據(jù)操縱、數(shù)據(jù)定義、事務控制、系統(tǒng)控制等功能集于一體，從而使得數(shù)據(jù)庫應用開發(fā)人員、數(shù)據(jù)庫管理員等都可以通過SQL語言實現(xiàn)對數(shù)據(jù)庫的訪問和操作。13.1.2SQL語言的分類據(jù)定義語言（DataDefinitionLanguage，DDL）：用于定義、修改、刪除數(shù)據(jù)庫對象，包括CREATE，ALTER，DROP，GRANT，REVOKE，AUDIT和NOAUDIT等。數(shù)據(jù)操縱語言（DataManipulationLanguage，DML）：用于改變數(shù)據(jù)庫中的數(shù)據(jù)，包括數(shù)據(jù)插入（INSERT）、數(shù)據(jù)修改（UPDATE）和數(shù)據(jù)刪除（DELETE）。數(shù)據(jù)查詢語言（DataQueryLanguage，DQL）：用于數(shù)據(jù)檢索，包括SELECT。事務控制（TransactionControl）：用于將一組DML操作組合起來，形成一個事務并進行事務控制。包括事務提交（COMMIT）、事務回滾（ROLLBACK）、設置保存點（SAVEPOINT）和設置事務狀態(tài)（SETTRANSACTION）。系統(tǒng)控制（SystemControl）：用于設置數(shù)據(jù)庫系統(tǒng)參數(shù)，包括ALTERSYSTEM。會話控制（SessionControl）：用于設置用戶會話相關(guān)參數(shù)，包括ALTERSESSION。13.1.3SQL語言的特點功能一體化：幾乎涵蓋了對數(shù)據(jù)庫的所有操作，語言風格統(tǒng)一。高度的非過程化：在使用SQL語言操作數(shù)據(jù)庫時，用戶只需要說明“做什么”，而不需要說明“怎樣做”。用戶任務的實現(xiàn)對用戶而言是透明的，由系統(tǒng)自動完成。這大大減輕了用戶的負擔，同時降低了對用戶的技術(shù)要求。面向集合的操作方式：SQL語言采用集合操作方式，不僅查詢結(jié)果可以是多條記錄的集合，而且一次插入、刪除、修改操作的對象也可以是多條記錄的集合。面向集合的操作方式極大地提高了對數(shù)據(jù)操作效率。多種使用方式：SQL語句既是自含式語言，又是嵌入式語言。SQL語言可以直接以命令方式與數(shù)據(jù)庫進行交互，也可以嵌入到其他的高級語言中使用。簡潔、易學：SQL語言命令數(shù)量有限，語法簡單，接近于自然語言（英語），因此容易學習和掌握。

13.2數(shù)據(jù)查詢數(shù)據(jù)查詢基礎基本查詢分組查詢連接查詢子查詢合并查詢

13.2.1數(shù)據(jù)查詢基礎基本語法：SELECT[ALL|DISTINCT]column_name[,expression…]FROMtable1_name[,table2_name,view_name,…][WHEREcondition][GROUPBYcolumn_name1[,column_name2,…][HAVINGgroup_condition]][ORDERBYcolumn_name2[ASC|DESC][,column_name2,…]];

13.2.2基本查詢無條件查詢有條件查詢查詢排序查詢統(tǒng)計（1）無條件查詢詢查詢所有列SELECT*FROMemp;查詢指定列SELECTdeptno,dnameFROMdept;使用算術(shù)表達達式SELECTempno,sal*0.8FROMemp;使用字符常量量SELECTempno,'Nameis:',enameFROMemp;使用函數(shù)SELECTempno,UPPER(ename)FROMemp;改變列標題SELECTenameemployeename,salsalaryFROMemp;使用連接字符符串SELECT'員工號：'||empno||'員工名'||enameFROMemp;消除重復行SELECTALLdeptnoFROMemp;SELECTDISTINCTdeptnoFROMemp;（2）有條件查詢詢查詢滿足條件件的元組可以以通過WHERE子句實現(xiàn)。WHERE條件中常用的的運算符號運算符號謂詞比較大小=,>,<,>=,<=,<>，!=確定范圍BETWEENAND，NOTBETWEENAND確定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重條件AND，OR關(guān)系運算SELECTempno,ename,salFROMempWHEREdeptno!=10;SELECTempno,ename,salFROMempWHEREsal>1500確定范圍謂詞BETWEENAND與NOTBETWEENAND。SELECT*FROMempWHEREdeptnoBETWEEN10AND20;SELECT*FROMempWHEREsalNOTBETWEEN1000AND2000;確定集合謂詞IN可以用來查找找屬性值屬于于指定集合的的元組。SELECTempno,ename,salFROMempWHEREdeptnoIN(10,30);字符匹配%（百分號）代代表任意長（（長度為0）字符串。_（下劃線）代代表任意單個個字符。ESCAPE：轉(zhuǎn)義字符SELECT*FROMempWHEREenameLIKE‘%S%’；SELECT*FROMempWHEREenameLIKE'_A%';SELECT*FROMempWHEREenameLIKE'%x_%'ESCAPE'x';空值操作涉及空值查詢詢時使用ISNULL或ISNOTNULL，這里的IS不能用=替代。SELECT*FROMempWHEREdeptnoISNULL;SELECT*FROMempWHEREcommISNOTNULL;邏輯操作用邏輯運算符符NOT、AND和OR來聯(lián)結(jié)多個查查詢條件。優(yōu)先級：NOT、AND、OR（用戶可以用用括號改變優(yōu)優(yōu)先級）。IN謂詞實際上是是多個OR運算的縮寫。。SELECT*FROMempWHEREdeptno=10ANDsal>1500;SELECT*FROMempWHERE(deptno=10ORdeptno=20)ANDsal>1500;注意：使用BETWEEN…AND，NOTBETWEEN…AND，IN，NOTIN運算符的查詢詢條件都可以以轉(zhuǎn)換為NOT，AND，OR的邏輯運算算。例如，，下面兩個個語句是等等價的：SELECT*FROMempWHEREsal>1000ANDsal<2000;SELECT*FROMempWHEREsalBETWEEN1000AND2000;升序、降序序排序ASC:升序（缺缺?。籇ESC:降序SELECTempno,ename,salFROMempORDERBYsal;SELECTempno,ename,salFROMempORDERBYsalDESC;多列排序首先按照第第一個列或或表達式進進行排序；；當?shù)谝粋€個列或表達達式的數(shù)據(jù)據(jù)相同時，，以第二個個列或表達達式進行排排序，以此此類推。。SELECT*FROMempORDERBYdeptno,salDESC;（3）查詢排序序按表達式排排序可以按特定定的表達式式進行排序序。SELECTempno,ename,salFROMempORDERBYsal*12;使用別名排排序可以使用用目標列列或表達達式的別別名進行行排序。。SELECTempno,sal*12salaryFROMempORDERBYsalary;使用列位位置編號號排序如果列名名或表達達式名稱稱很長，，那么使使用位置置排序可可以縮短短排序語語句的長長度。SELECTempno,sal*12salaryFROMempORDERBY2;（4）查詢統(tǒng)統(tǒng)計函數(shù)格式功能AVGAVG([DISTINCT|ALL]<列名>)計算一列值的平均值（要求數(shù)值列）COUNTCOUNT([DISTINCT|ALL]*)統(tǒng)計元組個數(shù)COUNTCOUNT([DISTINCT|ALL]<列名>)統(tǒng)計一列中非空值的個數(shù)MAXMAX([DISTINCT|ALL]<列名>)求一列值中的最大值MINMIN([DISTINCT|ALL]<列名>)求一列值中的最小值SUMSUM([DISTINCT|ALL]<列名>)計算一列值的總和（要求數(shù)值列）STDDEVSTDDEV(<列名>)..計算一列值的標準差VARIANCEVARIANCE(<列名>)計算一列值的方差注意除了COUNT(*)函數(shù)外，，其他的的統(tǒng)計函函數(shù)都不不考慮返返回值或或表達式式為NULL的情況。。聚集函數(shù)數(shù)只能出出現(xiàn)在目目標列表表達式、、ORDERBY子句、HAVING子句中，，不能出出現(xiàn)在WHERE子句和GROUPBY子句中。。默認對所所有的返返回行進進行統(tǒng)計計，包括括重復的的行；如如果要統(tǒng)統(tǒng)計不重重復的行行信息，，則可以以使用DISTINCT選項。如果對查查詢結(jié)果果進行了了分組，，則聚集集函數(shù)的的作用范范圍為各各個組，，否則聚聚集函數(shù)數(shù)作用于于整個查查詢結(jié)果果。SELECTcount(*),avg(sal),max(sal),min(sal)FROMempWHEREdeptno=10;SELECTavg(comm),sum(comm)FROMemp;SELECTcount(DISTINCTdeptno)FROMemp;SELECTvariance(sal),stddev(sal)FROMemp;13.2.3分組查詢詢基本語法法單列分組組查詢多列分組組查詢使用HAVING子句限制制返回組組使用ROLLUP和CUBE合并分組組查詢SELECTcolumn,group_function,…FROMtable[WHEREcondition][GROUP[BYROOLUP|CUBE|GROUPINGSETS]group_by_expression][HAVINGgroup_condition][ORDERBYcolumn[ASC|DESC]];（1）基本本語法法注意：：GROUPBY子句用用于指指定分分組列列或分分組表表達式式。集合函函數(shù)用用于對對分組組進行行統(tǒng)計計。如如果未未對查查詢分分組，，則集集合函函數(shù)將將作用用于整整個查查詢結(jié)結(jié)果；；如果果對查查詢結(jié)結(jié)果分分組，，則集集合函函數(shù)將將作用用于每每一個個組，，即每每一個個分組組都有有一個個集合合函數(shù)數(shù)。HAVING子句用用于限限制分分組的的返回回結(jié)果果。WHERE子句對對表中中的記記錄進進行過過濾，，而HAVING子句對對分組組后形形成的的組進進行過過濾。。在分組組查詢詢中，，SELECT子句后后面的的所有有目標標列或或目標標表達達式要要么是是分組組列，，要么么是分分組表表達式式，要要么是是集合合函數(shù)數(shù)。單列分分組查查詢將查詢詢出來來的記記錄按按照某某一個個指定定的列列進行行分組組SELECTdeptno,count(*),avg(sal)FROMempGROUPBYdeptno;多列分分組查查詢在GROUPBY子句中中指定定了兩兩個或或多個個分組組列SELECTdeptno,job,count(*),avg(sal)FROMempGROUPBYdeptno,job;使用HAVING子句限限制返返回組組可以使使用HAVING子句，，只有有滿足足條件件的組組才會會返回回。SELECTdeptno,count(*),avg(sal)FROMempGROUPBYdeptnoHAVINGavg(sal)>1500;使用ROLLUP和CUBE如果在在GROUPBY子句中中使用用ROLLUP選項，，則還還可以以生成成橫向向統(tǒng)計計和不不分組組統(tǒng)計計；如果在在GROUPBY子句中中使用用CUBE選項，，則還還可以以生成成橫向向統(tǒng)計計、縱縱向統(tǒng)統(tǒng)計和和不分分組統(tǒng)統(tǒng)計。。SELECTdeptno,job,avg(sal)FROMempGROUPBYROLLUP(deptno,job);SELECTdeptno,job,avg(sal)FROMempGROUPBYCUBE(deptno,job);合并分分組查查詢使用GROUPINGSETS可以將將幾個個單獨獨的分分組查查詢合合并成成一個個分組組查詢詢SELECTdeptno,job,avg(sal)FROMempGROUPBYGROUPINGSETS(deptno,job);13.2.4連接查查詢交叉連連接內(nèi)連接接等值連連接不等值值連接接自身連連接外連接接左外連連接右外連連接全外連連接（1）交叉叉連接接概念兩個或或多個個表之之間的的無條條件連連接。。一個個表中中所有有記錄錄分別別與其其他表表中所所有記記錄進進行連連接。。如果果進行行連接接的表表中分分別有有n1，n2，n3…條記錄錄，那那么交交叉連連接的的結(jié)果果集中中將有有n1×n2×n3×…條記錄錄。以下情情況可可以出出現(xiàn)交交叉連連接連接條條件省省略連接接條條件件非非法法一個個表表的的所所有有行行被被連連接接到到另另一一個個表表的的所所有有行行示例例SELECTename,dnamefromemp,dept;（2）內(nèi)內(nèi)連連接接執(zhí)行行過過程程內(nèi)連連接接語語法法等值值內(nèi)內(nèi)連連接接非等等值值內(nèi)內(nèi)連連接接自身身內(nèi)內(nèi)連連接接執(zhí)行行過過程程首先先在在表表1中找找到到第第一一個個元元組組，，然然后后從從頭頭開開始始掃掃描描表表2，逐逐一一查查找找滿滿足足連連接接條條件件的的元元組組，，找找到到后后就就將將表表1中的的第第1個元元組組與與該該元元組組拼拼接接形形成成結(jié)結(jié)果果表表中中的的一一個個元元組組。。表表2全部部找找完完后后，，再再找找表表1中的的第第2個元元組組，，然然后后再再從從頭頭掃掃描描表表2，逐逐一一查查找找滿滿足足連連接接條條件件的的元元組組，，找找到到后后就就將將表表1中的的第第2個元元組組與與該該元元組組拼拼接接形形成成結(jié)結(jié)果果表表中中的的一一個個元元組組。。重重復復執(zhí)執(zhí)行行，，直直到到表表1中的的全全部部元元組組都都處處理理完完畢畢為為止止。。內(nèi)連連接接語語法法：：標標準準SQL語句句的的連連接接方方式式SELECTtable1.column,talbe2.column[,…]FROMtable1[INNER]JOINtable2[JOIN…]ONcondition;內(nèi)連連接接語語法法：：Oracle擴展展的的連連接接方方式式SELECTtable1.column,talbe2.column[,…]FROMtable1,table2[,…]WHEREcondition;等值值內(nèi)內(nèi)連連接接SELECTempno,ename,sal,emp.deptno,dnameFROMempJOINdeptONemp.deptno=10ANDemp.deptno=dept.deptno;SELECTempno,ename,sal,emp.deptno,dnameFROMemp,deptWHEREemp.deptno=10ANDemp.deptno=dept.deptno;非等值值內(nèi)連連接SELECTempno,ename,sal,gradeFROMempJOINsalgradeONsal>losalANDsal<hisal;SELECTempno,ename,sal,gradeFROMemp,salgradeWHEREsal>losalANDsal<hisal;自身內(nèi)連接接SELECTworker.empno,worker.ename,manager.empno,manager.enameFROMempworkerJOINempmanagerONworker.mgr=manager.empno;SELECTworker.empno,worker.ename,manager.empno,manager.enameFROMempworker,empmanagerWHEREworker.mgr=manager.empno;（3）外連接左外連接右外連接全外連接左外連接語語法：標準準SQL語句的連接接方式SELECTtable1.column,table2.column[,…]FROMtable1LEFTJOINtable2[,]ONtable1.column<operator>table2.column[,…];左外連接語語法：Oracle擴展的連接接方式SELECTtable1.column,table2.column[,…]FROMtable1,table2[,…]WHEREtable1.column<operator>table2.column(+)[…];查詢10號部門的部部門名、員員工號、員員工名和所所有其他部部門的名稱稱，語句為為SELECTdname,empno,enameFROMdeptLEFTJOINempONdept.deptno=emp.deptnoANDdept.deptno=10;或SELECTdname,empno,enameFROMdept,empWHEREdept.deptno=emp.deptno(+)ANDemp.deptno(+)=10;右外連接語語法：標準準SQL語句的連接接方式SELECTtable1.column,table2.column[,…]FROMtable1RIGHTJOINtable2[,…]ONtable1.column<operator>table2.column[…];右外連接語語法：Oracle擴展的連接接方式SELECTtable1.column,table2.column[,…]FROMtable1,table2[,…]WHEREtable1.column(+)<operator>table2.column[…];查詢20號部門的部門門名稱及其員員工號、員工工名，和所有有其他部門的的員工名、員員工號，語句句為SELECTempno,ename,dnameFROMdeptRIGHTJOINempONdept.deptno=emp.deptnoANDdept.deptno=20;或SELECTempno,ename,dnameFROMdept,empWHEREdept.deptno(+)=emp.deptnoAND`dept.deptno(+)=20;全外連接是指指在內(nèi)連接的的基礎上，將將連接操作符符兩側(cè)表中不不符合連接條條件的記錄加加入結(jié)果集中中。在Oracle數(shù)據(jù)庫中，全全外連接的表表示方式為SELECTtable1.column,table2.column[,…]FROMtable1FULLJOINtable2[,…]ONtable1.column1=table2.column2[…];查詢所有的部部門名和員工工名，語句為為SELECTdname,enameFROMempFULLJOINdeptONemp.deptno=dept.deptno;13.2.5子查詢子查詢概述單行單列子查查詢多行單列子查查詢單行多列子查查詢多行多列子查查詢相關(guān)子查詢在FROM子句中使用子子查詢在DDL語句中使用子子查詢使用WITH子句的子查詢詢子查詢的概念念子查詢是指嵌嵌套在其他SQL語句中的SELECT語句，也稱為為嵌套查詢。。在執(zhí)行時，由由里向外，先先處理子查詢詢，再將子查查詢的返回結(jié)結(jié)果用于其父父語句（外部部語句）的執(zhí)執(zhí)行。子查詢作用在INSERT或CREATETABLE語句中使用子子查詢，可以以將子查詢的的結(jié)果寫入到到目標表中；；在UPDATE語句中使用子子查詢可以修修改一個或多多個記錄的數(shù)數(shù)據(jù)；在DELETE語句中使用子子查詢可以刪刪除一個或多多個記錄在WHERE和HAVING子句中使用子子查詢可以返返回的一個或或多個值。（1）子查詢概述述單行單列子查查詢是指子查查詢只返回一一行數(shù)據(jù)，而而且只返回一一列的數(shù)據(jù)。。運算符=，>，<，>=，<=，!=查詢比7934號員工工資高高的員工的員員工號、員工工名、員工工工資信息，語語句為SELECTempno,ename,salFROMempWHEREsal>(SELECTsalFROMempWHEREempno=7934);（2）單行單列子子查詢（3）多行單列子子查詢多行單列子查查詢是指返回回多行數(shù)據(jù)，，且只返回一一列的數(shù)據(jù)。。運算符號運算符含義IN與子查詢返回結(jié)果中任何一個值相等NOTIN與子查詢返回結(jié)果中任何一個值都不等>ANY比子查詢返回結(jié)果中某一個值大=ANY與子查詢返回結(jié)果中某一個值相等<ANY比子查詢返回結(jié)果中某一個值小>ALL比子查詢返回結(jié)果中所有值都大<ALL比子查詢返回結(jié)果中任何一個值都小EXISTS子查詢至少返回一行時條件為TRUENOTEXISTS子查詢不返回任何一行時條件為TRUE查詢與與10號部門門某個個員工工工資資相等等的員員工信信息。。SELECTempno,ename,salFROMempWHEREsalIN(SELECTsalFROMempWHEREdeptno=10);查詢比比10號部門門某個個員工工工資資高的的員工工信息息。SELECTempno,ename,salFROMempWHEREsal>ANY(SELECTsalFROMempWHEREdeptno=10);查詢比比10號部門門所有有員工工工資資高的的員工工信息息。SELECTempno,ename,salFROMempWHEREsal>ALL(SELECTsalFROMempWHEREdeptno=10);（4）單行行多列列子查查詢單行多多列子子查詢詢是指指子查查詢返返回一一行數(shù)數(shù)據(jù)，，但是是包含含多列列數(shù)據(jù)據(jù)。多列數(shù)數(shù)據(jù)進進行比比較時時，可可以成成對比比較，，也可可以非非成對對比較較。成成對比比較要要求多多個列列的數(shù)數(shù)據(jù)必必須同同時匹匹配，，而非非成對對比較較則不不要求求多個個列的的數(shù)據(jù)據(jù)同時時匹配配。查詢詢與與7844號員員工工的的工工資資、、工工種種都都相相同同的的員員工工的的信信息息。。SELECTempno,ename,sal,jobFROMempWHERE(sal,job)=(SELECTsal,jobFROMempWHEREempno=7844);查詢詢與與10號部部門門某某個個員員工工工工資資相相同同，，工工種種也也與與10號部部門門的的某某個個員員工工相相同同的的員員工工的的信信息息。。SELECTempno,ename,sal,jobFROMempWHEREsalIN(SELECTsalFROMempWHEREdeptno=10)ANDjobIN(SELECTjobFROMempWHEREdeptno=10);（5）多多行行多多列列子子查查詢詢多行行多多列列子子查查詢詢是是指指子子查查詢詢返返回回多多行行數(shù)數(shù)據(jù)據(jù)，，并并且且是是多多列列數(shù)數(shù)據(jù)據(jù)。。例如如，，查查詢詢與與10號部部門門某某個個員員工工的的工工資資和和工工種種都都相相同同的的員員工工的的信信息息，，語語句句為為SELECTempno,ename,sal,jobFROMempWHERE(sal,job)IN(SELECTsal,jobFROMempWHEREdeptno=10);（6）相相關(guān)關(guān)子子查查詢詢子查查詢詢在在執(zhí)執(zhí)行行時時并并不不需需要要外外部部父父查查詢詢的的信信息息，，這這種種子子查查詢詢稱稱為為無無關(guān)關(guān)子子查查詢詢。。如果果子子查查詢詢在在執(zhí)執(zhí)行行時時需需要要引引用用外外部部父父查查詢詢的的信信息息，，那那么么這這種種子子查查詢詢就就稱稱為為相相關(guān)關(guān)子子查查詢詢。。在相相關(guān)關(guān)子子查查詢詢中中經(jīng)經(jīng)常常使使用用EXISTS或NOTEXISTS謂詞來實實現(xiàn)。如如果子查查詢返回回結(jié)果，，則條件件為TRUE，如果子子查詢沒沒有返回回結(jié)果，，則條件件為FALSE。查詢沒有有任何員員工的部部門號、、部門名名。SELECTdeptno,dname,locFROMdeptWHERENOTEXISTS(SELECT*FROMempWHEREemp.deptno=dept.deptno);查詢比本本部門平平均工資資高的員員工信息息。SELECTempno,ename,salFROMempeWHEREsal>(SELECTavg(sal)FROMempWHEREdeptno=e.deptno);（7）在FROM子句中使用子子查詢當在FROM子句中使用子子查詢時，該該子查詢被作作為視圖對待待，必須為該該子查詢指定定別名。查詢各個員工工的員工號、、員工名及其其所在部門平平均工資。SELECTempno,ename,d.avgsalFROMemp,(SELECTdeptno,avg(sal)avgsalFROMempGROUPBYdeptno)dWHEREemp.deptno=d.deptno;查詢各個部門門號、部門名名、部門人數(shù)數(shù)及部門平均均工資。SELECTdept.deptno,dname,d.amount,d.avgsalFROMdept,(SELECTdeptno,count(*)amount,avg(sal)avgsalFROMempGROUPBYdeptno)dWHEREdept.deptno=d.deptno;（8）在DDL語句中使用子子查詢可以在CREATETABLE和CREATEVIEW語句中使用子子查詢來創(chuàng)建建表和視圖。。CREATETABLEemp_subqueryASSELECTempno,ename,salFROMemp;CREATEVIEWemp_view_subqueryASSELECT*FROMempWHEREsal>2000;（9）使用WITH子句的子查詢詢?nèi)绻谝粋€SQL語句中多次使使用同一個子子查詢，可以以通過WITH子句給子查詢詢指定一個名名字，從而可可以實現(xiàn)通過過名字引用該該子查詢，而而不必每次都都完整寫出該該子查詢。查詢?nèi)藬?shù)最多多的部門的信信息。SELECT*FROMdeptWHEREdeptnoIN(SELECTdeptnoFROMempGROUPBYdeptnoHAVINGcount(*)>=ALL(SELECTcount(*)FROMempGROUPBYdeptno));相同的子查詢詢連續(xù)出現(xiàn)了了兩次，因此此可以按下列列方式編寫查查詢語句。WITHdeptinfoAS(SELECTdeptno,count(*)numFROMempGROUPBYdeptno)SELECT*FROMdeptWHEREdeptnoIN(SELECTdeptnoFROMdeptinfoWHEREnum=(SELECTmax(num)FROMdeptinfo));13.2.6合并查詢語法UNIONINTERSECTMINUS語法：SELECTquery_statement1[UNION|UNIONALL|INTERSECT|MINUS]SELECTquery_statement2;注意：當要合并幾個個查詢的結(jié)果果集時，這幾幾個查詢的結(jié)結(jié)果集必須具具有相同的列列數(shù)與數(shù)據(jù)類類型。如果要對最終終的結(jié)果集排排序，只能在在最后一個查查詢之后用ORDERBY子句指明排序序列。（1）UNIONUNION運算符用于獲獲取幾個查詢詢結(jié)果集的并并集，將重復復的記錄只保保留一個，并并且默認按第第一列進行排排序。查詢10號部門的員工工號、員工名名、工資和部部門號以及工工資大于2000的所有員工的的員工號、員員工名、工資資和部門號，，語句為SELECTempno,ename,sal,deptnoFROMempWHEREdeptno=10UNIONSELECTempno,ename,sal,deptnoFROMempWHEREsal>2000ORDERBYdeptno;如果要保留所所有的重復記記錄，則需要要使用UNIONALL運算符。SELECTempno,ename,sal,deptnoFROMempWHEREdeptno=10UNIONALLSELECTempno,ename,sal,deptnoFROMempWHEREsal>2000ORDERBYdeptno;（2）INTERSECTINTERSECT用于獲取幾個個查詢結(jié)果集集的交集，只只返回同時存存在于幾個查查詢結(jié)果集中中的記錄。同同時，返回的的最終結(jié)果集集默認按第一一列進行排序序。查詢30號部門中工資資大于2000的員工號、員員工名、工資資和部門號，，語句為：SELECTempno,ename,sal,deptnoFROMempWHEREdeptno=30INTERSECTSELECTempno,ename,sal,deptnoFROMEMPWHEREsal>2000;（3）MINUSMINUS用于獲取幾個個查詢結(jié)果集集的差集，即即返回在第一一個結(jié)果集中中存在，而在在第二個結(jié)果果集中不存在在的記錄。同同時，返回的的最終結(jié)果集集默認按第一一列進行排序序。查詢30號部門中工工種不是“SALESMAN”的員工號、、員工名和和工種名稱稱，語句為為：SELECTempno,ename,jobFROMempWHEREdeptno=30MINUSSELECTempno,ename,jobFROMEMPWHEREjob='SALESMAN';13.3數(shù)據(jù)操縱插入數(shù)據(jù)修改數(shù)據(jù)MERGE語句句刪除除數(shù)數(shù)據(jù)據(jù)13.3.1插入入數(shù)數(shù)據(jù)據(jù)插入入單行行記記錄錄利用用子子查查詢詢插插入入數(shù)數(shù)據(jù)據(jù)向多多個個表表中中插插入入數(shù)數(shù)據(jù)據(jù)（1）插入入單行行記記錄錄語法法INSERTINTOtable_name|view_name[(column1[,column2…])]VALUES(value1[,values,…])注意如果在INTO子句中沒有指指明任何列名名，則VALUES子句中列值的的個數(shù)、順序序、類型必須須與表中列的的個數(shù)、順序序、類型相匹匹配。如果在INTO子句中中指定定了列列名，，則VALUES子句中中提供供的列列值的的個數(shù)數(shù)、順順序、、類型型必須須與指指定列列的個個數(shù)、、順序序、類類型按按位置置對應應。向表或或視圖圖中插插入的的數(shù)據(jù)據(jù)必須須滿足足表的的完整整性約約束。。字符型型和日日期型型數(shù)據(jù)據(jù)在插插入時時要加加單引引號。。日期期類型型數(shù)據(jù)據(jù)需要要按系系統(tǒng)默默認格格式輸輸入，，或使使用TO_DATE函數(shù)進進行日日期轉(zhuǎn)轉(zhuǎn)換。。向dept表中插插入一一行記記錄。。INSERTINTOdeptVALUES(50,'IM','dalian');向emp表中插插入一一行記記錄。。INSERTINTOemp(empno,ename,sal,hiredate)VALUES(1234,'JOAN',2500,'20-4月-2007');（2）利用用子查查詢插插入數(shù)數(shù)據(jù)語法INSERTINTOtable_name|view_name[(column1[,column2,…])subquery;注意INTO子句中中指定定的列列的個個數(shù)、、順序序、類類型必必須與與子查查詢中中列的的個數(shù)數(shù)、順順序和和類型型相匹匹配。。統(tǒng)計各各個部部門的的部門門號、、部門門最高高工資資和最最低工工資，，并將將統(tǒng)計計的結(jié)結(jié)果寫寫入到到表emp_salary（假設設該表表已經(jīng)經(jīng)創(chuàng)建建）中中。INSERTINTOemp_salarySELECTdeptno,max(sal),min(sal)FROMempGROUPBYdeptno;向emp表中插入入一行記記錄，其其員工名名為FAN，員工號號為1235，其他信信息與員員工名為為SCOTT的員工信信息相同同。INSERTINTOempSELECT1235,'FAN',job,mgr,hiredate,sal,comm,deptnoFROMempWHEREename='SCOTT';如果要將將大量數(shù)數(shù)據(jù)插入入表中，，可以利利用子查查詢直接接裝載的的方式進進行。由由于直接接裝載數(shù)數(shù)據(jù)的操操作過程程不寫入入日志文文件，因因此數(shù)據(jù)據(jù)插入操操作的速速度大大大提高。。利用子查查詢裝載載數(shù)據(jù)語語法為：：INSERT/*+APPEND*/INTOtable_name|view_name[(column1[,column2,…])subquery;復制emp表中empno，ename，sal，deptno四列的值值，并插插入到new_emp表中，語語句為：：INSERT/*+APPEND*/INTOnew_emp(empno,ename,sal,deptno)SELECTempno,ename,sal,deptnofromemp;13.3.2修改數(shù)據(jù)據(jù)基本語法法修改單行行記錄修改多行行記錄帶有子查查詢的修修改基本語法法UPDATEtable_name|view_nameSETcolumn1=value1[,column2=value2…][WHEREcondition]修改單條條記錄UPDATEempSETsal=sal+100,comm=200WHEREempno=7844;修改多條條記錄UPDATEempSETsal=sal+150WHEREdeptno=20;利用子查查詢修改改記錄UPDATEempSETsal=300+(SELECTavg(sal)FROMempWHEREdeptno=10)WHEREdeptno=30;13.3.3MERGE語句利用MERGE語句可以以同時完完成數(shù)據(jù)據(jù)的插入入與更新新操作。。將源表的的數(shù)據(jù)分分別與目目標表中中的數(shù)據(jù)據(jù)根據(jù)特特性條件件進行比比較（每每次只比比較一條條記錄）），如果果匹配，，則利用用源表中中的記錄錄更新目目標表中中的記錄錄，如果果不匹配配，則將將源表中中的記錄錄插入目目標表中中。使用MERGE語句操作作時，用用戶需要要具有源源表的SELECT對象權(quán)限限以及目目標表的的INSERT，UPDATE對象權(quán)限限。MERGE語句的基基本語法法為：MERGEINTO[schema.]target_table[target_alias]USING[schema.]source_table|source_view|source_subquery[source_alias]ON(condition)WHENMATCHEDTHENUPDATESETcolumn1=expression1[,column2=expression2…][where_clause][DELETEwhere_clause]WHENNOTMATCHEDTHENINSERT[(column2[,column2…])]VALUES(expresstion1[,expression2…])[where_clause];參數(shù)說明明INTO：指定進進行數(shù)據(jù)據(jù)更新或或插入的的目標表表；USING：指定用用于目標標表數(shù)據(jù)據(jù)更新或或插入的的源表或或視圖或或子查詢詢；ON：決定MERGE語句句執(zhí)執(zhí)行行更更新新操操作作還還是是插插入入操操作作的的條條件件。。對對于于目目標標表表中中滿滿足足條條件件的的記記錄錄，，則則利利用用源源表表中中的的相相應應記記錄錄進進行行更更新新；；而而源源表表中中不不滿滿條條件件的的記記錄錄將將被被插插入入目目標標表表中中；；where_clause：只只有有當當該該條條件件為為真真時時才才進進行行數(shù)數(shù)據(jù)據(jù)的的更更新新或或插插入入操操作作；；DELETEwhere_clause：當當目目標標表表中中更更新新后后的的記記錄錄滿滿足足該該條條件件時時，，則則刪刪除除該該記記錄錄。。使用用“雇員員”表（（別別名名e）作作為為數(shù)數(shù)據(jù)據(jù)源源，，在在該該表表的的副副本本“副本本_雇員員”表（（別別名名c）中中插插入入并并更更新新行行。。MERGEINTO副本本_雇員員cUSING雇員eON(c.雇員標識=e.雇員標識)WHENMATCHEDTHENUPDATESETc.姓氏=e.姓氏,c.部門標識=e.部門標識WHENNOTMATCHEDTHENINSERTVALUES(e.雇員標識,e.姓氏,e.部門標識);MERGEINTO副本_雇員cUSING雇員eON(c.雇員標識=e.雇員標識)WHENMATCHEDTHENUPDATESETc.姓氏=e.姓氏,c.部門標識=e.部門標識WHENNOTMATCHEDTHENINSERTVALUES(e.雇員標識,e.姓氏,e.部門標識);MERGE語句執(zhí)行前的的“副本_雇員”雇員標識姓姓氏 部門標標識100 Smith40103 Chang30MERGE執(zhí)行后的“副本_雇員”雇員標識姓姓氏 部門標標識100 King 90103 Hunold60152Davies50雇員員（（來來源源表表））雇員員標標識識姓姓氏氏部部門門標標識識100King90103Hunold60152Davies50現(xiàn)有有表表source_emp和target_emp，表表中中數(shù)數(shù)據(jù)據(jù)如如下下。。利利用用source_emp表中中的的數(shù)數(shù)據(jù)據(jù)更更新新target_emp表中中的的數(shù)數(shù)據(jù)據(jù)，，對對target_emp表中中存存在在的的員員工工信信息息進進行行更更新新，，對對不不存存在在的的員員工工進進行行信信息息插插入入。。SELECT*FROMsource_emp;EMPNOENAMEDEPTNO------------------------------100JOAN10110SMITH20120TOM30SELECT*FROMtarget_emp;EMPNOENAMEDEPTNO------------------------------------100MARRY2020JACK40MERGEINTOtarget_emptUSINGsource_empsON(t.empno=s.empno)WHENMATCHEDTHENUPDATESETt.ename=s.ename,t.deptno=s.deptnoWHENNOTMATCHEDTHENINSERTVALUES(s.empno,s.ename,s.deptno)；SELECT*FROMtarget_emp;EMPNOENAMEDEPTNO------------------------------100JOAN1020JACK40110SMITH20120TOM3013.3.4刪除數(shù)據(jù)基本語法刪除單條記錄錄刪除多條記錄錄帶有子查詢的的刪除操作利用TRUNCATE刪除數(shù)據(jù)TRUNCATE與DELETE區(qū)別基本語法DELETEFROMtable|view[WHEREcondition]刪除單條記錄錄DELETEFROMempWHEREempno=7844;刪除多條記錄錄DELETEFROMempWHEREdeptno=10;帶有子查詢的的刪除操作DELETEFROMempWHEREsal>(SELECTsalFROMempWHEREempno=7900);利用TRUNCATE刪除數(shù)據(jù)TRUNCATETABLEtable_nameTRUNCATE與DELETE區(qū)別釋放存儲空間間不寫入日志文文件，因此執(zhí)執(zhí)行效率較高高，但該操作作不可回滾。。13.4事務處理事務概述Oracle事務處理13.4.1事務概述原子性（Atomicity）：事務是數(shù)數(shù)據(jù)庫的邏輯輯工作單位，，事務中的所所有操作要么么都做，要么么都不做，不不存在第三種種情況。一致性（Consistency）：事務執(zhí)行行的結(jié)果必須須是使數(shù)據(jù)庫庫從一個一致致性狀態(tài)轉(zhuǎn)變變到另一個一一致性狀態(tài)，，不存在中間間的狀態(tài)。隔離性（Isolation）：數(shù)據(jù)庫中中一個事務的的執(zhí)行不受其其他事務干擾擾，每個事務務都感覺不到到還有其他事事務在并發(fā)執(zhí)執(zhí)行。持久性（Durability）：一個事務務一旦提交，，則對數(shù)據(jù)庫庫中數(shù)據(jù)的改改變是永久性性的，以后的的操作或故障障不會對事務務的操作結(jié)果果產(chǎn)生任何影影響。13.4.2Oracle事務處理事務提交方式式用戶顯式執(zhí)行行COMMIT命令執(zhí)行特定操作作時系統(tǒng)自動動提交。說明當事務提交后后，用戶對數(shù)數(shù)據(jù)庫修改操操作的日志信信息由日志緩緩沖區(qū)寫入重重做日志文件件中，釋放該該事務所占據(jù)據(jù)的系統(tǒng)資源源和數(shù)據(jù)庫資資源。此時，，其他會話可可以看到該事事務對數(shù)據(jù)庫庫的修改結(jié)果果。當執(zhí)行CREATE，ALTER，DROP，RENAME，REVOKE，GRANT，CONNECT，DISCONNECT等命令時，系系統(tǒng)將自動提提交。事務回滾方式式事務全部回滾滾ROLLBACK事務部分回滾滾SAVEPOINTXROLLBACKTOXROLLBACKTOB;（回滾最后一一個INSERT操作）ROLLBACKTOA;（回滾后后面的INSERT操作和UPDATE操作）ROLLBACK;（回滾全全部操作作）13.5SQL函數(shù)SQL函數(shù)分類類數(shù)值函數(shù)數(shù)字符函數(shù)數(shù)日期函數(shù)數(shù)轉(zhuǎn)換函數(shù)數(shù)其他函數(shù)數(shù)13.5.1SQL函數(shù)分類類根據(jù)參數(shù)數(shù)作用行行數(shù)的不不同，可可以分為為：單行函數(shù)數(shù)多行函數(shù)數(shù)根據(jù)參數(shù)數(shù)類型不不同，可可以分為為：數(shù)值函數(shù)數(shù)字符函數(shù)數(shù)日期函數(shù)數(shù)轉(zhuǎn)換函數(shù)數(shù)聚集函數(shù)數(shù)13.5.2數(shù)值函數(shù)數(shù)函數(shù)返回值ABS(n)返回n的絕對值CEIL(n)返回大于或等于n的最小整數(shù)EXP(n)返回e的n次冪FLOOR(n)返回小于或等于n的最大整數(shù)LN(n)返回以E為底的n的對數(shù)LOG(m,n)返回以m為底的n的對數(shù)MOD(m,n)返回m除以n的余數(shù)POWER(m,n)返回m的n次方ROUND(m[,n])對m進行四舍五入（n大于0時，將m四舍五入到小數(shù)點右邊n位，n等于零時，表示對m進行取整，n小于0時，則小數(shù)點左邊的數(shù)字位置被圓整。SIGN(n)判斷n的正負（n大于0返回，n等于0返回0，n小于0返回-1SQRT(n)返回n的平方根SQUARE(n)返回n的平方TRUNC(m[,n])對m進行截斷操作（n截斷到小數(shù)點后第n位，如果n未給出，則系統(tǒng)默認為0,n也可以為負數(shù)，表示小數(shù)點左邊的數(shù)字位置被刪除成零。SELECTsal/22daysal,round(sal/22,1),trunc(sal/22,1),round(sal/22,-1),trunc(sal/22,-1)FROMemp;DAYSALROUND(SAL/22,1)TRUNC(SAL/22,1)ROUND(SAL/22,-1)TRUNC(SAL/22,-1)---------------------------------------------------------------------45.454545545.545.4504090.909090990.990.9909054.545454554.554.5505086.363636486.486.3908070.454545570.570.47070146.590909146.6146.5150140SELECTsal,width_bucket(sal,1000,5000,10)FROMempWHEREdeptno=30;SALWIDTH_BUCKET(SAL,1000,5000,10)----------------------------------------17002135012950561001110501SELECTfloor(3.5),ceil(3.5),mod(5,3),remainder(5,3),mod(4,3),remainder(4,3)FROMdual;FLOOR(3.5)CEIL(3.5)MOD(5,3)REMAINDER(5,3)MOD(4,3)REMAINDER(4,3)-------------------------------------------------------342-11113.5.3字符函數(shù)數(shù)函數(shù)返回值ASCII(char)字符串首字符的ASC碼值CHR(n)ASC碼值為n的字符CONCAT把兩個列值拼接起來；“||”操作符更通用INITCAP(char)將字符串中每個單詞的首字母大寫INSTR（char1,char2,a,b)INSTRB（char1,char2,a,b)返回指定字符的位置LENGTH(char)LENGTHB(char)計算字符串的長度SUBSTR(char,m[,n])求子串。column中從起始位置m開始長度為n的子串Replace(str1,str2,str3)LOWER(char)、UPPER(char)將字符串中所有的大寫字母變?yōu)樾?大)寫LPAD(char1,n[,char2])從左側(cè)用字符串char2補齊字符串char1至長度n（右對齊）RPAD(char1,n[,char2])從右側(cè)用char2補齊char1至長度n.LTRIM(char[,SET])把char中最左側(cè)的若干個字符去掉，以使其首字符不在SET中RTRIM(char[,SET])把char中最右側(cè)的若干個字符去掉，以使其尾字符不在SET中TRIM([leading|trailing|both]FROMstring)SELECTlpad('abc',5,'#')leftpad,rpad('abc',5,'#')rightpad,ltrim('abcd','a')lefttrim,rtrim('abcde','e')righttrim,substr('abcd',2,3)substringFROMdual;LEFTPADRIGHTPADLEFTTRIMRIGHTTRIMSUBSTRING--------------------------------------------------------------##abcabc##bcdabcdbcdSELECTconcat(concat(ename,'''sjobcategoryis'),job)"Job"FROMempWHEREempno=7844;Job--------------------------------------TURNER'sjobcategoryisSALESMANSELECTinstr('abcde','b')position,replace('oracle9i','9i','10g')newstring,soundex('hello')soundFROMdual;POSITIONNEWSTRINGSOUND---------------------------------------2oracle10gH40013.5.4日期函函數(shù)日期函函數(shù)是是指對對日期期進行行處理理的函函數(shù)，，函數(shù)數(shù)輸入入為DATE或TIMESTAMP類型的數(shù)據(jù)據(jù)，輸出為為DATE類型的數(shù)據(jù)據(jù)（除MONTH_BETWEEN函數(shù)返回整整數(shù)以外））。Oracle數(shù)據(jù)庫中日日期的默認認格式為DD-MON-YY?？梢酝ㄟ^過設置NLS_DATE_FORMAT參數(shù)設置當當前會話的的日期格式式，通過NLS_LANGUAGE參數(shù)設置表表示日期的的字符集。。例如：ALTERSESSIONSETNLS_DATE_FORMAT='YYYY-MM-DDHH24:MI:SS';ALTERSESSIONSETNLS_LANGUAGE='AMERICAN';函數(shù)返回值ADD_MONTHS(d,n)返回日期d添加n個月的日期CURRENT_DATE返回系統(tǒng)當前日期和時間CURRENT_TIMESTAMP[(p)]返回系統(tǒng)當前時間戳EXTRACT(departFROMd)返回d中depart對應部分的內(nèi)容

LAST_DAY(d)返回d所在月份最后一天的日期LOCALTIMESTAMP(P)返回本地時間戳LOCALTIMESTAMP[(p)]返回當前會話時區(qū)所對應的日期時