--日期函数
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
