目录
- 10.1 子查询基本语法
- 10.2 in 和 not in
- 10.3 exists 和 not exists
- 10.4 基础结束语
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 基础结束语
多练习，现在如果你熟悉之前的课程，那么实习就基本达标了，后面的内容属于比较难的内容，对于实习生来说不是特别重要