子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

子查询中常用的操作符有 ANY（SOME）、ALL、IN 和 EXISTS。http://www.cndba.cn/hbhe0316/article/22647

子查询可以添加到 SELECT、UPDATE 和 DELETE 语句中，而且可以进行多层嵌套。子查询也可以使用比较运算符，如“<”、“<=”、“>”、“>=”、“！=”等。

1.在t1表中找到id最大的行http://www.cndba.cn/hbhe0316/article/22647

mysql> select * from t1 order by id desc limit 1;
+----------+------+
| id       | name |
+----------+------+
| 17530622 | aaa  |
+----------+------+
1 row in set (11.04 sec)

如果使用子查询

mysql> select * from t1 where id = (select max(id ) from t1);
+----------+------+
| id       | name |
+----------+------+
| 17530622 | aaa  |
+----------+------+
1 row in set (22.25 sec)

为什么这么慢？两张表成绩去查询。

mysql> explain select * from t1 where id = (select max(id ) from t1);
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | PRIMARY     | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 15728923 |    10.00 | Using where |
|  2 | SUBQUERY    | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 15728923 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

优化一下继续下一步吧http://www.cndba.cn/hbhe0316/article/22647

mysql> create index index_t1 on t1 (id);
Query OK, 0 rows affected (57.51 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from t1 where id = (select max(id ) from t1);
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+------------------------------+
|  1 | PRIMARY     | t1    | NULL       | ref  | index_t1      | index_t1 | 5       | const |    1 |   100.00 | Using where                  |
|  2 | SUBQUERY    | NULL  | NULL       | NULL | NULL          | NULL     | NULL    | NULL  | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> select * from t1 where id = (select max(id ) from t1);
+----------+------+
| id       | name |
+----------+------+
| 17530622 | aaa  |
+----------+------+
1 row in set (0.00 sec)

2.查询大于平均id值的数量http://www.cndba.cn/hbhe0316/article/22647

mysql> select count(*) from t1 where id > (select avg(id ) from t1);
+----------+
| count(*) |
+----------+
|  8983266 |
+----------+
1 row in set (21.00 sec)

3.子查询中带有IN

mysql> select * from t1 where id in (select max(id) from t2);
+--------+------+
| id     | name |
+--------+------+
| 499999 | aaa  |
+--------+------+
1 row in set (23.79 sec)

版权声明：本文为博主原创文章，未经博主允许不得转载。

MYSQL