常用的数据类型:数字(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过滤
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过滤