你可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。
-
JOIN 按照功能大致分为如下三类:
INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。 -
exmaple_userinfo表
mysql> select * from exmaple_userinfo;
+-----------+------+------+--------------------+
| name | sex | age | address |
+-----------+------+------+--------------------+
| 小伙子 | 男 | 18 | NULL |
| 男神 | 男 | 18 | 深圳市南山区 |
| 男神1 | 男 | 18 | 深圳市南山区 |
| 男神2 | 男 | 18 | 深圳市南山区 |
+-----------+------+------+--------------------+
4 rows in set (0.00 sec)
- employee_tbl
mysql> select * from employee_tbl;
+----+--------+---------------------+--------+
| id | name | date | singin |
+----+--------+---------------------+--------+
| 1 | 小明 | 2016-04-22 15:25:33 | 1 |
| 2 | 小王 | 2016-04-20 15:25:47 | 3 |
| 3 | 小丽 | 2016-04-19 15:26:02 | 2 |
| 4 | 小王 | 2016-04-07 15:26:14 | 4 |
| 5 | 小明 | 2016-04-11 15:26:40 | 4 |
| 6 | 小明 | 2016-04-04 15:26:54 | 2 |
| 7 | 男神 | 2016-04-07 15:26:14 | 0 |
+----+--------+---------------------+--------+
7 rows in set (0.00 sec)
- INNER JOIN (内连接 交集)
mysql> select a.name,a.date,b.age from employee_tbl a inner join exmaple_userinfo b on a.name = b.name ;
+--------+---------------------+------+
| name | date | age |
+--------+---------------------+------+
| 男神 | 2016-04-07 15:26:14 | 18 |
+--------+---------------------+------+
1 row in set (0.00 sec)
- LEFT JOIN 左连接
mysql> select a.name,a.date,b.age from employee_tbl a left join exmaple_userinfo b on a.name = b.name ;
+--------+---------------------+------+
| name | date | age |
+--------+---------------------+------+
| 男神 | 2016-04-07 15:26:14 | 18 |
| 小明 | 2016-04-22 15:25:33 | NULL |
| 小王 | 2016-04-20 15:25:47 | NULL |
| 小丽 | 2016-04-19 15:26:02 | NULL |
| 小王 | 2016-04-07 15:26:14 | NULL |
| 小明 | 2016-04-11 15:26:40 | NULL |
| 小明 | 2016-04-04 15:26:54 | NULL |
+--------+---------------------+------+
7 rows in set (0.00 sec)
- RIGHT JOIN 右连接
mysql> select a.name,a.date,b.age from employee_tbl a right join exmaple_userinfo b on a.name = b.name ;
+--------+---------------------+------+
| name | date | age |
+--------+---------------------+------+
| 男神 | 2016-04-07 15:26:14 | 18 |
| NULL | NULL | 18 |
| NULL | NULL | 18 |
| NULL | NULL | 18 |
+--------+---------------------+------+
4 rows in set (0.00 sec)