创建数据库，代码如下：
create database dbtest;
use dbtest;
DROP TABLE IF EXISTS EMP; DROP TABLE IF EXISTS DEPT; DROP TABLE IF EXISTS SALGRADE; CREATE TABLE DEPT -- 部门表 (DEPTNO int(2) not null , DNAME VARCHAR(14) , LOC VARCHAR(13), primary key (DEPTNO) ); CREATE TABLE EMP -- 雇员表 (EMPNO int(4) not null , ENAME VARCHAR(10), JOB VARCHAR(9), MGR INT(4), HIREDATE DATE DEFAULT NULL, SAL DOUBLE(7,2), COMM DOUBLE(7,2), primary key (EMPNO), DEPTNO INT(2) ) ; CREATE TABLE SALGRADE -- 薪水等级表 ( GRADE INT, LOSAL INT, HISAL INT ); -- 插入数据 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'); commit; 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); commit; 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); commit;
1：取得每个部门最高薪水的人员名称
第一步：首先将部门分组，查询出每个部门的最高薪水
第二步：将上面查询到的表当做临时表t与emp连接查询，查询条件为，emp表的部门编号与t表的部门编号deptno相等，并且emp表的sal值 与 t表 maxsal值相等
select e.eanme,e.deptno from (select deptno,max(sal) as maxsal from emp group by deptno) t -- 将查询到的表当做临时表t join emp e on t.deptno = e.deptno and t.maxsal = e.sal;
运行结果：
+-------+--------+ | ename | deptno | +-------+--------+ | BLAKE | 30 | | SCOTT | 20 | | KING | 10 | | FORD | 20 | +-------+--------+
2：哪些人的薪水在部门的平均薪水之上
第一步：查询出部门的平均薪水
select deptno,avg(sal) from emp group by deptno;
第二部：查询出各部门薪水高于平均薪水的人员，条件为 sal>平均薪水
select t.*, e.ename,e.sal from ( select deptno,avg(sal) as avgsal from emp group by deptno) t join emp e on t.deptno = e.deptno and e.sal > avgsal;
结果
+--------+-------------+-------+---------+ | deptno | avgsal | ename | sal | +--------+-------------+-------+---------+ | 30 | 1566.666667 | ALLEN | 1600.00 | | 20 | 2175.000000 | JONES | 2975.00 | | 30 | 1566.666667 | BLAKE | 2850.00 | | 20 | 2175.000000 | SCOTT | 3000.00 | | 10 | 2916.666667 | KING | 5000.00 | | 20 | 2175.000000 | FORD | 3000.00 | +--------+-------------+-------+---------+
3.取得部门中（所有人）平均的薪水等级
第一步：查询每一个人的薪水等级
第二部：将查询到的数据按照部门分组，求薪水等级的平均值
select e.deptno,avg(s.grade) from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno; -------------------------------------------------------------------------------------------- +--------+--------------+ | deptno | avg(s.grade) | +--------+--------------+ | 20 | 2.8000 | | 30 | 2.5000 | | 10 | 3.6667 | +--------+--------------+
4.不准使用max函数，求出最高薪水（给出两种解决方案）：
使用max函数得出结果为：
mysql> select max(sal) from emp; +----------+ | max(sal) | +----------+ | 5000.00 | +----------+
第一种：降序排列，分页查询(需要注意sal的值可能有多个相同的值)
mysql> select ename,sal from emp order by sal desc limit 0,1; +-------+---------+ | ename | sal | +-------+---------+ | KING | 5000.00 | +-------+---------+
第二种：表的自连接
select sal from emp where sal not in (select a.sal from emp a join emp b on a.sal < b.sal); +---------+ | sal | +---------+ | 5000.00 | +---------+
5.取得平均薪水最高的部门名称和部门编号（两种解决方案）
第一种：
select t.*,d.dname from (select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1) t join dept d on t.deptno = d.deptno; +--------+-------------+------------+ | deptno | avgsal | dname | +--------+-------------+------------+ | 10 | 2916.666667 | ACCOUNTING | +--------+-------------+------------+
第二种：
mysql> select deptno, avg(sal) as avgsal from emp group by deptno having avgsal = (select max(t.avgsal) from (select deptno,avg(sal) as avgsal from emp group by deptno) t); +--------+-------------+ | deptno | avgsal | +--------+-------------+ | 10 | 2916.666667 | +--------+-------------+
6.列出所有“clerk”的员工姓名，及其部门名称，部门的人数:
第一步：查询出职位为clerk的员工
mysql> select ename,deptno from emp where job="clerk"; +--------+--------+ | ename | deptno | +--------+--------+ | SMITH | 20 | | ADAMS | 20 | | JAMES | 30 | | MILLER | 10 | +--------+--------+
第二步：查询出对应的部门的deptno和人数count(*),与上表进行连接
mysql>select t.*,p.deptcount from (select e.ename,e.job,d.dname,e.deptno from emp e join dept d on e.deptno = d.deptno where e.job = 'CLERK') t join (select deptno,count(*) as deptcount from emp group by deptno) p on t.deptno = p.deptno; +--------+-------+------------+--------+-----------+ | ename | job | dname | deptno | deptcount | +--------+-------+------------+--------+-----------+ | SMITH | CLERK | RESEARCH | 20 | 5 | | ADAMS | CLERK | RESEARCH | 20 | 5 | | JAMES | CLERK | SALES | 30 | 6 | | MILLER | CLERK | ACCOUNTING | 10 | 3 | +--------+-------+------------+--------+-----------+