9. 子查询/INSERT/UPDATE/DELETE/REPLACE

一. 子查询

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

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

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

(select a from t2) 是子查询

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

 

1. 子查询的使用

1.1 ANY / SOME

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

[root@mysql.sock][dbt3_s1]> create table t1(a int);
Query OK, 0 rows affected (0.34 sec)

[root@mysql.sock][dbt3_s1]> create table t2(a int);
Query OK, 0 rows affected (0.02 sec)

[root@mysql.sock][dbt3_s1]> insert into t1 values(10),(4);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

[root@mysql.sock][dbt3_s1]> insert into t2 values(12),(13),(5);
Query OK, 3 rows affected (0.30 sec)
Records: 3  Duplicates: 0  Warnings: 0

[root@mysql.sock][dbt3_s1]> select a from t1;
+------+
| a    |
+------+
|   10 |
|    4 |
+------+
2 rows in set (0.00 sec)

[root@mysql.sock][dbt3_s1]> select a from t2;
+------+
| a    |
+------+
|   12 |
|   13 |
|    5 |
+------+
3 rows in set (0.00 sec)

[root@mysql.sock][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"

[root@mysql.sock][dbt3_s1]> insert into t1 values(5);
Query OK, 1 row affected (0.26 sec)

[root@mysql.sock][dbt3_s1]> select a from t1 where a = ANY(select a from t2);
+------+
| a    |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

[root@mysql.sock][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的（外查询）的记录

[root@mysql.sock][dbt3_s1]> truncate t1;
Query OK, 0 rows affected (0.20 sec)

[root@mysql.sock][dbt3_s1]> truncate table t2;
Query OK, 0 rows affected (0.06 sec)

[root@mysql.sock][dbt3_s1]> insert into t1 values(10),(4);
Query OK, 2 rows affected (0.29 sec)
Records: 2  Duplicates: 0  Warnings: 0

[root@mysql.sock][dbt3_s1]> insert into t2 values(5),(4),(3);
Query OK, 3 rows affected (0.30 sec)
Records: 3  Duplicates: 0  Warnings: 0

[root@mysql.sock][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. 子查询的分类

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

[root@mysql.sock][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
[root@mysql.sock][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的形式

select a from t1 where a in (select a from t2); -- 这个是一条独立的子查询，时间复杂度 O(M+N)
--
-- 经过优化器重写后
--
select a from t1 where exists (select 1 from t2 where t1.a = t2.a); -- 这是相关子查询，复杂度O(M*N + M)

所以在 MySQL 5.6 之前，部分的子查询需要重写成join的形式 （注意表的大小）,但在 MySQL 5.6 之后，优化器不会将子查询重写成 exists 的形式，而是自动优化，性能有了大幅提升

 

4. 包含NULL值的NOT IN

• MySQL数据库的 比较操作 ，除了返回 1(True) , 0(False) 之外，还会返回 NULL
• NULL 和 NULL 的比较，返回的还是 NULL 
• 和 null比较，使用is和is not， 而不是 = 和 <>
• 对于包含了 NULL 值的 IN 操作，总是返回 True 或者 NULL。NOT IN 返回 NOT True (False) 或者 NOT NULL (NULL)

root@mysqldb 09:53:  [test]> select null in ('a', 'b', null);
+--------------------------+
| null in ('a', 'b', null) |
+--------------------------+
|                     NULL |
+--------------------------+
1 row in set (0.00 sec)

mysql> select null not in ('a', 'b', null); 
+------------------------------+
| null not in ('a', 'b', null) |
+------------------------------+
| NULL | -- null不在('a', 'b', null)中，返回的还是null，因为有null和null的比较
+------------------------------+
1 row in set (0.00 sec)
mysql> select 'a' not in ('a', 'b', null); 
+-----------------------------+
| 'a' not in ('a', 'b', null) |
+-----------------------------+
| 0 | -- a 不在 ('a', 'b', null)中，返回0,即False
+-----------------------------+
1 row in set (0.00 sec)
mysql> select 'c' not in ('a', 'b');
+-----------------------+
| 'c' not in ('a', 'b') |
+-----------------------+
| 1 | -- 这个返回值可以理解 'c'不在('a', 'b')中，返回1，即为True
+-----------------------+
1 row in set (0.00 sec)
mysql> select 'c' not in ('a', 'b', null); 
+-----------------------------+
| 'c' not in ('a', 'b', null) |
+-----------------------------+
| NULL | -- 理论上应该是返回1，即True的。但是包含了null值。则返回null
+-----------------------------+
1 row in set (0.00 sec)

 

二. INSERT

insert into t1 values(1); -- 插入一个值
insert into t1 values(2),(3),(-1); -- 插入多个值，MySQL独有
insert into t1 select 8; -- insert XXX select XXX 语法，MySQ独有
insert into t3(b) select a from t2; -- 从t2表中查询数据并插入到t3(a)中，注意指定列
create table t3 (select * from t2); -- 将t2表中的数据查询出来插入刚刚创建的t3表中

 

三. DELETE

delete from t3 where a is null; -- 根据过滤条件删除
delete from t3; -- 删除整个表

 

四. UPDATE

update t3 set a=10 where a=1;
update t1 join t2 on t1.a = t2.a set t1.a=100; -- 先得到t1.a=t2.a的结果集
                                               -- 然后将结果集中的t1.a设置为100

 

五. REPLACE

root@mysqldb 10:16:  [test]> create table t4 (
    -> a int primary key auto_increment, 
    -> b int
    -> );
Query OK, 0 rows affected (0.01 sec)

root@mysqldb 10:17:  [test]> select * from t4;
+---+------+
| a | b    |
+---+------+
| 1 |   10 |
| 2 |   20 |
| 3 |   30 |
+---+------+
3 rows in set (0.00 sec)

root@mysqldb 10:17:  [test]> insert into t4 values(3, 30);
ERROR 1062 (23000): Duplicate entry '3' for key 't4.PRIMARY'  -- 主键重复

root@mysqldb 10:17:  [test]> replace into t4 values(3, 30); -- 可以插入。先delete再insert
Query OK, 1 row affected (0.01 sec)

root@mysqldb 10:23:  [test]> replace into t4 values(4, 40);  -- 没有替换对象时等于insert
Query OK, 1 row affected (0.00 sec)

--
-- insert on duplicate 效果和 replace类似
--
insert into t4 values(1,1) on duplicate key update b=1; -- 带上on duplicate参数
                                                        -- 非SQL标准，不推荐

--
-- insert ignore
--
root@mysqldb 10:24:  [test]> insert ignore into t4 values(1,1); -- 忽略重复的错误
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@mysqldb 10:25:  [test]> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 't4.PRIMARY' |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

 

六. 显示行号(RowNumber)  

--
-- 方法一
--
mysql> use employees;
mysql> set @rn:=0; -- 产生 SESSION(会话)级别的变量
mysql> select @rn:=@rn+1 as rownumber, emp_no, gender from employees limit 10; -- := 是赋值的意思

--
-- 方法二 （推荐）
--
mysql> select @rn1:=@rn1+1 as rownumber, emp_no, gender from employees, (select @rn1:=0) as a limit 10;

-- MySQL 自定义变量，根据每一记录进行变化的
mysql> select @rn1:=0;
+---------+
| @rn1:=0 |
+---------+
| 0       | -- 只有一行记录
+---------+
1 row in set (0.00 sec)
-- 相当于 把 employees 和 (select @rn1:=0)做了笛卡尔积，然后使用@rn1:=@rn + 1，根据每行进行累加

--
-- ":=" 和 "="
--
mysql> set @a:=1; -- 赋值为1
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+------+
| @a   |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

mysql> set @a:=10; -- 赋值为10
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+------+
| @a   |
+------+
| 10   |
+------+
1 row in set (0.00 sec)

mysql> select @a=9; -- 进行比较
+------+
| @a=9 |
+------+
| 0    | -- 返回为False
+------+
1 row in set (0.00 sec)