oracle之数据限定与排序
数据限定与排序
6.1 简单查询语句执行顺序
from, where, group by, having, order by, select
where限定from后面的表或视图，限定的选项只能是表的列或列单行函数或列表达式，where后不可以直接使用分组函数
SQL> select empno,job from emp where sal>2000;
SQL> select empno,job from emp where length(job)>5;
SQL> select empno,job from emp where sal+comm>2000;
having限定group by的结果，限定的选项必须是group by后的聚合函数或分组列，不可以直接使用where后的限定选项。
SQL> select sum(sal) from emp group by deptno having deptno=10;
SQL> select deptno,sum(sal) from emp group by deptno having sum(sal)>9000;
如果要使用group by及having，有条件的话先使用where筛选。
6.2 排序(order by)
1）位置：order by语句总是在一个select语句的最后面。
2）排序可以使用列名，列表达式，列函数，列别名，列位置编号等都没有限制，select的投影列可不包括排序列，除指定的列位置标号外。
3）升序和降序，升序ASC（默认), 降序DESC。有空值的列的排序，缺省（ASC升序）时 null排在最后面（考点）。
4）混合排序，使用多个列进行排序，多列使用逗号隔开，可以分别在各列后面加升降序。
SQL> select ename,sal from emp order by sal;
SQL> select ename,sal as salary from emp order by salary;
SQL> select ename,sal as salary from emp order by 2;
SQL> select ename,sal,sal+100 from emp order by sal+comm;
SQL> select deptno,avg(sal) from emp group by deptno order by avg(sal) desc;
SQL> select ename,job,sal+comm from emp order by 3 nulls first;
SQL> select ename,deptno,job from emp order by deptno asc,job desc;
6.3 空值（null)
空值既不是数值0,也不是字符" ", null表示不确定。
6.3.1 空值参与运算或比较时要注意几点：
1）空值（null）的数据行将对算数表达式返回空值
SQL> select ename,sal,comm,sal+comm from emp;
ENAME SAL COMM SAL+COMM
---------- ---------- ---------- ----------
SMITH 800
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975
MARTIN 1250 1400 2650
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500 0 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
已选择14行。
2）分组函数忽略空值
SQL> select sum(sal),sum(sal+comm) from emp; //为什么sal+comm的求和小于sal的求和？
SUM(SAL) SUM(SAL+COMM)
---------- -------------
29025 7800
3）比较表达式选择有空值（null）的数据行时，表达式返回为“假”， 结果返回空行。
SQL> select ename,sal,comm from emp where sal>=comm;
ENAME SAL COMM
---------- ---------- ----------
ALLEN 1600 300
WARD 1250 500
TURNER 1500 0
4）非空字段与空值字段做"||"时, null值转字符型""，合并列的数据类型为varchar2。
SQL> select ename, sal||comm from emp;
ENAME SAL||COMM
---------- --------------------------------------------------------------------------------
SMITH 800
ALLEN 1600300
WARD 1250500
JONES 2975
MARTIN 12501400
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 15000
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
已选择14行。
5）not in 在子查询中的空值问题（见第八章）
6）外键值可以为null，唯一约束中，null值可以不唯一（见十二章）
7）空值在where子句里使用“is null”或“is not null”
SQL> select ename,mgr from emp where mgr is null;
SQL> select ename,mgr from emp where mgr is not null;
8）空值在update语句和insert语句可以直接使用“=null” 赋值
SQL> update emp set comm=null where empno=7788;
6.3.2 处理空值的几种函数方法：
1）nvl(expr1,expr2)
当第一个参数不为空时取第一个值，当第一个值为NULL时，取第二个参数的值。
SQL>select nvl(1,2) from dual;
NVL(1,2)
----------
1
SQL> select nvl(null,2) from dual;
NVL(NULL,2)
-----------
2
*考点：nvl函数可以作用于数值类型，字符类型，日期类型，但数据类型尽量匹配。
NVL(comm,0)
NVL(hiredate,'1970-01-01')
NVL(ename,'no manager')
2）nvl2(expr1,expr2,expr3)
当第一个参数不为NULL，取第二个参数的值，当第一个参数为NULL，取第三个数的值。
SQL> select nvl2(1,2,3) from dual;
NVL2(1,2,3)
-----------
2
SQL> select nvl2(null,2,3) from dual;
NVL2(NULL,2,3)
--------------
3
SQL> select ename,sal,comm,nvl2(comm,SAL+COMM,SAL) income,deptno from emp where deptno in (10,30);
ENAME SAL COMM INCOME DEPTNO
---------- ---------- ---------- ---------- ----------
ALLEN 1600 300 1900 30
WARD 1250 500 1750 30
MARTIN 1250 1400 2650 30
BLAKE 2850 2850 30
CLARK 2450 2450 10
KING 5000 5000 10
TURNER 1500 0 1500 30
JAMES 950 950 30
MILLER 1300 1300 10
*考点: nvl和nvl2中的第二个参数不是一回事。
3）NULLIF（expr1,expr2) /*比对两个值是否一样，一样就返回为空，否则不会为空*/
当第一个参数和第二个参数相同时，返回为空，当第一个参数和第二个数不同时，返回第一个参数值，第一个参数值不允许为null
SQL> select nullif(2,2) from dual;
NULLIF(2,2)
-----------
SQL> select nullif(1,2) from dual;
NULLIF(1,2)
-----------
1
4）coalesce（expr1,expr2........) 返回从左起始第一个不为空的值，如果所有参数都为空，那么返回空值。
SQL> select coalesce(1,2,3,4) from dual;
COALESCE(1,2,3,4)
-----------------
1
SQL> select coalesce(null,2,null,4) from dual;
COALESCE(NULL,2,3,4)
--------------------
2
