MySQL之两张表关系查找例子

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