0、oracle函数

目录

• 函数
  • 单行函数
    • 数值函数
    • 字符型函数
    • 日期函数
    • 转换函数
    • 其他函数
      • decode
      • case when
  • 组函数
  • 通用函数（nvl(e.comm,0)）
  • 数据分组
  • 练习01

函数

函数一般是在数据上执行的，它给数据的转换和处理提供了方便。只是将取出的数据进行处理，不会改变数据库中的值。

sql函数分为两种，一种是组函数，一种是单行函数。

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

单行函数

分类：

数值函数

--数值函数
     --向上取整 结果为4  ceil
     select ceil(3.1) from dual;
     --向下取整 结果为3  floor
     select floor(3.9) from dual;
     --返回x的y次幂,  power(x,y)
     select power(2,2) from dual;
     --返回四舍五入后的值，round()
            --如果第二个参数是正数，保留小数点后面两位小数
     select round(123.123123,2) from dual;
            --如果第二个参数为负数，看小数点前的第几位，不保留小数。
     select round(123.123123,-2) from dual;
     select round(123.123,-2.6) from dual;
     --返回x的平方根，sqrt() ,结果为8
     select sqrt(64) from dual;
  --mod（x，y）求余数
     select mod(23,8) from dual;
  --trunc（number，n）  直接截断
     select trunc(23.652) from dual;
     select trunc(23.652, 2) from dual;
     select trunc(23.652, -1) from dual;

字符型函数

--字符型函数
     --连接两个字符，concat(str1,str2),用单引号 
     select concat('abc','de') from dual;
            --字符嵌套,从里面往外面，里面的作为外面的第一个字符。
            select concat(concat('ab','c'),'de') from dual;
     --把每个单词的首字母变为大写，其余都是小写，initcap();
     select initcap('ABCDEF') from dual;
     --将整个字符转换为小写，lower();
     select lower('ABDCK') from dual;
     --将整个字符转换为大写，upper();
     select upper('ada fda') from dual;
     --字符串中搜索字符的位置，instr(c1,c2[,i[,j]]);汉字按一个字符，instrb()汉字按2个字符
     --索引从1开始的
     select instr('abcde','c')from dual;
     --在左边添加字符，lpad(c1,n[,c2]);n代表会显示多少个字符，不够就在左边用c2代替，默认用空格
     select lpad('adb',8,'*') from dual;
     --在右边添加字符,rpad();
     select rpad('acb',5) from dual;
     --删除左边的字符串，ltrim(c1,[,c2]);c2默认是空格
     select ltrim(' acv ') from dual;
     --删除右边的字符串，rtrim(c1[,c2]);
     select rtrim(' cbc ') from dual;
     --删除两边的字符串，trim(c1[,c2]);
     select trim(' cac c ac ') from dual;
     --替换字符，replace(c1,c2[,c3]); 用i替换了he
     select replace('he love you','he','i') from dual;
     --截取字符，substr(c1,n1[,n2]),n1是从第几位开始截取，n2是截取多少位，n2可以没有，默认就截取到结尾，
     --是一个汉字一个字符，索引从1开始，substrb()是一个汉字2个字符。
     select substr('acbde',2,2)from dual;
     --返回字符串长度，length()
     select length('abcde') from dual;
  

日期函数

--日期函数
     --返回当前日期，sysdate;
     select sysdate from dual;
     --返回指定月数后的日期，add_months(date,n);date是指定日期，n是跳到几个月后的日期
     select add_months(sysdate,2) from dual;
     --返回本月的最后一天的日期,last_day();
     select last_day(sysdate) from dual;
     --返回两个日期间隔的月数，months_between();
     select months_between('1-2月-2020',sysdate) from dual;
     --日期时间格式的数值,前面多个+号
        --以天或天更小单位时可用数值表达式借用，如1表示1天，1/24表示1小时，1/24/60表示1分钟
             select
            trunc(sysdate)+(interval '1' second), --加1秒(1/24/60/60)
            trunc(sysdate)+(interval '1' minute), --加1分钟(1/24/60)
            trunc(sysdate)+(interval '1' hour), --加1小时(1/24)
            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 localtimestamp from dual;
 

转换函数

格式元素	含义
YYYY、YY	代表四位、两位数字的年份
MM	用数字表示的月份
MON	月份的缩写、对中文月份来说就是全称
DD	数字表示的日
DY	星期的缩写，对中文的星期来说就是全称
HH24，HH12	12小时或者24小时进制下的时间
MI	分钟数
SS	秒数

控制符	含义
9	代表一位数字，如果该位没有数字则不进行显示，但对于小数点后面的部分仍会强制显示
0	代表一位数字，如果该位没有数字则强制显示0
$	显示美元符号
L	显示本地货币符号
.	显示小数点
,	显示千分位符号

--转换函数
       --to_char
              --将日期转换成字符，to_char(date,日期格式)
              --YYYY-MM-dd   年月日
              --HH24:mi:SS 时分秒,HH24表示24小时制，HH12表示12小时制
              --DY 星期数
              select to_char(sysdate,'YYYY-MM-dd HH24:mi:SS DY') from dual;
              --将数字转换成字符，to_char(number,format);
              --9表示自动匹配字符的个数，0则从前面补0
              --     ， 还是  ，
              --     $ 还是 $
              select to_char(1232123.123,'$999,999,999.999') from dual;
       --to_number
              --将字符转成数字，第二个参数表示怎样去识别字符
              select to_number('$123,123.123','$999,999.999') from dual;
       --to_date
              --将字符转成日期，第二个参数表示怎样去识别日期的年月日
              select to_date('20001025','YYYYMMdd') from dual;

其他函数

decode

相当于switch语句

--decode
      --查询部门是10的涨薪10%，部门是20的涨薪20%，部门是30的涨薪30%，其他的涨薪15%。
      select e.ename,
             e.sal 涨薪前,
             decode(e.deptno,
                    10,
                    e.sal * 1.1,
                    20,
                    e.sal * 1.2,
                    30,
                    e.sal * 1.3,
                    e.sal * 1.15) 涨薪后
        from emp e；

case when

相当于if-else-if语句

--case when
       --查询500-1500薪资的为贫穷群众，1500-2500为贫困群众，2500-5500为小康群众，5500以上为大康群众。
      select e.ename,
             e.sal 薪资,
             case
               when e.sal < 1500 then
                '贫穷群众'
               when e.sal >= 1500 and e.sal < 2500 then
                '贫困群众'
               when e.sal >= 2500 and e.sal < 5500 then
                '小康群众'
             -- when e.sal >=5500 then '大康群众 '
               else
                '大康群众'
             end "身份"
        from emp e;

组函数

组函数是从一组数据中产生一个值。

例如：

1. avg()求平均值

2. max()求最大值

3. min()求最小值

4. sum()求和

5. count()返回某列的行数

   可以通过group by 来分组

   group by 后面的字段是分组的标志，要想select后面有字段，此字段必须是跟group by 后面的字段一致。

   组函数除了count()外，都跳过空值而处理非空值
   select count() from emp;
   select count(comm) from emp;
   select count(1) from emp;
   不能计算空值
   select count(distinct deptno) from emp;

   --组函数
   --求平均值
   select e.deptno,avg(e.sal) 平均值
   from emp e
   group by e.deptno;
   --求最大最小值，总和，总条数
   select e.deptno,max(e.sal) 最大值,min(e.sal) 最小值,sum(e.sal) 总和,count(e.sal) 总条数 
   from emp e
   group by e.deptno;

组函数不能处理null
select avg(comm) from emp;
NVL函数迫使分组函数包括空值
select avg(nvl(comm,0)) from emp;

通用函数（nvl(e.comm,0)）

Sql中允许列值为空，空值用保留字NULL表示。NULL不同与0或者空格，它就是代表了一个不确定的内容。任何含有null值的数学表达式最后的结果都为空值

select ename,sal,comm,(sal*12)+comm “income” from emp;
可以通过函数nvl将NULL转换成为一个不为空的值
select ename, comm, nvl(comm,0)  from emp 
select ename,sal,comm ,(sal*12)+nvl(comm,0) “income” from emp

数据分组

group by 分组的关键字

Group by 子句可以包含任意数目的列。
除组函数语句外，select语句中的每个列都必须在group by 子句中给出。
如果分组列中具有null值，则null将作为一个分组返回。如果列中有多行null值，他们将分为一组。
Group by 子句必须出现在where子句之后，order by 子句之前。
过滤分组（having子句)
Where过滤行，having过滤分组。
Having支持所有where操作符。
分组和排序
一般在使用group by 子句时，应该也给出order by子句。

练习01

--day02练习01
--1、查询82年员工
--select e.* from emp e where e.hiredate between  '1-1月-82' and '31-12月-82';
select e.* from emp e where to_char(e.hiredate, 'YYYY') in ('1982');
 
--2、查询32年工龄的人员
select e.*, trunc(months_between(sysdate, e.hiredate) / 12)
  from emp e
 where trunc(months_between(sysdate, e.hiredate) / 12) = 32;
 
--3、显示员工雇佣期 6 个月后下一个星期一的日期
select e.hiredate,next_day(add_months(e.hiredate,6),'星期一'),
from emp e;
 
--4、找没有上级的员工，把mgr的字段信息输出为 "boss"
select e.ename,nvl(to_char(mgr),'boss')
from emp e;
 
--5、为所有人长工资，标准是：10部门长10%；20部门长15%；30部门长20%其他部门长18%
select e.sal,
       decode(e.deptno,
              10,
              e.sal * 1.1,
              20,
              e.sal * 1.15,
              30,
              e.sal * 1.2,
              e.sal * 1.18)
  from emp e;