MySQL 5.7 NOT EXISTS用法介绍

ゝ一纸荒年。 2024-04-18 00:22 57阅读 0赞

在MySQL中没有MINUS语句，要想实现MINUS语句，可以使用NOT EXISTS语句。  
  
查看测试表中的数据  
 mysql> select \* from person;  
\+----------------+-----------------+-----------------+  
| MSISDN         | IMEI            | IMSI            |  
\+----------------+-----------------+-----------------+  
| +3301000000000 | 129980000000000 | 208011000000000 |  
| +3301000000001 | 129980000000100 | 208011000000001 |  
| +3301000000002 | 129980000000200 | 208011000000002 |  
| +3301000000003 | 129980000000300 | 208011000000003 |  
| +3301000000004 | 129980000000400 | 208011000000004 |  
| +3301000000005 | 129980000000500 | 208011000000005 |  
| +3301000000006 | 129980000000600 | 208011000000006 |  
| +3301000000007 | 129980000000700 | 208011000000007 |  
\+----------------+-----------------+-----------------+  
8 rows in set (0.00 sec)  
mysql> select \* from card;  
\+-----------------+----------------+-----------------+  
| IMSI            | MSISDN         | IMEI            |  
\+-----------------+----------------+-----------------+  
| 208011000000000 | +3301000000000 | 129980000000000 |  
| 208011000000001 | +3301000000001 | 129980000000100 |  
| 208011000000002 | +3301000000002 | 129980000000200 |  
| 208011000000003 | +3301000000003 | 129980000000300 |  
| 208011000000004 | +3301000000004 | 129980000000400 |  
| 208011000000005 | +3301000000005 | 129980000000500 |  
| 208011000000006 | +3301000000006 | 129980000000600 |  
| 208011000000007 | +3301000000007 | 129980000000700 |  
| 208011000000008 | +3301000000008 | 129980000000800 |  
| 208011000000009 | +3301000000009 | 129980000000900 |  
\+-----------------+----------------+-----------------+  
10 rows in set (0.00 sec)

#### 查询出在card中存在，但是在person中不存在的某一列MSISDN。 ####

mysql> select \* from card a where not exists(select \* from person b where a.MSISDN=b.MSISDN);  
\+-----------------+----------------+-----------------+  
| IMSI            | MSISDN         | IMEI            |  
\+-----------------+----------------+-----------------+  
| 208011000000008 | +3301000000008 | 129980000000800 |  
| 208011000000009 | +3301000000009 | 129980000000900 |  
\+-----------------+----------------+-----------------+  
2 rows in set (0.00 sec)  
  
查看执行计划  
  mysql> explain select \* from card a where not exists(select \* from person b where a.MSISDN=b.MSISDN);  
\+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+  
| id | select\_type        | table | partitions | type   | possible\_keys | key     | key\_len | ref           | rows | filtered | Extra                    |  
\+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+  
|  1 | PRIMARY            | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL          |   10 |   100.00 | Using where              |  
|  2 | DEPENDENT SUBQUERY | b     | NULL       | eq\_ref | PRIMARY       | PRIMARY | 20      | fire.a.MSISDN |    1 |   100.00 | Using where; Using index |  
\+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+  
2 rows in set, 2 warnings (0.00 sec)  
  
通过执行计划，card表使用了全表扫描，person表使用了主键索引   
mysql> show keys from card;  
\+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
| Table | Non\_unique | Key\_name             | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment | Index\_comment |  
\+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
| card  |          0 | PRIMARY              |            1 | IMSI        | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |  
\+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
6 rows in set (0.00 sec)  
mysql> show keys from person;  
\+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
| Table  | Non\_unique | Key\_name | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment | Index\_comment |  
\+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
| person |          0 | PRIMARY  |            1 | MSISDN      | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |  
\+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
1 row in set (0.00 sec)  
  
 由于两张表的主键不是相同列，  创建索引进行测试， 发现card表始终无法使用索引扫描  
 mysql> create index idx\_card\_msisdn on card(msisdn);  
Query OK, 0 rows affected (0.22 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
mysql> explain select \* from card a where not exists(select \* from person b where a.MSISDN=b.MSISDN);  
\+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+  
| id | select\_type        | table | partitions | type   | possible\_keys | key     | key\_len | ref           | rows | filtered | Extra                    |  
\+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+  
|  1 | PRIMARY            | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL          |   10 |   100.00 | Using where              |  
|  2 | DEPENDENT SUBQUERY | b     | NULL       | eq\_ref | PRIMARY       | PRIMARY | 20      | fire.a.MSISDN |    1 |   100.00 | Using where; Using index |  
\+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+  
2 rows in set, 2 warnings (0.00 sec)  
  
mysql> create index idx\_card\_msisdn\_imsi on card(msisdn,imsi);  
Query OK, 0 rows affected (0.06 sec)  
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select \* from card a where not exists(select \* from person b where a.MSISDN=b.MSISDN);

\+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+  
| id | select\_type        | table | partitions | type   | possible\_keys | key     | key\_len | ref           | rows | filtered | Extra                    |  
\+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+  
|  1 | PRIMARY            | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL          |   10 |   100.00 | Using where              |  
|  2 | DEPENDENT SUBQUERY | b     | NULL       | eq\_ref | PRIMARY       | PRIMARY | 20      | fire.a.MSISDN |    1 |   100.00 | Using where; Using index |  
\+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+  
2 rows in set, 2 warnings (0.00 sec)  
  
mysql> create index idx\_card\_imsi\_msisdn on card(imsi,msisdn);  
Query OK, 0 rows affected (0.06 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
mysql> explain select \* from card a where not exists(select \* from person b where a.MSISDN=b.MSISDN);  
\+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+  
| id | select\_type        | table | partitions | type   | possible\_keys | key     | key\_len | ref           | rows | filtered | Extra                    |  
\+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+  
|  1 | PRIMARY            | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL          |   10 |   100.00 | Using where              |  
|  2 | DEPENDENT SUBQUERY | b     | NULL       | eq\_ref | PRIMARY       | PRIMARY | 20      | fire.a.MSISDN |    1 |   100.00 | Using where; Using index |  
\+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+  
2 rows in set, 2 warnings (0.00 sec)

mysql> explain select \* from card a where not exists(select \* from person b where a.MSISDN=b.MSISDN);  
\+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+  
| id | select\_type        | table | partitions | type   | possible\_keys | key     | key\_len | ref           | rows | filtered | Extra                    |  
\+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+  
|  1 | PRIMARY            | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL          |   10 |   100.00 | Using where              |  
|  2 | DEPENDENT SUBQUERY | b     | NULL       | eq\_ref | PRIMARY       | PRIMARY | 20      | fire.a.MSISDN |    1 |   100.00 | Using where; Using index |  
\+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+  
2 rows in set, 2 warnings (0.00 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2123740/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2123740/

发表评论取消回复

表情：

评论列表（有 0 条评论，57人围观）

还没有评论，来说两句吧...

相关阅读

相关 day57-2 MySQL基本数据类型

目录 mysql中的数据类型 1 整数注意：默认情况整数是有符号的 2 浮点数

痛定思痛。/ 2023年10月09日 11:30/ 0 赞/ 43 阅读

相关 mysql57修改密码语句

mysql5.7.17免安装版怎样设置默认密码 1.在my.ini中配置explicit\_defaults\_for\_timestamp参数如我的系统：D:\\P

我会带着你远行/ 2023年09月25日 10:34/ 0 赞/ 67 阅读

相关 windows环境怎么安装mysql57

MySQL 5.7怎样安装从MSQL官网下载MySQL服务器安装软件包，我下载为版本“mysql-installer-community-5.7.4.0-m14.141

àì夳堔傛蜴生んèń/ 2023年09月25日 10:32/ 0 赞/ 182 阅读

相关 mysql57审计功能开启

mysql开启安全审计功能。 mysql服务器自身没有提供审计功能,但是我们可以使用init-connect+binlog的方法进行mysql的操作审计。由于mysqlb

喜欢ヅ旅行/ 2023年09月25日 10:31/ 0 赞/ 65 阅读

相关 mysql57修改用户密码

mysql 5.7 怎么修改默认密码，随机密码一般命令行修改 mysql-uroot-p >updatemysql.usersetpassword=PASSWORD

ゞ浴缸里的玫瑰/ 2023年09月25日 10:31/ 0 赞/ 158 阅读

相关 mysql57压缩包安装教程

centos7mysql5.7版本的数据库怎么连接在CentOS7上安装MySQL5.7 \[日期：2016-06-26\]来源：Linux社区作者：Linux\[字

「爱情、让人受尽委屈。」/ 2023年09月25日 10:31/ 0 赞/ 80 阅读

相关 mysql57中文手册

谁有mysql5.7从入门到精通这本书的pdf版啊，求分享一下云盘链接已私信发送给你了，你看一下是不是你需要的如果还有哪位朋友想要，请点“赞”此条回答以后，在下面的评论

秒速五厘米/ 2023年09月25日 10:30/ 0 赞/ 164 阅读

相关 CentOS7 配置MySQL57

文章目录一、下载二、安装三、启动四、密码五、授权六、自启动一、下载 wget http://dev.my

淩亂°似流年/ 2022年11月17日 13:34/ 0 赞/ 335 阅读

相关 CentOS 7安装Mysql57数据库

软件要求虚拟机：VMware Workstation 系统版本：CentOS-7-x86\_64(Linux) Mysql版本：mysql57-communit

刺骨的言语ヽ痛彻心扉/ 2022年04月05日 08:21/ 0 赞/ 403 阅读

相关 yum安装mysql57的操作步骤

yum安装mysql57的操作步骤： 1.现在centos上默认是没有yum源的，yum安装的是 MariaDB。所以我们需要自己先配置yum源。配置yum源步骤如下：

Bertha 。/ 2022年01月23日 05:43/ 0 赞/ 838 阅读