mysql连接的使用方法_mysql连接用法总结

1 -- MySQL连接简介

MySQL支持的连接类型如下:

交叉连接、内连接、外连接(左外连接和右外连接)、自连接、联合

2 -- 各种连接的使用方法

在演示各种连接的用法之前,我们先定义如下的数据库表格,以后的演示就使用它们。

mysql> select * from t_users;

+---------+-----------+---------+---------------------+

| iUserID | sUserName | iStatus | dtLastTime |

+---------+-----------+---------+---------------------+

| 1 | baidu | 0 | 2010-06-27 15:04:03 |

| 2 | google | 0 | 2010-06-27 15:04:03 |

| 3 | yahoo | 0 | 2010-06-27 15:04:03 |

| 4 | tencent | 0 | 2010-06-27 15:04:03 |

+---------+-----------+---------+---------------------+

mysql> select * from t_groups;

+----------+------------+---------------------+

| iGroupID | sGroupName | dtLastTime |

+----------+------------+---------------------+

| 1 | spring | 2010-06-27 15:04:03 |

| 2 | summer | 2010-06-27 15:04:03 |

| 3 | autumn | 2010-06-27 15:04:03 |

| 4 | winter | 2010-06-27 15:04:03 |

+----------+------------+---------------------+

mysql> select * from t_users_groups;

+---------+----------+---------------------+

| iUserID | iGroupID | dtLastTime |

+---------+----------+---------------------+

| 1 | 1 | 2010-06-27 15:04:03 |

| 2 | 1 | 2010-06-27 15:04:03 |

| 4 | 3 | 2010-06-27 15:04:03 |

| 6 | 4 | 2010-06-27 15:04:03 |

+---------+----------+---------------------+

1.交叉连接

2.内连接

3.外连接

外连接有什么特点?简而言之,外连接作用在通过某个key相连接的两张表上,它首先从A表中依次读出每行数据,然后到与之相连接的B表,寻找具有相同key值的记录。如果有匹配行,A和B的对应记录组成新结果行;如果没有,A与一条各字段为NULL的B记录组成新结果行。

到底从哪个表中选择所有行,SQL标准定义了左外连接和右外连接。

左外连接:

mysql> SELECT * FROM t_users LEFT JOIN t_users_groups ON t_users.iUserID=t_users_groups.iUserID;

+---------+-----------+---------+---------------------+---------+----------+---------------------+

| iUserID | sUserName | iStatus | dtLastTime | iUserID | iGroupID | dtLastTime |

+---------+-----------+---------+---------------------+---------+----------+---------------------+

| 1 | baidu | 0 | 2010-06-27 15:04:03 | 1 | 1 | 2010-06-27 15:04:03 |

| 2 | google | 1 | 2010-06-27 15:46:51 | 2 | 1 | 2010-06-27 15:04:03 |

| 3 | yahoo | 1 | 2010-06-27 15:46:51 | NULL | NULL | NULL |

| 4 | tencent | 0 | 2010-06-27 15:04:03 | 4 | 3 | 2010-06-27 15:04:03 |

+---------+-----------+---------+---------------------+---------+----------+---------------------+

4 rows in set (0.00 sec)

t_users为上述描述中的A表,t_users_groups为B表。

右外连接:

mysql> SELECT * FROM t_users RIGHT JOIN t_users_groups ON t_users.iUserID=t_users_groups.iUserID;

+---------+-----------+---------+---------------------+---------+----------+---------------------+

| iUserID | sUserName | iStatus | dtLastTime | iUserID | iGroupID | dtLastTime |

+---------+-----------+---------+---------------------+---------+----------+---------------------+

| 1 | baidu | 0 | 2010-06-27 15:04:03 | 1 | 1 | 2010-06-27 15:04:03 |

| 2 | google | 1 | 2010-06-27 15:46:51 | 2 | 1 | 2010-06-27 15:04:03 |

| 4 | tencent | 0 | 2010-06-27 15:04:03 | 4 | 3 | 2010-06-27 15:04:03 |

| NULL | NULL | NULL | NULL | 6 | 4 | 2010-06-27 15:04:03 |

+---------+-----------+---------+---------------------+---------+----------+---------------------+

4 rows in set (0.00 sec)

t_users_groups为上述描述中的A表,t_users为B表。

4.自连接

5.联合

UNION运算符表示联合,它用来把多个SELECT查询的结果连接成一个单独的结果集,但在连接时去除重复行。可以使用UNION连接尽可能多的SELECT查询,但要谨记两个基本条件。首先,每个SELECT查询返回的字段个数必须相同。第二,每个SELECT查询的字段类型必须依次相同。

我们举个联合例子:

mysql> SELECT iUserID,sUserName,dtLastTime FROM t_users

-> UNION

-> SELECT iGroupID,sGroupName,dtLastTime FROM t_groups;

+---------+-----------+---------------------+

| iUserID | sUserName | dtLastTime |

+---------+-----------+---------------------+

| 1 | baidu | 2010-06-27 15:04:03 |

| 2 | google | 2010-06-27 15:46:51 |

| 3 | yahoo | 2010-06-27 15:46:51 |

| 4 | tencent | 2010-06-27 15:04:03 |

| 1 | spring | 2010-06-27 15:04:03 |

| 2 | summer | 2010-06-27 15:04:03 |

| 3 | autumn | 2010-06-27 15:04:03 |

| 4 | winter | 2010-06-27 15:04:03 |

+---------+-----------+---------------------+

8 rows in set (0.01 sec)

对UNION的每个SELECT添加ORDER BY子句是没有意义的,如果要排序则必须将其施加到最后的结果集上。比如我们要对上面的例子中的iUserID进行排序,应该使用如下的SQL语句:

mysql> (SELECT iUserID,sUserName,dtLastTime FROM t_users)

-> UNION

-> (SELECT iGroupID,sGroupName,dtLastTime FROM t_groups)

-> ORDER BY iUserID ASC;

+---------+-----------+---------------------+

| iUserID | sUserName | dtLastTime |

+---------+-----------+---------------------+

| 1 | baidu | 2010-06-27 15:04:03 |

| 1 | spring | 2010-06-27 15:04:03 |

| 2 | google | 2010-06-27 15:46:51 |

| 2 | summer | 2010-06-27 15:04:03 |

| 3 | yahoo | 2010-06-27 15:46:51 |

| 3 | autumn | 2010-06-27 15:04:03 |

| 4 | tencent | 2010-06-27 15:04:03 |

| 4 | winter | 2010-06-27 15:04:03 |

+---------+-----------+---------------------+

8 rows in set (0.02 sec)

3 -- 数据库增量同步例子

假设我们还有一个db_src.t_users,它与db_tar.t_users结构完全相同。现网的应用策略,要求先操作db_src.t_users,确认无误后再同步到db_tar.t_users。所以两边数据只会存在三种情况,后面我们将分别介绍如何使用SQL连接在数据库间增量同步数据。

mysql> select * from t_users;

+---------+-----------+---------+---------------------+

| iUserID | sUserName | iStatus | dtLastTime |

+---------+-----------+---------+---------------------+

| 1 | baidu | 0 | 2010-06-27 15:04:03 |

| 2 | google | 1 | 2010-06-27 15:46:51 |

| 3 | yahoo | 1 | 2010-06-27 15:46:51 |

| 4 | tencent | 0 | 2010-06-27 15:04:03 |

| 5 | netease | 0 | 2010-06-27 15:04:03 |

+---------+-----------+---------+---------------------+

5 rows in set (0.01 sec)

情况1:db_src.t_users存在某条记录,db_tar.t_users中不存在。策略->将前者中的记录插入到后者中。

解决该问题的SQL语句为:

INSERT INTO db_tar.t_users select db_src.t_users.*

FROM db_src.t_users LEFT JOIN db_tar.t_users ON db_src.t_users.iUserID=db_tar.t_users.iUserID

WHERE db_tar.t_users.iUserID IS NULL;

同步原理其实很简单,核心SQL语句就是SELECT db_src.t_users.iUserID, db_src.t_users.sUserName, db_tar.t_users.* FROM db_src.t_users LEFT JOIN db_tar.t_users ON db_src.t_users.iUserID=db_tar.t_users.iUserID WHERE db_tar.t_users.iUserID IS NULL。如果不使用where条件检索数据,执行SQL得到结果如下:

mysql> SELECT db_src.t_users.iUserID, db_src.t_users.sUserName, db_tar.t_users.*

-> FROM db_src.t_users

-> LEFT JOIN db_tar.t_users ON db_src.t_users.iUserID=db_tar.t_users.iUserID;

+---------+-----------+---------+-----------+---------+---------------------+

| iUserID | sUserName | iUserID | sUserName | iStatus | dtLastTime |

+---------+-----------+---------+-----------+---------+---------------------+

| 1 | baidu | 1 | baidu | 0 | 2010-06-27 15:04:03 |

| 2 | google | 2 | google | 1 | 2010-06-27 15:46:51 |

| 3 | yahoo | 3 | yahoo | 1 | 2010-06-27 15:46:51 |

| 4 | tencent | 4 | tencent | 0 | 2010-06-27 15:04:03 |

| 5 | netease | NULL | NULL | NULL | NULL |

+---------+-----------+---------+-----------+---------+---------------------+

5 rows in set (0.00 sec)

加入where语句后,我们就能得到"iUserID=5"这条记录不在目标数据表中,使用insert语句可以将其插入到目标数据库中,从而完成同步操作。

情况2:db_src.t_users存在某条记录,db_tar.t_users中也存在。策略->如果两者数据不同,则修改后者的记录。

这里有个问题,就是如何判断这两条记录是否不同。我们可以采用逐个字段比较的方法,但字段较多的时候就不好使用了。简单点的方法是规定修改db_src.t_users时,必须同时将db_src.t_users.dtLastTime修改为当前时间,以后就可以通过比较dtLastTime来判断数据是否不同。

根据上述判断方法,解决该问题的SQL语句为:

select CONCAT('UPDATE db_src.t_users SET ',

'iUserID=\'', db_src.t_users.iUserID, '\',',

'sUserName=\'', db_src.t_users.sUserName, '\',',

'iStatus=\'', db_src.t_users.iStatus, '\',',

'dtLastTime=\'', db_src.t_users.dtLastTime, '\' ',

'WHERE db_tar.t_users.iUserID=\'', db_src.t_users.iUserID, '\';')

from db_src.t_users left join db_tar.t_users on db_src.t_users.iUserID = db_tar.t_users.iUserID

where db_tar.t_users.iUserID is not null and

db_src.t_users.dtLastTime > db_tar.t_users.dtLastTime;

直接执行上述语句得到只是一组UPDATE的SQL,并不能直接修改db_tar.t_users,我们可以在shell命令行上执行下面的这个命令完成同步功能:

mysql -Nse select CONCAT('UPDATE db_src.t_users SET ',

'iUserID=\'', db_src.t_users.iUserID, '\',',

'sUserName=\'', db_src.t_users.sUserName, '\',',

'iStatus=\'', db_src.t_users.iStatus, '\',',

'dtLastTime=\'', db_src.t_users.dtLastTime, '\' ',

'WHERE db_tar.t_users.iUserID=\'', db_src.t_users.iUserID, '\';')

from db_src.t_users left join db_tar.t_users on db_src.t_users.iUserID = db_tar.t_users.iUserID

where db_tar.t_users.iUserID is not null and

db_src.t_users.dtLastTime > db_tar.t_users.dtLastTime; | mysql;

情况3:db_src.t_users不存在某条记录,但在db_tar.t_users中存在。策略->将该记录从后者中删除。

解决该问题的SQL语句为:

SELECT CONCAT('DELETE FROM db_tar.t_users WHERE db_tar.t_users.iUserID=',

db_tar.t_users.iUserID, ';')

FROM db_tar.t_users LEFT JOIN db_src.t_users ON db_src.t_users.iUserID=db_tar.t_users.iUserID

WHERE db_src.t_users.iUserID IS NULL;

该SQL语句的情况和第二种情况相似,也不能直接执行。所以需要采用同样的方式使用SQL语句才能完成工作。可能已经有人看出了,还有更为简单的方法可以处理这种情况。对,确实存在!当然可以使用下面更为简洁的SQL语句:

DELETE FROM db_tar.t_users

WHERE db_tar.t_users.UserID NOT IN (SELECT db_src.t_users.iUserID FROM db_src.t_users);

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值