SQL常用函数

常用函数

常用函数
Oracle SQL提供了用于执行特定操作的专用函数。这些函数大大增强了SQL语言的功能。
函数可以接受零个或者多个输入参数，并返回一个输出结果。Oracle数据库中主要使用两种类型的函数：
1.单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果，
比如：MOD(x,y)返回x除以y的余数（x和y可以是两个整数，也可以是表中的整数列）。

常用的单行函数有：
字符函数：对字符串操作。
数字函数：对数字进行计算，返回一个数字。
转换函数：可以将一种数据类型转换为另外一种数据类型。
日期函数：对日期和时间进行处理。

2.聚合函数：聚合函数同时可以对多行数据进行操作，并返回一个结果。比如SUM(x)返回结果集中x列的总合。

1.字符函数
  函数  说明
  ASCII(x)  返回字符x的ASCII码。
  CONCAT(x,y) 连接字符串x和y。
  INSTR(x, str [,start] [,n)  在x中查找str，可以指定从start开始，也可以指定从第n次开始。
  LENGTH(x) 返回x的长度。
  LOWER(x)  x转换为小写。
  UPPER(x)  x转换为大写。
  LTRIM(x[,trim_str]) 把x的左边截去trim_str字符串，缺省截去空格。
  RTRIM(x[,trim_str]) 把x的右边截去trim_str字符串，缺省截去空格。
  TRIM([trim_str FROM] x) 把x的两边截去trim_str字符串，缺省截去空格。
  REPLACE(x,old,new)  在x中查找old，并替换为new。
  SUBSTR(x,start[,length])  返回x的字串，从staart处开始，截取length个字符，缺省length，默认到结尾。

案例:
  select ascii('0') from dual;
  select concat('hello','world') from dual;
  select instr('HellorWorld', 'or', 7) from dual;
  select length('HelloWorld') from dual;
  -- 查询 名字及长度为 4 
  select * from emp where length(ename) = 4;
  select lower('HelloWorld') from dual;
  select upper('HelloWorld') from dual;
  select ltrim('===HelloWorld','=') from dual;
  select rtrim('HelloWorld===','=') from dual;
  select trim('=' from '===HelloWorld==') from dual;
  select replace('ABCDE','AB', 'HEHE') from dual;
  select substr('ABCDE', 3, 2) from dual;

2.数字函数
  函数  说明  示例
  ABS(x)  x绝对值  ABS(-3)=3
  ACOS(x) x的反余弦 ACOS(1)=0
  COS(x)  余弦  COS(1)=1.57079633
  CEIL(x) 大于或等于x的最小值  CEIL(5.4)=6
  FLOOR(x)  小于或等于x的最大值  FLOOR(5.8)=5
  LOG(x,y)  x为底y的对数 LOG(2,4)=2
  MOD(x,y)  x除以y的余数 MOD(8,3)=2
  POWER(x,y)  x的y次幂 POWER(2,3)=8
  ROUND(x[,y])  x在第y位四舍五入 ROUND(3.456,2)=3.46
  SQRT(x) x的平方根 SQRT(4)=2
  TRUNC(x[,y])  x在第y位截断 TRUNC(3.456,2)=3.45

  1.ROUND(X[,Y])，四舍五入。
  在缺省y时，默认y=0；比如：ROUND(3.56)=4。
  y是正整数，就是四舍五入到小数点后y位。ROUND(5.654,2)=5.65。
  y是负整数，四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400。

  select round(3.26) from dual;  -- 保留整数 
  select ROUND(5.655,2) from dual; -- 保留2位小数
  -- 日薪 
  select ename,sal,round(sal / 30,1) 日薪 from emp;
  select ROUND(341.654,-2) from dual;
  
  
  2. TRUNC(x[,y])，直接截取，不四舍五入。
  在缺省y时，默认y=0；比如：TRUNC (3.56)=3。
  y是正整数，就是四舍五入到小数点后y位。TRUNC (5.654,2)=5.65。
  y是负整数，四舍五入到小数点左边|y|位。TRUNC (351.654,-2)=300。

  select TRUNC(5.654) from dual; -- 截取去除小数后面的 
  select TRUNC(5.658,2) from dual;
  select TRUNC(351.654,-2) from dual;


3.日期函数
  1.ADD_MONTHS(d,n)，在某一个日期d上，加上指定的月数n，返回计算后的新日期。d表示日期，n表示要加的月数。
  select add_months(sysdate, 12) from dual;
  
  2.LAST_DAY(d)，返回指定日期当月的最后一天。
  select LAST_DAY(sysdate) from dual;

  3.ROUND(d[,fmt])，返回一个以fmt为格式的四舍五入日期值，d是日期，fmt是格式模型。默认fmt为DDD，即月中的某一天。
  如果fmt为“YEAR”则舍入到某年的1月1日，即前半年舍去，后半年作为下一年。
  如果fmt为“MONTH”则舍入到某月的1日，即前月舍去，后半月作为下一月。
  默认为“DDD”，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天。
  如果fmt为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日。

  select sysdate, round(sysdate), 
                  round(sysdate, 'YEAR'), 
                  round(sysdate, 'MONTH'),
                  round(sysdate, 'DDD'),
                  round(sysdate, 'DAY') FROM DUAL;
                  
  4.EXTRACT(fmt FROM d)，提取日期中的特定部分。
    fmt为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。
    其中YEAR、MONTH、DAY可以为DATE类型匹配，也可以与TIMESTAMP类型匹配；
    但是HOUR、MINUTE、SECOND必须与TIMESTAMP类型匹配。
    
    SELECT EXTRACT(YEAR FROM SYSDATE),
           EXTRACT(MONTH FROM SYSDATE) ,
           EXTRACT(DAY FROM SYSDATE) ,
           EXTRACT(HOUR FROM SYSTIMESTAMP) ,
           EXTRACT(MINUTE FROM SYSTIMESTAMP) ,
           EXTRACT(SECOND FROM SYSTIMESTAMP)  FROM DUAL;
           
   -- 查看 我们的emp的员工的工龄  (当前的事件 - 入职时间)
   
   select ename,job,hiredate from emp;
   
   -- 哪一年入职
   select ename,job,hiredate,EXTRACT(YEAR from hiredate) from emp;
   -- 查看工龄
    select ename,job,hiredate,EXTRACT(YEAR from Sysdate) - EXTRACT(YEAR from hiredate) from emp;
    
    -- 查看二月份入职的员工  获取月份 
     select ename,job,hiredate from emp
     where extract(MONTH FROM hiredate) = 2;
   
           

  5.日期转换函数
    to_date : 将字符串转换成日期
    to_char : 将日期转换成字符串
    注意:
            yyyy表示四位年份
            mm表示两位月份
            dd表示两位天数
            hh24表示24小时，hh12表示12小时
            mi表示分钟
            ss表示秒钟
         select sysdate,
                to_char(sysdate,'yyyy/mm/dd hh:mi:ss'),
                to_char(sysdate, 'yyyy-mm-dd'),
                to_char(sysdate, 'yyyy-mm'),
                to_char(sysdate, 'yyyy')  
                from dual;
                
        注意 sql 中字符串是数字 可以进行运算 
         -- 工龄的计算 
        select ename,job,hiredate from emp;
        
        select ename,job,hiredate,to_char(hiredate,'yyyy') 入职年份 from emp;
        
        -- 工龄
        select ename,job,hiredate,(to_char(sysdate,'yyyy') - to_char(hiredate,'yyyy')) 工龄 from emp;
        
        

其他函数
6.nvl(column, value) 如果column为空，返回value，否则返回x。
   -- 查看 奖金 
   select ename,job,sal,comm from emp;
   
   -- 查看总工资 
    select ename,job,sal + comm 月收入 from emp;
   -- 有的人没有工资  (sal + comm ) 如果comm为null  结果为null  不合理 
   
   使用nvl 函数 nvl(x,y) x如果为null 可以设置值 y
     select ename,job,sal,nvl(comm,0) from emp;
    -- 查看收入 
    select ename,job,sal + nvl(comm,0) 月收入 from emp;
     
7.decode: 类似于Java当中 if 、 if else、if else if else
     create table stu(
        sid number(2)，
        sname varchar2(20)，
        ssex number(1)
     );
    
    select sid,sname,ssex
           , decode(ssex, 1, '女') 
           , decode(ssex, 1, '女', 2, '男')
           , decode(ssex, 1, '女', 2, '男','未知')        
         from stu;