Oracle数据库知识梳理---续(过滤和排序)

Oracle数据库

4.过滤和排序

where 过滤

查询比81年1月1日入职晚的员工

select * from emp where hiredate > '01-1月-81';

 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
                  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
                  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

查询在1000~2000之间的员工信息

select * from emp where sal between 1000 and 2000;

select * from emp where sal <= 2000 and sal >=1000;


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
           7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
              7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

查询10 20号部门的员工信息

select * from emp where deptno in (10,20);


EMPNO ENAME  JOB MGR HIREDATE  SAL  COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
                  7369 SMITH      CLERK           7902 17-12月-80            800                    20
                  7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
                  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
                  7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

like模糊查询
%代表零个或多个字符(任意个字符);
_代表一个字符.
查询名字S开头的员工信息

select * from emp where ename like 'S%';
注意:单引号中的字符区分大小写...
EMPNO ENAME JOB  MGR HIREDATE   SAL  COMM     DEPTNO
            ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
                  7369 SMITH      CLERK           7902 17-12月-80            800                    20
                  7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20

查询名字含有4个字母的员工

select * from emp where ename like '____'   ;


EMPNO ENAME JOB  MGR HIREDATE  SAL  COMM     DEPTNO
        ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
              7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
              7839 KING       PRESIDENT            17-11月-81           5000                    10
              7902 FORD       ANALYST         7566 03-12月-81           3000                    20   

order by 排序
order by 默认按照排序 从小到大
order by + 列名

查询员工信息,按年薪水排序
select empno, ename, sal, sal*12 年薪 from emp order by 年薪;
EMPNO ENAME             SAL       年薪
---------- ---------- ---------- ----------
      7369 SMITH             800       9600
      7900 JAMES             950      11400
      7876 ADAMS            1100      13200
      7521 WARD             1250      15000
      7654 MARTIN           1250      15000
      7934 MILLER           1300      15600
      7844 TURNER           1500      18000
      7499 ALLEN            1600      19200
      7782 CLARK            2450      29400
      7698 BLAKE            2850      34200
      7566 JONES            2975      35700
      7902 FORD             3000      36000
      7788 SCOTT            3000      36000
      7839 KING             5000      60000
         1 tom_abc          8000      96000

遇到空值问题:

按照奖金,查询员工信息
select empno,ename,comm from emp order by 3 desc null last;
order by 3 desc 根据列表的第三列降序
null last 将空值放到最后
    EMPNO ENAME          COMM
---------- ---------- ----------
    7654 MARTIN           1400
    7521 WARD              500
    7499 ALLEN             300
    7844 TURNER              0
    7782 CLARK
    7788 SCOTT
    7839 KING
    7876 ADAMS
    7900 JAMES
    7902 FORD
    7698 BLAKE
    7566 JONES
    7934 MILLER
    1 tom_abc
    7369 SMITH

order by 后面有多个列,如果多个列中有desc修饰,只作用在最近的一列

按照部门和薪资排序
select * form emp order by empno desc,sal desc;

EMPNO ENAME  JOB  MGR HIREDATE   SAL  COMM     DEPTNO
            ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
                  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
                  7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
                  7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400       30
                  7900 JAMES      CLERK           7698 03-12月-81            950                    30
                  7902 FORD       ANALYST         7566 03-12月-81           3000                    20
                  7788 SCOTT      ANALYST         7566 19-4月 -87           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
                     1 tom_abc                                              8000                    10
                  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