文章详细介绍了SQL查询语句的执行顺序，从FROM到SELECT的步骤，并强调了WHERE用于限定表或视图，不能直接使用分组函数。HAVING则用于限定GROUPBY的结果，必须是聚合函数或分组列。ORDERBY用于排序，支持列名、别名、位置编号和表达式，还讲解了NULL在查询中的处理，包括NVL函数和NULLIF函数的用法。
1. 查询语句执行顺序
from ——》where ——》group by ——》having ——》order by ——》select
1.1 where限定
where 限定from后面的表或视图，限定的选项只能是表的列或列单行函数或列表达式 where后面不能直接使用分组函数。
SQL>
SQL> select empno,job from emp where sal>2000;
EMPNO JOB
---------- ---------------------------
7566 MANAGER
7698 MANAGER
7782 MANAGER
7788 ANALYST
7902 ANALYST
SQL> -- emp表中列
SQL> select empno,job from emp where sal>2000;
EMPNO JOB
---------- ---------------------------
7566 MANAGER
7698 MANAGER
7782 MANAGER
7788 ANALYST
7902 ANALYST
SQL> -- 单行行数
SQL> select empno,job from emp where length(job) > 5;
EMPNO JOB
---------- ---------------------------
7499 SALESMAN
7521 SALESMAN
7566 MANAGER
7654 SALESMAN
7698 MANAGER
7782 MANAGER
7788 ANALYST
7844 SALESMAN
7902 ANALYST
9 rows selected.
SQL> -- 列表达式
SQL> select empno, job from emp where sal+comm>2000;
EMPNO JOB
---------- ---------------------------
7654 SALESMAN
SQL> -- 错误:where后不可使用分组函数
SQL> select deptno, avg(sal) avgcomm from emp where avg(sal)>2000;
select deptno, avg(sal) avgcomm from emp where avg(sal)>2000
*
ERROR at line 1:
ORA-00934: group function is not allowed here
SQL>
1.2 having 限定
having限定group by的结果,限定的选项必须是group by后的聚合函数或分组列,不可以直接使用where后的限定选项.
SQL> -- group分组中的列
SQL> select sum(sal) from emp group by deptno having deptno=10;
SUM(SAL)
----------
3750
SQL> -- 聚合函数
SQL> select deptno, sum(sal) from emp group by deptno having sum(sal)>9000;
DEPTNO SUM(SAL)
---------- ----------
30 9400
20 10875
SQL> -- 如果要使用group by及having,有条件的话,先使用where筛选,效率才会高,如例1中,应用where执行
SQL> select sum(sal) from emp where deptno=10 group by deptno;
SUM(SAL)
----------
3750
SQL> 2. 排序（order by）
2.1 位置
order by语句总是在一个select语句的最后面
2.2 排序内容
列名, 列别名, 列的位置编号,列表达式,列函数等都没有限制,select的投影列可不包括排序列,除了指定的列的位置标号外.
SQL>
SQL> select ename,sal from emp order by sal; ---列名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 300013 rows selected.
SQL> select ename,sal salary from emp order by salary; ---列别名
ENAME SALARY
------------------------------ ----------
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 300013 rows selected.
SQL> select ename,sal salary from emp order by 2; ---列的位置 编号
ENAME SALARY
------------------------------ ----------
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 300013 rows selected.
SQL> select ename,sal,sal+100 from emp order by sal+comm; ---列表达式
ENAME SAL SAL+100
------------------------------ ---------- ----------
TURNER 1500 1600
WARD 1250 1350
ALLEN 1600 1700
MARTIN 1250 1350
SCOTT 3000 3100
ADAMS 1100 1200
JAMES 950 1050
FORD 3000 3100
MILLER 1300 1400
BLAKE 2850 2950
JONES 2975 3075
SMITH 800 900
CLARK 2450 255013 rows selected.
SQL> select deptno,avg(sal) from emp group by deptno order by avg(sal) desc; ---列函数
DEPTNO AVG(SAL)
---------- ----------
20 2175
10 1875
30 1566.66667SQL>
2.3 Nulls first & Nulls Last
升序和降序,升序ASC(默认), 降序DESC.有空值的列的排序,缺省(ASC升序)时 null 排在最后面(知识点).
Nulls first和nulls last是Oracle Order by支持的语法
如果Order by 中指定了表达式Nulls first则表示null值的记录将排在最前(不管是asc 还是 desc)
如果Order by 中指定了表达式Nulls last则表示null值的记录将排在最后 (不管是asc 还是 desc)
SQL>
SQL> select ename,job,comm from emp order by 3;
ENAME JOB COMM
------------------------------ --------------------------- ----------
TURNER SALESMAN 0
ALLEN SALESMAN 300
WARD SALESMAN 500
MARTIN SALESMAN 1400
SCOTT ANALYST
ADAMS CLERK
JAMES CLERK
FORD ANALYST
MILLER CLERK
BLAKE MANAGER
JONES MANAGER
SMITH CLERK
CLARK MANAGER
13 rows selected.
SQL> select ename,job,comm from emp order by 3 nulls last;
ENAME JOB COMM
------------------------------ --------------------------- ----------
TURNER SALESMAN 0
ALLEN SALESMAN 300
WARD SALESMAN 500
MARTIN SALESMAN 1400
SCOTT ANALYST
ADAMS CLERK
JAMES CLERK
FORD ANALYST
MILLER CLERK
BLAKE MANAGER
JONES MANAGER
SMITH CLERK
CLARK MANAGER
13 rows selected.
SQL> select ename,job,comm from emp order by 3 nulls first;
ENAME JOB COMM
------------------------------ --------------------------- ----------
SMITH CLERK
CLARK MANAGER
FORD ANALYST
JAMES CLERK
ADAMS CLERK
JONES MANAGER
BLAKE MANAGER
MILLER CLERK
SCOTT ANALYST
TURNER SALESMAN 0
ALLEN SALESMAN 300
WARD SALESMAN 500
MARTIN SALESMAN 1400
13 rows selected.
SQL> 2.4 混合排序
使用多列进行排序,多列使用逗号隔开,可以分别在各列后面加升序降序.
SQL> select ename,job, sal+comm from emp order by 3 nulls first;
ENAME JOB SAL+COMM
------------------------------ --------------------------- ----------
SMITH CLERK
CLARK MANAGER
FORD ANALYST
JAMES CLERK
ADAMS CLERK
JONES MANAGER
BLAKE MANAGER
MILLER CLERK
SCOTT ANALYST
TURNER SALESMAN 1500
WARD SALESMAN 1750
ALLEN SALESMAN 1900
MARTIN SALESMAN 2650
13 rows selected.
SQL>
SQL> select ename,deptno,job from emp order by deptno, job desc;
ENAME DEPTNO JOB
------------------------------ ---------- ---------------------------
CLARK 10 MANAGER
MILLER 10 CLERK
JONES 20 MANAGER
ADAMS 20 CLERK
SMITH 20 CLERK
SCOTT 20 ANALYST
FORD 20 ANALYST
ALLEN 30 SALESMAN
TURNER 30 SALESMAN
WARD 30 SALESMAN
MARTIN 30 SALESMAN
BLAKE 30 MANAGER
JAMES 30 CLERK
13 rows selected.
SQL>
3.空值（null）
空值既不是数值0,也不是字符" ", null表示不确定
3.1 空值运算或比较时注意点
3.1.1 Null运算后返回空值
SQL>
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
TURNER 1500 0 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
13 rows selected.
SQL> select sum(sal),sum(sal+comm) from emp;
SUM(SAL) SUM(SAL+COMM)
---------- -------------
24025 7800
SQL>
为什么 sal+comm 的求和小于 sal 的求和?
聚合.空值(null)的数据行将对算数表达式返回空值
3.1.2 Null比较结果返回空值
SQL>
SQL> select ename, sal, comm from emp where sal >=comm;
ENAME SAL COMM
------------------------------ ---------- ----------
ALLEN 1600 300
WARD 1250 500
TURNER 1500 0
SQL> 3.1.3 非空字段与Null做"||"时
null值转为字符型"",合并列的数据类型为varchar2.
SQL> set pagesize 200 linesize 300
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
TURNER 15000
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
13 rows selected.
SQL> 3.1.4 not in 在子查询中的空值问题(见第八章)
3.1.5 外键值可以为null
3.1.6 where中使用”is null”或”is not null”
SQL>
SQL> select ename,mgr from emp where mgr is null;
no rows selected
SQL> select ename,mgr from emp where mgr is not null;
ENAME MGR
------------------------------ ----------
SMITH 7902
ALLEN 7698
WARD 7698
JONES 7839
MARTIN 7698
BLAKE 7839
CLARK 7839
SCOTT 7566
TURNER 7698
ADAMS 7788
JAMES 7698
FORD 7566
MILLER 7782
13 rows selected.
SQL>
3.1.7 update和insert中可以直接使用"=null" 赋值
SQL> update emp set comm=null where empno=7788;
1 row updated.
SQL>3.2 处理空值的几种函数方法:
3.2.1 nvl (expr1, expr2)
当第一个参数不为空时取第一个值,当第一个值为NULL时,取第二个参数的值.
SQL>
SQL> select nvl(1,2) from dual;
NVL(1,2)
----------
1
SQL> select nvl(null,2) from dual;
NVL(NULL,2)
-----------
2
SQL> 知识点:nvl函数可以作用于数值类型,字符类型,日期类型,但数据类型尽量匹配.
SQL> select nvl(comm,0) from emp;
NVL(COMM,0)
-----------
0
300
500
0
1400
0
0
0
0
0
0
0
0
13 rows selected.
SQL> select nvl(hiredate,'17-DEC-80') from emp;
NVL(HIREDATE,'17-DEC-80')
---------------
17-DEC-80
20-FEB-81
22-FEB-81
02-APR-81
28-SEP-81
01-MAY-81
09-JUN-81
24-JAN-87
08-SEP-81
02-APR-87
03-DEC-81
03-DEC-81
23-JAN-82
13 rows selected.
SQL> select ename,nvl(mgr,0) from emp;
ENAME NVL(MGR,0)
------------------------------ ----------
SMITH 7902
ALLEN 7698
WARD 7698
JONES 7839
MARTIN 7698
BLAKE 7839
CLARK 7839
SCOTT 7566
TURNER 7698
ADAMS 7788
JAMES 7698
FORD 7566
MILLER 7782
13 rows selected.
SQL> select ename,nvl(mgr,0) from emp;
ENAME NVL(MGR,0)
------------------------------ ----------
SMITH 7902
ALLEN 7698
WARD 7698
JONES 7839
MARTIN 7698
BLAKE 7839
CLARK 7839
SCOTT 7566
TURNER 7698
ADAMS 7788
JAMES 7698
FORD 7566
MILLER 7782
13 rows selected.
SQL> select ename,nvl(mgr,'No Manager') from emp;
select ename,nvl(mgr,'No Manager') from emp
*
ERROR at line 1:
ORA-01722: invalid number
SQL>3.2.2 nvl2(expr1, expr2, expr3)
当第一个参数不为NULL,取第二个参数的值,当第一个参数为NULL,取第三个数的值.
SQL>
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
TURNER 1500 0 1500 30
JAMES 950 950 30
MILLER 1300 1300 10
8 rows selected.
SQL>
3.2.3 NULLIF (expr1, expr2)
比对两个值是否一样,一样就返回为空,否则不会为空
当第一个参数和第二个参数相同时,返回为空;
当第一个参数和第二个参数不同时,返回第一个参数值,第一个参数值不允许为null
SQL>
SQL> select nullif(2,2) from dual;
NULLIF(2,2)
-----------
SQL> select nullif(1,2) from dual;
NULLIF(1,2)
-----------
1
SQL> select nullif(null,2) from dual;
select nullif(null,2) from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CHAR
SQL> select nullif(1,null) from dual;
NULLIF(1,NULL)
--------------
1
SQL> select nullif(null,null) from dual;
select nullif(null,null) from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CHAR
SQL> 3.2.4 coalesce (expr1, expr2........)
返回从左起始第一个不为空的值,如果所有参数都为空,那么返回空值.
SQL>
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
SQL> select coalesce(null,null,null,4) from dual;
COALESCE(NULL,NULL,NULL,4)
--------------------------
4
SQL> select coalesce(null,null,null,null) from dual;
C
-
SQL>
406
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
