Oracle的数据限定与排序

一、简单查询语句执行顺序

• <1>from , <2>where , <3>group by , <4>having , <5>order by , <6>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筛选。

二、排序(order by)

• 位置：order by 语句总是在一个select语句的最后面
• 排序可以使用列名，列表达式，列函数，列别名，列位置编号等都没有限制，select的投影列可不包括排序列，除指定的列位置标号外。
• 升序和降序，升序ASC（默认），降序DESC。有空值的列的排序，缺省（ASC升序）时null排在最后面.
• 混合排序，使用多个列进行排序，多列使用逗号隔开，可以分别在各列后面加升降序。

SQL> select ename,sal from emp order by sal;
SQL> select ename,sal salary from emp order by salary;
SQL> select ename,sal 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 desc nulls first;
SQL> select ename,deptno,job from emp order by deptno asc,job desc;

三、空值（null）

1. 空值既不是数值 0 ，也不是字符" "，null 表示不确定.

2. 空值参与运算或比较时要注意几点：

1) 空值（null）的数据行将对算数表达式返回空值

SQL> select ename,sal,comm,sal+comm from emp;
ENAME            SAL      COMM   SAL+COMM
---------- --------- --------- ----------
SMITH         800.00           
ALLEN        1600.00    300.00       1900
WARD         1250.00    500.00       1750
JONES        2975.00           
MARTIN       1250.00   1400.00       2650
BLAKE        2850.00           
CLARK        2450.00           
SCOTT        3000.00           
KING         5000.00           
TURNER       1500.00      0.00       1500
ADAMS        1100.00           
JAMES         950.00           
FORD         3000.00           
MILLER       1300.00           

如下：sum(sal+comm) < sum(sal)，就是因为空值（null）的数据行对算数表达式返回空值

SQL> select sum(sal),sum(sal+comm) from emp;
  SUM(SAL) SUM(SAL+COMM)
---------- -------------
     29025          7800

2) 比较表达式选择有空值（null）的数据行时，表达式返回为“假”，结果返回空行。

3) 非空字段与空值字段做 || 时，null值转字符型""，合并列的数据类型为varcahr2。

4) not in 在子查询中的空值问题：in 与not in 遇到空值时情况不同，对于”not in” 如果子查询的结果集中有空值，那么主查询得到的结果集也是空。

5) 外键可以为 null

6) 空值在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;

7) 空值在update语句和insert语句可以直接使用=null 赋值。

3. 处理空值的几种函数方法

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.00    300.00       1900     30
WARD         1250.00    500.00       1750     30
MARTIN       1250.00   1400.00       2650     30
BLAKE        2850.00                 2850     30
CLARK        2450.00                 2450     10
KING         5000.00                 5000     10
TURNER       1500.00      0.00       1500     30
JAMES         950.00                  950     30
MILLER       1300.00                 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,NULL,4)
-----------------------
                      2