SQL語句中EXISTS的詳細用法大全
前言
在業(yè)務(wù)開展中,會遇到類似需求。
需求1:UPDATE表TEST_TB01中的記錄;滿足條件:這些記錄不在TEST_TB02中。
需求2:UPDATE表TEST_TB01中的記錄;滿足條件:這些記錄在TEST_TB02中。
在SQL語句中EXISTS的用法,能夠比較簡潔的去解決這類需求。
一、建表
1.在MySQL數(shù)據(jù)庫建表語句
CREATE TABLE TEST_TB01 ( sensor_id BIGINT, part_id BIGINT, flag VARCHAR(64) ) COMMENT '數(shù)據(jù)表一'; CREATE TABLE TEST_TB02 ( sensor_id BIGINT, part_id BIGINT, flag VARCHAR(64) ) COMMENT '數(shù)據(jù)表二'; CREATE TABLE TEST_TB03 ( sensor_id BIGINT, part_id BIGINT, flag VARCHAR(64) ) COMMENT '數(shù)據(jù)表三';
2.在ORACLE數(shù)據(jù)庫建表語句
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插入數(shù)據(jù):
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插入數(shù)據(jù):
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插入數(shù)據(jù):
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數(shù)據(jù):
查看TEST_TB02數(shù)據(jù):
查看TEST_TB03數(shù)據(jù):
1.在SQL中使用EXISTS
需求:從TEST_TB01中查詢出在TEST_TB02中存在的記錄,關(guān)聯(lián)條件是兩個表的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);
執(zhí)行結(jié)果:
2.在SQL中使用NOT EXISTS
需求:從TEST_TB01中查詢出在TEST_TB02中不存在的記錄,關(guān)聯(lián)條件是兩個表的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);
執(zhí)行結(jié)果:
3.在SQL中使用多個NOT EXISTS
需求:從TEST_TB01中查詢出在TEST_TB02和TEST_TB03中都不存在的記錄,關(guān)聯(lián)條件是表的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);
執(zhí)行結(jié)果:
4.在SQL中使用多個EXISTS
需求:從TEST_TB01中查詢出在TEST_TB02和TEST_TB03中都存在的記錄,關(guān)聯(lián)條件是表的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);
執(zhí)行結(jié)果:
5.在SQL中使用NOT EXISTS和EXISTS
需求:從TEST_TB01中查詢出在TEST_TB02存在但是TEST_TB03中不存在的記錄,關(guān)聯(lián)條件是表的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);
執(zhí)行結(jié)果:
三、在DELETE語句中使用EXISTS
在DELETE的SQL語句中使用EXISTS和NOT EXISTS。
在TEST_TB01插入數(shù)據(jù):
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插入數(shù)據(jù):
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中存在的記錄,關(guān)聯(lián)條件是兩個表的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);
執(zhí)行結(jié)果:
結(jié)論:在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中存在的記錄,關(guān)聯(lián)條件是兩個表的sensor_id相等。
SQL語句:
DELETE FROM TEST_TB01 aa WHERE EXISTS (SELECT 1 FROM TEST_TB02 bb WHERE aa.sensor_id = bb.sensor_id);
執(zhí)行結(jié)果:
執(zhí)行前TEST_TB01:
執(zhí)行前TEST_TB02:
執(zhí)行后TEST_TB01:
四、在UPDATE語句中使用EXISTS
在UPDATE的SQL語句中使用EXISTS。
在TEST_TB01插入數(shù)據(jù):
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插入數(shù)據(jù):
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中存在的記錄,關(guān)聯(lián)條件是兩個表的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);
執(zhí)行結(jié)果:
結(jié)論:在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;
執(zhí)行結(jié)果:
執(zhí)行前TEST_TB01:
執(zhí)行前TEST_TB02:
執(zhí)行后TEST_TB01:
2.在Oracle中使用
需求:在TEST_TB01中更新,在TEST_TB02中存在的記錄,關(guān)聯(lián)條件是兩個表的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);
執(zhí)行結(jié)果:
執(zhí)行前TEST_TB01:
執(zhí)行前TEST_TB02:
執(zhí)行后TEST_TB01:
以上,感謝。
總結(jié)
到此這篇關(guān)于SQL語句中EXISTS用法的文章就介紹到這了,更多相關(guān)SQL語句EXISTS用法內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
CentOS7下MySQL5.7安裝配置方法圖文教程(YUM)
這篇文章主要為大家詳細介紹了CentOS7下MySQL5.7安裝配置方法圖文教程,具有一定的參考價值,感興趣的小伙伴們可以參考一下2017-01-01MySQL中l(wèi)ength()、char_length()的區(qū)別
在MySQL中l(wèi)ength(str)、char_length(str)都屬于判斷長度的內(nèi)置函數(shù),本文主要介紹了MySQL中l(wèi)ength()、char_length()的區(qū)別,文中通過示例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-05-05解決MySQL讀寫分離導(dǎo)致insert后select不到數(shù)據(jù)的問題
這篇文章主要介紹了解決MySQL讀寫分離導(dǎo)致insert后select不到數(shù)據(jù)的問題,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12安裝MySQL時,輸入mysqld --install后,顯式該文件已存在問題
這篇文章主要介紹了安裝MySQL時,輸入mysqld --install后,顯式該文件已存在問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-12-12