ORACLE百例試煉二

   Oracle系列《二》：多表復雜查詢和事務處理

多表查詢應該注意去除笛卡爾積，一般多個表時會為表起個別名

【1】要求查詢雇員的編號、姓名、部門編號、部門名稱及部門位置 

SQL> SELECT   e.empno,e.ename,d.deptno,d.dname,d.loc FROM emp e,dept d 

WHERE e.deptno = d.deptno;

【2】要求查詢每個雇員的姓名、工作、雇員的直接上級領導的姓名(表自關聯) 

SQL> SELECT e.ename,e.job,m.ename FROM emp e,emp m 

WHERE e.mgr = m.empno;

【3】對【2】進行擴充，將雇員所在部門名稱同時列出

 SQL> SELECT e.ename,e.job,m.ename,d.dname FROM emp e,emp m,dept d 

WHERE e.mgr = m.empno AND e.deptno=d.deptno;

【4】查詢每個雇員的姓名、工資、部門名稱，工資在公司的等級(salgrade)，及其領導的姓名所在公司的等級 

<1>先確定工資等級表的內容 

SQL> SELECT * FROM salgrade;

<2>查詢每個雇員的姓名、工資、部門名稱和工資在公司的等級 

SQL> SELECT e.ename,e.sal,d.dname,s.grade FROM emp e,dept d,salgrade s 

WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal;

<3>查詢其領導姓名及工資所在公司的等級 

SQL> SELECT 

e.ename,e.sal,d.dname,s.grade,m.ename,m.sal,ms.grade  FROM emp e,dept d,salgrade s,emp m,salgrade ms 

WHERE e.deptno = d.deptno 

AND e.sal BETWEEN s.losal AND s.hisal 

AND e.mgr = m.empno 

AND m.sal BETWEEN ms.losal AND ms.hisal;

【5】左連接與右連接的概念，"+"在等號左邊表示右連接，反之，左連接

查詢雇員的編號、姓名及其領導的編號、姓名 

SQL> SELECT e.empno,e.ename,m.empno,m.ename FROM emp e,emp m 

WHERE e.mgr = m.empno(+); 就發現將KING的那條記錄也連過來了

SQL1999語法中有如下幾種連接(了解) 

1、交叉連接CROSS JOIN，產生笛卡爾積 

SQL> SELECT * FROM emp CROSS JOIN dept;

2、自然連接NATURAL JOIN,自動進行關聯字段的匹配 

SQL> SELECT * FROM emp NATURAL JOIN dept;

3、使用USING子句，直接關聯操作列 

SQL> SELECT * FROM emp JOIN dept USING(deptno) WHERE deptno=30;

4、使用ON子句，用戶自己編寫的條件 

SQL> SELECT * FORM emp JOIN dept ON(emp.deptno = dept.deptno) WHERE deptno=30; 

5、左連接(左外連接、LEFT (OUTER) JOIN)、右連接(右外連接、RIGHT (OUTER) JOIN)

組函數及分組統計 

1、COUNT():求出全部記錄數 

2、MAX()：求出一組中最大值

3、MIN():求出最小值 

4、AVG()：求出平均值 

5、SUM()：求和

【1】求出每個部門的雇員數量 

SQL> SELECT deptno,count(empno) FROM emp 

GROUP BY deptno;

【2】按部門分組，并顯示部門的名稱，及每個部門的員工數 

SQL> SELECT d.dname,COUNT(e.empno) FROM emp e,dept d 

WHERE e.deptno=d.deptno GROUP BY d.dname;

【3】要求顯示平均工資大于2000的部門編號和平均工資

 SQL> SELECT deptno,AVG(sal) FROM emp 

WHERE AVG(sal)>2000 GROUP BY deptno;

出錯，WHERE子句中不能出現分組函數的條件，要使用HAVING子句 上述語句應該改為如下 

SQL> SELECT deptno,AVG(sal) FROM emp 

GROUP BY deptno 

HAVING AVG(sal)>2000

【4】顯示非銷售人員工作名稱以及從事同一工作雇員的月工資總和，

并且要求從事同一工作的雇員月工資合計大于$5000, 輸出結果按月工資的合計升序排序

<1>按工作分組，求出非銷售人員的月工資總和 

SQL> SELECT job,SUM(sal) FROM emp 

WHERE job<>'SALESMAN' GROUP BY job; 

<2>對分組條件進行限制，然后進行排序,HAVING子句不能使用別名 

SQL> SELECT job,SUM(sal)  totalSal  FROM emp 

WHERE job<>'SALESMAN' GROUP BY job 

HAVING SUM(sal) > 5000 ORDER BY totalSal;

【5】分組函數可以嵌套使用，但是在SELECT列中就不能再出現該分組條件的列名了 

SQL> SELECT deptno,MAX(AVG(sal)) FROM emp 

GROUP BY deptno; 

出錯！修改如下

SQL> SELECT MAX(AVG(sal)) FROM emp 

GROUP BY deptno;

【6】查詢出比7654工資要高的全部雇員的信息 

<1>首先要查詢雇員編號7654的工資 

SQL> SELECT sal FROM emp WHERE empno=7654;

<2>以上述條件的結果最后后續查詢的依據 

SQL> SELECT * FROM emp 

WHERE sal>(SELECT sal FROM emp WHERE empno=7654);

子查詢在操作中分為以下三類： 

1、單列子查詢：返回的結果是一列的內容 

2、單行子查詢：返回多個列，也可能是一條記錄 

3、多行子查詢：返回多個記錄

【1】要求查詢工資比7654高，同時與7788從事相同工作的全部雇員 

SQL> SELECT * FROM emp 

WHERE sal>(SELECT sal FROM emp WHERE empno=7654) 

AND job=(SELECT job FROM emp WHERE empno=7788);

【2】要求查詢 部門名稱、部門員工數、部門平均工資，部門的最低收入雇員的姓名 

<1>查詢部門員工數、部門平均工資 

SQL> SELECT deptno,COUNT(empno),AVG(sal) FROM emp 

GROUP BY deptno; 

<2>查詢部門的名稱，及最低收入雇員姓名，要進行表關聯(子查詢)

SQL> SELECT d.dname,ed.c,ed.a,e.ename FROM dept d,( 

 SELECT deptno,COUNT(empno) c,AVG(sal) a,MIN(sal) min  FROM emp 

 GROUP BY deptno) ed, emp e 

WHERE d.deptno=ed.deptno AND e.sal = ed.min;

若上述存在兩個最低工資的情況，則會出錯，在子查詢中存在以下3種查詢的操作符號 

IN：指定一個查詢范圍，例如查詢每個部門的最低工資(返回值有多個)

 SQL> SELECT * FROM emp 

WHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY deptno);

ANY：=ANY(與IN操作一樣)、>ANY(比最小大)、<ANY(比最大小) 

SQL> SELECT * FROM emp 

WHERE sal <ANY(SELECT MIN(sal) FROM emp GROUP BY deptno);

ALL: >ALL(比最大要大)、<ALL(比最小的小),SQL語句類似上面

多行子查詢

顯示和10號部門從事相同崗位的雇員信息

SQL>select *  from emp where job in (select  job  from emp  where  deptno=10);

數據庫更新操作INSERT、UPDATE、DELETE 

【1】復制一張表，例如復制EMP表為MYEMP 

SQL> CREATE TABLE MYTEMP AS SELECT * FROM emp;  

【2】將編號為7899的雇員的領導取消 

SQL> UPDATE myemp SET mgr=null WHERE empno=7899;

【3】更新時，一定要注意不能批量更新(加上WHERE子句)，多列更新例子如下

SQL> UPDATE myemp SET mgr=null,comm=null WHERE empno IN(7369,8899);

【4】刪除掉全部領取獎金的雇員 

SQL> DELECT FROM emp WHERE comm is NOT NULL;

事務處理 ACID 

A：Atomicity   原子性：事務中的操作或者都完成，或者都取消 

C：Consistency 一致性：事務中的操作保證數據庫中的數據不會出現邏輯上不一致的情況 

I：Isolation   隔離性：當前的事務與其他未完成的事務是隔離的 

D：Durability  持久性：在COMMIT之后，數據永久保存在數據庫中，在此之前，事務的操作都可以回滾

驗證事務過程： 

<1>創建一張臨時表，只包含部門10 

SQL> CREATE TABLE emp10 AS SELECT * FROM emp WHERE empno=10; 

<2>刪除emp10中的7782雇員 

SQL> DELETE FROM emp10 WHERE empno=7782; 

再打開另一個窗口，發現數據還存在，此時如果可以使用以下的兩種命令進行事務處理

COMMIT 和 ROLLBACK 提交事務和回滾事務

SQL查詢練習

【1】列出至少一個員工的所有部門 

SQL> SELECT d.*,ed.cou FROM dept d,( 

                                         SELECT deptno,COUNT(empno) cou FROM emp  GROUP BY deptno 

                                                  HAVING COUNT(empno) > 1

                                                        ) ed 

WHERE d.deptno=ed.deptno;

【2】列出部門名稱和這些部門的員工信息，同時列出那些沒有員工的部門

SQL> SELECT d.deptno,d.dname,e.empno,e.ename 

FROM dept d,emp e  WHERE d.deptno = e.deptno(+);

【3】列出所有"CLERK"(辦事員)的姓名及其部門名稱，部門的人數 

<1>關聯dept表 

SQL> SELECT e.ename,d.dname FROM emp e,dept d 

WHERE e.deptno=d.deptno and e.job='CLERK'; 

<2>使用GROUP BY 完成部門分組人數 

SQL> SELECT e.ename,d.dname,ed.cou FROM emp e,dept d,

(  SELECT deptno,COUNT(empno) cou FROM emp  GROUP BY deptno) ed 

WHERE job='CLERK' AND e.deptno=d.deptno AND ed.deptno=e.deptno;