欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

oracle表复杂查询的实例讲解

程序员文章站 2022-07-05 22:04:13
group by 和 having子句 group by用于对查询的结果分组统计, having子句用于限制分组显示结果. 对数据分组的总结 1分组函数只能出现在选择列表、...

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';