Oracle过滤和排序2

SQL> select *   2  from v$nls_parameters; SQL> set linesize 120 SQL> / 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                                                                            PARAMETER                      VALUE                                                                                     ------------------------------ ----------------------------------------------------------------                          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            FALSE                                                                                     已选择19行。 SQL> col VALUE for a30 SQL> / 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                                                                            PARAMETER                      VALUE                                                                                     ------------------------------ ------------------------------                                                            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            FALSE                                                                                     已选择19行。 SQL> --在上面可以看到默认的参数，比如日期 SQL> --between...and SQL>  SQL>  SQL>  SQL>  SQL> host cls SQL> --查询薪水在1000到2000之间的员工 SQL> select *   2  from emp   3  where sal between 1000 and 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 23-5月 -87           1100                    20                                     7934 MILLER     CLERK           7782 23-1月 -82           1300                    10                               已选择6行。 SQL> set linesize 100 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                 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           已选择6行。 SQL> /* SQL> 关于between...and有两点要说明： SQL> 1.包含边界 SQL> 2.小值在前，大值在后 SQL> */ SQL> host cls SQL> --in 在集合中 SQL> --查询部门号是10和20号的员工 SQL> select *   2  from emp   3  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           已选择8行。 SQL> ed 已写入 file afiedt.buf   1  select *   2  from emp   3* where deptno not in (10,20) 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> --如果集合中含有null，不能使用not in SQL> select *   2  from emp   3  where deptno not in (10,20,null); 未选定行 SQL> ed 已写入 file afiedt.buf   1  select *   2  from emp   3* where deptno in (10,20,null) SQL> /      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           已选择8行。 SQL> host cls SQL> --like 模糊查询 % _ SQL> --%表示任意长度的字符串，_表示任意的字符 SQL> --查询名字以S打头的员工 SQL> select *   2  from emp   3  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           SQL> insert into emp(empno,ename,sal,deptno)   2  values(1001,tom_gao,5000,10); values(1001,tom_gao,5000,10)             * 第 2 行出现错误:  ORA-00984: 列在此处不允许  SQL> insert into emp(empno,ename,sal,deptno)   2  values(1001,'tom_gao',5000,10); 已创建 1 行。 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                 1001 tom_gao                                              5000                    10           已选择15行。 SQL> --查询名字中包含_的员工 SQL> select *   2  from emp   3  where ename like '%\_%' escape '\';      EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO           ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------                 1001 tom_gao                                              5000                    10           SQL> rollback; 回退已完成。 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> --sql优化 SQL> --Oracle在解析where条件时，是按照从右至左的顺序解析 SQL> --所以在多个条件时，如where con1 and con2 应该尽量把假的放在右边 SQL> --这样con1就不用执行了，提高了效率 SQL> host cls SQL> --查询员工信息，按照年薪排序 SQL> select ename,sal,sal*12   2  from emp   3  order by sal*12; ENAME             SAL     SAL*12                                                                     ---------- ---------- ----------                                                                     SMITH             800       9600                                                                     JAMES             950      11400                                                                     ADAMS            1100      13200                                                                     WARD             1250      15000                                                                     MARTIN           1250      15000                                                                     MILLER           1300      15600                                                                     TURNER           1500      18000                                                                     ALLEN            1600      19200                                                                     CLARK            2450      29400                                                                     BLAKE            2850      34200                                                                     JONES            2975      35700                                                                     ENAME             SAL     SAL*12                                                                     ---------- ---------- ----------                                                                     SCOTT            3000      36000                                                                     FORD             3000      36000                                                                     KING             5000      60000                                                                     已选择14行。 SQL> a  desc;   3* order by sal*12 desc SQL> / ENAME             SAL     SAL*12                                                                     ---------- ---------- ----------                                                                     KING             5000      60000                                                                     FORD             3000      36000                                                                     SCOTT            3000      36000                                                                     JONES            2975      35700                                                                     BLAKE            2850      34200                                                                     CLARK            2450      29400                                                                     ALLEN            1600      19200                                                                     TURNER           1500      18000                                                                     MILLER           1300      15600                                                                     WARD             1250      15000                                                                     MARTIN           1250      15000                                                                     ENAME             SAL     SAL*12                                                                     ---------- ---------- ----------                                                                     ADAMS            1100      13200                                                                     JAMES             950      11400                                                                     SMITH             800       9600                                                                     已选择14行。 SQL> ed 已写入 file afiedt.buf   1  select ename,sal,sal*12 年薪   2  from emp   3* order by 年薪 SQL> / ENAME             SAL       年薪                                                                     ---------- ---------- ----------                                                                     SMITH             800       9600                                                                     JAMES             950      11400                                                                     ADAMS            1100      13200                                                                     WARD             1250      15000                                                                     MARTIN           1250      15000                                                                     MILLER           1300      15600                                                                     TURNER           1500      18000                                                                     ALLEN            1600      19200                                                                     CLARK            2450      29400                                                                     BLAKE            2850      34200                                                                     JONES            2975      35700                                                                     ENAME             SAL       年薪                                                                     ---------- ---------- ----------                                                                     SCOTT            3000      36000                                                                     FORD             3000      36000                                                                     KING             5000      60000                                                                     已选择14行。 SQL> --order by后面+多列  desc只作用于离它最近的列 SQL> select ename,sal,sal*12   2  from emp   3  order by deptno,sal desc; ENAME             SAL     SAL*12                                                                     ---------- ---------- ----------                                                                     KING             5000      60000                                                                     CLARK            2450      29400                                                                     MILLER           1300      15600                                                                     SCOTT            3000      36000                                                                     FORD             3000      36000                                                                     JONES            2975      35700                                                                     ADAMS            1100      13200                                                                     SMITH             800       9600                                                                     BLAKE            2850      34200                                                                     ALLEN            1600      19200                                                                     TURNER           1500      18000                                                                     ENAME             SAL     SAL*12                                                                     ---------- ---------- ----------                                                                     MARTIN           1250      15000                                                                     WARD             1250      15000                                                                     JAMES             950      11400                                                                     已选择14行。 SQL> ed 已写入 file afiedt.buf   1  select ename,deptno,sal   2  from emp   3* order by deptno,sal desc SQL> / ENAME          DEPTNO        SAL                                                                     ---------- ---------- ----------                                                                     KING               10       5000                                                                     CLARK              10       2450                                                                     MILLER             10       1300                                                                     SCOTT              20       3000                                                                     FORD               20       3000                                                                     JONES              20       2975                                                                     ADAMS              20       1100                                                                     SMITH              20        800                                                                     BLAKE              30       2850                                                                     ALLEN              30       1600                                                                     TURNER             30       1500                                                                     ENAME          DEPTNO        SAL                                                                     ---------- ---------- ----------                                                                     MARTIN             30       1250                                                                     WARD               30       1250                                                                     JAMES              30        950                                                                     已选择14行。 SQL> select * from emp order by comm desc;      EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO           ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------                 7369 SMITH      CLERK           7902 17-12月-80            800                    20                 7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10                 7902 FORD       ANALYST         7566 03-12月-81           3000                    20                 7900 JAMES      CLERK           7698 03-12月-81            950                    30                 7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20                 7566 JONES      MANAGER         7839 02-4月 -81           2975                    20                 7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30                 7934 MILLER     CLERK           7782 23-1月 -82           1300                    10                 7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20                 7839 KING       PRESIDENT            17-11月-81           5000                    10                 7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30                EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO           ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------                 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           已选择14行。 SQL> set linesize 120 SQL> /      EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO                               ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------                                     7369 SMITH      CLERK           7902 17-12月-80            800                    20                                     7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10                                     7902 FORD       ANALYST         7566 03-12月-81           3000                    20                                     7900 JAMES      CLERK           7698 03-12月-81            950                    30                                     7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20                                     7566 JONES      MANAGER         7839 02-4月 -81           2975                    20                                     7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30                                     7934 MILLER     CLERK           7782 23-1月 -82           1300                    10                                     7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20                                     7839 KING       PRESIDENT            17-11月-81           5000                    10                                     7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30                                    EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO                               ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------                                     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                               已选择14行。 SQL> set pagesize 29 SQL> /      EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO                               ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------                                     7369 SMITH      CLERK           7902 17-12月-80            800                    20                                     7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10                                     7902 FORD       ANALYST         7566 03-12月-81           3000                    20                                     7900 JAMES      CLERK           7698 03-12月-81            950                    30                                     7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20                                     7566 JONES      MANAGER         7839 02-4月 -81           2975                    20                                     7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30                                     7934 MILLER     CLERK           7782 23-1月 -82           1300                    10                                     7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20                                     7839 KING       PRESIDENT            17-11月-81           5000                    10                                     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                               已选择14行。 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                                     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                                     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                                     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                                     7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10                               已选择14行。 SQL> exit