十、子查询

• 10.1 子查询基本语法
• 10.2 in 和 not in
• 10.3 exists 和 not exists
• 10.4 基础结束语

将一个查询的结果作为另一个查询的数据来源或判断条件

子查询结果返回超过1行用in，只有一行可以用=

一般情况下都用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)

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)

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)

多练习！！！！！！！！！！！！！！！！现在如果你熟悉之前的课程，那么实习就基本达标了，后面的内容属于比较难的内容，对于实习生来说不是特别重要。