SQL查询总结(基于MySQL 5.0.22)

/*算出员工的平均工资*/
    select e.*,avg(e.salary) salarysum from employee e group by e.departmentNo;

    /*根据部门编号分组，列出部门名称，部门编号，部门人数，部门内最大薪水，部门内最小薪水，部门内薪水差，部门内平均工资*/
    select d.departmentName,e.departmentNo,count(e.employeeId) employeeNum,max(e.salary) maxsalary,min(e.salary) minsalary,max(e.salary)-min(e.salary) salarydiff,avg(e.salary) avgsalary from department d,employee e where e.departmentNo=d.departmentNo group by e.departmentNo;
    /*列出员工编号，员工姓名，员工薪水，公司平均工资，员工工资与平均工资差别，评语*/
    select e.employeeNo,e.employeeName,e.salary,(select avg(salary) from employee) comavgsalary,e.salary-(select avg(salary) from employee) salarydiff,
    case
      when abs(e.salary-(select avg(salary) from employee)) >300 then 'big diff'
      when abs(e.salary-(select avg(salary) from employee)) >=100 then 'a little diff'
      when abs(e.salary-(select avg(salary) from employee)) <100 then 'almost ignore'
    end
     classify
     from employee e

    /*查询出员工的薪水，将其与部门的平均薪水、公司的平均薪水作比较并作出评语。*/
    select e.salary,
    (select avg(salary) from employee where departmentNo = e.departmentNo) deptavg,
    (select avg(salary) from employee) comavg,
    e.salary-(select avg(salary) from employee where departmentNo = e.departmentNo) deptdiff,
    e.salary-(select avg(salary) from employee) companydiff,
    case
      when abs(e.salary-(select avg(salary) from employee where departmentNo = e.departmentNo)) >300 then 'big diff'
      when abs(e.salary-(select avg(salary) from employee where departmentNo = e.departmentNo)) >=100 then 'a little diff'
      when abs(e.salary-(select avg(salary) from employee where departmentNo = e.departmentNo)) <100 then 'almost ignore'
    end comparewithdept,
    case
      when abs(e.salary-(select avg(salary) from employee)) >300 then 'big diff'
      when abs(e.salary-(select avg(salary) from employee)) >=100 then 'a little diff'
      when abs(e.salary-(select avg(salary) from employee)) <100 then 'almost ignore'
    end comparewithcom
     from employee e

    /*员工编号，员工名称，部门编号，部门平均工资，部门最大工资，部门最小工资，部门内工资差*/
    select e.employeeNo,e.employeeName,e.departmentNo,e.salary,(select avg(salary) from employee where departmentNo = e.departmentNo) deptavg,(select max(salary) from employee where departmentNo = e.departmentNo) maxsalary,
    (select min(salary) from employee where departmentNo = e.departmentNo) minsalary,(select max(salary)-min(salary) from employee where departmentNo = e.departmentNo) diffwithmaxandmin from employee e order by diffwithmaxandmin desc;

    /*假如有个好心的老板要消除两极分化，对大于平均工资的乘以0.95，小于平均工资的乘以1.05，等于的不调整。*/
    SELECT e.employeeNo,e.employeeName,e.salary,
    case
      when e.salary>(select avg(salary) from employee) then e.salary*0.95
      when e.salary=(select avg(salary) from employee) then e.salary
      when e.salary<(select avg(salary) from employee) then e.salary*1.05
    end adjustsalary
     FROM employee e;

    /*查询部门编号最大部门的员工信息*/
    select e.employeeNo,e.employeeName,e.departmentNo,e.salary
     FROM employee e where e.departmentNo in(select max(departmentNo) from department);

    /*剔除部门编号最大和最小的员工信息*/
    SELECT e.employeeNo,e.employeeName,e.departmentNo,e.salary
     FROM employee e where e.departmentNo not in ((select max(departmentNo) from department),(select min(departmentNo) from department));

    /*列出和平均工资相差超过300的员工信息*/
    select e.employeeNo,e.employeeName,e.departmentNo,e.salary
     FROM employee e where abs(e.salary-(select avg(salary) from employee))>300

    /*列出平均工资大于4500的分组*/
    select e.employeeNo,e.employeeName,e.departmentNo,avg(e.salary) avgsalary
     FROM employee e group by e.departmentNo having avg(e.salary)>4500

    /*按多字段分组*/
    select e.employeeNo,e.employeeName,e.departmentNo,avg(e.salary) avgsalary
     FROM employee e group by e.departmentNo,e.employeeNo

    /*聚合统计，cube生成的结果显示了所选列中值的所有组合的聚合中，rollup生成的结果集显示了所选列中值的某个层次结构的聚合*/
    select e.employeeNo,e.employeeName,e.departmentNo,avg(e.salary) avgsalary
     FROM employee e where e.salary>4500 group by e.departmentNo with rollup

    /*分组查询的排序，求出每个部门内的最大、最小工资及极差。按照极差的倒序排序*/
    select e.departmentNo,max(e.salary) maxsalary,min(e.salary) minsalary,(max(e.salary)-min(e.salary)) diff
     FROM employee e group by e.departmentNo order by diff desc

    /*compute by MySQL不支持该语法*/
    select e.employeeNo,e.employeeName,e.salary from employee e order by e.salary compute avg(e.salary),max(e.salary),min(e.salary) by e.salary

    /*区间查询，在between之前加一个not 可以反向选择*/
    select * from employee where salary between (select min(salary) from employee where departmentNo = '1474') and (select MAX(salary) from employee where departmentNo='1915')

    /*多表嵌套子查询*/
    select * from employee where salary > (select avg(salary) from employee where departmentNo in (select departmentNo from department where departmentNo like '%18%'))

    /*查询人数最多的部门名称，用了虚拟表查询。觉得太复杂了。可以简化一下就好了*/
    select departmentName from department where departmentNo = (select departmentNo from (select departmentNo,count(*) employeeNum from employee group by departmentNo)temp where employeeNum = (select max(employeeNum) from (select departmentNo,count(*) employeeNum from employee group by departmentNo)temp))

    /*判断是否存在薪水在4000-4200之间的员工。返回的结果不重要*/
    select * from employee where exists (select * from employee where salary between 4000 and 4200)

    /*人话说一遍，列出薪水比部门编号为1296中任一员工高的信息。等价于列出比部门编号为1296最低工资高的员工信息,等价于 select * from employee where salary > (select min(salary) from employee where departmentNo ='1296');*/
    select * from employee where salary > any(select salary from employee where departmentNo='1296');

    /*改一下关键字any -> all 就等价于比部门编号为1296最高工资高的员工信息了。原意是比部门编号为1296所有员工工资都要高的员工信息。*/
    select * from employee where salary > all(select salary from employee where departmentNo='1296')

    /*列出大于所有部门的平均工资的员工信息。省了去找最高部门平均工资的步骤。换成any好像就没什么意义了*/
    select * from employee where salary > all(select avg(salary) from employee group by departmentNo)

    /*列出各个部门最高工资员工的信息*/
    select e.* from employee e where e.salary in (select max(salary) from employee where departmentNo = e.departmentNo )