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

来源：互联网发布：互联网java开发编辑：程序博客网时间：2024/05/16 23:20

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

阅读全文

0 0