ORACLE初学第四篇

一、分组函数

select job,count(ename) as c_e from emp group by job;/*分组函数一般是联合聚合函数count来用的,上面的意思是：统计出emp下面不同工作的人数，显示出不同的工作以及其统计数*/

二、过滤函数

/*在上面的例子中加上过滤条件*/select job,count(ename) as c_e from emp group by job having count(ename)>2;

三、排序函数

/*在上面的例子中在加上排序*/select job,count(ename) as c_e from emp group by job having count(ename)>2 order by c_e desc;//默认是asc进行排序的

四、子查询

select sum(sal) from emp e where sal>(select avg(sal) from emp);select count(sal) from emp e where sal>(select avg(sal) from emp);select job,count(sal) from emp e where sal>(select avg(sal) from emp) group by job;select job,count(sal) as cou_e from emp where sal>(select avg(sal) from emp) group by job having count(ename)>=2 order by cou_e desc;select job,count(sal) as cou_e from emp e where sal>(select avg(sal)           from emp           sal>(select avg(sal) from emp where job='SALESMAN')) group by job having count(ename)>=2 order by cou_e desc; //打印结果JOB            COU_E--------- ----------ANALYST           2MANAGER           2//看起来上面的语句有些复杂，可能到时候我自己都不明白，所以我进行select、from、where每段进行换行的分开

五、联合查询

//去掉重复元素：并集SQL> select * from a1  2  union  3  select * from a2;//union all不去重复//交集：intersectselect * from a1 intersect select * from a2;//差集：minus（结果查找到的是只在第一个集合中二不在第二个集合中的元素）select * from a1 minus select * from a2;//表关联查询（查询到的是每个表中的dept都不为空的数据）select * from emp e,dept d where e.deptno=d.deptno;//在ORACLE中效率更高的语句select * from emp e inner join dept d on e.deptno=d.deptno;//左链接left join(以左边的表数据为准，在右边有关联的数据链接起来，没有的就为空)select * from emp e left join dept d on e.deptno=d.deptno;//右关联select * from emp e right join dept d on e.deptno=d.deptno;/*注意：实际开发中用左外联接*/

六、EXISTS函数

select * from T_USER t where not exists(select * from t_user where t.id>=3);//exists是用来判断是否存在的，当exists(查询)中的查询存在结果时则返回真，否则返回假。not exists则相反。