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