【正文】 000 5000 20 CLERK 1900 950 20 ANALYST 6000 3000 20 MANAGER 2975 2975 30 CLERK 950 950 30 MANAGER 2850 2850 30 SALESMAN 5600 1400 使用 HAVING子句限定分組函數(shù)結(jié)果值 ? 前面我們講過，分組函數(shù)不能寫在 WHERE子句中，如果要對分組函數(shù)結(jié)果值進(jìn)行限定，可以用 HAVING子句 SQL SELECT DEPTNO,JOB,SUM(SAL),AVG(SAL) FROM EMP GROUP BY DEPTNO,JOB HAVING AVG(SAL)2021。 DEPTNO JOB SUM(SAL) AVG(SAL) 10 MANAGER 2450 2450 10 PRESIDENT 5000 5000 20 ANALYST 6000 3000 20 MANAGER 2975 2975 30 MANAGER 2850 2850 練習(xí) ? 練習(xí)分組函數(shù)的使用 ? 練習(xí) GROUP BY子句 ? 練習(xí) HAVING子句 ? 掌握分組函數(shù)中易犯的錯誤 6 子查詢 使用子查詢解決問題 ? 假設(shè)想知道誰的工資高于 Jones。 為了解決這個問題 ， 必須執(zhí)行兩個查詢：第一個查詢查到了 Jones的工資 ， 第二個查詢查找高于這個工資的人 。 ? 可以將這兩個查詢組合起來 ， 將一個查詢放在另一個中來解決此問題 。 一個內(nèi)部的查詢或子查詢返回一個值 ， 此值被外部查詢或主查詢使用 。 使用子查詢等價于執(zhí)行兩個順序查詢 。 第一個查詢的結(jié)果作為第二個查詢檢索的值 。 子查詢語法 ? 子查詢在主查詢前執(zhí)行一次 ? 主查詢使用子查詢的結(jié)果 SELECT select_list FROM tablename WHERE expr_operator (SELECTselect_list FROMtable)。 子查詢舉例 SQL select ename,sal from emp where sal(select sal from emp where ename=39。JONES39。)。 ENAME SAL SCOTT 3000 KING 5000 FORD 3000 子查詢類別 ? 單行子查詢：內(nèi)部 SELECT命令返回一條記錄 ? 多行子查詢：內(nèi)部 SELECT命令返回多條記錄 ? 多列子查詢：內(nèi)部 SELECT命令返回多個數(shù)據(jù)列 子查詢使用規(guī)則 ? 子查詢要用括號括起來 ? 將子查詢放在比較運算符的右邊 ? 子查詢中不要加 ORDER BY子句 ? 對單行子查詢使用單行運算符 （ 如 =,)， 也可以使用多行運算符 ? 對多行子查詢使用多行運算符， IN， ANY， ALL屬于多行運算符 子查詢?nèi)菀追傅腻e誤 1： ? 用單行操作符，但是子查詢返回多行 SQL select ename,sal from emp where sal = (select sal from emp )。 select ename,sal from emp where sal = (select sal from emp ) * ERROR at line 1: ORA01427: singlerow subquery returns more than one row 子查詢?nèi)菀追傅腻e誤 2： ? 另一個常見的錯誤是內(nèi)層查詢沒有返回記錄 SQL select ename,sal from emp where sal = (select sal from emp where ename=39。NOTHING39。)。 no rows selected 多行子查詢舉例 ? 查找個部門工資最高的員工 SQL SELECT ENAME,DEPTNO,SAL FROM EMP WHERE SAL IN(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO)。 ENAME DEPTNO SAL KING 10 5000 FORD 20 3000 SCOTT 20 3000 BLAKE 30 2850 在多行子查詢中使用 ANY ? ANY運算子查詢返回每個值。上面例子中返回工資低于任何 CLERK的員工，并且他不是 CLERK。 CLERK工資最高值為 $1300，上面的例子返回工資低于 $1300，且非 CLERK的員工， ” ANY”意味著小于最大值， ” ANY”意味著大于最小值， ” =ANY”等于 IN SQL SELECT ename ,deptno, job,sal 2 FROM emp WHERE sal ANY (SELECT sal FROM emp 3 WHERE job =39。CLERK39。)AND job39。CLERK39。 ENAME DEPTNO JOB SAL WARD 30 SALESMAN 1250 MARTIN 30 SALESMAN 1250 7 操作數(shù)據(jù) 課程目標(biāo) ? 在這一節(jié)課里 ， 你將學(xué)會如何往表中插入記錄 ， 如何修改和刪除表中的記錄 。另外 ， 你還將學(xué)會如何用 COMMIT 和ROLLBACK語句控制事務(wù) 。 ? 數(shù)據(jù)操作語言 (DML) ? 當(dāng)你想在數(shù)據(jù)庫中增加 、 修改或刪除數(shù)據(jù)時 ，你就要執(zhí)行 DML語句 。 由一組 DML語句組成的邏輯工作單元叫做一個事務(wù) 。 ? DML語句可以 SQL*PLUS或 SQL*DBA中直接執(zhí)行 。也可以在象 Developer/202 TOAD這樣的工具中執(zhí)行 ， 還可以在 SQL預(yù)編譯程序中執(zhí)行 。 ? 數(shù)據(jù)操作語言主要有 INSERT,UPDATE和 DELETE INSERT語句 ? table：表名 ? column：列名 ? value：列的值 ? 通過 INSERT語句在表中增加一條新記錄 。如果省略表后面的列，那么要在 VALUES后面按照表的順序指定所有的列的值 INSERT INTO table[(column [, column… ])] VALUES (value 〔 ， value… 〕 )； INSERT舉例 : ? 如果表中的列沒有在 VALUES中列出來，則系統(tǒng)分配給這些列默認(rèn)值。沒有默認(rèn)值則為空。如果該列不能為空，則插入語句不成功。如果違反約束條件，插入語句同樣不能成功。 SQL INSERT INTO dept(deptno,dname,loc) VALUES(50,39。DEVELOPMENT39。,39。BEIJING39。)。 1 row created. 從其它表中拷貝數(shù)據(jù) SQL INSERT INTO MANAGERS(empno,ename,sal,hiredate) SELECT empno,ename,sal,hiredate FROM emp WHERE job=39。MANAGER39。 3 rows created. UPDATE語句 ? 用 UPDATE語句可以一次修改一條或者多條記錄。如果沒有WHERE條件，則所有的 SET后面的列的值都會被更新。 UPDATE table SET column = value [, column = value] [WHEREcondition]。 UPDATE舉例 ? 給 EMP表中部門編號是 30的員工工資增加 20% SQL UPDATE EMP SET SAL=SAL* WHERE DEPTNO=30。 6 rows updated. DELETE語句 ? 用 DELETE語句從表中刪除數(shù)據(jù)。如果不加 WHERE條件，那么所有的記錄都將被刪除。 DELETE [FROM] table [WHEREcondition]。 從表中刪除指定的記錄 SQL DELETE FROM DEPT WHERE DNAME=39。DEVELOPMENT39。 1 row deleted. 提交所作的修改 ? 用 INSERT、 UPDATE和 DELETE語句對數(shù)據(jù)進(jìn)行改動后，數(shù)據(jù)并沒有真正的保存。這些改動的數(shù)據(jù)別的會話中看不到。為了真的保存，需要執(zhí)行 COMMIT命令。 一些隱式的 COMMIT ? 如果在 SQL*PLUS中執(zhí)行了 DML語句，然后雖然沒有執(zhí)行 COMMIT，但是如果存在如下情形之一， Oralce會執(zhí)行一個隱式的 COMMIT，一定要注意： SQL*PLUS DDL語句，例如創(chuàng)建了一個表 取消所做的修改 ? 可以用 ROLLBACK命令取消所做的修改 隱式的 ROLLBACK ? 如果修改數(shù)據(jù)后，沒有 COMMIT，也沒有提交，那么下列情況下 Oracle會取消所做的修改： DML語句應(yīng)該注意的問題 ? 一定要加合適的 WHERE條件，避免錯誤修改數(shù)據(jù) ? 執(zhí)行 DML后因該盡快的顯式執(zhí)行COMMIT或者 ROLLBACK，因為被改動的數(shù)據(jù)別的用戶是不能修改的，長時間不結(jié)束事務(wù)會增加死鎖的可能性 COMMIT舉例 SQL UPDATE emp SET deptno = 10 WHERE deptno = 7782。 1 row updated. SQL COMMIT。 Commit plete. ROLLBACK舉例 SQL SELECT COUNT(*) FROM EMP。 COUNT(*) 16 SQL DELETE FROM EMP。 已刪除 16行 。 SQL SELECT COUNT(*) FROM EMP。 COUNT(*) 0 SQL ROLLBACK。 回退已完成 。 SQL SELECT COUNT(*) FROM EMP。 COUNT(*) 16 SQL 8 創(chuàng)建并管理表 常用的數(shù)據(jù)庫對象 ? 表 （ Table） ：存儲數(shù)據(jù) ? 視圖 （ View） ：一個或幾個表數(shù)據(jù)的子集 ? 序列 （ sequence） ：生成主要健值 ? 索引 （ index） ：加快某些查詢速度 ? 同義詞 （ synonym） ：給出對象的另一個可選用名字 命名規(guī)則 ? 數(shù)據(jù)庫表和列的命名是根據(jù) Oracle數(shù)據(jù)庫對象的標(biāo)準(zhǔn)命名規(guī)則進(jìn)行的 。 ? 表名和列必須由字母開頭 ， 最多有 30個字符長 。 ? 名字只能包含這些字符 ， 大小字母 AZ、 小寫字母 az、數(shù)字 0 下劃線 (_)、 $、 和 。 ? 在同一個數(shù)據(jù)庫用戶中表名不能重復(fù) 。 ? 名字不能是 Oracle的保留字 。 創(chuàng)建表語法 ? tablename:要創(chuàng)建的表名 ? colname:列名 ? datatype：數(shù)據(jù)類型 CREATE TABLE tablename (colname datatype[,colname1 datatype][… ]) 創(chuàng)建表舉例 ? 這個例子中，我們創(chuàng)建一個 notebook表，包括了 ID、 NAME和 PHONE三個字段。其中 ID是主鍵，也就是說 ,ID不能重復(fù) ? 命令一旦執(zhí)行就會被自動提交 SQL CREATE TABLE notebook(id number(4) primary key, name varchar2(30),phone varchar2(30))。