1、第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.1 SQL語言概述,SQL語言介紹 SQL語言的分類 SQL語言的特點,13.1.1 SQL語言介紹,SQL（StructuredQueryLanguage）語言是1974年由Boyce和Chamberlin提出的。 SQL語言是關(guān)系數(shù)據(jù)庫操作的基礎(chǔ)語言，將數(shù)據(jù)查詢、數(shù)據(jù)操縱、數(shù)據(jù)定義、事務(wù)控制、系統(tǒng)控制等功能集于一體，從而使得數(shù)據(jù)庫應(yīng)用開發(fā)人員

2、、數(shù)據(jù)庫管理員等都可以通過SQL語言實現(xiàn)對數(shù)據(jù)庫的訪問和操作。,13.1.2 SQL語言的分類,據(jù)定義語言（Data Definition Language，DDL）：用于定義、修改、刪除數(shù)據(jù)庫對象，包括CREATE，ALTER，DROP，GRANT，REVOKE，AUDIT和NOAUDIT等。 數(shù)據(jù)操縱語言（Data Manipulation Language，DML）：用于改變數(shù)據(jù)庫中的數(shù)據(jù)，包括數(shù)據(jù)插入（INSERT）、數(shù)據(jù)修改（UPDATE）和數(shù)據(jù)刪除（DELETE）。 數(shù)據(jù)查詢語言（Data Query Language，DQL）：用于數(shù)據(jù)檢索，包括SELECT。 事務(wù)控制（Tran

3、saction Control）：用于將一組DML操作組合起來，形成一個事務(wù)并進(jìn)行事務(wù)控制。包括事務(wù)提交（COMMIT）、事務(wù)回滾（ROLLBACK）、設(shè)置保存點（SAVEPOINT）和設(shè)置事務(wù)狀態(tài)（SET TRANSACTION）。 系統(tǒng)控制（System Control）：用于設(shè)置數(shù)據(jù)庫系統(tǒng)參數(shù)，包括ALTER SYSTEM。 會話控制（Session Control）：用于設(shè)置用戶會話相關(guān)參數(shù)，包括ALTER SESSION。,13.1.3 SQL語言的特點,功能一體化：幾乎涵蓋了對數(shù)據(jù)庫的所有操作，語言風(fēng)格統(tǒng)一。 高度的非過程化：在使用SQL語言操作數(shù)據(jù)庫時，用戶只需要說明“做什么”，

4、而不需要說明“怎樣做”。用戶任務(wù)的實現(xiàn)對用戶而言是透明的，由系統(tǒng)自動完成。這大大減輕了用戶的負(fù)擔(dān)，同時降低了對用戶的技術(shù)要求。 面向集合的操作方式：SQL語言采用集合操作方式，不僅查詢結(jié)果可以是多條記錄的集合，而且一次插入、刪除、修改操作的對象也可以是多條記錄的集合。面向集合的操作方式極大地提高了對數(shù)據(jù)操作效率。 多種使用方式：SQL語句既是自含式語言，又是嵌入式語言。SQL語言可以直接以命令方式與數(shù)據(jù)庫進(jìn)行交互，也可以嵌入到其他的高級語言中使用。 簡潔、易學(xué)：SQL語言命令數(shù)量有限，語法簡單，接近于自然語言（英語），因此容易學(xué)習(xí)和掌握。,13.2 數(shù)據(jù)查詢,數(shù)據(jù)查詢基礎(chǔ) 基本查詢 分組查詢

5、連接查詢 子查詢 合并查詢,13.2.1 數(shù)據(jù)查詢基礎(chǔ),基本語法： SELECT ALL|DISTINCTcolumn_name,expression FROM table1_name,table2_name,view_name, WHERE condition GROUP BY column_name1,column_name2, HAVING group_condition ORDER BY column_name2 ASC|DESC ,column_name2,;,13.2.2 基本查詢,無條件查詢 有條件查詢 查詢排序 查詢統(tǒng)計,（1）無條件查詢,查詢所有列 SELECT * FROM

6、 emp; 查詢指定列 SELECT deptno,dname FROM dept; 使用算術(shù)表達(dá)式 SELECT empno,sal*0.8 FROM emp; 使用字符常量 SELECT empno, Name is: , ename FROM emp; 使用函數(shù) SELECT empno,UPPER(ename) FROM emp;,改變列標(biāo)題 SELECT ename employeename,sal salary FROM emp; 使用連接字符串 SELECT 員工號：|empno|員工名|ename FROM emp; 消除重復(fù)行 SELECT ALL deptno FROM e

7、mp; SELECT DISTINCT deptno FROM emp;,（2）有條件查詢,查詢滿足條件的元組可以通過WHERE 子句實現(xiàn)。 WHERE條件中常用的運算符號,關(guān)系運算 SELECT empno,ename,sal FROM emp WHERE deptno! = 10; SELECT empno,ename,sal FROM emp WHERE sal1500 確定范圍 謂詞BETWEEN AND與NOT BETWEEN AND。 SELECT * FROM emp WHERE deptno BETWEEN 10 AND 20; SELECT * FROM emp WHERE

8、sal NOT BETWEEN 1000 AND 2000;,確定集合 謂詞IN可以用來查找屬性值屬于指定集合的元組。 SELECT empno,ename,sal FROM emp WHERE deptno IN(10,30); 字符匹配 %（百分號）代表任意長（長度為0）字符串。 _（下劃線）代表任意單個字符。 ESCAPE：轉(zhuǎn)義字符 SELECT * FROM empWHERE ename LIKE %S%； SELECT * FROM emp WHERE ename LIKE _A%; SELECT * FROM emp WHERE ename LIKE %x_% ESCAPE x;,

9、空值操作 涉及空值查詢時使用IS NULL或 IS NOT NULL，這里的IS不能用=替代。 SELECT * FROM emp WHERE deptno IS NULL; SELECT * FROM emp WHERE comm IS NOT NULL;,邏輯操作 用邏輯運算符NOT、AND和OR來聯(lián)結(jié)多個查詢條件。 優(yōu)先級：NOT、AND、OR（用戶可以用括號改變優(yōu)先級）。 IN謂詞實際上是多個OR運算的縮寫。 SELECT * FROM emp WHERE deptno=10 AND sal 1500; SELECT * FROM emp WHERE (deptno=10 OR dep

10、tno=20)AND sal1500;,注意： 使用BETWEENAND，NOT BETWEENAND，IN，NOT IN運算符的查詢條件都可以轉(zhuǎn)換為NOT，AND，OR的邏輯運算。例如，下面兩個語句是等價的： SELECT * FROM emp WHERE sal1000 AND sal2000; SELECT * FROM emp WHERE sal BETWEEN 1000 AND 2000;,升序、降序排序 ASC: 升序 （缺?。籇ESC: 降序 SELECT empno,ename,sal FROM emp ORDER BY sal; SELECT empno,ename,sal

11、 FROM emp ORDER BY sal DESC; 多列排序 首先按照第一個列或表達(dá)式進(jìn)行排序；當(dāng)?shù)谝粋€列或表達(dá)式的數(shù)據(jù)相同時，以第二個列或表達(dá)式進(jìn)行排序，以此類推 。 SELECT * FROM emp ORDER BY deptno,sal DESC;,（3）查詢排序,按表達(dá)式排序 可以按特定的表達(dá)式進(jìn)行排序。 SELECT empno,ename,sal FROM emp ORDER BY sal*12; 使用別名排序 可以使用目標(biāo)列或表達(dá)式的別名進(jìn)行排序。 SELECT empno,sal*12 salary FROM emp ORDER BY salary; 使用列位置編號排序

12、 如果列名或表達(dá)式名稱很長，那么使用位置排序可以縮短排序語句的長度。 SELECT empno,sal*12 salary FROM emp ORDER BY 2;,（4）查詢統(tǒng)計,注意 除了COUNT(*)函數(shù)外，其他的統(tǒng)計函數(shù)都不考慮返回值或表達(dá)式為NULL的情況。 聚集函數(shù)只能出現(xiàn)在目標(biāo)列表達(dá)式、ORDER BY子句、HAVING子句中，不能出現(xiàn)在WHERE子句和GROUP BY子句中。 默認(rèn)對所有的返回行進(jìn)行統(tǒng)計，包括重復(fù)的行；如果要統(tǒng)計不重復(fù)的行信息，則可以使用DISTINCT選項。 如果對查詢結(jié)果進(jìn)行了分組，則聚集函數(shù)的作用范圍為各個組，否則聚集函數(shù)作用于整個查詢結(jié)果。,SELEC

13、T count(*),avg(sal),max(sal),min(sal) FROM emp WHERE deptno=10; SELECT avg(comm),sum(comm) FROM emp ; SELECT count(DISTINCT deptno) FROM emp; SELECT variance(sal),stddev(sal) FROM emp;,13.2.3 分組查詢,基本語法 單列分組查詢 多列分組查詢 使用HAVING子句限制返回組 使用ROLLUP 和CUBE 合并分組查詢,SELECT column, group_function, FROM table WHER

14、E condition GROUP BY ROOLUP|CUBE|GROUPING SETS group_by_expression HAVING group_condition ORDER BY columnASC|DESC;,（1）基本語法,注意： GROUP BY子句用于指定分組列或分組表達(dá)式。 集合函數(shù)用于對分組進(jìn)行統(tǒng)計。如果未對查詢分組，則集合函數(shù)將作用于整個查詢結(jié)果；如果對查詢結(jié)果分組，則集合函數(shù)將作用于每一個組，即每一個分組都有一個集合函數(shù)。 HAVING子句用于限制分組的返回結(jié)果。 WHERE子句對表中的記錄進(jìn)行過濾，而HAVING子句對分組后形成的組進(jìn)行過濾。 在分組查詢中，

15、SELECT子句后面的所有目標(biāo)列或目標(biāo)表達(dá)式要么是分組列，要么是分組表達(dá)式，要么是集合函數(shù)。,單列分組查詢 將查詢出來的記錄按照某一個指定的列進(jìn)行分組 SELECT deptno,count(*),avg(sal) FROM emp GROUP BY deptno; 多列分組查詢 在GROUP BY子句中指定了兩個或多個分組列 SELECT deptno,job,count(*),avg(sal) FROM emp GROUP BY deptno,job; 使用HAVING子句限制返回組 可以使用HAVING子句，只有滿足條件的組才會返回。 SELECT deptno,count(*),avg

16、(sal) FROM emp GROUP BY deptno HAVING avg(sal)1500;,使用ROLLUP 和CUBE 如果在GROUP BY子句中使用ROLLUP選項，則還可以生成橫向統(tǒng)計和不分組統(tǒng)計； 如果在GROUP BY子句中使用CUBE選項，則還可以生成橫向統(tǒng)計、縱向統(tǒng)計和不分組統(tǒng)計。 SELECT deptno,job,avg(sal) FROM emp GROUP BY ROLLUP(deptno,job); SELECT deptno,job,avg(sal) FROM emp GROUP BY CUBE(deptno,job);,合并分組查詢 使用GROUPIN

17、G SETS可以將幾個單獨的分組查詢合并成一個分組查詢 SELECT deptno,job,avg(sal) FROM emp GROUP BY GROUPING SETS(deptno,job);,13.2.4 連接查詢,交叉連接 內(nèi)連接 等值連接 不等值連接 自身連接 外連接 左外連接 右外連接 全外連接,（1）交叉連接,概念 兩個或多個表之間的無條件連接。一個表中所有記錄分別與其他表中所有記錄進(jìn)行連接。如果進(jìn)行連接的表中分別有n1，n2，n3條記錄，那么交叉連接的結(jié)果集中將有n1n2n3條記錄。 以下情況可以出現(xiàn)交叉連接 連接條件省略 連接條件非法 一個表的所有行被連接到另一個表的所有行

18、 示例 SELECT ename,dname from emp,dept;,（2）內(nèi)連接,執(zhí)行過程 內(nèi)連接語法 等值內(nèi)連接 非等值內(nèi)連接 自身內(nèi)連接,執(zhí)行過程 首先在表1中找到第一個元組，然后從頭開始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第1個元組與該元組拼接形成結(jié)果表中的一個元組。表2全部找完后，再找表1中的第2個元組，然后再從頭掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第2個元組與該元組拼接形成結(jié)果表中的一個元組。重復(fù)執(zhí)行，直到表1中的全部元組都處理完畢為止。,內(nèi)連接語法：標(biāo)準(zhǔn)SQL語句的連接方式 SELECT table1.column,talbe2.co

19、lumn, FROM table1 INNER JOIN table2 JOIN ON condition; 內(nèi)連接語法：Oracle擴展的連接方式 SELECT table1.column,talbe2.column, FROM table1,table2, WHERE condition;,等值內(nèi)連接 SELECT empno,ename,sal,emp.deptno,dname FROM emp JOIN dept ON emp.deptno=10 AND emp.deptno=dept.deptno; SELECT empno,ename,sal,emp.deptno,dname FR

20、OM emp,dept WHERE emp.deptno=10 AND emp.deptno=dept.deptno;,非等值內(nèi)連接 SELECT empno,ename,sal,grade FROM emp JOIN salgrade ON sallosal AND sallosal AND salhisal;,自身內(nèi)連接 SELECT worker.empno,worker.ename, manager.empno, manager.ename FROM emp worker JOIN emp manager ON worker.mgr=manager.empno; SELECT work

21、er.empno,worker.ename, manager.empno,manager.ename FROM emp worker,emp manager WHERE worker.mgr=manager.empno;,（3）外連接,左外連接 右外連接 全外連接,左外連接語法：標(biāo)準(zhǔn)SQL語句的連接方式 SELECT table1.column, table2.column, FROM table1 LEFT JOIN table2, ON table1.column table2.column,; 左外連接語法：Oracle擴展的連接方式 SELECT table1.column, tabl

22、e2.column, FROM table1, table2, WHERE table1.column table2.column(+);,查詢10號部門的部門名、員工號、員工名和所有其他部門的名稱，語句為 SELECT dname,empno,ename FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno AND dept.deptno=10; 或 SELECT dname,empno,ename FROM dept,emp WHERE dept.deptno=emp.deptno(+) AND emp.deptno(+)=10;,右外連接語法

23、：標(biāo)準(zhǔn)SQL語句的連接方式 SELECT table1.column, table2.column, FROM table1 RIGHT JOIN table2, ON table1.column table2.column; 右外連接語法：Oracle擴展的連接方式 SELECT table1.column, table2.column, FROM table1, table2, WHERE table1.column (+) table2.column;,查詢20號部門的部門名稱及其員工號、員工名，和所有其他部門的員工名、員工號，語句為 SELECT empno,ename,dname F

24、ROM dept RIGHT JOIN emp ON dept.deptno=emp.deptno AND dept.deptno=20; 或 SELECT empno,ename,dname FROM dept,emp WHERE dept.deptno(+)=emp.deptno AND dept.deptno(+)=20;,全外連接是指在內(nèi)連接的基礎(chǔ)上，將連接操作符兩側(cè)表中不符合連接條件的記錄加入結(jié)果集中。 在Oracle數(shù)據(jù)庫中，全外連接的表示方式為 SELECT table1.column, table2.column, FROM table1 FULL JOIN table2, O

25、N table1.column1 = table2.column2; 查詢所有的部門名和員工名，語句為 SELECT dname,ename FROM emp FULL JOIN dept ON emp.deptno=dept.deptno;,13.2.5 子查詢,子查詢概述 單行單列子查詢 多行單列子查詢 單行多列子查詢 多行多列子查詢 相關(guān)子查詢 在FROM子句中使用子查詢 在DDL語句中使用子查詢 使用WITH子句的子查詢,子查詢的概念 子查詢是指嵌套在其他SQL語句中的SELECT語句，也稱為嵌套查詢 。 在執(zhí)行時，由里向外，先處理子查詢，再將子查詢的返回結(jié)果用于其父語句（外部語句）的

26、執(zhí)行。 子查詢作用 在INSERT或CREATE TABLE語句中使用子查詢，可以將子查詢的結(jié)果寫入到目標(biāo)表中； 在UPDATE語句中使用子查詢可以修改一個或多個記錄的數(shù)據(jù)； 在DELETE語句中使用子查詢可以刪除一個或多個記錄 在WHERE和HAVING子句中使用子查詢可以返回的一個或多個值。,（1）子查詢概述,單行單列子查詢是指子查詢只返回一行數(shù)據(jù)，而且只返回一列的數(shù)據(jù)。 運算符 =，=， (SELECT sal FROM emp WHERE empno=7934);,（2）單行單列子查詢,（3）多行單列子查詢,多行單列子查詢是指返回多行數(shù)據(jù)，且只返回一列的數(shù)據(jù)。 運算符號,查詢與10號部

27、門某個員工工資相等的員工信息。 SELECT empno,ename,sal FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno=10); 查詢比10號部門某個員工工資高的員工信息。 SELECT empno,ename,sal FROM emp WHERE sal ANY (SELECT sal FROM emp WHERE deptno=10); 查詢比10號部門所有員工工資高的員工信息。 SELECT empno,ename,sal FROM emp WHERE sal ALL (SELECT sal FROM emp WHER

28、E deptno=10);,（4）單行多列子查詢,單行多列子查詢是指子查詢返回一行數(shù)據(jù)，但是包含多列數(shù)據(jù)。 多列數(shù)據(jù)進(jìn)行比較時，可以成對比較，也可以非成對比較。成對比較要求多個列的數(shù)據(jù)必須同時匹配，而非成對比較則不要求多個列的數(shù)據(jù)同時匹配。,查詢與7844號員工的工資、工種都相同的員工的信息。 SELECT empno,ename,sal,job FROM emp WHERE (sal,job)=(SELECT sal,job FROM emp WHERE empno=7844); 查詢與10號部門某個員工工資相同，工種也與10號部門的某個員工相同的員工的信息。 SELECT empno,en

29、ame,sal,job FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno=10) AND job IN (SELECT job FROM emp WHERE deptno=10);,（5）多行多列子查詢,多行多列子查詢是指子查詢返回多行數(shù)據(jù)，并且是多列數(shù)據(jù)。 例如，查詢與10號部門某個員工的工資和工種都相同的員工的信息，語句為 SELECT empno,ename,sal,job FROM emp WHERE (sal,job) IN( SELECT sal,job FROM emp WHERE deptno=10);,（6）相關(guān)子

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

31、,ename,sal FROM emp e WHERE sal( SELECT avg(sal) FROM emp WHERE deptno=e.deptno);,（7）在FROM子句中使用子查詢,當(dāng)在FROM子句中使用子查詢時，該子查詢被作為視圖對待，必須為該子查詢指定別名。 查詢各個員工的員工號、員工名及其所在部門平均工資。 SELECT empno,ename,d.avgsal FROM emp, (SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno) d WHERE emp.deptno=d.deptno; 查詢各個部門號、部門

32、名、部門人數(shù)及部門平均工資。 SELECT dept.deptno,dname, d.amount,d.avgsal FROM dept,(SELECT deptno,count(*)amount, avg(sal) avgsal FROM emp GROUP BY deptno)d WHERE dept.deptno=d.deptno;,（8）在DDL語句中使用子查詢,可以在CREATE TABLE和CREATE VIEW語句中使用子查詢來創(chuàng)建表和視圖。 CREATE TABLE emp_subquery AS SELECT empno,ename,sal FROM emp; CREATE

33、VIEW emp_view_subquery AS SELECT * FROM emp WHERE sal2000;,（9）使用WITH子句的子查詢,如果在一個SQL語句中多次使用同一個子查詢，可以通過WITH子句給子查詢指定一個名字，從而可以實現(xiàn)通過名字引用該子查詢，而不必每次都完整寫出該子查詢。 查詢?nèi)藬?shù)最多的部門的信息。 SELECT * FROM dept WHERE deptno IN ( SELECT deptno FROM emp GROUP BY deptno HAVING count(*)=ALL( SELECT count(*) FROM emp GROUP BY dept

34、no) );,相同的子查詢連續(xù)出現(xiàn)了兩次，因此可以按下列方式編寫查詢語句。 WITH deptinfo AS(SELECT deptno,count(*) num FROM emp GROUP BY deptno) SELECT * FROM dept WHERE deptno IN(SELECT deptno FROM deptinfo WHERE num=(SELECT max(num) FROM deptinfo) );,13.2.6 合并查詢,語法 UNION INTERSECT MINUS,語法： SELECT query_statement1 UNION|UNION ALL|INT

35、ERSECT|MINUS SELECT query_statement2; 注意： 當(dāng)要合并幾個查詢的結(jié)果集時，這幾個查詢的結(jié)果集必須具有相同的列數(shù)與數(shù)據(jù)類型。 如果要對最終的結(jié)果集排序，只能在最后一個查詢之后用ORDER BY子句指明排序列。,（1） UNION,UNION運算符用于獲取幾個查詢結(jié)果集的并集，將重復(fù)的記錄只保留一個，并且默認(rèn)按第一列進(jìn)行排序。 查詢10號部門的員工號、員工名、工資和部門號以及工資大于2000的所有員工的員工號、員工名、工資和部門號，語句為 SELECT empno,ename,sal,deptno FROM emp WHERE deptno=10 UNION

36、SELECT empno,ename,sal,deptno FROM emp WHERE sal2000 ORDER BY deptno;,如果要保留所有的重復(fù)記錄，則需要使用UNION ALL運算符。 SELECT empno,ename,sal,deptno FROM emp WHERE deptno=10 UNION ALL SELECT empno,ename,sal,deptno FROM emp WHERE sal2000 ORDER BY deptno;,（2） INTERSECT,INTERSECT用于獲取幾個查詢結(jié)果集的交集，只返回同時存在于幾個查詢結(jié)果集中的記錄。同時，返回

37、的最終結(jié)果集默認(rèn)按第一列進(jìn)行排序。 查詢30號部門中工資大于2000的員工號、員工名、工資和部門號，語句為： SELECT empno,ename,sal,deptno FROM emp WHERE deptno=30 INTERSECT SELECT empno,ename,sal,deptno FROM EMP WHERE sal2000;,（3） MINUS,MINUS用于獲取幾個查詢結(jié)果集的差集，即返回在第一個結(jié)果集中存在，而在第二個結(jié)果集中不存在的記錄。同時，返回的最終結(jié)果集默認(rèn)按第一列進(jìn)行排序。 查詢30號部門中工種不是“SALESMAN”的員工號、員工名和工種名稱，語句為： SE

38、LECT empno,ename,job FROM emp WHERE deptno=30 MINUS SELECT empno,ename,job FROM EMP WHERE job=SALESMAN;,13.3 數(shù)據(jù)操縱,插入數(shù)據(jù) 修改數(shù)據(jù) MERGE語句 刪除數(shù)據(jù),13.3.1 插入數(shù)據(jù),插入單行記錄 利用子查詢插入數(shù)據(jù) 向多個表中插入數(shù)據(jù),（1）插入單行記錄,語法 INSERT INTO table_name|view_name (column1,column2) VALUES(value1,values, ) 注意 如果在INTO子句中沒有指明任何列名，則VALUES子句中列值的個

39、數(shù)、順序、類型必須與表中列的個數(shù)、順序、類型相匹配。 如果在INTO子句中指定了列名，則VALUES子句中提供的列值的個數(shù)、順序、類型必須與指定列的個數(shù)、順序、類型按位置對應(yīng)。 向表或視圖中插入的數(shù)據(jù)必須滿足表的完整性約束。 字符型和日期型數(shù)據(jù)在插入時要加單引號。日期類型數(shù)據(jù)需要按系統(tǒng)默認(rèn)格式輸入，或使用TO_DATE函數(shù)進(jìn)行日期轉(zhuǎn)換。,向dept表中插入一行記錄。 INSERT INTO dept VALUES(50, IM, dalian); 向emp表中插入一行記錄。 INSERT INTO emp(empno,ename,sal,hiredate) VALUES(1234, JOAN,

40、2500, 20-4月-2007);,（2）利用子查詢插入數(shù)據(jù),語法 INSERT INTO table_name|view_name (column1,column2,) subquery; 注意 INTO子句中指定的列的個數(shù)、順序、類型必須與子查詢中列的個數(shù)、順序和類型相匹配。,統(tǒng)計各個部門的部門號、部門最高工資和最低工資，并將統(tǒng)計的結(jié)果寫入到表emp_salary（假設(shè)該表已經(jīng)創(chuàng)建）中。 INSERT INTO emp_salary SELECT deptno,max(sal),min(sal) FROM emp GROUP BY deptno; 向emp表中插入一行記錄，其員工名為FA

41、N，員工號為1235，其他信息與員工名為SCOTT的員工信息相同。 INSERT INTO emp SELECT 1235, FAN,job,mgr,hiredate,sal,comm,deptno FROM emp WHERE ename=SCOTT;,如果要將大量數(shù)據(jù)插入表中，可以利用子查詢直接裝載的方式進(jìn)行。由于直接裝載數(shù)據(jù)的操作過程不寫入日志文件，因此數(shù)據(jù)插入操作的速度大大提高。 利用子查詢裝載數(shù)據(jù)語法為： INSERT /*+APPEND*/ INTO table_name|view_name(column1,column2,) subquery; 復(fù)制emp表中empno，enam

42、e，sal，deptno四列的值，并插入到new_emp表中，語句為： INSERT /*+APPEND*/ INTO new_emp(empno,ename,sal,deptno) SELECT empno,ename,sal,deptno from emp;,13.3.2 修改數(shù)據(jù),基本語法 修改單行記錄 修改多行記錄 帶有子查詢的修改,基本語法 UPDATE table_name|view_name SET column1=value1,column2=value2 WHERE condition 修改單條記錄 UPDATE emp SET sal=sal+100,comm=200 WH

43、ERE empno=7844; 修改多條記錄 UPDATE emp SET sal=sal+150 WHERE deptno=20; 利用子查詢修改記錄 UPDATE emp SET sal=300+ (SELECT avg(sal) FROM emp WHERE deptno=10) WHERE deptno=30;,13.3.3 MERGE語句,利用MERGE語句可以同時完成數(shù)據(jù)的插入與更新操作。 將源表的數(shù)據(jù)分別與目標(biāo)表中的數(shù)據(jù)根據(jù)特性條件進(jìn)行比較（每次只比較一條記錄），如果匹配，則利用源表中的記錄更新目標(biāo)表中的記錄，如果不匹配，則將源表中的記錄插入目標(biāo)表中。 使用MERGE語句操作時，

44、用戶需要具有源表的SELECT對象權(quán)限以及目標(biāo)表的INSERT，UPDATE對象權(quán)限。,MERGE語句的基本語法為： MERGE INTO schema.target_table target_alias USING schema.source_table|source_view| source_subquery source_alias ON (condition) WHEN MATCHED THEN UPDATE SET column1=expression1 ,column2=expression2 where_clauseDELETE where_clause WHEN NOT MAT

45、CHED THEN INSERT (column2,column2) VALUES (expresstion1,expression2) where_clause;,參數(shù)說明 INTO：指定進(jìn)行數(shù)據(jù)更新或插入的目標(biāo)表； USING：指定用于目標(biāo)表數(shù)據(jù)更新或插入的源表或視圖或子查詢； ON：決定MERGE語句執(zhí)行更新操作還是插入操作的條件。對于目標(biāo)表中滿足條件的記錄，則利用源表中的相應(yīng)記錄進(jìn)行更新；而源表中不滿條件的記錄將被插入目標(biāo)表中； where_clause：只有當(dāng)該條件為真時才進(jìn)行數(shù)據(jù)的更新或插入操作； DELETE where_clause：當(dāng)目標(biāo)表中更新后的記錄滿足該條件時，則刪除該

46、記錄。,使用“雇員”表（別名e）作為數(shù)據(jù)源，在該表 的副本“副本_雇員”表（別名c）中插入并更新行。 MERGE INTO 副本_雇員 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 INTO 副本_雇員 c USING 雇員e ON (c.雇員標(biāo)識= e.雇員標(biāo)識) WHEN MATCHED THEN UPDATE SET c.姓氏

47、 = 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)識 100Smith40 103Chang30,MERGE 執(zhí)行后的“副本_雇員” 雇員標(biāo)識 姓氏部門標(biāo)識 100King90 103Hunold60 152Davies50,雇員（來源表） 雇員標(biāo)識 姓氏部門標(biāo)識 100King90 103Hunold60 152Davies50,現(xiàn)有表source_emp和target_emp，表中數(shù)據(jù)如下。利用source_em

48、p表中的數(shù)據(jù)更新target_emp表中的數(shù)據(jù)，對target_emp表中存在的員工信息進(jìn)行更新，對不存在的員工進(jìn)行信息插入。 SELECT * FROM source_emp; EMPNO ENAME DEPTNO - 100 JOAN 10 110 SMITH 20 120 TOM 30,SELECT * FROM target_emp; EMPNO ENAME DEPTNO - 100 MARRY 20 20 JACK 40 MERGE INTO target_emp t USING source_emp s ON (t.empno=s.empno) WHEN MATCHED THEN

49、UPDATE SET t.ename=s.ename,t.deptno=s.deptno WHEN NOT MATCHED THEN INSERT VALUES(s.empno,s.ename,s.deptno)；,SELECT * FROM target_emp; EMPNO ENAME DEPTNO - 100 JOAN 10 20 JACK 40 110 SMITH 20 120 TOM 30,13.3.4刪除數(shù)據(jù),基本語法 刪除單條記錄 刪除多條記錄 帶有子查詢的刪除操作 利用TRUNCATE刪除數(shù)據(jù) TRUNCATE與DELETE區(qū)別,基本語法 DELETE FROM table|v

50、iew WHERE condition 刪除單條記錄 DELETE FROM emp WHERE empno=7844; 刪除多條記錄 DELETE FROM emp WHERE deptno=10; 帶有子查詢的刪除操作 DELETE FROM emp WHERE sal(SELECT sal FROM emp WHERE empno=7900);,利用TRUNCATE刪除數(shù)據(jù) TRUNCATE TABLE table_name TRUNCATE與DELETE區(qū)別 釋放存儲空間 不寫入日志文件，因此執(zhí)行效率較高，但該操作不可回滾。,13.4事務(wù)處理,事務(wù)概述 Oracle事務(wù)處理,13.4.

51、1 事務(wù)概述,原子性（Atomicity）：事務(wù)是數(shù)據(jù)庫的邏輯工作單位，事務(wù)中的所有操作要么都做，要么都不做，不存在第三種情況。 一致性（Consistency）：事務(wù)執(zhí)行的結(jié)果必須是使數(shù)據(jù)庫從一個一致性狀態(tài)轉(zhuǎn)變到另一個一致性狀態(tài)，不存在中間的狀態(tài)。 隔離性（Isolation）：數(shù)據(jù)庫中一個事務(wù)的執(zhí)行不受其他事務(wù)干擾，每個事務(wù)都感覺不到還有其他事務(wù)在并發(fā)執(zhí)行。 持久性（Durability）：一個事務(wù)一旦提交，則對數(shù)據(jù)庫中數(shù)據(jù)的改變是永久性的，以后的操作或故障不會對事務(wù)的操作結(jié)果產(chǎn)生任何影響。,13.4.2 Oracle事務(wù)處理,事務(wù)提交方式 用戶顯式執(zhí)行COMMIT命令 執(zhí)行特定操作時系統(tǒng)

52、自動提交。 說明 當(dāng)事務(wù)提交后，用戶對數(shù)據(jù)庫修改操作的日志信息由日志緩沖區(qū)寫入重做日志文件中，釋放該事務(wù)所占據(jù)的系統(tǒng)資源和數(shù)據(jù)庫資源。此時，其他會話可以看到該事務(wù)對數(shù)據(jù)庫的修改結(jié)果。 當(dāng)執(zhí)行CREATE，ALTER，DROP，RENAME，REVOKE，GRANT，CONNECT，DISCONNECT等命令時，系統(tǒng)將自動提交。,事務(wù)回滾方式 事務(wù)全部回滾 ROLLBACK 事務(wù)部分回滾 SAVEPOINT X ROLLBACK TO X,ROLLBACK TO B;（回滾最后一個INSERT操作） ROLLBACK TO A;（回滾后面的INSERT操作和UPDATE操作） ROLLBACK;

53、（回滾全部操作）,13.5 SQL函數(shù),SQL函數(shù)分類 數(shù)值函數(shù) 字符函數(shù) 日期函數(shù) 轉(zhuǎn)換函數(shù) 其他函數(shù),13.5.1 SQL函數(shù)分類,根據(jù)參數(shù)作用行數(shù)的不同，可以分為： 單行函數(shù) 多行函數(shù) 根據(jù)參數(shù)類型不同，可以分為： 數(shù)值函數(shù) 字符函數(shù) 日期函數(shù) 轉(zhuǎn)換函數(shù) 聚集函數(shù),13.5.2 數(shù)值函數(shù),SELECT sal/22 daysal,round(sal/22,1), trunc(sal/22,1),round(sal/22,-1),trunc(sal/22,-1) FROM emp; DAYSAL ROUND(SAL/22,1)TRUNC(SAL/22,1) ROUND(SAL/22,-1)

54、TRUNC(SAL/22,-1) - 45.4545455 45.5 45.4 50 40 90.9090909 90.9 90.9 90 90 54.5454545 54.5 54.5 50 50 86.3636364 86.4 86.3 90 80 70.4545455 70.5 70.4 70 70 146.590909 146.6 146.5 150 140,SELECT sal,width_bucket(sal,1000,5000,10) FROM emp WHERE deptno=30; SAL WIDTH_BUCKET(SAL,1000,5000,10) - - 1700 2 1

55、350 1 2950 5 6100 11 1050 1,SELECT floor(3.5),ceil(3.5),mod(5,3), remainder(5,3), mod(4,3),remainder(4,3) FROM dual; FLOOR(3.5) CEIL(3.5) MOD(5,3) REMAINDER(5,3) MOD(4,3) REMAINDER(4,3) - 3 4 2 -1 1 1,13.5.3 字符函數(shù),SELECT lpad(abc,5, #) leftpad,rpad(abc,5, #) rightpad, ltrim(abcd, a) lefttrim,rtrim(abcde, e) righttrim, substr(abcd,2,3) substring FROM dual; LEFTPAD RIGHTPAD LEFTTRIM RIGHTTRIM SUBSTRING - - - - - #abc abc# bcd abcd bcd,SELECT concat(concat(ename, s job category is ),job) Job FROM emp WHERE empno=7844; Job - TURNERs job ca