Oracle日期函数

--日期函数
select name, create_date 入职时间,
add_months(create_date,2) 转正时间
from tsm_employee where dept_no='78';--月加


select  add_months(sysdate,2) from dual;--月加

select round(months_between(to_date('2008-09-17','yyyy-mm-dd'),
to_date('2008-08-01','yyyy-mm-dd')),2)
from dual;--求两日期月差

select last_day(create_date) from tsm_employee;--当月最后一天

insert into tsm_employee(emp_no,name,create_date,dept_no)
values(100,'test',last_day(sysdate),'87')

select name,create_date,last_day(create_date) 
from tsm_employee 
where create_date=last_day(create_date)



select round(to_date('2008/06/17','yyyy/mm/dd'),'MONTH') 
from dual;--YEAR舍入到最近的年，MONTH舍入最近的月DAY天

select next_day(sysdate,'星期三') from dual;--返回下个星期几的日期

select trunc(to_date('2008/07/17','yyyy/mm/dd'),'YEAR') 
from dual;--YEAR截断日期到年月日，与round的区别是只舍不入默认返回天

select extract(day from sysdate) from dual;--返回日期的特定部分year,month,day

--数字函数
Select abs(-15) from dual; 
Select ceil(44.0001) from dual; 
Select cos(180) from dual; 
Select cosh(0) from dual; 
Select floor(100.999) from dual; 
Select power(4,2) from dual; 
Select mod(10,3) from dual; 
Select round(100.256,2) from dual; 
Select trunc(100.256,2) from dual; 
Select sqrt(4) from dual; 
Select sign(0) from dual;



--字符函数
select chr(65) from dual;--返回ASCII码字符


select trim(leading '1' from '111234156789111') from dual;--去前面

select trim(trailing '8' from '8912348567888') from dual;--去后面

select trim('9' from '991293459967899') from dual;--去所有

select length('string   ') from dual;--返回字符串长度注意空格也算长度

select RPad('salary 1', 13,'0') from dual; 

select LPad(' ok', 26,'10') from dual;
 
select decode('abc','abd','字符串１','字符串２') from dual;--逐值替换,可替换多个，完全相同才替换
  if 'abc'='abd' then
     返回　字符串１
  else
     返回　字符串２
  end if;
--员工表，如果雇员职位是部门经理返回20000元的薪水,
--如果是项目经理返回１５０００
--如果是软件开发工程师返回10000　　

select translate('abcd','adcf','0123k') from dual;--在jack中用1234翻译abcd来替换其中的字符
j132f4 0b21

--转换函数
select to_char(sysdate,'YYYY-MM-DD') from dual;--日期转换字符串

select to_char('456','c9999') from dual;--添加人民币符号CNY

select to_date('2005-2-3 12:12:12','YYYY-MM-DD hh24:mi:ss') from dual;--将字符转为日期

select sqrt(to_number('100')) from dual;--数字转换函数,取平方根

--其它函数
select name ,nvl(phone,'139') from tsm_employee where phone is null and dept_no='79'
　
select nvl(address,'空') from tsm_employee;

select nvl2('','dd','dd1') from dual;

select nullif('33','133') from dual;--相等返回空，否则返回前一个值

--组合函数
select avg(salary) from tsm_salary_records;--求平均值
select sum(salary) from tsm_salary_records;--求和
select min(salary) from tsm_salary_records;--最小值
select max(salary) from tsm_salary_records;--最大值
select count(*) from tsm_salary_records;--求记录数


select count(distinct working_days) from tsm_salary_records;--统计不重复记录



--分析函数
--根椐工资排序 
select empno,ename, sal,(row_number() over (order by sal desc)) num
 from scott.emp

--根椐部门分组再排序工资  排名是1，2，3
 select deptno,empno,ename,sal,
(row_number() over (partition by deptno order by sal desc))  num from scott.emp;

--根椐部门分组再排序工资，数值相同时将并列,排名将变成 1 1 3
 select deptno,empno,ename,sal,
 (rank() over (partition by deptno order by sal desc))  num from scott.emp

--根椐部门分组再排序工资，数值相同时将并列,排名将变成 1 1 2
  select deptno,empno,ename,sal,
 (dense_rank() over (partition by deptno order by sal desc))  num from scott.emp