Oracle过滤与排序

来源：互联网发布：jquery.eraser.js 编辑：程序博客网时间：2024/05/16 13:58

SQL> --查询部门号为10的员工信息
SQL> select *
2 from emp
3 where deptno=10;

     EMPNO ENAME           JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- --------------- --------- ---------- -------------- ----- ---------- ----------
      7782 CLARK           MANAGER         7839 09-6月 -81      2450                    10
      7839 KING            PRESIDENT            17-11月-81      5000                    10
      7934 MILLER          CLERK           7782 23-1月 -82      1300                    10

已选择3行。

SQL> --查询名字叫KING的员工信息
SQL> select *
2 from emp
3 where ename='king';

未选定行

SQL> ed
已写入 file afiedt.buf

1 select *
2 from emp
3* where ename='KING'
SQL> /

     EMPNO ENAME           JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- --------------- --------- ---------- -------------- ----- ---------- ----------
      7839 KING            PRESIDENT            17-11月-81      5000                    10

已选择 1 行。

SQL> --日期：格式问题
SQL> --查询入职时间是1981-11-17的员工信息
SQL> select *
2 from emp
3 where hiredate='1981-11-17';
where hiredate='1981-11-17'
*
第 3 行出现错误:
ORA-01861: 文字与格式字符串不匹配

SQL> --通过两种方式得知系统默认的日期格式:
SQL> --1. sysdate
SQL> select sysdate from dual;

SYSDATE
--------------
11-6月 -11

已选择 1 行。

SQL> select *
2 from emp
3 where hiredate='17-11月-81';

已选择 1 行。

SQL> --2. 通过查询v$nls_parameters: Oracle提供的一个数据字典，包含一些配置的参数信息
SQL> select * from v$nls_parameters;

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_LANGUAGE
SIMPLIFIED CHINESE

NLS_TERRITORY
CHINA

NLS_CURRENCY
￥

NLS_ISO_CURRENCY
CHINA

NLS_NUMERIC_CHARACTERS
.,

NLS_CALENDAR
GREGORIAN

NLS_DATE_FORMAT
DD-MON-RR

NLS_DATE_LANGUAGE
SIMPLIFIED CHINESE

NLS_CHARACTERSET
ZHS16GBK

NLS_SORT
BINARY

NLS_TIME_FORMAT
HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY
￥

NLS_NCHAR_CHARACTERSET
AL16UTF16

NLS_COMP
BINARY

NLS_LENGTH_SEMANTICS
BYTE

NLS_NCHAR_CONV_EXCP

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
FALSE

已选择19行。

SQL> col parameter for a20
SQL> /

PARAMETER            VALUE
-------------------- ----------------------------------------------------------------
NLS_LANGUAGE         SIMPLIFIED CHINESE
NLS_TERRITORY        CHINA
NLS_CURRENCY         ￥
NLS_ISO_CURRENCY     CHINA
NLS_NUMERIC_CHARACTE .,
RS

NLS_CALENDAR         GREGORIAN
NLS_DATE_FORMAT      DD-MON-RR
NLS_DATE_LANGUAGE    SIMPLIFIED CHINESE
NLS_CHARACTERSET     ZHS16GBK
NLS_SORT             BINARY
NLS_TIME_FORMAT      HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT   HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FOR DD-MON-RR HH.MI.SSXFF AM TZR
MAT

NLS_DUAL_CURRENCY    ￥
NLS_NCHAR_CHARACTERS AL16UTF16
ET

NLS_COMP             BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

已选择19行。

SQL> --可以使用函数来对日期进行转换
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 13-7月 -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 13-7月 -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

已选择14行。

SQL> --修改系统的日期格式
SQL> alter session set NLS_DATE_FORMAT='yyyy-mm-dd';

会话已更改。

SQL> select * from v$nls_parameters;

PARAMETER            VALUE
-------------------- ----------------------------------------------------------------
NLS_LANGUAGE         SIMPLIFIED CHINESE
NLS_TERRITORY        CHINA
NLS_CURRENCY         ￥
NLS_ISO_CURRENCY     CHINA
NLS_NUMERIC_CHARACTE .,
RS

NLS_CALENDAR         GREGORIAN
NLS_DATE_FORMAT      yyyy-mm-dd
NLS_DATE_LANGUAGE    SIMPLIFIED CHINESE
NLS_CHARACTERSET     ZHS16GBK
NLS_SORT             BINARY
NLS_TIME_FORMAT      HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT   HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FOR DD-MON-RR HH.MI.SSXFF AM TZR
MAT

NLS_DUAL_CURRENCY    ￥
NLS_NCHAR_CHARACTERS AL16UTF16
ET

NLS_COMP             BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

已选择19行。

SQL> select *
2 from emp
3 where hiredate='1980-12-17';

     EMPNO ENAME           JOB              MGR HIREDATE     SAL       COMM     DEPTNO
---------- --------------- --------- ---------- ---------- ----- ---------- ----------
      7369 SMITH           CLERK           7902 1980-12-17   800                    20

已选择 1 行。

SQL> alter session set NLS_DATE_FORMAT='DD-MON-RR';

会话已更改。

SQL> select *
2 from emp
3 where hiredate=to_date('1980-12-17','yyyy-mm-dd');

已选择 1 行。

SQL> --显示的性能高于隐式的转换方式
SQL> --查询工资在1000~2000之内的员工
SQL> select *
2 from emp
3 where sal>=1000 and sal<=2000;

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

已选择6行。

SQL> ed
已写入 file afiedt.buf

1 select *
2 from emp
3* where sal between 1000 and 2000
SQL> /

已选择6行。

SQL> --between and 注意: 大值在后，小值在前
SQL> ed
已写入 file afiedt.buf

1 select *
2 from emp
3* where sal between 2000 and 1000
SQL> /

未选定行

SQL> --in(set):集合
SQL> --查询部门号为10 20 30 的员工信息
SQL> select *
2 from emp
3 where deptno=10 or deptno=20 or deptno=30;

已选择14行。

SQL> select *
2 from emo
3 where deptno in (10,20,30);
from emo
*
第 2 行出现错误:
ORA-00942: 表或视图不存在

SQL> 2
2* from emo
SQL> c /emo/emp
2* from emp
SQL> /

已选择14行。

SQL> --like:模糊查询
SQL> --查询名字以S打头的员工信息
SQL> select *
2 from emp
3 where ename like 'S%';

已选择2行。

SQL> insert into emp(empno,ename) values(1234,'Tom_123');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from emp;

已选择15行。

SQL> --查询员工名字中含有下划线的员工信息
SQL> select *
2 from emp
3 where ename like '%_%';

已选择15行。

SQL> --在模糊查询中，使用转意
SQL> select *
2 from emp
3 where ename like '%\_%' escape '\';

     EMPNO ENAME           JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- --------------- --------- ---------- -------------- ----- ---------- ----------
      1234 Tom_123

已选择 1 行。

SQL> --escape：指明的字符即为转意字符，在该转意字符后的一个字符为字符本身
SQL> ed
已写入 file afiedt.buf

1 select *
2 from emp
3* where ename like '%a_%' escape 'a'
SQL> /

     EMPNO ENAME           JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- --------------- --------- ---------- -------------- ----- ---------- ----------
      1234 Tom_123

已选择 1 行。

SQL> --查询名字是四个字符的员工
SQL> select *
2 from emp
3 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

已选择3行。

SQL> exit
SQL> --查询不是10，20号部门的员工
SQL> select
2 *
3 from emp
4 where deptno not in(10,20);

     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

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850
        30

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

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

已选择6行。

SQL> set linesize 120
SQL> col sal for 9999
SQL> /

     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
      7698 BLAKE      MANAGER         7839 01-5月 -81      2850                    30
      7844 TURNER     SALESMAN        7698 08-9月 -81      1500          0         30
      7900 JAMES      CLERK           7698 03-12月-81       950                    30

已选择6行。

SQL> --排序
SQL> --按照员工工资排序
SQL> select *
2 from emp
3 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 13-7月 -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

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
      7902 FORD       ANALYST         7566 03-12月-81      3000                    20
      7788 SCOTT      ANALYST         7566 13-7月 -87      3000                    20
      7839 KING       PRESIDENT            17-11月-81      5000                    10
      1234 Tom_123

已选择15行。

SQL> a desc
3* order by sal desc
SQL> /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
      1234 Tom_123
      7839 KING       PRESIDENT            17-11月-81      5000                    10
      7902 FORD       ANALYST         7566 03-12月-81      3000                    20
      7788 SCOTT      ANALYST         7566 13-7月 -87      3000                    20
      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
      7499 ALLEN      SALESMAN        7698 20-2月 -81      1600        300         30
      7844 TURNER     SALESMAN        7698 08-9月 -81      1500          0         30
      7934 MILLER     CLERK           7782 23-1月 -82      1300                    10
      7521 WARD       SALESMAN        7698 22-2月 -81      1250        500         30

已选择15行。

SQL> --按照奖金排序
SQL> select *
2 from emp
3 order by comm;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
      7844 TURNER     SALESMAN        7698 08-9月 -81      1500          0         30
      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
      1234 Tom_123
      7839 KING       PRESIDENT            17-11月-81      5000                    10
      7876 ADAMS      CLERK           7788 13-7月 -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
      7782 CLARK      MANAGER         7839 09-6月 -81      2450                    10

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
      7698 BLAKE      MANAGER         7839 01-5月 -81      2850                    30
      7566 JONES      MANAGER         7839 02-4月 -81      2975                    20
      7369 SMITH      CLERK           7902 17-12月-80       800                    20
      7788 SCOTT      ANALYST         7566 13-7月 -87      3000                    20

已选择15行。

SQL> set pagesize 60
SQL> /

已选择15行。

SQL> ed
已写入 file afiedt.buf

1 select *
2 from emp
3* order by comm desc
SQL> /

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

已选择15行。

SQL> --将null排到最后
SQL> select *
2 from emp
3 order by comm desc
4 nulls last;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
      7654 MARTIN     SALESMAN        7698 28-9月 -81      1250       1400         30
      7521 WARD       SALESMAN        7698 22-2月 -81      1250        500         30
      7499 ALLEN      SALESMAN        7698 20-2月 -81      1600        300         30
      7844 TURNER     SALESMAN        7698 08-9月 -81      1500          0         30
      1234 Tom_123
      7839 KING       PRESIDENT            17-11月-81      5000                    10
      7876 ADAMS      CLERK           7788 13-7月 -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
      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
      7369 SMITH      CLERK           7902 17-12月-80       800                    20
      7788 SCOTT      ANALYST         7566 13-7月 -87      3000                    20

已选择15行。

SQL> --指定nulls last将null排到最后
SQL> --order by后面可以跟什么? 列名别名序号
SQL> select ename,sal 工资
2 from emp
3 order by 工资;

ENAME            工资
---------- ----------
SMITH             800
JAMES             950
ADAMS            1100
WARD             1250
MARTIN           1250
MILLER           1300
TURNER           1500
ALLEN            1600
CLARK            2450
BLAKE            2850
JONES            2975
FORD             3000
SCOTT            3000
KING             5000
Tom_123

已选择15行。

SQL> select ename,sal
2 from emp
3 order by 2;

ENAME        SAL
---------- -----
SMITH        800
JAMES        950
ADAMS       1100
WARD        1250
MARTIN      1250
MILLER      1300
TURNER      1500
ALLEN       1600
CLARK       2450
BLAKE       2850
JONES       2975
FORD        3000
SCOTT       3000
KING        5000
Tom_123

已选择15行。

SQL> select ename,sal,comm,sal*12+nvl(comm,0)
2 from emp
3 order by 4;

ENAME        SAL       COMM SAL*12+NVL(COMM,0)
---------- ----- ---------- ------------------
SMITH        800                          9600
JAMES        950                         11400
ADAMS       1100                         13200
WARD        1250        500              15500
MILLER      1300                         15600
MARTIN      1250       1400              16400
TURNER      1500          0              18000
ALLEN       1600        300              19500
CLARK       2450                         29400
BLAKE       2850                         34200
JONES       2975                         35700
FORD        3000                         36000
SCOTT       3000                         36000
KING        5000                         60000
Tom_123

已选择15行。

SQL> --按照部门号和工资排序输出员工的信息
SQL> select *
2 from emp
3 order by deptno,sal;

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

已选择15行。

SQL> --当order by作用与多列时，先按照第一列排序，如果相同，则按照第二列排序；以此类推
SQL> spool off