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就实现了 

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐