子查询最佳化

举一些子查询的例子方便大家容易理解

mysql> select m,n from (select m2+1 as m, n2 as n from t2 where m2 > 2) as t;
+------+------+
| m    | n    |
+------+------+
|    4 | c    |
|    5 | d    |
+------+------+
2 rows in set (0.00 sec)

这个例子中子查询就是select m2+1 as m, n2 as n from t2 where m2 > 2
以这个子查询的结果当作表t来查询,这种放在from後面的子查询又称为衍生表。

子查询的类型可以分为几种:

  1. 按照返回结果集区分子查询
    纯量子查询:只返回一个单一值的子查询
mysql> select m1 from t1 limit1;
+------+
| m1   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

mysql> select min(m2) from t2;
+---------+
| min(m2) |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

行子查询:返回一笔纪录的子查询,包含多个列

mysql> select m2,n2 from t2 limit 1;
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
+------+------+
1 row in set (0.00 sec)

列子查询:查询出一个列的资料,不过这个列的资料需要包含多笔纪录(只有一笔就是纯量了)

mysql> select m2 from t2;
+------+
| m2   |
+------+
|    2 |
|    3 |
|    4 |
+------+
3 rows in set (0.00 sec)

表子查询:查询出多笔纪录且有多个列。

mysql> select m2,n2 from t2;
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.00 sec)
  1. 按与外层查询的关系来区分子查询
    不相关子查询:如果子查询可以单独运行,不需要外层查询的值,就是不相关子查询,前面提到的都是。
    相关子查询:子查询需要外层查询的值。
mysql> select * from t1 where m1 in (select m2 from t2 where n1 = n2);
+------+------+
| m1   | n1   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.02 sec)

子查询select m2 from t2 where n1 = n2
由於n1是t1的列,所以这是个相关子查询(依赖外层查询的列)。

  1. 子查询在布林运算式中的使用
    使用=、>、<、>=、<=、!=等作为布林运算式的符号
mysql> select * from t1 where m1 < (select min(m2) from t2);
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.01 sec)

mysql> select * from t1 where m1 < (select max(m2) from t2);
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from t1 where (m1,n1) = (select m2,n2 from t2 limit 1);
+------+------+
| m1   | n1   |
+------+------+
|    2 | b    |
+------+------+
1 row in set (0.00 sec)

要注意的是这里一定只能是纯量子查询(单一值)或是行子查询(一笔纪录)喔!!

[not]in/any/some/all子查询
当子查询的结果有多笔纪录的时候使用如下:

mysql> select * from t1 where (m1,n1) in (select m2,n2 from t2);
+------+------+
| m1   | n1   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.00 sec)

找出t1的m1,n1列存在於子查询select m2,n2 from t2的集合中

mysql> select * from t1 where m1 > any(select m2 from t2);
+------+------+
| m1   | n1   |
+------+------+
|    3 | c    |
|    4 | d    |
+------+------+
2 rows in set (0.00 sec)

只要子查询select m2 from t2集合中有纪录比t1.m1还要小,就是true,返回纪录。

mysql> select * from t1 where m1 > all(select m2 from t2);
Empty set (0.00 sec)

只要子查询select m2 from t2集合中所有的纪录都比t1.m1还要小,就是true,返回纪录。
这边可以看到并不符合,所以返回空集合。

mysql> select * from t1 where exists (select m2 from t2);
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.00 sec)

只要子查询select m2 from t2集合中有一笔纪录就是true,就返回所有t1纪录。


<<:  (Day23) ES6 箭头函式

>>:  企划实现(8)

LineBot 读取使用者档案格式

想请问各位, 如何读取 使用者传送的文件档案格式? 目前想要读取後 再存放到电脑指定资料夹中, 存放...

【Day01】数据输入元件 - Button

参赛前言 第一次参赛是 2019铁人赛(连结),也是刚接触 React 不久,透过那次真的觉得收获良...

day22: declarative vs. imperative

今天我们谈到 declarative 声明式和 imperative 命令式, 他的概念比较像是以下...

【C++】使用CMD 测试资料

我们来看到如何用命令程序视窗将文字档的资料丢到程序吧~ 总共有三个步骤~ 第一是建构程序~ 第二是配...

Day 19 - Spring Boot & Cookie

Cookie 介绍 Cookie 指得是储存在Client (用户)端上的资料,是一种在服务器与浏览...