用Mysql进行emp、dept、salgrade表的相关查询操作

初学者都会接触到三种表：emp、dept、salgrade表，进行练习各种语句操作再合适不过

但是，网上大多数的操作语句都是用oracle进行操作的，小编在学习mysql的时候，参考网上的书写遇到了不少问题

都是由于oracle语句和mysql语句的不兼容的引起的。

写多行sql语句的时候或者嵌套查询的时候，切记，分行与缩进，条理清晰

大家在学习的时候，注意看小编的书写格式和书写缩进，同一级的select、where尽量对其，子级的要缩进，避免干扰

简单的语句给出答案即可，复杂的语句，会给出书写思路的

一：单表查询

 1 -- 1)    试用sql语言完成下列查询(单表查询)：
 2 -- a)    查询20号部门的所有员工信息：
 3 select * from emp where deptno = 20;
 4 -- b)    查询奖金（comm）高于工资（sal）的员工信息：
 5 select * from emp where comm > sal;
 6 -- c)    查询奖金高于工资的20%的员工信息:
 7 select * from emp where comm > sal*0.2;
 8 -- d)    查询10号部门中工种为manager和20号部门中工种为clerk的员工的信息:
 9 select * from emp where (job = 'manager' and deptno = 10) or (job = 'clerk' and deptno = 20);
10 -- e)    查询所有工种不是manager和clerk，且工资大于或等于2000员工的详细信息：
11 select * from emp where (sal >= 2000) and (job not in('manager','clerk'));
12 -- f)    查询没有奖金或奖金低于100的员工信息:
13 select * from emp where (comm < 100 ) or comm is null;
14 -- g)    查询员工工龄大于或等于10年的员工信息:
15 select * from emp where (now() - hiredate) >= 10;
16 -- h)    查询员工信息，要求以首字母大写的方式显示所有员工的姓名:
17 select concat(upper(substring(ename,1,1)),lower(substring(ename,2,(char_length(ename)-1))))from emp;

（h）解析：用到的五个函数：

　　upper（）：将字符串大写处理

　　lower（）：将字符串小写处理

　　substring(ename， 1 ， 1 )：从一段字符串中截取字符串，例如：smith：

　　　　　　　substring（smith，1，1）：表示：从第一个字符开始，截取1个字符，结果就是：s

　　　　　　　substring（smith，2，（char_length('smith')-1））：表示：从第二个字符开始，截取字符长度-1个字符，即除去首字母剩下的字符，结果：mith:

　　char_length():获取指定字符串的的长度

　　concat(str1，str2);拼接两个字符串

-- i)    查询在2月份入职的所有员工信息:
select * from emp where hiredate like '%-02-%';
-- j)    显示所有员工的姓名、入职的年份和月份，按入职日期所在的月份排序，若月份相同则按入职的年份排序:
select ename,substr(hiredate from 1 for 7) from emp order by substr(hiredate from 6 for 2),substring(hiredate from 1 for 4);

二：多表查询

-- a)    查询从事同一种工作但不属于同一部门的员工信息:
select e.* from emp e
cross join emp m
on (e.job = m.job) and (e.deptno <> m.deptno);
-- b)    查询各个部门的详细信息以及部门人数、部门平均工资:
select d.*,count(e.ename),avg(e.sal) from emp e,dept d
where e.deptno = d.deptno
group by d.deptno,d.dname;

多表查询不是很难，不做过多的解释，不懂得，可以留言或者查看小编的前两篇随笔，有解释的。

三：嵌套子查询

-- a)    查询10号部门员工以及领导的信息:
-- 不相关子查询
select * from emp
where empno in(select empno from emp where deptno = 10);
-- b)    查询工资为某个部门平均工资的员工信息:
-- 不相关子查询
select * from emp
where sal in(select avg(sal) from emp  group by deptno);
-- c)    查询工资高于本部门平均工资的员工的信息: 
-- 相关子查询
select * from emp e
where sal > (select avg(sal) from emp where e.deptno = deptno group by deptno);
-- d)    查询工资高于本部门平均工资的员工的信息及其部门的平均工资:
-- 不相关子查询
select e.*,a.avgsal 
from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) a 
where e.sal > a.avgsal and e.deptno = a.deptno;

嵌套子查询，不是很难，如果有不懂得，看小编下一篇文章，这篇文章主要以语句练习为主，不做太多的知识讲解。

四：使用聚合函数进行查询（重点介绍）

 常见的五个聚合函数：sum()、count（）、max（）、min（）、avg（）。聚合函数通常与group  by 子句一起使用

 

-- a)    统计各个工种的人数与平均工资:
select job,count(ename),avg(sal) from emp group by job;

  -- b) 统计每个部门中各个工种的人数与平均工资:
select deptno,job,count(*),avg(sal) from emp group by deptno,job;

注：很简单，不做解释

 

-- c)    查询人数最多的部门信息：

有两种方法：

方法一：利用分页 语句

-- 1、查询出各部门的人数, 按人数进行降序排序
select deptno,count(*) count from emp group by deptno order by count desc ;
-- 2、分页查询，只显示第一行，即最大的人数部门
select deptno,count(*) count from emp group by deptno order by count desc  limit 1;
-- 3、将上面的查询结果当做一张表，联合部门表进行查询
select * from dept d where d.deptno =(
                select deptno  from emp group by deptno order by count(*) desc  limit 1);

方法二：较麻烦

where deptno in(
            select b.deptno from (select count(*) count,deptno from emp group by deptno) b
            where 
            b.count in (
                        select max(a.count)from (select count(*) count,deptno from emp group by deptno ) a));

-- d)    部门平均薪水最高的部门编号:

有两种方法：

方法一：利用分页查询：

-- 1、查询各部门的平均薪水,并进行降序排序
select deptno, avg(sal) avgsal from emp group by deptno order by avgsal desc ;
-- 2、分页查询，取出第一行数据，即最高的平均薪水部门编号
select deptno, avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1;
-- 3、经上面数据当做一张表，只显示部门编号
select deptno from emp group by deptno order by avg(sal) desc limit 1;

方法二：

select deptno from dept
where deptno in(
            select b.deptno from (select avg(sal) avgsal,deptno from emp group by deptno) b
            where
            b.avgsal in(
                         select max(a.avgsal)from (select avg(sal) avgsal,deptno from emp group by deptno) a));

-- e)    部门平均薪水最高的部门名称:

有两种方法：

方法一：利用分页

-- 1、查询各部门的平均薪水,并进行降序排序
select deptno, avg(sal) avgsal from emp group by deptno order by avgsal desc ;
-- 2、分页查询，取出第一行数据，即最高的平均薪水部门编号
select deptno, avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1;
-- 3、将上面的查询结果当做一张表，联合部门表，查询最终结果
select dname from dept d where d.deptno = (
            select deptno from emp group by deptno order by avg(sal) desc limit 1);

方法二：

select dname from dept
where
deptno in(
            select b.deptno from (select avg(sal) avgsal,deptno from emp group by deptno) b
            where
                b.avgsal in(
                        select max(a.avgsal)from (select avg(sal) avgsal,deptno from emp group by deptno) a));

-- f)    平均薪水最低的部门的部门名称:

有两种方法：

方法一：利用分页

-- 1、查询各部门的平均薪水,并进行升序排序
select deptno, avg(sal) avgsal from emp group by deptno order by avgsal ;
-- 2、分页查询，取出第一行数据，即最低的平均薪水部门编号
select deptno, avg(sal) avgsal from emp group by deptno order by avgsal limit 1;
-- 3、将上面的查询结果当做一张表，联合部门表，查询最终结果
select dname from dept d where d.deptno = (
            select deptno from emp group by deptno order by avg(sal)  limit 1);

方法二：

select dname from dept
where
deptno in(
            select b.deptno from (select avg(sal) avgsal,deptno from emp group by deptno) b
            where
            b.avgsal in(
                       select min(a.avgsal)from (select avg(sal) avgsal,deptno from emp group by deptno) a));

-- g)    平均薪水等级最低的部门的部门名称:

select d.dname from dept d
where 
d.deptno in
            (select a.deptno from (select e.deptno from emp e,salgrade s
                                   where (e.sal between s.losal and s.hisal)group by e.deptno order by avg(s.grade)) a) limit 1;

-- h)    部门经理人中，薪水最低的部门名称:

方法一：

-- 1、查询部门经理的薪水及其所属的部门编号
select deptno,job,min(sal) from emp where job = 'manager' group by deptno;
-- 2、分页查询，升序排列，只显示第一行数据，即工资最低的部门经理
select deptno from emp  where job = 'manager' group by deptno order by min(sal) limit 1 ;
-- 3、将上面结果当做一张表，联合部门表
select dname from dept d where d.deptno = (
                select deptno from emp  where job = 'manager' group by deptno order by min(sal) limit 1 );

方法二：

select dname from dept d where d.deptno in

(select deptno from emp where job='manager' group by deptno order by min(sal));

-- i)    比普通员工的最高薪水还要高的经理人名称:

-- 1、查询普通员工的最高薪水
select max(sal) from emp where job not in('manager','president');
-- 2、查询
select ename,sal from emp 
    where  sal >(select max(sal) from emp where job not in('manager','president'))
        and job = 'manager' or job = 'president';

五：嵌套子查询

-- a)    查询所有【员工工资都大于1000】的部门的信息:
            -- 1、查询员工工资小于1000的员工编号,及其部门编号
            select deptno from emp where sal < 1000 group by deptno;
            -- 2、将上面的查询结果当做一张表，查询部门编号不在里面的部门编号
            select d.* from dept d 
            where d. deptno not in(select deptno from emp where sal < 1000 group by deptno);

-- b)    查询所有员工工资都大于1000的部门的信息及其员工信息:
        -- 1、查询员工工资小于1000的员工编号,及其部门编号
        select deptno from emp where sal < 1000 group by deptno;
        -- 2、联合查询，联合部门表
        select * from emp e 
        join dept d
        on e.deptno = d.deptno 
        where e.deptno not in(select deptno from emp where sal < 1000 group by deptno);

-- c)    查询所有员工工资都在900~3000之间的部门的信息: 
        -- 1、查询员工工资都在900-3000的部门编号
        select deptno,max(sal),min(sal) from emp group by deptno having max(sal) <= 3000 and min(sal) >=900;
        -- 2、根据上面查询的部门编号，查询出部门信息
        select * from dept d where d.deptno in(
                    select deptno from emp group by deptno having max(sal) <= 3000 and min(sal) >=900); 

-- d)    查询所有工资都在900~3000之间的员工所在部门的员工信息:
        select * from emp e
        where e.deptno = (select deptno from emp group by deptno having max(sal) <= 3000 and min(sal) >=900);

-- e)    查询每个员工的领导所在部门的信息：
        -- 1、查询每个员工的领导，及其部门编号
        select distinct e2.deptno from emp e1,emp e2 where e1.mgr = e2.empno;
        -- 2、根据部门编号，查出部门信息
        select * from dept 
        where deptno in(select distinct e2.deptno from emp e1,emp e2 where e1.mgr = e2.empno);

-- f)    查询30号部门中工资排序前3名的员工信息:
        -- 1、查询30号部门的员工工资
        select * from emp where deptno = 30 order by sal desc
        -- 2、分页查询，查出前三名即可
        select  e.* from (select * from emp where deptno = 30 order by sal desc ) e limit 3;

-- g)    查询工资等级为2级，1985年以后入职的工作地点为dallas的员工编号、姓名和工资：
select e.empno,e.ename,e.sal,e.hiredate
from emp e,dept d,salgrade s
where (e.sal between  losal and hisal) and s.grade = 2
and e.hiredate>'1985' 
and d.loc = 'dallas';

-- 6)    用sql语句完成下列操作：
-- a)    将各部门员工的工资修改为该员工所在部门平均工资加1000:
update emp b
set sal=(select sal from (select deptno,avg(sal)+1000 sal from emp group by deptno) a 
where a.deptno=b.deptno)
select * from emp;
-- b)    更新员工工资,奖金为他的主管的工资,奖金:
update emp e set (sal,comm)=(select sal,comm from emp where empno=mgr);
select * from emp