# 为什么需要子查询？

• 集合成员资格

某一元素是否是某一个集合的成员

• 集合之间的比较

某一个集合是否包含另一个集合

• 集合基数的测试

测试集合是否为空

测试集合是否存在重复元素

# 基本语法

• 语法中，表达式最简单的形式就是列名或者常数
• 语义：判断某一表达式的值是否在子查询的结果中

# 例1

+----------------+
| name           |
+----------------+
| tom            |
| david          |
| lily           |
| jony           |
+----------------+


mysql>select * from test where name='tom' or name ='david';
+----------------+
| name           |
+----------------+
| tom            |
| david          |
+----------------+


mysql>select * from test where name in ("tom","david");
+----------------+
| name           |
+----------------+
| tom            |
| david          |
+----------------+


# 例2

+----------------+------------------+
| name           | course           |
+----------------+------------------+
| tom            | 001              |
| tom            | 002              |
| david          | 001              |
+----------------+------------------+


select t1.name
from test as t1, test as t2
where t1.name = t2.name
and t1.course='001'
and t2.course='002'

+----------------+
| name           |
+----------------+
| tom            |
+----------------+


+----------------+------------------+----------------+------------------+
| name           | course           | name           | course           |
+----------------+------------------+----------------+------------------+
| tom            | 001              | tom            | 001              |
| tom            | 002              | tom            | 001              |
| david          | 001              | tom            | 001              |
| tom            | 001              | tom            | 002              |
| tom            | 002              | tom            | 002              |
| david          | 001              | tom            | 002              |
| tom            | 001              | david          | 001              |
| tom            | 002              | david          | 001              |
| david          | 001              | david          | 001              |
+----------------+------------------+----------------+------------------+


select name
from test
where course = '001'
and name in
(
select name
from test
where course = '002'
)


+----------------+
| name           |
+----------------+
| tom            |
+----------------+


posted @ 2019-02-27 22:32  Velscode  阅读(1571)  评论(0编辑  收藏