ORACLE百例試煉二

   Oracle系列《二》：多表復(fù)雜查詢和事務(wù)處理

多表查詢應(yīng)該注意去除笛卡爾積，一般多個(gè)表時(shí)會(huì)為表起個(gè)別名

【1】要求查詢雇員的編號(hào)、姓名、部門(mén)編號(hào)、部門(mén)名稱及部門(mén)位置 

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

WHERE e.deptno = d.deptno;

【2】要求查詢每個(gè)雇員的姓名、工作、雇員的直接上級(jí)領(lǐng)導(dǎo)的姓名(表自關(guān)聯(lián)) 

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

WHERE e.mgr = m.empno;

【3】對(duì)【2】進(jìn)行擴(kuò)充，將雇員所在部門(mén)名稱同時(shí)列出

 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】查詢每個(gè)雇員的姓名、工資、部門(mén)名稱，工資在公司的等級(jí)(salgrade)，及其領(lǐng)導(dǎo)的姓名所在公司的等級(jí) 

<1>先確定工資等級(jí)表的內(nèi)容 

SQL> SELECT * FROM salgrade;

<2>查詢每個(gè)雇員的姓名、工資、部門(mén)名稱和工資在公司的等級(jí) 

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>查詢其領(lǐng)導(dǎo)姓名及工資所在公司的等級(jí) 

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】左連接與右連接的概念，"+"在等號(hào)左邊表示右連接，反之，左連接

查詢雇員的編號(hào)、姓名及其領(lǐng)導(dǎo)的編號(hào)、姓名 

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

WHERE e.mgr = m.empno(+); 就發(fā)現(xiàn)將KING的那條記錄也連過(guò)來(lái)了

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

1、交叉連接CROSS JOIN，產(chǎn)生笛卡爾積 

SQL> SELECT * FROM emp CROSS JOIN dept;

2、自然連接NATURAL JOIN,自動(dòng)進(jìn)行關(guān)聯(lián)字段的匹配 

SQL> SELECT * FROM emp NATURAL JOIN dept;

3、使用USING子句，直接關(guān)聯(lián)操作列 

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

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

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

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

組函數(shù)及分組統(tǒng)計(jì) 

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

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

3、MIN():求出最小值 

4、AVG()：求出平均值 

5、SUM()：求和

【1】求出每個(gè)部門(mén)的雇員數(shù)量 

SQL> SELECT deptno,count(empno) FROM emp 

GROUP BY deptno;

【2】按部門(mén)分組，并顯示部門(mén)的名稱，及每個(gè)部門(mén)的員工數(shù) 

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

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

【3】要求顯示平均工資大于2000的部門(mén)編號(hào)和平均工資

 SQL> SELECT deptno,AVG(sal) FROM emp 

WHERE AVG(sal)>2000 GROUP BY deptno;

出錯(cuò)，WHERE子句中不能出現(xiàn)分組函數(shù)的條件，要使用HAVING子句 上述語(yǔ)句應(yīng)該改為如下 

SQL> SELECT deptno,AVG(sal) FROM emp 

GROUP BY deptno 

HAVING AVG(sal)>2000

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

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

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

SQL> SELECT job,SUM(sal) FROM emp 

WHERE job<>'SALESMAN' GROUP BY job; 

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

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

WHERE job<>'SALESMAN' GROUP BY job 

HAVING SUM(sal) > 5000 ORDER BY totalSal;

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

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

GROUP BY deptno; 

出錯(cuò)！修改如下

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

GROUP BY deptno;

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

<1>首先要查詢雇員編號(hào)7654的工資 

SQL> SELECT sal FROM emp WHERE empno=7654;

<2>以上述條件的結(jié)果最后后續(xù)查詢的依據(jù) 

SQL> SELECT * FROM emp 

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

子查詢?cè)诓僮髦蟹譃橐韵氯悾?nbsp;

1、單列子查詢：返回的結(jié)果是一列的內(nèi)容 

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

3、多行子查詢：返回多個(gè)記錄

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

SQL> SELECT * FROM emp 

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

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

【2】要求查詢 部門(mén)名稱、部門(mén)員工數(shù)、部門(mén)平均工資，部門(mén)的最低收入雇員的姓名 

<1>查詢部門(mén)員工數(shù)、部門(mén)平均工資 

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

GROUP BY deptno; 

<2>查詢部門(mén)的名稱，及最低收入雇員姓名，要進(jìn)行表關(guān)聯(lián)(子查詢)

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;

若上述存在兩個(gè)最低工資的情況，則會(huì)出錯(cuò)，在子查詢中存在以下3種查詢的操作符號(hào) 

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

 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語(yǔ)句類似上面

多行子查詢

顯示和10號(hào)部門(mén)從事相同崗位的雇員信息

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

數(shù)據(jù)庫(kù)更新操作INSERT、UPDATE、DELETE 

【1】復(fù)制一張表，例如復(fù)制EMP表為MYEMP 

SQL> CREATE TABLE MYTEMP AS SELECT * FROM emp;  

【2】將編號(hào)為7899的雇員的領(lǐng)導(dǎo)取消 

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

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

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

【4】刪除掉全部領(lǐng)取獎(jiǎng)金的雇員 

SQL> DELECT FROM emp WHERE comm is NOT NULL;

事務(wù)處理 ACID 

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

C：Consistency 一致性：事務(wù)中的操作保證數(shù)據(jù)庫(kù)中的數(shù)據(jù)不會(huì)出現(xiàn)邏輯上不一致的情況 

I：Isolation   隔離性：當(dāng)前的事務(wù)與其他未完成的事務(wù)是隔離的 

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

驗(yàn)證事務(wù)過(guò)程： 

<1>創(chuàng)建一張臨時(shí)表，只包含部門(mén)10 

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

<2>刪除emp10中的7782雇員 

SQL> DELETE FROM emp10 WHERE empno=7782; 

再打開(kāi)另一個(gè)窗口，發(fā)現(xiàn)數(shù)據(jù)還存在，此時(shí)如果可以使用以下的兩種命令進(jìn)行事務(wù)處理

COMMIT 和 ROLLBACK 提交事務(wù)和回滾事務(wù)

SQL查詢練習(xí)

【1】列出至少一個(gè)員工的所有部門(mén) 

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】列出部門(mén)名稱和這些部門(mén)的員工信息，同時(shí)列出那些沒(méi)有員工的部門(mén)

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

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

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

<1>關(guān)聯(lián)dept表 

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

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

<2>使用GROUP BY 完成部門(mén)分組人數(shù) 

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;