在使用子查询语句的过程中经常使用ANY、SOME、ALL(另外还有一个IN)关键字作为筛选条件。
使用关系表一览
运算符\关键字 | ANY | SOME | ALL |
---|---|---|---|
> 、>= | 最小值 | 最小值 | 最大值 |
< 、<= | 最大值 | 最大值 | 最小值 |
= | 任意值 | 任意值 | |
<> 、!= | 任意值 |
这里先创建一张数据表并插入一些数据用来作为演示:
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)
发表评论(对文章涉及的知识点还有疑问,可以在这里留言,老高看到后会及时回复的。)