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