SQL语句中EXISTS的详细用法大全
作者:zhangbeizhen18 发布时间:2024-01-22 11:09:11
前言
在业务开展中,会遇到类似需求。
需求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数据:
查看TEST_TB02数据:
查看TEST_TB03数据:
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);
执行结果:
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);
执行结果:
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);
执行结果:
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);
执行结果:
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);
执行结果:
三、在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);
执行结果:
结论:在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后面紧跟着的是需求中需要删除的表名的别名。
如果不使用别名会报错:
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:
执行前TEST_TB02:
执行后TEST_TB01:
四、在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);
执行结果:
结论:在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:
执行前TEST_TB02:
执行后TEST_TB01:
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:
执行前TEST_TB02:
执行后TEST_TB01:
以上,感谢。
来源:https://blog.csdn.net/zhangbeizhen18/article/details/119060431
猜你喜欢
- 有时候要用Javascript输常用的字符,比如每个页面都要有的脚注。这里提供一个转换脚本:将HTML自动转为JS代码<script&
- 创建类from django.forms import ModelFormfrom django.forms import widgets
- 本文实例讲述了PHP共享内存使用与信号控制。分享给大家供大家参考,具体如下:共享内存共享内存的使用主要是为了能够在同一台机器不同的进程中共享
- 下面是一个实战项目的结果。 #coding: utf-8 import Image,ImageDraw,ImageFont,os,strin
- 任务1、将指定的十进制转换为二进制、八进制、十六进制二进制转换第一种和第二种写法:num=int(input('请输入一个十进制的整
- 1、JavaScript方法:document.getElementById("id").innerHTML; (1)实
- 介绍 append()语法list.append( element )参数element:任何类型的元素列表「末尾」添加元素nam
- 打开pycharm,程序某一行序号出出现书签bookmark,编号为9如果想要删除bookmark,将光标移至bookmark所在行,按快捷
- requests是一个很实用的Python HTTP客户端库,编写爬虫和测试服务器响应数据时经常会用到。可以说,Requests 完全满足如
- 需求:给定一个dataframe和一个list,list中存放的是dataframe中某一列的元素,删除dataframe中与list元素重
- 访问数组元素数组索引等同于访问数组元素。可以通过引用其索引号来访问数组元素。NumPy 数组中的索引以 0 开头,这意味着第一个元素的索引为
- 解读数据库的嵌套查询的性能explain 是非常重要的性能查询的工具!!!1、嵌套查询首先大家都知道我们一般不提倡嵌套查询或是join查询原
- # coding=utf-8 from BeautifulSoup import BeautifulSoup, Tag, Navigable
- 要开发一个基于数据库的应用系统,其中最关键的一步就是整个系统所依据的数据库的建模设计,从逻辑的到物理的,一个环节疏于设计,整个的应用系统便似
- 打开在节点中加入一句:max_allowed_packet=5M 将MySQL的容量扩大到5M
- 软件环境: 1、操作系统:Windows 2000 Server 2、数 据 库:Oracle 8i R2 (8.1.7) for NT 企
- 1、什么是哈希hashhash一类算法,该算法接受传入的内容,经过运算得到一串hash值hash值的特点:只要传入的内容一样,得到的hash
- 下面通过一段代码给大家介绍php参数过滤class mysafe{ public $logname; public $isshwomsg;
- SQLSERVER编译与重编译编译的含义当SQLSERVER收到任何一个指令,包括查询(query)、批处理(batch)、存储过程、触发器
- 考点:将字典转换为XML文档;将XML文档转换为字典。面试题1.面试题一:如何将一个字典转换为XML文档,并将该XML文档保存为文本文件。2