10:子查询-MySQL
【摘要】 10.1 子查询基本语法将一个查询的结果作为另一个查询的数据来源或判断条件一般情况下子查询结果返回超过1行用in,只有一行可以用=select * from stu where stuId in (select stuId from eatery where money > 800);select * from stu where stuId = (select stuId from e...
10.1 子查询基本语法
将一个查询的结果作为另一个查询的数据来源或判断条件
一般情况下子查询结果返回超过1行用in
,只有一行可以用=
select * from stu where stuId in (select stuId from eatery where money > 800);
select * from stu where stuId = (select stuId from eatery where money > 1000);
mysql> select * from eatery;
+----+----------+-------+
| id | money | stuId |
+----+----------+-------+
| 0 | 999.0000 | 5 |
| 1 | 20.5000 | NULL |
| 2 | 78.6000 | 4 |
| 3 | 99.9000 | NULL |
| 4 | 748.4000 | 4 |
| 5 | 748.4000 | NULL |
| 6 | 999.0000 | 5 |
| 7 | 345.0000 | 4 |
+----+----------+-------+
8 rows in set (0.00 sec)
mysql> select * from stu;
+-------+-------+
| stuId | name |
+-------+-------+
| 4 | frank |
| 5 | Tom |
+-------+-------+
2 rows in set (0.00 sec)
mysql> select stuId from eatery where money > 800;
+-------+
| stuId |
+-------+
| 5 |
| 5 |
+-------+
2 rows in set (0.00 sec)
mysql> select * from stu where stuId in (select stuId from eatery where money > 800);
+-------+------+
| stuId | name |
+-------+------+
| 5 | Tom |
+-------+------+
1 row in set (0.00 sec)
10.2 in 和 not in
mysql> select * from stu where stuId not in (select stuId from eatery where money > 800);
+-------+-------+
| stuId | name |
+-------+-------+
| 4 | frank |
+-------+-------+
1 row in set (0.00 sec)
10.3 exists 和 not exists
exists
是一个存在的条件,只要子查询存在,就把主查询所有的列出来
mysql> select stuId from eatery where money > 900;
+-------+
| stuId |
+-------+
| 5 |
| 5 |
+-------+
2 rows in set (0.00 sec)
mysql> select * from stu where exists (select stuId from eatery where money > 900);
+-------+-------+
| stuId | name |
+-------+-------+
| 4 | frank |
| 5 | Tom |
+-------+-------+
2 rows in set (0.00 sec)
10.4 基础结束语
多练习,现在如果你熟悉之前的课程,那么实习就基本达标了,后面的内容属于比较难的内容,对于实习生来说不是特别重要
推荐
![](https://res.hc-cdn.com/ecology/9.3.130/v2_resources\images\global\blog-dialog.png)
华为开发者空间发布
让每位开发者拥有一台云主机
【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱:
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论(0)