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