（六）Oracle 的 oracle表查询关键字

参考：http://www.hechaku.com/Oracle/oracle_tables2.html

1、使用逻辑操作符号
问题：查询工资高于500或者是岗位为manager的雇员，同时还要满足他们的姓名首字母为大写的J？

SQL> select * from emp where (sal>500 or job='manager') and ename like 'J%';

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7900 JAMES      CLERK           7698 03-12月-81            950                    30

已用时间:  00: 00: 00.02
SQL>

2、使用order by字句 默认asc 问题：如何按照工资从低到高的顺序显示雇员的信息？

SQL> select * from emp order by sal;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7900 JAMES      CLERK           7698 03-12月-81            950                    30
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
      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
      7839 KING       PRESIDENT            17-11月-81           5000                    10

已选择14行。

已用时间:  00: 00: 00.07
SQL>
问题：按照部门号升序而雇员的工资降序排列
SQL> select * from emp order by deptno,sal desc;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-11月-81           5000                    10
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7900 JAMES      CLERK           7698 03-12月-81            950                    30

已选择14行。

已用时间:  00: 00: 00.10
SQL> select * from emp order by sal,deptno desc;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7900 JAMES      CLERK           7698 03-12月-81            950                    30
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
      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
      7839 KING       PRESIDENT            17-11月-81           5000                    10

已选择14行。

已用时间:  00: 00: 00.08
SQL>

3、使用列的别名排序 问题：按年薪排序 select ename, (sal+nvl(comm,0))*12 "年薪" from emp order by "年薪" asc; 备注：别名需要使用“”号圈中,英文不需要“”号

SQL> select ename,(sal+nvl(comm,0))*12 "年薪" from emp order by "年薪" asc;

ENAME            年薪
---------- ----------
SMITH            9600
JAMES           11400
ADAMS           13200
MILLER          15600
TURNER          18000
WARD            21000
ALLEN           22800
CLARK           29400
MARTIN          31800
BLAKE           34200
JONES           35700
FORD            36000
SCOTT           36000
KING            60000

已选择14行。

已用时间:  00: 00: 00.06

SQL> select ename as "姓名",(sal+nvl(comm,0))*12 "年薪" from emp order by "年薪" desc;

姓名             年薪
---------- ----------
KING            60000
FORD            36000
SCOTT           36000
JONES           35700
BLAKE           34200
MARTIN          31800
CLARK           29400
ALLEN           22800
WARD            21000
TURNER          18000
MILLER          15600
ADAMS           13200
JAMES           11400
SMITH            9600

已选择14行。

已用时间:  00: 00: 00.06
SQL>

4、聚合函数用法：max，min，avg，sum，count 问题：如何显示所有员工中最高工资和最低工资？

select max(sal),min(sal) from emp e;
最高工资那个人是谁？

    错误写法：select ename, sal from emp where sal=max(sal);

    正确写法：select ename, sal from emp where sal=(select max(sal) from emp);

    注意：select ename, max(sal) from emp;这语句执行的时候会报错，说ora-00937：非单组分组函数。因为max是分组函数，而ename不是分组函数.......

    但是select min(sal), max(sal) from emp;这句是可以执行的。因为min和max都是分组函数，就是说：如果列里面有一个分组函数，其它的都必须是分组函数，否则就出错。这是语法规定的
SQL> select max(sal),min(sal) from emp;

  MAX(SAL)   MIN(SAL)
---------- ----------
      5000        800

已用时间:  00: 00: 00.01
SQL> select ename,sal from emp where sal=(select max(sal) from emp);

ENAME             SAL
---------- ----------
KING             5000

已用时间:  00: 00: 00.04

5、问题：如何显示所有员工的平均工资和工资总和？

select sum(e.sal), avg(e.sal)  from emp e;
    查询最高工资员工的名字，工作岗位

    select ename, job, sal from emp e where sal = (select max(sal) from emp);

    显示工资高于平均工资的员工信息

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

  SUM(SAL)   AVG(SAL)
---------- ----------
     29025 2073.21429

已用时间:  00: 00: 00.02


SQL> select ename,job from emp where sal=(select max(sal) from emp);

ENAME      JOB
---------- ---------
KING       PRESIDENT

已用时间:  00: 00: 00.01
SQL> select * from emp where sal>avg(sal);
select * from emp where sal>avg(sal)
                            *
第 1 行出现错误:
ORA-00934: 此处不允许使用分组函数


已用时间:  00: 00: 00.01
SQL> select * from emp where sal>(select avg(sal) from emp);

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      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
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20

已选择6行。

已用时间:  00: 00: 00.03

6、group by 和 having 子句 group by 用于对查询的结果分组统计， having 子句用于限制分组显示结果。 问题：如何显示每个部门的平均工资和最高工资？

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

    （注意：这里暗藏了一点，如果你要分组查询的话，分组的字段deptno一定要出现在查询的列表里面，否则会报错。因为分组的字段都不出现的话，就没办法分组了）

    问题：显示每个部门的每种岗位的平均工资和最低工资？

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

    问题：显示平均工资低于2000的部门号和它的平均工资？

    select avg(sal), max(sal), deptno from emp group by deptno having avg(sal)< 2000;
SQL> select avg(sal),deptno from emp group by deptno;

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

已用时间:  00: 00: 00.02

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

  MIN(SAL)   AVG(SAL)     DEPTNO
---------- ---------- ----------
       800        950         20
      1250       1400         30
      2975       2975         20
       950        950         30
      5000       5000         10
      2850       2850         30
      1300       1300         10
      2450       2450         10
      3000       3000         20

已选择9行。

已用时间:  00: 00: 00.04
SQL> select deptno,avg(sal) from emp group by deptno having avg(sal) < 2000;

    DEPTNO   AVG(SAL)
---------- ----------
        30 1566.66667

已用时间:  00: 00: 00.01

7、对数据分组的总结 1 分组函数只能出现在选择列表、having、order by子句中(不能出现在where中) 2 如果在select语句中同时包含有group by, having, order by

那么它们的顺序是group by, having, order by 3 在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在group by 子句中，否则就会出错。

如select deptno, avg(sal), max(sal) from emp group by deptno having avg(sal) < 2000;这里deptno就一定要出现在group by中

8、多表查询 多表查询是指基于两个和两个以上的表或是视图的查询。在实际应用中，查询单个表可能不能满足你的需求，如显示sales部门位置和其员工的姓名，这种情况下需要使用到dept表和emp表。 1)、问题：显示雇员名，雇员工资及所在部门的名字【笛卡尔集】？ SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno; 规定：多表查询的条件是至少不能少于表的个数N-1才能排除笛卡尔集（如果有N张表联合查询，必须得有N-1个条件，才能避免笛卡尔集合）

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/xiangtingshen/p/10697721.html