比较运算符
比较对象 比较运算符 子查询
mysql> SELECT * FROM 运算符例子;
+----+------+
| id | 数值 |
+----+------+
| 1 | 1 |
| 2 | 3 |
| 3 | 5 |
| 4 | 7 |
| 5 | 9 |
| 6 | 11 |
| 7 | 13 |
+----+------+
7 rows in set (0.01 sec)
mysql> SELECT * FROM 运算符例子2;
+----+------+
| id | 数值 |
+----+------+
| 1 | 2 |
| 2 | 4 |
| 3 | 6 |
| 4 | 8 |
| 5 | 10 |
| 6 | 12 |
| 7 | 14 |
+----+------+
7 rows in set (0.00 sec)
#查找 运算符例子表中 数值小于 运算符例子2表中 id=5(10)的数据
mysql> SELECT id,数值 FROM 运算符例子 WHERE 数值 < (SELECT 数值 FROM 运算符例子2 WHERE id=5);
+----+------+
| id | 数值 |
+----+------+
| 1 | 1 |
| 2 | 3 |
| 3 | 5 |
| 4 | 7 |
| 5 | 9 |
+----+------+
5 rows in set (0.00 sec)
#10>9=1(true)
mysql> SELECT (SELECT 数值 FROM 运算符例子2 WHERE id=5) > (SELECT 数值 FROM 运算符例子 WHERE id = 1) AS 比较;
+------+
| 比较 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
#10<9=0(false)
mysql> SELECT (SELECT 数值 FROM 运算符例子2 WHERE id=5) < (SELECT 数值 FROM 运算符例子 WHERE id = 1) AS 比较;
+------+
| 比较 |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
用ANY、SOME或ALL修饰的比较运算符
| 运算符/关键字 | ANY | SOME | ALL |
|---|---|---|---|
| >、>= | 最小值 | 最小值 | 最大值 |
| <、<= | 最大值 | 最大值 | 最小值 |
| = | 任意值 | 任意值 | |
| <>、!=、 | 任意值 |
比较对象 比较运算符 ANY(子查询)
-
假设子查询返回结果为值1,值2,值3举例:
值 > ANY(...)
实际上等于 值 > 值1 OR 值>值2 OR 值>值3
相当于 或
ANY
比较对象 比较运算符 SOME(子查询)
- 和ANY一样也相当于 或
SOME
比较对象 比较运算符 ALL(子查询)
-
假设子查询返回结果为值1,值2,值3举例:
值 > SOME(...)
实际上等于 值 > 值1 AND 值>值2 AND 值>值3
相当于 与
ALL
[NOT] IN
这个就相当于Python中的find,用来确认是两组数据是否相同或不同
值 运算符 [NOT] IN (子查询)
- =ANY运算符与IN等效
- != ALL或者<>ALL运算与NOT IN等效
mysql> select * from 运算符例子;
+----+------+
| id | 数值 |
+----+------+
| 1 | 1 |
| 2 | 3 |
| 3 | 5 |
| 4 | 7 |
| 5 | 9 |
| 6 | 11 |
| 7 | 13 |
+----+------+
7 rows in set (0.00 sec)
mysql> select * from 运算符例子2;
+----+------+
| id | 数值 |
+----+------+
| 1 | 2 |
| 2 | 4 |
| 3 | 6 |
| 4 | 8 |
| 5 | 10 |
| 6 | 12 |
| 7 | 14 |
| 8 | 13 |
+----+------+
8 rows in set (0.00 sec)
mysql> select id,数值 from 运算符例子 WHERE 数值 != ALL(select 数值 from 运算符例子2 WHERE 数值);
+----+------+
| id | 数值 |
+----+------+
| 1 | 1 |
| 2 | 3 |
| 3 | 5 |
| 4 | 7 |
| 5 | 9 |
| 6 | 11 |
+----+------+
6 rows in set (0.00 sec)
mysql> select id,数值 from 运算符例子 WHERE 数值 NOT IN(select 数值 from 运算符例子2 WHERE 数值);
+----+------+
| id | 数值 |
+----+------+
| 1 | 1 |
| 2 | 3 |
| 3 | 5 |
| 4 | 7 |
| 5 | 9 |
| 6 | 11 |
+----+------+
6 rows in set (0.00 sec)
mysql> select id,数值 from 运算符例子 WHERE 数值 = ANY(select 数值 from 运算符例子2 WHERE 数值);
+----+------+
| id | 数值 |
+----+------+
| 7 | 13 |
+----+------+
1 row in set (0.00 sec)
mysql> select id,数值 from 运算符例子 WHERE 数值 IN (select 数值 from 运算符例子2 WHERE 数值);
+----+------+
| id | 数值 |
+----+------+
| 7 | 13 |
+----+------+
1 row in set (0.00 sec)
