mysql多表查询练习

一、建表语句

CREATE TABLE dept (
  id INT PRIMARY KEY PRIMARY KEY, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门所在地
);

-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

-- 职务表，职务名称，职务描述
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20),
  description VARCHAR(50)
);
 
-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');
 
 
 
-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资
  bonus DECIMAL(7,2), -- 奖金
  dept_id INT, -- 所在部门编号
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
 
-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
 
 
 
 
-- 工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,   -- 级别
  losalary INT,  -- 最低工资
  hisalary INT -- 最高工资
);
 
-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

dept表：

emp表：

job表：

salarygrade表：

二、查询练习

-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
select ep.id,ep.ename,ep.salary,jb.jname,jb.description
from emp ep
left join job jb
on ep.job_id = jb.id;

-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
select ep.id,ep.ename,ep.salary,jb.jname,jb.description,dp.dname,dp.loc
from emp ep,job jb,dept dp
where ep.job_id = jb.id
and ep.dept_id = dp.id;

-- 3.查询员工姓名，工资，工资等级
select ep.ename,ep.salary,sg.grade
from emp ep,salarygrade sg
where ep.salary between sg.losalary and sg.hisalary;

-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
select ep.id,ep.ename,ep.salary,jb.jname,jb.description,dp.dname,dp.loc,sg.grade
from emp ep,job jb,dept dp,salarygrade sg
where ep.job_id = jb.id
and ep.dept_id = dp.id
and ep.salary between sg.losalary and sg.hisalary;

-- 5.查询出部门编号，部门名称，部门位置，部门人数
select dt.id,dt.dname,dt.loc,pt.ct
from dept dt,
(select count(id) as ct,dept_id from emp group by emp.dept_id) pt
where dt.id = pt.dept_id;

-- 6.查询所有员工的姓名及直接上级的姓名，没有领导的员工也需要查询
select * from emp;
select ep.id,ep.ename,mep.ename
from emp ep
left join emp mep
on ep.mgr = mep.id;

---

三、建表语句

CREATE TABLE dept (
  deptno INT PRIMARY KEY PRIMARY KEY, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门所在地
);

-- 添加4个部门
INSERT INTO dept(deptno,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','深圳'),
(40,'财务部','广州'),
(50,'董事会','太原');


-- 员工表
CREATE TABLE emp (
  empno INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job VARCHAR(50), -- 职务
  mgr INT , -- 上级领导
  hiredate DATE, -- 入职日期
  sal DECIMAL(7,2), -- 工资
  comm DECIMAL(7,2), -- 奖金
  deptno INT -- 所在部门编号
);
 
-- 添加员工
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES 
(1001,'甘宁','文员',1013,'2000-12-17','8000.00',NULL,20),
(1002,'黛绮丝','销售员',1006,'2001-02-20','16000.00','3000.00',20),
(1003,'殷天正','销售员',1006,'2001-02-22','12500.00','5000.00',30),
(1004,'刘备','经理',1009,'2001-04-02','29750.00',NULL,20),
(1005,'谢逊','销售员',1006,'2001-09-28','12500.00','14000.00',30),
(1006,'关羽','经理',1009,'2001-05-01','28500.00',NULL,30),
(1007,'张飞','经理',1009,'2001-09-01','24500.00',NULL,10),
(1008,'诸葛亮','分析师',1004,'2007-04-19','30000.00',NULL,20),
(1009,'张无忌','董事长',NULL,'2001-11-17','50000.00',NULL,10),
(1010,'韦一笑','销售员',1006,'2001-09-08','15000.00','0.00',30),
(1011,'周泰','文员',1008,'2007-05-23','11000.00',NULL,30),
(1012,'程普','文员',1006,'2001-12-03','9500.00',NULL,30),
(1013,'庞统','分析师',1004,'2001-12-03','30000.00',NULL,20),
(1014,'黄盖','文员',1007,'2002-01-23','13000.00',NULL,10),
(1015,'张三','保洁员',1001,'2013-05-01','80000.00',50000.00,50);
 
 
-- 工资等级表
CREATE TABLE salgrade (
  grade INT PRIMARY KEY,   -- 级别
  losal INT,  -- 最低工资
  hisal INT -- 最高工资
);
 
-- 添加5个工资等级
INSERT INTO salgrade(grade,losal,hisal) VALUES 
(1,7000,12000),
(2,12000,14000),
(3,14000,20000),
(4,20000,30000),
(5,null,90000);


-- tbyear
CREATE TABLE tbyear (
  year INT PRIMARY KEY,
  zz INT
);
 
-- 添加5个工资等级
INSERT INTO tbyear(year,zz) VALUES 
(2010,100),
(2011,150),
(2012,250),
(2013,800),
(2014,1000);

dept表

emp表

salgrade表

tbyear表

-- 1.查出至少有一个员工的部门，显示部门编号、部门名称、部门位置、部门人数
select count(empno) as peopleNumber,deptno from emp group by deptno;

select dp.deptno,dp.dname,dp.loc,ep.peopleNumber
from dept dp,(select count(empno) as peopleNumber,deptno from emp group by deptno) ep
where dp.deptno = ep.deptno and ep.peopleNumber >= 1;

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

-- 3.列出所有员工的姓名及其直接上级的姓名
select ep.ename,et.ename 上级
from emp ep,emp et
where ep.mgr = et.empno;

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

select ep.empno,ep.ename,ep.hiredate,et.hiredate,dt.dname
from emp ep,emp et,dept dt
where ep.mgr = et.empno and ep.hiredate < et.hiredate and
ep.deptno = dt.deptno;

-- 5.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
-- select dt.dname,ep.*
-- from dept dt,emp ep
-- where dt.deptno = ep.deptno;

select dt.dname,ep.*
from dept dt
left outer join emp ep
on dt.deptno = ep.deptno;

-- 6.列出所有文员的姓名及其部门名称，部门的人数
select ep.ename,dt.dname,ep.deptno
from emp ep,dept dt
where ep.job = '文员' and ep.deptno = dt.deptno;

select count(ep.empno) as personCount,dt.deptno from emp ep
right outer join
dept dt
on ep.deptno = dt.deptno
group by dt.deptno;

select a.ename,a.dname,b.personCount
from (select ep.ename,dt.dname,ep.deptno
from emp ep,dept dt
where ep.job = '文员' and ep.deptno = dt.deptno) a, 
(select count(ep.empno) as personCount,dt.deptno from emp ep
right outer join
dept dt
on ep.deptno = dt.deptno
group by dt.deptno) b
where a.deptno = b.deptno;

-- 7.列出最低薪金大于15000的各种工作及从事此工作的员工人数
select distinct (t.job),t.empno,t.ename,t.deptno from
(select empno,ename,job,sal,deptno from emp where sal > 15000) t;

select * from emp where sal > 15000;
select distinct job from emp where sal > 15000;

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

-- 8.列出在销售部工作的员工的姓名，假定不知道销售部的部门编号
select ep.ename from emp ep,dept dt
where ep.deptno = dt.deptno and dt.dname = '销售部';

-- 9.列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级
select avg(sal) from emp;
select * from emp where sal > (select avg(sal) from emp);

-- select ep.*,dt.dname,et.ename,sg.grade
-- from emp ep,dept dt,emp et,salgrade sg
-- where ep.deptno = dt.dname and ep.mgr = et.empno and ep.sal between sg.losal and sg.hisal and ep.sal > (select avg(sal) from emp ep);


select ep.*,dt.dname from emp ep,dept dt where ep.deptno = dt.deptno and ep.sal > (select avg(sal) from emp ep);

select t.*,b.ename,c.grade from (select ep.*,dt.dname from emp ep,dept dt where ep.deptno = dt.deptno and ep.sal > (select avg(sal) from emp ep)) t
left outer join emp b on t.mgr = b.empno
left outer join salgrade c on t.sal between c.losal and c.hisal;

-- 10.列出与庞统从事相同工作的所有员工及部门名称
-- select ep.*,dt.dname
-- from emp ep,dept dt,emp et
-- where ep.deptno = dt.deptno
-- and ep.job = (select job from emp et where et.ename = '庞统');


select a.*,dt.dname
from (select * from emp where job = (select job from emp where ename = '庞统')) a, dept dt
where a.deptno = dt.deptno;

-- 11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称
select ep.ename,ep.sal,dt.dname from emp ep,dept dt where sal > (select max(sal) from emp ep where deptno = '30') and ep.deptno = dt.deptno;

-- 12.列出每个部门的员工数量、平均工资
select dt.deptno,count(ep.empno),avg(ep.sal)
from dept dt
left join emp ep on dt.deptno = ep.deptno
group by dt.deptno;

-- 13.查出年份、利润、年度增长比

-- year +1 作为条件

SELECT tb1.*,IFNULL(CONCAT((tb1.zz-tb2.zz)/tb2.zz*100,'%'),0) 年度增长比
FROM tbyear tb1 LEFT OUTER JOIN tbyear tb2
ON tb1.`year`=tb2.`year`+1;