mysql多表查询

/*创建部门表dept*/
CREATE TABLE dept(
deptno INT PRIMARY KEY,
dname VARCHAR(50),
loc VARCHAR(50)
);

/*插入dept表数据*/
INSERT INTO dept VALUES (10, '教研部', '北京');
INSERT INTO dept VALUES (20, '学工部', '上海');
INSERT INTO dept VALUES (30, '销售部', '广州');
INSERT INTO dept VALUES (40, '财务部', '武汉'); 

/*创建雇员表emp*/
CREATE TABLE emp(
empno INT PRIMARY KEY,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
COMM DECIMAL(7,2),
deptno INT,
CONSTRAINT fk_emp_dept FOREIGN KEY(deptno) REFERENCES dept(deptno)
);

/*插入emp表数据*/
INSERT INTO emp VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20);
INSERT INTO emp VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO emp VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO emp VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO emp VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO emp VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO emp VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO emp VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO emp VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO emp VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000, NULL, 20);
INSERT INTO emp VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO emp VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20);
INSERT INTO emp VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10);



CREATE TABLE salgrade(
grade INT PRIMARY KEY,
losal INT,
hisal INT
);

/*插入salgrade表数据*/
INSERT INTO salgrade VALUES (1, 7000, 12000);
INSERT INTO salgrade VALUES (2, 12010, 14000);
INSERT INTO salgrade VALUES (3, 14010, 20000);
INSERT INTO salgrade VALUES (4, 20010, 30000);
INSERT INTO salgrade VALUES (5, 30010, 99990);

 1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
/*分析：
需查询的列：dept.*,部门人数
在哪几个表里查：dept d,emp e
条件：d.deptno=e,deptno */

select  d.*,z1.cnt 
from dept d,(select deptno,count(*) cnt from emp group by deptno)z1
where d.deptno=z1.deptno;

2. 列出薪金比关羽高的所有员工。
select * 
from emp  
where sal>(select sal from emp where ename='关羽');

3. 列出所有员工的姓名及其直接上级的姓名。

select e.*,IFNULL(m.ename,'BOSS') leader 
from emp e
left join emp m on e.mgr=m.empno;

4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
/*列：e.empno,e.ename,d.dname
表：emp e,emp m(直接上级表),dept d
条件：e.hiredate<m.hiredate

select e.empno,e.ename,e.deptno,e.hiredate,m.ename,m.hiredate
from emp e,emp m
where e.mgr=m.empno and e.hiredate<m.hiredate;  */

select e.empno,e.ename,d.dname
from emp e,emp m,dept d
where e.mgr=m.empno and e.hiredate<m.hiredate and e.deptno=d.deptno;

5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select * 
from emp e right outer join dept d
on e.deptno=d.deptno;

6. 列出所有文员的姓名及其部门名称，部门的人数。
select p.ename,d.dname,z1.cnt
from (select ename,deptno from emp where job='文员')p 
left join  dept d 
on p.deptno=d.deptno
left join (select deptno,count(*) cnt from emp group by deptno)z1
on d.deptno=z1.deptno;

7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。(单表也可)

select job,count(*)
from emp 
group by job
having min(sal)>15000;

select e.job,m.cnt
from (select job,count(*),min(sal) minsal from emp group by job)e
left join(select job,count(*) cnt from emp group by job)m
on e.job=m.job
where e.minsal>15000;

8. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。
select emp.ename,m.dname
from emp 
left join (select dname,deptno from dept) m on emp.deptno=m.deptno
where m.dname='销售部';

select e.ename,m.dname
from emp e,dept m
where e.deptno=m.deptno and m.dname='销售部';

9. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。
/*内连接得到的数据不全*/
select e.ename,d.dname,m.ename,s.grade
from emp e,dept d,emp m,salgrade s
where e.sal>(select avg(sal) from emp) and e.deptno=d.deptno and e.mgr=m.empno and e.sal between s.losal and s.hisal;

select e.ename,d.dname,m.ename leader,s.grade
from emp e 
left join dept d on e.deptno=d.deptno
left join emp m on e.mgr=m.empno
left join salgrade s on e.sal between s.losal and s.hisal
where e.sal>(select avg(sal) from emp);

10.列出与庞统从事相同工作的所有员工及工资及部门名称。

select e.ename,e.sal,d.dname
from emp e,dept d
where e.job=(select job from emp where ename='庞统') and e.deptno=d.deptno;

select e.ename,e.sal,d.dname
from emp e 
left join dept d on e.deptno=d.deptno
where e.job=(select job from emp where ename='庞统');

11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。

select e.ename,e.sal,d.dname
from emp e,dept d
where e.sal>all(select sal from emp where deptno=30) and e.deptno=d.deptno
order by e.sal;

select e.ename,e.sal,d.dname
from emp e
left join dept d on e.deptno=d.deptno
where e.sal>all(select sal from emp where deptno=30);

12.列出在每个部门工作的员工数量、平均工资。
/*平均工资也是在分组后的每组中进行*/
select d.deptno,d.dname,m.cnt,m.asal
from dept d
left join (select deptno,count(*)cnt,avg(sal)asal from emp group by deptno)m on d.deptno = m.deptno;