MySQL数据库之子查询

子查询

  • 语法

    • 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)`
上一篇:WisDom.Net 框架设计(八) 持久层


下一篇:Java解析Json字符串--数组或列表