Oracle复杂查询

Oracle复杂查询

数据分组，

分组函数max, min, avg, sum,count

1. 查询工资最高的人的姓名

select ename from emp where sal =(select max(sal) from emp);

1. 给所有低于平均工资的员工薪水上涨10%

update emp set sal = (select avt(sal)from emp)*10% where sal<(select avt(sal) from emp);

grout by 和 having 字句

groupt by用于对查询结果分组统计

having子句用于限制分组结果显示

1. 如何显示每个部门的平均工资和最高工资

select avg(sal),max(sal), deptno from emp group by deptno;

1. 显示每个部门的每种岗位的平均工资和最低工资

select avg(sal),min(sal),deptno,job fromemp group by deptno,job;

1. 显示平均工资低于2000的部门和号和它的平均工资

select deptno,avg(sal) from emp group bydeptno having avg(sal)>2000;

 

总结：

1. 分组函数(max，min，avg，count)只能出现在选择列表(select后)，having和order by子句中
2. 如果select语句中同时包含group by，having和order by，他们的顺序必须是group by，having和order by
   （先分组→再抑制结果显示→最后分组）
3. 在选择列中如果有列，表达式和分组函数，那么这些列和表达式必须有一个出现在group by子句中，否则就会报错。如deptno

 

多表查询

1. 显示雇员名，雇员工资和雇员所在部门的名称

select a1.ename,a1.sal,a2.dname from empa1,dept a2 where a1.deptno=a2.deptno;

笛卡尔积，原则：多表查询的条件是至少不能少于表的个数-1

1. 如何显示部门号为10的部门名、员工名和工资

select b.dname, a.ename,a.sal from empa,dept b where b.deptno=a.deptno and a.deptno=10;

——先把2张表连上，再用用红色条件过滤掉

1. 显示各个员工的姓名、工资及其工资的级别

select a1.ename,a1.sal,a2.grade from empa1, salgrade a2 where a1.sal between a2.losal and a2.hisal;

1. (扩展)显示雇员名、雇员工资以及所在部门的名字并按部门排序

select a1.ename,a1.sal,a2.dname from empa1,dept a2 where a1.deptno = a2.deptno order by a1.deptno;

——多表排序

 

自连接

自连接是指在同一张的连接查询

1. 显示某个员工上级领导的姓名。如Ford

select a.ename from emp a where a.mgr =a.empno and a.ename = 'Ford';

select worker.ename, boss.ename from empworker, emp boss where worker.mgr = boss.empno and worker.ename = 'FORD';

——小窍门：把一张表想象成2张不同的表，起别名，然后就好做了。

 

子查询

子查询是指嵌入在其它sql语句中的select语句。也叫嵌套查询。

单行子查询

单行子查询是指只返回一行数据的查询语句；

例如：如何显示与SMITH同一部门的所有员工

select a.ename from emp a where a.deptno= (select deptno from emp b where b.ename=' SMITH');

多行字查询

多行子查询是指返回多行数据的查询语句

例如：如何查询和部门10的工作相同的雇员名字、岗位、工资和部门号

select * from emp a where a.job in(select distinct job from emp where emp.deptno = 10);

在多行子查询中使用all操作

例如：如何显示工资比部门30的所有员工的工资高的员工姓名、工资和部门号

方法1：

select ename,sal,deptno from emp wheresal >all (select sal from emp where deptno=30);

方法2：

select ename,sal,deptno from emp wheresal > (select max(sal) from emp where deptno=30);

比较：

方法2效率要远高于方法1，因为方法1会一条一条的比较；方法2直接比较结果。

 

在多行子查询中使用any操作符

例：显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号

方法1：

select a.ename,a.sal,a.deptno from emp awhere a.sal > any(select sal from emp where deptno=30);

方法2：

select ename,sal,deptno from emp wheresal > (select min(sal) from emp where deptno=30);

多列子查询

单行子查询是指子查询返回单行、单列数据；

多行子查询是指子查询返回多行、单列数据。都是针对单列而言的；

而多列子查询则是指子查询返回多个列的子查询语句。

例如：查询与Smith部门和岗位完全相同的所有雇员。

select * from emp where(deptno,job)=(select deptno,job from emp where ename='SMITH');

 

from子句中使用子查询

例子：显示高于自己部门平均工资的职员信息

1)查询各个部门的平均工资和部门号

select deptno, avg(sal) from emp groupby deptno;

2)查询自己部门的

把上面的查询看做是一张子表， (select deptno, avg(sal) from emp group by deptno) a1

selecta2.ename,a2.sal,a2.deptno,a1.avgSal from emp a2, (select deptno, avg(sal)avgSal from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal >a1.avgSal;

 

总结：当在from子句中使用子查询时，该子查询会被作为一个视图对待，因此叫做内嵌视图，当在from子句中使用子查询时，必须给子查询指定别名。

——给列取别名可以使用as，但是给表、视图、子查询起别名不可以用as

 

分页

Oracle的分页是最复杂的，要使用2次子查询，但效率也是最高的，因为内部使用了2分查找的原理。MySql的分页是最简单的，直接一个limit就实现了；SqlServer次之。

Oracle的分页一共有3中方式

1. rownum 先做一个子查询

select * from emp

1. 显示rownum[rownum是Oracle分配的]

select a1.*,rownum rn from (select *from emp) a1;

查询结果就会多出一列，rn，表示rownum，行号数，是Oracle分配的。

1. 显示想要选取的行数据

select a1.*,rownum rn from (select *from emp) a1 where rownum<=10;

and rownum>=6;(rownum不能使用2次,否则查不出数据)

到此，可以显示1-10行数据，已经砍掉一大半了，目标是显示6-10行数据。

1. 再做一次子查询

select * form (select a1.*,rownum rnfrom (select * from emp) a1 where rownum<=10) a2 where rn>=6;

结果才是想要的6-10行数据。

 

注意，几个查询变化：

1. 如果要指定查询列，而不是查询所有列，只需修改最里层的子查询即可；
2. 排序也只需修改最里层的子查询就可。
3. 同理，分组啊，再排序啊，统统的改动最里层的子查询即可。