SQL语句中EXISTS的用法

记录:258

在业务开展中,会遇到类似需求。

需求1:UPDATE表TEST_TB01中的记录;满足条件:这些记录不在TEST_TB02中。

需求2:UPDATE表TEST_TB01中的记录;满足条件:这些记录在TEST_TB02中。

在SQL语句中EXISTS的用法,能够比较简洁的去解决这类需求。

一、建表

1.在MySQL数据库建表语句

CREATE TABLE TEST_TB01
(
  sensor_id   BIGINT,
  part_id     BIGINT,
  flag        VARCHAR(64)
 )
COMMENT '数据表一';
CREATE TABLE TEST_TB02
(
  sensor_id   BIGINT,
  part_id     BIGINT,
  flag        VARCHAR(64)
 )
COMMENT '数据表二';
CREATE TABLE TEST_TB03
(
  sensor_id   BIGINT,
  part_id     BIGINT,
  flag        VARCHAR(64)
 )
COMMENT '数据表三';

2.在ORACLE数据库建表语句

CREATE TABLE TEST_TB01
(
  sensor_id  NUMBER(16),
  part_id    NUMBER(16),
  flag       VARCHAR(64)
 );
CREATE TABLE TEST_TB02
(
  sensor_id  NUMBER(16),
  part_id    NUMBER(16),
  flag       VARCHAR(64)
 );

二、在SELECT语句中使用EXISTS

在SELECT的SQL语句中使用EXISTS。

在TEST_TB01插入数据:

INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2101,8811,'厦门');
INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2102,8812,'泉州');
INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2103,8813,'福州');
INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2104,8814,'漳州');
INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2105,8815,'杭州');
INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2106,8816,'上海');
INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2107,8817,'北京');
INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2108,8818,'深圳');
INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2109,8819,'中国');

在TEST_TB02插入数据:

INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2101,8811,'厦门');
INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2102,8812,'泉州');
INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2103,8813,'福州');
INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2104,8814,'漳州');
INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2109,8819,'中国');

在TEST_TB03插入数据:

INSERT INTO TEST_TB03 (sensor_id,part_id,flag) VALUES(2106,8816,'上海');
INSERT INTO TEST_TB03 (sensor_id,part_id,flag) VALUES(2107,8817,'北京');
INSERT INTO TEST_TB03 (sensor_id,part_id,flag) VALUES(2109,8819,'中国');

查看TEST_TB01数据:

SQL语句中EXISTS的用法

查看TEST_TB02数据:

SQL语句中EXISTS的用法

查看TEST_TB03数据:

SQL语句中EXISTS的用法

 1.在SQL中使用EXISTS

需求:从TEST_TB01中查询出在TEST_TB02中存在的记录,关联条件是两个表的sensor_id相等。

SQL语句:

SELECT
  aa.sensor_id,aa.part_id,aa.flag
FROM
  TEST_TB01 aa
WHERE EXISTS
  (SELECT 1 FROM
    TEST_TB02 bb
  WHERE aa.sensor_id = bb.sensor_id);

执行结果:

SQL语句中EXISTS的用法

 2.在SQL中使用NOT EXISTS

需求:从TEST_TB01中查询出在TEST_TB02中不存在的记录,关联条件是两个表的sensor_id相等。

SQL语句:

SELECT
  aa.sensor_id,aa.part_id,aa.flag
FROM
  TEST_TB01 aa
WHERE NOT EXISTS
  (SELECT 1 FROM
    TEST_TB02 bb
  WHERE aa.sensor_id = bb.sensor_id);

执行结果:

SQL语句中EXISTS的用法

 3.在SQL中使用多个NOT EXISTS

需求:从TEST_TB01中查询出在TEST_TB02和TEST_TB03中都不存在的记录,关联条件是表的sensor_id相等。

SQL语句:

SELECT
  aa.sensor_id,aa.part_id,aa.flag
FROM
  TEST_TB01 aa
WHERE NOT EXISTS
  (SELECT 1 FROM
    TEST_TB02 bb
  WHERE aa.sensor_id = bb.sensor_id)
  AND NOT EXISTS
  (SELECT 1 FROM
    TEST_TB03 cc
  WHERE aa.sensor_id = cc.sensor_id);

执行结果:

SQL语句中EXISTS的用法

 4.在SQL中使用多个EXISTS

需求:从TEST_TB01中查询出在TEST_TB02和TEST_TB03中都存在的记录,关联条件是表的sensor_id相等。

SQL语句:

SELECT
  aa.sensor_id,aa.part_id,aa.flag
FROM
  TEST_TB01 aa
WHERE  EXISTS
  (SELECT 1 FROM
    TEST_TB02 bb
  WHERE aa.sensor_id = bb.sensor_id)
  AND  EXISTS
  (SELECT 1 FROM
    TEST_TB03 cc
  WHERE aa.sensor_id = cc.sensor_id);

执行结果:

SQL语句中EXISTS的用法

 5.在SQL中使用NOT EXISTS和EXISTS

需求:从TEST_TB01中查询出在TEST_TB02存在但是TEST_TB03中不存在的记录,关联条件是表的sensor_id相等。

SQL语句:

SELECT
  aa.sensor_id,aa.part_id,aa.flag
FROM
  TEST_TB01 aa
WHERE  EXISTS
  (SELECT 1 FROM
    TEST_TB02 bb
  WHERE aa.sensor_id = bb.sensor_id)
  AND NOT EXISTS
  (SELECT 1 FROM
    TEST_TB03 cc
  WHERE aa.sensor_id = cc.sensor_id);

执行结果:

SQL语句中EXISTS的用法

三、在DELETE语句中使用EXISTS

在DELETE的SQL语句中使用EXISTS和NOT EXISTS。

在TEST_TB01插入数据:

INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2101,8811,'厦门');
INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2102,8812,'泉州');
INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2103,8813,'福州');
INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2104,8814,'漳州');
INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2105,8815,'杭州');
INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2106,8816,'上海');
INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2107,8817,'北京');
INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2108,8818,'深圳');
INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2109,8819,'中国');

在TEST_TB02插入数据:

INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2101,8811,'厦门');
INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2102,8812,'泉州');
INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2103,8813,'福州');
INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2104,8814,'漳州');
INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2109,8819,'中国');

1.在MySQL中使用

需求:从TEST_TB01中删除在TEST_TB02中存在的记录,关联条件是两个表的sensor_id相等。

注意:本例使用MySQL版本:MySQL 5.7.33。

SQL语句:

DELETE  FROM 
TEST_TB01 aa
WHERE EXISTS
  (SELECT 1 FROM
    TEST_TB02 bb
  WHERE aa.sensor_id = bb.sensor_id);

执行结果:

SQL语句中EXISTS的用法

 结论:在MySQL中是不支持在DELETE的SQL语句中使用EXISTS和NOT EXISTS这种句法。(本例版本:MySQL 5.7.33)。

解决此需求

SQL语句:

DELETE
  aa
FROM
  TEST_TB01 aa
 INNER JOIN TEST_TB02 bb
    ON aa.sensor_id = bb.sensor_id;

注意:在SQL中DELETE后面紧跟着的是需求中需要删除的表名的别名

如果不使用别名会报错:

SQL语句中EXISTS的用法

 2.在Oracle中使用

需求:从TEST_TB01中删除在TEST_TB02中存在的记录,关联条件是两个表的sensor_id相等。

SQL语句:

DELETE  FROM 
TEST_TB01 aa
WHERE EXISTS
  (SELECT 1 FROM
    TEST_TB02 bb
  WHERE aa.sensor_id = bb.sensor_id);

执行结果:

执行前TEST_TB01:

SQL语句中EXISTS的用法

 执行前TEST_TB02:

SQL语句中EXISTS的用法

 执行后TEST_TB01:

SQL语句中EXISTS的用法

四、在UPDATE语句中使用EXISTS

在UPDATE的SQL语句中使用EXISTS。

在TEST_TB01插入数据:

INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2101,8811,'城市');
INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2102,8812,'城市');
INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2103,8813,'城市');
INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2104,8814,'城市');
INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2105,8815,'城市');
INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2106,8816,'城市');
INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2107,8817,'城市');
INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2108,8818,'城市');
INSERT INTO TEST_TB01 (sensor_id,part_id,flag) VALUES(2109,8819,'城市');

在TEST_TB02插入数据:

INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2101,8811,'厦门');
INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2102,8812,'泉州');
INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2103,8813,'福州');
INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2104,8814,'漳州');
INSERT INTO TEST_TB02 (sensor_id,part_id,flag) VALUES(2109,8819,'中国');

1.在MySQL中使用

需求:在TEST_TB01中更新,在TEST_TB02中存在的记录,关联条件是两个表的sensor_id相等。

注意:本例使用MySQL版本:MySQL 5.7.33。

SQL语句:

UPDATE TEST_TB01 aa
   SET (aa.part_id, aa.flag) =
       (SELECT bb.part_id, bb.flag
          FROM TEST_TB02 bb
         WHERE aa.sensor_id = bb.sensor_id)
 WHERE EXISTS
 (SELECT 1 FROM TEST_TB02 cc 
       WHERE aa.sensor_id = cc.sensor_id);

执行结果:

SQL语句中EXISTS的用法

 结论:在MySQL中是不支持在UPDATE的SQL语句中使用EXISTS和NOT EXISTS这种句法。(本例版本:MySQL 5.7.33)。

解决此需求

SQL语句:

UPDATE TEST_TB01 aa ,TEST_TB02 bb
SET 
    aa.part_id=bb.part_id,
    aa.flag=bb.flag
WHERE aa.sensor_id = bb.sensor_id;

执行结果:

执行前TEST_TB01:

SQL语句中EXISTS的用法

 执行前TEST_TB02:

SQL语句中EXISTS的用法

 执行后TEST_TB01:

SQL语句中EXISTS的用法

 2.在Oracle中使用

需求:在TEST_TB01中更新,在TEST_TB02中存在的记录,关联条件是两个表的sensor_id相等。

SQL语句:

UPDATE TEST_TB01 aa
   SET (aa.part_id, aa.flag) =
       (SELECT bb.part_id, bb.flag
          FROM TEST_TB02 bb
         WHERE aa.sensor_id = bb.sensor_id)
 WHERE EXISTS
 (SELECT 1 FROM TEST_TB02 cc 
       WHERE aa.sensor_id = cc.sensor_id);

执行结果:

执行前TEST_TB01:

SQL语句中EXISTS的用法

 执行前TEST_TB02:

SQL语句中EXISTS的用法

 执行后TEST_TB01:

SQL语句中EXISTS的用法

 以上,感谢。

上一篇:电力系统中机组组合强大的Yalmip+Cplex


下一篇:cpu架构 x86 ARM MIPS PowerPC RISC-V以及RISC CISC