数字函数:
//abs():返回指定数值的绝对值
select abs(-1) from dual;
//ceil():向上取整
select ceil(11.5) from dual;
//floor():向下取整
select floor(11.5) from dual;
//power(a,b):求a的b次方
select power(2,3) from dual;
//round(a,b):按四舍五入,保留b位小数
select round(15.5,1) from dual;
//mod(a,b):取余(a与b相除所得余数)
select mod(5,2) from dual;
//trunc(a,b):截取字符串
select trunc(11.5,3) from dual;
时间函数:
//add_months(date,count):表示在指定日期,增加count个月
select hiredate,add_months(hiredate,5) from emp;
//last_day(date):返回date所在月的最后一天
select last_day(to_date('2020-04-09','YYYY-MM-DD')) from dual;
//months_between(date1,date2):返回date1和date2间隔的月数
select months_between(sysdate,hiredate) from emp;
//next_day(date,'day'):返回指定日期后的第一个星期几的日期,day为星期
select next_day(to_date('2020-04-09','YYYY-MM-DD'),'星期一') from dual;
//sysdate:获取系统当前日期
select sysdate from dual;
//current_timestamp:获取当前时间和日期值
select current_timestamp from dual;
//round:对日期进行四舍五入
select round(to_date('2020-04-09','YYYY-MM-DD'),'DD') from dual;
//trunc:对日期进行截取
select trunc(to_date('2020-04-09','YYYY-MM-DD'),'DD') from dual;
类型转换函数:
//to_char(date,'format'):按指定格式format将数字或日期类型转换为字符串
select to_char(hiredate,'YYYY') from emp; //结果左对齐
select extract(year from hiredate) from emp; //结果右对齐
//to_number(char):将包含了数字的字符串转换成数字数据
select to_number('$123','$123') from dual; //字符串转数字
通用函数:
//nvl(a,b):若a为空,则返回b
//nvl2(a,b,c):若a不为空,返回b,否则返回c
select nvl2(comm,comm,'0') from emp;
//nullif(a,b):若a=b,则返回null,否则返回a
select nullif('1','2') from dual;
//coalesce(expr1,expr2...):返回清单中第一个非空值
select coalesce('','','123','') from dual;
//decode(colume|expression,search1,result1,search2,result2,..,default)
select decode(sal,'500','1','1100','2','3') from emp;
select case when sal = 500
then '1'
when sal = 1100
then '2'
else '3'
end
from emp;
分组函数:
//avg()、max()、min()、count()、group by()、having
//连接查询
select * from emp e1 left join dept d1 on e1.deptno = d1.deptno(+);
//+:表示补充缺省值
<=> select * from emp e1 dept d1 where e1.deptno = d1.deptno