Oracle-多表查询

select *from emp;
select *from emp where ename=upper('SMITH');

select *from dept;
---  56 =14*4  笛卡尔集现象  
select *from emp ,dept 

-- 避免笛卡尔集现象  使用 where 子句过滤
select *from emp ,dept  where emp.deptno=dept.deptno  order by  sal

---查询 员工 名  入职日期  部门编号  部门名称   等值判定   内连接   必须返回 两个表都满足条件的数据
select ename ,hiredate ,emp.deptno, dname from emp  ,dept where emp.deptno=dept.deptno

--表的别名不能使用 as 
select ename ,hiredate ,e.deptno, d.deptno, dname from emp    e ,dept d where e.deptno<>d.deptno

---------------- 外连接 1   可以返回 不满足条件的数据 （主表的数据全部返回  从表如果唯有匹配  补null）
---(+) 表示自己是从表    对面是主表
select *from emp ,dept where emp.deptno(+)=dept.deptno

select *from emp ,dept where emp.deptno=dept.deptno(+)

---------------- 外连接 2   sql1999 
select *from emp  cross  join   dept  --- 笛卡尔积    --- 16*4= 64

--- 自然连接   等价于  等值连接   等价于 内连接 
select  *from emp natural join dept  --- 

---using   
select *from emp  inner join  dept using(deptno) -- 内连接 
---等价 
select *from emp   join  dept using(deptno)
--- using (两张表的连接条件) 外连接  
select  *from emp  left  join  dept using(deptno)

select  *from emp  right join  dept using(deptno)

select  *from emp  full join  dept using(deptno)

---on 内连接     等价 自然连接  等值连接
select  *from emp  inner   join  dept on  emp.deptno=dept.deptno
-----    on   外连接
select  *from emp  left  join  dept on  emp.deptno=dept.deptno
select  *from emp  right  join  dept on  emp.deptno=dept.deptno
select  *from emp  full  join  dept on  emp.deptno=dept.deptno

---- 自连接    自己连接自己  一张表
select e1.*,e2.* from emp e1,emp  e2 

---找自己的领导  

select *from emp  e1 ,emp e2 where e1.mgr=e2.empno

-- 返回所有员工 找自己的领导   即使没有管理者      外连接  

select *from emp  e1 ,emp e2 where e1.mgr=e2.empno    

select *from emp  e1  left join    emp e2 on e1.mgr=e2.empno

select *from emp  e1 ,emp e2 where e1.mgr=e2.empno(+)  

---------------------- 组函数---------

select min(sal),max(sal),avg(sal),sum(sal)/count(*) from emp;

select  sum(comm) from emp; --- 空值不参与组函数运算

select  avg(comm) from emp;--- 空值不参与组函数运算

---函数嵌套使用  
select   ceil( avg(sal)) from emp

---count --计数
select count(comm)  from emp;

--- * 表示有多少条数据
select count(*)  from emp;
select count(empno)  from emp;

--- 有多少人有管理者
select count(mgr) from emp;

---统计一下员工有多少个部门 ------  
select count(distinct  deptno) from emp
--- group  by  条件  ----分组条件                  
----部门编号进行分组   组函数 会返回  空 组  
select    deptno     from emp group by  deptno  

--- 根据多个条件分组   
select    deptno ,sal ,count(*)  from emp group by  deptno , sal 

----  计算每个部门的工资和
select    deptno  ,sum(sal) ,min(sal),max(sal),avg(sal) ,count(*)  from emp group by  deptno 

select *from emp

-----  不是10号部门    部门编号      和部门人数     按照部门编号排序

select deptno  , count(*)  from emp where deptno<>10  group by deptno  order by deptno asc

select deptno  , count(*) e  from emp where deptno<>10  group by deptno  order by e desc

----  先过滤   后分组  
select deptno  , count(*) e  from emp where count(*)>5     group by deptno 

select *from    (select deptno  , count(*) e  from emp      group by deptno )  tab where   tab.e>=5

-----  需要使用组函数作为 过滤条件       having 子句
-----  不是10号部门    部门编号      和部门人数     按照部门编号排序  部门人数 必须大于5
select deptno  , count(*) e  from emp where deptno<>10  group by deptno  having count(*)>5    order by e desc

---- where having  都是过滤子句   结果集还没有返回  不能使用结果集的别名
select deptno  , count(*) e  from emp where deptno<>10  group by deptno  having count(*)>5    order by e desc

--- 查询每个部门的人数，部门 人数的工资和  工资和大于9000 的部门编号
select  count(*) ,sum(sal),emp.deptno,dname     from emp ,dept where emp.deptno=dept.deptno group by emp.deptno,dname 
   having sum(sal)>9000 

--查询 员工表中的每个部门 相同职位的人数  当前职位的工资和  工资和大于5000 的职位和部门编号

select  deptno,job ,sum(sal+nvl(comm,0)),count(*) from emp group by deptno,job   having sum(sal+nvl(comm,0))>5000 

----- 子查询   把一个查询结果集  当做另外一个要查询的对象（表） 
select  count(deptno) from (   select  distinct  deptno from emp)

--比强哥工资高的人
--强哥工资
select sal from emp where ename='强哥'

select  *from emp where sal>(select sal from emp where ename='强哥')

--查询  比KING 晚的人 
select hiredate from emp where ename='KING'

select *from emp where hiredate>(select hiredate from emp where ename='KING')

--------------------   in  any all
--in 等值判定 

select  *from emp where  sal  in (1600,5000)

select  *from emp where  sal  =1600 or sal=5000

select  *from emp where  sal  in (select sal  from emp where deptno=10)

---any      大于 任意一个值
select *from emp where   sal>=any (1600,5000)

--all   大于所有的值
select *from emp where   sal>=all (1600,5000)
 ------查询比10 号部门任意员工工 资高的员工信息
select  *from emp where  sal >any (select sal  from emp where deptno=10)

  --------查询与10号部门同一年入职的员工  但是不要 10号部门 

select  *from emp where  to_char(hiredate,'yyyy') in  (select  to_char(hiredate,'yyyy')  from emp where deptno=10) and  deptno!=10

---子查询  +分组  
---查询员工信息  每个部门 下最低工资的员工信息
--- 1每个部门 下最低工资
--- 2 sal=1
-------select  *from emp where sal in (  select min(sal) from emp group by deptno)  错误
---- 判定  最低工资和部门得 一致

select *from  emp ,  ( select min(sal) ms,deptno from emp group by deptno ) e1   where  e1.deptno=emp.deptno and  e1.ms=emp.sal

---with 子句    
with 
    res as (select min(sal) ms,deptno from emp group by deptno) ----声明一张 瞬时表 临时表
    
    select *from  emp ,res where      res.deptno=emp.deptno and  res.ms=emp.sal  and  res.ms>900
    
    
    
with  
    copyEmp as (select *from emp)
    
    select *from copyEmp;
    
    
 with  
    copyEmp as (select *from emp)
    
    select *from emp ,copyEmp where emp.mgr=copyEmp.empno  ;
    
    
    
------ 分页 语句  ----------
----每页显示几条    总条数   
----------------------   5 条
                      1,5                1+(1-1)*5    , 1*5       
                      6,10               1+(2-1)*5  , 2*5
                      11,15              1+(3-1)*5  , 3*5
oracle -----  分页   最简单的分页   limit  1,4

？  从指定位置 ---指定位置
rowid ----当前查询结果集的  每一个对象的唯一标识   

select emp.*,rowid from emp
rownum --- -当前查询结果集 中   每一行的行号  始终从1开始  到 count(*) 结束
select emp.*,rownum from emp

select emp.*,rownum from emp where rownum<=5
---rownum  > >= =  返回均是空值    <  <= 
select emp.*,rownum   from emp where rownum<=10 and rownum>5

select emp.*,rownum from emp where rownum=4
---oracle 分页  必须使用子查询 rownum 起别名

select * from 
  (select emp.*,rownum r from emp ) 
  emp2 where emp2.r>=5  and  emp2.r<10

优化：
select * from  
  (select emp.*,rownum r from emp where rownum<10 ) 
  emp2 where emp2.r>=5   
  
 排序   工资排序     先生成rownum    后排序的

  select  emp.*,rownum r from emp  order by sal  
 
  -- 先 排序  后 rownum
  
 select * from   
 (select  e1.*,rownum r from 
  (select * from emp order by sal) e1 where rownum<=10) e2
  where e2.r>5
  
  
  

--- 效率要 高很多  推荐使用 
select *from   (select emp.*,rownum  r  from emp where rownum<=10  ) e where  e.r>5

--- 效率低   不建议  使用 
select *from   (select emp.*,rownum  r  from emp   ) e where   e.r>5 and e.r<=10

select *from   (select emp.*,rownum  r  from emp   ) e where e.r  between   6 and  10
 
---------------------------排序了    先生成  rownum  再进行排序的   没有意义
select emp.*,rownum  r  from emp  order by sal  

--先排序  后生成 rownum   
select *from   (  select  e1.*,rownum r  from  ( select *  from emp  order by sal  )   e1  ) e where   e.r>5 and e.r<=10

select *from   (  select  e1.*,rownum r  from  ( select *  from emp  order by sal  )   e1  ) e where   e.r=4