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