表结构
--部门表create tabledept(deptnoint(3)primary key,dnamevarchar(14),locvarchar(13));--雇员表create tableemp(empnoint(4)notnullprimary key,enamevarchar(10),jobvarchar(10),mgrint(4),hiredate datetime,saldouble,commdouble,deptnoint(3),foreignkey(deptno)referencesdept(deptno));--工资级别表create tablesalgrade(gradeint(3)primary key,losalint(3),hisalint(3));2.插入数据（进行初始化）use company;--往部门表中查数据insert into deptvalues(10,'Accounting','New York');insert into deptvalues(20,'Research','Dallas');insert into deptvalues(30,'Sales','Chicago');insert into deptvalues(40,'Operations','Boston');insert into deptvalues(50,'Admin','Washing');--往雇员表中插数据insert into empvalues(7369,'Smith','Clerk',7902,'1980-12-17',800,0,20);insert into empvalues(7499,'Allen','Salesman',7698,'1981-2-20',1600,300,30);insert into empvalues(7844,'Turner','Salesman',7499,'1981-9-8',1500,0,30);insert into empvalues(7698,'Tom','Manager',0,'1981-9-8',6100,600,40);insert into empvalues(7876,'Adams','Clerk',7900,'1987-5-23',1100,0,20);insert into empvalues(7900,'James','Clerk',7698,'1981-12-3',2400,0,30);insert into empvalues(7902,'Ford','Analyst',7698,'1981-12-3',3000,null,20);insert into empvalues(7901,'Kik','Clerk',7900,'1981-12-3',1900,0,30);--往工资级别表中插数据insert into salgradevalues(1,700,1200);insert into salgradevalues(2,1201,1400);insert into salgradevalues(3,1401,2000);insert into salgradevalues(4,2001,3000);insert into salgradevalues(5,3001,5000);insert into salgradevalues(6,5001,10000);
1.查询雇员表中工资最高的雇员的员工号、员工姓名、工资和部门号。
SELECT empno, ename, sal, deptnoFROM empWHERE sal = (SELECTmax(sal)FROM emp);
2.薪水大于1200的雇员，按照部门编号进行分组，分组后的平均薪水必须大于1500，查询各分组的平均工资，按照工资的倒序进行排列。
SELECTavg(sal)avg_sal, deptnoFROM empWHERE sal > 1200GROUP BY deptnoHAVING avg_sal > 1500ORDER BY avg_sal DESC;
3.查询每个雇员和其所在的部门名
SELECT ename,dnameFROM emp,deptWHERE(emp.deptno = dept.deptno);
-- 或者（推荐）(on中就写连接条件，where中就写过滤条件，各司其职)
SELECT ename,dnameFROM empJOIN deptON(emp.deptno = dept.deptno);
4.查询每个雇员姓名及其工资所在的等级
SELECTename,gradeFROMempeJOINsalgradesON(e.salBETWEENs.losalANDs.hisal);
5.查询雇员名第2个字母不是a的雇员的姓名、所在的组名、工资所在的等级。
SELECT ename, dname, gradeFROM emp eJOIN dept d ON (e.deptno = d.deptno)JOIN salgrade s ON ( e.sal BETWEEN s.losalANDs.hisal)WHERE ename NOT LIKE'_a%';
6.查询每个雇员和其经理的姓名
SELECT e1.ename, e2.enameFROM emp e1,emp e2WHERE(e1.mgr = e2.empno);SELECT e1.ename, e2.enameFROM emp e1JOIN emp e2ON(e1.mgr = e2.empno);
7.查询每个雇员和其经理的姓名（包括公司老板本身（他上面没有经理））
SELECT e1.ename, e2.enameFROM emp e1LEFT JOIN emp e2ON(e1.mgr = e2.empno);
8.查询每个雇员的姓名及其所在部门的部门名（包括没有雇员的部门）
SELECT ename,dnameFROM emp eRIGHT JOIN dept dON(e.deptno = d.deptno);
9.查询每个部门中工资最高的人的姓名、薪水和部门编号
-- 先求出每个部门中的最高工资：select max(sal) max_sal,deptnofromemp group by deptno-- 在使用连接查询：select ename,sal,e.deptnofromemp e join(select max(sal) max_sal,deptnofromemp group by deptno) ton(e.sal = t.max_salande.deptno = t.deptno);
10.查询每个部门平均工资所在的等级
select deptno,avg_sal,gradefromsalgradejoin(select deptno,avg(sal) avg_salfromemp group by deptno) ton(t.avg_sal between salgrade.losalandsalgrade.hisal);或者：select deptno,avg_sal,gradefrom(select deptno,avg(sal) avg_salfromemp group by deptno) tjoin salgrade s on(t.avg_sal between s.losalands.hisal);
11.查询每个部门内平均的薪水等级
先求每个人的薪水等级selectename,deptno,gradefromempjoinsalgradeson(emp.salbetweens.losalands.hisal);再按组进行分组求平均selectdeptno,avg(grade)from(selectename,deptno,gradefromempjoinsalgradeson(emp.salbetweens.losalands.hisal))tgroupbydeptno;
12.查询雇员中有哪些人是经理人：
select enamefromemp where empnoin(select distinct mgrfromemp);或者：select enamefromemp join(select distinct mgrfromemp) ton(emp.empno=t.mgr);
13.平均薪水最高的部门的部门编号
1：先求出每个部门的平均薪水和部门号（把这个看成一张表）select avg(sal) avg_sal,deptnofromemp group by deptno;2：再求出平均薪水最高值（把这个看成一个值）select max(avg_sal)from(select avg(sal) avg_sal,deptnofromemp group by deptno) t;3：对1表使用2条件进行查询即可select avg_sal,deptnofrom(select avg(sal) avg_sal,deptnofromemp group by deptno) t1where avg_sal=(select max(avg_sal)from(select avg(sal) avg_sal,deptnofromemp group by deptno) t2);
14.求平均薪水最高的部门的部门名称
select dnamefromdept where deptno = ( select deptnofrom(select avg(sal) avg_sal,deptnofromemp group by deptno) t1 where avg_sal= (select max(avg_sal)from(select avg(sal) avg_sal,deptnofromemp group by deptno) t2));
15.查询平均薪水的等级最低的部门名称
1.求平均薪水select avg(sal) avg_sal,deptnofromemp group by deptno;2.求平均薪水的等级select avg_sal,deptno,gradefrom(select avg(sal) avg_sal,deptnofromemp group by deptno) tjoin salgrade s on(t.avg_sal between s.losalandhisal);3.求平均薪水的等级最低的那个值select min(grade)from( select avg_sal,deptno,gradefrom(select avg(sal) avg_sal,deptnofromemp group by deptno) t join salgrade s on(t.avg_sal between s.losalandhisal)) t3;4.平均薪水的等级最低的部门的部门编号(将2和3组合起来，将2看成要查询的表，将3看成查询条件)select deptnofrom( select avg_sal,deptno,gradefrom(select avg(sal) avg_sal,deptnofromemp group by deptno) t join salgrade s on(t.avg_sal between s.losalandhisal)) t2where grade=( select min(grade)from( select avg_sal,deptno,gradefrom(select avg(sal) avg_sal,deptnofromemp group by deptno) t join salgrade s on(t.avg_sal between s.losalandhisal) ) t3);5.平均薪水的等级最低的部门名称select dname,deptnofromdept where deptnoin( select deptnofrom( select avg_sal,deptno,gradefrom(select avg(sal) avg_sal,deptnofromemp group by deptno) t join salgrade s on(t.avg_sal between s.losalandhisal) ) t2 where grade= ( select min(grade)from( select avg_sal,deptno,gradefrom(select avg(sal) avg_sal,deptnofromemp group by deptno) t join salgrade s on(t.avg_sal between s.losalandhisal) ) t3 ));
作者：foreknow
链接：https://www.jianshu.com/p/77f0c5c78921
来源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。
