MySQL 多表查询练习题

已于 2023-11-16 09:49:00 修改
阅读量538 收藏 7
点赞数 4
分类专栏： 数据库原理和应用 文章标签： 数据库 mysql
于 2023-10-26 15:03:25 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2301_76294113/article/details/134042985
版权

多表查询

f48f3f07d36b4baa969a3b3c3dca196c.jpg

一、实验目的

1、 掌握外键的概念，使用SELECT语句实现多表查询；
2、 掌握子查询、聚合函数的设计；
3、 掌握连接查询的设计等。

 

二、实验环境

操作系统：Windows11 

应用软件：MySQL   Navicat

 

三、实验内容

【壹】创建下面练习题中的四个表，分别为：

①dept表（部门表：部门编号，部门名称，部门位置）

5fa664bf6e85473c9229e915ea1cf670.jpeg

 

②emp表（员工表：工号，姓名，职位，上级领导工号，入职日期，工资，部门编号）

8e224439cf024dc0949afe7ba70bdf3d.jpeg

 

③salgrade表（工资等级：等级，最低工资，最高工资）

7fc42714e72b4e839416bde7671a7fa6.png

 

④tbyear表（公司利润表：年份，利润）

a69708281bef498b8a24bd886475d6ad.png

 

【贰】请使用SQL命令完成以下操作：

 

1. 查出至少有一个员工的部门，显示部门编号、部门名称、部门位置、部门人数。

 

2. 列出薪金比关羽高的所有员工。

 

3. 列出所有员工的姓名及其直接上级的姓名。

 

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

 

5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。 

 

6. 列出所有文员的姓名及其部门名称，部门的人数。

 

7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。

 

8. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。

 

9. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。

 

10.列出与庞统从事相同工作的所有员工及部门名称。

 

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

 

12.列出每个部门的员工数量、平均工资。

 

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

 

四、实验步骤和运行结果

【Ⅰ】在使用SQL命令之前，需要进行的操作有：

（1）创建数据库deep

CREATE DATABASE deep;
USE deep;

（2）分别创建四个表

-- 部门表
CREATE TABLE dept (
deptno INT PRIMARY KEY auto_increment,-- 部门编号
dname VARCHAR ( 14 ), -- 部门名字
loc VARCHAR ( 13 ) -- 地址
);

-- 员工表
create table emp(
	empno int primary key auto_increment,-- 员工编号
	ename varchar(10), -- 员工姓名										-
	job varchar(9),	-- 岗位
	mgr int,	 -- 直接领导编号
	hiredate date, -- 雇佣日期，入职日期
	sal int, -- 薪水
	comm int,  -- 提成
	deptno int not null, -- 部门编号
	foreign key (deptno) references dept(deptno)
);

-- 工资等级表
create table salgrade(
  grade INT,  -- 等级
  losal DECIMAL(10, 2),  -- 最低工资
  hisal DECIMAL(10, 2)  -- 最高工资 
);

-- 公司利润表
create table tbyear (
  year INT,-- 年份
  zz DECIMAL(10, 2) -- 利润
);

（3）为这四张表添加数据

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

INSERT INTO emp VALUES ('1001', '甘宁', '文员', '1013', '2000-12-17', '8000.00', null, '20');
INSERT INTO emp VALUES ('1002', '黛绮丝', '销售员', '1006', '2001-02-20', '16000.00', '3000.00', '30');
INSERT INTO emp VALUES ('1003', '殷天正', '销售员', '1006', '2001-02-22', '12500.00', '5000.00', '30');
INSERT INTO emp VALUES ('1004', '刘备', '经理', '1009', '2001-04-02', '29750.00', null, '20');
INSERT INTO `emp` VALUES ('1005', '谢逊', '销售员', '1006', '2001-09-28', '12500.00', '14000.00', '30');
INSERT INTO emp VALUES ('1006', '关羽', '经理', '1009', '2001-05-01', '28500.00', null, '30');
INSERT INTO `emp` VALUES ('1007', '张飞', '经理', '1009', '2001-09-01', '24500.00', null, '10');
INSERT INTO emp VALUES ('1008', '诸葛亮', '分析师', '1004', '2007-09-01', '30000.00', null, '20');
INSERT INTO emp VALUES ('1009', '曾阿牛', '董事长', null, '2001-11-17', '50000.00', null, '10');
INSERT INTO emp VALUES ('1010', '韦一笑', '销售员', '1006', '2001-09-08', '15000.00', '0.00', '30');
INSERT INTO emp VALUES ('1011', '周泰', '文员', '1008', '2007-05-28', '11000.00', null, '20');
INSERT INTO emp VALUES ('1012', '程普', '文员', '1006', '2001-12-03', '9500.00', null, '30');
INSERT INTO emp VALUES ('1013', '庞统', '分析师', '1004', '2001-12-09', '30000.00', null, '20');
INSERT INTO emp VALUES ('1014', '黄盖', '文员', '1007', '2002-01-23', '13000.00', null, '10');
INSERT INTO emp VALUES ('1015', '张三', '保洁员', '1009', '1999-09-09', '80000.00', '90000.00', '50');

INSERT INTO salgrade VALUES ('1', '7000', '12000');
INSERT INTO salgrade VALUES ('2', '12000', '14000');
INSERT INTO salgrade VALUES ('3', '14000', '20000');
INSERT INTO salgrade VALUES ('4', '20000', '30000');
INSERT INTO salgrade VALUES ('5', null, '90000');


INSERT INTO tbyear VALUES ('2010', '100');
INSERT INTO tbyear VALUES ('2011', '150');
INSERT INTO tbyear VALUES ('2012', '250');
INSERT INTO tbyear VALUES ('2013', '800');
INSERT INTO tbyear VALUES ('2014', '1000');

【Ⅱ】创建完成之后，接下来使用SQL命令完成下列操作：

--1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。

SELECT d.deptno,d.dname,d.loc,e1.`count(*)`

FROM dept d INNER JOIN (SELECT deptno,COUNT(*) FROM emp GROUP BY deptno) e1

ON d.deptno=e1.deptno;

84588abe218b4ac781af88eacd1b60fc.png

 

-- 2. 列出薪金比关羽高的所有员工。

SELECT *

FROM emp e

WHERE e.sal>(SELECT sal FROM emp WHERE ename='关羽');

c5f57873d5e447ccbaefd46c0c151125.png

 

-- 3. 列出所有员工的姓名及其直接上级的姓名。

SELECT e1.ename,e2.ename 上级

FROM emp e1 LEFT OUTER JOIN emp e2

ON e1.mgr=e2.empno;

0d68ea85cf0a48488a13b13bc3a1667d.png

 

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

SELECT  e1.empno,e1.ename,d.dname

FROM emp e1 LEFT OUTER JOIN emp e2 ON e1.mgr=e2.empno 

LEFT OUTER JOIN dept d ON e1.deptno=d.deptno

WHERE e1.hiredate<e2.hiredate;

77e88bf3e20c4d6e8d1425d031f40558.png

 

-- 5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

SELECT d.dname,e.*

FROM dept d LEFT OUTER JOIN emp e

ON d.deptno=e.deptno;

b42de4aa5d0d48328fecf8d74ff3cee5.png

 

-- 6. 列出所有文员的姓名及其部门名称，部门的人数

SELECT e.ename,d.dname,z.`count(*)`

FROM emp e INNER JOIN dept d ON e.deptno=d.deptno

INNER JOIN (SELECT deptno,COUNT(*) FROM emp GROUP BY deptno)z ON z.deptno=d.deptno

WHERE e.job='文员';

8bc05643f6824fb08683bf1a34af88d7.png

 

-- 7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数

SELECT job,COUNT(*)

FROM emp

GROUP BY job

HAVING MIN(sal)>15000;

ce1ae89a352549449774377f9c9aee6f.png

 

-- 8. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。

SELECT e1.ename

FROM emp e1 INNER JOIN dept d

ON e1.deptno=d.deptno

WHERE d.dname='销售部';

75827b6b5cfc46c8b25d6df80f638ab1.png

 

-- 9. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。

SELECT e1.*,e2.ename 上级,d.dname 部门名称,sal.`grade`

FROM emp e1 LEFT OUTER JOIN emp e2 ON e1.mgr=e2.empno

            LEFT OUTER JOIN dept d ON e1.deptno=d.deptno

            LEFT OUTER JOIN salgrade sal ON e1.sal BETWEEN losal AND hisal

WHERE e1.`sal`>(SELECT AVG(sal) FROM emp);

4c0237c6d95c47458e87af718aa1c021.png

 

-- 10.列出与庞统从事相同工作的所有员工及部门名称。

SELECT e.ename,d.dname

FROM emp e INNER JOIN dept d

ON e.deptno=d.deptno

WHERE e.job=(SELECT job FROM emp WHERE ename='庞统');

e682ff76b69347aaa6482cf34cf68ca2.png

 

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

SELECT e.ename,e.sal,d.dname

FROM emp e LEFT OUTER JOIN dept d

ON e.deptno=d.deptno

WHERE e.sal>ALL(SELECT sal FROM emp WHERE deptno=30);

8fef0674ce05429e8570cbe77a1f5afe.png

 

-- 12.列出每个部门的员工数量、平均工资。

SELECT d.dname,e1.*

FROM (SELECT e.deptno,COUNT(*),AVG(sal) FROM emp e GROUP BY e.deptno)e1 INNER JOIN dept d

ON e1.deptno=d.deptno;

a43c4ab0ac8e47818c5b4aefcf890d00.png

 

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

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;

b90dd144b59c400588cd1872a44c9ef6.png

 

 

 

 

噗-噗
关注
  • 4
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
mysql练习题
05-25
mysql简单练习题，这是 非常简单的练习题mysql简单练习题，这是 非常简单的练习题mysql简单练习题，这是 非常简单的练习题mysql简单练习题，这是 非常简单的练习题
MySQL【3】多表查询（练习1）
巴尔渣克
09-04 1366
部门表 CREATE TABLE dept ( id INT PRIMARY KEY PRIMARY KEY, -- 部门id dname VARCHAR(50), -- 部门名称 loc VARCHAR(50) -- 部门所在地 ); ...
MySQL 多表查询练习
最新发布
Kr__z的博客
07-14 316
3.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息。15.从student表和score表中查询出学生的学号，然后合并查询结果。17.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩。4.从student表中查询计算机系和英语系的学生的信息。16.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩。5.从student表中查询年龄18~22岁的学生信息。13.查询同时参加计算机和英语考试的学生的信息。9.用连接的方式查询所有学生的信息和考试信息。
MySQL多表查询练习
weixin_45826228的博客
06-06 201
4张表,6个需求,完整的解决思路,逻辑分析+具体SQL语句实现. 本来打算单个写,不过有明确的注释 有注释,能完整阅读代码 -- 部门表 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,'教研部','北京'),
MySQL多表查询练习题原数据
08-18
练习题答案解决思路请查看本人原文章MySQL数据库多表查询练习题
MySQL单表查询练习
12-07
MySQL单表查询练习题
mysql查询练习题
12-11
一份关于mysql的题目，查询相关的练习题，一表，两表，多表等的连接查询。
mysql 多表联接查询练习
10-31
mysql,多表联接查询练习,mysql,多表联接内联,外联练习。 mysql,多表联接查询练习,mysql,多表联接内联,外联练习。
MySQL中DQL练习题答案.md
01-03
该内容是本人博客练习sql使用的原素材，主要有：创建sql语句和插入大量数据。让你将更多的精力放在sql语句的其他学习方向上，而不再需要自己做创建sql语句和插入大量数据这样既浪费时间又毫无技术含量的工作。
MYSQL8.0练习题表清单和关联关系
04-02
用户MySQL8.0练习的表清单和关联关系图
MySQL常用练手题目集合 MySQL查询练习题（50题） 共18页.pdf
08-21
MySQL 查询练习（50题） MySQL 查询练习（50题） I 1 -- 建表 1 1.1 -- 学生表 1 1.2 -- 课程表 1 1.3 -- 教师表 1 1.4 -- 成绩表 2 1.5 -- 插入学生表测试数据 2 1.6 -- 课程表测试数据 2 1.7 -- 教师表测试数据 2 ...
MySQL单表练习题及其答案.md
01-02
MySQL单表练习题及其答案 该内容是本人博客练习sql使用的原素材，主要有：创建sql语句和插入大量数据。让你将更多的精力放在sql语句的其他学习方向上，而不再需要自己做创建sql语句和插入大量数据这样既浪费时间又...
员工部门数据库
diaopitian0181的博客
06-21 2343
1 -- 4张表 员工 部门 薪资等级 工作 2 -- 删除全部表，原则：被引用的表不能直接删除。先删从表，再删主表。 3 drop table if exists emp; 4 drop table if exists dept; 5 drop table if exists job; 6 drop table if exists salary...
MySQL查询语句
weixin_30500663的博客
01-08 150
来源于网络... Sutdent表的定义 字段名 字段描述 数据类型 主键 外键 非空 唯一 自增 Id 学号 INT(10) 是 否 是 是 是 Name 姓名 VARCHAR...
mysql多表查询练习题
05-16
好的，以下是一些MySQL多表查询的练习题： 1. 查询订单表中所有订单的详细信息，包括订单号、下单时间、客户名、客户电话、商品名、商品单价、商品数量、商品总价，并按照订单号升序排列。 ``` SELECT o.order_id, o.order_time, c.customer_name, c.customer_phone, p.product_name, p.product_price, od.quantity, od.total_price FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN order_details od ON o.order_id = od.order_id JOIN products p ON p.product_id = od.product_id ORDER BY o.order_id ASC; ``` 2. 查询所有客户的订单总数和订单总金额，并按照订单总金额降序排列。 ``` SELECT c.customer_name, COUNT(o.order_id) AS order_count, SUM(od.total_price) AS total_amount FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id LEFT JOIN order_details od ON o.order_id = od.order_id GROUP BY c.customer_name ORDER BY total_amount DESC; ``` 3. 查询订单表中所有订单的商品种类数和商品总数量，并按照订单总数量降序排列。 ``` SELECT o.order_id, COUNT(DISTINCT od.product_id) AS product_count, SUM(od.quantity) AS total_quantity FROM orders o JOIN order_details od ON o.order_id = od.order_id GROUP BY o.order_id ORDER BY total_quantity DESC; ``` 希望这些练习题对你有所帮助！

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

噗-噗

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值