Oracle过滤和排序2

最新推荐文章于 2017-12-03 13:11:36 发布

高小毅

最新推荐文章于 2017-12-03 13:11:36 发布

阅读量245

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/smugaoyi/article/details/77757514

版权

Oracle 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

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

高小毅

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle过滤和排序2

SQL> select * 2 from v$nls_parameters;PARAMETER ----------------------------------------------------------------
复制链接

扫一扫

专栏目录