9. 子查询/INSERT/UPDATE/DELETE/REPLACE（未完成）

一. 子查询

子查询就是指在一个select语句中嵌套另外一个select语句。同时子查询必须包含括号。MySQL 5.6之前，子查询的性能较差，但是从5.6开始，不存在性能差的问题。

select a from t1 where a > any(select a from t2);

1. select a from t1是外部查询（outer query）

2. (select a from t2) 是子查询

一般来说子查询嵌套与外部查询中，可以将两个或两个以上子查询嵌套

1. 子查询的使用

1.1 ANY / SOME

如果外部查询列的结果和子查询列的结果比较得到为True，则返回比较值为True的（外查询）的记录

[[email protected]][dbt3_s1]> create table t1(a int);
Query OK, 0 rows affected (0.34 sec)

[[email protected]][dbt3_s1]> create table t2(a int);
Query OK, 0 rows affected (0.02 sec)

[[email protected]][dbt3_s1]> insert into t1 values(10),(4);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

[[email protected]][dbt3_s1]> insert into t2 values(12),(13),(5);
Query OK, 3 rows affected (0.30 sec)
Records: 3  Duplicates: 0  Warnings: 0

[[email protected]][dbt3_s1]> select a from t1;
+------+
| a    |
+------+
|   10 |
|    4 |
+------+
2 rows in set (0.00 sec)

[[email protected]][dbt3_s1]> select a from t2;
+------+
| a    |
+------+
|   12 |
|   13 |
|    5 |
+------+
3 rows in set (0.00 sec)

[[email protected]][dbt3_s1]> select a from t1 where a > ANY(select a from t2); -- 子查询返回(12,13,5)
+------+                                                                      -- t1 a列只要大于(12,13,5)中的任一值
| a    |                                                                      -- 即t1.a > t2.a为True，则返回t1.a
+------+
|   10 |
+------+
1 row in set (0.07 sec)
-- 这个查询可以解释为，t1表内a列的值 大于t2表中a列的任意(any)一个值（t1.a > any(t2.a) == true）,则返回t1.a的记录

ANY 关键词必须与一个 比较操作符 一起使用：  = ,  > ,  < ,  >= ,  <= ,  <> (这个是!=的意思)

子查询中 SOME 和 ANY 是同一个意思

1.2. IN

in 是 ANY 的一种特殊情况： "in" equals "= any"

[[email protected]][dbt3_s1]> insert into t1 values(5);
Query OK, 1 row affected (0.26 sec)

[[email protected]][dbt3_s1]> select a from t1 where a = ANY(select a from t2);
+------+
| a    |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

[[email protected]][dbt3_s1]> select a from t1 where a in (select a from t2);
+------+
| a    |
+------+
|    5 |
+------+
1 row in set (0.01 sec)

 select a from t1 where a in (select a from t2); 是用的比较多的一种语法

1.3. ALL

如果外部查询的列的结果和子查询的列的 所有结果 比较得到为True的话，则返回比较值为True的（外查询）的记录

[[email protected]][dbt3_s1]> truncate t1;
Query OK, 0 rows affected (0.20 sec)

[[email protected]][dbt3_s1]> truncate table t2;
Query OK, 0 rows affected (0.06 sec)

[[email protected]][dbt3_s1]> insert into t1 values(10),(4);
Query OK, 2 rows affected (0.29 sec)
Records: 2  Duplicates: 0  Warnings: 0

[[email protected]][dbt3_s1]> insert into t2 values(5),(4),(3);
Query OK, 3 rows affected (0.30 sec)
Records: 3  Duplicates: 0  Warnings: 0

[[email protected]][dbt3_s1]> select a from t1 where a > all(select a from t2);
+------+
| a    |
+------+
|   10 |  -- (10 > 5, 4, 3 为True) 而(4 >5, 4, 3 为False)
+------+
1 row in set (0.00 sec)

ALL 关键词必须与一个 比较操作符 一起使用
NOT IN  是  <> ALL 的别名

2. 子查询的分类

独立子查询

不依赖外部查询而运行的子查询

[[email protected]][dbt3_s1]> select a from t1 where a in (1,2,3,4,5);
+------+
| a    |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

相关子查询

引用了外部查询的子查询

-- 这个例子中，子查询使用了外部的列t2.a
[[email protected]][dbt3_s1]> select a from t1 where a in (select * from t2 where t1.a = t2.a);
+------+
| a    |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

3. 子查询的优化

MySQL 5.6之前

  在MySQL 5.6之前，优化器会把子查询重写成exists的形式