mysql的左连接应用

如果两个表存在外键连接，可根据外键对两个表进行左连接，这时候外键相对应的其他列如果有NULL值，代表这两个表可能存在不同步的现象，可删除相应的值使2个表同步。 操作实例：
mysql> desc branch;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| bid   | int(4)      | NO   | PRI |         |       |
| cid   | int(3)      | NO   |     |         |       |
| bdesc | text        | NO   |     |         |       |
| bloc  | varchar(25) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+ mysql> desc client;
+-------+--------+------+-----+---------+----------------+
| Field | Type   | Null | Key | Default | Extra          |
+-------+--------+------+-----+---------+----------------+
| cid   | int(3) | NO   | PRI | NULL    | auto_increment |
| cname | text   | NO   |     |         |                |
+-------+--------+------+-----+---------+----------------+
2 rows in set (0.01 sec) mysql> select client.cid,client.cname,branch.bid,branch.bdesc from client left join branch using (cid);
+-----+-------+------+---------------+
| cid | cname | bid  | bdesc         |
+-----+-------+------+---------------+
| 101 | WANG  | 1011 | cororate hq   |
| 101 | WANG  | 1012 | cDDororate hq |
| 101 | WANG  | 1013 | DDrate hq     |
| 102 | JV    | NULL | NULL          |
| 103 | dJV   | 1015 | DDratDDhq     |
| 103 | dJV   | 1016 | DDratDDhDq    |
| 104 | JAMES | 1014 | DDratDDhq     |
| 105 | TOMES | NULL | NULL          |
+-----+-------+------+---------------+
8 rows in set (0.00 sec) mysql> delete from client where cid in(102,105);
Query OK, 2 rows affected (0.00 sec) mysql> select client.cid,client.cname,branch.bid,branch.bdesc from client left join branch using (cid);
+-----+-------+------+---------------+
| cid | cname | bid  | bdesc         |
+-----+-------+------+---------------+
| 101 | WANG  | 1011 | cororate hq   |
| 101 | WANG  | 1012 | cDDororate hq |
| 101 | WANG  | 1013 | DDrate hq     |
| 103 | dJV   | 1015 | DDratDDhq     |
| 103 | dJV   | 1016 | DDratDDhDq    |
| 104 | JAMES | 1014 | DDratDDhq     |
+-----+-------+------+---------------+
6 rows in set (0.00 sec)

转载于:https://blog.51cto.com/dadloveu/197238

总结

以上是生活随笔为你收集整理的mysql的左连接应用的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。