oracle函数

1.NEXT_DAY

指定某个日期之后的某天

select NEXT_DAY('10-2月-2019','星期四')
from dual;

2.LAST_DAY

返回某月的最后一天

select LAST-DAY('10-2月-2019')
from dual;

3.round

四舍五入截取日期中的年月日

select round(TO_DATE('10-2月-2019'),'MONTH')
FROM dual;

4.TROUNC

截取日期中的年月日

select TROUNC(TO_DATE('10-8月-2019'),'year')
from dual;

5.to_char

将日期函数转换成字符串类型的数据

select ename,TO_CHAR(hiredate,'fmDD Month YYYY') HIREDATE
from emp;

6.TO_CHAR(用于数值型)

select to_char(sal,'fm$3,333.00')
from emp;

7.TO_NUMBER

将字符串类型转换为数值类型
格式：TO_NUMEBR(char[,‘fmt’])
fm消除前导零或者空格，t转换后的格式

select to_number('$1234.56','9,999.00') "result"
from dual;

8.TO_DATE

将字符串类型数据转换成日期型数据
TO_DATE(char[,‘fmt’])

select ename,hiredate
from emp
where hiredate=to_date('2月 22,1981','MONTH DD,YYYY')

9.NVL

将空值转换为有意义的实际值

10.DECODE 函数

根据相应条件进行计算

DECODE(col/expression,searchl,result1
[,search2,result2,.....,]
[,default]
)

eg:按照员工工种给涨工资

if job='ANAYLYS' THEN SAL=SAL*10%
if job='CLERK' THEN SAL=SAL*15%
if job='MANAGER' THEN SAL=SAL*20%
ELES SAL=SAL
SELECT ename,sal,decode(job,'ANALYST',SAL*1.1,
'CLERK',SAL*1.15,
'MANAGER',SAL*1.2,
SAL
)
FROM EMP;

11，笛卡尔积

select * from emp,dept;

12.使用组函数的非法查询

select deptno,AVG(sal)
from emp
where avg(sal)>2000
group by deptno;

替换为

select deptno,avg(sal)
from emp
having avg(sal)>2000
group by deptno;

13.having语句的使用

select job,sum(sal)
from emp
where job not  like'sales%'
having sum(sal)>5000
group by job;

14.子查询的使用

select *
from emp
 where job=(select job from emp where empno=7369)
and sal>(select sal from emp where empno=7876);

15,having与子查询的结合使用

select deptno,min(sal)
from emp
group by deptno
having min(sal)>(select min(sal)
from emp
where deptno=20);