Oracle學習系列3

標籤：

Oracle學習系列3************************************************************************************多表查詢：1，SQL1999文法對多表查詢的支援2，分組統計及統計函數的使用3，子查詢，並結合多表查詢，分組統計做複雜查詢4，資料庫的更新操作5，交易處理和資料庫死結************************************************************************************多表查詢：文法：select { distinct } * | col1 別名1   col2 別名2 ...from  tab1 別名1 ， tab2 別名2 ， tab3 別名3 ，...{where 條件s }{ order by col1 ASC | DESC , col2  ASC | DESC, ...} ;同時查詢emp表和dept表：select *from emp, dept ;   //產生笛卡爾積加入where語句：select *from emp  e,dept  dwhere e.deptno = d.deptno;要求查詢出僱員的編號，姓名，部門編號，部門名稱，部門位置：select e.empno, e.ename, d.deptno, d.dname, d.locfrom emp e, dept dwhere e.deptno=d.deptno;要求查詢出每個僱員的姓名，工作，僱員的直接上級領導的姓名：select e.ename, e.job, m.ename, d.dnamefrom emp e, emp m ,dept dwhere e.mgr = m.empno and e.deptno=d.deptno ;要求查詢出每個僱員的姓名，工資，部門名稱，工資在公司的等級，及其領導的姓名及工資所在公司的等級：select e.ename, e.sal, d.dname, s.grade, m.ename, m.sal, ms.gradefrom emp e, dept d, salgrade s, emp m, salgrade mswhere 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);進一步：按照下面樣式顯示工資等級：1：第五等工資2：第四等工資3：第三等工資4：第二等工資5：第一等工資此時肯定使用decode()函數：select e.ename, e.sal, d.dname, DECODE(s.grade, 1，‘第五等工資‘，2，‘第四等工資‘，3，‘第三等工資‘，4，‘第二等工資‘，1，‘第一等工資‘), m.ename,m.sal,DECODE(ms.grade, 1，‘第五等工資‘，2，‘第四等工資‘，3，‘第三等工資‘，4，‘第二等工資‘，1，‘第一等工資‘), from emp e, dept d, salgrade s, emp m, salgrade mswhere 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);************************************************************************************左、右串連<重點>（+）=    -->右串連， =（+）   -->左串連，  預設select e.empno, e.ename, d.deptno, d.dname, d.locfrom emp e, dept dwhere e.deptno（+）=d.deptno; //表示以dept表為準  。右串連************************************************************************************SQL:1999文法對SQL的支援<瞭解>交叉串連（cross join）:  <產生笛卡爾積>select * from emp corss join dept ;自然串連（natural join）：<自動進行關聯欄位的匹配>select * from emp natural join dept ;USING子句：<直接指定關聯的操作列>select *from emp e join dept d USING(deptno)where deptno=30;ON子句：<使用者自訂串連條件>select *from emp e join dept d  ON(e.deptno=d.deptno)where e.deptno=30 ;左串連（left join）,右串連（right join）:select e.ename, d.deptno, d.dname, d.locfrom emp e right outer join dept don(e.deptno=d.deptno) ;************************************************************************************組函數和分組統計：<重點>count(): //求出全部記錄數max()://求出一組中最大值min()://最小值avg(): //平均值sum():      //求和count()函數：select count(emp) from emp ;  //查詢多少行記錄max()，min()函數：求出所有員工的最低工資select max(sal) ,min(sal) , sum(sal) from emp ;分組統計<group by>：文法：select { distinct } * | col1 別名1   col2 別名2 ...from  tab1 別名1 ， tab2 別名2 ， tab3 別名3 ，...{where 條件s }{group by 分組條件}{ order by col1 ASC | DESC , col2  ASC | DESC, ...} ;求出每個部門的僱員數量，可定按照部門編號劃分：select deptno, count(empno)from empgroup by deptno ;求出每個部門的平均工資：select deptno, avg(sal)from emp group by deptno ;---------------------------------------------------------------------------------select deptno, count(empno) from emp ;// error:不是單組分組函數/**若程式使用了分組函數，條件如下；1.程式中存在group by ,並且指定了分組條件，這樣可以將分組條件一起查詢出來2.若不用分組的話，則只能單獨使用分組函數3.在使用分組函數時，不能出現分組函數和分組條件（group by ）之外的欄位|  */    |  |^select deptno ,empno, count(empno)  //error:empno不是group by 的運算式from empgroup by deptno ;按部門分組，並顯示部門的名稱，及每個部門的員工數：select d.dname, count(e.empno)from dept d, emp ewhere d.deptno = e.deptnogroup by d.dname ;要求顯示出平均工資大於2000的部門編號和平均工資： error：select deptno , avg(sal) from empwhere avg(sal) >2000    //此處不允許使用分組函數group by deptno ;  /**  分組函數只能在分組中使用，不允許出現在where語句中，若現在假設要指定分組條件，則只能通過第二種指令：having，此時SQL文法格式：select { distinct } * | col1 別名1   col2 別名2 ...from  tab1 別名1 ， tab2 別名2 ， tab3 別名3 ，...{where 條件s }{group by 分組條件   { having  分組條件 }  }{ order by col1 ASC | DESC , col2  ASC | DESC, ...} ;*/correct： select deptno, avg(sal)from empgroup by deptno  having avg(sal)>2000 ;顯示非銷售人員工作名稱以及從事統一工作僱員的月工資的總和，並且滿足從事同一工作的僱員的月工資合計大於$5000,輸出結果按月工資的合計升序排列：/**分組簡單的原則：1，只要一列上存在重複的內容才有可能考慮到分組2，分組函數可以嵌套使用，但是嵌套使用時不能再出現group by 後的欄位：ex:求出平均工資最高的部門：error： select  deptno, max(avg(sal))  //不是單組分組函數from empgroup by deptno;correct： select max(avg(sal))from empgroup by deptno ;--------------------------------------------------------------------------------分析：1，顯示全部非銷售人員：job<> ‘salesman‘select * from emp  where job <>‘salesman‘ ;2,按工作分組，同時求出工資的總和：select job,sum(sal) from emp where job<> ‘salesman‘group by job ;3,對分組條件進行限制，工資總和大於500：select job,sum(sal) from emp where job<> ‘salesman‘group by job having sum(sal)>5000 ;4,按升序排序：select job,sum(sal) sufrom emp where job<> ‘salesman‘group by job having sum(sal)>5000 order by su ; */************************************************************************************子查詢：在一個查詢內部還可以包括另一個查詢：分類：單列子查詢：返回的結果是一列的一個內容 （出現幾率最高）單行子查詢：返回多個列，有可能為一條完整的記錄多行子查詢：返回多個記錄格式：select { distinct } * | col1 別名1   col2 別名2 ...from  tab1 別名1 ， tab2 別名2 ， （select { distinct } * | col1 別名1   col2 別名2 ...from  tab1 別名1 ， tab2 別名2 ， tab3 別名3 ，...{where 條件s }{group by 分組條件   { having  分組條件 }  }{ order by col1 ASC | DESC , col2  ASC | DESC, ...} ; ）別名x tab3 別名3 ，...{where 條件s （select { distinct } * | col1 別名1   col2 別名2 ...from  tab1 別名1 ， tab2 別名2 ， tab3 別名3 ，...{where 條件s }{group by 分組條件   { having  分組條件 }  }{ order by col1 ASC | DESC , col2  ASC | DESC, ...} ; ）}{group by 分組條件   { having  分組條件 }  }{ order by col1 ASC | DESC , col2  ASC | DESC, ...} ;   要求查詢出比7654工資高的全部僱員的資訊：   分析:1,7654僱員的工資是多少：select sal from emp where empno=7654 ;2,只要是其他工資大於7654編號僱員的工資，則符合條件：select * from empwhere sal > (select sal from emp where empno=7654;  //子查詢語句處);要求查詢出工資比7654高，同時與7788從事相同工作的全部僱員資訊：查詢出7654僱員的工資:select sal from emp where empno=7654 ;與7788從事相同的工作:select job from emp where empno = 7788;綜合尋找：select * from empwhere sal >(select sal from emp where empno=7654 )and  job =(select job from emp where empno =7788     ) ;要求查詢出工資最低僱員姓名，工作，工資：最低工資：select min(sal) from emp ;查詢所有：select * from empwhere sal =(select min(sal) from emp ) ;要求查詢：部門名稱，部門員工數，部門的平均工資，部門的最低收入的僱員的姓名：1，求出每個部門的員工數量，平均工資select deptno,count(empno) ,avg(sal)from empgroup by  deptno ;2,查出部門的名稱：select d.dname, ed.c, ed.a from dept d, (select deptno,count(empno) ,avg(sal)from empgroup by  deptno     ) edwhere d.deptno=ed.deptno ;3,求出最低收入的僱員姓名：select d.dname, ed.c, ed.a  ,e.enamefrom dept d, (select deptno,count(empno) ,avg(sal)，min(sal) minfrom empgroup by  deptno     ) ed ,emp ewhere d.deptno=ed.deptno and e.sal =ed.min ; //若一個部門中存在兩個最低工資的僱員，則該指令碼會出現錯誤          ------------------------------------------------------子查詢中的三種操作符： IN, ANY, ALL求出每個部門的最低工資的僱員資訊：select * from emp where sal IN (  //指定查詢範圍select min(sal)  from emp          group by deptno) ;=ANY : 與IN功能一樣select * from emp where sal =ANY (  //指定查詢範圍select min(sal)  from emp          group by deptno) ;>ANY：比裡面最小的值要大 select * from emp where sal >ANY (  //指定查詢範圍select min(sal)  from emp          group by deptno) ;<ANY :比裡面最大的值要小select * from emp where sal <ANY (  //指定查詢範圍select min(sal)  from emp          group by deptno) ;==========================>ALL:比最大的值要大 select * from emp where sal >ALL (  //指定查詢範圍select min(sal)  from emp          group by deptno) ;<ALL:比最大的值要小select * from emp where sal <ALL (  //指定查詢範圍select min(sal)  from emp          group by deptno) ;  /**  對於子查詢中，還可以進行多列子查詢，一個子查詢中同時返回多個查詢的列  select  *from empwhere (sal, NVL(comm,-1)) IN(select sa,NVL(comm, -1) from emp where deptno=20 ) ;    */

 

Oracle學習系列3