首先创建四张表：

create table DEPT(  
  DEPTNO int(2) not null,  
  DNAME  VARCHAR(14),  
  LOC    VARCHAR(13)  
);  
alter table DEPT  
  add constraint PK_DEPT primary key (DEPTNO); 
  
create table EMP  
(  
  EMPNO    int(4) primary key,  
  ENAME    VARCHAR(10),  
  JOB      VARCHAR(9),  
  MGR      int(4),  
  HIREDATE DATE,  
  SAL      double(7,2),  
  COMM     double(7,2),  
  DEPTNO   int(2)  
);  
alter table EMP  
  add constraint FK_DEPTNO foreign key (DEPTNO)  
  references DEPT (DEPTNO);  
  
create table SALGRADE  
(  
  GRADE int primary key,  
  LOSAL double(7,2),  
  HISAL double(7,2)  
);  
create table BONUS  
(  
  ENAME VARCHAR(10),  
  JOB   VARCHAR(9),  
  SAL   double(7,2),  
  COMM  double(7,2)  
);  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (10, 'ACCOUNTING', 'NEW YORK');  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (20, 'RESEARCH', 'DALLAS');  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (30, 'SALES', 'CHICAGO');  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (40, 'OPERATIONS', 'BOSTON');  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (1, 700, 1200);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (2, 1201, 1400);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (3, 1401, 2000);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (4, 2001, 3000);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (5, 3001, 9999);  
-- 查看表：
select * from dept; 
-- 部门表：dept:department 部分 ，loc - location 位置
select * from emp;
-- 员工表：emp:employee 员工   ,mgr :manager上级领导编号，hiredate 入职日期  firedate 解雇日期 ，common：补助
-- deptno 外键 参考  dept - deptno字段
-- mgr 外键  参考  自身表emp - empno  产生了自关联
select * from salgrade;
-- losal - lowsal
-- hisal - highsal
select * from bonus;

求部门平均薪水的等级

SALARYGRADE
EMP表
EMPNO 员工号,ENAME 员工名称
JOB 工作,MGR 经理的工号
HIREDATE 入职时间,SAL 薪资
COMM 年终奖, DEPTNO 部门ID
EMP
DEPT表DEPT

先找到所有部门的平均薪水：

SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO


再将以上结果看做一个表，与DEPT，和SALGRADE联表查询查出部门和平均薪水等级

SELECT D.DNAME,S.GRADE FROM
(SELECT DEPTNO,AVG(SAL) AS AVG_SAL FROM EMP GROUP BY DEPTNO) T
JOIN SALGRADE S ON T.AVG_SAL BETWEEN S.LOSAL AND S.HISAL
JOIN DEPT D ON T.DEPTNO = D.DEPTNO

求部门平均的薪水等级

先查出所有员工所在部门以及员工的薪水等级

SELECT E.DEPTNO,E.SAL,S.GRADE FROM EMP E JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL

再对部门号进行分组算每个部门的薪水平均等级

SELECT T.DEPTNO,AVG(GRADE) FROM
(SELECT E.DEPTNO,E.SAL,S.GRADE FROM EMP E JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL) T
GROUP BY T.DEPTNO

哪些人是经理

先找出经理的EMPNO

SELECT DISTINCT MGR FROM EMP WHERE MGR IS NOT NULL


再查出经理的ENAME

SELECT ENAME FROM
(SELECT DISTINCT MGR FROM EMP WHERE MGR IS NOT NULL) T
JOIN EMP E
ON T.MGR = E.EMPNO

不用组函数求最高薪水

排序过后再使用limit方法

SELECT E.SAL FROM EMP E ORDER BY E.SAL DESC LIMIT 0,1

平均薪水最高的部门编号与名称

先查出部门以及部门的平均薪水

SELECT E.DEPTNO,AVG(SAL) AS AVG_SAL FROM EMP E GROUP BY E.DEPTNO


再另写一条语句查询部门的最高薪水

SELECT MAX(AVG_SAL) AS MAX_SAL FROM 
(SELECT AVG(SAL) AS AVG_SAL FROM EMP E 
GROUP BY E.DEPTNO) T2


最后查出平均薪水最高的部门

SELECT DEPTNO,DNAME FROM DEPT WHERE DEPTNO =
(
    SELECT DEPTNO FROM
    (SELECT E.DEPTNO,AVG(SAL) AS AVG_SAL FROM EMP E GROUP BY E.DEPTNO) T1
    WHERE T1.AVG_SAL = 
    (
    SELECT MAX(AVG_SAL) AS MAX_SAL FROM
        (SELECT AVG(SAL) AS AVG_SAL FROM EMP E GROUP BY E.DEPTNO) T2
    )
)

求平均薪水的等级最低的部门和部门名称

思路跟求平均薪水最高的部门的部门编号一样，只需要修改MAX函数为MIN函数即可

SELECT D.DNAME,D.DEPTNO FROM DEPT D WHERE D.DEPTNO = 
(
    SELECT DEPTNO FROM
    (SELECT DEPTNO,AVG(SAL) AS AVG_SAL FROM EMP E GROUP BY DEPTNO) T
    WHERE T.AVG_SAL = 
    (
        SELECT MIN(MIN_SAL) FROM
        (SELECT AVG(SAL) AS MIN_SAL FROM EMP GROUP BY DEPTNO) T1
    )
)

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

先找到普通员工的最高薪水

SELECT MAX(SAL) AS MAX_SAL FROM EMP E WHERE E.MGR IS NOT NULL

因为已经找到普通员工的最高薪水，所以再从EMP查找大于普通员工最高薪水的员工，此时查出来的就全是经理人。

SELECT E.EMPNO,E.ENAME,E.SAL FROM
(SELECT MAX(SAL) AS MAX_SAL FROM EMP E WHERE E.MGR IS NOT NULL) T
JOIN EMP E
ON E.SAL > T.MAX_SAL

最后修改：2022 年 01 月 30 日
© 允许规范转载
如果觉得我的文章对你有用，请随意赞赏