表查询常用命令高级查询方法(二)

----------------------------------------------------分组查询 group by    having
 
 --平均工资和最高工资
   select avg(sal)平均工资,max(sal)  from emp ;
 
 --每个部门的平均工资和最高工资
   --分组的条件一定要先查询出来
 
  select deptno, avg(sal),max(sal) from emp group by deptno;
--每个岗位的平均工资和最低工资
    select job 岗位, avg(sal) 平均工资,min(sal) 最低工资,max(sal) 最高工资 from  emp  group by job;

--显示平均工资小于2000的部门和它的平均数
 ---  分组之前的条件是where  ,分组之后的条件是having

select avg(sal) ,deptno from emp group by deptno having avg(sal)<2000;
   
    
  
------------------------------------------------多表查询----------------------------
--查询部门表
select * from dept;

--显示员工名,员工工资,及所在部门的名字
  --笛卡尔集 ,两个表的关联条件
select e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno;

      
--查询部门号为10的部门名,员工名,工资

  select d.dname,e.ename,e.sal from emp e,dept d where e.deptno=10;

--显示员工资,员工名字,工资级别 scott下的SALGRADE表中的grade

    select e.sal ,e.ename,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;
 
2014,09,22

-------------------------------------内连接
----查询SMITH的上司
   select mgr,ename from emp where ename ='SMITH';
  
---查询与部门10工作相同的员工的信息
select ename ,deptno from emp where job in(select  job from emp where deptno =10);

--查找比部门30工资高的所有员工的信息

  
    
   select ename ,sal ,deptno from emp where sal> (select max(sal) from emp where deptno=30);
        ---使用all与结果集比较
 select ename ,sal ,deptno from emp where sal> all (select sal  from emp where deptno=30);

--查找比部门30任意的工资高的所有员工的信息   
  ----使用any或者
  select ename ,sal ,deptno from emp where sal> any (select sal  from emp where deptno=30);

      
      
      
      
      
------------------------------------------伪列查询  rownum  rowid
---查询部分数据
  -- 查询第五到第十条的数据
 
 select ROWNUM ename ,job from (select ROWNUM r ,ename,job from emp where ROWNUM<=10) where r>5;
 
  -----MYsql + sqlite分页查询的方法
   
     -- select * from emp limit 1,10;
   
 
 
 
 
------------------合并查询操作符 union ,unuion all,intersect,minus

union ,union all合并表的查询

select * from emp where job ='SALESMAN';
select * from emp where sal>1500;

union的合并查询  合并并且去除重复的行 
select * from emp where job ='SALESMAN'union select * from emp where sal>1500;

union all的合并查询  取所有的数据
  select * from emp where job ='SALESMAN'union all select * from emp where sal>1500;

 

intersect取交集查询
select * from emp where job ='SALESMAN' intersect select * from emp where sal>1500;

 

minus查询出来的结果是只显示在第一个结果集中存在 而不在第二个结果集重负存在的
select * from emp where job ='SALESMAN' minus select * from emp where sal>1500;