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%'