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           2
MANAGER           2
//看起来上面的语句有些复杂，可能到时候我自己都不明白，所以我进行select、from、where每段进行换行的分开

五、联合查询

//去掉重复元素：并集
SQL> select * from a1
  2  union
  3  select * from a2;
//union all不去重复

//交集：intersect
select * 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则相反。