DROP TABLE IF EXISTS `dept`; CREATE TABLE `dept` ( `DEPTNO` int(2) NOT NULL COMMENT '部门编号', `DNAME` varchar(14) DEFAULT NULL COMMENT '部门名称', `LOC` varchar(13) DEFAULT NULL COMMENT '位置', PRIMARY KEY (`DEPTNO`) ) ENGINE=InnoDB;
INSERT INTO `dept` VALUES ('10', 'ACCOUNTING', 'NEW YORK'); INSERT INTO `dept` VALUES ('20', 'RESEARCH', 'DALLAS'); INSERT INTO `dept` VALUES ('30', 'SALES', 'CHICAGO'); INSERT INTO `dept` VALUES ('40', 'OPERATIONS', 'BOSTON');
创建员工表
DROP TABLE IF EXISTS `emp`; CREATE TABLE `emp` ( `EMPNO` int(4) NOT NULL COMMENT '员工编号', `ENAME` varchar(10) DEFAULT NULL COMMENT '员工姓名', `JOB` varchar(9) DEFAULT NULL COMMENT '工作岗位', `MGR` int(4) DEFAULT NULL COMMENT '上级经理', `HIREDATE` date DEFAULT NULL, `SAL` double(7,2) DEFAULT NULL, `COMM` double(7,2) DEFAULT NULL, `DEPTNO` int(2) DEFAULT NULL, PRIMARY KEY (`EMPNO`), KEY `DEPTNO` (`DEPTNO`), KEY `SAL` (`SAL`), CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`DEPTNO`) REFERENCES `dept` (`DEPTNO`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工编号';
插入语句：
INSERT INTO `emp` VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800.00', null, '20'); INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600.00', '300.00', '30'); INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250.00', '500.00', '30'); INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975.00', null, '20'); INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250.00', '1400.00', '30'); INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850.00', null, '30'); INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450.00', null, '10'); INSERT INTO `emp` VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19', '3000.00', null, '20'); INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000.00', null, '10'); INSERT INTO `emp` VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500.00', '0.00', '30'); INSERT INTO `emp` VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1981-05-23', '1100.00', null, '20'); INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950.00', null, '30'); INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000.00', null, '20'); INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300.00', null, '10');
C:薪水等级表
1，建表语句
DROP TABLE IF EXISTS `salgrade`; CREATE TABLE `salgrade` ( `GRADE` int(11) DEFAULT NULL, `LOSAL` int(11) DEFAULT NULL, `HISAL` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `salgrade` VALUES ('1', '700', '1200'); INSERT INTO `salgrade` VALUES ('2', '1201', '1400'); INSERT INTO `salgrade` VALUES ('3', '1401', '2000'); INSERT INTO `salgrade` VALUES ('4', '2001', '3000'); INSERT INTO `salgrade` VALUES ('5', '3001', '9999');
表的结构如下所示：
1.取得每个部门最高薪水的人员名称
第一步：求出每个部门的最高薪水 select e.deptno,max(e.sal) as maxsal from emp e group by e.deptno; +--------+---------+ | deptno | maxsal | +--------+---------+ | 10 | 5000.00 | | 20 | 3000.00 | | 30 | 2850.00 | +--------+---------+ 将以上查询结果当成一个临时表t(deptno,maxsal) select e.deptno,e.ename,t.maxsal,e.sal from (select e.deptno,max(e.sal) as maxsal from emp e group by e.deptno)t join emp e on t.deptno = e.deptno where t.maxsal = e.sal order by e.deptno; +--------+-------+---------+---------+ | deptno | ename | maxsal | sal | +--------+-------+---------+---------+ | 10 | KING | 5000.00 | 5000.00 | | 20 | SCOTT | 3000.00 | 3000.00 | | 20 | FORD | 3000.00 | 3000.00 | | 30 | BLAKE | 2850.00 | 2850.00 | +--------+-------+---------+---------+
分析下：
首先group by 首先经常和聚合函数max等配合使用，第二使用了group by 在select后面的查询字段只能是group by 后面指定的字段不能是其他字段
第三：join on 条件中 on 和where的却别，不清楚的看自己的博客
MYSQL LEFT JOIN操作中 ON与WHERE放置条件的区别
on是两个表联合查询连接起来生成一个临时表，where是在生成临时表的基础上，对生成的临时表进行条件帅选
t.deptno = e.deptno 表示两个表生成临时表的关系是 emp表中的部门编号必须等于 t表中的部门编号
where之后的条件是：emp表和t表已经生成了临时表，然后对临时表进行条件过滤
2.哪些人的薪水在部门平均薪水之上
2.哪些人的薪水在部门平均薪水之上 第一步：求出每个部门的平均薪水 select e.deptno,avg(e.sal) as avgsal from emp e group by e.deptno; +--------+-------------+ | deptno | avgsal | +--------+-------------+ | 10 | 2916.666667 | | 20 | 2175.000000 | | 30 | 1566.666667 | +--------+-------------+ 将以上查询结果当成临时表t(deptno,avgsal) select t.deptno,e.ename from (select e.deptno,avg(e.sal) as avgsal from emp e group by e.deptno) t join emp e on e.deptno = t.deptno where e.sal > t.avgsal; +--------+-------+ | deptno | ename | +--------+-------+ | 30 | ALLEN | | 20 | JONES | | 30 | BLAKE | | 20 | SCOTT | | 10 | KING | | 20 | FORD | +--------+-------+
3.取得部门中（所有人的）平均薪水等级
第一种情况：emp表中按照部门进行分组，求出每个组的平均工资，看每个组的平均工资属于那个等级
第一步：求出部门的平均薪水 select e.deptno,avg(e.sal) as avgsal from emp e group by e.deptno; 将以下查询结果当成临时表t(deptno,avgsal) +--------+-------------+ | deptno | avgsal | +--------+-------------+ | 10 | 2916.666667 | | 20 | 2175.000000 | | 30 | 1566.666667 | +--------+-------------+ select * from salgrade; +-------+-------+-------+ | GRADE | LOSAL | HISAL | +-------+-------+-------+ | 1 | 700 | 1200 | | 2 | 1201 | 1400 | | 3 | 1401 | 2000 | | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | +-------+-------+-------+ select t.deptno,t.avgsal,s.grade from (select e.deptno,avg(e.sal) as avgsal from emp e group by e.deptno) t join salgrade s on t.avgsal between s.losal and s.hisal; +--------+-------------+-------+ | deptno | avgsal | grade | +--------+-------------+-------+ | 30 | 1566.666667 | 3 | | 10 | 2916.666667 | 4 | | 20 | 2175.000000 | 4 | +--------+-------------+-------+
第二种情况：首先求出每个人的薪水属于那个等级，然后进行分组
3.2 取得部门中所有人的平均的薪水等级 第一步：求出每个人的薪水等级 select e.deptno,e.ename,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal order by e.deptno; +--------+--------+-------+ | deptno | ename | grade | +--------+--------+-------+ | 10 | CLARK | 4 | | 10 | MILLER | 2 | | 10 | KING | 5 | | 20 | ADAMS | 1 | | 20 | SMITH | 1 | | 20 | FORD | 4 | | 20 | SCOTT | 4 | | 20 | JONES | 4 | | 30 | BLAKE | 4 | | 30 | JAMES | 1 | | 30 | ALLEN | 3 | | 30 | WARD | 2 | | 30 | TURNER | 3 | | 30 | MARTIN | 2 | +--------+--------+-------+ 将以上查询结果当成临时表t(deptno,ename,grade) select t.deptno,avg(t.grade) as avgGrade from (select e.deptno,e.ename,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t group by t.deptno; +--------+----------+ | deptno | avgGrade | +--------+----------+ | 10 | 3.6667 | | 20 | 2.8000 | | 30 | 2.5000 | +--------+----------+
4.不准用组函数（MAX），取得最高薪水（给出两种解决方案） select sal from emp order by sal desc limit 1;
5.取得平均薪水最高的部门的部门编号
5.取得平均薪水最高的部门的部门编号 第一步：求出部门平均薪水 select e.deptno,avg(e.sal) as avgsal from emp e group by e.deptno; +--------+-------------+ | deptno | avgsal | +--------+-------------+ | 10 | 2916.666667 | | 20 | 2175.000000 | | 30 | 1566.666667 | +--------+-------------+ 第二步：将以上查询结果当成临时表t(deptno,avgsal),求出最高的平均薪水 select max(t.avgsal) as maxAvgSal from (select e.deptno,avg(e.sal) as avgsal from emp e group by e.deptno) t; +-------------+ | maxAvgSal | +-------------+ | 2916.666667 | +-------------+ select e.deptno,avg(e.sal) as avgsal from emp e group by e.deptno having avgsal = (select max(t.avgsal) as maxAvgSal from (select e.deptno,avg(e.sal) as avgsal from emp e group by e.deptno) t); +--------+-------------+ | deptno | avgsal | +--------+-------------+ | 10 | 2916.666667 | +--------+-------------+
主要这里有一个坑不能写成下面的形式：
select e.deptno,avg(e.sal) avgSal from emp e group by e.deptno order by avgSal desc limit 1;
因为如果有100个部门，可能存在很多个部门的平均值都是一样的
6.取得平均薪水最高的部门的部门名称 select e.deptno,d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by e.deptno,d.dname having avgsal = (select max(t.avgsal) as maxAvgSal from (select e.deptno,avg(e.sal) as avgsal from emp e group by e.deptno) t); 7.求平均薪水的等级最低的部门的部门名称 第一步：部门的平均薪水 select e.deptno,d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by e.deptno,d.dname; +--------+------------+-------------+ | deptno | dname | avgsal | +--------+------------+-------------+ | 10 | ACCOUNTING | 2916.666667 | | 20 | RESEARCH | 2175.000000 | | 30 | SALES | 1566.666667 | +--------+------------+-------------+ 第二步：将以上结果当成临时表t(deptno,avgsal)与salgrade表进行表连接：t.avgsal between s.losal and s.hisal; select t.deptno,t.dname,s.grade from (select e.deptno,d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by e.deptno,d.dname)t join salgrade s on t.avgsal between s.losal and s.hisal; +--------+------------+-------+ | deptno | dname | grade | +--------+------------+-------+ | 30 | SALES | 3 | | 10 | ACCOUNTING | 4 | | 20 | RESEARCH | 4 | +--------+------------+-------+ 第三步：将以上查询结果当成一张临时表t select min(t.grade) as minGrade from (select t.deptno,t.dname,s.grade from (select e.deptno,d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by e.deptno,d.dname)t join salgrade s on t.avgsal between s.losal and s.hisal)t; +----------+ | minGrade | +----------+ | 3 | +----------+ select t.deptno,t.dname,s.grade from (select e.deptno,d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by e.deptno,d.dname)t join salgrade s on t.avgsal between s.losal and s.hisal where s.grade = (select min(t.grade) as minGrade from (select t.deptno,t.dname,s.grade from (select e.deptno,d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by e.deptno,d.dname)t join salgrade s on t.avgsal between s.losal and s.hisal)t); +--------+-------+-------+ | deptno | dname | grade | +--------+-------+-------+ | 30 | SALES | 3 | +--------+-------+-------+
8.取得比普通员工（员工代码没有在mgr上出现的）的最高薪水还要高的经理人姓名 第一步：找出普通员工(员工代码没有出现在mgr上的) 1.1 先找出mgr有哪些人 select distinct mgr from emp; +------+ | mgr | +------+ | 7902 | | 7698 | | 7839 | | 7566 | | NULL | | 7788 | | 7782 | +------+ select * from emp where empno in(select distinct mgr from emp); +-------+-------+-----------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+-------+-----------+------+------------+---------+------+--------+ | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | +-------+-------+-----------+------+------------+---------+------+--------+ select max(sal) as maxsal from emp where empno not in(select distinct mgr from emp where mgr is not null); +---------+ | maxsal | +---------+ | 1600.00 | +---------+ not in不会自动忽略空值 in会自动忽略空值 select ename from emp where sal > (select max(sal) as maxsal from emp where empno not in(select distinct mgr from emp where mgr is not null)); +-------+ | ename | +-------+ | JONES | | BLAKE | | CLARK | | SCOTT | | KING | | FORD | +-------+
第一步：找出普通员工(员工代码没有出现在mgr上的) 1.1 先找出mgr有哪些人 select distinct mgr from emp; +------+ | mgr | +------+ | 7902 | | 7698 | | 7839 | | 7566 | | NULL | | 7788 | | 7782 | +------+ select * from emp where empno in(select distinct mgr from emp); +-------+-------+-----------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+-------+-----------+------+------------+---------+------+--------+ | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | +-------+-------+-----------+------+------------+---------+------+--------+ select max(sal) as maxsal from emp where empno not in(select distinct mgr from emp where mgr is not null); +---------+ | maxsal | +---------+ | 1600.00 | +---------+ not in不会自动忽略空值 in会自动忽略空值 select ename from emp where sal > (select max(sal) as maxsal from emp where empno not in(select distinct mgr from emp where mgr is not null)); +-------+ | ename | +-------+ | JONES | | BLAKE | | CLARK | | SCOTT | | KING | | FORD | +-------+
这里有一个很关键的地方，not in 没有排除null值，如果存在null值和not in 做计算，得带的值就是null
mysql> select * from emp where empno not in(select distinct mgr from emp); Empty set mysql>
select distinct mgr from emp 的结果存在null值
这里not in 没有去掉null值得到的结果就是空
9.取得薪水最高的前五名员工 select * from emp order by sal desc limit 0,5; +-------+-------+-----------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+-------+-----------+------+------------+---------+------+--------+ | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | +-------+-------+-----------+------+------------+---------+------+--------+ 10.取得薪水最高的第六到第十名员工 select * from emp order by sal desc limit 5,5; +-------+--------+----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+----------+------+------------+---------+---------+--------+ | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | +-------+--------+----------+------+------------+---------+---------+--------+ 11.取得最后入职的5名员工 select * from emp order by hiredate desc limit 5; +-------+--------+---------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+---------+------+------------+---------+------+--------+ | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | +-------+--------+---------+------+------------+---------+------+--------+
12.取得每个薪水等级有多少员工 第一步：查询出每个员工的薪水等级 select e.ename,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal order by s.grade; +--------+-------+ | ename | grade | +--------+-------+ | JAMES | 1 | | SMITH | 1 | | ADAMS | 1 | | MILLER | 2 | | WARD | 2 | | MARTIN | 2 | | ALLEN | 3 | | TURNER | 3 | | BLAKE | 4 | | FORD | 4 | | CLARK | 4 | | SCOTT | 4 | | JONES | 4 | | KING | 5 | +--------+-------+ 将以上查询结果当成临时表t(ename,grade) select t.grade,count(t.ename) as totalEmp from (select e.ename,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t group by t.grade; +-------+----------+ | grade | totalEmp | +-------+----------+ | 1 | 3 | | 2 | 3 | | 3 | 2 | | 4 | 5 | | 5 | 1 | +-------+----------+