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, 8)=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('aabd[color=cyan]w[/color]ecab', 'abcd')='wecab'
TRIM函数：默认去掉两端的空格，从左或/和右边去掉第二个参数指定的字符，仅一个
trim('a' from 'aabdwecaba')=bdwecab
取子串 substr('lastwinner@dataguru',5,6) = winner （从第五位开始，截取6位）
substr('lastwinner@dataguru',-5,6) =aguru
[color=cyan] 查找替换 [/color] 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过滤