九、MySQL基础系列笔记之SQL DQL简单子查询

作者: 温新

分类: 【MySql】

阅读: 1747

时间: 2020-10-11 07:43:03

子查询指一个查询语句嵌套在另一个查询语句内部的查询。使用子查询时,先计算子查询,然后把子查询的结果作为外层的一个查询过滤条件。

常用的子查询操作符有ANY(SOME)、ALL、IN、EXISTS。

any/some-查询

以any为例,如 id > any(1,2,4);只要id大于any中的任何一个值就显示,也就是说,只要id大于any中的最小值即可。可以使用in来代替。

<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">user</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">where</span> id > any (<span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> id <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> info);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----+-----------+------------------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| id | name      | email            |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----+-----------+------------------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span> | 王五      | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">124568</span><span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span>    |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">3</span> | 张三      | zhangsan<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@163.com</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">4</span> | 王美丽    | meili<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@163.com</span>    |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">5</span> | 你真帅    | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">7788641</span><span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span>   |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">6</span> | 刘一      | liuyi<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@163.com</span>    |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">7</span> | 陈二      | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">782372340</span><span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">8</span> | 吴九      | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">8193730</span>-<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span>  |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----+-----------+------------------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(255, 83, 112)">7</span> rows <span style="box-sizing: border-box;color: rgb(199, 146, 234)">in</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">set</span> (<span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.00</span> sec)</span>

all-查询

如,id > all(1,2,4);id必须大于all()中所数的数才显示,也就是说,只要id大于all中的最大值即可。

<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">user</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">where</span> id > <span style="box-sizing: border-box;color: rgb(199, 146, 234)">all</span>(<span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> id <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> info);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----+--------+------------------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| id | name   | email            |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----+--------+------------------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">7</span> | 陈二   | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">782372340</span><span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">8</span> | 吴九   | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">8193730</span>-<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span>  |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----+--------+------------------+</span>

in-查询

mysql> select * from user where id in (select id from info);

exists-查询

exists关键字后的参数是任何一个查询语句。查询有结果为1,无结果为0.

mysql> select name from user where exists(select * from user where id > 3);

我是小白,期待和优秀的你一起同行!

小白

2020年10月11日

请登录后再评论