MySQL数据查询之子查询

简介：

子查询指一个查询语句嵌套在另一个查询语句内部的查询。

子查询中常用的操作符有ANY、ALL、IN、EXISTS。也可以使用比较运算符，<、<=、>、>=和!=

1、准备两个表

#创建tb11表
MariaDB [vincen]> create table tb11
    -> (
    -> num1 INT NOT NULL
    -> )
    -> ;
Query OK, 0 rows affected (0.03 sec)
#为tb11表插入数据
MariaDB [vincen]> insert into tb11 values (1),(5),(13),(27);
#创建tb12表
MariaDB [vincen]> create table tb12
    -> (
    -> num2 INT NOT NULL
    -> )
    -> ;
Query OK, 0 rows affected (0.01 sec)
#为tb12表插入数据
MariaDB [vincen]> insert into tb12 values (6),(14),(11),(20);

2、ANY子查询

#num1和num2两个表的数据比较
MariaDB [vincen]> select num1               #查询num1字段
    -> from tb11                            #从tb11表中
    -> where                                #条件是
    -> num1 > ANY (select num2 from tb12)   #num1字段的数据要大于num2的任何一个值
    -> ;
+------+
| num1 |
+------+
|   13 |
|   27 |
+------+
2 rows in set (0.01 sec)
#在num1字段中13大于num2字段的6
#在num1字段的27大于num2字段的所有数据
#ANY关键字的要求是任何一个数值，也就是最少一个，否则不成立

3、ALL子查询

#查询tb11表中比tb12表num2的列所有值都大的值
MariaDB [vincen]> select num1               #查询num1字段
    -> from tb11                            #从tb11表中
    -> where                                #条件是
    -> num1 > ALL (select num2 from tb12)   #num1字段的大于num2字段中所有数据的值
    -> ;                                    #结束
+------+
| num1 |
+------+
|   27 |
+------+
1 row in set (0.00 sec)
#num1中的数据只有27比num2字段的所有数据都要大，因此查询结果为27
#和ANY不同的是，ALL要满足所有要求

4、EXISTS子查询

EXISTS关键字后面的参数是一个任意的子查询，系统判断这个子查询是否返回至少一条记录，如果可以返回至少一条记录，则EXISTS的结果为true；如果不能返回至少一条记录，则EXISTS的结果为false，并且不执行整个查询操作。

MariaDB [vincen]> select * from fruits    #查询fruits表中所有的列    
    -> where f_price > 10.20              #条件是f_price列中的数值大于10.20
    -> AND EXISTS                         #判断后面的子查询是否返回至少一条记录
    -> (select s_name from suppliers      #子查询，查询suppliers表中的s_name列的数据
    -> where s_id = 107)                  #条件是s_id列中的数值等于107
    -> ;                                  #结束
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| bs1  |  102 | orange |   11.20 |
| m1   |  106 | mango  |   15.60 |
| m3   |  105 | xxtt   |   11.60 |
| t1   |  102 | banana |   10.30 |
+------+------+--------+---------+
4 rows in set (0.01 sec)                #返回结果有4条记录
#首先在子查询里的suppliers表中存在s_id=107的记录
#然后EXISTS表达式返回结果为true
#在外层语句接收true之后根据结果查询条件f_price大于10.20的查询操作
#EXISTS返回的结果并不会显示出来，系统内部将这结果返回给外层查询，再确认是否执行外层的查询操作

5、IN子查询

IN关键字查询的时候，内层查询只返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作

MariaDB [vincen]> select c_id from orders  #查询orders表中的c_id列
    -> where o_num                         #条件是0_num列的值
    -> IN (30003,30005)                    #值为30003或30005
    -> ;                                   #结束
+-------+
| c_id  |
+-------+
| 10004 |
| 10001 |
+-------+
2 rows in set (0.01 sec)              #结果返回两条记录

6、比较运算子查询

MariaDB [vincen]> select s_id,f_name from fruits  #查询fruits表的s_id列和f_name列
    -> where s_id =                             #条件是s_id的数据等于
    -> (                                  #子查询
    -> select s1.s_id from suppliers      #查询suppliers表中的s_id字段
    -> AS                                 #将这个查询结果命名为
    -> s1                                 #s1
    -> where s1.s_city = 'Tianjin'        #条件是s_city列中字符为Tianjin
    -> )              
    -> ;                                  #结束
+------+------------+
| s_id | f_name     |
+------+------------+
|  101 | apple      |
|  101 | blackberry |
|  101 | cherry     |
+------+------------+
3 rows in set (0.00 sec)            #返回了3条记录