技术分享 数据库 MYSQL 查看内容

子查询语句中ANY、SOME、ALL关键字的使用

老高 | 发布于 2019-06-19 22:59| 浏览()| 评论() | 收藏() | 打印

摘要: 在使用子查询语句的过程中经常使用ANY、SOME、ALL(另外还有一个IN)关键字作为筛选条件。

在使用子查询语句的过程中经常使用ANY、SOME、ALL(另外还有一个IN)关键字作为筛选条件。

ANY、SOME、ALL关键字的使用

使用关系表一览

运算符\关键字ANYSOMEALL
>>=最小值最小值最大值
<<=最大值最大值最小值
=任意值任意值
<>!=

任意值

操作示例

这里先创建一张数据表并插入一些数据用来作为演示:

mysql> CREATE TABLE salary_table(
    -> id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> position VARCHAR(40) NOT NULL,
    -> salary INT);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT salary_table(position,salary) VALUES('JAVA',8000),('Java',8400),('Java',9000),('Python',6500),('Python',10000),('Python',8900);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM salary_table;
+----+----------+--------+
| id | position | salary |
+----+----------+--------+
|  1 | JAVA     |   8000 |
|  2 | Java     |   8400 |
|  3 | Java     |   9000 |
|  4 | Python   |   6500 |
|  5 | Python   |  10000 |
|  6 | Python   |   8900 |
+----+----------+--------+
6 rows in set (0.00 sec)


① SOME和ANY的使用

下面语句查找出高于Python职位的其它职位,设定关键字为ANY\ SOME;

mysql> SELECT * FROM salary_table WHERE salary > ANY( SELECT salary FROM salary_table WHERE position = 'Python');
+----+----------+--------+
| id | position | salary |
+----+----------+--------+
|  1 | JAVA     |   8000 |
|  2 | Java     |   8400 |
|  3 | Java     |   9000 |
|  5 | Python   |  10000 |
|  6 | Python   |   8900 |
+----+----------+--------+
5 rows in set (0.02 sec)

mysql> SELECT * FROM salary_table WHERE salary > SOME( SELECT salary FROM salary_table WHERE position = 'Python');
+----+----------+--------+
| id | position | salary |
+----+----------+--------+
|  1 | JAVA     |   8000 |
|  2 | Java     |   8400 |
|  3 | Java     |   9000 |
|  5 | Python   |  10000 |
|  6 | Python   |   8900 |
+----+----------+--------+
5 rows in set (0.00 sec)

当查找关键字为SOME或ANY时,根据查询结果可以得出只要满足设定条件的最小值均可返回结果,与上关系表一致。在这个案例中只要所有salary大于Python职位中最低的6500,该字段就会被返回。 

另外可以得出的是使用ANY和SOME关键字的时候功能是一致的,在实际运用中使用ANY偏多一些,有的资料中说SOME是ANY的别名。


② ALL的使用

下面语句查找出高于Python职位的其它职位,设定关键字为ALL;

mysql> SELECT * FROM salary_table WHERE salary > ALL( SELECT salary FROM salary_table WHERE position = 'Java');
+----+----------+--------+
| id | position | salary |
+----+----------+--------+
|  5 | Python   |  10000 |
+----+----------+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM salary_table WHERE salary < ALL( SELECT salary FROM salary_table WHERE position = 'Java');
+----+----------+--------+
| id | position | salary |
+----+----------+--------+
|  4 | Python   |   6500 |
+----+----------+--------+
1 row in set (0.00 sec)

当查询关键字设定为ALL时,即所有的条目均要同时满足设定条件。在上述查询中,使用>时查找出大于Java中salary的最大值,而使用<时查找出小于Java中salary的最小值,总结的信息如上关系表一致。


③ =ANY 或 =SOME 等价于IN

IN:在指定项内,同 IN(项1,项2,…),IN关键字使用之前是需要提供一个列表的,而=ANY和=SOME则正好是筛选数据,组成新的满足条件的列表。 

这样我们可以很好理解到和=ANY或=SOME之间的等价关系。

mysql> SELECT * FROM salary_table WHERE salary IN (SELECT salary FROM salary_table WHERE position = 'Python');
+----+----------+--------+
| id | position | salary |
+----+----------+--------+
|  4 | Python   |   6500 |
|  5 | Python   |  10000 |
|  6 | Python   |   8900 |
+----+----------+--------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM salary_table WHERE salary = SOME (SELECT salary FROM salary_table WHERE position = 'Python');
+----+----------+--------+
| id | position | salary |
+----+----------+--------+
|  4 | Python   |   6500 |
|  5 | Python   |  10000 |
|  6 | Python   |   8900 |
+----+----------+--------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM salary_table WHERE salary = ANY (SELECT salary FROM salary_table WHERE position = 'Python');
+----+----------+--------+
| id | position | salary |
+----+----------+--------+
|  4 | Python   |   6500 |
|  5 | Python   |  10000 |
|  6 | Python   |   8900 |
+----+----------+--------+
3 rows in set (0.00 sec)


发表评论(对文章涉及的知识点还有疑问,可以在这里留言,老高看到后会及时回复的。)

表情
返回顶部