Oracle表複雜查詢

標籤：多行   name   分享   ora   into   崗位   select   margin   mysql   

 　　　　Oracle表複雜查詢彙總函式

   max(欄位值)  -- 求最大值    

   min(欄位值)  -- 求最小值

  sum(欄位值)  -- 求總和

  avg(欄位值)  -- 求平均值

  count(欄位值)  -- 求個數

 

group by 和 having 字句

group by :  用於對查詢的結果分組統計

having 子句:用於過濾分組顯示的結果

 

案例:

1.顯示每個部門的平均工資和最高工資?

  select avg(sal),max(sal) from emp group by deptno

2.顯示每個部門的每種崗位的平均工資和最低工資?

  select avg(sal),min(sal) from emp group by deptno,job

3.顯示平均工資低於2000的部門號和它的平均工資?

  select avg(sal) deptno  from emp group by deptno  having avg(sal) < 2000;

 

　　多表查詢

 原理:

  笛卡兒積:  在多表查詢的時候，如果不帶任何條件，則會出現笛卡兒積現象。

  規定:  多表查詢的條件至少不能少於表的個數-1；

 案例:

     1.顯示僱員名，僱員工資及所在部門的名字？

        select e.ename,e.sal, d.deptno 

　 　from emp e,dept d 

　　where e.deptno = d.deptno

　　order by d.deptno;

      2.顯示部門號為10的部門名、員工名和工資?
           select d.dname, e.ename, e.sa

　　   l from emp e, dept d

　　 where e.deptno = d.deptnoand d.deptno = 10;

     3.顯示各個員工的姓名，工資，及其工資的層級?

            select emp.ename ,emp.sal ,salgrade.grade

　　　fromemp,salgrade

          where emp.sal between salgrade.losal andsalgrade.hisal;

　　　　自串連

     案例：

          1.顯示“FORD”的上級？

           select * from emp where emp.empno =

　　　　(select mgr from emp where ename = ‘FORD‘);

          2.顯示各員工的姓名和他上級領導的姓名?

            select worker.ename, boss.ename

            from emp worker ,emp boss

            where worker.mgr = boss.empno;

　　　子查詢定義

嵌入到其他sql語句的select語句,也叫巢狀查詢。

單行子查詢

    定義:  返回一行資料的子查詢

案例:

   如何顯示SMITH同一部門的所有員工？

   select * from emp where emp.deptno =

(select deptno  from emp where ename = ‘SMITH’) and ename != ‘SMITH‘;

　　多行子查詢

      定義：返回多行資料的子查詢

 

      案例：

         顯示10號部門的工作相同的員工姓名，工作?

         select ename,job from emp where job in (

　　select job from emp where deptno = 10);

 

      all(  大於最大的):

          如何顯示工資比30號部門高的員工的姓名、工資、部門號

 

　　select ename,sal,deptno from emp where sal > all(select sal from emp where emp.deptno = 30);

        

                        等效於

 

            select ename,sal,deptno from emp where sal > (

　　　　select max(sal) from emp where deptno =30);

      any(  大於最小的):

            如何顯示工資比30號部門任意員工高的員工的姓名、工資、部門號

 　　select ename,sal,deptno from emp where sal > anyl(select sal from emp where emp.deptno = 30);

 　　　　　　等效於

　　select ename,sal,deptno from emp where sal > (

　　select min(sal) from emp where deptno =30);

　　多列子查詢

   　　如何查詢與smith的部門和崗位完全相同的所有僱員

 

   　　select * from emp where (deptno,job) =

 　　(select deptno,job from emp where ename = ‘SMITH’);

 

　　----------------------------------------------------

       注: “(deptno,job) =(select deptno,job ”是有順序的

　　from中的子查詢

  定義:   將select查詢結果當作一個虛表處理

 

  案例:

   顯示高於自己部門的平均工資的員工資訊?

  select t1.ename, t1.sal,t2.myavg from

 emp t1,( select avg(sal) myavg ,deptno from emp group by deptno) t2

  where t1.deptno = t2.deptno and t1.sal > t2.myavg;

 

  顯示每個部門的資訊(編號，名稱) 和人數?

  select t1.dname, t1.deptno,t2.num from dept t1 , (select count(*) num,deptno  from emp group by deptno) t2

   where t1.deptno = t2.deptno(+);

  --------------------------------------------------------

   (+) 在左表示右外串連，在右表示左外串連

　　分頁查詢

mysql:

            select * from 表名 where 條件 limit 從第幾條取,取幾條

 

sql server:

select top 4 * from 表名 where id not  in  (select  top  4  id  from 表名 where 條件) 

---------------------------

排除前4條，再取4條,實際上是5-8條

 

oracle:

    格式:

select * from (select  rownum rn,t1.* from (select * from 表名 [ where 條件]) where rownum <= 末尾) t2 where t2.rn >= 開始;

         ---------------------------

         rownum:偽列，用於顯示資料的行索引。

 

         select * from emp where rownum > = 3

     說明:   因為oracle的行索引(rownum)是從第1開始索引的，所以不能用>=（條件無法成立）,可以用<=。

     解決:   採取截取結果集的方式，將已經查詢好的查詢結果再進行過濾。

     三層: 

        第一層: select * from 表名 [ where 條件]  --放條件,比如排序等

        第二層: select  rownum rn,t1.* from (select * from 表名 [ where 條件]) where rownum <= 末尾  --決定末尾位置

        第三層: select * from (select  rownum rn,t1.* from (select * from 表名 [ where 條件]) where rownum <= 末尾) t2

        where t2.rn >= 開始;    --決定開始位置

 

   拓展:

 

       複製表:  create table mytest  as select empno,ename,sal,comm,deptno from emp;

       插入表：insert into mytest(empno,ename,sal,comm,deptno) select empno,ename,sal,comm,deptno from mytest;

內串連與外串連

 內串連: 笛卡兒積過濾後的串連

 案例:

     select * from emp inner join dept on emp.deptno = dept.deptno;

     等效於

     select *  from  emp,dept where emp.deptno = dept.deptno

 

 外串連：

   案例： 

      測試表

 

     學生表：

   create table stu (id number,name varchar2(32)); 

 

  insert into stu values(1,’tom’);

  insert into stu values(2,’jerry’);

  insert into stu values(3,’jack’);

  insert into stu values(4,’rose’);

 

     成績表

  create table exam(id number,grade number(6,2));

 

insert into exam(1,56);

insert into exam(2,76);

insert into exam(11,86);

 

要求1：顯示所有人的成績，如果沒有成績，也要顯示該人的姓名和id號，成績為空白

    select stu.id,stu.name,exam.grade from stu inner join exam on stu.id = exam.id;

    結果：

 

   

     原因:在笛卡兒積串連之後，相同條件的才會匹配

   左串連：select stu.id,stu.name,exam.grade from stu  left  join exam on stu.id = exam.id;

          另一種寫法: select stu.id,stu.name,exam.grade from stu  , exam where stu.id = exam.id(+);

 

   結果：

 

 

原因:串連後的資料以左邊表為基準，即使對應的右邊沒有資料，也要顯示為空白。

 

要求2：顯示所有成績，如果沒有名字匹配，顯示空

右串連：select stu.id,stu.name,exam.grade from stu  right  join exam on stu.id = exam.id;

          另一種寫法: select stu.id,stu.name,exam.grade from stu  , exam where stu.id(+) = exam.id;

      結果:

 

 

原因:串連後的資料以右邊表為基準，即使對應的左邊沒有資料，也要顯示為空白。

 

小結： 左右外串連可以互為轉換

      比如：顯示所有成績，如果沒有名字匹配，顯示空

      可以寫出：select stu.id,stu.name,exam.grade from exam  left  join stu on stu.id = exam.id;

 

案例3：顯示所有的成績和所有人的名字，如果沒有匹配值，就顯示空

   完全外串連：兩個表查詢，不管有麼有匹配，都顯示。

  select stu.id,stu.name,exam.grade from exam full outer join stu on  stu.id = exam.id;

結果:

 

Oracle表複雜查詢