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