oracle高级查询（实例基于scott用户四张表）
oracle高级查询（实例基于scott用户四张表）
分组查询
多表查询
子查询
综合实例
=======================================================================
scott用户的四张表(emp,dept,bonus,salgrade)
没有这四张表的可参考http://blog.csdn.net/love_legain/article/details/54311040进行创建
-------------------------------------------
desc emp
名称 空值 类型
-------- -------- ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
---------------------------------------------
desc dept
名称 空值 类型
------ -------- ------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
------------------------------------------------
desc salgrade
名称 空值 类型
----- -- ------
GRADE NUMBER
LOSAL NUMBER
HISAL NUMBER
------------------------------------------------
desc bonus
名称 空值 类型
----- -- ------------
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
SAL NUMBER
COMM NUMBER
=============================分组查询==================================
①分组函数的概念
分组函数作用于一组数据，并对一组数据返回一个值
②分组函数的使用
--select AVG(sal),sum(sal) from emp;
-- select max(sal),min(sal) from emp;
--select count(*) from emp;
--select count (distinct DEPTNO) from emp;
wm_concat:行转列
select deptno,wm_concat(ename) from emp group by deptno--11gr2和12C上已经摒弃了wm_concat函数
在分组函数中使用nvl函数：nvl函数使分组函数无法忽略空值
select count(*),count(NVL(comm,0)) from emp;
③使用group by子句数据分组
select deptno,avg(sal) from emp group by deptno;
注意：在select列表中所有未包含在组函数中的列都应该包含在group by子句中
包含在group by子句中的列不必包含在select列表中
④使用having子句过滤分组结果集
不能再where子句中使用分组函数
可以在having子句中使用分组函数
select deptno,avg(sal) from emp group by deptno having deptno=10;
select deptno,avg(sal) from emp where deptno=10 group by deptno;
⑤在分组查询中使用order by子句
⑥group by语句得增强
select deptno,job,sum(sal) from emp group by deptno,job;
+
select deptno,sum(sal) from emp group by deptno;
+
select sum(sal) from emp;
=
select deptno,job,sum(sal) from emp group by rollup(deptno,job);
sql*plus的报表功能
================================多表查询================================
①什么是多表查询
从多个表中获取数据
②笛卡尔积
③等值连接
select e.empno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno;
④不等值连接
select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;
⑤外连接
核心：通过外链接，把链接不成立的记录，任然包含在最后的结果中
左外连接：当连接条件不成立的时候，等号左边的表依然被包含
右外连接：当连接条件不成立的时候，等号右边的表依然被包含
select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数 from emp e,dept d where e.deptno(+)=d.deptno group by d.deptno,d.dname;--右外连接
⑥自连接
核心：通过别名，将同一张表视为多张表
select e.ename 员工姓名,b.ename 老板姓名 from emp e,emp b where e.mgr=b.empno;
自连接存在的问题：不适合操作大表
⑦解决方法：层次查询
select level,empno,ename,sal,mgr from emp connect by prior empno=mgr start with mgr is null order by 1;
==============================子查询===================================
①子查询概述
--查询比scott工资高的员工信息
select * from emp where sal>(select sal from emp where ename='scott');
②子查询的使用
可以使用子查询的位置:where,select,having,from
主查询和子查询可以不是同一张表
select * from emp where deptno=(select deptno from dept where dname='SALES');
select e.* from emp e,dept d where e.deptno=d.deptno and d.dname='SALES';
一般不在子查询中，使用排序，但在top-n分析问题中，必须对子查询排序
--rownum 行号 伪列
select rownum,empno,ename,sal from (select * from emp order by sal desc) where rownum <= 3;
行号永远按照默认的顺序生成
行号只能使用<,<=;不能使用>,>=
一般先执行子查询，再执行主查询，但相关子查询除外
select empno,ename,sal,(select avg(sal)from emp where deptno=e.deptno) avgsal from emp e where sal>(select avg(sal)from emp where deptno=e.deptno);
单行子查询和多行子查询
操作符（多行）
in 等于列表中的任何一个
any 和子查询返回的任意一个值比较
all 和子查询返回的所有值比较
操作符（单行）
= equal to
> greater than
>=greater than or equal to
<less than
<= less than or equal to
<>not equal to
select * from emp where job=(select job from emp where empno=7566) and sal >(select sal from emp where empno=7782);
select * from emp where sal=(select min(sal) from emp);
--查询最低工资大于20号部门最低工资的部门号和部门的最低工资
select deptno,min(sal) from emp group by deptno having min(sal) > (select min(sal) from emp where deptno=20);
--查询部门名称是SALES和ACCOUNTING的员工信息
select * from emp where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING');
select e.* from emp e,dept d where e.deptno=d.deptno and (d.dname='SALES' OR d.dname='ACCOUNTING');
SELECT * from emp where sal>any(select sal from emp where deptno=30);
--等价于
select * from emp where sal >(select min(sal) from emp where deptno=30);
子查询对控制问题
--查询不是老板的员工
select * from emp where empno not in (select mgr from emp where mgr is not null);
===================================综合实例=============================
实例一
分页查询显示员工信息：显示员工号，姓名，月薪
-每页显示四条记录
-显示第二页的员工
-按照月薪降序排列
select r,emp,ename,sal
from(select rownum r,empno,ename,sal
from(select rownum,empno,ename,sal from emp order by sal desc) e1 where rownum <=8) e2
where r>=5;
--oracle分页通过子查询实现
实例二
找到员工表中薪水大于本部门平均薪水的员工
select e.empno,e.name,e.sal,d.avgsal from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d where e.deptno=d.deptno and e.sal>d.avgsal;
实例三
按部门统计员工人数，按照如下格式输出（员工的入职年份已知）
total 1980 1981 1982 1987
14 1 10 1 2
select count(*) total,
sum(decode(to_char(hiredate,'YYYY'),'1980',1,0)) "1980",
sum(decode(to_char(hiredate,'YYYY'),'1981',1,0)) "1981",
sum(decode(to_char(hiredate,'YYYY'),'1982',1,0)) "1982"
from emp;
--使用子查询方式
select
(select count(*) from emp) total,
(select count(*) from emp where to_char(hiredate,'yyyy')='1980') "1980",
(select count(*) from emp where to_char(hiredate,'yyyy')='1981') "1981",
(select count(*) from emp where to_char(hiredate,'yyyy')='1982') "1982"
from dual;
