AI 助理
备案控制台
开发者社区 数据库 文章 正文

「MySql高级查询与编程」练习：企业员工管理

2022-05-14 238
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： 「MySql高级查询与编程」练习：企业员工管理

题目：企业员工管理


一、语言和环境


1.实现语言：SQL。

2.开发环境：MySQL，navicat for mysql。


二、题目（100分）：


1、创建数据库及数据表：


（1）创建数据库，名称为db_emp；
（2）创建数据表t_emp（员工表）、t_dept（部门表）、t_salary（薪资表）。


表1 t_emp（员工表）


66.png

create table t_emp (
  empid int(11) not null auto_increment,
  empname varchar(30) not null,
  gender char(1) not null,
  age int(4) not null,
  deptid int(6) not null,
  intdate datetime(0) null,
  tel varchar(15) null,
  primary key (empid),
  unique index tel(tel)
);


67.png



create table t_dept (
  id int(11) not null auto_increment,
  deptname varchar(30) not null,
  primary key (id)
);


68.png

create table t_salary  (
  id int(11) not null,
  emp_id int(6) not null,
  salary decimal(8, 2) not null default 0.00,
  bonus decimal(8, 2) null default 0.00,
  primary key (id)
);


（3）添加测试数据：


t_emp（员工表）69.png


insert into `t_emp` values (1001, '张三', '男', 25, 1, '2020-01-01 15:07:59', '13977641234');
insert into `t_emp` values (1002, '李四', '女', 21, 2, '2020-03-10 00:00:00', '14787651234');
insert into `t_emp` values (1003, '王五', '男', 30, 1, '2015-01-01 00:00:00', '15998761234');
insert into `t_emp` values (1004, '赵六', '男', 17, 1, '2018-10-24 00:00:00', '15877931234');
insert into `t_emp` values (1005, '田七', '女', 26, 3, '2019-09-09 00:00:00', '18887641234');
insert into `t_emp` values (1006, '贺老三', '男', 36, 2, '2016-01-15 00:00:00', '17377641790');
insert into `t_emp` values (1007, '高晓苑', '女', 28, 4, '2020-01-02 00:00:00', '15973641230');
insert into `t_emp` values (1008, '吴三', '男', 21, 1, '2022-11-12 00:00:00', '15555115475');


70.png

insert into `t_dept` values (1, '技术部');
insert into `t_dept` values (2, '产品部');
insert into `t_dept` values (3, '运营部');
insert into `t_dept` values (4, '人事部');


71.png


insert into `t_salary` values (1, 1001, 5000.00, 1500.00);
insert into `t_salary` values (2, 1002, 3500.00, 800.00);
insert into `t_salary` values (3, 1003, 4500.00, 2000.00);
insert into `t_salary` values (4, 1004, 6000.00, 4000.00);
insert into `t_salary` values (5, 1005, 5200.00, 1700.00);
insert into `t_salary` values (6, 1006, 4800.00, 3000.00);
insert into `t_salary` values (7, 1007, 3000.00, 1200.00);
insert into `t_salary` values (8, 1008, 12000.00, 3100.00);

2、数据操作：


a) 左连接形式查询入职时间最早的4位员工信息，需要显示员工姓名，入职日期，部门，联系电话。（10分）


72.png

select empName 员工姓名,DATE_FORMAT(intDate,'%Y-%m-%d') 入职日期,deptName 部门,tel 联系电话 
from t_emp e left join t_dept d
 on e.deptid=d.id order by intDate asc limit 0,4;


b) 根据员工部门统计人数、最高基本薪资、最低基本薪资和每个部门的平均基本薪资，并按实际薪资金额升序显示（15分）


73.png


select deptName 部门名称,count(*) 部门人数,max(salary) 最高薪资,min(salary) 最低薪资,avg(salary) 平均薪资 
from t_dept d,t_emp e,t_salary s where e.deptId=d.id and s.emp_id=e.empId 
group by deptName order by (salary+Bonus) asc;

c) 创建视图并使用子查询的方式查询薪资大于“产品部”平均基本薪资的员工信息，要求输出员工姓名和基本薪资。（15分）

74.png

create view view_name as
select empName 姓名,salary 基本薪资 from t_emp e,t_salary s 
where s.emp_id=e.empId and salary > 
all(select avg(salary) from t_dept d,t_emp e,t_salary s where e.deptId=d.id and s.emp_id=e.empId and d.id=2);


也可以通过 navicat 直接创建


75.png

76.png


d) 创建一个名为 proc_Max_salary 的存储过程，其将获取指定部门的最高薪资，例如技术部的最高薪资如下（20分）

77.png


创建存储过程：

delimiter $$
create procedure proc_Max_salary(
  in _depName varchar(20),
  out _salary decimal )
reads sql data
begin
  select max(salary) into _salary 
  from t_dept d,t_emp e,t_salary s 
  where e.deptId=d.id and s.emp_id=e.empId and d.deptName=_depName;
end
$$
delimiter ;


查询：


set @depName= '技术部';
call proc_Max_salary(@depName, @_salary);
select concat(@depName,' 部门的最高的底薪是 ',@_salary,' 元 ');


也可以通过 navicat 直接创建


78.png79.png


e) 创建一个名为v_sum_salary的视图，用于查看出每个员工的总薪资（基本薪资+绩效）。（10分）

80.png

create view v_sum_salary as
select empName 员工姓名,salary 基本薪资,bonus 效绩,(salary+bonus) 最终工资
from t_dept d,t_emp e,t_salary s 
where e.deptId=d.id and s.emp_id=e.empId;


f) 员工信息管理平台浏览员工信息时需要按年龄升序显示，为了提高检索效率，请在在员工表年龄列上创建索引，请写出创建索引的sql语句。（10分）


81.png


alter table t_emp add index index_age(age) using btree;


82.png


3、推荐实现步骤

（1）打开Navicat，创建数据库db_emp;

（2）创建数据表t_emp（员工表）、t_dept（部门表）、t_salary（薪资表），建表时注意主键、外健以及相关约束、类型和长度、并根据以上表格数据进行数据的录入（可用sql语句，也可使用navicat直接填入数据）。

（3）按要求完成查询和修改将sql语句根据相应的备注信息保存查询或视图；sql语句可以外联或子查询，方法不做限定；建立视图可以直接用navicat视图工具也可以写代码创建。



83.png


全部代码：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for t_dept
-- ----------------------------
DROP TABLE IF EXISTS `t_dept`;
CREATE TABLE `t_dept`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deptName` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of t_dept
-- ----------------------------
INSERT INTO `t_dept` VALUES (1, '技术部');
INSERT INTO `t_dept` VALUES (2, '产品部');
INSERT INTO `t_dept` VALUES (3, '运营部');
INSERT INTO `t_dept` VALUES (4, '人事部');
-- ----------------------------
-- Table structure for t_emp
-- ----------------------------
DROP TABLE IF EXISTS `t_emp`;
CREATE TABLE `t_emp`  (
  `empId` int(11) NOT NULL AUTO_INCREMENT,
  `empName` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` int(4) NOT NULL,
  `deptId` int(6) NOT NULL,
  `intDate` datetime(0) NULL DEFAULT NULL,
  `tel` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`empId`) USING BTREE,
  UNIQUE INDEX `tel`(`tel`) USING BTREE,
  INDEX `index_age`(`age`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1009 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of t_emp
-- ----------------------------
INSERT INTO `t_emp` VALUES (1001, '张三', '男', 25, 1, '2020-01-01 15:07:59', '13977641234');
INSERT INTO `t_emp` VALUES (1002, '李四', '女', 21, 2, '2020-03-10 00:00:00', '14787651234');
INSERT INTO `t_emp` VALUES (1003, '王五', '男', 30, 1, '2015-01-01 00:00:00', '15998761234');
INSERT INTO `t_emp` VALUES (1004, '赵六', '男', 17, 1, '2018-10-24 00:00:00', '15877931234');
INSERT INTO `t_emp` VALUES (1005, '田七', '女', 26, 3, '2019-09-09 00:00:00', '18887641234');
INSERT INTO `t_emp` VALUES (1006, '贺老三', '男', 36, 2, '2016-01-15 00:00:00', '17377641790');
INSERT INTO `t_emp` VALUES (1007, '高晓苑', '女', 28, 4, '2020-01-02 00:00:00', '15973641230');
INSERT INTO `t_emp` VALUES (1008, '吴三', '男', 21, 1, '2022-11-12 00:00:00', '15555115475');
-- ----------------------------
-- Table structure for t_salary
-- ----------------------------
DROP TABLE IF EXISTS `t_salary`;
CREATE TABLE `t_salary`  (
  `id` int(11) NOT NULL,
  `emp_id` int(6) NOT NULL,
  `salary` decimal(8, 2) NOT NULL DEFAULT 0.00,
  `Bonus` decimal(8, 2) NULL DEFAULT 0.00,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of t_salary
-- ----------------------------
INSERT INTO `t_salary` VALUES (1, 1001, 5000.00, 1500.00);
INSERT INTO `t_salary` VALUES (2, 1002, 3500.00, 800.00);
INSERT INTO `t_salary` VALUES (3, 1003, 4500.00, 2000.00);
INSERT INTO `t_salary` VALUES (4, 1004, 6000.00, 4000.00);
INSERT INTO `t_salary` VALUES (5, 1005, 5200.00, 1700.00);
INSERT INTO `t_salary` VALUES (6, 1006, 4800.00, 3000.00);
INSERT INTO `t_salary` VALUES (7, 1007, 3000.00, 1200.00);
INSERT INTO `t_salary` VALUES (8, 1008, 12000.00, 3100.00);
-- ----------------------------
-- View structure for view_name
-- ----------------------------
DROP VIEW IF EXISTS `view_name`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY DEFINER VIEW `view_name` AS select `e`.`empName` AS `姓名`,`s`.`salary` AS `基本薪资` from (`t_emp` `e` join `t_salary` `s`) where ((`s`.`emp_id` = `e`.`empId`) and `s`.`salary` > all (select avg(`s`.`salary`) from ((`t_dept` `d` join `t_emp` `e`) join `t_salary` `s`) where ((`e`.`deptId` = `d`.`id`) and (`s`.`emp_id` = `e`.`empId`) and (`d`.`id` = 2))));
-- ----------------------------
-- View structure for v_sum_salary
-- ----------------------------
DROP VIEW IF EXISTS `v_sum_salary`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY DEFINER VIEW `v_sum_salary` AS select `e`.`empName` AS `员工姓名`,`s`.`salary` AS `基本薪资`,`s`.`Bonus` AS `效绩`,(`s`.`salary` + `s`.`Bonus`) AS `最终工资` from ((`t_dept` `d` join `t_emp` `e`) join `t_salary` `s`) where ((`e`.`deptId` = `d`.`id`) and (`s`.`emp_id` = `e`.`empId`));
-- ----------------------------
-- Procedure structure for proc_Max_salary
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_Max_salary`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_Max_salary`(
in _depName varchar(20),
out _salary decimal )
    READS SQL DATA
begin
select max(salary) into _salary from t_dept d,t_emp e,t_salary s where e.deptId=d.id and s.emp_id=e.empId and d.deptName=_depName;
end
;;
delimiter ;
SET FOREIGN_KEY_CHECKS = 1;



文章标签：
云数据库 RDS MySQL 版
SQL
关系型数据库
索引
MySQL
数据库
存储
关键词：
云数据库 RDS MySQL 版查询
云数据库 RDS MySQL 版企业
云数据库 RDS MySQL 版编程
云数据库 RDS MySQL 版管理
云数据库 RDS MySQL 版高级查询
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
明金同学
目录
相关文章
sunrr
|
1月前
|
存储 关系型数据库 MySQL
提高MySQL查询性能的方法有很多
提高MySQL查询性能的方法有很多
sunrr
145 7
sunrr
|
1月前
|
存储 关系型数据库 MySQL
提高MySQL的查询性能
提高MySQL的查询性能
sunrr
66 4
Star时光
|
17天前
|
存储 关系型数据库 MySQL
MySQL在企业内部应用场景有哪些
【10月更文挑战第17天】MySQL在企业内部应用场景有哪些
Star时光
26 0
刘大猫.
|
2天前
|
SQL 前端开发 关系型数据库
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
刘大猫.
29 9
vohelon
|
4天前
|
缓存 监控 关系型数据库
如何优化MySQL查询速度？
如何优化MySQL查询速度？【10月更文挑战第31天】
vohelon
15 3
肾透侧视攻城狮
|
9天前
|
SQL NoSQL 关系型数据库
2024Mysql And Redis基础与进阶操作系列（5）作者——LJS[含MySQL DQL基本查询:select；简单、排序、分组、聚合、分组、分页等详解步骤及常见报错问题所对应的解决方法]
MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页、INSERT INTO SELECT / FROM查询结合精例等详解步骤及常见报错问题所对应的解决方法
肾透侧视攻城狮
33 5
技能实验室
|
14天前
|
SQL Java 关系型数据库
java连接mysql查询数据(基础版，无框架)
【10月更文挑战第12天】该示例展示了如何使用Java通过JDBC连接MySQL数据库并查询数据。首先在项目中引入`mysql-connector-java`依赖，然后通过`JdbcUtil`类中的`main`方法实现数据库连接、执行SQL查询及结果处理，最后关闭相关资源。
技能实验室
55 6
刘大猫.
|
10天前
|
SQL 关系型数据库 MySQL
定时任务频繁插入数据导致锁表问题 -＞ 查询mysql进程
定时任务频繁插入数据导致锁表问题 -＞ 查询mysql进程
刘大猫.
25 1
Dylaniou
|
23天前
|
Java 关系型数据库 MySQL
【编程基础知识】Eclipse连接MySQL 8.0时的JDK版本和驱动问题全解析
本文详细解析了在使用Eclipse连接MySQL 8.0时常见的JDK版本不兼容、驱动类错误和时区设置问题，并提供了清晰的解决方案。通过正确配置JDK版本、选择合适的驱动类和设置时区，确保Java应用能够顺利连接MySQL 8.0。
Dylaniou
103 1
土木林森
|
8天前
|
监控 关系型数据库 MySQL
数据库优化：MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型，以及如何创建和维护索引，结合实战案例分析查询执行计划，帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
土木林森
40 0

热门文章

最新文章

  • 1
    测试开启MySQL performance_schema后对性能的影响
  • 2
    MySQL调优之服务器参数优化实践
  • 3
    Mysql5注射技巧总结
  • 4
    mysql左关联不走索引的一个问题的排查过程及思考
  • 5
    应用MySQL MGR
  • 6
    mysql修改用户密码
  • 7
    如何修改WAMP中mysql默认空密码
  • 8
    MySQL-5.5.33主从复制
  • 9
    mysql的总结6--视图的学习
  • 10
    删除MySQL重复数据
  • 1
    MySQL上亿数据查询优化：实践与技巧
    275
  • 2
    如何理解Mysql的索引及他们的原理--------二叉查找树和平衡二叉树和B树和B+树
    46
  • 3
    MySQL：union all与union区别详解
    85
  • 4
    学习mysql中使用inner join，left join 等
    47
  • 5
    【最佳实践】MySQL数据库迁移到PXC集群
    85
  • 6
    MySQL PXC集群配置IPv6
    45
  • 7
    【实操记录】MySQL主从配置
    131
  • 8
    【实操记录】MySQL二进制安装包部署
    176
  • 9
    mysql模糊查询指定根据第几个字符来匹配
    225
  • 10
    mysql使用 CONCAT(字段,字段) 函数拼接
    59

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布
  • 阿里云企业级自治数据库 RDS 详解
  • 阿里云MySQL云数据库产品体系介绍

    • 相关实验场景

    更多
  • 灵活弹性的PolarDB Serverless确保数据业务持续在线
  • 云端MySQL的安装及应用
  • 体验PolarDB MySQL多主集群
  • 基于ECS+RDS的在线留言系统
  • RDS MySQL的SQL问题诊断与调优
  • RDS MySQL Serverless资源与负载自适应

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    DataWorks售前咨询