SQL語言基礎(chǔ)資料介紹

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

Query

Language）語言是1974年由Boyce

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

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

13.2.1數(shù)據(jù)查詢基礎(chǔ)基本語法：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)計(jì)（1）無條條件查查詢查詢所所有列列SELECT*FROMemp;查詢指指定列列SELECTdeptno,dnameFROMdept;使用算算術(shù)表表達(dá)式式SELECTempno,sal*0.8FROMemp;使用字字符常常量SELECTempno,'Nameis:',enameFROMemp;使用函函數(shù)SELECTempno,UPPER(ename)FROMemp;改變列列標(biāo)題題SELECTenameemployeename,salsalaryFROMemp;使用連連接字字符串串SELECT'員工號(hào)號(hào)：'||empno||'員工名名'||enameFROMemp;消除重重復(fù)行行SELECTALLdeptnoFROMemp;SELECTDISTINCTdeptnoFROMemp;（2）有條條件查查詢查詢滿滿足條條件的的元組組可以以通過過WHERE子句實(shí)實(shí)現(xiàn)。。WHERE條件中中常用用的運(yùn)運(yùn)算符符號(hào)運(yùn)算符號(hào)謂詞比較大小=,>,<,>=,<=,<>，!=確定范圍BETWEENAND，NOTBETWEENAND確定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重條件AND，OR關(guān)系運(yùn)運(yùn)算SELECTempno,ename,salFROMempWHEREdeptno!=10;SELECTempno,ename,salFROMempWHEREsal>1500確定范圍謂詞BETWEENAND與NOTBETWEENAND。SELECT*FROMempWHEREdeptnoBETWEEN10AND20;SELECT*FROMempWHEREsalNOTBETWEEN1000AND2000;確定集合謂詞IN可以用來查查找屬性值值屬于指定定集合的元元組。SELECTempno,ename,salFROMempWHEREdeptnoIN(10,30);字符匹配%（百分號(hào)））代表任意意長(zhǎng)（長(zhǎng)度度為0）字符串。。_（下劃線））代表任意意單個(gè)字符符。ESCAPE：轉(zhuǎn)義字符符SELECT*FROMempWHEREenameLIKE‘%S%’；SELECT*FROMempWHEREenameLIKE'_A%';SELECT*FROMempWHEREenameLIKE'%x_%'ESCAPE'x';空值操作涉及空值查查詢時(shí)使用用ISNULL或ISNOTNULL，這里的IS不能用=替代。SELECT*FROMempWHEREdeptnoISNULL;SELECT*FROMempWHEREcommISNOTNULL;邏輯操作作用邏輯運(yùn)運(yùn)算符NOT、AND和OR來聯(lián)結(jié)多多個(gè)查詢?cè)儣l件。。優(yōu)先級(jí)：：NOT、AND、OR（用戶可可以用括括號(hào)改變變優(yōu)先級(jí)級(jí)）。IN謂詞實(shí)際際上是多多個(gè)OR運(yùn)算的縮縮寫。SELECT*FROMempWHEREdeptno=10ANDsal>1500;SELECT*FROMempWHERE(deptno=10ORdeptno=20)ANDsal>1500;注意：使用BETWEEN…AND，NOTBETWEEN…AND，IN，NOTIN運(yùn)算符的的查詢條條件都可可以轉(zhuǎn)換換為NOT，AND，OR的邏輯運(yùn)運(yùn)算。例例如，下下面兩個(gè)個(gè)語句是是等價(jià)的的：SELECT*FROMempWHEREsal>1000ANDsal<2000;SELECT*FROMempWHEREsalBETWEEN1000AND2000;升序、降降序排序序ASC:升序（（缺?。籇ESC:降序SELECTempno,ename,salFROMempORDERBYsal;SELECTempno,ename,salFROMempORDERBYsalDESC;多列排序序首先按照照第一個(gè)個(gè)列或表表達(dá)式進(jìn)進(jìn)行排序序；當(dāng)?shù)诘谝粋€(gè)列列或表達(dá)達(dá)式的數(shù)數(shù)據(jù)相同同時(shí)，以以第二個(gè)個(gè)列或表表達(dá)式進(jìn)進(jìn)行排序序，以此此類推。。SELECT*FROMempORDERBYdeptno,salDESC;（3）查詢排排序按表達(dá)式式排序可以按特特定的表表達(dá)式進(jìn)進(jìn)行排序序。SELECTempno,ename,salFROMempORDERBYsal*12;使用別名名排序可以使用用目標(biāo)列列或表達(dá)達(dá)式的別別名進(jìn)行行排序。。SELECTempno,sal*12salaryFROMempORDERBYsalary;使用列位位置編號(hào)號(hào)排序如果列名名或表達(dá)達(dá)式名稱稱很長(zhǎng)，，那么使使用位置置排序可可以縮短短排序語語句的長(zhǎng)長(zhǎng)度。SELECTempno,sal*12salaryFROMempORDERBY2;（4）查詢統(tǒng)統(tǒng)計(jì)函數(shù)格式功能AVGAVG([DISTINCT|ALL]<列名>)計(jì)算一列值的平均值（要求數(shù)值列）COUNTCOUNT([DISTINCT|ALL]*)統(tǒng)計(jì)元組個(gè)數(shù)COUNTCOUNT([DISTINCT|ALL]<列名>)統(tǒng)計(jì)一列中非空值的個(gè)數(shù)MAXMAX([DISTINCT|ALL]<列名>)求一列值中的最大值MINMIN([DISTINCT|ALL]<列名>)求一列值中的最小值SUMSUM([DISTINCT|ALL]<列名>)計(jì)算一列值的總和（要求數(shù)值列）STDDEVSTDDEV(<列名>)..計(jì)算一列值的標(biāo)準(zhǔn)差VARIANCEVARIANCE(<列名>)計(jì)算一列值的方差注意除了COUNT(*)函數(shù)外，，其他的的統(tǒng)計(jì)函函數(shù)都不不考慮返返回值或或表達(dá)式式為NULL的情況。。聚集函數(shù)數(shù)只能出出現(xiàn)在目目標(biāo)列表表達(dá)式、、ORDERBY子句、HAVING子句中，，不能出出現(xiàn)在WHERE子句和GROUPBY子句中。。默認(rèn)對(duì)所所有的返返回行進(jìn)進(jìn)行統(tǒng)計(jì)計(jì)，包括括重復(fù)的的行；如如果要統(tǒng)統(tǒng)計(jì)不重重復(fù)的行行信息，，則可以以使用DISTINCT選項(xiàng)。如果對(duì)查查詢結(jié)果果進(jìn)行了了分組，，則聚集集函數(shù)的的作用范范圍為各各個(gè)組，，否則聚聚集函數(shù)數(shù)作用于于整個(gè)查查詢結(jié)果果。SELECTcount(*),avg(sal),max(sal),min(sal)FROMempWHEREdeptno=10;SELECTavg(comm),sum(comm)FROMemp;SELECTcount(DISTINCTdeptno)FROMemp;SELECTvariance(sal),stddev(sal)FROMemp;分組查詢?cè)兓菊Z法法單列分組組查詢多列分組組查詢使用HAVING子句限制制返回組組使用ROLLUP和CUBE合并分組組查詢SELECTcolumn,group_function,…FROMtable[WHEREcondition][GROUP[BYROOLUP|CUBE|GROUPINGSETS]group_by_expression][HAVINGgroup_condition][ORDERBYcolumn[ASC|DESC]];（1）基本語語法注意：GROUPBY子句用于于指定分分組列或或分組表表達(dá)式。。集合函數(shù)數(shù)用于對(duì)對(duì)分組進(jìn)進(jìn)行統(tǒng)計(jì)計(jì)。如果果未對(duì)查查詢分組組，則集集合函數(shù)數(shù)將作用用于整個(gè)個(gè)查詢結(jié)結(jié)果；如如果對(duì)查查詢結(jié)果果分組，，則集合合函數(shù)將將作用于于每一個(gè)個(gè)組，即即每一個(gè)個(gè)分組都都有一個(gè)個(gè)集合函函數(shù)。HAVING子句用于于限制分分組的返返回結(jié)果果。WHERE子句對(duì)表表中的記記錄進(jìn)行行過濾，，而HAVING子句對(duì)分分組后形形成的組組進(jìn)行過過濾。在分組查查詢中，，SELECT子句后面面的所有有目標(biāo)列列或目標(biāo)標(biāo)表達(dá)式式要么是是分組列列，要么么是分組組表達(dá)式式，要么么是集合合函數(shù)。。單列分組組查詢將查詢出出來的記記錄按照照某一個(gè)個(gè)指定的的列進(jìn)行行分組SELECTdeptno,count(*),avg(sal)FROMempGROUPBYdeptno;多列分組組查詢?cè)贕ROUPBY子句中指指定了兩兩個(gè)或多多個(gè)分組組列SELECTdeptno,job,count(*),avg(sal)FROMempGROUPBYdeptno,job;使用HAVING子句限制制返回組組可以使用用HAVING子句，只只有滿足足條件的的組才會(huì)會(huì)返回。。SELECTdeptno,count(*),avg(sal)FROMempGROUPBYdeptnoHAVINGavg(sal)>1500;使用ROLLUP和CUBE如果在GROUPBY子句中使使用ROLLUP選項(xiàng)，則則還可以以生成橫橫向統(tǒng)計(jì)計(jì)和不分分組統(tǒng)計(jì)計(jì)；如果在GROUPBY子句中使使用CUBE選項(xiàng)，則則還可以以生成橫橫向統(tǒng)計(jì)計(jì)、縱向向統(tǒng)計(jì)和和不分組組統(tǒng)計(jì)。。SELECTdeptno,job,avg(sal)FROMempGROUPBYROLLUP(deptno,job);SELECTdeptno,job,avg(sal)FROMempGROUPBYCUBE(deptno,job);合并分組組查詢使用GROUPINGSETS可以將幾幾個(gè)單獨(dú)獨(dú)的分組組查詢合合并成一一個(gè)分組組查詢SELECTdeptno,job,avg(sal)FROMempGROUPBYGROUPINGSETS(deptno,job);連接查詢?cè)兘徊孢B接接內(nèi)連接等值連接接不等值連連接自身連接接外連接左外連接接右外連接接全外連接接（1）交叉連連接概念兩個(gè)或多多個(gè)表之之間的無無條件連連接。一一個(gè)表中中所有記記錄分別別與其他他表中所所有記錄錄進(jìn)行連連接。如如果進(jìn)行行連接的的表中分分別有n1，n2，n3…條記錄，，那么交交叉連接接的結(jié)果果集中將將有n1×n2×n3×…條記錄。以下情況可以以出現(xiàn)交叉連連接連接條件省略略連接條件非法法一個(gè)表的所有有行被連接到到另一個(gè)表的的所有行示例SELECTename,dnamefromemp,dept;（2）內(nèi)連接執(zhí)行過程內(nèi)連接語法等值內(nèi)連接非等值內(nèi)連接接自身內(nèi)連接執(zhí)行過程首先在表1中找到第一個(gè)個(gè)元組，然后后從頭開始掃掃描表2，逐一查找滿滿足連接條件件的元組，找找到后就將表表1中的第1個(gè)元組與該元元組拼接形成成結(jié)果表中的的一個(gè)元組。。表2全部找完后，，再找表1中的第2個(gè)元組，然后后再從頭掃描描表2，逐一查找滿滿足連接條件件的元組，找找到后就將表表1中的第2個(gè)元組與該元元組拼接形成成結(jié)果表中的的一個(gè)元組。。重復(fù)執(zhí)行，，直到表1中的全部元組組都處理完畢畢為止。內(nèi)連接語法：：標(biāo)準(zhǔn)SQL語句的連接方方式SELECTtable1.column,talbe2.column[,…]FROMtable1[INNER]JOINtable2[JOIN…]ONcondition;內(nèi)連接語法：：Oracle擴(kuò)展的連接方方式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）外連接左外連接右外連接全外連接左外連接語法法：標(biāo)準(zhǔn)SQL語句的連接方方式SELECTtable1.column,table2.column[,…]FROMtable1LEFTJOINtable2[,]ONtable1.column<operator>table2.column[,…];左外連接語法法：Oracle擴(kuò)展的連接方方式SELECTtable1.column,table2.column[,…]FROMtable1,table2[,…]WHEREtable1.column<operator>table2.column(+)[…];查詢10號(hào)部門的部門門名、員工號(hào)號(hào)、員工名和和所有其他部部門的名稱，，語句為SELECTdname,empno,enameFROMdeptLEFTJOINempONdept.deptno=emp.deptnoANDdept.deptno=10;或SELECTdname,empno,enameFROMdept,empWHEREdept.deptno=emp.deptno(+)ANDemp.deptno(+)=10;右外連接語法法：標(biāo)準(zhǔn)SQL語句的連接方方式SELECTtable1.column,table2.column[,…]FROMtable1RIGHTJOINtable2[,…]ONtable1.column<operator>table2.column[…];右外連接語法法：Oracle擴(kuò)展的連接方方式SELECTtable1.column,table2.column[,…]FROMtable1,table2[,…]WHEREtable1.column(+)<operator>table2.column[…];查詢20號(hào)部門的部部門名稱及及其員工號(hào)號(hào)、員工名名，和所有有其他部門門的員工名名、員工號(hào)號(hào)，語句為為SELECTempno,ename,dnameFROMdeptRIGHTJOINempONdept.deptno=emp.deptnoANDdept.deptno=20;或SELECTempno,ename,dnameFROMdept,empWHEREdept.deptno(+)=emp.deptnoAND`dept.deptno(+)=20;全外連接是是指在內(nèi)連連接的基礎(chǔ)礎(chǔ)上，將連連接操作符符兩側(cè)表中中不符合連連接條件的的記錄加入入結(jié)果集中中。在Oracle數(shù)據(jù)庫中，，全外連接接的表示方方式為SELECTtable1.column,table2.column[,…]FROMtable1FULLJOINtable2[,…]ONtable1.column1=table2.column2[…];查詢所有的的部門名和和員工名，，語句為SELECTdname,enameFROMempFULLJOINdeptONemp.deptno=dept.deptno;子查詢子查詢概述述單行單列子子查詢多行單列子子查詢單行多列子子查詢多行多列子子查詢相關(guān)子查詢?cè)冊(cè)贔ROM子句中使用用子查詢?cè)贒DL語句中使用用子查詢使用WITH子句的子查查詢子查詢的概概念子查詢是指指嵌套在其其他SQL語句中的SELECT語句，也稱稱為嵌套查查詢。在執(zhí)行時(shí)，，由里向外外，先處理理子查詢，，再將子查查詢的返回回結(jié)果用于于其父語句句（外部語語句）的執(zhí)執(zhí)行。子查詢作用用在INSERT或CREATETABLE語句中使用用子查詢，，可以將子子查詢的結(jié)結(jié)果寫入到到目標(biāo)表中中；在UPDATE語句中使用用子查詢可可以修改一一個(gè)或多個(gè)個(gè)記錄的數(shù)數(shù)據(jù)；在DELETE語句中使用用子查詢可可以刪除一一個(gè)或多個(gè)個(gè)記錄在WHERE和HAVING子句句中中使使用用子子查查詢?cè)兛煽梢砸苑捣祷鼗氐牡囊灰粋€(gè)個(gè)或或多多個(gè)個(gè)值值。。（1）子子查查詢?cè)兏鸥攀鍪鰡涡行袉螁瘟辛凶幼硬椴樵冊(cè)兪鞘侵钢缸幼硬椴樵冊(cè)冎恢环捣祷鼗匾灰恍行袛?shù)數(shù)據(jù)據(jù)，，而而且且只只返返回回一一列列的的數(shù)數(shù)據(jù)據(jù)。。運(yùn)算算符符=，>，<，>=，<=，!=查詢?cè)儽缺?934號(hào)員員工工工工資資高高的的員員工工的的員員工工號(hào)號(hào)、、員員工工名名、、員員工工工工資資信信息息，，語語句句為為SELECTempno,ename,salFROMempWHEREsal>(SELECTsalFROMempWHEREempno=7934);（2）單單行行單單列列子子查查詢?cè)儯?）多多行行單單列列子子查查詢?cè)兌嘈行袉螁瘟辛凶幼硬椴樵冊(cè)兪鞘侵钢阜捣祷鼗囟喽嘈行袛?shù)數(shù)據(jù)據(jù)，，且且只只返返回回一一列列的的數(shù)數(shù)據(jù)據(jù)。。運(yùn)算算符符號(hào)號(hào)運(yùn)算符含義IN與子查詢返回結(jié)果中任何一個(gè)值相等NOTIN與子查詢返回結(jié)果中任何一個(gè)值都不等>ANY比子查詢返回結(jié)果中某一個(gè)值大=ANY與子查詢返回結(jié)果中某一個(gè)值相等<ANY比子查詢返回結(jié)果中某一個(gè)值小>ALL比子查詢返回結(jié)果中所有值都大<ALL比子查詢返回結(jié)果中任何一個(gè)值都小EXISTS子查詢至少返回一行時(shí)條件為TRUENOTEXISTS子查詢不返回任何一行時(shí)條件為TRUE查詢?cè)兣c與10號(hào)部部門門某某個(gè)個(gè)員員工工工工資資相相等等的的員員工工信信息息。。SELECTempno,ename,salFROMempWHEREsalIN(SELECTsalFROMempWHEREdeptno=10);查詢?cè)儽缺?0號(hào)部門某某個(gè)員工工工資高高的員工工信息。。SELECTempno,ename,salFROMempWHEREsal>ANY(SELECTsalFROMempWHEREdeptno=10);查詢比10號(hào)部門所所有員工工工資高高的員工工信息。。SELECTempno,ename,salFROMempWHEREsal>ALL(SELECTsalFROMempWHEREdeptno=10);（4）單行多多列子查查詢單行多列列子查詢?cè)兪侵缸幼硬樵兎捣祷匾恍行袛?shù)據(jù)，，但是包包含多列列數(shù)據(jù)。。多列數(shù)據(jù)據(jù)進(jìn)行比比較時(shí)，，可以成成對(duì)比較較，也可可以非成成對(duì)比較較。成對(duì)對(duì)比較要要求多個(gè)個(gè)列的數(shù)數(shù)據(jù)必須須同時(shí)匹匹配，而而非成對(duì)對(duì)比較則則不要求求多個(gè)列列的數(shù)據(jù)據(jù)同時(shí)匹匹配。查詢與7844號(hào)員工的的工資、、工種都都相同的的員工的的信息。。SELECTempno,ename,sal,jobFROMempWHERE(sal,job)=(SELECTsal,jobFROMempWHEREempno=7844);查詢與10號(hào)部門某某個(gè)員工工工資相相同，工工種也與與10號(hào)部門的的某個(gè)員員工相同同的員工工的信息息。SELECTempno,ename,sal,jobFROMempWHEREsalIN(SELECTsalFROMempWHEREdeptno=10)ANDjobIN(SELECTjobFROMempWHEREdeptno=10);（5）多行多多列子查查詢多行多列列子查詢?cè)兪侵缸幼硬樵兎捣祷囟嘈行袛?shù)據(jù)，，并且是是多列數(shù)數(shù)據(jù)。例如，查查詢與10號(hào)部門某某個(gè)員工工的工資資和工種種都相同同的員工工的信息息，語句句為SELECTempno,ename,sal,jobFROMempWHERE(sal,job)IN(SELECTsal,jobFROMempWHEREdeptno=10);（6）相關(guān)子子查詢子查詢?cè)冊(cè)趫?zhí)執(zhí)行時(shí)時(shí)并不不需要要外部部父查查詢的的信息息，這這種子子查詢?cè)兎Q為為無關(guān)關(guān)子查查詢。。如果子子查詢?cè)冊(cè)趫?zhí)執(zhí)行時(shí)時(shí)需要要引用用外部部父查查詢的的信息息，那那么這這種子子查詢?cè)兙头Q稱為相相關(guān)子子查詢?cè)?。在相關(guān)關(guān)子查查詢中中經(jīng)常常使用用EXISTS或NOTEXISTS謂詞來來實(shí)現(xiàn)現(xiàn)。如如果子子查詢?cè)兎祷鼗亟Y(jié)果果，則則條件件為TRUE，如果果子查查詢沒沒有返返回結(jié)結(jié)果，，則條條件為為FALSE。查詢沒沒有任任何員員工的的部門門號(hào)、、部門門名。。SELECTdeptno,dname,locFROMdeptWHERENOTEXISTS(SELECT*FROMempWHEREemp.deptno=dept.deptno);查詢比比本部部門平平均工工資高高的員員工信信息。。SELECTempno,ename,salFROMempeWHEREsal>(SELECTavg(sal)FROMempWHEREdeptno=e.deptno);（7）在FROM子句中中使用用子查查詢當(dāng)在FROM子句中中使用用子查查詢時(shí)時(shí)，該該子查查詢被被作為為視圖圖對(duì)待待，必必須為為該子子查詢?cè)冎付ǘ▌e名名。查詢各各個(gè)員員工的的員工工號(hào)、、員工工名及及其所所在部部門平平均工工資。。SELECTempno,ename,d.avgsalFROMemp,(SELECTdeptno,avg(sal)avgsalFROMempGROUPBYdeptno)dWHEREemp.deptno=d.deptno;查詢各個(gè)部部門號(hào)、部部門名、部部門人數(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子句的子查詢?cè)內(nèi)绻谝粋€(gè)SQL語句中多次使使用同一個(gè)子子查詢，可以以通過WITH子句給子查詢?cè)冎付ㄒ粋€(gè)名名字，從而可可以實(shí)現(xiàn)通過過名字引用該該子查詢，而而不必每次都都完整寫出該該子查詢。查詢?nèi)藬?shù)最多多的部門的信信息。SELECT*FROMdeptWHEREdeptnoIN(SELECTdeptnoFROMempGROUPBYdeptnoHAVINGcount(*)>=ALL(SELECTcount(*)FROMempGROUPBYdeptno));相同的子查詢?cè)冞B續(xù)出現(xiàn)了了兩次，因此此可以按下列列方式編寫查查詢語句。WITHdeptinfoAS(SELECTdeptno,count(*)numFROMempGROUPBYdeptno)SELECT*FROMdeptWHEREdeptnoIN(SELECTdeptnoFROMdeptinfoWHEREnum=(SELECTmax(num)FROMdeptinfo));合并查詢語法UNIONINTERSECTMINUS語法：SELECTquery_statement1[UNION|UNIONALL|INTERSECT|MINUS]SELECTquery_statement2;注意：當(dāng)要合并幾個(gè)個(gè)查詢的結(jié)果果集時(shí)，這幾幾個(gè)查詢的結(jié)結(jié)果集必須具具有相同的列列數(shù)與數(shù)據(jù)類類型。如果要對(duì)最終終的結(jié)果集排排序，只能在在最后一個(gè)查查詢之后用ORDERBY子句指明排序序列。（1）UNIONUNION運(yùn)算符用于獲獲取幾個(gè)查詢?cè)兘Y(jié)果集的并并集，將重復(fù)復(fù)的記錄只保保留一個(gè)，并并且默認(rèn)按第第一列進(jìn)行排排序。查詢10號(hào)部門的員工工號(hào)、員工名名、工資和部部門號(hào)以及工工資大于2000的所有員工的的員工號(hào)、員員工名、工資資和部門號(hào)，，語句為SELECTempno,ename,sal,deptnoFROMempWHEREdeptno=10UNIONSELECTempno,ename,sal,deptnoFROMempWHEREsal>2000ORDERBYdeptno;如果要保留所所有的重復(fù)記記錄，則需要要使用UNIONALL運(yùn)算符。SELECTempno,ename,sal,deptnoFROMempWHEREdeptno=10UNIONALLSELECTempno,ename,sal,deptnoFROMempWHEREsal>2000ORDERBYdeptno;（2）INTERSECTINTERSECT用于獲取幾個(gè)個(gè)查詢結(jié)果集集的交集，只只返回同時(shí)存存在于幾個(gè)查查詢結(jié)果集中中的記錄。同同時(shí)，返回的的最終結(jié)果集集默認(rèn)按第一一列進(jìn)行排序序。查詢30號(hào)部門中工資資大于2000的員工號(hào)、員員工名、工資資和部門號(hào)，，語句為：SELECTempno,ename,sal,deptnoFROMempWHEREdeptno=30INTERSECTSELECTempno,ename,sal,deptnoFROMEMPWHEREsal>2000;（3）MINUSMINUS用于獲取幾個(gè)個(gè)查詢結(jié)果集集的差集，即即返回在第一一個(gè)結(jié)果集中中存在，而在在第二個(gè)結(jié)果果集中不存在在的記錄。同同時(shí)，返回的的最終結(jié)果集集默認(rèn)按第一一列進(jìn)行排序序。查詢30號(hào)部門中工種種不是“SALESMAN”的員工號(hào)、員員工名和工種種名稱，語句句為：SELECTempno,ename,jobFROMempWHEREdeptno=30MINUSSELECTempno,ename,jobFROMEMPWHEREjob='SALESMAN';13.3數(shù)據(jù)操縱插入數(shù)據(jù)修改數(shù)據(jù)MERGE語句刪除數(shù)據(jù)插入數(shù)據(jù)插入單行記錄利用子查詢插插入數(shù)據(jù)向多個(gè)表中插插入數(shù)據(jù)（1）插入單行記錄語法INSERTINTOtable_name|view_name[(column1[,column2…])]VALUES(value1[,values,…])注意如果在INTO子句中沒有指指明任何列名名，則VALUES子句中列值的的個(gè)數(shù)、順序序、類型必須須與表中列的的個(gè)數(shù)、順序序、類型相匹匹配。如果在INTO子句中指定了了列名，則VALUES子句中提供的的列值的個(gè)數(shù)數(shù)、順序、類類型必須與指指定列的個(gè)數(shù)數(shù)、順序、類類型按位置對(duì)對(duì)應(yīng)。向表或視圖中中插入的數(shù)據(jù)據(jù)必須滿足表表的完整性約約束。字符型和日期期型數(shù)據(jù)在插插入時(shí)要加單單引號(hào)。日期期類型數(shù)據(jù)需需要按系統(tǒng)默默認(rèn)格式輸入入，或使用TO_DATE函數(shù)進(jìn)行日期期轉(zhuǎn)換。向dept表中插入一行行記錄。INSERTINTOdeptVALUES(50,'IM','dalian');向emp表中插入一行行記錄。INSERTINTOemp(empno,ename,sal,hiredate)VALUES(1234,'JOAN',2500,'20-4月-2007');（2）利用子查詢?cè)儾迦霐?shù)據(jù)語法INSERTINTOtable_name|view_name[(column1[,column2,…])subquery;注意INTO子句中指定的的列的個(gè)數(shù)、、順序、類型型必須與子查查詢中列的個(gè)個(gè)數(shù)、順序和和類型相匹配配。統(tǒng)計(jì)各個(gè)部門門的部門號(hào)、、部門最高工工資和最低工工資，并將統(tǒng)統(tǒng)計(jì)的結(jié)果寫寫入到表emp_salary（假設(shè)該表已已經(jīng)創(chuàng)建）中中。INSERTINTOemp_salarySELECTdeptno,max(sal),min(sal)FROMempGROUPBYdeptno;向emp表中插入一行行記錄，其員員工名為FAN，員工號(hào)為1235，其他信息與與員工名為SCOTT的員工信息相相同。INSERTINTOempSELECT1235,'FAN',job,mgr,hiredate,sal,comm,deptnoFROMempWHEREename='SCOTT';如果要將大量量數(shù)據(jù)插入表表中，可以利利用子查詢直直接裝載的方方式進(jìn)行。由由于直接裝載載數(shù)據(jù)的操作作過程不寫入入日志文件，，因此數(shù)據(jù)插插入操作的速速度大大提高高。利用子查詢裝裝載數(shù)據(jù)語法法為：INSERT/*+APPEND*/INTOtable_name|view_name[(column1[,column2,…])subquery;復(fù)制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í)時(shí)完成數(shù)據(jù)的的插入與更新新操作。將源表的數(shù)據(jù)據(jù)分別與目標(biāo)標(biāo)表中的數(shù)據(jù)據(jù)根據(jù)特性條條件進(jìn)行比較較（每次只比比較一條記錄錄），如果匹匹配，則利用用源表中的記記錄更新目標(biāo)標(biāo)表中的記錄錄，如果不匹匹配，則將源源表中的記錄錄插入目標(biāo)表表中。使用MERGE語句操作時(shí)，，用戶需要具具有源表的SELECT對(duì)象權(quán)限以及及目標(biāo)表的INSERT，UPDATE對(duì)象權(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：指定進(jìn)行數(shù)數(shù)據(jù)更新或插插入的目標(biāo)表表；USING：指定用于目目標(biāo)表數(shù)據(jù)更更新或插入的的源表或視圖圖或子查詢；；ON：決定MERGE語句執(zhí)行行更新操操作還是是插入操操作的條條件。對(duì)對(duì)于目標(biāo)標(biāo)表中滿滿足條件件的記錄錄，則利利用源表表中的相相應(yīng)記錄錄進(jìn)行更更新；而而源表中中不滿條條件的記記錄將被被插入目目標(biāo)表中中；where_clause：只有當(dāng)當(dāng)該條件件為真時(shí)時(shí)才進(jìn)行行數(shù)據(jù)的的更新或或插入操操作；DELETEwhere_clause：當(dāng)目標(biāo)標(biāo)表中更更新后的的記錄滿滿足該條條件時(shí)，，則刪除除該記錄錄。使用“雇員”表（別名名e）作為數(shù)數(shù)據(jù)源，，在該表表的副副本“副本_雇員”表（別名名c）中插入入并更新新行。MERGEINTO副本_雇員cUSING雇員eON(c.雇員標(biāo)識(shí)識(shí)=e.雇員標(biāo)識(shí)識(shí))WHENMATCHEDTHENUPDATESETc.姓氏=e.姓氏,c.部門標(biāo)識(shí)識(shí)=e.部門標(biāo)識(shí)識(shí)WHENNOTMATCHEDTHENINSERTVALUES(e.雇員標(biāo)識(shí)識(shí),e.姓氏,e.部門標(biāo)識(shí)識(shí));MERGEINTO副本_雇員cUSING雇員eON(c.雇員標(biāo)識(shí)識(shí)=e.雇員標(biāo)識(shí)識(shí))WHENMATCHEDTHENUPDATESETc.姓氏=e.姓氏,c.部門標(biāo)識(shí)識(shí)=e.部門標(biāo)識(shí)識(shí)WHENNOTMATCHEDTHENINSERTVALUES(e.雇員標(biāo)識(shí)識(shí),e.姓氏,e.部門標(biāo)識(shí)識(shí));MERGE語句執(zhí)行行前的“副本_雇員”雇員標(biāo)識(shí)識(shí)姓氏氏 部門門標(biāo)識(shí)100Smith40103Chang30MERGE執(zhí)行后的的“副本_雇員”雇員標(biāo)識(shí)識(shí)姓氏氏 部門門標(biāo)識(shí)100King90103Hunold 60152Davies 50雇員（來來源表））雇員標(biāo)識(shí)識(shí)姓氏氏 部門門標(biāo)識(shí)100King90103Hunold 60152Davies 50現(xiàn)有表source_emp和target_emp，表中數(shù)據(jù)如如下。利用source_emp表中的數(shù)據(jù)更更新target_emp表中的數(shù)據(jù)，，對(duì)target_emp表中存存在的的員工工信息息進(jìn)行行更新新，對(duì)對(duì)不存存在的的員工工進(jìn)行行信息息插入入。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ū)別釋放存儲(chǔ)儲(chǔ)空間不寫入日日志文件件，因此此執(zhí)行效效率較高高，但該該操作不不可回滾滾。13.4事務(wù)處理理事務(wù)概述述Oracle事務(wù)處理理事務(wù)概述述原子性（（Atomicity）：事務(wù)務(wù)是數(shù)據(jù)據(jù)庫的邏邏輯工作作單位，，事務(wù)中中的所有有操作要要么都做做，要么么都不做做，不存存在第三三種情況況。一致性（（Consistency）：事務(wù)務(wù)執(zhí)行的的結(jié)果必必須是使使數(shù)據(jù)庫庫從一個(gè)個(gè)一致性性狀態(tài)轉(zhuǎn)轉(zhuǎn)變到另另一個(gè)一一致性狀狀態(tài)，不不存在中中間的狀狀態(tài)。隔離性（（Isolation）：數(shù)據(jù)據(jù)庫中一一個(gè)事務(wù)務(wù)的執(zhí)行行不受其其他事務(wù)務(wù)干擾，，每個(gè)事事務(wù)都感感覺不到到還有其其他事務(wù)務(wù)在并發(fā)發(fā)執(zhí)行。。持久性（（Durability）：一個(gè)個(gè)事務(wù)一一旦提交交，則對(duì)對(duì)數(shù)據(jù)庫庫中數(shù)據(jù)據(jù)的改變變是永久久性的，，以后的的操作或或故障不不會(huì)對(duì)事事務(wù)的操操作結(jié)果果產(chǎn)生任任何影響響。事務(wù)處理理事務(wù)提交交方式用戶顯式式執(zhí)行COMMIT命令執(zhí)行特定定操作時(shí)時(shí)系統(tǒng)自自動(dòng)提交交。說明當(dāng)事務(wù)提提交后，，用戶對(duì)對(duì)數(shù)據(jù)庫庫修改操操作的日日志信息息由日志志緩沖區(qū)區(qū)寫入重重做日志志文件中中，釋放放該事務(wù)務(wù)所占據(jù)據(jù)的系統(tǒng)統(tǒng)資源和和數(shù)據(jù)庫庫資源。。此時(shí)，，其他會(huì)會(huì)話可以以看到該該事務(wù)對(duì)對(duì)數(shù)據(jù)庫庫的修改改結(jié)果。。當(dāng)執(zhí)行CREATE，ALTER，DROP，RENAME，REVOKE，GRANT，CONNECT，DISCONNECT等命令時(shí)時(shí)，系統(tǒng)統(tǒng)將自動(dòng)動(dòng)提交。。事務(wù)回滾滾方式事務(wù)全部部回滾ROLLBACK事務(wù)部分分回滾SAVEPOINTXROLLBACKTOXROLLBACKTOB;（回滾最最后一個(gè)個(gè)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ù)的不不同，可可以分為為：?jiǎn)涡泻瘮?shù)數(shù)多行函數(shù)數(shù)根據(jù)參數(shù)數(shù)類型不不同，可可以分為為：數(shù)值函數(shù)數(shù)字符函數(shù)數(shù)日期函數(shù)數(shù)轉(zhuǎn)換函數(shù)數(shù)聚集函數(shù)數(shù)數(shù)值函數(shù)數(shù)函數(shù)返回值A(chǔ)BS(n)返回n的絕對(duì)值CEIL(n)返回大于或等于n的最小整數(shù)EXP(n)返回e的n次冪FLOOR(n)返回小于或等于n的最大整數(shù)LN(n)返回以E為底的n的對(duì)數(shù)LOG(m,n)返回以m為底的n的對(duì)數(shù)MOD(m,n)返回m除以n的余數(shù)POWER(m,n)返回m的n次方ROUND(m[,n])對(duì)m進(jìn)行四舍五入（n大于0時(shí)，將m四舍五入到小數(shù)點(diǎn)右邊n位，n等于零時(shí)，表示對(duì)m進(jìn)行取整，n小于0時(shí)，則小數(shù)點(diǎn)左邊的數(shù)字位置被圓整。SIGN(n)判斷n的正負(fù)（n大于0返回，n等于0返回0，n小于0返回-1SQRT(n)返回n的平方根SQUARE(n)返回n的平方TRUNC(m[,n])對(duì)m進(jìn)行截?cái)嗖僮鳎╪截?cái)嗟叫?shù)點(diǎn)后第n位，如果n未給出，則系統(tǒng)默認(rèn)為0,n也可以為負(fù)數(shù)，表示小數(shù)點(diǎn)左邊的數(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ù)返回值A(chǔ)SCII(char)字符串首字符的ASC碼值CHR(n)ASC碼值為n的字符CONCAT把兩個(gè)列值拼接起來；“||”操作符更通用INITCAP(char)將字符串中每個(gè)單詞的首字母大寫INSTR（char1,char2,a,b)INSTRB（char1,char2,a,b)返回指定字符的位置LENGTH(char)LENGTHB(char)計(jì)算字符串的長(zhǎng)度SUBSTR(char,m[,n])求子串。column中從起始位置m開始長(zhǎng)度為n的子串Replace(str1,str2,str3)LOWER(char)、UPPER(char)將字符串中所有的大寫字母變?yōu)樾?大)寫LPAD(char1,n[,char2])從左側(cè)用字符串char2補(bǔ)齊字符串char1至長(zhǎng)度n（右對(duì)齊）RPAD(char1,n[,char2])從右側(cè)用char2補(bǔ)齊char1至長(zhǎng)度n.LTRIM(char[,SET])把char中最左側(cè)的若干個(gè)字符去掉，以使其首字符不在SET中RTRIM(char[,SET])把char中最右側(cè)的若干個(gè)字符去掉，以使其尾字符不在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ù)是是指對(duì)日期期進(jìn)行處理理的函數(shù)，，函數(shù)輸入入為DATE或TIMESTAMP類型的數(shù)據(jù)據(jù)，輸出為為DATE類型的數(shù)據(jù)據(jù)（除MONTH_BETWEEN函數(shù)返回整整數(shù)以外））。Oracle數(shù)據(jù)庫中日日期的默認(rèn)認(rèn)格式為DD-MON-YY?？梢酝ㄟ^過設(shè)置NLS_DATE_FORMAT參數(shù)設(shè)置當(dāng)當(dāng)前會(huì)話的的日期格式式，通過NLS_LANGUAGE參數(shù)設(shè)置表表示日期的的字符集。。例如：ALTERSESSIONSETNLS_DATE_FORMAT='YYYY-MM-DDHH24:MI:SS';ALTERSESSIONSETNLS_LANGUAGE='AMERICAN';函數(shù)返回值A(chǔ)DD_MONTHS(d,n)返回日期d添加n個(gè)月的日期CURRENT_DATE返回系統(tǒng)當(dāng)前日期和時(shí)間CURRENT_TIMESTAMP[(p)]返回系統(tǒng)當(dāng)前時(shí)間戳EXTRACT(departFROMd)返回d中depart對(duì)應(yīng)部分的內(nèi)容

LAST_DAY(d)返回d所在月份最后一天的日期LOCALTIMESTAMP(P)返回本地時(shí)間戳LOCALTIMESTAMP[(p)]返回當(dāng)前會(huì)話時(shí)區(qū)所對(duì)應(yīng)的日期時(shí)間MONTHS_BETWEEN(d1,d2)日期d1和d2