orcl 复杂查询

测试环境：

create table bqh6 (xm varchar2(10),bmbh number(2),bmmc varchar2(15),gz int);
insert into bqh6 values ('张三',01,'技术支持',3500);
insert into bqh6 values ('李四',02,'研发',4500);
insert into bqh6 values ('王五',03,'外业测绘',5000);
insert into bqh6 values ('小崔',02,'研发',8000);
insert into bqh6 values ('钱六',01,'技术支持',5500);
insert into bqh6 values ('赵二',03,'外业测绘',4500);
select * from bqh6

create table bqh7 (xm varchar2(10),bmbh number(2),bmmc varchar2(15), gwjb varchar2(10));

insert into bqh7 values ('张三',01,'技术支持','C');
insert into bqh7 values ('李四',02,'研发','C');
insert into bqh7 values ('王五',03,'外业测绘','A');
insert into bqh7 values ('小崔',02,'研发','A');
insert into bqh7 values ('钱六',01,'技术支持','A');
insert into bqh7 values ('赵二',03,'外业测绘','B');
select * from bqh7

分组函数：max, min, avg, sum, count

查询工资最高和最低的人的姓名:

select '最高的：'||xm,gz from bqh6 where gz=(select max(gz)from bqh6) union all
select '最低的：'||xm,gz from bqh6 where gz=(select min(gz)from bqh6)

查询所有工资低于平均工资的人的信息：

select * from bqh6 where gz< (select avg(gz) from bqh6)

给所有低于平均工资的员工薪水上涨10%：

update bqh6 set gz=gz+(select avg(gz) from bqh6)*0.1 where gz< (select avg(gz) from bqh6);

commit;

select * from bqh6;

groupt by用于对查询结果分组统计

查询每个部门的平均工资和最高工资：

select avg(gz),max(gz),bmmc from bqh6 group by bmmc;

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

查询平均工资低于5000的部门名称和它的平均工资：

select bmmc,avg(gz) from bqh6 group by bmmc having avg(gz)<5000

注意：

分组函数(max，min，avg，count)只能出现在选择列表(select后)，having和order by子句中；

如果select语句中同时包含group by，having和order by，他们的顺序必须是group by，having和order by (先分组→再抑制结果显示→最后分组)；

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

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

多表查询

 查询雇员姓名及工资和所在部门的的岗位级别：

select a.xm,a.gz,b.gwjb from bqh6 a,bqh7 b where a.xm=b.xm

笛卡尔积，原则：多表查询的条件是至少不能少于表的个数-1

查询部门编号为10的部门名称、雇员姓名和工资：

select a.xm,b.bmbh,b.bmmc,a.gz from bqh6 a,bqh7 b where a.xm=b.xm and b.bmbh=2;

 查询雇员姓名、工资以及所在部门名称并按部门排序

select a.xm,b.bmbh,b.bmmc,a.gz from bqh6 a,bqh7 b where a.xm=b.xm order by b.bmmc

order by 默认升序(asc)，降序(desc)

子查询

 .单行子查询

查询与张三同一部门的所有雇员姓名：

select a.xm from bqh6 a where a.bmmc=(select bmmc from bqh7 b where b.xm='张三')

 多行子查询

 查询部门编号为3的雇员姓名、部门名称、工资：

all在多行子查询的使用：与每一个内容相匹配

查询工资比部门编号3的所有员工的工资高的雇员姓名、工资和部门名称：

>ALL：比子查询中返回的最大的记录还要大

<ALL：比子查询中返回的最小的记录还要小

①select xm,bmmc,gz from bqh6 where gz>all (select max(gz) from bqh6 where bmbh=3)

②select xm,bmmc,gz from bqh6 where gz>(select max(gz) from bqh6 where bmbh=3)

②的效率要高于①，因为①会逐条的对比，而②直接比较结果。

IN在子查询的使用：用于指定一个子查询的判断范围

查询部门编号为1的雇员姓名、部门名称及工资信息。

select * from bqh6 where gz in(select gz from bqh6 where bmbh=1);

 any在多行子查询的使用：与每一个内容相匹配，有三种匹配形式

①=any：功能与IN操作符是完全一样的

②>any：比子查询中返回记录最小的还要大的数据

 

③<any：比子查询中返回记录最大的还要小的数据

 

FROM子句中使用子查询一般都是返回多行多列，可以将其当作一张数据表：

 查询出每个部门的编号，名称，部门人数，平均工资

select a.xm,a.bmbh,a.bmmc,b.部门人数,b.平均工资 from bqh6 a,
(select bmbh bh,count(xm) 部门人数,avg(gz) 平均工资 from bqh6 group by bmbh) b
where a.bmbh=b.bh

注意：

当在from子句中使用子查询时，该子查询会被作为一个视图对待，因此叫做内嵌视图，当在from子句中使用子查询时，必须给子查询指定别名。给列取别名可以使用as，但是给表、视图、子查询起别名不可以用as

分页查询：ROWNUM为显示每一条记录动态自动生成行号。

 为显示每一条记录动态自动生成行号

查询结果就会多出一列，rn，表示rownum，行号数，是Orcl分配的。

查询前条记录：

查询前3-6条记录：

Oracle的分页是最复杂的，要使用2次子查询，但效率也是最高的，因为内部使用了2分查找的原理。MySql的分页是最简单的，直接一个limit就实现了