SQL语句EXISTS用法
zhangbeizhen18 人气:7前言
在业务开展中,会遇到类似需求。
需求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:
以上,感谢。
总结
加载全部内容