oracle组函数、多表查询、分页、

平均工资：

SQL> select sum(sal)/count(*) 一, avg(sal) 二 from emp;

        一         二
---------- ----------
2073.21429 2073.21429

空值  组函数会自动滤空

SQL> select  comm from emp;

      COMM
----------

       300
       500

      1400




         0


      COMM
----------




已选择14行。

作为参照：

SQL> select count(*),count(comm) from emp;

  COUNT(*) COUNT(COMM)
---------- -----------
        14           4

可以在组函数中嵌套滤空函数，来屏蔽组函数的滤空功能：

SQL> select count(*),count(nvl(comm,0)) from emp;

  COUNT(*) COUNT(NVL(COMM,0))
---------- ------------------
        14                 14

查询平均工资大于2000的部门：

SQL> ed
已写入 file afiedt.buf

  1  select deptno,avg(sal)
  2      from emp
  3      group by deptno
  4*    having avg(sal)>2000
SQL> /

    DEPTNO   AVG(SAL)
---------- ----------
        20       2175
        10 2916.66667

求10号部门的平均工资：

SQL> ed
已写入 file afiedt.buf

  1  select deptno,avg(sal)
  2      from emp
  3      group by deptno
  4*     having deptno=10
SQL> /

    DEPTNO   AVG(SAL)
---------- ----------
        10 2916.66667

此时改用 where：

SQL> ed
已写入 file afiedt.buf

  1  select deptno,avg(sal)
  2      from emp
  3      group by deptno
  4*     where deptno=10
SQL> /
    where deptno=10
    *
第 4 行出现错误:
ORA-00933: SQL 命令未正确结束

内连接：

语法一：

select *  from 表1 [inner] join 表2 on 表1.字段1=表2.字段1;

语法二：

select *   from 表1,表2  where 表1.字段1=表2.字段1;

外连接：
1、左连接：

select * from 表1 left outer join 表2 on 表1.字段1=表2.字段1;

select * from 表1 left outer join 表2 where 表1.字段1=表2.字段1;

2、右连接：

select *  from 表1 right outer join 表2  on 表1.字段1=表2.字段1;

select *  from 表1 left outer join 表2  where 表1.字段1(+)=表2.字段1;

3、全外连接：

select *   from 表1 full outer join 表2  on 表1.字段1=表2.字段1;

自连接：
举个例子：

自连接(selfjoin)是SQL语句中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。
示例：
　　在oracle的scott的schema中有一个表是emp。在emp中的每一个员工都有自己的mgr(经理)，并且每一个经理自身也是公司的员工，自身也有自己的经理。
但现在我们只有一张emp表。所以我们可以采用自连接。自连接的本意就是将一张表看成多张表来做连接。我们可以这样来写SQL语句:

SQL> select a.ename as 员工,b.ename as 上级 from emp a,emp b where a.mgr=b.empno;

员工                 上级
-------------------- --------------------
FORD                 JONES
SCOTT                JONES
JAMES                BLAKE
TURNER               BLAKE
MARTIN               BLAKE
WARD                 BLAKE
ALLEN                BLAKE
MILLER               CLARK
ADAMS                SCOTT
CLARK                KING
BLAKE                KING

员工                 上级
-------------------- --------------------
JONES                KING
SMITH                FORD

已选择13行。

交叉连接： 表与表之间做笛卡尔积查询！：

select * from 表1 cross join 表2;
        
select * from 表1, 表2;

 分页查询：

1、提取员工表前三行：

SQL> select rownum, emp.* from emp where rownum <4;

    ROWNUM      EMPNO ENAME                JOB                       MGR HIREDATE              SAL    COMM        DEPTNO
---------- ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
         1       7369 SMITH                CLERK                    7902 17-12月-80            800                20
         2       7499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300         30
         3       7521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500         30

2、提取员工表四行之后的：

错误！：

SQL> select rownum,emp.* from emp where rownum >3;

未选定行

正确：

SQL> select * from (select rownum r,emp.* from emp) re where re.r >3;

         R      EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
         4       7566 JONES                MANAGER                  7839 02-4月 -81           2975                20
         5       7654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400         30
         6       7698 BLAKE                MANAGER                  7839 01-5月 -81           2850                30
         7       7782 CLARK                MANAGER                  7839 09-6月 -81           2450                10
         8       7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                20
         9       7839 KING                 PRESIDENT                     17-11月-81           5000                10
        10       7844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0         30
        11       7876 ADAMS                CLERK                    7788 23-5月 -87           1100                20
        12       7900 JAMES                CLERK                    7698 03-12月-81            950                30
        13       7902 FORD                 ANALYST                  7566 03-12月-81           3000                20
        14       7934 MILLER               CLERK                    7782 23-1月 -82           1300                10

已选择11行。

提取工资排行前三的员工：

SQL> select rownum ,e.* from (select * from emp order by sal desc) e where rownum <4;

    ROWNUM      EMPNO ENAME                JOB                       MGR HIREDATE              SAL    COMM        DEPTNO
---------- ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
         1       7839 KING                 PRESIDENT                     17-11月-81           5000                10
         2       7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                20
         3       7902 FORD                 ANALYST                  7566 03-12月-81           3000                20

提取6----10的记录：
1、扫描全表生成伪表，再进行提取分页(表数据多时效率极低)：

SQL> select * from (select rownum r, e.* from emp e ) er where er.r > 5 and er.r <11;

         R      EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
         6       7698 BLAKE                MANAGER                  7839 01-5月 -81           2850                30
         7       7782 CLARK                MANAGER                  7839 09-6月 -81           2450                10
         8       7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                20
         9       7839 KING                 PRESIDENT                     17-11月-81           5000                10
        10       7844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0         30

2、提高效率后的写法，只扫描10行提取分页生成伪表：

SQL> select * from (select rownum r, e.* from emp e where rownum < 11) er where er.r > 5;

         R      EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
         6       7698 BLAKE                MANAGER                  7839 01-5月 -81           2850                30
         7       7782 CLARK                MANAGER                  7839 09-6月 -81           2450                10
         8       7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                20
         9       7839 KING                 PRESIDENT                     17-11月-81           5000                10
        10       7844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0         30

排序加分页：

--a排序
select * from emp order by sal desc
--b生成前10条伪表
select rownum , t1.* 
from (select * from emp order by sal desc ) t1
where rownum <11
--提取6到10
select * from
(select rownum r , t1.* from (select * from emp order by sal desc ) t1
where rownum <11) t2 where t2.r >4

SQL> ed
已写入 file afiedt.buf

  1  select * from
  2  (select rownum r , t1.* from (select * from emp order by sal desc ) t1
  3* where rownum <11) t2 where t2.r >5
  4  ;

         R      EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
         6       7782 CLARK                MANAGER                  7839 09-6月 -81           2450                10
         7       7499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300         30
         8       7844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0         30
         9       7934 MILLER               CLERK                    7782 23-1月 -82           1300                10
        10       7521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500         30

集合使用

1、并集：union

工资大于1500,或者是20部门下的员工：

SQL> select * from emp where sal > 1500
  2  union
  3  select * from emp where deptno =20
  4  ;

     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      7369 SMITH                CLERK                    7902 17-12月-80            800                 20
      7499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300      30
      7566 JONES                MANAGER                  7839 02-4月 -81           2975                 20
      7698 BLAKE                MANAGER                  7839 01-5月 -81           2850                 30
      7782 CLARK                MANAGER                  7839 09-6月 -81           2450                 10
      7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                 20
      7839 KING                 PRESIDENT                     17-11月-81           5000                 10
      7876 ADAMS                CLERK                    7788 23-5月 -87           1100                 20
      7902 FORD                 ANALYST                  7566 03-12月-81           3000                 20

已选择9行。

2、交集：intersect

工资大于1500,并且是20部门下的员工

SQL> select * from emp where sal > 1500
  2  intersect
  3  select * from emp where deptno =20;

     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      7566 JONES                MANAGER                  7839 02-4月 -81           2975                 20
      7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                 20
      7902 FORD                 ANALYST                  7566 03-12月-81           3000                 20

3、差集：minus

1981年入职的普通员工（不包括经理，总裁）：

SQL> select * from emp where to_char(hiredate,'yyyy')='1981'
  2  minus
  3  select * from emp where job in ('MANAGER','PRESIDENT')
  4  ;

     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      7499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300      30
      7521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500      30
      7654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400      30
      7844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0      30
      7900 JAMES                CLERK                    7698 03-12月-81            950                 30
      7902 FORD                 ANALYST                  7566 03-12月-81           3000                 20

已选择6行。