MySQL EXISTS运算符，not exists 和 in，exists在增删改查中的使用

最新推荐文章于 2024-03-18 15:17:00 发布

天海华兮

最新推荐文章于 2024-03-18 15:17:00 发布

阅读量3.1k

点赞数 1

分类专栏：数据库相关知识文章标签： mysql 数据库 java exists in sql

原文链接：https://www.begtut.com/mysql/mysql-exists.html

版权

数据库相关知识专栏收录该内容

35 篇文章 1 订阅

订阅专栏

MySQL EXISTS运算符

https://www.begtut.com/mysql/mysql-exists.html

简介：在本教程中，您将学习如何使用MySQL EXISTS运算符以及何时使用它来提高查询性能。

exists
n.
存在量词（exist 的复数）
v.
存在；出现；活着（exist 的三单形式）

MySQL EXISTS运算符简介

EXISTS是一个布尔运算符返回true或false。

EXISTS经常使用的在一个子查询，以测试一个“存在”状态。

以下说明了EXISTS运营商的常见用法。

SELECT 
    select_list
FROM
    a_table
WHERE
    [NOT] EXISTS(subquery);

如果子查询返回任何行，

则EXISTS运算符返回true，否则返回false。

此外，EXISTS一旦找到匹配的行，立即终止进一步的处理。

由于此特性，您可以EXISTS在某些情况下使用运算符来提高查询的性能。

NOT EXISTS是否定操作符。换句话说，

如果子查询没有返回任何行，NOT EXISTS则返回true，否则返回false。

您可以使用SELECT *，SELECT column，SELECT a_constant，或在子查询任何东西。结果是相同的，因为

MySQL忽略select_list在 SELECT子句中出现的结果。

MySQL EXISTS示例

让我们举一些使用EXISTS运算符来了解它是如何工作的例子。

MySQL SELECT EXISTS示例

我们来看看示例数据库中的customers和orders表。

+------------------------+
| customers              |
+------------------------+
| customerNumber         | 客户 号
| customerName           | 客户 名
| contactLastName        | 客户名 后缀
| contactFirstName       | 客户名 前缀
| phone                  | 手机号
| addressLine1           | 地址1
| addressLine2           | 地址2
| city                   | 城市
| state                  | 状态
| postalCode             | 右边
| country                | 国家
| salesRepEmployeeNumber | 售卖 仓库 雇员 编号
| creditLimit            | 信用
+------------------------+
13 rows in set (0.01 sec)

country
n.
国，国家；全国人民，全体国民（the country）；乡下，乡村；地区，区域；乡村音乐；故乡
adj.
乡下的，农村的；乡村音乐的；祖国的，故乡的；粗鲁的

credit
n.
信用，信贷，赊购；赞扬，信誉，声望；存款金额，余额；补助；退税，抵免；学分；带来荣耀的人（或事）；作品（指影片、电视节目、戏剧等）；信任，相信
v.
把（钱）记入总账，把钱存入（账户）；相信，信任；认为是……的功劳，给予承认；认为……有特定品质（或效果）

limit
英
/ˈlɪmɪt/
n.
限度，限制；极限，限量；（区域的）界限，边界；（法律允许的机动车驾驶员血液的）最高酒精浓度；（可能行动或结果的）范围
v.
限制，限定；制约（某人的发展等）；减少

+----------------+
| orders         |
+----------------+
| orderNumber    | 订单号
| orderDate      | 订单时间
| requiredDate   | 确认时间
| shippedDate    | 运输时间
| status         | 状态
| comments       | 评论
| customerNumber | 客户 编号
+----------------+
7 rows in set (0.00 sec)

required
英
/rɪˈkwaɪəd/
adj.
必需的，规定的；理想的
v.
需要；要求做（某事），规定（require 的过去式和过去分词形式）；要求做（某事），规定（require 的过去式和过去分词）

ship
英
/ʃɪp/
n.
（大）船，舰；三（或多）桅帆船；<非正式> 艇（尤指赛艇）；宇宙飞船；<美> 飞机
v.
（用船、飞机、卡车）运送，运输；遣送（某人）；推出（计算机商品），（使）上市；

comment
英
/ˈkɒment
n.
评论，意见；批评，指责；说明，写照；<旧>解说，注释；（计算机）注解
v.
评论，发表意见；（计算机）注解，把（部分程序）转成注解

至少有一个销售订单的客户

假设您要查找至少有一个销售订单的客户，请按以下方式使用EXISTS运算符：

SELECT 
    customerNumber, customerName
FROM
    customers
WHERE
    EXISTS( -- 存在。 订单的表的 客户号 = 客户表的 客户号
        SELECT 
            1
        FROM
            orders
        WHERE
            orders.customernumber = customers.customernumber
          );

运行结果：

+----------------+------------------------------------+
| customerNumber | customerName                       |
+----------------+------------------------------------+
|            103 | Atelier graphique                  |
|            112 | Signal Gift Stores                 |
|            114 | Australian Collectors, Co.         |
|            119 | La Rochelle Gifts                  |
...

对于在customers表每一行，查询检查customerNumber 的orders表。

如果表customerNumber中出现的customers表存在于orders表中，则子查询返回第一个匹配的行。

结果EXISTS运算符返回true并停止扫描orders表。
否则，子查询不返回任何行EXISTS操作符返回false。

要获得未有任何销售订单的客户，请使用NOT EXISTS运算符作为以下语句：

SELECT 
    customerNumber, customerName
FROM
    customers
WHERE
    NOT EXISTS(  -- 增加not，不在 这个查询里面的
        
        SELECT 
            1
        FROM
            orders
        WHERE
            orders.customernumber = customers.customernumber
              
              );

运行结果：

+----------------+--------------------------------+
| customerNumber | customerName                   |
+----------------+--------------------------------+
|            125 | Havel & Zbyszek Co             |
|            168 | American Souvenirs Inc         |
...

MySQL UPDATE EXISTS示例

假设您必须更新在旧金山办事处工作的员工的电话分机。

要查找在San Franciso办公室工作的员工，请使用EXISTS运算符作为以下UPDATE语句：

查询旧金山办公室工作的员工

SELECT 
    employeenumber, firstname, lastname, extension
FROM
    employees
WHERE
    EXISTS(
        
        SELECT 
            1
        FROM
            offices
        WHERE
            city = 'San Francisco' -- 城市为旧金山。办公室的办公室编码 = 雇员的办公室编码
                AND offices.officeCode = employees.officeCode
           
          );

运行结果：

+----------------+-----------+-----------+-----------+
| employeenumber | firstname | lastname  | extension |
+----------------+-----------+-----------+-----------+
|           1002 | Diane     | Murphy    | x5800     |
|           1056 | Mary      | Patterson | x4611     |
|           1076 | Jeff      | Firrelli  | x9273     |
+----------------+-----------+-----------+-----------+
6 rows in set (0.01 sec)

更新在旧金山的办公室工作的员工

假设你想在谁在旧金山的办公室工作的员工的每一个电话分机添加的号码5，您可以在UPDATE语句中WHERE的子句使用EXISTS如下：

UPDATE employees 
SET 
    extension = CONCAT(extension, '1') --题目说错了，这里是拼接了1
WHERE
    EXISTS( SELECT 
            1
        FROM
            offices
        WHERE
            city = 'San Francisco'
                AND offices.officeCode = employees.officeCode);

MySQL INSERT EXISTS 实例

假设您要将未在任何销售订单中放置的客户归档到单独的表中。为此，请按照以下步骤操作。

首先，通过从customers表中复制结构来创建一个的新表用于归档客户。

CREATE TABLE customers_archive LIKE customers;

其次，使用以下INSERT语句将没有任何销售订单的客户插入customers_archive表中。

INSERT INTO customers_archive

SELECT * FROM customers

WHERE NOT EXISTS(  -- 不存在订单的 用户
    
    SELECT 
            1
        FROM
            orders
        WHERE
            orders.customernumber = customers.customernumber
                );

第三，从表中查询数据customers_archive以验证插入操作。

SELECT 
    *
FROM
    customers_archive;

运行结果：

MySQL DELETE EXISTS 实例

归档客户数据的最后一项任务是customers_archive从customers表中删除表中存在的客户。

为此，请使用DELETE语句中的WHERE子句中使用EXISTS，如下所示：

DELETE FROM customers
WHERE
    EXISTS(  --如果客户存在 customers_archive 表，就删除
        
   SELECT 
        1
    FROM
        customers_archive a
    
    WHERE
        a.customernumber = customers.customerNumber
        
          );

MySQL EXISTS与IN

要查找至少有一个销售订单的客户，可以使用IN运算符，如下所示：

SELECT 
    customerNumber, customerName
FROM
    customers
WHERE
    customerNumber IN ( -- 客户号，必须在 这个客户列表里面
        
        SELECT 
            customerNumber
        FROM
            orders
        
    );

让我们比较使用的查询IN操作与使用的一个EXISTS使用操作符EXPLAIN的语句。

EXPLAIN SELECT 
    customerNumber, customerName
FROM
    customers
WHERE
    EXISTS( -- 客户在 订单表里
        SELECT 
            1
        FROM
            orders
        WHERE
            orders.customernumber = customers.customernumber);

现在，检查使用IN运算符的查询的性能。

SELECT 
    customerNumber, customerName
FROM
    customers
WHERE
    customerNumber IN (
        SELECT 
            customerNumber
        FROM
            orders
    );

总结：子查询包含大量数据 EXISTS

使用EXISTS运算符的查询比使用IN运算符的查询要快得多。

原因是EXISTS操作员基于**“至少找到”原则工作**。

它返回true并在找到至少一个匹配行后停止扫描表。

另一方面，当IN运算符与子查询组合时，MySQL必须首先处理子查询，然后使用子查询的结果来处理整个查询。

一般的经验法则是，

如果子查询包含大量数据，则EXISTS运算符可提供更好的性能。

但是，如果从

子查询返回的结果集非常小，则使用IN运算符的查询将执行得更快。

例如，以下声明使用IN操作员选择在旧金山办公室工作的所有员工。

SELECT 
    employeenumber, firstname, lastname
FROM
    employees
WHERE
    officeCode IN (
        
        SELECT 
            officeCode
        FROM
            offices
        WHERE
            offices.city = 'San Francisco'      
     );