MySQL(38)如何使用EXISTS检查记录是否存在？

EXISTS 是 SQL 中用于检查子查询是否返回至少一条记录的运算符。它通常用于测试是否存在满足特定条件的记录，从而在主查询中进行相应操作。EXISTS 通常用于 SELECT、UPDATE、DELETE 语句中，来提高查询的效率和简化逻辑。

基本语法

SELECT column1, column2, ...
FROM table1
WHERE EXISTS (subquery);

子查询 subquery 返回的结果集如果至少有一行满足条件，则 EXISTS 操作符返回 TRUE，否则返回 FALSE。

示例数据库和表结构

假设我们有两个表：employees 表和 departments 表。

-- 创建数据库
CREATE DATABASE company;

-- 选择数据库
USE company;

-- 创建表 employees
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    emp_position VARCHAR(100),
    dept_id INT,
    hire_date DATE
);

-- 创建表 departments
CREATE TABLE departments (
    dept_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(100)
);

-- 插入示例数据到 departments 表
INSERT INTO departments (dept_name)
VALUES 
('Engineering'),
('Research'),
('Marketing');

-- 插入示例数据到 employees 表
INSERT INTO employees (emp_name, emp_position, dept_id, hire_date)
VALUES 
('John Doe', 'Manager', 1, '2023-10-01'),
('Jane Smith', 'Developer', 1, '2023-09-01'),
('Alice Johnson', 'Analyst', 2, '2023-08-01'),
('Bob Brown', 'Developer', NULL, '2023-07-01');

1. 使用 EXISTS 在 SELECT 语句中

假设我们希望查询有员工的部门列表：

SELECT dept_name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.dept_id = d.dept_id
);

结果：

dept_name
-----------
Engineering
Research

2. 使用 EXISTS 在 DELETE 语句中

假设我们希望删除没有员工的部门：

DELETE FROM departments
WHERE NOT EXISTS (
    SELECT 1
    FROM employees
    WHERE employees.dept_id = departments.dept_id
);

删除后的 departments 表：

dept_id | dept_name
--------|-----------
1       | Engineering
2       | Research

3. 使用 EXISTS 在 UPDATE 语句中

假设我们希望更新每个员工的 emp_position，如果他们所属的部门存在，则将其职位设为 “Active”，否则设为 “Inactive”：

UPDATE employees e
SET emp_position = CASE
                      WHEN EXISTS (
                          SELECT 1
                          FROM departments d
                          WHERE d.dept_id = e.dept_id
                      ) THEN 'Active'
                      ELSE 'Inactive'
                  END;

更新后的 employees 表：

emp_id | emp_name      | emp_position | dept_id | hire_date
-------|---------------|--------------|---------|-----------
1      | John Doe      | Active       | 1       | 2023-10-01
2      | Jane Smith    | Active       | 1       | 2023-09-01
3      | Alice Johnson | Active       | 2       | 2023-08-01
4      | Bob Brown     | Inactive     | NULL    | 2023-07-01

4. 使用 EXISTS 在 INSERT 语句中

假设我们希望插入一条新员工记录，但仅在指定的部门存在时插入：

INSERT INTO employees (emp_name, emp_position, dept_id, hire_date)
SELECT 'Charlie White', 'Tester', 3, '2023-11-01'
WHERE EXISTS (
    SELECT 1
    FROM departments
    WHERE dept_id = 3
);

此查询不会插入任何记录，因为 dept_id = 3 已被删除。而如果我们插入一个存在的 dept_id，比如：

INSERT INTO employees (emp_name, emp_position, dept_id, hire_date)
SELECT 'Charlie White', 'Tester', 1, '2023-11-01'
WHERE EXISTS (
    SELECT 1
    FROM departments
    WHERE dept_id = 1
);

插入后的 employees 表：

emp_id | emp_name      | emp_position | dept_id | hire_date
-------|---------------|--------------|---------|-----------
1      | John Doe      | Active       | 1       | 2023-10-01
2      | Jane Smith    | Active       | 1       | 2023-09-01
3      | Alice Johnson | Active       | 2       | 2023-08-01
4      | Bob Brown     | Inactive     | NULL    | 2023-07-01
5      | Charlie White | Tester       | 1       | 2023-11-01

5. 使用 EXISTS 在 JOIN 语句中

假设我们希望查询所有有员工的部门及其员工数量：

SELECT d.dept_name, COUNT(e.emp_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE EXISTS (
    SELECT 1
    FROM employees
    WHERE employees.dept_id = d.dept_id
)
GROUP BY d.dept_name;

结果：

dept_name   | employee_count
------------|----------------
Engineering | 3
Research    | 1

小结

EXISTS 是一个强大的 SQL 运算符，用于检查子查询是否返回任何记录。它可以用于 SELECT、UPDATE、DELETE 和 INSERT 语句中，以实现条件逻辑处理。通过使用 EXISTS，可以简化查询逻辑，提高查询效率，并处理复杂的业务需求。上述示例展示了如何在不同情况下使用 EXISTS 运算符，以实现记录存在性检查。