Oracle表查询(分组查询,多表查询,子查询,集合查询等)

--Oracle表查询(分组查询,多表查询,子查询,集合操作等)

 

--分组函数(max, min, avg, sum, count)

--1. 查询所有员工最高和最低工资

select max(sal) 最高工资, min(sal) 最低工资 from emp;

 

--2. 利用子查询查询最高工资的人是谁

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

 

--3. group by(用于对查询的结果分组统计) 查询每个部门的平均工资和最高工资

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

 

--4. having(用于限制分组显示结果)

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

 

--5. 多表查询

 

--a. 查询名字,工资及所在部门的名字

select e.ename, e.sal, d.dname from emp e, dept d where e.deptno = d.deptno;

 

--b. 查询员工的行吗,工资及工资级别

select e.ename, e.sal, s.grade from emp e, salgrade s where e.sal between s.losal and s.hisal;

 

--自连接 显示员工FORD的上级

select worker.ename, boss.ename from emp worker, emp boss

where worker.mgr = boss.empno and worker.ename = 'FORD';

 

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

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操作符

select ename, sal, deptno from emp

where sal > any(select sal from emp where deptno = 30);

--等价于

select ename, sal, deptno from emp where sal >

(select min(sal) from emp where deptno = 30);

 

--多列子查询

select deptno, ename, job from emp

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

 

--在from子句中使用子查询 如何显示高于自己部门平均工资的员工信息

select e.ename, e.deptno, e.sal, ds.avgsal from emp e,

    (select deptno, avg(sal) avgsal from emp group by deptno) ds

where e.deptno = ds.deptno and e.sal > ds.avgsal;

 

--用查询结果创建新表

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

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

 

--合并查询 union取得两个结果的并集,自动去掉重复行,union all则不去除重复行

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

union

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

 

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

union all

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

 

--取交集 intersect

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

intersect

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

 

--取差集 minus

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

minus

 

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