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語言是關系數(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）：用于設置用戶會話相關參數(shù)，包括ALTERSESSION。13.1.3SQL語言的特點功能一體化：幾乎涵蓋了對數(shù)據(jù)庫的所有操作，語言風格統(tǒng)一。高度的非過程化：在使用SQL語言操作數(shù)據(jù)庫時，用戶只需要說明“做什么”，而不需要說明“怎樣做”。用戶任務的實現(xiàn)對用戶而言是透明的，由系統(tǒng)自動完成。這大大減輕了用戶的負擔，同時降低了對用戶的技術要求。面向集合的操作方式：SQL語言采用集合操作方式，不僅查詢結果可以是多條記錄的集合，而且一次插入、刪除、修改操作的對象也可以是多條記錄的集合。面向集合的操作方式極大地提高了對數(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;使用算算術表表達式式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關系運運算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)結多多個查詢詢條件。。優(yōu)先級：：NOT、AND、OR（用戶可可以用括括號改變變優(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ù)數(shù)據(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選項。如果對查查詢結果果進行了了分組，，則聚集集函數(shù)的的作用范范圍為各各個組，，否則聚聚集函數(shù)數(shù)作用于于整個查查詢結果果。SELECTcount(*),avg(sal),max(sal),min(sal)FROMempWHEREdeptno=10;SELECTavg(comm),sum(comm)FROMemp;SELECTcount(DISTINCTdeptno)FROMemp;SELECTvariance(sal),stddev(sal)FROMemp;分組查詢詢基本語法法單列分組組查詢多列分組組查詢使用HAVING子句限制制返回組組使用ROLLUP和CUBE合并分組組查詢SELECTcolumn,group_function,…FROMtable[WHEREcondition][GROUP[BYROOLUP|CUBE|GROUPINGSETS]group_by_expression][HAVINGgroup_condition][ORDERBYcolumn[ASC|DESC]];（1）基本語語法注意：GROUPBY子句用于于指定分分組列或或分組表表達式。。集合函數(shù)數(shù)用于對對分組進進行統(tǒng)計計。如果果未對查查詢分組組，則集集合函數(shù)數(shù)將作用用于整個個查詢結結果；如如果對查查詢結果果分組，，則集合合函數(shù)將將作用于于每一個個組，即即每一個個分組都都有一個個集合函函數(shù)。HAVING子句用于于限制分分組的返返回結果果。WHERE子句對表表中的記記錄進行行過濾，，而HAVING子句對分分組后形形成的組組進行過過濾。在分組查查詢中，，SELECT子句后面面的所有有目標列列或目標標表達式式要么是是分組列列，要么么是分組組表達式式，要么么是集合合函數(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)計。。SELECTdeptno,job,avg(sal)FROMempGROUPBYROLLUP(deptno,job);SELECTdeptno,job,avg(sal)FROMempGROUPBYCUBE(deptno,job);合并分組組查詢使用GROUPINGSETS可以將幾幾個單獨獨的分組組查詢合合并成一一個分組組查詢SELECTdeptno,job,avg(sal)FROMempGROUPBYGROUPINGSETS(deptno,job);連接查詢詢交叉連接接內(nèi)連接等值連接接不等值連連接自身連接接外連接左外連接接右外連接接全外連接接（1）交叉連連接概念兩個或多多個表之之間的無無條件連連接。一一個表中中所有記記錄分別別與其他他表中所所有記錄錄進行連連接。如如果進行行連接的的表中分分別有n1，n2，n3…條記錄，，那么交交叉連接接的結果果集中將將有n1×n2×n3×…條記錄。以下情況可以以出現(xiàn)交叉連連接連接條件省略略連接條件非法法一個表的所有有行被連接到到另一個表的的所有行示例SELECTename,dnamefromemp,dept;（2）內(nèi)連接執(zhí)行過程內(nèi)連接語法等值內(nèi)連接非等值內(nèi)連接接自身內(nèi)連接執(zhí)行過程首先在表1中找到第一個個元組，然后后從頭開始掃掃描表2，逐一查找滿滿足連接條件件的元組，找找到后就將表表1中的第1個元組與該元元組拼接形成成結果表中的的一個元組。。表2全部找完后，，再找表1中的第2個元組，然后后再從頭掃描描表2，逐一查找滿滿足連接條件件的元組，找找到后就將表表1中的第2個元組與該元元組拼接形成成結果表中的的一個元組。。重復執(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)連接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è)表中中不符合連連接條件的的記錄加入入結果集中中。在Oracle數(shù)據(jù)庫中，，全外連接接的表示方方式為SELECTtable1.column,table2.column[,…]FROMtable1FULLJOINtable2[,…]ONtable1.column1=table2.column2[…];查詢所有的的部門名和和員工名，，語句為SELECTdname,enameFROMempFULLJOINdeptONemp.deptno=dept.deptno;子查詢子查詢概述述單行單列子子查詢多行單列子子查詢單行多列子子查詢多行多列子子查詢相關子查詢詢在FROM子句中使用用子查詢在DDL語句中使用用子查詢使用WITH子句的子查查詢子查詢的概概念子查詢是指指嵌套在其其他SQL語句中的SELECT語句，也稱稱為嵌套查查詢。在執(zhí)行時，，由里向外外，先處理理子查詢，，再將子查查詢的返回回結果用于于其父語句句（外部語語句）的執(zhí)執(zhí)行。子查詢作用用在INSERT或CREATETABLE語句中使用用子查詢，，可以將子子查詢的結結果寫入到到目標表中中；在UPDATE語句中使用用子查詢可可以修改一一個或多個個記錄的數(shù)數(shù)據(jù)；在DELETE語句中使用用子查詢可可以刪除一一個或多個個記錄在WHERE和HAVING子句句中中使使用用子子查查詢詢可可以以返返回回的的一一個個或或多多個個值值。。（1）子子查查詢詢概概述述單行行單單列列子子查查詢詢是是指指子子查查詢詢只只返返回回一一行行數(shù)數(shù)據(jù)據(jù)，，而而且且只只返返回回一一列列的的數(shù)數(shù)據(jù)據(jù)。。運算算符符=，>，<，>=，<=，!=查詢詢比比7934號員員工工工工資資高高的的員員工工的的員員工工號號、、員員工工名名、、員員工工工工資資信信息息，，語語句句為為SELECTempno,ename,salFROMempWHEREsal>(SELECTsalFROMempWHEREempno=7934);（2）單單行行單單列列子子查查詢詢（3）多多行行單單列列子子查查詢詢多行行單單列列子子查查詢詢是是指指返返回回多多行行數(shù)數(shù)據(jù)據(jù)，，且且只只返返回回一一列列的的數(shù)數(shù)據(jù)據(jù)。。運算算符符號號運算符含義IN與子查詢返回結果中任何一個值相等NOTIN與子查詢返回結果中任何一個值都不等>ANY比子查詢返回結果中某一個值大=ANY與子查詢返回結果中某一個值相等<ANY比子查詢返回結果中某一個值小>ALL比子查詢返回結果中所有值都大<ALL比子查詢返回結果中任何一個值都小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ù)據(jù)，，但是包包含多列列數(shù)據(jù)。。多列數(shù)據(jù)據(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ù)據(jù)，，并且是是多列數(shù)數(shù)據(jù)。例如，查查詢與10號部門某某個員工工的工資資和工種種都相同同的員工工的信息息，語句句為SELECTempno,ename,sal,jobFROMempWHERE(sal,job)IN(SELECTsal,jobFROMempWHEREdeptno=10);（6）相關子子查詢子查詢詢在執(zhí)執(zhí)行時時并不不需要要外部部父查查詢的的信息息，這這種子子查詢詢稱為為無關關子查查詢。。如果子子查詢詢在執(zhí)執(zhí)行時時需要要引用用外部部父查查詢的的信息息，那那么這這種子子查詢詢就稱稱為相相關子子查詢詢。在相關關子查查詢中中經(jīng)常常使用用EXISTS或NOTEXISTS謂詞來來實現(xiàn)現(xiàn)。如如果子子查詢詢返回回結果果，則則條件件為TRUE，如果果子查查詢沒沒有返返回結結果，，則條條件為為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ù)及及部門平均均工資。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));合并查詢語法UNIONINTERSECTMINUS語法：SELECTquery_statement1[UNION|UNIONALL|INTERSECT|MINUS]SELECTquery_statement2;注意：當要合并幾個個查詢的結果果集時，這幾幾個查詢的結結果集必須具具有相同的列列數(shù)與數(shù)據(jù)類類型。如果要對最終終的結果集排排序，只能在在最后一個查查詢之后用ORDERBY子句指明排序序列。（1）UNIONUNION運算符用于獲獲取幾個查詢詢結果集的并并集，將重復復的記錄只保保留一個，并并且默認按第第一列進行排排序。查詢10號部門的員工工號、員工名名、工資和部部門號以及工工資大于2000的所有員工的的員工號、員員工名、工資資和部門號，，語句為SELECTempno,ename,sal,deptnoFROMempWHEREdeptno=10UNIONSELECTempno,ename,sal,deptnoFROMempWHEREsal>2000ORDERBYdeptno;如果要保留所所有的重復記記錄，則需要要使用UNIONALL運算符。SELECTempno,ename,sal,deptnoFROMempWHEREdeptno=10UNIONALLSELECTempno,ename,sal,deptnoFROMempWHEREsal>2000ORDERBYdeptno;（2）INTERSECTINTERSECT用于獲取幾個個查詢結果集集的交集，只只返回同時存存在于幾個查查詢結果集中中的記錄。同同時，返回的的最終結果集集默認按第一一列進行排序序。查詢30號部門中工資資大于2000的員工號、員員工名、工資資和部門號，，語句為：SELECTempno,ename,sal,deptnoFROMempWHEREdeptno=30INTERSECTSELECTempno,ename,sal,deptnoFROMEMPWHEREsal>2000;（3）MINUSMINUS用于獲取幾個個查詢結果集集的差集，即即返回在第一一個結果集中中存在，而在在第二個結果果集中不存在在的記錄。同同時，返回的的最終結果集集默認按第一一列進行排序序。查詢30號部門中工種種不是“SALESMAN”的員工號、員員工名和工種種名稱，語句句為：SELECTempno,ename,jobFROMempWHEREdeptno=30MINUSSELECTempno,ename,jobFROMEMPWHEREjob='SALESMAN';13.3數(shù)據(jù)操縱插入數(shù)據(jù)修改數(shù)據(jù)MERGE語句刪除數(shù)據(jù)插入數(shù)據(jù)插入單行記錄利用子查詢插插入數(shù)據(jù)向多個表中插插入數(shù)據(jù)（1）插入單行記錄語法INSERTINTOtable_name|view_name[(column1[,column2…])]VALUES(value1[,values,…])注意如果在INTO子句中沒有指指明任何列名名，則VALUES子句中列值的的個數(shù)、順序序、類型必須須與表中列的的個數(shù)、順序序、類型相匹匹配。如果在INTO子句中指定了了列名，則VALUES子句中提供的的列值的個數(shù)數(shù)、順序、類類型必須與指指定列的個數(shù)數(shù)、順序、類類型按位置對對應。向表或視圖中中插入的數(shù)據(jù)據(jù)必須滿足表表的完整性約約束。字符型和日期期型數(shù)據(jù)在插插入時要加單單引號。日期期類型數(shù)據(jù)需需要按系統(tǒng)默默認格式輸入入，或使用TO_DATE函數(shù)進行日期期轉(zhuǎn)換。向dept表中插入一行行記錄。INSERTINTOdeptVALUES(50,'IM','dalian');向emp表中插入一行行記錄。INSERTINTOemp(empno,ename,sal,hiredate)VALUES(1234,'JOAN',2500,'20-4月-2007');（2）利用子查詢詢插入數(shù)據(jù)語法INSERTINTOtable_name|view_name[(column1[,column2,…])subquery;注意INTO子句中指定的的列的個數(shù)、、順序、類型型必須與子查查詢中列的個個數(shù)、順序和和類型相匹配配。統(tǒng)計各個部門門的部門號、、部門最高工工資和最低工工資，并將統(tǒng)統(tǒng)計的結果寫寫入到表emp_salary（假設該表已已經(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ù)據(jù)插入表表中，可以利利用子查詢直直接裝載的方方式進行。由由于直接裝載載數(shù)據(jù)的操作作過程不寫入入日志文件，，因此數(shù)據(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;修改數(shù)據(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ù)的的插入與更新新操作。將源表的數(shù)據(jù)據(jù)分別與目標標表中的數(shù)據(jù)據(jù)根據(jù)特性條條件進行比較較（每次只比比較一條記錄錄），如果匹匹配，則利用用源表中的記記錄更新目標標表中的記錄錄，如果不匹匹配，則將源源表中的記錄錄插入目標表表中。使用MERGE語句操作時，，用戶需要具具有源表的SELECT對象權限以及及目標表的INSERT，UPDATE對象權限。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ù)數(shù)據(jù)更新或插插入的目標表表；USING：指定用于目目標表數(shù)據(jù)更更新或插入的的源表或視圖圖或子查詢；；ON：決定MERGE語句執(zhí)行行更新操操作還是是插入操操作的條條件。對對于目標標表中滿滿足條件件的記錄錄，則利利用源表表中的相相應記錄錄進行更更新；而而源表中中不滿條條件的記記錄將被被插入目目標表中中；where_clause：只有當當該條件件為真時時才進行行數(shù)據(jù)的的更新或或插入操操作；DELETEwhere_clause：當目標標表中更更新后的的記錄滿滿足該條條件時，，則刪除除該記錄錄。使用“雇員”表（別名名e）作為數(shù)數(shù)據(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í)行行前的“副本_雇員”雇員標識識姓氏氏 部門門標識100Smith40103Chang30MERGE執(zhí)行后的的“副本_雇員”雇員標識識姓氏氏 部門門標識100King90103Hunold 60152Davies 50雇員（來來源表））雇員標識識姓氏氏 部門門標識100King90103Hunold 60152Davies 50現(xiàn)有表source_emp和target_emp，表中數(shù)據(jù)如如下。利用source_emp表中的數(shù)據(jù)更更新target_emp表中的數(shù)據(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------------------------------100JOAN1020JACK40110SMITH20120TOM30刪除數(shù)據(jù)據(jù)基本語法法刪除單條條記錄刪除多條條記錄帶有子查查詢的刪刪除操作作利用TRUNCATE刪除數(shù)據(jù)據(jù)TRUNCATE與DELETE區(qū)別基本語法法DELETEFROMtable|view[WHEREcondition]刪除單條條記錄DELETEFROMempWHEREempno=7844;刪除多條條記錄DELETEFROMempWHEREdeptno=10;帶有子查查詢的刪刪除操作作DELETEFROMempWHEREsal>(SELECTsalFROMempWHEREempno=7900);利用TRUNCATE刪除數(shù)據(jù)據(jù)TRUNCATETABLEtable_nameTRUNCATE與DELETE區(qū)別釋放存儲儲空間不寫入日日志文件件，因此此執(zhí)行效效率較高高，但該該操作不不可回滾滾。13.4事務處理理事務概述述Oracle事務處理理事務概述述原子性（（Atomicity）：事務務是數(shù)據(jù)據(jù)庫的邏邏輯工作作單位，，事務中中的所有有操作要要么都做做，要么么都不做做，不存存在第三三種情況況。一致性（（Consistency）：事務務執(zhí)行的的結果必必須是使使數(shù)據(jù)庫庫從一個個一致性性狀態(tài)轉(zhuǎn)轉(zhuǎn)變到另另一個一一致性狀狀態(tài)，不不存在中中間的狀狀態(tài)。隔離性（（Isolation）：數(shù)據(jù)據(jù)庫中一一個事務務的執(zhí)行行不受其其他事務務干擾，，每個事事務都感感覺不到到還有其其他事務務在并發(fā)發(fā)執(zhí)行。。持久性（（Durability）：一個個事務一一旦提交交，則對對數(shù)據(jù)庫庫中數(shù)據(jù)據(jù)的改變變是永久久性的，，以后的的操作或或故障不不會對事事務的操操作結果果產(chǎn)生任任何影響響。事務處理理事務提交交方式用戶顯式式執(zhí)行COMMIT命令執(zhí)行特定定操作時時系統(tǒng)自自動提交交。說明當事務提提交后，，用戶對對數(shù)據(jù)庫庫修改操操作的日日志信息息由日志志緩沖區(qū)區(qū)寫入重重做日志志文件中中，釋放放該事務務所占據(jù)據(jù)的系統(tǒng)統(tǒng)資源和和數(shù)據(jù)庫庫資源。。此時，，其他會會話可以以看到該該事務對對數(shù)據(jù)庫庫的修改改結果。。當執(zhí)行CREATE，ALTER，DROP，RENAME，REVOKE，GRANT，CONNECT，DISCONNECT等命令時時，系統(tǒng)統(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ù)函數(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ù)數(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)---------------------------------------------------------------------SELECTsal,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-111字符函數(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---------------------------------------2oracle10gH400日期函數(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ū)所對應的日期時間MONTHS_BETWEEN(d1,d2)日期d1和d2