13、oracle--sql 数字/日期/字符串/过滤函数

  常用的数据类型：数字（number|float）、日期（date|timestamp）、字符串(char|varchar2)
1、number（38） 最大位数38
   number（7,2） = 12345.67
   小数点后2位，不能是3位
2、float  二进制存储，不方便使用；但小数点可以任意浮动，是number的子类型

3、date 记录年、月、日、时、分、秒

4、timestamp 记录年、月、日、时、分、秒和纳秒
   select systimestamp from dual;
   alter session set nls_timestamp_tz_format='yyyy-mm-dd hh24:mi:ss.ff tzh:tzm';

5、char 定长 最小为1，最大为2000字节

6、varchar2  变长 最小为1，最大为4000字节
   varchar 目前和VARCHAR2是一样的，但Oracle建议不要使用这个类型，因为将来另有他用。
   英文总是单字节字符， 中、日、韩、俄、阿拉伯等文字，会占据至少两个字节，并在不同的字符集下，占据的字节数不完全一样;可以用lengthb来测试字符所占据的字节数
    select lengthb('好的')from dual  4

函数：
    CAST函数 --  将某常量或变量的类型强制设为指定类型
    select cast('1234' as number(9)) from dual;
    select cast(12 as number(9)) from dual;
    select cast(1.2345e4 as varchar(9)) from dual;

    表示date的方法
    select to_date('2013-02-09 23:59:59','yyyy-mm-dd hh24:mi:ss') from dual;
    date'2013-02-09'
    to_date中的分隔符可以更换， date中的分隔符必须是"-"号；date只可以表示日期，不可以表示时间
    判断一个时间变量是否在某个范围？
    select 'TRUE' from dual where to_date('2013-04-12 13:48:22','YYYY-MM-DD HH24:MI:SS') between date'2013-04-05' and date'2013-04-06'-1/86400;
    1天=24小时=24*60*60=86400秒
    select date'2013-04-06'-1/86400 from dual 
    2013/4/5 23:59:59
    select date'2013-04-06'-1 from dual
    2013/4/5

    表示timestamp的方法
    to_timestamp('2013-02-09 23:59:59.000','yyyy-mm-dd hh24:mi:ss.ff')
    timestamp '2013-04-05 13:48:00.123456789'
    to_timestamp中的分隔符可以更换， timestamp中的日期分隔符必须是"-"，时间必须是:，秒后面必须跟上.timestamp可以精确表示到毫秒、微秒甚至纳秒级别

    如何显示"2013年4月 17日"这样的日期
    select to_char(sysdate,'YYYY"年"MM"月 "DD"日 "') from dual;
    select to_char(sysdate,'YYYY"年"MM"月 "DD"日 " HH24"点"MI"分"')from dual;
    select  to_char(date'2013-04-06','YYYY"年"MM"月 "DD"日 "') from dual
    select to_char(to_date('2013-02-09 23:59:59', 'yyyy-mm-dd hh24:mi:ss') ,' YYYY"年"MM"月 "DD"日 " HH24"点"MI"分" ') from dual

    单行函数
    此类函数可出现在select后的字段列表、 WHERE、 START WITH、 CONNECT BY、
GROUP BY和HAVING子句中

1、数字类
   绝对值：  abs(-300）
   正负号：  sign(-300）  返回值 1,0,1
   舍入类：  向上取整  ceil(1.06)=2
             向下取整  floor(1.9)=1
             四舍五入  round(1.06)=1
             截断      trunc(15.16, 1)=15.1, trunc(15.16, -1)=10
   求余类：  mod(7,2）=1
   幂指类：  幂函数    power(2,10)=1024
             指数函数  exp(1)＝ 2.718281828459045.……
             对数函数  ln(2.718281828459045)=1
                       log(2,=3, log(2,1024)=10
   三角类：  三角函数： cos、 sin、 tan……
             反三角函数： acos、 asin、 atan……
             双曲函数： sinh、 cosh、 tanh……

2、日期类
   日期加法   直接相加(天)    date'2013-01-01'+1/12=to_date('2013-01-01 02', 'yyyy-mm-dd hh24')
              add_months函数  add_months(date'2010-02-27',24)＝ date'2012-02-27'
   日期相减  直接相减(天)    select date'2013-01-01'-1 from dual
                              date'2013-01-31'-to_date('2013-01-01 12:00:00','yyyy-mm-dd hh24:mi:ss')=29.5
              months_between函数    months_between(date'2013-01-31',to_date('2010-11-01 12','yyyy-mm-dd hh24'))= 26.9516129    

   获取日期中的某个值  to_char函数 
                                  获取年  to_char(sysdate, 'yyyy')=2015
                                  获取分  to_char(sysdate, 'mi')
                       extract函数
                                  获取月   select extract(month from date'2015-03-31') from dual
                                            extract(second from timestamp'2013-08-04 09:00:05')=5    
                                          （year, month, day, hour, minute, second）
   日期舍入   trunc函数
                      trunc(to_date('12:35:35','hh24:mi:ss'),'MI')=to_date('12:35:00','hh24:mi:ss')
                      trunc(to_date('12:35:35','hh24:mi:ss'),'HH')=to_date('12:00','hh24:mi')
                   round函数
                      round(date'2013-04-17','MM')=date'2013-05-01'
                      round(to_date('12:35:25','hh24:mi:ss'),'MI')=to_date('12:35:00','hh24:mi:ss') 
                     round(to_date('12:35:35','hh24:mi:ss'),'MI')=to_date('12:36:00','hh24:mi:ss')

3、字符串类
   大小写转换   首字母大写  select  initcap('skx') from dual
                转大写      lower
                转小写      upper
   字符与ASCII码转换        select  ASCII('s') from dual
                            select  CHR(115) from dual
   字符串连接   concat函数，同||操作符
                            select 'last'||'winner'||'' from dual;
                            select concat(concat('last','winner'),'') from dual;
   字符串填补   LPAD、 RPAD函数
                            lpad('1234',9,'数')=' 数数1234'
                            rpad('1234',9,'数')='1234数数 '
   字符串修剪  
             LTRIM和RTRIM函数: 默认从左或右去掉空格,从左或右去掉第一个参数中的字符，直到该字符不等于第二个参数中的任意字符
                           ltrim('aabdwecab', 'abcd')='wecab'
             TRIM函数：默认去掉两端的空格，从左或/和右边去掉第二个参数指定的字符，仅一个 
                           trim('a' from 'aabdwecaba')=bdwecab
   取子串    substr('lastwinner@dataguru',5,6) = winner （从第五位开始，截取6位）
             substr('lastwinner@dataguru',-5,6) =aguru
  查找替换   INSTR函数
                           instr('lastwinner@dataguru','win')=5（查找）
             replace函数   replace('Today is a good day. ',' ','##')=' Today##is##a##good##day.#### '
             translate函数   select translate('acdd','cd','ef') from dual; -->aeff
             利用TRANSLATE实现关键字的过滤
                             translate('(+0086-)010-68345678','0(+)-','0')=008601068345678
                             ('1last34winn00er97是剑2破53冰45山4的5作5者之4453一45','l0123456789','l') =lastwinner是剑破冰山的作者之一


4、操作函数
   返回最大值：greatest可跟若干参数（ 类型可为数字、字符、日期等），返回其中的最大值，若参数中有一个NULL，则返回NULL,注意不要和MAX搞混
   返回最小值 least
   select greatest(sysdate, date'2013-12-31',to_date('13:12','mi:hh')+interval '1' year) from dual;
   三目运算 nvl(a,b)--->if (a==null) return b; else return a;
            nvl2(a,b,c)--->if (a==null) return c; else return b;
            coalesce 返回参数中第一个非NULL的值。若参数都是NULL，则返回NULL
            coalesce(expression_1, expression_2, ...,expression_n)
   if-else：decode(x, 1, 'one', 2, 'two', null, 'NULL', 3,'three', 4,'four','数不过来了')
            select decode(月份,'一月'，1) from t
5、聚合函数
     聚合前，用where过滤
     聚合后，用having过滤