一、数据类型转换函数 (说明本文中用到的数据是oracle自带的scott用户下的表)
数据类型转换函数分为:数据类型的隐式转换 和 数据类型的显示转换
1.数据类型的隐式转换
字符串可以转化为数字和日期,数字要合法,日期要格式匹配。
select ename,empno from emp where empno = '7900';
数字和日期在赋值的时候可以转换为字符串,但在表达式的时候不可以转换。
select ename,empno from emp where ename = '123';
select ename,empno from emp where ename = 123; //错误
2.数据类型的显示转换
to_char:转化为字符串
select ename,hiredate, to_char(hiredate,'yyyy/mm/dd') from emp;
//日期转化为字符串,得说明字符串的格式。
select ename,to_char(hiredate,'fmyyyy/mm/dd') from emp;
//fm是清除前置的零和空格
select to_char(hiredate,'fmyyyy"年"mm"月"') from emp;
//格式内加入字符串需要用双引
select ename,to_char(sal,'9999.000') salary from emp;
select ename,to_char(sal,'l99,999.000') salary from emp;
select ename,to_char(sal,'$99,999.000') salary from emp;
select ename,to_char(sal,'9g999d99')salary from emp;
9代表有多少宽度,如果不足会显示成#######,0代表强制显示0,但不会改变结果,g是千分符,d是小数点,l 指代¥
十进制转化为十六进制:在数据库中16进制的表达式按照字符串来描述的,所以在转换时需要用to_char函数
select to_char(11,'xxxx') from dual;
//其中xxx的位数要足够,不然不会正确显示,需要多写,足够多就ok.
to_number:转化为数字
十六进制的数转换为十进制的数要用to_number函数
select to_number('a','xx') from dual;
to_date:转化为日期
select to_date('1992-10-20','yy/mm/dd hh24:mi:ss') from dual;
select to_date('19921021','yy/mm/dd') from dual;
select to_date( 'January 15, 1989, 11:00 A.M.', 'Month dd, YYYY, HH:MI A.M.','NLS_DATE_LANGUAGE = American') from dual;
//yy是两位来表示年,世纪永远和说话者的当前世纪相同
二、综合数据类型函数
1.操作数据位null的函数
nvl(expr1,expr2):如果expr1为空,就返回expr1,如果expr2为空返回expr2,两个表达式的数据类型一定要相同
select ename,comm,nvl(comm,-1) from emp;
nvl2(expr1,expr2,expr3):如果expr为非空,就返回expr2,如果expr1为空返回expr3
select ename,comm,nvl2(comm,comm,-1) from emp;
nullif(expr1,expr2):如果expr1和expr2相同就返回空,否则返回expr1
select ename,nullif(ename,'KING') from emp;
coalesce(expr1,expr2,...,exprm):返回括号内第一个非空的值
select ename,coalesce(comm,sal,100)"奖金" from emp;
2.分支函数
case语句
select ename,job,sal, case job when 'CLERK' then 1.10*sal when 'SALESMAN' then 1.15*sal else sal end "revise_salary" from emp;
//case...end 查询的是一列数据,end后面是给该列起的别名
decode:和case语句一样都是分支语句,但decode函数是oracle自己定义的,其它数据库可能不支持
语法: decode(col | expression, search1,result1[,search2,result2,...,][,default])
select ename, job, sal, decode( job, 'CLERK',1.10*sal,'SALESMAN','1.15*sal',sal) "revised_salary" from emp;
译: 判别job,不同工作的人赋予不同的工资,除了clerk,salesman以外,其它的人工资不变,将函数的值起一个别名 revised_salary
巧用decode函数,改变排版方式
select sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",
sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981",
sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",
count(ename)"总人数" from emp;
说明:不管case语句还是decode函数,它们都是单行函数,每一行都有一个返回值,从oracle角度来讲,decode更好,因为各个版本的数据库都支持,横向来说,case语句更好,因为它是国际,不同数据库间都认可。
3.分组统计函数
avg(平均), sum(求和),max(最大),min(最小),count(计数)
select avg(sal),sum(sal),max(sal),min(sal) from emp;
select count(*), count(comm) from emp;
输出: 14 4
所有组函数,除了count(*)以外,都忽略null值,count是计数,查看有多少行,count(列) 是查看该列有多少非空的
select avg(comm),avg(nvl(comm,0)) from emp;
输出: 550 157.142857
avg求得是非空值的平均
select count(distinct deptno) from emp;
计算有多少不同的部门数