MySQL 实例employee表综合查询

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: MySQL 实例employee表综合查询



表关系图:

# 创建表
create table employee
(
empno int NOT NULL AUTO_INCREMENT,
ename varchar(20) NULL,
job varchar(20) NULL,
mgr int NULL,
hiredate date NULL,
sal decimal(10,2) NULL,
comm decimal(10,2) NULL,
deptno int NULL,
primary key (empnos)
)engine=InnoDB;

例题:

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

mysql> SELECT   
    ->     d.deptno,   
    ->     d.dname,   
    ->     d.loc,   
    ->     COUNT(e.empno) AS dept_count  
    -> FROM   
    ->     department d  
    -> JOIN   
    ->     employee e ON d.deptno = e.deptno  
    -> GROUP BY   
    ->     d.deptno, d.dname, d.loc  
    -> HAVING   
    ->     COUNT(e.empno) > 0;
+--------+-----------+--------+------------+
| deptno | dname     | loc    | dept_count |
+--------+-----------+--------+------------+
|     20 | 学工部    | 上海   |          5 |
|     30 | 销售部    | 广州   |          6 |
|     10 | 教研部    | 北京   |          3 |
+--------+-----------+--------+------------+
3 rows in set (0.01 sec)

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

mysql> SELECT   
    ->     e1.ename AS employee_name,   
    ->     e2.ename AS manager_name  
    -> FROM   
    ->     employee e1  
    -> LEFT JOIN   
    ->     employee e2 ON e1.mgr = e2.empno;
+---------------+--------------+
| employee_name | manager_name |
+---------------+--------------+
| 甘宁          | 庞统         |
| 黛琦丝        | 关羽         |
| 殷天正        | 关羽         |
| 刘备          | 曾阿牛       |
| 谢逊          | 关羽         |
| 关羽          | 曾阿牛       |
| 张飞          | 曾阿牛       |
| 诸葛亮        | 刘备         |
| 曾阿牛        | NULL         |
| 韦一笑        | 关羽         |
| 周泰          | 诸葛亮       |
| 程普          | 关羽         |
| 庞统          | 刘备         |
| 黄盖          | 张飞         |
+---------------+--------------+
14 rows in set (0.01 sec)

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

mysql> SELECT   
    ->     e1.empno,   
    ->     e1.ename,   
    ->     d.dname  
    -> FROM   
    ->     employee e1  
    -> JOIN   
    ->     employee e2 ON e1.mgr = e2.empno  
    -> JOIN   
    ->     department d ON e1.deptno = d.deptno  
    -> WHERE   
    ->     e1.hiredate < e2.hiredate;
+-------+-----------+-----------+
| empno | ename     | dname     |
+-------+-----------+-----------+
|  1001 | 甘宁      | 学工部    |
|  1002 | 黛琦丝    | 销售部    |
|  1003 | 殷天正    | 销售部    |
|  1004 | 刘备      | 学工部    |
|  1006 | 关羽      | 销售部    |
|  1007 | 张飞      | 教研部    |
+-------+-----------+-----------+
6 rows in set (0.00 sec)

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

mysql> SELECT   
    ->     d.deptno,   
    ->     d.dname,   
    ->     e.empno,   
    ->     e.ename,   
    ->     e.job,   
    ->     e.mgr,   
    ->     e.hiredate,   
    ->     e.sal,   
    ->     e.comm  
    -> FROM   
    ->     department d  
    -> LEFT JOIN   
    ->     employee e ON d.deptno = e.deptno  
    -> ORDER BY   
    ->     d.deptno, e.empno;
+--------+-----------+-------+-----------+-----------+------+------------+----------+----------+
| deptno | dname     | empno | ename     | job       | mgr  | hiredate   | sal      | comm     |
+--------+-----------+-------+-----------+-----------+------+------------+----------+----------+
|     10 | 教研部    |  1007 | 张飞      | 经理      | 1009 | 2001-09-01 | 24500.00 | NULL     |
|     10 | 教研部    |  1009 | 曾阿牛    | 董事长    | NULL | 2001-11-17 | 50000.00 | NULL     |
|     10 | 教研部    |  1014 | 黄盖      | 文员      | 1007 | 2002-01-23 | 13000.00 | NULL     |
|     20 | 学工部    |  1001 | 甘宁      | 文员      | 1013 | 2000-12-17 | 8000.00  | NULL     |
|     20 | 学工部    |  1004 | 刘备      | 经理      | 1009 | 2001-04-02 | 29750.00 | NULL     |
|     20 | 学工部    |  1008 | 诸葛亮    | 分析师    | 1004 | 2007-04-19 | 30000.00 | NULL     |
|     20 | 学工部    |  1011 | 周泰      | 文员      | 1008 | 2007-05-23 | 11000.00 | NULL     |
|     20 | 学工部    |  1013 | 庞统      | 分析师    | 1004 | 2001-12-03 | 30000.00 | NULL     |
|     30 | 销售部    |  1002 | 黛琦丝    | 销售员    | 1006 | 2001-02-20 | 16000.00 | 3000.00  |
|     30 | 销售部    |  1003 | 殷天正    | 销售员    | 1006 | 2001-02-22 | 12500.00 | 5000.00  |
|     30 | 销售部    |  1005 | 谢逊      | 销售员    | 1006 | 2001-09-28 | 12500.00 | 14000.00 |
|     30 | 销售部    |  1006 | 关羽      | 经理      | 1009 | 2001-05-01 | 28500.00 | NULL     |
|     30 | 销售部    |  1010 | 韦一笑    | 销售员    | 1006 | 2001-09-08 | 15000.00 | 0.00     |
|     30 | 销售部    |  1012 | 程普      | 文员      | 1006 | 2001-12-03 | 9500.00  | NULL     |
|     40 | 财务部    |  NULL | NULL      | NULL      | NULL | NULL       | NULL     | NULL     |
+--------+-----------+-------+-----------+-----------+------+------------+----------+----------+
15 rows in set (0.00 sec)

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

mysql> SELECT  job,count(*) as 人数 
    —> from employee 
    —> where sal > 15000 or sal+comm > 15000 
    —> group by job;
+-----------+--------+
| job       | 人数   |
+-----------+--------+
| 销售员    |      3 |
| 经理      |      3 |
| 分析师    |      2 |
| 董事长    |      1 |
+-----------+--------+
4 rows in set (0.01 sec)

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

mysql> SELECT   
    ->     e.ename  
    -> FROM   
    ->     employee e  
    -> JOIN   
    ->     department d ON e.deptno = d.deptno  
    -> WHERE   
    ->     d.dname = '销售部';
+-----------+
| ename     |
+-----------+
| 黛琦丝    |
| 殷天正    |
| 谢逊      |
| 关羽      |
| 韦一笑    |
| 程普      |
+-----------+
6 rows in set (0.00 sec)

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

mysql> SELECT   
    ->     e.empno,   
    ->     e.ename,   
    ->     d.dname,   
    ->     e.mgr,   
    ->     e.sal,   
    ->     sg.grade  
    -> FROM   
    ->     employee e  
    -> JOIN   
    ->     department d ON e.deptno = d.deptno  
    -> LEFT JOIN   
    ->     salarygrade sg ON e.sal BETWEEN sg.losal AND sg.hisal  
    -> WHERE   
    ->     e.sal > (SELECT avg(sal)+sum(comm)/14 FROM employee)  
    -> ORDER BY   
    ->     e.empno;
+-------+-----------+-----------+------+----------+-------+
| empno | ename     | dname     | mgr  | sal      | grade |
+-------+-----------+-----------+------+----------+-------+
|  1004 | 刘备      | 学工部    | 1009 | 29750.00 |     4 |
|  1006 | 关羽      | 销售部    | 1009 | 28500.00 |     4 |
|  1007 | 张飞      | 教研部    | 1009 | 24500.00 |     4 |
|  1008 | 诸葛亮    | 学工部    | 1004 | 30000.00 |     4 |
|  1009 | 曾阿牛    | 教研部    | NULL | 50000.00 |     5 |
|  1013 | 庞统      | 学工部    | 1004 | 30000.00 |     4 |
+-------+-----------+-----------+------+----------+-------+
6 rows in set (0.02 sec)

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

mysql> SELECT a.ename, b.dname 
    -> FROM employee  a 
    -> JOIN department b ON a.deptno = b.deptno 
    -> WHERE a.job = (SELECT job FROM employee WHERE ename = '庞统');
+-----------+-----------+
| ename     | dname     |
+-----------+-----------+
| 诸葛亮    | 学工部    |
| 庞统      | 学工部    |
+-----------+-----------+
2 rows in set (0.00 sec)

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

mysql> select e.ename,e.sal,d.dname from employee e      
    ->  inner join department d     
    -> on e.deptno = d.deptno  where sal >
    -> (select sum(sal)+sum(comm) from  employee where deptno = 30);
Empty set (0.01 sec)

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

mysql> SELECT current_year.year, current_year.zz,
    -> CASE WHEN previous_year.zz = 0 THEN NULL
    -> 
    -> ELSE CONCAT(ROUND(( (current_year.zz - previous_year.zz) / previous_year.zz )*100 ,2 ),'%')
    -> 
    -> END AS growth_rate
    -> 
    -> FROM
    -> 
    ->          annualprofit current_year
    -> 
    -> LEFT JOIN
    -> 
    ->         annualprofit previous_year ON current_year.year = previous_year.year + 1
    -> 
    -> ORDER BY current_year.year;
+------+---------+-------------+
| year | zz      | growth_rate |
+------+---------+-------------+
| 2010 | 100.00  | NULL        |
| 2011 | 150.00  | 50%         |
| 2012 | 250.00  | 66.67%      |
| 2013 | 800.00  | 220%        |
| 2014 | 1000.00 | 25%         |
+------+---------+-------------+
5 rows in set (0.01 sec)
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
196 66
|
14天前
|
存储 Oracle 关系型数据库
索引在手,查询无忧:MySQL索引简介
MySQL 是一款广泛使用的关系型数据库管理系统,在2024年5月的DB-Engines排名中得分1084,仅次于Oracle。本文介绍MySQL索引的工作原理和类型,包括B+Tree、Hash、Full-text索引,以及主键、唯一、普通索引等,帮助开发者优化查询性能。索引类似于图书馆的分类系统,能快速定位数据行,极大提高检索效率。
48 8
|
16天前
|
SQL 关系型数据库 MySQL
MySQL 窗口函数详解:分析性查询的强大工具
MySQL 窗口函数从 8.0 版本开始支持,提供了一种灵活的方式处理 SQL 查询中的数据。无需分组即可对行集进行分析,常用于计算排名、累计和、移动平均值等。基本语法包括 `function_name([arguments]) OVER ([PARTITION BY columns] [ORDER BY columns] [frame_clause])`,常见函数有 `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `SUM()`, `AVG()` 等。窗口框架定义了计算聚合值时应包含的行。适用于复杂数据操作和分析报告。
59 11
|
20天前
|
存储 关系型数据库 MySQL
mysql怎么查询longblob类型数据的大小
通过本文的介绍,希望您能深入理解如何查询MySQL中 `LONG BLOB`类型数据的大小,并结合优化技术提升查询性能,以满足实际业务需求。
80 6
|
2月前
|
SQL 前端开发 关系型数据库
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
68 9
|
2月前
|
缓存 监控 关系型数据库
如何优化MySQL查询速度?
如何优化MySQL查询速度?【10月更文挑战第31天】
134 3
|
2月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
344 1
|
2月前
|
关系型数据库 MySQL 数据库
【赵渝强老师】启动与关闭MySQL数据库实例
MySQL数据库安装完成后,可以通过命令脚本启动、查看状态、配置开机自启、查看自启列表及关闭数据库。本文提供了详细的操作步骤和示例代码,并附有视频讲解。
|
11天前
|
存储 Oracle 关系型数据库
数据库传奇:MySQL创世之父的两千金My、Maria
《数据库传奇:MySQL创世之父的两千金My、Maria》介绍了MySQL的发展历程及其分支MariaDB。MySQL由Michael Widenius等人于1994年创建,现归Oracle所有,广泛应用于阿里巴巴、腾讯等企业。2009年,Widenius因担心Oracle收购影响MySQL的开源性,创建了MariaDB,提供额外功能和改进。维基百科、Google等已逐步替换为MariaDB,以确保更好的性能和社区支持。掌握MariaDB作为备用方案,对未来发展至关重要。
39 3
|
11天前
|
安全 关系型数据库 MySQL
MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!
《MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!》介绍了MySQL中的三种关键日志:二进制日志(Binary Log)、重做日志(Redo Log)和撤销日志(Undo Log)。这些日志确保了数据库的ACID特性,即原子性、一致性、隔离性和持久性。Redo Log记录数据页的物理修改,保证事务持久性;Undo Log记录事务的逆操作,支持回滚和多版本并发控制(MVCC)。文章还详细对比了InnoDB和MyISAM存储引擎在事务支持、锁定机制、并发性等方面的差异,强调了InnoDB在高并发和事务处理中的优势。通过这些机制,MySQL能够在事务执行、崩溃和恢复过程中保持
39 3