Oracle第13章SQL語言基礎(chǔ)

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

2、ryLanguage）語言是1974年由Boyce和Chamberlin提出的。 pSQL語言是關(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)對數(shù)據(jù)庫的訪問和操作。 Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程13.1.2 SQL語言的分類語言的分類p據(jù)定義語言（Data Definition Language，DDL）：用于定義、修改、刪除數(shù)據(jù)庫對象，包括CREATE，ALTER，DROP，GRANT，REVOKE，AUDIT和NOAUDIT等。p數(shù)據(jù)操縱語言（Data Manipulati

3、on Language，DML）：用于改變數(shù)據(jù)庫中的數(shù)據(jù)，包括數(shù)據(jù)插入（INSERT）、數(shù)據(jù)修改（UPDATE）和數(shù)據(jù)刪除（DELETE）。p數(shù)據(jù)查詢語言（Data Query Language，DQL）：用于數(shù)據(jù)檢索，包括SELECT。p事務(wù)控制（Transaction Control）：用于將一組DML操作組合起來，形成一個事務(wù)并進(jìn)行事務(wù)控制。包括事務(wù)提交（COMMIT）、事務(wù)回滾（ROLLBACK）、設(shè)置保存點(diǎn)（SAVEPOINT）和設(shè)置事務(wù)狀態(tài)（SET TRANSACTION）。p系統(tǒng)控制（System Control）：用于設(shè)置數(shù)據(jù)庫系統(tǒng)參數(shù)，包括ALTER SYSTEM。p會話控制

4、（Session Control）：用于設(shè)置用戶會話相關(guān)參數(shù)，包括ALTER SESSION。 Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程13.1.3 SQL語言的特點(diǎn)語言的特點(diǎn)p功能一體化：幾乎涵蓋了對數(shù)據(jù)庫的所有操作，語言風(fēng)格統(tǒng)一。p高度的非過程化：在使用SQL語言操作數(shù)據(jù)庫時，用戶只需要說明“做什么”，而不需要說明“怎樣做”。用戶任務(wù)的實(shí)現(xiàn)對用戶而言是透明的，由系統(tǒng)自動完成。這大大減輕了用戶的負(fù)擔(dān)，同時降低了對用戶的技術(shù)要求。p面向集合的操作方式：SQL語言采用集合操作方式，不僅查詢結(jié)果可以是多條記錄的集合，而且一次插入、刪除、修改操作的對象也可以是多條記錄的集合。面向集合的操作方式極大地提高了

5、對數(shù)據(jù)操作效率。p多種使用方式：SQL語句既是自含式語言，又是嵌入式語言。SQL語言可以直接以命令方式與數(shù)據(jù)庫進(jìn)行交互，也可以嵌入到其他的高級語言中使用。p簡潔、易學(xué)：SQL語言命令數(shù)量有限，語法簡單，接近于自然語言（英語），因此容易學(xué)習(xí)和掌握。 Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程13.2 數(shù)據(jù)查詢數(shù)據(jù)查詢p數(shù)據(jù)查詢基礎(chǔ)p基本查詢p分組查詢p連接查詢p子查詢p合并查詢Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程 13.2.1 數(shù)據(jù)查詢基礎(chǔ)數(shù)據(jù)查詢基礎(chǔ)p基本語法：nSELECT ALL|DISTINCTcolumn_name,expressionnFROM table1_name,table2_name,

6、view_name,nWHERE conditionnGROUP BY column_name1,column_name2, nHAVING group_conditionnORDER BY column_name2 ASC|DESCn,column_name2,; Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程13.2.2 基本查詢基本查詢p無條件查詢p有條件查詢p查詢排序p查詢統(tǒng)計(jì)Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程 （1）無條件查詢）無條件查詢p查詢所有列nSELECT * FROM emp;p查詢指定列nSELECT deptno,dname FROM dept; p使用算術(shù)表達(dá)式nSELECT e

7、mpno,sal*0.8 FROM emp;p使用字符常量nSELECT empno, Name is: , ename FROM emp; p使用函數(shù)nSELECT empno,UPPER(ename) FROM emp; Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p改變列標(biāo)題nSELECT ename employeename,sal salary FROM emp;p使用連接字符串nSELECT 員工號：員工號：|empno|員工名員工名|ename FROM emp;p消除重復(fù)行nSELECT ALL deptno FROM emp; nSELECT DISTINCT deptno FROM

8、emp; Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程（2）有條件查詢）有條件查詢p查詢滿足條件的元組可以通過WHERE 子句實(shí)現(xiàn)。pWHERE條件中常用的運(yùn)算符號運(yùn)算符號謂詞比較大小=, , =, =, ，!=確定范圍BETWEEN AND，NOT BETWEEN AND確定集合IN，NOT IN字符匹配LIKE，NOT LIKE空值IS NULL，IS NOT NULL多重條件AND，OROracle 10g數(shù)據(jù)庫基礎(chǔ)教程p關(guān)系運(yùn)算 nSELECT empno,ename,sal FROM emp WHERE deptno! = 10;nSELECT empno,ename,sal FROM emp

9、 WHERE sal1500p確定范圍n謂詞謂詞BETWEEN AND與與NOT BETWEEN AND。n SELECT * FROM emp WHERE deptno BETWEEN 10 AND 20;nSELECT * FROM emp WHERE sal NOT BETWEEN 1000 AND 2000;Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p確定集合n謂詞謂詞IN可以用來查找屬性值屬于指定集合的元組?？梢杂脕聿檎覍傩灾祵儆谥付系脑M。nSELECT empno,ename,sal FROM emp WHERE deptno IN(10,30); p字符匹配n%（百分號）代表任意長

10、（長度為（百分號）代表任意長（長度為0）字符串。）字符串。n_（下劃線）代表任意單個字符。（下劃線）代表任意單個字符。nESCAPE：轉(zhuǎn)義字符：轉(zhuǎn)義字符nSELECT * FROM empWHERE ename LIKE %S%； nSELECT * FROM emp WHERE ename LIKE _A%; nSELECT * FROM emp WHERE ename LIKE %x_% ESCAPE x; Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p空值操作n涉及空值查詢時使用涉及空值查詢時使用IS NULL或或 IS NOT NULL，這里的這里的IS不能用不能用=替代。替代。nSELECT

11、* FROM emp WHERE deptno IS NULL;nSELECT * FROM emp WHERE comm IS NOT NULL; Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p邏輯操作n用邏輯運(yùn)算符用邏輯運(yùn)算符NOT、AND和和OR來聯(lián)結(jié)多個查詢條件。來聯(lián)結(jié)多個查詢條件。n優(yōu)先級：優(yōu)先級：NOT、AND、OR（用戶可以用括號改變優(yōu)（用戶可以用括號改變優(yōu)先級）。先級）。 nIN謂詞實(shí)際上是多個謂詞實(shí)際上是多個OR運(yùn)算的縮寫。運(yùn)算的縮寫。n nSELECT * FROM emp WHERE deptno=10 AND sal 1500; nSELECT * FROM emp WHERE

12、(deptno=10 OR deptno=20)AND sal1500; Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p注意：n使用使用BETWEENAND，NOT BETWEENAND，IN，NOT IN運(yùn)算符的查詢條件都可以轉(zhuǎn)換為運(yùn)算符的查詢條件都可以轉(zhuǎn)換為NOT，AND，OR的邏輯運(yùn)算。例如，下面兩個語句是等價的：的邏輯運(yùn)算。例如，下面兩個語句是等價的：nSELECT * FROM emp WHERE sal1000 AND sal2000;nSELECT * FROM emp WHERE sal BETWEEN 1000 AND 2000; Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p升序、降序排序nA

13、SC: 升序升序 （缺省）；（缺省）；DESC: 降序降序nSELECT empno,ename,sal FROM emp ORDER BY sal;nSELECT empno,ename,sal FROM emp ORDER BY sal DESC; p多列排序 n首先按照第一個列或表達(dá)式進(jìn)行排序；當(dāng)?shù)谝粋€列或表達(dá)首先按照第一個列或表達(dá)式進(jìn)行排序；當(dāng)?shù)谝粋€列或表達(dá)式的數(shù)據(jù)相同時，以第二個列或表達(dá)式進(jìn)行排序，以此類式的數(shù)據(jù)相同時，以第二個列或表達(dá)式進(jìn)行排序，以此類推推 。nSELECT * FROM emp ORDER BY deptno,sal DESC; （3）查詢排序）查詢排序Oracl

14、e 10g數(shù)據(jù)庫基礎(chǔ)教程p按表達(dá)式排序 n可以按特定的表達(dá)式進(jìn)行排序?？梢园刺囟ǖ谋磉_(dá)式進(jìn)行排序。 nSELECT empno,ename,sal FROM emp ORDER BY sal*12; p使用別名排序 n可以使用目標(biāo)列或表達(dá)式的別名進(jìn)行排序?？梢允褂媚繕?biāo)列或表達(dá)式的別名進(jìn)行排序。nSELECT empno,sal*12 salary FROM emp ORDER BY salary; p使用列位置編號排序 n如果列名或表達(dá)式名稱很長，那么使用位置排序可以縮短如果列名或表達(dá)式名稱很長，那么使用位置排序可以縮短排序語句的長度。排序語句的長度。nSELECT empno,sal*12

15、salary FROM emp ORDER BY 2; Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程（4）查詢統(tǒng)計(jì)）查詢統(tǒng)計(jì)函數(shù)格式功能AVGAVG(DISTINCT|ALL)計(jì)算一列值的平均值（要求數(shù)值列）COUNTCOUNT(DISTINCT|ALL *)統(tǒng)計(jì)元組個數(shù)COUNTCOUNT(DISTINCT|ALL) 統(tǒng)計(jì)一列中 非空值的個數(shù)MAXMAX(DISTINCT|ALL)求一列值中的最大值MINMIN(DISTINCT|ALL)求一列值中的最小值SUMSUM(DISTINCT|ALL)計(jì)算一列值的總和（要求數(shù)值列）STDDEV STDDEV().計(jì)算一列值的標(biāo)準(zhǔn)差VARIANCEVARIAN

16、CE()計(jì)算一列值的方差Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p注意n除了除了COUNT(*)函數(shù)外，其他的統(tǒng)計(jì)函數(shù)都不考慮返函數(shù)外，其他的統(tǒng)計(jì)函數(shù)都不考慮返回值或表達(dá)式為回值或表達(dá)式為NULL的情況。的情況。n聚集函數(shù)只能出現(xiàn)在目標(biāo)列表達(dá)式、聚集函數(shù)只能出現(xiàn)在目標(biāo)列表達(dá)式、ORDER BY子句、子句、HAVING子句中，不能出現(xiàn)在子句中，不能出現(xiàn)在WHERE子句和子句和GROUP BY子句中。子句中。n默認(rèn)對所有的返回行進(jìn)行統(tǒng)計(jì)，包括重復(fù)的行；如果默認(rèn)對所有的返回行進(jìn)行統(tǒng)計(jì)，包括重復(fù)的行；如果要統(tǒng)計(jì)不重復(fù)的行信息，則可以使用要統(tǒng)計(jì)不重復(fù)的行信息，則可以使用DISTINCT選項(xiàng)。選項(xiàng)。n如果對查詢

17、結(jié)果進(jìn)行了分組，則聚集函數(shù)的作用范圍如果對查詢結(jié)果進(jìn)行了分組，則聚集函數(shù)的作用范圍為各個組，否則聚集函數(shù)作用于整個查詢結(jié)果。為各個組，否則聚集函數(shù)作用于整個查詢結(jié)果。Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程pSELECT count(*),avg(sal),max(sal),min(sal) FROM emp WHERE deptno=10; pSELECT avg(comm),sum(comm) FROM emp ; pSELECT count(DISTINCT deptno) FROM emp; pSELECT variance(sal),stddev(sal) FROM emp; Oracle

18、 10g數(shù)據(jù)庫基礎(chǔ)教程13.2.3 分組查詢分組查詢p基本語法p單列分組查詢p多列分組查詢p使用HAVING子句限制返回組p使用ROLLUP 和CUBEp合并分組查詢Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程nSELECT column, group_function, nFROM tablenWHERE conditionnGROUP BY ROOLUP|CUBE|GROUPING SETS ngroup_by_expressionnHAVING group_conditionnORDER BY columnASC|DESC;（1）基本語法）基本語法Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p注意：nGRO

19、UP BY子句用于指定分組列或分組表達(dá)式。子句用于指定分組列或分組表達(dá)式。n集合函數(shù)用于對分組進(jìn)行統(tǒng)計(jì)。如果未對查詢分組，集合函數(shù)用于對分組進(jìn)行統(tǒng)計(jì)。如果未對查詢分組，則集合函數(shù)將作用于整個查詢結(jié)果；如果對查詢結(jié)果則集合函數(shù)將作用于整個查詢結(jié)果；如果對查詢結(jié)果分組，則集合函數(shù)將作用于每一個組，即每一個分組分組，則集合函數(shù)將作用于每一個組，即每一個分組都有一個集合函數(shù)。都有一個集合函數(shù)。nHAVING子句用于限制分組的返回結(jié)果。子句用于限制分組的返回結(jié)果。nWHERE子句對表中的記錄進(jìn)行過濾，而子句對表中的記錄進(jìn)行過濾，而HAVING子子句對分組后形成的組進(jìn)行過濾。句對分組后形成的組進(jìn)行過濾。n

20、在分組查詢中，在分組查詢中，SELECT子句后面的所有目標(biāo)列或目子句后面的所有目標(biāo)列或目標(biāo)表達(dá)式要么是分組列，要么是分組表達(dá)式，要么是標(biāo)表達(dá)式要么是分組列，要么是分組表達(dá)式，要么是集合函數(shù)。集合函數(shù)。Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p單列分組查詢n將查詢出來的記錄按照某一個指定的列進(jìn)行分組將查詢出來的記錄按照某一個指定的列進(jìn)行分組nSELECT deptno,count(*),avg(sal) FROM emp GROUP BY deptno; p多列分組查詢n在在GROUP BY子句中指定了兩個或多個分組列子句中指定了兩個或多個分組列nSELECT deptno,job,count(*),

21、avg(sal) FROM emp GROUP BY deptno,job; p使用HAVING子句限制返回組n可以使用可以使用HAVING子句，只有滿足條件的組才會返回。子句，只有滿足條件的組才會返回。nSELECT deptno,count(*),avg(sal) FROM emp GROUP BY deptno HAVING avg(sal)1500;Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p使用ROLLUP 和CUBEn如果在如果在GROUP BY子句中使用子句中使用ROLLUP選項(xiàng)，則還選項(xiàng)，則還可以生成橫向統(tǒng)計(jì)和不分組統(tǒng)計(jì)；可以生成橫向統(tǒng)計(jì)和不分組統(tǒng)計(jì)；n如果在如果在GROUP BY子句

22、中使用子句中使用CUBE選項(xiàng)，則還可以選項(xiàng)，則還可以生成橫向統(tǒng)計(jì)、縱向統(tǒng)計(jì)和不分組統(tǒng)計(jì)。生成橫向統(tǒng)計(jì)、縱向統(tǒng)計(jì)和不分組統(tǒng)計(jì)。nSELECT deptno,job,avg(sal) FROM emp GROUP BY ROLLUP(deptno,job);nSELECT deptno,job,avg(sal) FROM emp GROUP BY CUBE(deptno,job);Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p合并分組查詢n使用使用GROUPING SETS可以將幾個單獨(dú)的分組查詢可以將幾個單獨(dú)的分組查詢合并成一個分組查詢合并成一個分組查詢 nSELECT deptno,job,avg(sa

23、l) FROM emp GROUP BY GROUPING SETS(deptno,job); Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程13.2.4 連接查詢連接查詢p交叉連接p內(nèi)連接n等值連接等值連接n不等值連接不等值連接n自身連接自身連接p外連接n左外連接左外連接n右外連接右外連接n全外連接全外連接Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程（1）交叉連接）交叉連接p概念n兩個或多個表之間的無條件連接。一個表中所有記錄兩個或多個表之間的無條件連接。一個表中所有記錄分別與其他表中所有記錄進(jìn)行連接。如果進(jìn)行連接的分別與其他表中所有記錄進(jìn)行連接。如果進(jìn)行連接的表中分別有表中分別有n1，n2，n3條記錄，那么交叉

24、連接的條記錄，那么交叉連接的結(jié)果集中將有結(jié)果集中將有n1n2n3條記錄。條記錄。 p以下情況可以出現(xiàn)交叉連接n連接條件省略連接條件省略n連接條件非法連接條件非法n一個表的所有行被連接到另一個表的所有行一個表的所有行被連接到另一個表的所有行p示例nSELECT ename,dname from emp,dept;Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程（2）內(nèi)連接）內(nèi)連接p執(zhí)行過程p內(nèi)連接語法p等值內(nèi)連接p非等值內(nèi)連接p自身內(nèi)連接Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p執(zhí)行過程n首先在表首先在表1中找到第一個元組，然后從頭開始掃描表中找到第一個元組，然后從頭開始掃描表2，逐一查找滿足連接條件的元組，找到后

25、就將表逐一查找滿足連接條件的元組，找到后就將表1中的第中的第1個元組與該元組拼接形成結(jié)果表中的一個元組。表個元組與該元組拼接形成結(jié)果表中的一個元組。表2全部找完后，再找表全部找完后，再找表1中的第中的第2個元組，然后再從頭掃個元組，然后再從頭掃描表描表2，逐一查找滿足連接條件的元組，找到后就將表，逐一查找滿足連接條件的元組，找到后就將表1中的第中的第2個元組與該元組拼接形成結(jié)果表中的一個元個元組與該元組拼接形成結(jié)果表中的一個元組。重復(fù)執(zhí)行，直到表組。重復(fù)執(zhí)行，直到表1中的全部元組都處理完畢為止。中的全部元組都處理完畢為止。Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p內(nèi)連接語法：標(biāo)準(zhǔn)SQL語句的連接方式

26、nSELECT table1.column,talbe2.column, nFROM table1 INNER JOIN table2 JOIN nON condition;p內(nèi)連接語法：Oracle擴(kuò)展的連接方式nSELECT table1.column,talbe2.column, nFROM table1,table2, nWHERE condition;Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p等值內(nèi)連接 nSELECT empno,ename,sal,emp.deptno,dname FROM emp JOIN dept ON emp.deptno=10 AND emp.deptno=de

27、pt.deptno;nSELECT empno,ename,sal,emp.deptno,dname FROM emp,dept WHERE emp.deptno=10 AND emp.deptno=dept.deptno; Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p非等值內(nèi)連接nSELECT empno,ename,sal,grade FROM emp JOIN salgrade ON sallosal AND sallosal AND salhisal;Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p自身內(nèi)連接 nSELECT worker.empno,worker.ename, manager.empno

28、, manager.ename FROM emp worker JOIN emp manager ON worker.mgr=manager.empno;nSELECT worker.empno,worker.ename, manager.empno,manager.ename FROM emp worker,emp manager WHERE worker.mgr=manager.empno;Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程（3）外連接）外連接p左外連接p右外連接p全外連接Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p左外連接語法：標(biāo)準(zhǔn)SQL語句的連接方式nSELECT table1.column,

29、 table2.column, nFROM table1 LEFT JOIN table2, nON table1.column table2.column,;p左外連接語法：Oracle擴(kuò)展的連接方式nSELECT table1.column, table2.column, nFROM table1, table2,nWHERE table1.column n table2.column(+);Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p查詢10號部門的部門名、員工號、員工名和所有其他部門的名稱，語句為nSELECT dname,empno,ename nFROM dept nLEFT JOIN e

30、mpnON dept.deptno=emp.deptno AND dept.deptno=10;p或nSELECT dname,empno,enamenFROM dept,empnWHERE dept.deptno=emp.deptno(+) AND n emp.deptno(+)=10;Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p右外連接語法：標(biāo)準(zhǔn)SQL語句的連接方式nSELECT table1.column, table2.column, nFROM table1 RIGHT JOIN table2, nON table1.column table2.column;p右外連接語法：Oracle擴(kuò)展

31、的連接方式nSELECT table1.column, table2.column, nFROM table1, table2,nWHERE table1.column (+)n table2.column; Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p查詢20號部門的部門名稱及其員工號、員工名，和所有其他部門的員工名、員工號，語句為nSELECT empno,ename,dname nFROM dept RIGHT JOIN empnON dept.deptno=emp.deptno AND n dept.deptno=20;p或nSELECT empno,ename,dname nFROM dep

32、t,empnWHERE dept.deptno(+)=emp.deptno AND n dept.deptno(+)=20; Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p全外連接是指在內(nèi)連接的基礎(chǔ)上，將連接操作符兩側(cè)表中不符合連接條件的記錄加入結(jié)果集中。p在Oracle數(shù)據(jù)庫中，全外連接的表示方式為nSELECT table1.column, table2.column, nFROM table1 FULL JOIN table2, nON table1.column1 = table2.column2;p查詢所有的部門名和員工名，語句為nSELECT dname,ename nFROM emp FU

33、LL JOIN dept nON emp.deptno=dept.deptno; Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程13.2.5 子查詢子查詢p子查詢概述p單行單列子查詢p多行單列子查詢p單行多列子查詢p多行多列子查詢p相關(guān)子查詢p在FROM子句中使用子查詢 p在DDL語句中使用子查詢p使用WITH子句的子查詢Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p子查詢的概念n子查詢是指嵌套在其他子查詢是指嵌套在其他SQL語句中的語句中的SELECT語句，也語句，也稱為嵌套查詢稱為嵌套查詢 。n在執(zhí)行時，由里向外，先處理子查詢，再將子查詢的返在執(zhí)行時，由里向外，先處理子查詢，再將子查詢的返回結(jié)果用于其父語句（外

34、部語句）的執(zhí)行?；亟Y(jié)果用于其父語句（外部語句）的執(zhí)行。 p子查詢作用n在在INSERT或或CREATE TABLE語句中使用子查詢，可語句中使用子查詢，可以將子查詢的結(jié)果寫入到目標(biāo)表中；以將子查詢的結(jié)果寫入到目標(biāo)表中；n在在UPDATE語句中使用子查詢可以修改一個或多個記錄語句中使用子查詢可以修改一個或多個記錄的數(shù)據(jù)；的數(shù)據(jù)；n在在DELETE語句中使用子查詢可以刪除一個或多個記錄語句中使用子查詢可以刪除一個或多個記錄n在在WHERE和和HAVING子句中使用子查詢可以返回的子句中使用子查詢可以返回的一個或多個值。一個或多個值。（1）子查詢概述）子查詢概述Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p單

35、行單列子查詢是指子查詢只返回一行數(shù)據(jù)，而且只返回一列的數(shù)據(jù)。 p運(yùn)算符n=，=，n (SELECT sal FROM emp WHERE empno=7934); （2）單行單列子查詢）單行單列子查詢Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程（3）多行單列子查詢）多行單列子查詢p多行單列子查詢是指返回多行數(shù)據(jù)，且只返回一列的數(shù)據(jù)。 p運(yùn)算符號運(yùn)運(yùn) 算算 符符含含 義義IN與子查詢返回結(jié)果中任何一個值相等與子查詢返回結(jié)果中任何一個值相等NOT IN與子查詢返回結(jié)果中任何一個值都不等與子查詢返回結(jié)果中任何一個值都不等ANY比子查詢返回結(jié)果中某一個值大比子查詢返回結(jié)果中某一個值大=ANY與子查詢返回結(jié)果中某

36、一個值相等與子查詢返回結(jié)果中某一個值相等ALL比子查詢返回結(jié)果中所有值都大比子查詢返回結(jié)果中所有值都大ANY n (SELECT sal FROM emp WHERE deptno=10);p查詢比10號部門所有員工工資高的員工信息。nSELECT empno,ename,sal FROM empnWHERE sal ALL n (SELECT sal FROM emp WHERE deptno=10); Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程（4）單行多列子查詢）單行多列子查詢p單行多列子查詢是指子查詢返回一行數(shù)據(jù)，但是包含多列數(shù)據(jù)。p多列數(shù)據(jù)進(jìn)行比較時，可以成對比較，也可以非成對比較。成對比較

37、要求多個列的數(shù)據(jù)必須同時匹配，而非成對比較則不要求多個列的數(shù)據(jù)同時匹配。Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p查詢與7844號員工的工資、工種都相同的員工的信息。nSELECT empno,ename,sal,job FROM emp nWHERE (sal,job)=(SELECT sal,job FROM empn WHERE empno=7844);p查詢與10號部門某個員工工資相同，工種也與10號部門的某個員工相同的員工的信息。nSELECT empno,ename,sal,job FROM emp nWHERE sal IN (SELECT sal FROM emp WHERE nde

38、ptno=10) AND job IN (SELECT job FROM emp nWHERE deptno=10); Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程（5）多行多列子查詢）多行多列子查詢p多行多列子查詢是指子查詢返回多行數(shù)據(jù)，并且是多列數(shù)據(jù)。p例如，查詢與10號部門某個員工的工資和工種都相同的員工的信息，語句為nSELECT empno,ename,sal,job FROM empnWHERE (sal,job) IN(nSELECT sal,job FROM emp WHERE deptno=10); Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程（6）相關(guān)子查詢）相關(guān)子查詢p子查詢在執(zhí)行時并不需要

39、外部父查詢的信息，這種子查詢稱為無關(guān)子查詢。p如果子查詢在執(zhí)行時需要引用外部父查詢的信息，那么這種子查詢就稱為相關(guān)子查詢。p在相關(guān)子查詢中經(jīng)常使用EXISTS或NOT EXISTS謂詞來實(shí)現(xiàn)。如果子查詢返回結(jié)果，則條件為TRUE，如果子查詢沒有返回結(jié)果，則條件為FALSE。Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p查詢沒有任何員工的部門號、部門名。nSELECT deptno,dname,loc FROM dept nWHERE NOT EXISTS(SELECT * FROM emp WHERE nemp.deptno=dept.deptno);p查詢比本部門平均工資高的員工信息。nSELECT

40、empno,ename,sal nFROM emp e nWHERE sal( SELECT avg(sal) FROM emp WHERE ndeptno=e.deptno); Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程（7）在）在FROM子句中使用子查詢子句中使用子查詢p當(dāng)在FROM子句中使用子查詢時，該子查詢被作為視圖對待，必須為該子查詢指定別名。p查詢各個員工的員工號、員工名及其所在部門平均工資。nSELECT empno,ename,d.avgsal FROM emp,n(SELECT deptno,avg(sal) avgsal FROM emp GROUP nBY deptno) d W

41、HERE emp.deptno=d.deptno;p查詢各個部門號、部門名、部門人數(shù)及部門平均工資。nSELECT dept.deptno,dname, d.amount,d.avgsal nFROM dept,(SELECT deptno,count(*)amount, navg(sal) avgsal FROM emp GROUP BY deptno)d nWHERE dept.deptno=d.deptno; Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程（8）在）在DDL語句中使用子查詢語句中使用子查詢p可以在CREATE TABLE和CREATE VIEW語句中使用子查詢來創(chuàng)建表和視圖。nCRE

42、ATE TABLE emp_subquery nAS nSELECT empno,ename,sal FROM emp;nCREATE VIEW emp_view_subquerynASnSELECT * FROM emp WHERE sal2000; Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程（9）使用）使用WITH子句的子查詢子句的子查詢p如果在一個SQL語句中多次使用同一個子查詢，可以通過WITH子句給子查詢指定一個名字，從而可以實(shí)現(xiàn)通過名字引用該子查詢，而不必每次都完整寫出該子查詢。p查詢?nèi)藬?shù)最多的部門的信息。nSELECT * FROM deptnWHERE deptno IN (nSELE

43、CT deptno FROM emp GROUP BY deptno nHAVING count(*)=ALL(n SELECT count(*) FROM emp GROUP BY deptno)n);Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p相同的子查詢連續(xù)出現(xiàn)了兩次，因此可以按下列方式編寫查詢語句。nWITH deptinfo AS(SELECT deptno,count(*) num nFROM emp GROUP BY deptno) nSELECT * FROM dept WHERE deptno IN(SELECT ndeptno FROM deptinfo WHERE num=(SE

44、LECT nmax(num) FROM deptinfo)n); Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程13.2.6 合并查詢合并查詢p語法pUNIONpINTERSECTpMINUSOracle 10g數(shù)據(jù)庫基礎(chǔ)教程p語法： SELECT query_statement1 UNION|UNION ALL|INTERSECT|MINUS SELECT query_statement2;p注意：n當(dāng)要合并幾個查詢的結(jié)果集時，這幾個查詢的結(jié)果集當(dāng)要合并幾個查詢的結(jié)果集時，這幾個查詢的結(jié)果集必須具有相同的列數(shù)與數(shù)據(jù)類型。必須具有相同的列數(shù)與數(shù)據(jù)類型。n如果要對最終的結(jié)果集排序，只能在最后一個查詢之如果要

45、對最終的結(jié)果集排序，只能在最后一個查詢之后用后用ORDER BY子句指明排序列。子句指明排序列。 Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程（1） UNIONpUNION運(yùn)算符用于獲取幾個查詢結(jié)果集的并集，將重復(fù)的記錄只保留一個，并且默認(rèn)按第一列進(jìn)行排序。p查詢10號部門的員工號、員工名、工資和部門號以及工資大于2000的所有員工的員工號、員工名、工資和部門號，語句為nSELECT empno,ename,sal,deptno nFROM emp WHERE deptno=10nUNIONnSELECT empno,ename,sal,deptno nFROM emp WHERE sal2000nOR

46、DER BY deptno; Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p如果要保留所有的重復(fù)記錄，則需要使用UNION ALL運(yùn)算符。nSELECT empno,ename,sal,deptnonFROM emp WHERE deptno=10nUNION ALLnSELECT empno,ename,sal,deptno nFROM emp WHERE sal2000nORDER BY deptno; Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程（2） INTERSECTpINTERSECT用于獲取幾個查詢結(jié)果集的交集，只返回同時存在于幾個查詢結(jié)果集中的記錄。同時，返回的最終結(jié)果集默認(rèn)按第一列進(jìn)行排序。p查

47、詢30號部門中工資大于2000的員工號、員工名、工資和部門號，語句為：nSELECT empno,ename,sal,deptno nFROM emp WHERE deptno=30nINTERSECTnSELECT empno,ename,sal,deptno nFROM EMP WHERE sal2000; Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程（3） MINUSpMINUS用于獲取幾個查詢結(jié)果集的差集，即返回在第一個結(jié)果集中存在，而在第二個結(jié)果集中不存在的記錄。同時，返回的最終結(jié)果集默認(rèn)按第一列進(jìn)行排序。p查詢30號部門中工種不是“SALESMAN”的員工號、員工名和工種名稱，語句為： nS

48、ELECT empno,ename,job nFROM emp WHERE deptno=30nMINUSnSELECT empno,ename,job nFROM EMP WHERE job=SALESMAN;Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程13.3 數(shù)據(jù)操縱數(shù)據(jù)操縱p插入數(shù)據(jù)p修改數(shù)據(jù)pMERGE語句 p刪除數(shù)據(jù)Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程13.3.1 插入數(shù)據(jù)插入數(shù)據(jù)p插入單行記錄p利用子查詢插入數(shù)據(jù)p向多個表中插入數(shù)據(jù)Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程（1）插入插入單行記錄單行記錄p語法nINSERT INTO table_name|view_name n(column1,co

49、lumn2) nVALUES(value1,values, ) p注意n如果在如果在INTO子句中沒有指明任何列名，則子句中沒有指明任何列名，則VALUES子句中列值子句中列值的個數(shù)、順序、類型必須與表中列的個數(shù)、順序、類型相匹配。的個數(shù)、順序、類型必須與表中列的個數(shù)、順序、類型相匹配。n如果在如果在INTO子句中指定了列名，則子句中指定了列名，則VALUES子句中提供的列值子句中提供的列值的個數(shù)、順序、類型必須與指定列的個數(shù)、順序、類型按位置對的個數(shù)、順序、類型必須與指定列的個數(shù)、順序、類型按位置對應(yīng)。應(yīng)。n向表或視圖中插入的數(shù)據(jù)必須滿足表的完整性約束。向表或視圖中插入的數(shù)據(jù)必須滿足表的完整

50、性約束。n字符型和日期型數(shù)據(jù)在插入時要加單引號。日期類型數(shù)據(jù)需要按字符型和日期型數(shù)據(jù)在插入時要加單引號。日期類型數(shù)據(jù)需要按系統(tǒng)默認(rèn)格式輸入，或使用系統(tǒng)默認(rèn)格式輸入，或使用TO_DATE函數(shù)進(jìn)行日期轉(zhuǎn)換。函數(shù)進(jìn)行日期轉(zhuǎn)換。 Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p向dept表中插入一行記錄。nINSERT INTO dept VALUES(50, IM, dalian);p向emp表中插入一行記錄。nINSERT INTO emp(empno,ename,sal,hiredate) nVALUES(1234, JOAN,2500, 20-4月月-2007);Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程（2）利

51、用子查詢插入數(shù)據(jù)）利用子查詢插入數(shù)據(jù) p語法nINSERT INTO table_name|view_namen(column1,column2,)nsubquery;p注意nINTO子句中指定的列的個數(shù)、順序、類型必須與子子句中指定的列的個數(shù)、順序、類型必須與子查詢中列的個數(shù)、順序和類型相匹配。查詢中列的個數(shù)、順序和類型相匹配。 Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p統(tǒng)計(jì)各個部門的部門號、部門最高工資和最低工資，并將統(tǒng)計(jì)的結(jié)果寫入到表emp_salary（假設(shè)該表已經(jīng)創(chuàng)建）中。nINSERT INTO emp_salary nSELECT deptno,max(sal),min(sal) FR

52、OM emp nGROUP BY deptno;p向emp表中插入一行記錄，其員工名為FAN，員工號為1235，其他信息與員工名為SCOTT的員工信息相同。nINSERT INTO emp nSELECT 1235, FAN,job,mgr,hiredate,sal,comm,deptnonFROM emp WHERE ename=SCOTT;Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p如果要將大量數(shù)據(jù)插入表中，可以利用子查詢直接裝載的方式進(jìn)行。由于直接裝載數(shù)據(jù)的操作過程不寫入日志文件，因此數(shù)據(jù)插入操作的速度大大提高。p利用子查詢裝載數(shù)據(jù)語法為：nINSERT /*+APPEND*/ INTO nta

53、ble_name|view_name(column1,column2,)nsubquery;p復(fù)制emp表中empno，ename，sal，deptno四列的值，并插入到new_emp表中，語句為：nINSERT /*+APPEND*/ INTO nnew_emp(empno,ename,sal,deptno)nSELECT empno,ename,sal,deptno from emp; Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程13.3.2 修改數(shù)據(jù)修改數(shù)據(jù)p基本語法p修改單行記錄p修改多行記錄p帶有子查詢的修改Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p基本語法nUPDATE table_name|vi

54、ew_namenSET column1=value1,column2=value2nWHERE conditionp修改單條記錄nUPDATE emp SET sal=sal+100,comm=200 nWHERE empno=7844;p修改多條記錄nUPDATE emp SET sal=sal+150 nWHERE deptno=20; p利用子查詢修改記錄nUPDATE emp SET sal=300+ (SELECT avg(sal) nFROM emp WHERE deptno=10) nWHERE deptno=30; Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程13.3.3 MERGE語句

55、語句p利用MERGE語句可以同時完成數(shù)據(jù)的插入與更新操作。p將源表的數(shù)據(jù)分別與目標(biāo)表中的數(shù)據(jù)根據(jù)特性條件進(jìn)行比較（每次只比較一條記錄），如果匹配，則利用源表中的記錄更新目標(biāo)表中的記錄，如果不匹配，則將源表中的記錄插入目標(biāo)表中。p使用MERGE語句操作時，用戶需要具有源表的SELECT對象權(quán)限以及目標(biāo)表的INSERT，UPDATE對象權(quán)限。Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程pMERGE語句的基本語法為：nMERGE INTO schema.target_table target_alias nUSING schema.source_table|source_view|nsource_subque

56、ry source_aliasnON (condition)nWHEN MATCHED THEN UPDATE SETn column1=expression1 n ,column2=expression2 n where_clauseDELETE where_clausenWHEN NOT MATCHED THEN n INSERT (column2,column2) n VALUES (expresstion1,expression2) n where_clause;Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p參數(shù)說明nINTO：指定進(jìn)行數(shù)據(jù)更新或插入的目標(biāo)表；：指定進(jìn)行數(shù)據(jù)更新或插入的目標(biāo)表；n

57、USING：指定用于目標(biāo)表數(shù)據(jù)更新或插入的源表或視圖或子查：指定用于目標(biāo)表數(shù)據(jù)更新或插入的源表或視圖或子查詢；詢；nON：決定：決定MERGE語句執(zhí)行更新操作還是插入操作的條件。對于語句執(zhí)行更新操作還是插入操作的條件。對于目標(biāo)表中滿足條件的記錄，則利用源表中的相應(yīng)記錄進(jìn)行更新；目標(biāo)表中滿足條件的記錄，則利用源表中的相應(yīng)記錄進(jìn)行更新；而源表中不滿條件的記錄將被插入目標(biāo)表中；而源表中不滿條件的記錄將被插入目標(biāo)表中；nwhere_clause：只有當(dāng)該條件為真時才進(jìn)行數(shù)據(jù)的更新或插：只有當(dāng)該條件為真時才進(jìn)行數(shù)據(jù)的更新或插入操作；入操作；nDELETE where_clause：當(dāng)目標(biāo)表中更新后的記錄

58、滿足該條：當(dāng)目標(biāo)表中更新后的記錄滿足該條件時，則刪除該記錄。件時，則刪除該記錄。 Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p使用“雇員”表（別名e）作為數(shù)據(jù)源，在該表 的副本“副本_雇員”表（別名c）中插入并更新行。pMERGE INTO 副本_雇員 c USING 雇員epON (c.雇員標(biāo)識= e.雇員標(biāo)識)pWHEN MATCHED THEN UPDATEpSETpc.姓氏 = e.姓氏,pc.部門標(biāo)識 = e.部門標(biāo)識pWHEN NOT MATCHED THEN INSERT pVALUES (e.雇員標(biāo)識, e.姓氏, e.部門標(biāo)識);Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程MERGE INTO

59、 副本_雇員 c USING 雇員e ON (c.雇員標(biāo)識= e.雇員標(biāo)識) WHEN MATCHED THEN UPDATE SET c.姓氏 = e.姓氏, c.部門標(biāo)識 = e.部門標(biāo)識WHEN NOT MATCHED THEN INSERT VALUES (e.雇員標(biāo)識, e.姓氏, e.部門標(biāo)識); MERGE 語句執(zhí)行前的“副本_雇員”雇員標(biāo)識 姓氏部門標(biāo)識100Smith40103Chang30MERGE 執(zhí)行后的“副本_雇員”雇員標(biāo)識 姓氏部門標(biāo)識100King90103Hunold 60152Davies 50雇員（來源表）雇員標(biāo)識 姓氏部門標(biāo)識100King90103Hun

60、old 60152Davies 50Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程p現(xiàn)有表source_emp和target_emp，表中數(shù)據(jù)如下。利用source_emp表中的數(shù)據(jù)更新target_emp表中的數(shù)據(jù)，對target_emp表中存在的員工信息進(jìn)行更新，對不存在的員工進(jìn)行信息插入。 nSELECT * FROM source_emp;nEMPNO ENAME DEPTNOn-n100 JOAN 10n110 SMITH 20n120 TOM 30 Oracle 10g數(shù)據(jù)庫基礎(chǔ)教程pSELECT * FROM target_emp;pEMPNO ENAME DEPTNOp-p100 MARR