oracle常用查询语句示例
最近复习了一下简单的查询语句,举出一些常用的例子,以oracle自带的表emp为例::

一.单表查询

1.查询出1982年1月1号以后入职的员工

select empno,ename,hiredate from emp where hiredate>'1-1月-1982';

比较日期时,一定要注意日期的格式为"1-1月-1982",不能写为"1982-1-1"

2.查询出工资最高的员工姓名和工资

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

3.查出最高工资和最低工资

select max(sal),min(sal) from emp;

4.查出工资高于平均工资的员工

select ename,sal from emp where sal>(select avg(sal) from emp);

5.显示每个部门的平均工资和最高工资

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

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

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

注意此处:当需要按多个条件进行分组时,都放在group by之后并用","隔开即可。


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

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

二.多表查询:

1.显示雇员名，雇员工资及所在部门的名字

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

2.显示部门号为10的部门名、员工名和工资

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

3.显示各个员工的姓名，工资，及其工资的级别

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

4.显示雇员名，雇员工资及所在部门的名字，并按部门排序。

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

5.自连接:在同一张表的连接查询

查出某个员工的上级领导。

select worker.ename,boss.ename from emp worker,emp boss where
worker.MGR=boss.empno;

查出"SMITH"的上级领导？？

select worker.ename,boss.ename from emp worker,emp boss where
worker.MGR=boss.empno and worker.ename='SMITH';

6.如何显示与SMITH同一部门的所有员工

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

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

select ename,job,sal,deptno from emp where job in(select job from emp where
deptno=10);

8.显示工资比部门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);

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

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

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

10.如何查询与SMITH的部门和岗位完全相同的所有雇员

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

11.如何显示高于自己部门平均工资的员工的信息

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

12.oracle分页:

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

13.用查询结果创建新表:

create table myEmp(id,name,sal,job) as select empno,ename,sal,job from emp;

14.合并查询: 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:取得两结果集的并集,不会取消重复行,而且不会排序

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 wher