Day028 MySQL的多表查询的练习

建议：将复杂的查询分解成简单的查询，然后逐步的完成，达到最终结果。

前期准备：

--新建部门表。（包含部门编号、部门名称、部门所在的城市）

create table dept(

deptno int(4) primary key,

dname varchar(20),

loc varchar(20)

);

--给部门插入数据

insert into dept values

(10, 'Accounting', 'New York'),

(20, 'Research', 'Dallas'),

(30, 'Sales', 'Chicago'),

(40, 'Operations', 'Boston');

--创建雇员emp表（包含雇员编号、雇员姓名、职位、雇员领导、入职日期、薪资、津贴、部门编，部门编号外连接到部门表）

create table emp(

empno int(4) primary key,

ename varchar(10),

job varchar(9),

mgr int(4),

hiredate date,

sal decimal(7,2),

comm decimal(7,2),

deptno int(2),

constraint fk_deptno foreign key(deptno) references dept(deptno));

--给雇员表插入数据

insert into emp values

(7369, 'Smith', 'clerk', 7902, '1980-12-17', 800, null, 20),

(7499, 'Allen', 'salesman', 7698, '1981-02-20', 1600, 300, 30),

(7521, 'Ward', 'salesman', 7698, '1981-02-22', 1250, 500, 30),

(7566, 'Jones', 'manager', 7839, '1981-04-02', 2975, null, 20),

(7654, 'Maritn', 'salesman', 7698, '1981-09-28', 1250, 1400, 30),

(7698, 'Blake', 'manager', 7839, '1981-05-01', 2850, null, 30),

(7782, 'Clark', 'manager', 7839, '1981-06-09', 2450, null, 10),

(7788, 'Scott', 'analyst', 7566, '1987-04-19', 3000, null, 20),

(7839, 'King', 'president', null, '1981-11-17', 5000, null, 10),

(7844, 'Turner', 'salesman', 7698, '1981-09-08', 1500, 0, 30),

(7876, 'Adams', 'clerk', 7788, '1987-05-23', 1100, null, 20),

(7900, 'James', 'clerk', 7698, '1981-12-03', 950, null, 30),

(7902, 'Ford', 'analyst', 7566, '1981-12-03', 3000, null, 20),

(7934, 'Miller', 'clerk', 7782, '1982-01-23', 1300, null, 10);

1.列出至少有四位员工的所有部门信息

(1).查询每个部门的人数

select deptno,count(*) from emp group by deptno

(2).至少有四位

select deptno,count(*) from emp group by deptno having count(*)>4

(3).部门信息

select d.*,dd.cou

from dept d,(select deptno,count(*) cou from emp group by deptno having count(*)>4)dd

where d.deptno=dd.deptno

2.列出薪金比“SMITH”多的员工信息

(1).查询“SMITH”的薪金

select sal from emp where ename='smith'

(2).查询比子查询结果工资高的雇员信息

select * from emp where sal>(select sal from emp where ename='smith')

3.列出所有员工的姓名及其直接上级的姓名。没有领导的雇员也列出

select e.ename,m.ename

from emp e left join emp m

on e.mgr=m.empno

4.列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称

(1).受雇日期早于其直接上级的所有员工的编号、姓名

select e.empno,e.ename

from emp e join emp m on e.mgr=m.empno

where e.hiredate<m.hiredate

(2).连接部门表

select e.empno,e.ename,d.dname

from emp e join emp m on e.mgr=m.empno

join dept d on e.deptno=d.deptno

where e.hiredate<m.hiredate

5.列出所有“CLERK”（办事员）的姓名及其部门名称、部门人数

(1).所有“CLERK”（办事员）的姓名

select ename from emp where job='clerk'

(2).部门人数

select count(*),deptno from emp group by deptno

(3).select d.dname,dd.cou,e.ename

from dept d join (select count(*) cou,deptno from emp group by deptno)dd on d.deptno=dd.deptno

join emp e on d.deptno=e.deptno

where job='clerk'

6.列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数

(1).最低薪金大于1500的各种工作

select job from emp group by job having min(sal)>1500

(2).每个工作的雇员人数

select count(*) from emp group by job

(3). select count(*),job from emp

where job in(select job from emp group by job having min(sal)>1500)

group by job

7.列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。

(1).部门“SALES”（销售部）的部门号

select deptno from dept where dname='sales'

(2).查询子查询结果的雇员姓名

select ename

from emp

where deptno=(select deptno from dept where dname='sales')

8.列出薪金高于公司平均薪金的所有员工，所在部门、上级领导

(1).薪金高于公司平均薪金的所有员工

select e.*

from emp e

where sal>(select avg(sal) from emp)

(2).连接部门表

select e.*,d.*

from emp e join dept d on e.deptno=d.deptno

where sal>(select avg(sal) from emp)

(3).上级领导连接emp

select e.*,d.*,m.*

from emp e join dept d on e.deptno=d.deptno

join emp m on e.mgr=m.empno

where e.sal>(select avg(sal) from emp)

9.列出与“SCOTT”从事相同工作的所有员工及部门名称

(1).“SCOTT”从事工作

select job from emp where ename='scott'

(2).从事子查询结果的所有员工

select e.*

from emp e

where job=(select job from emp where ename='scott') and e.ename!='scott'

(3).连接部门表查询部门名称

select e.*,d.dname

from emp e join dept d on e.deptno=d.deptno

where job=(select job from emp where ename='scott') and e.ename!='scott'

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

(1).部门30的薪资

select sal from emp where deptno=30

(2).高于部门30工作的所有员工的薪金

select e.ename,e.sal

from emp e

where e.sal > all(select sal from emp where deptno=30)

(3).部门连接部门表

select e.ename,e.sal,d.dname

from emp e join dept d on e.deptno=d.deptno

where e.sal > all(select sal from emp where deptno=30)

11.列出在每个部门工作的员工数量、部门名称、平均工资和平均服务期限,

(1)平均服务期限--服务条数datediff

select datediff(sysdate(),hiredate)/365 from emp

(2).部门表和雇员表连表查询

select avg(sal),d.dname,count(*),avg(datediff(sysdate(),hiredate)/365)

from dept d join emp e on d.deptno=e.deptno

group by d.dname

12.列出所有员工的年工资，按年薪从低到高排序

(1).员工的年工资

select (sal+ifnull(comm,0))*12 yearsal from emp;

(2).年薪从低到高排序

select (sal+ifnull(comm,0))*12 yearsal from emp order by yearsal;

13.求出部门名称中，带‘s’字符的部门员工的工资合计、部门人数

(1). 部门名称中，带‘s’字符的部门的部门号

select deptno from dept where dname like '%s%'

(2).每个工资合计、部门人数

select sum(sal),count(*) from emp group by deptno

(3). 用where：

select sum(sal),count(*),deptno from emp

where deptno in(select deptno from dept where dname like '%s%') group by deptno

用 having：

select sum(sal),count(*) from emp

group by deptno having deptno in(select deptno from dept where dname like '%s%')