mysql查询员工最高最低工资三种方式

最新推荐文章于 2024-09-29 17:56:12 发布

海底大菠萝

最新推荐文章于 2024-09-29 17:56:12 发布

阅读量1.3w

点赞数 4

文章标签： mysql

本文链接：https://blog.csdn.net/smart60/article/details/115391341

版权

该博客展示了如何使用SQL查询从员工表中提取最高和最低工资。首先创建了两个表，`depts`（部门）和`employees`（员工），并填充了相关数据。接着，提供了三个查询示例：（1）仅显示最高和最低工资；（2）显示工资最高和最低的员工详细信息；（3）同时显示这两名员工的所有信息。这些查询对于理解和操作数据库中的工资数据非常有用。

摘要由CSDN通过智能技术生成

首先建立部门表和员工表并插入数据

DROP DATABASE IF EXISTS teaching ;
CREATE DATABASE teaching CHARACTER SET utf8 COLLATE utf8_general_ci;
USE teaching;

-- 创建部门表
CREATE TABLE IF NOT EXISTS depts(
dept_id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
description VARCHAR(200)
);
-- 插入部门数据
INSERT INTO depts VALUES
(1,'人事部','NULL'),
(2,'研发部','NULL'),
(3,'市场部','NULL'),
(4,'培训部','NULL');

-- 创建员工表
CREATE TABLE IF NOT EXISTS employees(
employee_id INT PRIMARY KEY,
NAME VARCHAR(20) NOT NULL,
gender ENUM('M','F') DEFAULT 'M',
dept_id INT,
join_time DATETIME,
salary DECIMAL(8,2),
address VARCHAR(200),
image BLOB,
description VARCHAR(250)
);

-- 插入员工数据
INSERT INTO employees VALUES
(1,'张三','M',1,'2008-02-02 00:00:00','3500.00','北京','',''),
(2,'李四','F',2,'2007-02-20 00:00:00','5000.00','上海','',''),
(3,'王五','M',3,'2006-12-20 00:00:00','7000.00','福建','',''),
(4,'赵六','F',2,'2008-06-06 00:00:00','2800.00','广东','',''),
(5,'钱七','M',4,'2005-08-21 00:00:00','8000.00','山东','',''),
(6,'孙八','F',1,'2008-04-16 00:00:00','2000.00','河北','NULL','这家伙很懒，什么也没说！');

下面查询最高最低工资

（1）只显示工资

select max(salary) as 最高工资,min(salary) as 最低工资 from employees

（2）显示全部信息

select * from employees where employee_id =
(select employee_id from employees order by salary asc limit 1)
or
employee_id =
(select employee_id from employees order by salary desc limit 1)

（3）

select * from employees where employee_id in
(
(select employee_id from employees order by salary asc limit 1),
(select employee_id from employees order by salary desc limit 1)
)