[大数据] mysql多表查询练习

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> mysql多表查询练习 -> 正文阅读

[大数据]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;