最全MySQL8.0实战教程 11 MySQL的多表操作 11.2 多表联合查询【10 练习】-蒲公英云

最全MySQL8.0实战教程

文章目录

- - 最全MySQL8.0实战教程
  - 11 MySQL的多表操作
  - - 11.2 多表联合查询
    - - 11.2.10 练习

【黑马程序员MySQL知识精讲+mysql实战案例_零基础mysql数据库入门到高级全套教程】

11 MySQL的多表操作

11.2 多表联合查询

11.2.10 练习

【数据准备】

-- 创建test1数据库
create database test1;
-- 选择使用test1数据库
use test1;
-- 创建部门表
create table dept(
    deptno int primary key, -- 部门编号
    dname varchar(14), -- 部门名称
    loc varchar(13) -- 部门地址
);
-- 插入部门数据
insert into dept values(10,'accounting','new york');
insert into dept values(20,'research','dallas');
insert into dept values(30,'sales','chicago');
insert into dept values(40,'operations','boston');
-- 创建员工表
create table emp(
    empno int primary key, -- 员工编号
    ename varchar(10), -- 员工姓名
    job varchar(9), -- 员工工作
    mgr int, -- 员工直属领导编号
    hiredate date, -- 入职时间
    sal double, -- 工资
    comm double, -- 奖金
    deptno int -- 对应dept表的外键
);
-- 添加 部门 和 员工 之间的主外键关系
alter table emp add constraint foreign key emp(deptno) references dept(deptno);
-- 添加员工信息
INSERT INTO `emp` VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO `emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
INSERT INTO `emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
INSERT INTO `emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO `emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO `emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);
INSERT INTO `emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);
INSERT INTO `emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20);
INSERT INTO `emp` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO `emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
INSERT INTO `emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20);
INSERT INTO `emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);
INSERT INTO `emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO `emp` VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);
-- 创建工资等级表
create table salgrade(
    grade int, -- 等级
    losal double, -- 最低工资
    hisal double -- 最高工资
);
-- 插入工资等级数据
insert into salgrade values(1,700,1200);
insert into salgrade values(2,1201,1400);
insert into salgrade values(3,1401,2000);
insert into salgrade values(4,2001,3000);
insert into salgrade values(5,3001,9999);

在这里插入图片描述

表间关系为：

在这里插入图片描述

【操作】

-- 练习
-- 返回拥有员工的部门名、部门号
select distinct d.dname,d.deptno from dept d inner join emp e on d.deptno = e.deptno;

在这里插入图片描述

-- 工资水平多于SMITH的员工数量
select * from emp where sal > (select sal from emp where ename = 'SMITH');

在这里插入图片描述

-- 返回员工和所属经历的姓名
select a.ename, b.ename from emp a , emp b where a.mgr = b.empno;

在这里插入图片描述

-- 返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名
select a.ename,a.hiredate,b.ename,b.hiredate from emp a join emp b on a.mgr = b.empno and a.hiredate < b.hiredate;

在这里插入图片描述

-- 返回员工姓名及其所在的部门名称
select a.ename, b.dname from emp a join dept b on a.deptno = b.deptno;

在这里插入图片描述

-- 返回从事clerk工作的员工姓名和所在部门名称
select a.ename, b.dname,a.job from emp a join dept b on a.deptno = b.deptno and job = 'CLERK';

在这里插入图片描述

-- 返回部门号及其本部门的最低工资
select deptno, min(sal) from emp group by deptno;

在这里插入图片描述

-- 返回销售部(sales) 所有员工的姓名
select b.ename from dept a join emp b on a.deptno = b.deptno and a.dname = 'sales';

在这里插入图片描述

-- 返回工资水平多于平均工资的员工
select * from emp where sal > (select avg(sal) from emp);

在这里插入图片描述

-- 返回与Scott从事相同工作的员工
select * from emp where job = (select job from emp where ename = 'SCOTT') and ename != 'SCOTT';

在这里插入图片描述

-- 返回工资高于30部门所有员工工资水平的员工信息
select * from emp where sal > all(select sal from emp where deptno = 30);

在这里插入图片描述

-- 返回员工工作及其从事此工作的最低工资
select job,min(sal) from emp group by job;

在这里插入图片描述

-- 计算出员工的年薪，并且以年薪排序
select ename,(sal*12 + ifnull(comm,0)) as year_sal from emp order by year_sal desc;

在这里插入图片描述

-- 返回工资处于第四级别的员工的姓名
select ename from emp
where sal between (select losal from salgrade where grade = 4) and (select hisal from salgrade where grade = 4);

在这里插入图片描述

-- 返回工资为第二等级的职员姓名、部门所在地
select
b.ename,a.loc
from dept a
join emp b on a.deptno = b.deptno
join salgrade c on grade = 2 and b.sal >= c.losal and b.sal <= c.hisal;