Oracle中常用的函数

  ---sql的函数的使用(Oracle)      
      ---dual的使用：哑元表：没有表需要查询的时候 可以用它
               ---select 'Hello World' from dual;     ---结果：Hello World
    ---单行函数：单行
     1.日期函数：系统时间：sysdate---select sysdate from dual;                       ---当前的系统时间---结果：2010-05-14
           sysdate+(-)整数的含义---select sysdate+1 from dual;        ---当前系统日期的后一天---结果：2010-05-15
                    ---select sysdate-7 from dual;        ---当前系统日期的上一周---结果：2010-05-07          
        日期间间隔的天数：日期1+(-)日期2---select (sysdate-hiredate) as 天数 from emp;     
        日期间间隔的月份：months_between(day1,day2)：返回day1日期和day2日期之间相差的月份
               ---select months_between(sysdate,hiredate) as 月份 from emp;   
           to_char(day,'格式')：将一个数字或日期转换为字符串
                  ---select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; ---结果：2010-05-14 18:49:07
                    ---格式变化：---select to_char(sysdate,'d') from dual;---今天是这一周的第几天---结果：6（周五）
                       ---select to_char(sysdate,'DAY') from dual;---今天是周几---结果：星期五
                       ---select to_char(sysdate,'DDD') from dual; ---今天是这一年中的第多少天---结果：134
                       ---select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss d day ddd') from dual;---2010-05-14 19:10:29 6 星期五 134
                       ---select to_char(sysdate,'fmyyyy-mm-dd') from dual;                   ---取消月日前面的0---结果：2010-5-17
                       ---select to_char(sysdate,'Q') from dual;   ---当前时间的季度数，1~3为第一季度---结果：2
                       ---select to_char(sysdate,'WW') from dual;  ---当前时间是当年的第几周
                  eg：本季度第一天
                    ---select trunc(sysdate,'Q') from dual;
                    ---select trunc(sysdate,'D') from dual;
                  eg：当前月份的英文表示
                    ---SELECT to_char(SYSDATE+5,'mon','nls_date_language=american') FROM dual;         ---may
           add_months(day,n)：返回day日期在n个月后（n为正数）或前（n为负数）的日期
                  ---select add_months(sysdate,1) from dual;         ---向后1个月的现在这个时间---结果：2010-06-14 19:13:29
                      ---select add_months(sysdate,-1) from dual;        ---向前1个月的现在这个时间
           last_day(day)：返回day日期所指定月份中最后一天所对应的日期
                  ---select last_day(sysdate) from dual;             ---当前时间的这个月的最后一天---结果：2010-05-31 11:53:32
                     ---select last_day(sysdate)-1 from dual;        ---当前时间的这个月的倒数第二天---结果：2010-05-30 11:54:01
           next_day(day,char)：返回day指定的日期之后并满足char指定条件的第一个日期，char所指定条件只能为星期几
                  ---select next_day(sysdate,'星期一') from dual;     ---下一个星期一---结果：2010-05-17 11:56:30
        round(day[,fmt])：按照fmt指定格式对日期数据day做舍入处理，默认舍入到日             
               ---select hiredate,round(hiredate,'mm') from emp;                ---结果：1987-05-23  1987-06-01
        trunc(day[,fmt])：按照fmt指定格式对日期day做截断处理，默认截断到日              
               ---select hiredate,trunc(hiredate,'mm') from emp;         ---结果：1987-05-23  1987-05-01
           to_date(char,'格式')：将字符串数据转换为日期型数据
                  ---insert into emp values(6666,'tom','MANAGER',7369,to_date('1995-10-09','yyyy-mm-dd'),5000.00,10.00,10);  ---插入的就是10月
                 设置日期的输入格式---alter session set nls_date_format='dd-mm-yyyy';         ---则表示以后输入的时候，日期的格式输入是dd-mm-yyyy   ---Session altered
                           ---insert into emp values(111,null,null,null,to_date('09-09-1999','dd-mm-yyyy'),null,null,null);
                              insert into emp values(111,null,null,null,to_date('09-09-1999'),null,null,null);       与上面的语句等价
                           ---相反的，原来的日期输入格式将不再适用！！！insert into emp values( 7521 ,'WARD','SALESMAN',7698,to_date('1981-02-22'),1250.00,500.00,30);    ---报错：ORA-01861: 文字与格式字符串不匹配
          
      
     2.数值的函数
      1.转换：数值<--->字符串 to_number(char)---select to_number('56')+1 from dual; ---结果：57
            to_char(number)---select to_char(15)||'123' from dual;  ---字符串连接---结果15123
             eg：19801215--->日期
              select to_date(to_char(19891215),'yyyy-mm-dd') from dual;      ---结果：1989-12-15
            convert(char,dset,sset)：将一个字符串从一种字符集转换为另一种字符集
                ---select convert('strutz','we8hp','f7dec') "conversion" from dual;        ---结果：strutz
            chartorowid：将一个字符串转换为ROWID数据类型
            rowidtochar：将一个ROWID数据类型数据转换为字符串
            hextoraw：将一个十六进制字符串转换为RAW数据类型
            rawtohex：将一个RAW类型的二进制数据转换为十六进制表达的字符串
            to_multi_byte：将一个单字节字符串转换为多字节字符串
            to_single_byte：将一个多字节字符串转换为单字节字符串
      2.ceil(n)：大于等于n的最小整数
          ---select ceil(5.6) from dual;          ---6
          ---select ceil(-5.6) from dual;         --- -5
      3.floor(n)：小于等于n的最大整数
          ---select floor(-5.6) from dual;        --- -6
      4.mod(m,n)：求余数
          ---select mod(3,2) from dual;              ---1
          ---select mod(-3,2) from dual;             --- -1
      5.round(n,m)：对n小数点后的数值做四舍五入处理，保留m位
          ---select round(4.5) from dual;         ---5
           ---select round(4.567,2) from dual;       ---4.57
      6.trunc(n,m)：对n小数点后的值做截断处理，保留m位
          ---select trunc(4.9) from dual;         ---4
          ---select trunc(4.999,2) from dual;        ---4.99
          ---select trunc(444.999,-2) from dual;     ---400
           ---select to_char(trunc(sysdate),'hh24:mi:ss') from dual;     ---00:00:00
      7.abs(n)：求n的绝对值
          ---select abs(-3),-3 from dual;      ---3         -3
      8.exp(n)：返回一个数字e的n次方根
          ---select exp(2),exp(1) from dual;         ---7.38905609 2.71828182
      9.sqrt(n)：求n的平方根
          ---select sqrt(4),4 from dual;      ---4          2
      10.ln(n)：求一个数字的对数值
          ---select ln(1),ln(2) from dual;     ---0          0.69314718
      11.log(n1,n2)：返回一个以n1为底，n2的对数
          ---select log(2,1),log(2,4) from dual;     ---0          2
      12.power(n1,n2)：返回n1的n2次方
          ---select power(2,10),power(3,3) from dual;---1024       27
     3.字符函数
      1.连接---||
                ---select 'Hello' || 'world' from dual;    ---Helloworld
                  ---select to_char(sysdate,'yyyy')||'年'||to_char(sysdate,'mm')||'月'||to_char(sysdate,'dd')||'日' from dual;---结果：2010年05月14日---不好
         ---concat(char1,char2)           ---！！！只能是两个char的连接
            ---select concat(to_char(sysdate,'yyyy'),'年') from dual;     ---2010年
      2.首字母大写---initcap(char)
         ---select ename,initcap(ename) from emp;       ---SMITH      Smith
      3.全部转为小写---lower(char)
         ---select ename,lower(ename) from emp;     ---SMITH      smith
      4.全部转为大写---upper(char)
         ---select ename,upper(ename) from emp;      ---SMITH      SMITH
      5.左/右填充---lpad/rpad(char1,length,char2)---将char1的左/右变成length字节长度，不足的用char2填充
         ---select lpad(ename,10,'*') from emp;   ---多用于$钱数
         ---select rpad('aaa',10,'*') from dual;   ---aaa*******
      6.左/右/全部除去某字符---ltrim/rtrim/trim(char1,char2)     ---除去char1左/右/全部的char2
         ---select ltrim('   nn') from dual;       ---只除去左边的空格
         <===>select ltrim('   nn',' ') from dual;
         ---select rtrim(ename) from emp;       ---只除去右边的空格
         ---select trim(ename) from emp;        ---除去左右两边的空格（不包括中间的空格）
         ---select rtrim(ltrim('$$$software@@','$'),'@') from dual;        ---结果：software
      7.替换---replace(char,str1,str2)：将字符串char中所有str1换成str2     <===>  转换---translate
         ---select ename,replace(ename,'S','s') from emp;       ---SCOTT      sCOTT
         ---select ename,translate(ename,'S','s') from emp;   ---与上面的等价
      8.求ASCII码：ascii(char)：取字符串char的首字符的ascii值<--->chr(number)：取number的ascii字符
         ---select ascii('a'),chr(97) from dual; ---97 a
         ---select ascii('ab'),chr(97) from dual; ---97 a
      9.截取---substr(char,start,length)：返回字符串表达式char中从第start开始的length个字符
         ---select ename,substr(ename,1,3) from emp;     ---从第1位开始截，截3位---SMITH      SMI
         ---select ename,substr(ename,1) from emp;       ---从第1位开始截，截全部---SMITH      SMITH
      10.长度---length(char)：返回字符串表达式char的长度
         ---select ename,length(ename) from emp;       ---SMITH     5
      11.查询字符---instr(char1,char2,start,times)：在char1字符串中搜索char2字符串，start为执行搜索操作的起始位置，times为搜索次数
                              代替了like
         ---select ename,instr(ename,'S') from emp;      ---JAMES     5
         ---select ename,instr(ename,'S',1,4) from emp;  ---JAMES     0
         ---select ename from emp where instr(ename,'S')>0;     ---效率高！！！
            select ename from emp where ename like '%S%';
           
     4.混合函数
      1.求最大值---greatest/小---least(number1,number2...)
         ---select greatest(100,15,39,2139) from dual;           ---2139
         ---select least(100,15,-39,2139) from dual;    --- -39
      2.！！！空值转换---nvl(eExpression1, eExpression2)：从两个表达式返回一个非 null值
             如果 eExpression1 的计算结果为 null 值，则返回 eExpression2。如果 eExpression1 的计算结果不是 null值，则返回 eExpression1。
             eExpression1 和 eExpression2 可以是任意一种数据类型。如果 eExpression1 与 eExpression2 的结果皆为 null 值，则返回 .NULL.。
         eg：求sal,comm的和     
         ---select sal+comm from emp;            数值+空值=空值     ---缺点：当两个数求和时，可能因为一个数是空值，不显示和
         ---select sal,comm,sal+comm,sal+nvl(comm,0) from emp       是否有空，有空就当零用
                         ---结果：  1250.00    500.00    1750    1750
                                  1800.00                       1800
      3.结构分支---decode(e,s1,t1[,s2,t2]...[,def])：若e等于s1，函数返回t1...，否则返回def。表达式e允许是任何数据类型，但要求被比较的各个s具有相同的数据类型。def被默认时，表示默认值是null
           ---相当于if--else
         ---select sal,decode(sal,850,'低工资',3000,'正常工资',5000,'高工资','不判断') from emp;           ---1800.00    不判断
      4.判断正负函数---sign(n)：n的值为正数、0或负数时分别返回1、0、-1
         ---select sign(-5) from dual;     --- -1
         ---select sign(0) from dual;     ---0
         ---select sign(5) from dual;     ---1
         ---select sal,decode(sign(sal-850),-1,'低工资',decode(sign(sal-3000),-1,'正常工资',1,'高工资','不判断')) from emp;  ---1800.00    不判断
         
    
    ---分组函数：汇总
      1.group by / sum / avg / count
       count
         ---select count(1) from emp;        ---第一列有多少行---16
         ---select count(8) from emp;        ---第八列有多少行---16
         ---select count(comm) from emp;      ---！！！当count中是字段名时，空不参与记录---6
        eg：不同工作的个数---select count(distinct upper(job)) from emp;
            ---distinct---消除重复行
       group by-having：对分组后的数据进行汇总操作
         ---注意：select和from之间只能出现两种数据：聚合函数
                     分组字段
            若分组字段是两个或多个，！！！一定给要规定好分组顺序
            ！！！掌握分组层次！
        在没有分组的函数的情况下，相当于去重     select distinct job from emp;
                 <===>select job from emp group by job;
        eg： 按工作分组，统计每一种工作的工资总和
          ---select job,sum(sal) from emp group by job;
        eg：显示出平均工资>1300的---对分组结果进行操作！！！用having
          ---select job,avg(sal) from emp group by job having avg(sal)>1300;
        eg：找出每个部门工资最高和工资最低的
          ---select max(sal),min(sal) from emp group by deptno;
        eg：找出每个部门中每种工作的工资最高和工资最低的 ---分组字段是两个或多个，！！！一定给要规定好分组顺序
          ---select deptno,job,max(sal),min(sal) from emp group by deptno,job;