teacher表
mysql> select * from teacher;
+-----+--------+-----+---------+----------+
| tid | tname | age | address | courseid |
+-----+--------+-----+---------+----------+
| 101 | 马云 | 50 | 杭州 | NULL |
| 102 | 赵本山 | 52 | 沈阳 | NULL |
| 103 | 刘强东 | 45 | 北京 | NULL |
+-----+--------+-----+---------+----------+
students表
mysql> select * from students;
+-----+--------+-----+---------+----------+
| sid | sname | age | address | courseid |
+-----+--------+-----+---------+----------+
| 1 | 小海子 | 23 | 北京 | 1003 |
| 2 | 小沈阳 | 45 | 沈阳 | 1003 |
| 3 | 刘阳 | 25 | 山东 | 1002 |
| 4 | 甘能 | 22 | 广东 | 1002 |
+-----+--------+-----+---------+----------+
找出teacher表中在students表中 address相同 的 tname
1、第一种做法(teacher表中的 address 在students表里面,即可完成本道题)
mysql> select t.tname
-> from teacher t
-> where t.address in(select address from students);
+--------+
| tname |
+--------+
| 刘强东 |
| 赵本山 |
+--------+
2、第二种做法(在students表中是否与teacher表中 存在address相等的情况)
①
mysql> select t.tname
-> from teacher t
-> where exists(select* from students s where t.address = s.address);
+--------+
| tname |
+--------+
| 赵本山 |
| 刘强东 |
+--------+
②
mysql> select t.tname
-> from teacher t ,students s
-> where t.address = s.address;
+--------+
| tname |
+--------+
| 刘强东 |
| 赵本山 |
+--------+
2018年1月18日00:38:29