子查询
-
语法
select * from 表1 where （子查询）
-
说明
- 外面的查询称为父查询
- 子查询为父查询提供查询条件
标量子查询
- 特点
- 子查询返回的值是一个
MariaDB [sel]> select * from resume where name=(select name from resume where id=3);
+----+-------+-----------+
| id | name | skill |
+----+-------+-----------+
| 3 | Jerry | php,mysql |
+----+-------+-----------+
# `1 row in set (0.010 sec)`
- 找到数学最高分
MariaDB [sel]> select * from grades where name=(select name from grades order by math desc limit 1);
+-------+---------+------+
| name | chinese | math |
+-------+---------+------+
| Sunny | 93 | 96 |
+-------+---------+------+
# `1 row in set (0.004 sec)`
MariaDB [sel]> select * from grades where name=(select name from grades where math=(select max(math) from grades));
+-------+---------+------+
| name | chinese | math |
+-------+---------+------+
| Sunny | 93 | 96 |
+-------+---------+------+
# `1 row in set (0.001 sec)`
列子查询
- 特点
- 子查询返回的结果是一列
- 如果子查询的结果返回多条记录，不能使用等于，用in或not in
MariaDB [sel]> select * from grades where name in (select name from grades where math>92);
+-------+---------+------+
| name | chinese | math |
+-------+---------+------+
| Sunny | 93 | 96 |
| Marry | 95 | 94 |
| Tommy | 98 | 94 |
+-------+---------+------+
# `3 rows in set (0.007 sec)`
MariaDB [sel]> select * from grades where name not in (select name from grades where math>92);
+-------+---------+------+
| name | chinese | math |
+-------+---------+------+
| Jerry | 97 | 91 |
+-------+---------+------+
# `1 row in set (0.008 sec)`
MariaDB [sel]> select * from grades where name in (select name from grades where math<92);
+-------+---------+------+
| name | chinese | math |
+-------+---------+------+
| Jerry | 97 | 91 |
+-------+---------+------+
# `1 row in set (0.000 sec)`
行子查询
- 特点
- 子查询返回的结果是多个字段组成
MariaDB [sel]> select * from grades where (sex,math) in (select sex,max(math) from grades group by sex);
+-------+------+---------+------+
| name | sex | chinese | math |
+-------+------+---------+------+
| Sunny | boy | 93 | 96 |
| Marry | girl | 95 | 94 |
+-------+------+---------+------+
# `2 rows in set (0.001 sec)`
表子查询
- 特点
- 将子查询的结果作为表
- from后面跟的是数据源，如果将子查询当成表来看， 必须给结果集取别名
MariaDB [sel]> select * from (select * from grades order by math desc) best group by sex;
+-------+------+---------+------+
| name | sex | chinese | math |
+-------+------+---------+------+
| Sunny | boy | 93 | 96 |
| Marry | girl | 95 | 94 |
+-------+------+---------+------+
# `2 rows in set (0.007 sec)`
存在子查询
- 特点
- 提高查询效率
MariaDB [sel]> select * from grades where exists (select * from grades where math>90);
+-------+------+---------+------+
| name | sex | chinese | math |
+-------+------+---------+------+
| Sunny | boy | 93 | 96 |
| Jerry | boy | 97 | 91 |
| Marry | girl | 95 | 94 |
| Tommy | boy | 98 | 94 |
+-------+------+---------+------+
# `4 rows in set (0.001 sec)`
MariaDB [sel]> select * from grades where not exists (select * from grades where math>90);
# `Empty set (0.001 sec)`