时间函数
18 to_number() 将字符串转换成数字
19 to_date(str) 将字符串转换成日期yyyyMMdd,hh24,miss
20 to_char() 字符串转换函数
21 last_day(日期) 取当前日期月的最后一天
22 add_months(日期,月) 给一个日期加上若干个月
23 months_between(date1,date2) 取两个日期相差的月数
24 nvl2(列名,值1,值2)空值转换函数,当第一个参数的值是空时,返回
结果是第3个参数的值,当第一个参数不为空时,返回结果是第2个参数的值
空值赋值nvl
[语法]NVL (expr1, expr2)
【功能]若expr1为NULL,返回expr2;expr1不为NULL,返回expr1注意两者的类型要一致
[语法]NVL2 (expr1, expr2, expr3)
[功能]expr1不为NULL,返回expr2;expr2为NULL,返回expr3。 expr2和expr3类型不同的话,expr3会转换为expr2的类型
Decode 等值翻译
decode 函数在sql中作用相当于case when,意思是根据某一字段的值作为条件,根据这个条件进行输出。decode函数相当于一条件语句(IF),它将输入数值与函数中的参数列表相比较,根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然,如果未能与任何一个实参序偶匹配成功,则函数也有默认的返回值。 decode函数用法:
格式:decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
Case when
用法:一旦满足了某一个when,则这一条数据就会退出case when,而不再考虑其他case; case具有两种格式。简单case函数和case搜索函数。简单Case函数
简单Case表达式的作用是:使用表达式确定返回值:
select deptno,case deptno
when 10 then'第一部门' when 20 then'第二部门' when 30 then'第三部门 else'第四部门' end
from emp;
Case搜索函数
判断表达式的真假,如果为真,返回结果;如果为假,返回else值;如果未定义else值,则返回空值(使用条件确定返回值);
select deptno,
case when deptno=10 then'第一部门'
when deptno=20 then'第二部门' when deptno=30 then'第三部门' else'第四部门' end
from emp;
这两种方式,可以实现相同的功能。简单Case函数的写法相对比较简洁,但是和Case搜索函数相比,功能方面会有些限制,比如写判断式。
还有一个需要注意的问题,Case函数只返回第一个符合条件的值,剩下的Case部分将会被自动忽略。
练习4
select * from emp
select to_char(sysdate,'dd') from emp
select to_char(hiredate,'dd') from emp
12.求1981年2月20号入职的员工
select ename,hiredate from emp where hiredate=to_date('19810220','yyyymmdd')
select ename,hiredate from emp where to_char(hiredate,'yyyymmdd')=19810220
13.求1981年入职的员工
select ename,hiredate from emp where hiredate>=to_date('19810101','yyyymmdd') and hiredate<=to_date('19811231','yyyymmdd')
select ename,hiredate from emp where to_char(hiredate,'yyyy')=1981
14.求2月份入职的员工
select ename,hiredate from emp where hiredate>=to_date('19810201','yyyymmdd') and hiredate<to_date('19810301','yyyymmdd')
select ename,hiredate from emp where to_char(hiredate,'mm')=02
15.取当前月倒数第5天入职的员工
select * from emp where hiredate>=last_day(sysdate)-5 and hiredate<=last_day(sysdate)-4
select * from emp where to_char(hiredate,'dd')=to_char((last_day(sysdate)-4),'dd')
16.取每个月倒数第5天入职的员工
select ename,hiredate from emp where hiredate=(last_day(hiredate)-4)
17.查询工资是偶数的员工
select ename,sal from emp where mod(sal,2)=0
18.求3个月后的今天
select add_months(sysdate,3) from dual
19.求每个员工的月收入
select ename,nvl2(sal,sal,0) from emp
20.如果comm为空就显示“No Commission”,如果不为空就显示comm的值
select ename,nvl2(comm,to_char(comm),'No Commission')from emp
21.求员工的一年的总收入(工资和佣金)
select ename,12*(nvl(sal,0)+nvl(comm,0)) from emp
select next_day(sysdate+7,4) from dual
练习5
1.把10号部门翻译成“十号部门”
,20号部门翻译成“二十号部门”
,30号部门翻译成“三十
号部门”
select deptno,
case when deptno=10 then '十号部门'
when deptno=20 then '二十号部门'
when deptno=30 then '三十号部门'
else '其他'
end
from emp
2.给所有的10号部门员工加薪10% 20号部门的员工加薪20% 30号员工加薪30% ,其他部门
加薪5%
select ename,sal,
case when deptno=10 then sal+0.1*sal
when deptno=20 then sal+0.2*sal
when deptno=30 then sal+0.3*sal
else null
end
from emp
3.统计工资级别相应的数量(1600以下 C级,1600-3000 B级,3000以上 A级)
select
case when sal<1600 then 'C'
when 1600<=sal and sal<=3000 then 'B'
when sal>3000 then 'A'
else null
end,count(1)
from emp group by case when sal<1600 then 'C'
when 1600<=sal and sal<=3000 then 'B'
when sal>3000 then 'A'
else null
end
把工作为MANAGER的翻译成"部门经理",工作是CLERK的翻译成'文员',把工作为SALESMAN翻译成'销售',其他工作写成其他,再分别求出各类工作的数量
select job,count(1),
case when job='MANAGER' then '部门经理'
when job='CLERK' then '文员'
when job='SALESMAN' then '销售'
else '其他' end
from emp
group by case when job='MANAGER' then '部门经理'
when job='CLERK' then '文员'
when job='SALESMAN' then '销售'
else '其他' end ,job
select count(1) from emp
select job,
case when job='MANAGER' then '部门经理'
when job='CLERK' then '文员'
when job='SALESMAN' then '销售'
else '其他' end
from emp
求出员工一年的总收入,总收入小于1万元的算A级,1万-3万元的B级,3万以上算C级别v
select ename,12*sal ,
case when 12*sal<10000 then 'A'
when 12*sal>=10000 and 12*sal<=30000 then 'B'
when 12*sal>30000 then 'C'
else null end
from emp
~~~