Oracle中常用的函数

--Oracle中常用的数值函数
select abs(-123) as "绝对值" from dual;
select ceil(5.5) from dual;--不小于当前数值的最小整数
select floor(5.5) from dual;--不大于当前数值的最大整数
select round(5.5555,2) from dual;--5.56
select round(55555.5555,2) from dual;--55555.56
select round(5.4) from dual;--5

select trunc(55555.555) from dual;--55555
select trunc(55555.555,2) from dual;--55555.55

select trunc(55555.555,-2) from dual;--55500

--Oracle中常用的数值函数和符号
select 'XXX' || 'ZZZZZ' from dual;--XXXZZZZZ  连接字符串

select lower('ABDdddd') from dual;--全转换小写
select upper('ABDdddd') from dual;--全转换大写

select length('ABDdddd') from dual; --得到字符个数
select trim('   AAA    BBB    ') from dual;--去两端空格
select ltrim('   AAA    BBB    ') from dual;--去左端空格
select rtrim('   AAA    BBB    ') from dual;--去右端空格

select substr('AAAABBBB',2,5) from dual; --AAABB
select replace('AAAABBBB','A','C') from dual;   --CCCCBBBB
select replace('AAAABBBB','AB','CCCC') from dual;        --AAACCCCBBB


--聚合函数
select * from scott.emp;

select max(sal) from scott.emp;--最大值
select min(sal) from scott.emp;--最小值
select sum(sal) from scott.emp;--总和
select avg(sal) from scott.emp;--平均值
select count(1) from scott.emp;--总个数

select deptno,max(sal),min(sal),avg(sal) from scott.emp group by deptno;

--转换函数

--sysdate 当前时间
select to_char(sysdate,'yyyy-MM-dd HH:mi:ss day') from dual;--2015-09-13 09:19:19 星期日
select to_char(sysdate,'day') from dual;--得到星期几
select to_char(sysdate,'day d') from dual;--星期几 几
select to_char(sysdate,'yyyy') from dual;--年
select to_char(sysdate,'MM') from dual;--月份
select * from scott.emp where to_char(hiredate,'yyyy') = '1987'; --查询员工表的数据


select to_number('1112.11','9999D99') from dual;--1112.11

select to_date('02-01-03','yy-dd-MM') from dual;--2002/3/1
select to_date('02-01-12','yy-dd-MM') from dual;--2002/12/1

select to_date('2015-09-07 13:55:20','yyyy-MM-dd HH24:mi:ss') from dual;--2015/9/7 13:55:20
select * from scott.emp where round(months_between(sysdate,hiredate)) >= 360;

select round(sysdate - hiredate)/365 from scott.emp;

select nvl(null,2) from dual;
select nvl(comm,0) from scott.emp;
select sal + nvl(comm,0) from scott.emp;--如果第一个参数为空返回第二个参数，否则返回第一个参数本身
select comm,nvl2(comm,'收取了佣金','没有收取佣金') from scott.emp;--如果第一个参数不为空返回第二参数，否则返回第三个参数



select sysdate from dual;
--结果集的运算
select * from scott.emp;
select * from scott.emp where sal > 2000;
--不管是否有重复
select * from scott.emp
union all
select * from scott.emp where sal > 2000;

--去除了两个结果集中相同的数据
select * from scott.emp
union
select * from scott.emp where sal > 2000;

--求两个结果集中的交集
select * from scott.emp
intersect
select * from scott.emp where sal > 2000;

--第二个结果相对第一个结果集的补集
select * from scott.emp
minus
select * from scott.emp where sal > 2000;


--去除重复
select distinct job from scott.emp;

--多表连接查询
--外连接（左外连接/右外连接）
select * from scott.emp;
select * from scott.dept;
select * from scott.emp e left join scott.dept d on e.deptno = d.deptno;--以左边的表为标准依次匹配右边表的数据
select * from scott.emp e right join scott.dept d on e.deptno = d.deptno;--以右边的表为标准依次匹配左边表的数据
--内连接
select * from scott.emp e inner join scott.dept d on e.deptno = d.deptno;--两张表交叉匹配都有的数据
select * from scott.emp e,scott.dept d where e.deptno = d.deptno;--两张表交叉匹配都有的数据
--自连接
select * from scott.emp;
select e.ename,nvl(mgr.ename,'董事长') from scott.emp e left join scott.emp mgr on e.mgr = mgr.empno;