MySQL-实操：部门、员工信息与管理

创建部门dept、员工emp数据表，并插入数据

部门dept数据表

1.创建部门信息：

CREATE TABLE dept(
    deptno        INT,
    dname        VARCHAR(14),
    loc        VARCHAR(13)
);

2.插入部门信息：

INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'),(20, 'RESEARCH', 'DALLAS'),(30, 'SALES', 'CHICAGO'),(40, 'OPERATIONS', 'BOSTON');

3.查看确定：

SELECT * FROM dept;

员工emp数据表

1.创建员工信息：

CREATE TABLE emp(
    empno INT,
    ename VARCHAR(50),
    job VARCHAR(50),
    mgr    INT,
    hiredate DATE,
    sal    DECIMAL(7,2),
    comm DECIMAL(7,2),
    deptno INT
) ;

2.插入员工信息：

INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20),(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30),(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30),(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20),(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30),(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30),(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10),(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20),(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10),(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30),(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20),(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30),(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20),(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10),(7981,'MILLER','CLERK',7788,'1992-01-23',2600,500,20);

3.查看确定：

SELECT * FROM emp;

实操1：基本查询

--所有员工的信息
--薪资大于等于1000并且小于等于2000的员工信息
--从员工表中查询出所有的部门编号
--查询出名字以A开头的员工的信息
--查询出名字第二个字母是L的员工信息
--查询出没有奖金的员工信息
--所有员工的平均工资
--所有员工的工资总和
--所有员工的数量
--最高工资
--最少工资
--最高工资的员工信息
--最低工资的员工信息

1.所有员工的信息：SELECT * FROM emp;

2.薪资大于等于1000并且小于等于2000的员工信息：

SELECT empno AS 员工编号,ename AS 姓名,job AS 工作,mgr AS 上级编号,hiredate AS 出生日期,sal AS 薪资（1000元至2000元）,comm AS 佣金,deptno AS 部门 FROM emp WHERE sal>=1000 AND sal<=2000;

3.从员工表中查询出所有的部门编号：

SELECT DISTINCT deptno FROM emp;

4.查询出名字以A开头的员工的信息：

SELECT empno AS 员工编号,ename AS 姓名（以A开头）,job AS 工作 FROM emp WHERE ename LIKE "A%";

5.查询出名字第二个字母是L的员工信息

SELECT empno AS 员工编号,ename AS 姓名（第二个字母是L）,job AS 工作 FROM emp WHERE ename LIKE "_L%";

6.查询出没有奖金的员工信息：

SELECT empno AS 员工编号,ename AS 姓名（没有奖金）,job AS 工作 FROM emp WHERE comm=" " OR comm IS NULL;

7.所有员工的平均工资：

SELECT AVG(sal) AS 平均工资 FROM emp;

8.所有员工的工资总和：

SELECT SUM(sal) AS 工资总和 FROM emp;

9.所有员工的数量：

SELECT COUNT(ename) AS 员工数量 FROM emp;

10.最高工资：

SELECT MAX(sal) AS 最高工资 FROM emp;

11.最少工资：

SELECT MIN(sal) AS 最高工资 FROM emp;

12.最高工资的员工信息：

SELECT * FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);

13.最低工资的员工信息：

SELECT * FROM emp WHERE sal=(SELECT MIN(sal) FROM emp);

实操2：分组查询

--每个部门的平均工资

SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;

group by关键字，详见“（数据库-MySQL）表-数据查询”的“其他查询方式：”-“7、分组”。

实操3：子查询

-- 单行子查询(> < >= <= = <>)
    -- 查询出高于10号部门的平均工资的员工信息
-- 多行子查询(in  not in any all)    >any  >all
    -- 查询出比10号部门任何员工薪资高的员工信息
-- 多列子查询(实际使用较少)   in
    -- 和10号部门同名同工作的员工信息
-- Select接子查询
    -- 获取员工的名字和部门的名字
-- from后面接子查询
    -- 查询emp表中经理信息
-- where 接子查询
    -- 薪资高于10号部门平均工资的所有员工信息
-- having后面接子查询
    -- 有哪些部门的平均工资高于30号部门的平均工资
-- 工资>JONES工资
-- 查询与SCOTT同一个部门的员工
-- 工资高于30号部门所有人的员工信息
-- 查询工作和工资与MARTIN完全相同的员工信息
-- 有两个以上直接下属的员工信息
-- 查询员工编号为7788的员工名称,员工工资,部门名称,部门地址

1.单行子查询(> < >= <= = <>)
    -- 查询出高于10号部门的平均工资的员工信息

第一步：确定“10号部门的平均工资”

SELECT AVG(sal) FROM emp WHERE deptno=10;

第二步：查询高于上面结果（10号部门的平均工资）员工的信息

SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp WHERE deptno=10);

2.多行子查询(in  not in any all)    >any  >all
    -- 查询出比10号部门任何员工薪资高的员工信息

常规实现也是两步

第一步：既然是比高，那就先找出10号部门的最高薪资

SELECT MAX(sal) FROM emp WHERE deptno = 10;

第二步：薪资对比，得出结果

SELECT * FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 10);

    -- 查询出比10号部门任意一个员工薪资高的所有员工信息 : 只要比其中随便一个工资都可以。

SELECT * FROM emp WHERE sal >ANY(SELECT sal FROM emp WHERE deptno = 10) AND deptno != 10;

3.多列子查询(实际使用较少)   in
    -- 和10号部门同名同工作的员工信息

第一步：查询出10号部门所有人的名字和工作

第二步：名字和工作对比，得出结果（使用in）

SELECT * FROM emp WHERE (ename,job) IN (SELECT ename,job FROM emp WHERE deptno=10) AND deptno !=10;

4.Select后面接子查询
    -- 获取员工的名字和部门的名字

SELECT e.empno 员工编号,e.ename 姓名,(SELECT d.dname FROM dept d WHERE e.deptno=d.deptno) 部门,e.deptno 部门编号 FROM emp e;

5.from后面接子查询
    -- 查询emp表中经理信息

首先，我们一起来分析emp表中所有信息，谁是谁的领导，怎么判断呢？   不难看出，mgr是每个员工的上级编码，该编码是否与员工编码empno列中值有对应呢？  除了董事长president的上级编码为NULL，其他都有，那么，我们可以先使用“DISTINCT”对mgr进行筛选，得出所有领导层的编码；董事长president的上级编码为NULL，就不用考虑啦！

第一步：SELECT DISTINCT mgr FROM emp;

第二步：SELECT * FROM emp e,(SELECT DISTINCT mgr FROM emp) mgrtable WHERE e.empno = mgrtable.mgr;

上下对比，mgr数据在下表查询的empno数据中都有对应值；而“(SELECT DISTINCT mgr FROM emp) mgrtable”将查询得到的数据以mgrtable表的形式出现，更容易被调用！这样，子查询出现在from后面是允许的，一定要有括号“（）”括起来！

6.where 接子查询
    -- 薪资高于10号部门平均工资的所有员工信息

之前“多行子查询”有使用过查询，自己慢慢分析：SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno=10);

7.having后面接子查询
    -- 有哪些部门的平均工资高于30号部门的平均工资

传统的写法，很长的代码：

SELECT allavgsal.deptno,allavgsal.avgsal FROM (SELECT deptno,AVG(sal) avgsal FROM emp GROUP BY deptno) allavgsal,(SELECT AVG(sal) avgsal FROM emp WHERE deptno=30) tavgsal WHERE allavgsal.avgsal>tavgsal.avgsal;

如果使用“having”，我们3步可以完成：

第一步：统计所有的部门的平均工资

SELECT deptno, AVG(sal) FROM emp GROUP BY deptno;

第二步：确定30号部门的平均工资

SELECT AVG(sal) FROM emp WHERE deptno=30;

第三步：使用GROUP BY分组之后结合“having”判断，得出结果

SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > (SELECT AVG(sal) FROM emp WHERE deptno=30);

此时，我们可以如此理解： having单独使用，与where类似。（此处，由于使用了GROUP BY分组，生成新的数据组合，where只能使用传统的方式实现）

例如，我们如果想实现，查询所有薪资高于30号部门的平均工资的员工信息

SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp WHERE deptno=30);

SELECT * FROM emp HAVING sal>(SELECT AVG(sal) FROM emp WHERE deptno=30);   （换having）

前后对比，可以看出，显示效果一样！

8.工资>JONES工资（所有员工的信息）

SELECT * FROM emp HAVING sal>(SELECT sal FROM emp WHERE ename="JONES");

9.查询与SCOTT同一个部门的员工

SELECT * FROM emp HAVING deptno=(SELECT deptno FROM emp WHERE ename="SCOTT");

10.工资高于30号部门所有人的员工信息

SELECT * FROM emp HAVING sal>(SELECT MAX(sal) FROM emp WHERE deptno=30);

11.查询工作和工资与MARTIN完全相同的员工信息

SELECT * FROM emp HAVING (sal,job) IN (SELECT sal,job FROM emp WHERE ename="MARTIN");

12.有两个以上直接下属的员工信息

第一步：查出emp表中mgr信息

SELECT mgr FROM emp;

第二步：分组统计mgr的信息

SELECT mgr,COUNT(*) FROM emp GROUP BY mgr HAVING COUNT(*)>2;

第三步：使用“in”遍历查询，得出结果

SELECT * FROM emp e1 WHERE e1.empno IN (SELECT e2.mgr FROM emp e2 GROUP BY e2.mgr HAVING COUNT(*)>2);

13.查询员工编号为7788的员工名称,员工工资,部门名称,部门地址

较为容易理解的：SELECT e2.ename,e2.sal,d.dname,d.loc FROM emp e2,dept d WHERE d.deptno=(SELECT e1.deptno FROM emp e1 WHERE e1.empno=7788) AND e2.empno=7788;

简洁的写法：SELECT ename,sal ,dname, loc FROM emp ,dept WHERE emp.deptno = dept.deptno AND empno=7788;

综合查询

-- 查询出高于本部门平均工资的员工信息

-- 列出达拉斯加工作的人中,比纽约平均工资高的人

-- 查询7369员工编号,姓名,经理编号和经理姓名

-- 查询出各个部门薪水最高的员工所有信息

1.查询出高于本部门平均工资的员工信息

SELECT * FROM emp e1 WHERE e1.sal > (SELECT AVG(e2.sal) FROM emp e2 WHERE e1.deptno=e2.deptno  GROUP BY e2.deptno);

附带部门平均工资：

SELECT e1.*,deptavgsal.avgsal 部门平均工资 FROM emp e1,(SELECT deptno,AVG(sal) avgsal FROM emp e2 GROUP BY e2.deptno) deptavgsal WHERE e1.deptno=deptavgsal.deptno AND e1.sal>deptavgsal.avgsal;

2.列出达拉斯加（DALLAS）工作的人中,比纽约（NEW YORK）平均工资高的人

SELECT * FROM emp e1 WHERE e1.deptno = (SELECT d1.deptno FROM dept d1 WHERE d1.loc="DALLAS") AND e1.sal>(SELECT AVG(e2.sal) FROM emp e2 WHERE e2.deptno=(SELECT d2.deptno FROM dept d2 WHERE d2.loc="NEW YORK"));

3.查询7369员工编号,姓名,经理编号和经理姓名

根据员工emp数据表，可以先确定“7369员工编号,姓名,经理编号”

SELECT empno 员工编号,ename 姓名,mgr 经理编号 FROM emp WHERE empno=7369;

再把确定的经理编号mgr的值，查询经理的姓名

SELECT empno,ename FROM emp WHERE emp.empno=(SELECT mgr FROM emp WHERE empno=7369);

整合之后

SELECT e1.empno 员工编号,e1.ename 姓名,e1.mgr 经理编号,(SELECT ename FROM emp WHERE emp.empno=(SELECT mgr FROM emp WHERE empno=7369)) 经理姓名 FROM emp e1 WHERE e1.empno=7369;

 

简化

SELECT e1.empno 员工编号,e1.ename 姓名,e1.mgr 经理编号,(SELECT e2.ename FROM emp e2 WHERE e2.empno=e1.mgr) 经理姓名 FROM emp e1 WHERE e1.empno=7369;

4.查询出各个部门薪水最高的员工所有信息

第一步：确定所有部门薪资最高是多少

SELECT e1.deptno,MAX(e1.sal) FROM emp e1 GROUP BY e1.deptno;

第二步：根据部门和薪资获取员工信息

SELECT * FROM emp e2 WHERE (e2.deptno,e2.sal) IN (SELECT e1.deptno,MAX(e1.sal) sal FROM emp e1 GROUP BY e1.deptno);

前面使用in实现，如果使用where咋样呢？

SELECT * FROM emp e1 WHERE e1.sal = (SELECT MAX(sal) FROM emp e2 WHERE e1.deptno = e2.deptno GROUP BY e2.deptno);

显示效果一样！

持续更新之中...