ORACLE学习第七篇（常用处理函数）

一、常用聚合函数

求最大值：select max(sal) from emp ;求最小值：select min(sal) from emp ;求平均值：select avg(sal) from emp ;Sum 求和：select sum(sal) from emp ;Count 统计记录数：select count(ename) from emp ;（Having 过滤分组：select job,count(ename) as num from EMP t group by job having count(ename)>=2;）

二、空值处理

select ename,nvl(sal,0) sal from emp;

三、分页处理
rownum行号

select e.*,rownum from emp e;select a.*,rownum rn from (select * from emp) a where rownum<=2; //获取行号小于等于2的数据select * from (select a.*,rownum rn from (select * from emp) a where rownum<=3) where rn>1;//获取行号为2~3的数据

四、oracle中的运算

select 2+1 from dual;select 2-1 from dual;select 2*1 from dual;select 2/1 from dual;

五、条件判断

Between and 范围查询：select * from emp where sal between 900 and 1500;select * from emp where sal>=900 and sal<=1500;

六、In 集合范围：

select ename,hiredate from emp where ename in (select distinct ename from bonus)

七、Like 模糊查询：

select * from emp where ename like '%M%select * from emp where ename like 'M%'select * from emp where ename like '_M%'