SQL函数

SQL函数可以完成功能如下：
执行数据计算
修改单个数据项
格式化显示的日期和数字
进行数据类型转换
根据行分组操纵输出

字符函数：
–Lower 将字符转换成小写
select lower(first_name)
from employees;

–Upper 将所有字段转换成小写
select upper(first_name) as first_name
from employees;

–Initcap 首字母大写
select initcap(job_id) as first_name
from employees;

–Concat 连接字符串的另一种用法
select concat(first_name||’ ‘,last_name)
from employees;

select concat(concat(first_name,’ ‘),last_name)
from employees;

–Substr 截取字符串
select substr(first_name,1,2)
from employees;

–Replace 字符串替换
select replace(first_name,’a’,’A’)
from employees;

–Length 获取长度
select length(first_name) as name_length
from employees;

–Instr 查找值的位置
select instr(first_name,’e’) as locat
from employees;

–Lpad 从左面进行填充直到字段为指定长度多补充少删除
select lpad(first_name,8,’e’) as locat
from employees;

–Rpad 从右面进行填充直至字段为指定长度多补充少删除
select Rpad(first_name,9,’e’) as locat
from employees;

数字函数
–Round 数字四舍五入 Trunc 数字截断 Mod 取余
–dual表中只有一行一列，且属于sys 但是所有用户否可以看到
select round(7.896544,3)，trunc(7.89456,2)， mod(21,5)
from dual;

日期函数
–获取当前数据库具体时间
select sysdate from dual;

–查询60号部门员工入职了多少时间
select employee_ID,first_name,trunc((sysdate-hire_date)/7)
from employees
where department_id=60;

–查询60号部门员工入职了多少月
select employee_id,trunc(months_between(sysdate,hire_date))
from employees
where department_id=60;
–查询三个月后日期
select add_months(sysdate,3)
from dual;

–查询下周五的日期
select next_day(sysdate,’星期一’)
from dual;

–计算所在月的最后一天
select last_day(sysdate) from dual;

select employee_id,hire_date,
trunc(months_between(sysdate,hire_date)) as workmonth,
add_months(hire_date,3) as threemonth,
next_day(hire_date,’星期一’) as filedate,
last_day(hire_date) as moneydate
from employees
where hire_date between ‘1-1月-07’and ‘30-6月-07’;

转换函数
–对数字进行格式
select employee_id,first_name,
to_char(salary,’$999,999,99’)
from employees;

–to_number把字符串转化成数字
select to_number(‘9527’,’99999’)
from dual;

–to_date函数 加fx为精准匹配，格式要一模一样
select to_date(‘25-12月-17’,’fxDD-Mon-YY’)
from dual;

通用函数
– Nvl 用一个实际的值去替换一个空值
select first_name ,
salary*12+500,
（salary*12+500)*(nvl(commission_pct,0)+1),
commission_pct
from employees;

–Nvl2（first_name，参数1，参数2） 如果第一各参数为空，则返回第二个参数
select first_name ,
salary*12+500,
（salary*12+500)*(nvl(commission_pct,0)+1),
（salary*12+500)*
(nvl(commission_pct,0)+1)+nvl2(commission_pct,salary*0.3,salary*0.4),
nvl2(commission_pct,salary*0.3,salary*0.4),
commission_pct
from employees
where department_id in(80,90);

–Coalesce 如果第一个值为空则返回第二个值，依次类推，只要为空，就返回下一个值
select employee_id , first_name ,
coalesce(to_char(commission_pct),to_char(manager_id),’老板’)
from employees
where department_id in(80,90);