Oracle高级查询

关联查询	1.1内连接：两个关联的表中所有满足条件的数据(不管那张表的数据全部返回)    select emp.ename ,emp.deptno, dept.deptno from emp,dept where emp.deptno=dept.deptno;    或者是select emp.ename ,emp.deptno, dept.deptno from emp join dept on emp.deptno=dept.deptno;      1.2外连接：将两张表中不满足条件的数据也返回。    左外连接：select e.*,d.* from emp e left join  dept d on e.deptno=d.deptno;    右外连接：select e.*,d.* from emp e right join  dept d on e.deptno=d.deptno;    全连接：多张表中不满足条件的数据全部返回，等于左外连接和右外连接的总和。            select e.*,d.* from emp e full join  dept d on e.deptno=d.deptno;      1.3自连接：数据来源于同一张表中的多个列（字段），查询时将表虚拟成两张表，通过有关联的字段去连接。    查经理下面的员工有哪些 select worker.empno,worker.ename,worker.job,manager.empno,manager.ename  from emp worker join emp manager on worker.empno=manager.mgr; 查员工的领导是谁 select manager.empno,manager.ename,worker.empno,worker.ename,worker.job from emp worker join emp manager on worker.empno=manager.mgr;
子查询	（就是查询的嵌套） 查询scoot同职位的员工（分析：首先要查询scoot的职位，然后把查询出来的职位作为条件在次查询） SELECT EMPNO,ENAME FROM EMP WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME='SCOTT'); 查询员工的工资比公司平均工资高的员工是哪些员工。 select empno,ename,sal,job,hiredate from emp where sal>(select avg(sal)avg_sal from emp); 查询出部门中有SALESMAN但职位不是SALESMAN的员工的信息： select empno,ename,job,hiredate,sal from emp where deptno in(select deptno from emp where job='SALESMAN') and job<>'SALESMAN'; 2.1子查询在having子句中出现 列出最低薪水高于30部门的最低薪水的部门信息 select子句中的聚合函数怎么写，having中就怎么写 select deptno, min(sal) min_sal from emp group by deptno having min(sal)>(select min(sal) min_sal from emp where deptno=30); 列出最高工资高于10号部门最高工资的部门信息 select deptno,min(sal) max_sal from emp group by deptno having min(sal)<(select min(sal) from emp where deptno=10); 2.2子查询在form子句中 （将查询出来的数据作为视图） 查询出薪水比本部门平均薪水高的员工信息 首先查询部门的平均工资 select deptno,avg(sal) avg_sal from emp group by deptno; 查询部门员工信息 select * from emp e(需要一张表去关联部门) where e.deptno =？; 综合 select * from emp e,(select deptno, avg(sal) avg_sal from emp group by deptno) x where e.deptno =X.DEPTNO and e.sal>X.AVG_SAL order by E.DEPTNO asc; 2.3子查询在select子句中，就是外连接的另一种形式。 查询部门部门编号为null的员工 查询部门编号 SELECT e.ename,e.sal,e.deptno,(select d.deptno from dept d where d.deptno=e.deptno) deptno from emp e;
分页查询	分页查询rownum:属于oracle的方言（将来这条语句只能在oracle数据库中使用）; rownum作为伪列存在，注意不要在写大于符号，那是查不到数据的。 select rownum,empno,ename,job,sal from emp where rownum<10; 要直接截取表中的话， 要将rownum作为行内视图，在通过主查询将行内视图作为查询条件 将rownum查询出来的结果作为视图（视图就是一张表，只不过是查询出来的结果）。 select * from (select rownum rn,e.* from emp e) where rn>=13 and rn<25;--给大家看的 select * from (select rownum rn,e.* from emp e) where rn between 8 and 10;--写这个   分页与排序 1.首先将数据库中记录进行排序 2.排序后加上rownum 3.在主查询当中截取 select * from(select ROWNUM rn,t.* from(select * from emp order by sal desc) t) where rn between 9 and 15 ;
DECODE	(expr, search1, result1[, search2, result2…][, default]) 做参数匹配，如果expr，匹配到search1，返回result1 在匹配到search2，返回result2 ……如果前面都没有匹配上返回default，如果没有匹配上，又没default，返回null select empno,ename, job,sal decode(job,'manager',sal*1.2,'analyst',sal*1.1,'salesman',sal*1.05,sal) bounds from emp;
Case语句	case语句与decode相似  select empno,ename,job,sal       case job when 'manager' THEN sal*1.2   when 'analyst' then sal * 1.1   when 'salesman' then sal * 1.05   else sal end brouns from emp; decode在分组中的应用，可以实现复杂的分组，比如统计VIP的记录
排序函数	row_number:连续排序   排序函数 row_number(一般用于组内排序，而且可以得到连续唯一的编号。) select deptno,empno,ename,job,row_number() over( partition by deptno order by empno ) emp_id from emp;   rank（）重复跳跃排序 rank()与row_number相似，区别就是row_number返回的结果不重复 rank()结果重复。相同结果会跳过一个。 select deptno,empno,ename,sal,rank()over( partition by deptno order by sal desc,comm ) rank_id from emp;   dense_rank（）重复不跳跃的排序
高级分组查询	rollup(a,b,c) 对ROLLUP的列从右到左以一次少一列的方式进行分组直到所有列都去掉后的分组 --之前的做法 select sum(sales_value) "总数" from sales_tab; select year_id,count(*) "总行数",sum(sales_value) "总数"  from sales_tab group by year_id order by year_id;   select year_id,month_id,day_id,count(*) "总行数",sum(sales_value) "总数"  from sales_tab group by year_id,month_id,day_id order by year_id,month_id,day_id;   rollup()函数的做法 select year_id,month_id,day_id,count(*) row_nums,sum(sales_value) sales_value from sales_tab group by rollup(year_id,month_id,day_id) rder by year_id,month_id,day_id;     cube(a,b,c)     如果GROUP BY CUBE(a,b,c)，首先对(a,b,c)进行GROUP BY，然后依次是(a,b)，(a,c)，(a)，(b,c)，(b)，(c)，最后对全表进行GROUP BY操作，所以一共是2^3=8次分组。 年月日 年月 年日 年 月日 月 日 总 grouping sets()可以返回指定的分组集合 select year_id,month_id,count(*) num_rows,sum(sales_value) sales_value from sales_tab group by grouping sets((year_id),(month_id)) order by 1,2;
集合	UNION和UNION ALL用来获取两个或两个以上结果集的并集： UNION操作符会自动去掉合并后的重复记录。 UNION ALL返回两个结果集中的所有行，包括重复的行。 UNION操作符对查询结果排序，UNION ALL不排序。   -- 并集：显示两条语句中查询出所有数据，union去重，union all不去重 select empno,ename,job,SAL from emp where job='MANAGER' union all SELECT EMPNO,ENAME,job,SAL FROM EMP WHERE SAL>2500;   --交集 select empno,ename,job,SAL from emp where job='MANAGER' intersect SELECT EMPNO,ENAME,job,SAL FROM EMP WHERE SAL>2500;   --差集(去掉交集，第二个结果集中的数据全部不要) select empno,ename,job,SAL from emp where job='MANAGER' minus SELECT EMPNO,ENAME,job,SAL FROM EMP WHERE SAL>2500;