举一些子查询的例子方便大家容易理解
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後面的子查询又称为衍生表。
子查询的类型可以分为几种:
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)
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的列,所以这是个相关子查询(依赖外层查询的列)。
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纪录。
想请问各位, 如何读取 使用者传送的文件档案格式? 目前想要读取後 再存放到电脑指定资料夹中, 存放...
参赛前言 第一次参赛是 2019铁人赛(连结),也是刚接触 React 不久,透过那次真的觉得收获良...
今天我们谈到 declarative 声明式和 imperative 命令式, 他的概念比较像是以下...
我们来看到如何用命令程序视窗将文字档的资料丢到程序吧~ 总共有三个步骤~ 第一是建构程序~ 第二是配...
Cookie 介绍 Cookie 指得是储存在Client (用户)端上的资料,是一种在服务器与浏览...