SQL 查询练习：员工和部门数据库操作示例

员工和部门数据库 SQL 查询示例

本页面提供了一些基于员工和部门数据库的 SQL 查询示例，涵盖了查找最高工资员工、平均工资、员工数量、部门经理等操作。

数据库表结构

员工表 employees

CREATE TABLE `employees` (
  `employee_id` int(6) NOT NULL DEFAULT '0',
  `first_name` varchar(20) DEFAULT NULL,
  `last_name` varchar(25) NOT NULL,
  `email` varchar(25) NOT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `hire_date` date NOT NULL,
  `job_id` varchar(10) NOT NULL,
  `salary` double(8,2) DEFAULT NULL,
  `commission_pct` double(2,2) DEFAULT NULL,
  `manager_id` int(6) DEFAULT NULL,
  `department_id` int(4) DEFAULT NULL
);

部门表 departments

CREATE TABLE `departments` (
  `department_id` int(4) NOT NULL DEFAULT '0',
  `department_name` varchar(30) NOT NULL,
  `manager_id` int(6) DEFAULT NULL,
  `location_id` int(4) DEFAULT NULL
);

查询示例

查询取得每个部门最高工资的人员信息

SELECT e.employee_id, e.first_name, e.last_name, e.salary, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary = (
  SELECT MAX(salary)
  FROM employees
  WHERE department_id = e.department_id
);

查询员工的最高工资（不使用 max 函数）

SELECT e.employee_id, e.first_name, e.last_name, e.salary
FROM employees e
WHERE NOT EXISTS (
  SELECT 1
  FROM employees
  WHERE salary > e.salary
);

查询每个部门的平均工资和总工资

SELECT d.department_id, d.department_name, AVG(e.salary) as avg_salary, SUM(e.salary) as total_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_id, d.department_name;

查询没有分配部门的员工信息

SELECT e.employee_id, e.first_name, e.last_name
FROM employees e
WHERE e.department_id IS NULL;

查询每个部门的员工数量

SELECT d.department_id, d.department_name, COUNT(e.employee_id) as employee_count
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_id, d.department_name;

查询每个部门的经理姓名和联系电话

SELECT d.department_id, d.department_name, e.first_name, e.last_name, e.phone_number
FROM departments d
JOIN employees e ON d.manager_id = e.employee_id;

查询每个部门的员工数量，并按照员工数量降序排序

SELECT d.department_id, d.department_name, COUNT(e.employee_id) as employee_count
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_id, d.department_name
ORDER BY employee_count DESC;

查询每个部门的平均工资，并按照平均工资升序排序

SELECT d.department_id, d.department_name, AVG(e.salary) as avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_id, d.department_name
ORDER BY avg_salary ASC;