Oracle base SQL语句
----------////////////////// 通用函数 ////////////////////////---------------nvl函数 如果 exp1 为空，则返回 exp2Select nvl(comm,0) From emp--nullif函数 如果 exp1 和 exp2 相等，则返回 null ，否则返回 exp1Select nullif(1,2) From dual--nvl2函数 如果 exp1 不为空，则返回 exp2 ，否则返回 exp3Select empno,ename,sal,comm,nvl2(comm,sal+comm,sal) total From emp--coalesce函数 遇到非空即返回Select empno,ename,sal,comm,coalesce(sal+comm,sal,0) total From emp--case函数Select empno,ename,sal,Case deptno When 10 Then '财务部' When 20 Then '研发部' When 30 Then '销售部'Else '未知部门'End 部门From emp;--decode函数Select empno,ename,sal,decode(deptno,10,'财务部',20,'研发部',30,'销售部','未知部门') 部门From emp;--多行子查询，子查询返回不是一个结果的Select * From emp Where sal>Any(Select Avg(sal) From emp Group By deptno);Select * From emp Where sal>All(Select Avg(sal) From emp Group By deptno);Select * From emp Where job In (Select job From emp Where ename='MARTIN' Or ename='SMTTH')--分页查询第一种写法select * from ( select rownum no,e.* from ( select * from emp order by sal Desc ) e where rownum<=5) where no>=3;--分页查询第二种写法select * from ( select rownum no,e.* from ( select * from emp order by sal Desc ) e) where no>=3 and no<=5;--随机返回五行数据Select * From ( Select empno,ename,job From emp Order By dbms_random.value() ) Where Rownum <= 5;Select * From emp Order By sal
--首字母转为大写Select initcap(ename) From emp--连接字符串Select concat('hello ','word') From dual--截取字符串Select substr(ename,-3,3) From emp--四舍五入Select round(789.567,-2) From dual--直接取几位，不四舍五入Select trunc(789.576,-2) From dual--雇佣了几周Select round((Sysdate-hiredate)/7) From emp--年 月 日Select empno,ename,to_char(hiredate,'yyyy') Year,to_char(hiredate,'mm') months,to_char(hiredate,'dd') Day From emp--格式化为制定时间格式Select empno,ename,to_char(hiredate,'yyyy-mm-dd') From emp--去零Select empno,ename,to_char(hiredate,'fmyyyy-mm-dd') From emp--美元Select empno,ename,to_char(sal,'$99,999') From emp--本地Select empno,ename,to_char(sal,'L99,999') From emp--年薪资 （工资+奖金）×12Select empno,ename,nvl(comm,0),(sal+nvl(comm,0))*12 income From emp--奖金不为空Select Distinct job From emp Where comm Is Not Null--每月倒数第三天雇佣的员工信息Select * From emp Where Last_day(hiredate)-2=hiredate--最高12年前雇佣的，今天和雇佣日期之间总月份/12Select * From emp Where months_between(Sysdate,hiredate)/12 > 12--雇佣了多少天Select ename,round(Sysdate-hiredate) From empSelect * From emp--工作年限，月限Select ename,trunc(months_between(Sysdate,hiredate)/12) Year, trunc(Mod(months_between(Sysdate,hiredate),12)) monthsFrom emp