oracle 雇佣表,Oracle 表复杂查询（五）

表基本查询用 PL/SQL 的 scott 用户自带的四个表，选用三个表进行练习。

雇员表 emp

emp表

部门表 dept

dept表

工资级别表 salgrade

salgrade表

一、数据分组

① max、min、avg、sum、count

显示所有员工中最高工资和最低工资

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

显示最高工资的员工的名字(涉及子查询)

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

显示所有员工的平均工资和工资总和

select avg(sal),sum(sal) from emp;

计算共有多少员工

select count (*) from emp;

显示工资最高的员工的名字，工作岗位

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

显示工资高于平均工资的员工的信息

select avg(sal) from emp;

select * from emp where sal>(select avg(sal) from emp);

② group by 和 having 子句

注：

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

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

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

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

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

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

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

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

总结：数据分组

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

如果在 select 语句种同时包含有 group by、having、order by，那么他们的出现顺序必须是 group by、having，order by。

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

比如：

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

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

二、多表查询

多表查询是指：基于两个或两个以上的表或视图的查询。

① 笛卡尔积，利用别名

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

select a1.ename,a1.sal,a2.deptno from emp a1,dept a2 where a1.deptno=a2.deptno;

注意：

多表查询的条件是：至少不能少于表的个数-1。也就是说，如果有两个表，他的条件就有一个。如果是三个表，他的条件就有两个。

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

select al.dname,a2.ename,a2.sal from dept a1,emp a2 where a1.deptno=a2.deptno and a1.deptno=10;

显示各个员工的姓名、工资、及其工资的级别

工资级别表在：salgrade 表中

select * from salgrade;

GRADE LOSAL HISAL

---------- ---------- ----------

1 700 1200

2 1201 1400

3 1401 2000

4 2001 3000

5 3001 9999

select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;

显示雇员名、雇员工资及所在部门的名字、并按部门排序

select a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno order by a1.deptno;

② 自连接 (利用别名)

自连接：同一张表的连接查询

显示某个员工的上级领导的姓名

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

显示 'FORD' 的上级

select worker.ename,boss.ename from emp worker,emp boss where worker.mgr = boss.empno and worker.ename='FORD';

③ 子查询

子查询：嵌入在其他 sql 语句中的 select 语句，也叫嵌套查询

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

select * from table where = (select …… select()) //嵌套格式

显示与SMITH同一部门的所有员工

select deptno from emp where ename='SMITH';//先查询出smith的部门号

select * from emp where deptno=(select deptno from emp where ename='SMITH');//再嵌套显示显示与SMITH同一部门的所有员工

注：数据库在执行sql是从左到右顺序执行。

④ 多行子查询：返回多行数据的子查询

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

select distinct job from emp where deptno=10;//先查询10号部门的工作

select * from emp where job in (select distinct job from emp where deptno=10);//再嵌套查询和部门10的工作相同的雇员的名字、岗位、工资、部门号

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

显示工资比部门 30 的所有员工的工资高的员工的姓名、工资和部门号

//方法1

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

//方法2

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

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

⑥ 多列子查询

多列子查询：查询返回多个列数据的子查询语句。

单行子查询：是子查询只返回单列，单行数据。

多行子查询：返回单列多行数据，都是针对单列而言。

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

select deptno,job from emp where ename='SMITH';//先查询出SMITH的部门号，岗位

select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');//再嵌套查询与SMITH的部门和岗位完全相同的所有雇员

⑦ 在 from 子句中使用子查询

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

select deptno,avg(sal) mysal from emp group by deptno;//先查出各个部门的平均工资和部门号

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

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

where e.deptno =ds.deptno and e.sal>ds.mysal;//再把上面的查询结果看做是一张子表

总结:

当 from 子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫做内嵌式图。

当 from 子句中使用子查询时，必须给子查询指定别名。

注意：

给表取别名的时候，不能加 as ;但是给列取别名是可以加 as 的。

别名不能用 as 的情况，如：

(select deptno,avg(sal)mysal from emp group by deptno)ds where e.deptno =ds.deptno and e.sal>ds.mysal; //在ds前不能加as,否则会报错

⑧ 分页查询

Oracle分页三种方式：

1. 按 rowid 分页(效率最高，复杂)

2. 按分析函数来分

3. 按 rownum 分页

其中利用 rownum 分页较为简单，所以这里以 rownum 为例：

第一步：子查询

select * from emp;

第二步：显示 rownum(Oracle分配的)

select a1.*,rownum rn from (select * from emp) al;

第三步：选出 rn 编号大于等于 10 的员工信息。

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

选出 rn 行号大于等于 10 的员工中再次截取大于等于 6 的行号的员工信息。

如果需在这 rn 行号大于等于 10 的员工中再次截取大于等于 6 的行号就不能再使用 rownum 。需要使用子查询再次切割。

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

注意：

指定查询列，只需要修改最里层的子查询。例如：

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

⑨ 其他

使用特点格式插入日期值

使用to_date函数

使用子查询插入数据(插入大量数据)

当使用Values子句时，一次只能插入一行数据;

当使用子查询插入数据时，一条insert语句可以插入大量得数据;

当处理行迁移或者装载外部表的数据到数据库时，可以使用子查询来插入数据。

//先新建一张表，这张表想存储emp表中的部分数据，则导入。

create table kkk(myId number(4),myname varcher2(50),myDept number(5));

//把emp表中的10号员工的信息导入到kkk表

insert into kkk (myID,myname,myDept) select empno,ename,deptno from emp where deptno =10;