--Oracle复杂查询

 

--Oracle复杂查询：
--一，分类：
--1，连接查询
--2，子查询
--3，分页与层次查询。

--二，连接查询：
--1，使用情景：当用户需要用select显示多个表的字段时，使用连接查询。
--例1，查看所有员工的姓名及工作地点，部门编号。
--分两种：

--2,格式
--1）传统连接查询
--select 表.列,表.列,...... from 表1,表2,.... where 连接条件 and 筛选条件;
select * from emp,dept where emp.deptno=dept.deptno;--返回两个表都满足记录的数据
select ename,loc,dept.deptno from emp,dept where emp.deptno=dept.deptno;
--注意：显示两个表都有的数据时，要用表名来约束dept.deptno

--2）标准连接查询
--select 表.列，表.列 ,..... from 表1 inner join 表2 on 连接条件  where 筛选条件;
select ename,loc,emp.deptno from emp inner join dept on emp.deptno=dept.deptno;

--多个表
select 表.列，表.列 ,..... from
       ((表1 inner join 表2 on 表1.列=表2.列)
       inner join 表3 on 表3.列=表1/2.列)
       inner join 表4 on 表4.列=表1/2/3.列

--例2
--查看在DALLAS工作的员工的姓名及工作地点，部门编号。
select ename,loc,emp.deptno from emp,dept where emp.deptno=dept.deptno and loc='DALLAS';

select ename,loc,emp.deptno from emp inner join dept on emp.deptno=dept.deptno where loc='DALLAS';

--使用别名
select ename,loc,e.deptno from emp e,dept d where e.deptno=d.deptno and loc='DALLAS';
select ename,loc,e.deptno from emp e inner join dept d on e.deptno=d.deptno where loc='DALLAS';

--3，外连接：
内连接-- inner join 特点：返回的是两个表都满足条件的记录
--有的员工（张三）没有部门，有的部门（40）没有员工
外连接 --
左(join)外连接 -- 返回的是两个表都满足条件的记录，及左边的表中不满足条件的记录
右外连接 -- 返回的是两个表都满足条件的记录，及右边的表中不满足条件的记录

--例3 ，查看员工姓名工作地点，部门名称，部门编号，包括没有员工的部门
select * from emp right outer join dept on emp.deptno=dept.deptno;
select * from dept left outer join emp on emp.deptno=dept.deptno;

-- 传统连接：(+)对面的不满足条件的记录也返回
select * from emp,dept where emp.deptno(+)=dept.deptno;

--例4 ，查看员工姓名工作地点，部门名称，部门编号，包括没有部门的员工
select * from emp left outer join dept on emp.deptno=dept.deptno
select * from emp,dept where emp.deptno=dept.deptno(+);

--4,全外
--例5 ，查看员工姓名工作地点，部门名称，部门编号，包括没有员工的部门,及没有部门的员工
select * from emp full outer join dept on emp.deptno=dept.deptno;

--5,自连接
-- 特定场景，通常用于自己表中的两个字段，一个是主键，一个是外键，report to
-- 显示员工姓名及其上级姓名
select e1.ename 上级,e2.ename 下级 from emp e1 inner join emp e2 on e1.empno = e2.mgr ;

-- 连接的优点：1，返回多表记录    2，可以使用索引，速度快，但是功能有限。

--三，子查询：
--分类：嵌套子查询，关联子查询。
--1，嵌套子查询
特点：1，运行自内而外，2，子查询可以独立运行
--例1：查询工资高于 SMITH 的所有员工
select * from emp where sal>(select sal from emp where ename='SMITH');

--例2：找到高于公司平均工资的员工
select * from emp where sal>(select avg(sal) from emp);

--例3：找到最高工资的员工
select * from emp where sal=(select max(sal) from emp);

--2, 可以使用 > >= < <= = !=  ，但是这些关系运算符后只能跟 一个值。
--例4，找出工资高于 SMITH及FORD 的员工。
select * from emp where sal>(select sal from emp where ename='SMITH' or ename='FORD');

--> >= < <= = != 对于多个值，可以使用 >all  >any
>all  --  >select max()
>any  --  >select min()
select * from emp where sal>all(select sal from emp where ename='SMITH' or ename='FORD');

-- 例5，找出比 10 部门最低工资高的员工
select * from emp where sal>(select min(sal) from emp where deptno=10);
select * from emp where sal>any(select sal from emp where deptno=10);

--3， 使用 in
-- 找到在 DALLAS 或 NEW YORK
select * from emp where deptno in(select deptno from dept where loc in('DALLAS','NEW YORK')) ;
-- in(跟多个值)

--4，关联子查询
--特点：1，运行自外而内   2，子查询不可以独立运行
-- 例6，查找工资高于 本部门 平均工资的员工。
select * from emp a where a.sal>(select avg(sal) from emp where deptno=a.deptno);
                         
-- 例7，查找每个部门的最高工资的员工
-- 关联子查询
select ename,deptno from emp a where a.sal=(select max(sal) from emp where deptno=a.deptno);

-- 嵌套子查询
select * from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);

-- 名次问题：
select * from emp a where
       (select count(*) from emp where deptno=a.deptno and sal>a.sal)=0;

-- 例8，找到每个部门的第二高的工资
select * from emp a where
       (select count(*) from emp where deptno=a.deptno and sal>a.sal)=1;
      

四，select做表达式：
--例1：查询部门信息，及每个部门的人数
select a.*,(select count(*) from emp where deptno = a.deptno) 部门人数 from dept a;      

--例2：查询部门信息，及每个部门 工资在500-1000  1001-3000  3001- 的人数
select a.*,
(select count(*) from emp where sal>=500 and sal<=1000 and a.deptno=deptno) "500-1000",
(select count(*) from emp where sal>=1001 and sal<=3000 and a.deptno=deptno) "1001-3000",
(select count(*) from emp where sal>=3001 and a.deptno=deptno) "3001-"
from dept a;

五，特殊的select
1，备份表：
create table empbk1 as select *from emp;
select * from empbk1;

2, 备份表结构
create table empbk3 as select * from emp where 1=2;

3, 批量填充数据
insert into empbk3 select * from emp;
select * from empbk3

4, 选择批量填充数据
insert into empbk3(empno,ename) select empno,ename from emp where sal>1000;

六，层次查询：
--level --伪列
select * from emp connect by prior 层次条件(父=子) start with 起始搜索条件

select lpad('+',level*10,' ')||a.ename,level from emp a connect by prior empno = mgr start with ename='KING';

select lpad('+',10,'#')||'abcd' from dual;

--应用：
--计算jones 部门的所有员工工资
select sum(sal) from emp a connect by prior empno = mgr start with ename='JONES';

--七，分页 -- 伪列 rownum

create or replace package myp
as--包开始
type curtype is ref cursor;--游标类型
function myfun(age number) return curtype;
end;--包结束
/
--声明了一个包，包含了一个游标类型，包含了一个函数：传入年龄，返回游标

create or replace package body myp
as--包开始
 function myfun(age number) return curtype
 as--函数定义变量
 mycur curtype;

 open mycur for select * from usinfo where uage<age;
 return mycur;
 end;--函数结束
end;--包结束
/

 

create or replace package pk
as--包开始
type ctype is ref cursor;--定义游标类型 以供fun和proc使用
function getuserf(ps number) return ctype;
procedure getuserp(ps number,mycur in out ctype);
end;--包结束
/

函数的返回值，必须要在as后定义
游标如果作为存储过程的参数，则必须定义为in out类型

create or replace package body pk
as--包开始
 function getuserf(ps number) return ctype
 as--函数开始
 mycur ctype;
 begin
  open mycur for select * from usinfo;
  return mycur;
 end;--函数结束

 procedure getuserp(ps number,mycur in out ctype)
 as--存储过程开始
 begin
  open mycur for select * from usinfo;
 end;--过程结束
end;--包结束
/

 

 

ceil向上取整
floor向下取整

 

create or replace procedure  fy1(pagesize number,y number,pagecount out number)
as
rowcount number;--总行
a number;
b number;
begin
select count(*) into rowcount from usinfo;
pagecount:=ceil(rowcount/pagesize);--向下取整得总页数
a:=(y*pagesize)+1;
b:=(y-1)*pagesize;
select * from (select rownum r,u.* from usinfo u where rownum<a) where r>b;
end;
/

 

select * from (select rownum r,u.* from usinfo u where rownum<21) where r>10;

21

(y*10)+1
(y-1)*10

create or replace package  fypac
as
type cur is ref cursor;
function fyfun(pagesize number,y number,pagecount out number) return cur;
end;
/

create or replace package body  fypac
as
function fyfun(pagesize number,y number,pagecount out number) return cur
as
mycur cur;
rowcount number;--总行
a number;
b number;
begin
select count(*) into rowcount from usinfo;
pagecount:=ceil(rowcount/pagesize);--向下取整得总页数
a:=(y*pagesize)+1;
b:=(y-1)*pagesize;
open mycur for select * from (select rownum r,u.* from usinfo u where rownum<a) where r>b;
return mycur;
end;
end;
/