SQL> select e.empno,e.ename,e.sal,d.dname

  2  from emp e,dept d

  3  where e.deptno=d.deptno;

     EMPNO ENAME             SAL DNAME

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

      7782 CLARK            2450 ACCOUNTING

      7839 KING             5000 ACCOUNTING

      7934 MILLER           1300 ACCOUNTING

      7566 JONES            2975 RESEARCH

      7902 FORD             3000 RESEARCH

      7876 ADAMS            1100 RESEARCH

      7369 SMITH             800 RESEARCH

      7788 SCOTT            3000 RESEARCH

      7521 WARD             1250 SALES

      7844 TURNER           1500 SALES

      7499 ALLEN            1600 SALES

     EMPNO ENAME             SAL DNAME

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

      7900 JAMES             950 SALES

      7698 BLAKE            2850 SALES

      7654 MARTIN           1250 SALES

连接条件可以避免使用笛卡尔乘积的全集

多表查询

1、什么是多表查询？

2、笛卡尔集

3、等值连接

4、不等值连接

5、外连接

6、自连接

7、层次查询

ttitle col 15 '我的报表' col 35 sql.pno

col deptno heading 部门号

col job heading 职位

col sum(sal) heading 工资总额

break on deptno skip 1

ttitle col 15 '我的报表' col 35 sql.pno

表示标题前空15列，后空35列

sql.pno 表示报表的第一页，第二页

break on deptno skip 1

get sql文件，读取sql文件

@sql文件，执行sql文件

*******************************************************

SQL> get e:\temp\report.sql

  1  ttitle col 15 '我的报表' col 35 sql.pno

  2  col deptno heading 部门号

  3  col job heading 职位

  4  col sum(sal) heading 工资总额

  5* break on deptno skip 1

SQL> @e:\temp\report.sql

SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

              我的报表                     1

    部门号 职位        工资总额

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

        10 CLERK           1300

           MANAGER         2450

           PRESIDENT       5000

                           8750

        20 CLERK           1900

           ANALYST         6000

           MANAGER         2975

                          10875

        30 CLERK            950

           MANAGER         2850

           SALESMAN        5600

                           9400

                          29025

已选择13行。

SQL> set pagesize 10

SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

              我的报表                     1

    部门号 职位        工资总额

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

        10 CLERK           1300

           MANAGER         2450

           PRESIDENT       5000

                           8750

        20 CLERK           1900

              我的报表                     2

    部门号 职位        工资总额

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

        20 ANALYST         6000

           MANAGER         2975

                          10875

        30 CLERK            950

           MANAGER         2850

              我的报表                     3

    部门号 职位        工资总额

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

        30 SALESMAN        5600

                           9400

                          29025

已选择13行。

select deptno,job,sum(salary) from users group by deptno,job;

+

select deptno,sum(sal) from emp group by deptno

+

select sum(sal) from emp

=

select deptno,job,sum(sal) from emp group by rollup(deptno,job);

group by rollup---叫group by语句的增强------在报表中用的比较多

group by rollup(a,b)

等价于

group by a,b

+

group by a

+

group by null

break on deptno skip 2

相同部门号仅仅显示一次，

不同的部门号之间跳过2行

set pagesize 30 ---让每列显示30条数据

*******************************************************

SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

    DEPTNO JOB         SUM(SAL)

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

        10 CLERK           1300

        10 MANAGER         2450

        10 PRESIDENT       5000

        10                 8750

        20 CLERK           1900

        20 ANALYST         6000

        20 MANAGER         2975

        20                10875

        30 CLERK            950

        30 MANAGER         2850

        30 SALESMAN        5600

    DEPTNO JOB         SUM(SAL)

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

        30                 9400

                          29025

已选择13行。

SQL> break on deptno skip 2

SQL> set pagesize 30

SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

    DEPTNO JOB         SUM(SAL)

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

        10 CLERK           1300

           MANAGER         2450

           PRESIDENT       5000

                           8750

        20 CLERK           1900

           ANALYST         6000

           MANAGER         2975

                          10875

        30 CLERK            950

           MANAGER         2850

           SALESMAN        5600

                           9400

                          29025

已选择13行。

分组函数的嵌套

示例：求部门平均工资的最大值

*******************************************************

SQL> select max(avg(sal))

  2  from emp

  3  group by deptno;

MAX(AVG(SAL))

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

   2916.66667

select deptno,avg(sal)

from emp

group by deptno

order by avg(sal);

select deptno,avg(sal) 平均工资

from emp

group by deptno

order by 平均工资;

select deptno,avg(sal) 平均工资

from emp

group by deptno

order by 2;

2指得是第二列;

默认是升序的，降序加上desc

-- a命令 append，指在上行命令下追加

tips:a命令后面的空格一定是2个或者2个以上

"/"---表示执行这条sql语句

******************************************************************************

SQL> select deptno,avg(sal)

  2  from emp

  3  group by deptno

  4  order by avg(sal);

    DEPTNO   AVG(SAL)

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

        30 1566.66667

        20       2175

        10 2916.66667

SQL> select deptno,avg(sal) 平均工资

  2  from emp

  3  group by deptno

  4  order by 平均工资;

    DEPTNO   平均工资

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

        30 1566.66667

        20       2175

        10 2916.66667

SQL> select deptno,avg(sal) 平均工资

  2  from emp

  3  group by deptno

  4  order by 2;

    DEPTNO   平均工资

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

        30 1566.66667

        20       2175

        10 2916.66667

SQL> a  desc

  4* order by 2 desc

SQL> /

    DEPTNO   平均工资

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

        10 2916.66667

        20       2175

        30 1566.66667

having子句的语法

group by之后再加上having来过滤数据

where和having的区别

1.不能在where子句中使用组函数

2.可以在having子句中使用组函数

3.having 先分组再过滤；where先过滤再分组

----where使得分组记录数据大大降低，从而提高了sql的效率

查询10号部门的平均工资

select deptno,avg(sal)

from emp

group by deptno

having deptno=10;

select deptno,avg(sal)

from emp

where deptno=10

group by deptno;

*******************************************************

SQL> select deptno,avg(sal)

  2  from emp

  3  group by deptno

  4  having avg(sal) > 2000;

    DEPTNO   AVG(SAL)

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

        20       2175

        10 2916.66667

SQL> select deptno,avg(sal)

  2  from emp

  3  group by deptno

  4  having deptno=10;

    DEPTNO   AVG(SAL)

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

        10 2916.66667

SQL>    select deptno,avg(sal)

  2  from emp

  3  where deptno=10

  4  group by deptno;

    DEPTNO   AVG(SAL)

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

        10 2916.66667

在select列表中所有未包含在组函数中的列都应包含在group by子句中

换句话说:包含在group by子句中的列不必包含在select列表中

select a,b,c,组函数(x)

from table

group by a,b,c;

上面select后面a，b，c一个都不能少；

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

select avg(sal) from emp group by deptno;

按照部门号，不同的职位对员工工资进行分组

select deptno,job,sum(sal)

from emp

group by deptno,job;

加上排序

select deptno,job,sum(sal)

from emp

group by deptno,job

order by deptno;

*******************************************************

SQL> select deptno,avg(sal) from emp group by deptno;

    DEPTNO   AVG(SAL)

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

        30 1566.66667

        20       2175

        10 2916.66667

SQL>

SQL> select avg(sal) from emp group by deptno;

  AVG(SAL)

----------

1566.66667

      2175

2916.66667

SQL> select deptno,job,sum(sal)

  2  from emp

  3  group by deptno,job;

    DEPTNO JOB         SUM(SAL)

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

        20 CLERK           1900

        30 SALESMAN        5600

        20 MANAGER         2975

        30 CLERK            950

        10 PRESIDENT       5000

        30 MANAGER         2850

        10 CLERK           1300

        10 MANAGER         2450

        20 ANALYST         6000

已选择9行。

SQL> select deptno,job,sum(sal)

  2  from emp

  3  group by deptno,job

  4  order by deptno;

    DEPTNO JOB         SUM(SAL)

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

        10 CLERK           1300

        10 MANAGER         2450

        10 PRESIDENT       5000

        20 ANALYST         6000

        20 CLERK           1900

        20 MANAGER         2975

        30 CLERK            950

        30 MANAGER         2850

        30 SALESMAN        5600

已选择9行。

分组函数会自动忽略空值

NVL函数使分组函数无法忽略空值

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

nvl(comm,0) 

如果comm不为空，返回comm

如果是空，返回0，就不会返回空

*******************************************************

SQL> select sum(sal)/count(*) 一,sum(sal)/count(sal) 二,avg(sal) 三

  2  from emp;

        一         二         三

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

2073.21429 2073.21429 2073.21429

SQL> select sum(comm)/count(*) 一,sum(comm)/count(comm) 二,avg(comm) 三

  2  from emp;

        一         二         三

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

157.142857        550        550

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

  COUNT(*) COUNT(COMM)

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

        14           4

SQL> select * from emp;

     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

      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30

      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20

      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30

      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

      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30

      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO

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

      7900 JAMES      CLERK           7698 03-12月-81            950                    30

      7902 FORD       ANALYST         7566 03-12月-81           3000                    20

      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

已选择14行。

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

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

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

        14                 14

wm_concat 行转列

select deptno 部门号,wm_concat(ename) 部门中员工的姓名 from emp group by deptno;

wm_concat(ename) 将部门中员工的姓名用，拼接

col 部门中员工的姓名 for a60

把列宽设置为60的宽度

************************************************************

SQL> select deptno,wm_concat(ename) from emp group by deptno;

    DEPTNO

----------

WM_CONCAT(ENAME)

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

        10

CLARK,MILLER,KING

        20

SMITH,FORD,ADAMS,SCOTT,JONES

        30

ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD

SQL> set linesize 200;

SQL> col 部门中员工的姓名 for a60

SQL> select deptno 部门号,wm_concat(ename) 部门中员工姓名 from emp group by deptno;

    部门号

----------

部门中员工姓名

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

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

        10

CLARK,MILLER,KING

        20

SMITH,FORD,ADAMS,SCOTT,JONES

        30

ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD

SQL> select deptno 部门号,wm_concat(ename) 部门中员工的姓名 from emp group by deptno;

    部门号 部门中员工的姓名

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

        10 CLARK,MILLER,KING

        20 SMITH,FORD,ADAMS,SCOTT,JONES

        30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD

SQL> select * from salgrade;

     GRADE      LOSAL      HISAL

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

         1        700       1200

         2       1201       1400

         3       1401       2000

         4       2001       3000

         5       3001       9999

SQL> select avg(sal),sum(sal) from emp;

  AVG(SAL)   SUM(SAL)

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

2073.21429      29025

SQL> select min(sal),max(sal) from emp;

  MIN(SAL)   MAX(SAL)

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

       800       5000

SQL> select count(*) from emp;

  COUNT(*)

----------

        14

SQL> select count(empno) from emp;

COUNT(EMPNO)

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

          14

SQL> select count(deptno) from emp;

COUNT(DEPTNO)

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

           14

SQL> select depno from emp;

select depno from emp

       *

第 1 行出现错误:

ORA-00904: "DEPNO": 标识符无效

SQL> select deptno from emp;

    DEPTNO

----------

        20

        30

        30

        20

        30

        30

        10

        20

        10

        30

        20

    DEPTNO

----------

        30

        20

        10

已选择14行。

SQL> select count(distinct deptno) from emp;

COUNT(DISTINCTDEPTNO)

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

                    3

scott 

下有个员工表emp

host cls 清屏

实现oracle的分页查询一定需要嵌套子查询来实现

和mysql 的 limit方式不同---

select rownum,r,empno,ename,sal

from (select rownum r,empno,ename,sal

from (select rownum,empno,ename,sal from emp order by sal desc) e1

where rownum<=8) e2

where r>=5;

子查询注意的问题