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

冷不防 2024-03-26 12:59 22阅读 0赞

#### 最全MySQL8.0实战教程 ####

#### 文章目录 ####

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

[【黑马程序员MySQL知识精讲+mysql实战案例\_零基础mysql数据库入门到高级全套教程】 ][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);

![在这里插入图片描述][4d9929504c5c444fb244c3b27104f038.png_pic_center]

表间关系为：

![在这里插入图片描述][410833682b4241e497698f7631676f5d.png_pic_center]

【操作】

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

![在这里插入图片描述][3be50f5707c14a6889993a0395cdf0fd.png_pic_center]

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

![在这里插入图片描述][0e2b0e7d476d4df389585f6db9fbe22f.png_pic_center]

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

![在这里插入图片描述][ccd1181b817f44e28e49169fe1f2c2a4.png_pic_center]

-- 返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名
    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;

![在这里插入图片描述][d5ce2e03bfd943b3b2c4c4bd255e0888.png_pic_center]

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

![在这里插入图片描述][bdc173b949fc4a81a8841b594b47b978.png_pic_center]

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

![在这里插入图片描述][0283b0fb0a76458dbf5cfd4d70da8673.png_pic_center]

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

![在这里插入图片描述][9743a4bf9f264f97b41857b814c850ba.png_pic_center]

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

![在这里插入图片描述][b8cb3cba4ee049aa9d4adb4b718f3cd0.png_pic_center]

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

![在这里插入图片描述][b243f018c790464ba17758ee359bc1f7.png_pic_center]

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

![在这里插入图片描述][229dfe3d3c154e118a88a94d362972dc.png_pic_center]

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

![在这里插入图片描述][b8abb0787d684b78acc69f1c59d3f49c.png_pic_center]

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

![在这里插入图片描述][6ac291f9c83c4e6bb4ff61710d4c98ee.png_pic_center]

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

![在这里插入图片描述][aae7a9d5d063407e86137c8d53355c38.png_pic_center]

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

![在这里插入图片描述][b50427a04ad04200885a651c4cebc658.png_pic_center]

-- 返回工资为第二等级的职员姓名、部门所在地
    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;

![在这里插入图片描述][ec1c77c505f04235bf3d228b50b0820d.png_pic_center]

[MySQL_mysql_mysql_]: https://www.bilibili.com/video/BV1iF411z7Pu
[4d9929504c5c444fb244c3b27104f038.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/21e2b3eb44664316821a278b4bb89e36.png
[410833682b4241e497698f7631676f5d.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/7850c77ddd4a46279756605962359cf0.png
[3be50f5707c14a6889993a0395cdf0fd.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/deddaa1190a4401f93af589286c7eb61.png
[0e2b0e7d476d4df389585f6db9fbe22f.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/a88b0687fbf649f3863dfb7117e4713c.png
[ccd1181b817f44e28e49169fe1f2c2a4.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/f0591e71215b43a581594da4268d4b2a.png
[d5ce2e03bfd943b3b2c4c4bd255e0888.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/2d0f7f5d8f0146d39f7e12864b17e939.png
[bdc173b949fc4a81a8841b594b47b978.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/035583010a444f8592635e58ae18deb0.png
[0283b0fb0a76458dbf5cfd4d70da8673.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/66c7ca75b3ad4df88311858e3de66bb3.png
[9743a4bf9f264f97b41857b814c850ba.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/c6acaef7a6834f41a3e2a5b1664c36a1.png
[b8cb3cba4ee049aa9d4adb4b718f3cd0.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/b8093f5895714dbb8b132d0a4c38edab.png
[b243f018c790464ba17758ee359bc1f7.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/e75fe809d4af4b7bb5d91c0243ab235d.png
[229dfe3d3c154e118a88a94d362972dc.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/34dea60dd2554a11a8cd472e4ad1b61f.png
[b8abb0787d684b78acc69f1c59d3f49c.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/c74daaa784024e94b25baa3298d7a649.png
[6ac291f9c83c4e6bb4ff61710d4c98ee.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/3970f6a52f504303861862a0f43f14db.png
[aae7a9d5d063407e86137c8d53355c38.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/f0523fa4e3f34c0fa3b19e115b7ff24b.png
[b50427a04ad04200885a651c4cebc658.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/b53d4ae7edba44cdbabb331b423d1f2d.png
[ec1c77c505f04235bf3d228b50b0820d.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/454e50af07c84598a187ba5fc66526bc.png