ITPub博客

首页 > 数据库 > Oracle > 转oracle11g学习笔记二 www daota

转oracle11g学习笔记二 www daota

Oracle 作者:wsjsjsjy 时间:2013-12-17 21:01:24 0 删除 编辑



转oracle11g学习笔记二


Oracle的复杂查询

在实际应用中经常需要执行复杂的数据统计,经常需要现实多张表的数据

所以经常要用到数据分组函数如 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 ename,sal from emp where sal= (select min(sal) from emp);

这里利用了子查询

?请显示工资最高的员工的名字,工作岗位

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

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

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

如果这里不是*号而是ename,sal等字段与分组函数那么在语句的最后要加上 group by ename,sal..(与select后的字段一致)

二、group by 和having 子句

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

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

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

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

分组字段依据必须出现在查询结果中,否则结果可读性太差

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

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

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

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

三、对分组函数的总结

1.分组函数只能出现在选择列表,having,group by,order by子句中

2.如果在select语句中同时包含有group by,having, order by,那么顺序为group by,having,order by

3.在选择列中如果有列,表达式,和分组函数,那么这些列表达式必须有一个出现在group by 子句中,否则出错 如:select deptno,avg(sal),max(sal),max(sal) from emp group by deptno having avg(sal)>2000; 这里的deptno就一定要出现在group by中

四、多表查询

基于两个或两个以上的表或是视图的查询

查单表满足不了要求如部门和员工的关系

?显示雇员名,雇员工资及所在部门的名称

select a.ename,a.sal,b.dname from emp a,dept b where a.deptno=b.deptno;

如果不加where子句,就会产生笛卡尔集

所谓笛卡尔集,就是不加筛选,将所有的都查询出来

两张表关联,至少需要一个条件排除笛卡尔集

三张表关联,至少需要二个条件排除笛卡尔集

以此类推:多表查询中,判断条件至少是表的个数-1

?如何显示部门号为10的部门名,员工名和工资

select b.dname,a.ename,a.sal from emp a,dept b where a.deptno=b.deptno    and b.deptno=10;

?显示各个员工的姓名,工资及工资的级别

select a.ename,a.sal,b.grade from emp a,salgrade b where a.sal between losal and hisal;

这里用到了between ... and 子句. 表示在losal和hisal之间(很好理解)

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

select a.ename,a.sal,b.dname,b.deptno   

from emp a,dept b  

where a.deptno=b.deptno  

order by b.deptno;

五、自连接

自连接:指在同一张表内的连接查询

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

select worker.ename,boss.ename

  from emp worker,emp boss

where worker.mgr=boss.empno   

and worker.ename='FORD';

根据FORD的名字找到ford的mgr编号再根据这个编号找到boss的empno,最后显示出来

六、子查询

子查询:指嵌入在其他sql语句中的select语句,也叫做嵌套查询

(1)单行子查询:是指只返回一行数据的子查询语句

?如何显示与SMITH同一部门的所有员工 分两步:

1.查出SMITH所在部门

2.根据部门查出所有员工

1.select deptno from emp where ename='SMITH';

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

(2)多行子查询:返回多行数据的子查询

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

1.首先查出部门10的工作种类

select job from emp where deptno=10;

发现有重复结果.所以在job前加上distinct

select distinct job from emp where deptno=10;

2.根据工作的种类查询

select ename,job,sal,deptno   

from emp where job in  

(select distinct job from emp where deptno=10);

注意这里job之后用的是"in"而非"=".

(3)all操作符

?如何显示工资比部门30的所有员工的工资高的员工的姓名,工资和部门号

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

可以使用max方法

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

max方法的效率较高,原因是使用all操作符时,要和子查询所得结果逐一比较 而使用max只需和sal中的最大值比较,减少了比较的次数,缩短了时间. 数据量较大是会比较明显,数据量较小基本看不出来

(4)any操作符

?如何显示工资比部门30的任意一个员工的工资高的员工的姓名,工资和部门号

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

可以使用min方法

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

原理同上

(5)多列子查询

多列子查询:指子查询返回多列数据 ?如何查询与SMITH部门和岗位完全相同的所有雇员

1.查询出SMITH的部门号,岗位

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

2.显示结果

select * from emp where (deptno,job)=  

(select deptno,job     

from emp    

where ename='SMITH'  

);

(6)在from子句中使用子查询

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

1.查询各个部门的平均工资和部门号

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

这里group by deptno是必须要有的.用来对分组结果进行统计

2.把上面的查询看作是一张子表

select a.ename,a.sal,a.deptno,b.avgsal  

from emp a,   (select avg(sal) avgsal,deptno from emp group by deptno) b

where a.deptno=b.deptno and a.sal>b.avgsal;

这个比较难想到,将这两个表做关联查询 当在from子句中使用子查询时,该子查询会被作为一个视图来对待,因此叫做内嵌视图 挡在from子句中使用子查询时,必须为子查询指定别名



 

一、oracle的分页

oracle的分页一共有三中方式(这里只有一种)

1.rownum 分页

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

这条语句是用来将11-15条记录提取出来,拆分这条语句

1.将所有想要的结果查询出来

select * from emp;

2.将上一步的结果作为一个视图,给每一条记录加上一个rn编号并将前15条记录查询出来.其中rownum为oracle的关键字,

且在第一次在查询字段中使用rownum时,如果有条件限制在where子句中也要用rownum,不可以用rn

select a.*,rownum rn from (select * from emp) a where rownum<=15;

3.将前15条数据作为一个视图,提取出11-15条

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

在java程序中只需要替换15和10这两个数字就可以实现分页了

二、用查询结果创建新表

这个命令是一种快捷的建表方法

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

三、合并查询

为了合并多个select语句的结果,可以使用集合操作符号,union,union all,intersect(交集),minus(差集)

(1)union

该操作符,用于取得两个结果集的并集,使用时自动去掉相同部分结果集

select ename,sal,job from emp where job='MANAGER' union select ename,sal,job from emp where sal>2500;

(2)union all

与union相似,但是不取消重复行,而且不排序

(3)intersect

取交集

(4)minus

取差集 只会显示存在第一个集合中,而不存在在第二个集合中的数据

集合操作要比and,or效率高很多

四、操作数据

使用特定的格式插入日期值

(1)使用to_date函数

?如何插入带有日期的表,并按照年月日格式插入

insert into emp values(9994,'hou','PRESIDENT',null,to_date('1990-10-10','YYYY-MM-DD'),800,1000,10);

使用to_date函数可以插入任意形式的日期

(2)使用子查询插入数据 一条insert语句可以插入大量的数据,当处理行迁移或者装载外部表的数据到数据库时, 可以使用子查询插入数据

insert into mytable(id,name,deptno) select empno,ename,deptno from emp where deptno=10;

(3)使用子查询更新数据 ?希望员工scott的岗位、工资、补助与SMITH一样

update emp set (job,sal,comm)= (select job,sal,comm from emp where ename='SMITH' ) where ename='SCOTT'; ename的值要用大写,oracle对值的大小写是敏感的

<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24059090/viewspace-1115211/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2010-06-08