mysql中關(guān)鍵詞exists的用法實(shí)例詳解
前言
在日常開發(fā)中，用mysql進(jìn)行查詢的時(shí)候，有一個(gè)比較少見的關(guān)鍵詞exists，我們今天來學(xué)習(xí)了解一下這個(gè)exists這個(gè)sql關(guān)鍵詞的用法，這樣在工作中遇到一些特定的業(yè)務(wù)場(chǎng)景就可以有更加多樣化的解決方案
語法解釋
語法
SELECT column1 FROM t1 WHERE [conditions] and EXISTS (SELECT * FROM t2 );
說明
括號(hào)中的子查詢并不會(huì)返回具體的查詢到的數(shù)據(jù)，只是會(huì)返回true或者false，如果外層sql的字段在子查詢中存在則返回true，不存在則返回false
即使子查詢的查詢結(jié)果是null，只要是對(duì)應(yīng)的字段是存在的，子查詢中則返回true，下面有具體的例子
執(zhí)行過程
1、首先進(jìn)行外層查詢，在表t1中查詢滿足條件的column1
2、接下來進(jìn)行內(nèi)層查詢，將滿足條件的column1帶入內(nèi)層的表t2中進(jìn)行查詢，
3、如果內(nèi)層的表t2滿足查詢條件，則返回true，該條數(shù)據(jù)保留
4、如果內(nèi)層的表t2不滿足查詢條件，則返回false，則刪除該條數(shù)據(jù)
5、最終將外層的所有滿足條件的數(shù)據(jù)進(jìn)行返回
貼個(gè)鏈接，mysql官方對(duì)于這個(gè)命令的說明： https://dev.mysql.com/doc/refman/8.0/en/exists-and-not-exists-subqueries.html；喜歡看英文原版說明的可以來這里看一下
使用案例
環(huán)境準(zhǔn)備
?? mysql版本： 8.0.28
?? 數(shù)據(jù)庫表設(shè)計(jì)：
學(xué)生表： t_student
CREATE TABLE `t_student` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '學(xué)生姓名', `age` int NOT NULL COMMENT '年齡', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='學(xué)生表';
導(dǎo)入部分?jǐn)?shù)據(jù)
INSERT INTO `t_student` (`id`, `name`, `age`) VALUES (1, '小張', 10), (2, 'chenille', 13), (3, '小王', 15), (4, '小米', 11), (5, 'dong', 13), (6, 'xi', 12), (7, 'chenille', 13), (8, '小王地方', 15), (9, '米來', 11), (10, 'dong', 13), (11, '呵呵', 12), (12, 'chenille', 13), (13, '小趙', 15), (14, '小米-0', 11), (15, 'bei', 13), (16, 'xi-xx', 12), (17, 'chenille', 13), (18, '小王-hehe', 15), (19, '小米-qian', 11), (20, 'dong', 13), (21, 'xi', 12), (22, 'chenille', 13), (23, '小王-1', 15), (24, '小米-2', 11), (25, 'dong-3', 13), (26, 'xi-0', 12), (27, 'chenille-4', 13), (28, '小王-4', 15), (29, '小米-7', 11), (30, 'dong-1', 13), (31, 'xi-5', 12), (32, '貔貅', 10), (33, '耄耋', 12), (34, '饕餮', 9), (35, '龍', 13), (36, '青牛', 12);
班級(jí)學(xué)生表：t_class_student
CREATE TABLE `t_class_student` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `student_id` int NOT NULL COMMENT '學(xué)生ID', `class_id` int NOT NULL COMMENT '班號(hào)', `class_name` varchar(100) DEFAULT '' COMMENT '班級(jí)名稱', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='班級(jí)學(xué)生表';
導(dǎo)入部分?jǐn)?shù)據(jù)
INSERT INTO `t_class_student` (`id`, `student_id`, `class_id`, `class_name`)
VALUES
(1, 1, 1, '一年級(jí)1班'),
(2, 2, 1, '一年級(jí)1班'),
(3, 3, 1, '一年級(jí)1班'),
(4, 4, 1, '一年級(jí)1班'),
(5, 5, 1, '一年級(jí)1班'),
(6, 6, 1, '一年級(jí)1班'),
(7, 7, 1, '一年級(jí)1班'),
(8, 8, 1, '一年級(jí)1班'),
(9, 9, 1, '一年級(jí)1班'),
(10, 10, 1, '一年級(jí)1班'),
(11, 11, 2, '一年級(jí)2班'),
(12, 12, 2, '一年級(jí)2班'),
(13, 13, 2, '一年級(jí)2班'),
(14, 14, 2, '一年級(jí)2班'),
(15, 15, 2, '一年級(jí)2班'),
(16, 16, 2, '一年級(jí)2班'),
(17, 17, 2, '一年級(jí)2班'),
(18, 18, 2, '一年級(jí)2班'),
(19, 19, 2, '一年級(jí)2班'),
(20, 20, 2, '一年級(jí)2班'),
(21, 21, 3, '二年級(jí)2班'),
(22, 22, 3, '二年級(jí)2班'),
(23, 23, 3, '二年級(jí)2班'),
(24, 24, 3, '二年級(jí)2班'),
(25, 25, 3, '二年級(jí)2班'),
(26, 26, 3, '二年級(jí)2班'),
(27, 27, 3, '二年級(jí)2班'),
(28, 28, 3, '二年級(jí)2班'),
(29, 29, 3, '二年級(jí)2班'),
(30, 30, 3, '二年級(jí)2班'),
(31, 31, 4, '三年級(jí)1班');
(32, 32, 4, null);
常用查詢
已分配班級(jí)的學(xué)生名單 ??
select * from t_student as s where exists (select student_id from t_class_student where student_id = s.id);
未分配班級(jí)的學(xué)生名單 ??
select * from t_student as s where not exists (select student_id from t_class_student where student_id = s.id);
已分配 三年級(jí)1班 的學(xué)生名單 ??
select * from t_student as s where exists (select student_id from t_class_student where student_id = s.id and class_id = 4);
已分配 并且班級(jí)是 一年級(jí)1班 和 一年級(jí)2班 的學(xué)生名單 ??
select * from t_student as s where exists (select student_id from t_class_student where student_id = s.id and class_id in (1, 2) );
查詢到的字段為null，但是子查詢返回的結(jié)果為true ??
select * from t_student as s where exists (select class_name from t_class_student where student_id = s.id and class_id = 4);
查詢?nèi)繉W(xué)生名單 ??
select * from t_student as s where exists (select student_id from t_class_student where 1=1);
已分配 三年級(jí)1班 的并且年齡大于10歲的學(xué)生名單 ??
select * from t_student as s where age > 10 and exists (select student_id from t_class_student where student_id = s.id and class_id = 4);
exists與in的效率比較
上面的這些查詢其實(shí)也可以通過 in 關(guān)鍵字來實(shí)現(xiàn)，下面我們寫一下 in 關(guān)鍵字對(duì)應(yīng)的查詢語句，
通過 in 實(shí)現(xiàn)已分配班級(jí)的學(xué)生名單 ??
select * from t_student as s where id in (select student_id from t_class_student where student_id = s.id);
通過 in 實(shí)現(xiàn)未分配班級(jí)的學(xué)生名單 ??
select * from t_student as s where id not in (select student_id from t_class_student where student_id = s.id);
下面我們來分析一下這兩個(gè)關(guān)鍵字使用效率到底那個(gè)更高呢？
循環(huán)嵌套查詢執(zhí)行原理
?? 循環(huán)由外向內(nèi)，外層循環(huán)執(zhí)行一次，內(nèi)層循環(huán)則需要完整的執(zhí)行一次，內(nèi)層執(zhí)行執(zhí)行完后返回執(zhí)行結(jié)果，外層循環(huán)繼續(xù)執(zhí)行，直到外層循環(huán)完全執(zhí)行完成
循環(huán)優(yōu)化策略
?? 有了上面的執(zhí)行原理的說明，我們明白了一個(gè)道理：內(nèi)層循環(huán)次數(shù)的多少不會(huì)影響到外層的次數(shù)，但是外層循環(huán)的次數(shù)直接會(huì)影響到內(nèi)層循環(huán)的次數(shù)，外層循環(huán)每多一次，內(nèi)層循環(huán)就需要多完整的一次循環(huán)，所以我們優(yōu)化的目標(biāo)其實(shí)就是使外層的循環(huán)次數(shù)盡量少，總結(jié)來說：小表驅(qū)動(dòng)大表。小表就是外層循環(huán)，大表就是內(nèi)層循環(huán)，也就是盡量減少外層循環(huán)的次數(shù)
exists和in查詢?cè)淼膮^(qū)別
?? exists ： 外表先進(jìn)行循環(huán)查詢，將查詢結(jié)果放入exists的子查詢中進(jìn)行條件驗(yàn)證，確定外層查詢數(shù)據(jù)是否保留
?? in ： 先查詢內(nèi)表，將內(nèi)表的查詢結(jié)果當(dāng)做條件提供給外表查詢語句進(jìn)行比較
結(jié)論
通過上面的優(yōu)化策略分析和exists和in的查詢?cè)淼姆治?，將這兩塊內(nèi)容結(jié)合起來其實(shí)就得出了我們想要的一個(gè)結(jié)論：
外層小表，內(nèi)層大表（或者將sql從左到由來看：左面小表，右邊大表）： exists 比 in 的效率高
外層大表，內(nèi)層小表（或者將sql從左到由來看：左面大表，右邊小表）： in 比 exists 的效率高
參考資料
https://www.bilibili.com/video/BV1V64y1q7yi?spm_id_from=333.337.search-card.all.click
總結(jié)
上面我們簡(jiǎn)單介紹了一下平時(shí)我們比較少用到的一個(gè)exists的關(guān)鍵字，通過一些使用的實(shí)例，大家也可以基本上了解了它的使用方法，在以后的日常工作中，我們?cè)谂龅揭恍┎樵儐栴}的時(shí)候，這個(gè)時(shí)候就有了更加多樣化的選擇方案啦
到此這篇關(guān)于mysql中關(guān)鍵詞exists用法的文章就介紹到這了,更多相關(guān)mysql exists用法內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
mysql 表空間及索引的查看方法，需要的朋友可以參考下。2011-07-07
這篇文章主要介紹了mysql數(shù)據(jù)庫中的索引類型和原理，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-02-02
這篇文章主要介紹了mysql按照時(shí)間分組查詢的語句,非常實(shí)用，sql語句簡(jiǎn)單易懂，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-10-10
order?by?+?limit分頁時(shí)數(shù)據(jù)重復(fù)問題及解決方法
這篇文章主要介紹了order?by?+?limit分頁時(shí)數(shù)據(jù)重復(fù),本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2023-03-03
Mysql5.6啟動(dòng)內(nèi)存占用過高解決方案
vps的內(nèi)存為512M,安裝好nginx,php等啟動(dòng)起來,mysql死活啟動(dòng)不起來看了日志只看到對(duì)應(yīng)pid被結(jié)束了,后跟蹤看發(fā)現(xiàn)是內(nèi)存不足被killed;mysql5.6啟動(dòng)內(nèi)存占用過高怎么辦呢，下面小編給大家解答下2016-09-09
符串或串(String)是由零個(gè)或多個(gè)字符組成的有限序列。一般記為 s=’dreamflys’(n>=0)。它是編程語言中表示文本的數(shù)據(jù)類型2012-01-01
這篇文章主要給大家介紹了關(guān)于Mysql如何同時(shí)交換兩個(gè)表的表名,以及MySQL命令rename修改表名的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-01-01
這篇文章主要介紹了mysql多主雙向和級(jí)聯(lián)復(fù)制，架構(gòu)內(nèi)各個(gè)庫均同時(shí)開啟binlog的master和slave，主主庫額外開啟級(jí)聯(lián)復(fù)制開關(guān)，下面詳細(xì)內(nèi)容，需要的小伙伴可以參考一下2022-01-01
MySQL中出現(xiàn)lock?wait?timeout?exceeded問題及解決
這篇文章主要介紹了MySQL中出現(xiàn)lock?wait?timeout?exceeded問題及解決方案，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-08-08
SQL重復(fù)記錄查詢 查詢多個(gè)字段、多表查詢、刪除重復(fù)記錄的方法
下面小編就為大家?guī)硪黄猄QL重復(fù)記錄查詢 查詢多個(gè)字段、多表查詢、刪除重復(fù)記錄的方法。小編覺得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過來看看吧2016-09-09