04.oracle函数

04.oracle函数

概念

输入 -> (函数) -> 输出结果

sql的函数一般是在数据上执行,处理数据,不会改变数据库中的值;

分类

组函数: 聚合函数,用于对多行数据进行操作,并返回一个单一的结果,组函数仅可用于选择列表或查询的having子句

单行函数: 单个数值进行操作,并返回一个值

单行函数

• 字符函数

  • 返回字符类型

  • 返回数字类型

    --字符函数
    --concat：表示字符串的连接  等同于||
    select concat('my name is ',ENAME)
    from EMP;
    --将字符串的首字母大写
    select initcap(ENAME)
    from EMP;
    --将字符串全部转换为大写
    select upper(ENAME)
    from EMP;
    --将字符串全部转换为小写
    select lower(ENAME)
    from EMP;
    --填充字符串 lpad rpad
    select lpad(ENAME,10,'*')
    from EMP;
    select rpad(ENAME,10,'*')
    from EMP;
    -- 去除空格 trim ltrim rtrim
    select trim(ENAME)
    from EMP;
    
    select ltrim(ENAME)
    from EMP;
    
    select rtrim(ENAME)
    from EMP;
    -- 查找指定字符串的位置 instr
    select instr('ACABBB','A')
    from dual;
    -- 查看字符串的长度 length
    select length(ENAME) from emp;
    -- 截取字符串 substr
    select substr(ENAME,0,2) from emp;
    -- 替换操作 replace
    select replace('abckassjfalfajlabc','abc','acdc') from dual;
    

• 数字函数

  • 输入为number类型,返回为number类型

    -- 四舍五入 round
    select round(123.123,2) from dual;
    select round(123.128,2) from dual;
    select round(-123.128,2) from dual;
    -- 截断数据,按照位数去进行截取，但是不会进行四舍五入的操作 trunc
    select trunc(123.123,2) from dual;
    -- 取模操作 mod
    select mod(10,4)
    from dual;
    select mod(-10,4)
    from dual;
    -- 向上取整 ceil
    select ceil(12.12)
    from dual;
    -- 向下去整 floor
    select floor(12.12) from dual;
    -- 取绝对值 abs
    select abs(-100)
    from dual;
    -- 获取正负值 正为 1 负为 -1 sign
    select sign(100)
    from dual;
    -- power x的y次幂
    select power(2,3)
    from dual;
    

• 日期函数

  -- 日期函数
  -- 返回当前的日期
  select sysdate
  from dual;
  select current_date
  from dual;
  --add_months,添加指定的月份
  select add_months(HIREDATE,2),HIREDATE
  from EMP;
  
  -- last_day 返回输入日期所在月份的最后一天
  select last_day(sysdate)
  from dual;
  --两个日期相间隔的月份
  select sysdate,HIREDATE,months_between(sysdate,HIREDATE)
  from emp;
  
  -- round: 返回四舍五入的第一天
  select sysdate 当时日期,
         round(sysdate) 最近0点日期,
         round(sysdate,'day') 最近星期日,
         round(sysdate,'month') 最近月初,
         round(sysdate,'q') 最近季初,
         round(sysdate,'year') 最近年初日期
  from dual;
  -- next_day 返回下周的星期几
  select next_day(sysdate,'星期日') from dual;
  
  -- extract 提取日期中的时间
  select
         extract(hour from timestamp '2001-2-16 2:36:40') 小时,
         extract(minute from timestamp '2001-2-16 2:36:40') 分,
         extract(second from timestamp '2001-2-16 2:36:40') 秒,
         extract(day from timestamp '2001-2-16 2:36:40') 日,
         extract(month from timestamp '2001-2-16 2:36:40') 月,
         extract(year from timestamp '2001-2-16 2:36:40') 年
  from dual;
  -- 返回日期的时间戳
  select localtimestamp
  from dual;
  select current_date
  from dual;
  select current_timestamp
  from dual;
  -- 给指定的时间单位增加数值
  select
  trunc(sysdate) + (interval '1' second ), -- 加1秒
  trunc(sysdate) + (interval '1' minute ), -- 加1分
  trunc(sysdate) + (interval '1' hour ), -- 加1小时
  trunc(sysdate)+(INTERVAL '1' DAY),  --加1天(1)
  trunc(sysdate)+(INTERVAL '1' month ),  --加1个月
  trunc(sysdate)+(INTERVAL '1' year ),  --加1 年
  trunc(sysdate)+(INTERVAL '01:02:03' hour to second ),  --指定小时到秒
  trunc(sysdate)+(INTERVAL '01:02' minute to second ),  --加指定分钟到秒
  trunc(sysdate)+(INTERVAL '01:02' hour to minute ),  -- 加指小时到分钟
  trunc(sysdate)+(INTERVAL '2 01:02' day to minute )  -- 加指小时到分钟
  from dual;
  

• 转换函数

  • 隐式转换

    select '999' + 10 from dual;
    

  • 显式转换

    -- date : to_char
    select to_char(sysdate,'yyyy-MI-SS HH24:MI:SS')
    from dual;
    -- number ： to_char
    select to_char(123.456,'99999999') from DUAL;
    select to_char(123.456789,'0000.00') from DUAL;
    select to_char(123.456789,'$0000.00') from DUAL;
    select to_char(123.456789,'L0000.00') from DUAL;
    select to_char(123456789,'999,999,999') from DUAL;
    -- to_date: 转换之后都是固定的格式
    select to_date('2020/12/29 10:10:10','YYYY-MM-DD HH:MI:SS') from dual;
    -- to_number: 转换为数字
    select to_number('123,456,789','999,999,999')
    from dual;
    
    -- 显示没有上级管理的公司首脑
    select ENAME,nvl(to_char(MGR),'boss')
    from EMP where MGR is null;
    -- 显示员工雇佣期满6个月后下一个星期五
    select HIREDATE,next_day(add_months(HIREDATE,6),'星期五')
    from emp;
    

• 其他函数

  • 条件函数

    -- 条件函数
    -- decode, case when
    
    -- 给不同部门的人员涨薪，10部门涨10%，20部门涨20%，30部门涨30%
    select ENAME,sal,DEPTNO,
           decode(DEPTNO,10,SAL*1.1,20,SAL*1.2,30,SAL*1.3) 涨薪
    from EMP;
    
    -- 给不同部门的人员涨薪，10部门涨10%，20部门涨20%，30部门涨30% - case when
    select ENAME,sal,DEPTNO,
           case DEPTNO
               when 10 then SAL*1.1
               when 20 then SAL*1.2
               when 30 then SAL*1.3
               end 涨薪
    from EMP;
    

  • 组合函数

    /*
    组函数,一般情况下，组函数都要和groupby组合使用
    组函数一般用于选择列表或者having条件判断
    常用的组函数有5个
    avg()  平均值,只用于数值类型的数据
    min()  最小值，适用于任何类型
    max()  最大值，适用于任何类型
    count() 记录数,处理的时候会跳过空值而处理非空值
        count一般用来获取表中的记录条数，获取条数的时候可以使用*或者某一个具体的列
           甚至可以使用纯数字来代替，但是从运行效率的角度考虑，建议使用数字或者某一个具体的列
           而不要使用*
    
    sum()   求和，只适合数值类型的数据
    */
    
    
    select avg(SAL)
    from EMP;
    
    select min(SAL)
    from EMP;
    
    select max(SAL)
    from EMP;
    
    select count(SAL)
    from EMP;
    
    select sum(SAL)
    from emp;
    /*
        group by,按照某些相同的值去进行分组操作
        group进行分组操作的时候，可以指定一个列或者多个列，但是当使用了group by 之后，
        选择列表中只能包含组函数的值或者group by 的普通字段
    */
    -- 求每个部门的平均薪水
    select round(avg(SAL),2)
    from EMP group by DEPTNO;
    -- 求平均薪水大鱼2000的部门
    select round(avg(SAL),2),DEPTNO
    from EMP group by DEPTNO having avg(SAL) >2000 order by avg(SAL) desc;
    
    select count(100000)
    from EMP;
    -- 部门下雇员的工资>2000 人数
    select count(1)
    from EMP
    where SAL > 2000 ;
    
    -- 每个部门薪水最高
    select DEPTNO,max(SAL)
    from EMP group by DEPTNO;
    
    -- 部门里面 工龄最小和最大的人找出来  使用了group by 之后，
    --     选择列表中只能包含组函数的值或者group by 的普通字段
    select DEPTNO,min(HIREDATE),max(HIREDATE)
    from EMP group by DEPTNO;
    
    select ENAME,DEPTNO
    from EMP where
                   HIREDATE in (select min(HIREDATE) from EMP group by DEPTNO)
            or HIREDATE in (select max(HIREDATE) from EMP group by DEPTNO);
    
    select mm2.DEPTNO,e1.ENAME,e1.HIREDATE
    from EMP e1,
         (select min(e.HIREDATE) mind,
                 max(e.HIREDATE) maxd,
                 e.DEPTNO
         from EMP e group by DEPTNO) mm2
    where (e1.HIREDATE = mm2.mind or e1.HIREDATE = mm2.maxd) and e1.DEPTNO = mm2.DEPTNO;