Oracle高级查询

分组Group by 与Having

--统计每个部门的员工个数

select * from emp;
select deptno, count(1),job from emp group by deptno,job;


--统计每个部门的员工人数至少是5个以上的部门
select deptno,count(1) from emp group by deptno having count(1)>5;


--根据不同的部门排名
--分析函数
 --（1）ROW_NUMBER 返回连续的排位，不论值是否相等
 --【语法】ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) 
 --【功能】表示根据COL1分组，在分组内部根据 COL2排序，而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的） 
  select deptno,ename,sal,row_number() OVER (partition by deptno order by sal desc) aa from emp;
  
 
--（2）RANK 具有相等值的行排位相同，序数随后跳跃
--【语法】RANK ( ) OVER ( [query_partition_clause] order_by_clause )
 select deptno,ename,sal,rank() OVER (partition by deptno order by sal desc) aa from emp;


--（3）DENSE_RANK 具有相等值的行排位相同，序号是连续的
--【语法】dense_RANK ( ) OVER ( [query_partition_clause] order_by_clause )

   --根据每个部门工资降序排序的名次

  select deptno,ename,sal,dense_rank() OVER (partition by deptno order by sal desc) aa from emp;

  --根据员工表中的工资降序排序的名次
  select deptno,ename,sal,dense_rank() OVER (order by sal desc) aa from emp;
  
  
 -- 子查询
  --查询与“SCOTT”在同一个部门的雇员信息
  
   --(1)查询“SCOTT”用户的部门编号
  select deptno from emp where ename='SCOTT'
  
  --(2)查询20的部门的雇员信息
  select * from emp where deptno=20;
  
  --  = (只能一个值)  /in(单个/多个)   发生错误?
   select * from emp where deptno in (select deptno from emp where ename='SCOTT');
  
  select * from emp;
  
  --查询与“SCOTT”不在同一个部门的雇员信息  not in
    select * from emp where deptno not in(select deptno from emp where ename='SCOTT');
  
  
  --查询工资在1500-3000的员工信息部门是10与40 
  select * from emp where sal>=1500 and sal<=3000
  
  --部门是10与40 
  select * from ( select * from emp where sal>=1500 and sal<=3000) e1
  where e1.deptno in (10,40);
  
  
  --EMP表中，查询雇员薪水大于3000的部门名称
  
  --薪水大于3000的部门编号
  select * from emp where sal>3000;
  
  --薪水大于3000的部门名称
  select dname from  dept where deptno  in(select deptno from emp where sal>3000)
   select dname from  dept where deptno not in(select deptno from emp where sal>3000)


  --exists  存在  where /
   select dname from  dept d where not exists (select * from emp where sal>3000 and emp.deptno=d.deptno)
  
    select dname from  dept d where  exists (select * from emp where sal>3000)
    
    
    
 连接查询
 --内连接
   --(1) select * from  表1 inner join 表2 on  ...
   
   select * from emp;   --18
   select * from dept;  --4
   
   --查询雇佣所在的部门名称
   select empno,ename,job,sal,dept.dname from emp inner join dept on  emp.deptno =dept.deptno
   
   --(2) select * from 表1，表2 where.....
    select empno,ename,job,sal,dept.dname from emp, dept where  emp.deptno =dept.deptno
  
   --表名能否交换位置？？会影响查询结果   (没有影响，可以交换!)
     select empno,ename,job,sal,dept.dname from dept inner join emp on  emp.deptno =dept.deptno
     
     --不等值=18*4-18
      select empno,ename,job,sal,dept.dname from dept inner join emp on  emp.deptno !=dept.deptno
 
 select * from emp;
 select * from dept;

insert into dept values(50,'IT','HZ');

   --左外连接  
      --以左表为基准 ，如果没有匹配的，显示为null
   --select * from  表1 left join 表2 on  ...
select empno,ename,job,sal,dept.dname from emp left join dept on  emp.deptno =dept.deptno   --18
 --两张能否位置  （不能）
select empno,ename,job,sal,dept.dname from dept left join emp on  emp.deptno =dept.deptno   --4?5 19
  
 --右外连接  
      --以右表为基准 ，如果没有匹配的，显示为null
   --select * from  表1 left join 表2 on  ...
select empno,ename,job,sal,dept.dname from emp right join dept on  emp.deptno =dept.deptno   --19
 --两张能否位置  （不能）
select empno,ename,job,sal,dept.dname from dept right join emp on  emp.deptno =dept.deptno   --18




联合查询：
 select * from emp;  --18
 select * from emp2;  --3
 
 --删除多余的列
 alter table emp2 drop column dept;
 
 --  union   (过滤重复的数据)
select * from emp  
union
select * from emp2  


 --  union all (不过滤)
select * from emp  
union all
select * from emp2 


 -- intersect  (交集)
select * from emp  
intersect
select * from emp2 


 
 --  minus (补集) 18-2=16
select * from emp  
minus
select * from emp2