MySQL多表查询

有些时候我们需要在多个表中查询数据，并满足一定的约束条件，得到多个表中字段的组合，这时候需要用到多表查询。

比如，我们有两个表

mysql> show tables;
+-------------------+
| Tables_in_mybatis |
+-------------------+
| it_dwh            |
| it_mon            |
+-------------------+
2 rows in set (0.00 sec)

两个表中的记录分别为：

mysql> select * from it_mon;  ;
+----+--------+------------+
| ID | NAME   | JOB        |
+----+--------+------------+
|  1 | RYAN   | teamleader |
|  2 | jensen | mon        |
|  3 | tracy  | mon        |
|  4 | jade   | mon        |
+----+--------+------------+
4 rows in set (0.00 sec)

mysql> select * from it_dwh;
+----+--------+-------+
| ID | NAME   | SCORE |
+----+--------+-------+
|  1 | jd     |   100 |
|  2 | RYAN   |    99 |
|  3 | jensen |    98 |
|  4 | ali    |   100 |
+----+--------+-------+
4 rows in set (0.00 sec)

此时我们想查出两个表中都有的职工的job和score字段：

mysql> SELECT it_mon.NAME,JOB,SCORE FROM it_mon,it_dwh where it_mon.NAME = it_dwh.NAME;
+--------+------------+-------+
| NAME   | JOB        | SCORE |
+--------+------------+-------+
| RYAN   | teamleader |    99 |
| jensen | mon        |    98 |
+--------+------------+-------+
2 rows in set (0.00 sec)