函数三要素
(函数既可以作为查询结果,也可以作为查询条件)
- 函数的名称
- 函数的参数
- 函数的返回值
单行函数(标量函数)
- 日期函数
// sysdate:获取当前系统时间
select sysdate from emp;
// 虚拟表
select * from dual;
- 日期的算术运算符和关系运算符
//计算10天前的时间
select sysdate-10 from dual;
//计算10天后的时间
select sysdate+10 from dual;
//计算你到今天活了多少年
//round:去掉精度
select round(sysdate_to_date('2016-11-03','yyyy-MM-dd')) from dual;
- add_months(date,number)
//add_months(date,number):返回指定的date日期加上指定的 number月份返回date类型
//查询一个月前的日期
select add_months(sysdate,-1) from dual;
//查询一个月后的日期
select add_months(sysdate,1) from dual;
- months_between(date1,date2)
//months_between(date1,date2):返回date1与date2的时间差,返回时间的月份
//现在离元旦还有几个月
select round(months_beteen(to_date('2017-01-01','yyyy-MM-dd'),sysdate)) from dual;
//计算每个员工的工龄
select months_between(sysdate,hiredate)/12 from emp;
- last_day(date)
last_day(date):返回当前时间date的所在月份的最后一天,返回date
//返回指定日期当月的最后一天的日期
select last_day(sysdate) from dual;
- Extract(fmt from date)
返回日期中的指定部分(fmt:年/year,月/month,日/day)
//返回年
select extract (year from sysdate) from dual;
//返回月
select extract (month from sysdate) from dual;
//返回日
select extract (day from sysdate) from dual;
字符函数
- legnth(str)
返回字符串的长度
//查询员工姓名长度超过5个字符的员工的信息
select emp.ename,length(emp.ename) from emp where length(emp.ename) > 5;
- substr(基础字符串,开始位置,截取长度)
SQL和Oracle的下标都是从1开始
select substr('jack@gmail.com',instr('jack@gmail.com','.')+1) from dual;
- replace(str,old,new)
返回替换指定的字符串
select replace('jack@gmail.com','com','cn') from dual;
- contact
相当于连接符 ||
select contact('jack','tom') as 'person' from dual;
- instr(str1,str2,[index])
返回指定字符串在指定的字符串中的下标
select instr('jack@gmail.com','.',11) from dual;
- lower(str)
将字符串全部转为小写
select lower('LOVE') from dual;
- upper(str)
将字符串全部转为大写
select upper('love') from dual;
数学函数
- mod(num1,num2)
返回num1/num2的余数,即求模
select mod(10,3) from dual;
- ceil(num)
返回大于等于num的最小整数
select ceil(-19.3) from dual;
- floor(num)
返回小于等于num的最大整数
select floor(-19.3) from dual;
- round(num,保留小数点的个数)
四舍五入
select round(-19.335,2) form dual;
转换函数
将值从一种数据类型转换为另一种数据类型
- to_char(date,[fmt]) 日期转换为字符
指定格式:fmt:yyyy-mm-dd hh:mm:ss
//返回当天的星期几
select to_char(sysdate,'day') from dual;
- to_date(char,fmt) 字符 >> 日期
将字符按指定格式抓换当前日期
select to_date('2016-11-03','yyyy-MM-dd hh') from dual;
to_char(number,fmt) 数字 >> 字符
用于货币的显示:$999.99 美元 c9.999 人民币 CNY其他函数
nvl(exp1,exp2) 空值替换函数
如果表达式exp1为空,则使用表达式exp2替换
//查询所有员工的奖金,如果为null就显示'没有'
select nvl(to_char(comm,'没有')) from emp;
//查询所有员工的年薪和奖金
select sal,comm,sal*12+nvl(comm,0)*12 from emp;