oracle表复杂查询的实例讲解

group by 和 having子句

group by用于对查询的结果分组统计,

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

对数据分组的总结

1分组函数只能出现在选择列表、having、order by子句种

2 如果在select 语句种同时包含有group by ,having ,order by 那么他们的顺序是group by , having , order by

3 在选择列种如果有列、表达式、和分组函数,那么这些列和表达式必须有一个出现在group by 子句中，否则就会出错

如select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000;

这里deptno就一定要出现在 group by 中

自连接

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

什么是子查询

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

■ 单行子查询

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

请思考：如何显示与SMITH同一部门的所有员工?

■ 多行子查询

多行子查询指返回多行数据的子查询

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

请思考:如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

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

扩展要求：大家想想还有没有别的查询方法.

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

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

请思考:如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

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

扩展要求：大家想想还有没有别的查询方法.

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

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

请思考:如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号

扩展要求：

大家想想还有没有别的查询方法.

多列子查询

单行子查询是指子查询只返回单列、单行数据,多行子查询是指返回单列多行数据,都是针对单列而言的，而多列子查序则是指查询返回多个列数据的子查询语句

请思考如何查询与smith的部门和岗位完全相同的所有雇员

在from子句中使用子查询

请思考：如何显示高于自己部门平均工资的员工的信息

这里要用到数据查询的小技巧，把一个子查询当作一个临时表使用

解法①

select e1.*,e2.myavg from emp e1,(select avg(sal) myavg,deptno from emp group by deptno) e2 where e1.deptno=e2.deptno and e1.sal>e2.myavg

解法②

select e1.* from emp e1 where e1.sal>(select avg(sal) from emp where deptno=e1.deptno)

在from子句中使用子查询

请思考：查找每个部门工资最高的人的详细资料

解法①

解法②

思路:得到所有的员工，进行筛选，每拿到一个员工，判断该员工的工资是否是他们部门的最高工资。

select * from emp e where sal=(select max(sal) from emp where deptno=e.deptno);

在from子句中使用子查询

请思考：显示每个部门的信息和人员数量

解法①

自己完成…

解法②

查询获得所有的部门信息，每获得一个部门，我们就查询该部门的人数，保存为一个新的列。

select d.*,(select count(*) from emp where deptno=d.deptno) allnum from dept d;

在from子句中使用子查询

这里需要说明的当在from子句中使用子查询时

,该子查询会被作为一个临时表来对待,

当在from子句中使用子查询时，必须给子查询

指定别名.

用查询结果创建新表

这个命令是一种快捷的建表方法.

create table mytable (id,name,sal,job,deptno)

as select empno,ename,sal,job,deptno from emp;

■ 自我复制数据(蠕虫复制)

有时，为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据。

insert into mytable (id,name,sal,job,deptno)

select empno,ename,sal,job,deptno from emp;

合并查询

有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号 union , union all,intersect , minus

1)union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。

select ename,sal,job from emp where sal>2500 union

select ename,sal,job from emp where job=‘MANAGER';

2)union all

该操作赋与union相似，但是它不会取消重复行,而且不会排序。

select ename,sal,job from emp where sal>2500

union all select ename,sal,job from emp where

job='manager';

3)intersect

使用该操作符用于取得两个结果集的交集。

select ename,sal,job from emp where sal>2500

intersect select ename,sal,job from emp where

job='manager';

4) minus

使用该操作符用于取得两个结果集的差集，它只会显示存在第一个集合中，而不存在第二个集合中的数据。

select ename,sal,job from emp where sal>2500 minus

select ename,sal,job from emp where job='manager';