Oracle数据库之内置函数和Case表达式练习

小灰灰 2021-03-29 15:02 662阅读 0赞

在这里插入代码片select * from emp;
    --substr()截取字符串
    select substr('abgrfea', 4) from dual;
    select substr('abgrfea', 4, 2) from dual;
    select substr('abgrfea', -3, 2) from dual;
    select length('汉') from dual; --字符
    select lengthb('汉') from dual; --字节
    --round()四舍五入
    select round(321.636155,2) from dual;
    select round(321.030, -2) from dual; --从整数开始从右往左取零
    select round(p.sal * 0.3, 2) 税金 from emp p;
    --mod()取余
    select mod(10, 3) from dual;
    --trunc()截断，直接截掉
    select trunc(421.16, 1) from dual;
    select trunc(421.16, -2) from dual;
    ---trunc对时间的处理
    select trunc(sysdate, 'yyyy') from dual; --本年度第一天
    select trunc(sysdate, 'month') from dual; --本月第一天
    select trunc(sysdate, 'day') from dual; --本周第一天
    select trunc(sysdate, 'hh24') from dual;
    select trunc(sysdate, 'mi') from dual;
    --查询雇员信息，入职时间是85年的人
    select * from emp p where trunc(p.hiredate, 'year') = date '1981-01-01';
    --months_between()
    select months_between(sysdate, sysdate + 1) from emp;
    select sysdate + 1 from dual; --直接加数字，相当于加天数
    --add_months()
    select add_months(sysdate, 1) from dual;
    --next_day()从当前日期算，下一个星期一是哪天
    select next_day(sysdate, '星期一') from dual;
    --last_day() 当前日期的最后一天
    select last_day(sysdate) from dual;
    --to_date()返回的是日期类型
    select to_date('2008-8-8-15-12-56', 'yyyy-mm-dd hh24:mi:ss') from dual;
    --to_char()
    select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
    --to_number()
    select to_number('13') + to_number('14') from dual;
    select '2' + '9' from dual;
    --通用函数
    --nvl()
    select nvl(comm, 0) from emp;
    --查看某人一年的实际收入（薪水*12+奖金）
    select p.*, p.sal * 12 + p.comm 实际收入 from emp p;
    select p.*, nvl(p.sal, 0) + nvl(p.comm, 0) 实际收入, nvl(p.comm, 0) 奖金
      from emp p;
    select p.*,
           round(nvl(p.sal, 0) + nvl(p.comm, 0), 2) 实际收入,
           nvl(p.comm, 0) 奖金
      from emp p;
    select p.*,
           to_char(round(nvl(p.sal, 0) + nvl(p.comm, 0), 2), 'fm9999999.00') 实际收入,
           nvl(p.comm, 0) 奖金
      from emp p;
    ---nullif()
    select nullif('a', 'a') from dual;
    select nullif('a', 'b') from dual;
    --nvl2()
    select nvl2('','a','b') from dual;
    select nvl2('abc','a','b') from dual;
    select p.empno,p.ename,p.sal,p.comm,nvl2(comm,sal+comm,sal) from emp p;
    --coalessce()了解即可，遇到空值就过
    select coalesce('','','','abc','efg','fsf') from dual;
    --decode()实现一个多路分支的比较
    --如果部门编号是20的话显示教学部
    select p.*, decode(p.deptno, 20, '教学部', '其他部门') from emp p;
    select p.*,
           decode(p.deptno,
                  20,
                  '教学部',
                  '30',
                  '市场部',
                  '10',
                  '财务部',
                  '其他')
      from emp p;
    --case表达式
    ---如果部门编号是20的话显示教学部,30市场部，10 财务部，其他数字为其他部门请写出一个标准的SQL语句
    select p.*,
           case
             when p.deptno = 20 then
              '教学部'
             when p.deptno = 30 then
              '市场部'
             when p.deptno = 10 then
              '财务部'
             else
              '其他部门'
           end 部门名称
      from emp p;
      select p.*,
           case
             p.deptno
             when  20 then
              '教学部'
             when  30 then
              '市场部'
             when 10 then
              '财务部'
             else
              '其他部门'
           end 部门名称
      from emp p;
      select p.*,
             case
               when p.hiredate >= date '1985-01-01' then
                '新员工'
               when p.hiredate < date '1985-01-01' then
                '老员工'
             end 老旧员工
        from emp p
        order by 老旧员工;
    --作业：
      --统计一下雇员的收入情况，包括雇员的名称，雇员的部门名称，雇员的税率（3000以下 税率0.1 300-500 税率0.2 5000以上0.3），税额。
      --实际收入（总收入包含工资加奖金-税额），而且实际收入保留2位小数点
      select * from emp;
      select p.*,
             p.ename 雇员名称,
             case p.deptno
               when 20 then
                '教学部'
               when 30 then
                '市场部'
               when 10 then
                '财务部'
               else
                '其他部门'
             end 部门名称,
             nvl(p.sal, 0) + nvl(p.comm, 0) 税前总收入,
             case
               when (nvl(p.sal, 0) + nvl(p.comm, 0)) < 3000 then
                '0.1'
               when (nvl(p.sal, 0) + nvl(p.comm, 0)) >= 3000 and
                    (nvl(p.sal, 0) + nvl(p.comm, 0)) <= 5000 then
                '0.2'
               when (nvl(p.sal, 0) + nvl(p.comm, 0)) > 5000 then
                '0.3'
             end 税率,
             case
               when (nvl(p.sal, 0) + nvl(p.comm, 0)) < 3000 then
                (nvl(p.sal, 0) + nvl(p.comm, 0)) * 0.1
               when (nvl(p.sal, 0) + nvl(p.comm, 0)) >= 3000 and
                    (nvl(p.sal, 0) + nvl(p.comm, 0)) <= 5000 then
                (nvl(p.sal, 0) + nvl(p.comm, 0)) * 0.2
               when (nvl(p.sal, 0) + nvl(p.comm, 0)) > 5000 then
                (nvl(p.sal, 0) + nvl(p.comm, 0)) * 0.1 * 0.3
             end 税额,
             case
               when (nvl(p.sal, 0) + nvl(p.comm, 0)) < 3000 then
                round((nvl(p.sal, 0) + nvl(p.comm, 0)) -
                      (nvl(p.sal, 0) + nvl(p.comm, 0)) * 0.1,
                      2)
               when (nvl(p.sal, 0) + nvl(p.comm, 0)) >= 3000 and
                    (nvl(p.sal, 0) + nvl(p.comm, 0)) <= 5000 then
                round((nvl(p.sal, 0) + nvl(p.comm, 0)) -
                      (nvl(p.sal, 0) + nvl(p.comm, 0)) * 0.2,
                      2)
               when (nvl(p.sal, 0) + nvl(p.comm, 0)) > 5000 then
                round((nvl(p.sal, 0) + nvl(p.comm, 0)) -
                      (nvl(p.sal, 0) + nvl(p.comm, 0)) * 0.1 * 0.3,
                      2)
             end 实际收入
        from emp p;