九、MySQL基础系列笔记之SQL DQL简单子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询。使用子查询时,先计算子查询,然后把子查询的结果作为外层的一个查询过滤条件。
常用的子查询操作符有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日
请登录后再评论