mysql中 any、some、exist、not exist关键字的解释以及运用
本文主要是对mysql中比较易混的几个关键字的解释和运用来说明的
- any 或 some
any 或 some关键字是同义词,表示满足其中任何一个条件即可,它们允许创建一个表达式对子查询的返回值列表进行比较,只要满足内层子查询中的任何一个比较条件,就返回一个结果作为外层查询的条件
举例:
创建两个表并插入数据
create table tal1 (num1 int not null);
create table tal2 (num2 int not null);
insert into tal1 values (1),(5),(13),(27);
insert into tal2 values (6),(14),(11),(20);
mysql> select num1 from tal1 where num1 > ANY (select num2 from tal2);
+------+
| num1 |
+------+
| 13 |
| 27 |
+------+
2 rows in set (0.01 sec)
mysql> select num1 from tal1 where num1 > some (select num2 from tal2);
+------+
| num1 |
+------+
| 13 |
| 27 |
+------+
2 rows in set (0.02 sec)
- exists 和 not exists
exists关键字后面的参数是一个任意的子查询,系统对子查询进行运算以判断它是否返回行,如果至少返回一行,那么exists的结果就为true,此时外层的查询语句将进行查询;如果子查询没有返回任何行,那么exists返回的结果是false,此时外层语句将不进行查询。
举例:
创建两个表并插入数据
create table tal1 (num1 int not null);
create table tal2 (num2 int not null);
insert into tal1 values (1),(5),(13),(27);
insert into tal2 values (6),(14),(11),(20);
mysql> select num1 from tal1 where exists (select num2 from tal2);
+------+
| num1 |
+------+
| 1 |
| 5 |
| 13 |
| 27 |
+------+
4 rows in set (0.01 sec)
mysql> select num1 from tal1 where exists (select count(1) from tal2);
+------+
| num1 |
+------+
| 1 |
| 5 |
| 13 |
| 27 |
+------+
4 rows in set (0.01 sec)
mysql> select num1 from tal1 where exists (select num2 from tal2 where num2>100 );
Empty set (0.00 sec)
从上面代码看出 exists后面的子查询查询的什么数据并不重要,重要的是要返回数据
not exists 与exists的功能相反
举例:
mysql> select num1 from tal1 where exists (select num2 from tal2 where num2>100 );
Empty set (0.00 sec)
mysql> select num1 from tal1 where not exists (select num2 from tal2);
Empty set (0.00 sec)
mysql> select num1 from tal1 where not exists (select count(1) from tal2);
Empty set (0.00 sec)
mysql> select num1 from tal1 where not exists (select num2 from tal2 where num2>100 );
+------+
| num1 |
+------+
| 1 |
| 5 |
| 13 |
| 27 |
+------+
4 rows in set (0.01 sec)