sql第五天，nvl,decode,子查询

最新推荐文章于 2021-04-10 19:11:16 发布

wsdsbh

最新推荐文章于 2021-04-10 19:11:16 发布

阅读量1.7k

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/qq_34907701/article/details/79801971

版权

oracle 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

首先介绍 NVL函数：是一个空值转换函数大致是因为空值不可直接与数字型数据相加减的缘故。

SQL> select ename "NAME" ,sal+comm "income",job
  2  from emp
  3  where job not like 'SALES%'
  4  order by job;

NAME           income JOB
---------- ---------- ---------
SCOTT                 ANALYST
FORD                  ANALYST
MILLER                CLERK
ADAMS                 CLERK
JAMES                 CLERK
SMITH                 CLERK
BLAKE                 MANAGER
CLARK                 MANAGER
JONES                 MANAGER
KING                  PRESIDENT

已选择10行。

SQL> select ename name,sal+nvl(comm,0),job
  2  from emp
  3  where job not like 'SALES%'
  4  ORDER by job;

NAME       SAL+NVL(COMM,0) JOB
---------- --------------- ---------
SCOTT                 3000 ANALYST
FORD                  3000 ANALYST
MILLER                1300 CLERK
ADAMS                 1100 CLERK
JAMES                  950 CLERK
SMITH                  800 CLERK
BLAKE                 2850 MANAGER
CLARK                 2450 MANAGER
JONES                 2975 MANAGER
KING                  5000 PRESIDENT

已选择10行。

nul(expr1,expr2):如果expr1为空，则用expr2替换

expr1 与expr2可以是数字型，字符型，日期型。但是expr1与expr2必须是同一数据类型

如果不同则要数据转换：

1.对数字： nvl（comm，0）

2.对字符： nvl（to_char（comm），‘no commission’）

3.对日期：nvl（hiredate，‘31-DEC-99')

==========

decode 函数：由于sql中没有 switch case 这种函数，所以oracle引进了 decode

select ename "name",job,sal "salary",

decode(job,'SALESMAN',SAL*1.1,

'CLERK',SAL*1.2,

'ANALYST',SAL*1.3,

SAL*1.4) "NEW SALARY"

FROM EMP

ORDER BY JOB;

SQL> l
  1  select ename"name",job,sal "salary",
  2  decode(job,'SALESMAN',SAL*1.1,
  3                      'CLERK',SAL*1.2,
  4                      'ANALYST',SAL*1.3,
  5                                      SAL*1.4) "NEW SALARY"
  6  FROM EMP
  7  ORDER BY JOB
  8*
SQL> /

name       JOB           salary NEW SALARY
---------- --------- ---------- ----------
SCOTT      ANALYST         3000       3900
FORD       ANALYST         3000       3900
MILLER     CLERK           1300       1560
JAMES      CLERK            950       1140
SMITH      CLERK            800        960
ADAMS      CLERK           1100       1320
BLAKE      MANAGER         2850       3990
JONES      MANAGER         2975       4165
CLARK      MANAGER         2450       3430
KING       PRESIDENT       5000       7000
TURNER     SALESMAN        1500       1650

name       JOB           salary NEW SALARY
---------- --------- ---------- ----------
MARTIN     SALESMAN        1250       1375
WARD       SALESMAN        1250       1375
ALLEN      SALESMAN        1600       1760

已选择14行。

但是switch case 中的 case是不能为表达式的，只能为一个值

decode （）

最后一栏没有job，是默认的，也就是其他不符合前面的项。

=======================

引入单行子查询：

先看两个东西：

SQL> select job
  2  from emp
  3  where ename='SMITH';

JOB
---------
CLERK

SQL> SELECT empno,ename,sal,job
  2  from emp
  3  where job='CLERK';

     EMPNO ENAME             SAL JOB
---------- ---------- ---------- ---------
      7369 SMITH             800 CLERK
      7876 ADAMS            1100 CLERK
      7900 JAMES             950 CLERK
      7934 MILLER           1300 CLERK

由第一个查询得到job为clerk，放到第二个查询里做条件。

从而引出子查询。

又分好几个类别：

所谓的子查询，是一个完整的select语句，

子查询的结果分为单列，单行，多行，与多列

构成了，主查询与子查询

一般是先做子查询，再做主查询

而单行子查询可用比较运算符，多行得用 IN

单行子查询不能使用order by语句，但可结合rownum

where子句中的单列单行子查询

SQL> select empno,ename,sal,job
2 from emp
3 where job=
4 (select job
5 from emp
6 where ename='SMITH');

EMPNO ENAME SAL JOB
---------- ---------- ---------- ---------
7369 SMITH 800 CLERK
7876 ADAMS 1100 CLERK
7900 JAMES 950 CLERK
7934 MILLER 1300 CLERK

SQL>

============

having 子句中的单行子查询：

1 select job,min(sal),avg(sal),max(sal)
2 from emp
3 where job not like 'PRESID%'
4 group by job
5 having avg(sal)>(
6 select min(avg(sal))
7 from emp

8* group by job)

首先，having里的子查询是按job分好组，求每个组里的平均薪水，这些组里的最小值

然后外查询里，group by job，按job分组，having里平均薪水大于子查询里的最低平均薪水。

总的来说算是查询：

平均工资高于最低平均工资（按职位分类）的所有职位。

而查询语句的顺序是自下往上。

也就是说，最里面的子查询先做，然后group by 与having是一组

group by 分组，但是还有要求就是having了。

SQL> l
  1  select job,min(sal),avg(sal),max(sal)
  2  from emp
  3  where job not like 'PRESID%'
  4  group by job
  5  having avg(sal)>(
  6             select min(avg(sal))
  7             from emp
  8*            group by job)
SQL> /

JOB         MIN(SAL)   AVG(SAL)   MAX(SAL)
--------- ---------- ---------- ----------
SALESMAN        1250       1400       1600
MANAGER         2450 2758.33333       2975
ANALYST         3000       3000       3000

SQL>

=============

from子句中的单行子查询。

查出所有工资高于所任职位平均工资的员工。

SQL> l
1 select e.empno,e.ename,e.sal,e.job,a.avesal
2 from emp e,(select job,avg(sal) avesal
3 from emp
4 group by job) a
5 where e.job=a.job
6 and e.sal>a.avesal
7* and e.job!='CLERK'

这个就叫关联查询，执行顺序是一起查询

我给from子句里查到的东西起了个名字 a ，emp表起了个名字 e

反正就是把from子句查到的东西要用好多地方。

SQL> l
  1  select e.empno,e.ename,e.sal,e.job,a.avesal
  2  from emp e,(select  job,avg(sal) avesal
  3             from emp
  4             group by job) a
  5  where e.job=a.job
  6  and e.sal>a.avesal
  7* and e.job!='CLERK'
SQL> /

     EMPNO ENAME             SAL JOB           AVESAL
---------- ---------- ---------- --------- ----------
      7499 ALLEN            1600 SALESMAN        1400
      7566 JONES            2975 MANAGER   2758.33333
      7698 BLAKE            2850 MANAGER   2758.33333
      7844 TURNER           1500 SALESMAN        1400

SQL>

==============

多行子查询：

比较操作符有： IN ANY ALL

SQL> SELECT empno,ename,job,sal
  2  from emp
  3  where sal IN (select max(sal)
  4             from emp
  5             group by job)
  6  and job<>'CLERK'
  7  AND job not like 'PRES%';

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7499 ALLEN      SALESMAN        1600
      7566 JONES      MANAGER         2975
      7788 SCOTT      ANALYST         3000
      7902 FORD       ANALYST         3000

首先在子查询里，得到好多组里的最大薪水，然后就是where子句

大概的意思是 a < ALL（查询子句），这样我们就可以使用比较运算符了

那这样的话，岂不都是放在where当中了？？

===============

使用exists操作符：

exists操作符用于查询语句中确定某些特定的数据行是否在一个表中存在，如果存在至少一行

其子查询的返回结果就为真。（TRUE)

SQL> select *
  2  from dept
  3  where not exists
  4             (select  *
  5             from emp
  6             where emp.deptno=dept.deptno);

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

SQL> desc dept
名称是否为空? 类型
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

以上是所有没有任何员工的部门的详细信息。

wsdsbh

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
sql第五天，nvl,decode,子查询

首先介绍 NVL函数：是一个空值转换函数大致是因为空值不可直接与数字型数据相加减的缘故。SQL&gt; select ename "NAME" ,sal+comm "income",job 2 from emp 3 where job not like 'SALES%' 4 order by job;NAME income JOB--------...
复制链接

扫一扫

专栏目录