一个菜鸟的oracle之路--------二(续)

案例16 哪些员工的奖金不是400,500,1000，也不为空。

 select ename,bonus from emp_xxx
       where bonus not in(400,1000) or bonus is  null;

//用not in 进行比较时，集合中不能包含null値，若包含
//则会出现no rows selected
//显示每个员工的最后两个字符。

 select substr(ename,2,2)from emp_xxx;
   alter session set nls_date_format=
      'yyyy_mm_dd hh24:mi:ss';

案例17. case语句
根据员工的职位，计算加薪后的数据。
如果职位是Analist,加薪10%
如果职位是Programmer 加薪5%
如果职位是clerk，加薪2%
其他职位 薪水不变

select ename,job,
         case job when 'Analyst' then salary*1.1
                  when 'Programmer' then salary*1.05
                  when 'clerk' then salary*1.02
                  else salary
                  end new_salary from emp_xxx;
     select ename,salary,
         case salary when  <5000 then salary*1.2                                       
                     else salary
                     end new_salary 
                     from emp_xxx;

//此处有问题。

 select ename,salary,
         case  salary  when  10000 then salary*9                   
         else salary
         end new_salary
from emp_xxx;

案例18 decode函数
decode()函数是oracle中等价于case when语句的函数。
作用同case相同
decode 函数语法如下。
要求和case语句相同

  select ename,decode(job, 'Analyst',salary*1.1,
                             'Programmer',salary*1.05,
                             'clerk',salary*1.02,
                             salary) new_salary from emp_xxx;

案例19 函数的嵌套
薪水由低到高排序

 select ename,salary from emp_xxx order by salary ;

也可以这么写：
select ename,salary from emp_xxx order by salary asc ;
案例20 薪水由高到低排序

 select ename,nvl(salary,100000) from emp_xxx order by salary desc;

案例21 按照入职时间排序，入职时间越早排在前面

  select ename,hiredate from emp_xxx order by hiredate;

案例22 按照部门排序，同一个部门按薪水由高到低排序

 select ename,deptno,salary from emp_xxx order by deptno,salary desc;  

案例23 员工表中有多少条记录

 select count(*) from emp_xxx;

数据字典 user_tables
案例24 当前用户下有多少张表

   select count(*) from user_tables;

案例25 openlab账户下有多少包含emp的表

 select count(*) from user_tables where table_name like '%emp%';

案例26 入职时间不是null的数据总数

 select count(hiredate) from emp_xxx;

sqlplus 命令
查看当前用户账户 show user

组函数 count() arv() sum() max() min()
与单行函数round(),to_date() to_char() coalesce()等不同，单行函数
是每行函数返回一个结果，组函数是多行数据返回一个结果
案例27 计算员工的薪水总和是多少

  select sum(salary) from emp_xxx;

案例28 计算员工的薪水平均值是多少

select avg(salary) from emp_xxx;

//注意，薪水为null的情况下不参与平均薪値的计算
计算员工的人数综合，薪水总和，平均薪水是多少

 select count(*) num ,sum(salary) sum,avg(salary) avg from emp_xxx;

//薪水平均值=薪水总和/人数总和，avg(salary)=sum(salary)/count(*)
//avg(salary)只按有薪水的员工人数计算平均值，这样得到的数据不够准确
案例29 计算员工的最高薪水和最低薪水

  select max(salary) 高薪,min(salary) 最低薪 from emp_xxx;

案例30 计算员工最早和最晚入职时间

 select max(hiredate) max_hiredate,min(hiredate) min_hiredate from emp_xxx;

//此处课件上有错误，max是计算入职时间最晚的，min使计算最早的

分组查询 group by **
案例31 按部门计算每个部门的最高和最低薪水是多少

  select deptno,max(salary),min(salary) from emp_xxx group by deptno;

案例32 计算每个部门的薪水总和和平均薪水

  select deptno,sum(salary),avg(salary)from emp_xxx group by deptno;

案例33 每个部门的统计信息

 select deptno,max(salary) max_s,min(salary) min_s,sum(salary) sum_s 
      from emp_xxx group by deptno;

案例34 按职位分组，每个职位的最高最低薪水和人数

 select job,max(salary) max_s,min(salary) min_s,count(*) emp_num
     from emp_xxx group by job order by emp_num;

案例35 如果select后没有被组函数的列，没有出现在group by 短语中，会出错

 select deptno,max(salary) max_s,
                  min(salary) min_s,
                  sum(salary) sum_s,
                  avg(nvl(salary,0)) avg_s,
                  count(*) emp_num
                  from emp_xxx;

//此查询语句是错误的，原因deptno不是单组分组函数
案例36 如果group by 短语中的列，没有出现在select短语中，不会出错，信息不够全

   select max(salary) max_s,
              min(salary) min_s,
              sum(salary) sum_s,
              avg(nvl(salary,0)) avg_s,
              count(*) emp_num
              from emp_xxx
              group by deptno;
       select deptno,max(salary) max_s,
              min(salary) min_s,
              sum(salary) sum_s,
              avg(nvl(salary,0)) avg_s,
              count(*) emp_num
              from emp_xxx
              group by deptno;

//如果group by 短语中的列，没有出现在select短语中，不会出错，信息不够全

having 子句
having 自己用于对分组后的数据进行过滤。
注意区别where是对表中数据的过滤，having是堆分组得到的结果数据进一步过滤。
案例37 平均薪水大于5000元的部门数据，没有部门的不算在内？

 select  deptno ,avg(nvl(salary,0)) avg_s
           from emp_xxx 
           where deptno is not null
           group by deptno having
           avg(nvl(salary,0))>5000 ;

案例38 考察的还是having 对分组得到的结果进一步过滤功能
薪水总和大于20000元的部门数据

   select deptno,avg(nvl(salary,0)) avg_s,sum(salary)
            from emp_xxx
            where deptno is not null
            group by deptno
            having sum(salary)>20000;

案例39 哪些职位的人数超过2名

  select job ,count(*) emp_num
          from emp_xxx
          where job is not null
          group by job
          having count(*)>2
          order by emp_num;

//这一个案例很经典，我感觉还没有完全掌握
//order by 一定要放在最后
子查询
案例40 查询最高薪水是谁

select max(salary) max_s from emp_xxx;

查询每个部门的最高薪水是

  select deptno,max(salary) max_s from emp_xxx group by deptno;

//查询到该表中最高的薪水是
//利用到子查询的知识

    select ename from emp_xxx 
          where salary=(select max(salary) max_s from emp_xxx);

案例41 查询最低薪水的人是谁？
//首先更新数据，方法如下

 update emp_xxx
          set salary=100
          where ename='郭靖';

//查询最低薪水的人是

   select ename ename_min,deptno deptno_min 
          from emp_xxx
          where salary=(select min(salary) from emp_xxx);