MySQL連接用法示例

作者：佚名 2010-05-27 15:18:47

今天我們要和大家一起分享的是MySQL連接用法總結(jié)。即，對(duì)MySQL連接的概念、各種連接的具體應(yīng)用、數(shù)據(jù)庫增量同步實(shí)例的介紹。

以下的文章主要介紹的是MySQL連接用法總結(jié)，以及MySQL連接的概念，各種連接的具體使用方案，數(shù)據(jù)庫增量同步實(shí)例的介紹，如果你對(duì)這些相關(guān)的內(nèi)容心存好奇的話，你就可以對(duì)以下的文章進(jìn)行閱讀了。

1、MySQL連接簡介

MySQL支持的連接類型如下：

交叉連接、內(nèi)連接、外連接（左外MySQL連接和右外連接）、自連接、聯(lián)合

2、各種連接的使用方法

在演示各種MySQL連接的用法之前，我們先定義如下的數(shù)據(jù)庫表格，以后的演示就使用它們。

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.內(nèi)連接

3.外連接

外連接有什么特點(diǎn)？簡而言之，外連接作用在通過某個(gè)key相連接的兩張表上，它首先從A表中依次讀出每行數(shù)據(jù)，然后到與之相連接的B表，尋找具有相同key值的記錄。如果有匹配行，A和B的對(duì)應(yīng)記錄組成新結(jié)果行；如果沒有，A與一條各字段為NULL的B記錄組成新結(jié)果行。

到底從哪個(gè)表中選擇所有行，SQL標(biāo)準(zhǔn)定義了左外連接和右外連接。

左外連接：

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.自MySQL連接

5.聯(lián)合

UNION運(yùn)算符表示聯(lián)合，它用來把多個(gè)SELECT查詢的結(jié)果連接成一個(gè)單獨(dú)的結(jié)果集，但在MySQL連接時(shí)去除重復(fù)行。可以使用UNION連接盡可能多的SELECT查詢，但要謹(jǐn)記兩個(gè)基本條件。首先，每個(gè)SELECT查詢返回的字段個(gè)數(shù)必須相同。第二，每個(gè)SELECT查詢的字段類型必須依次相同。

我們舉個(gè)聯(lián)合例子：

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)

對(duì)UNION的每個(gè)SELECT添加ORDER BY子句是沒有意義的，如果要排序則必須將其施加到***的結(jié)果集上。比如我們要對(duì)上面的例子中的iUserID進(jìn)行排序，應(yīng)該使用如下的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)

以上的相關(guān)內(nèi)容就是對(duì)MySQL連接與各種連接的使用方法的介紹，望你能有所收獲。#p#

3、數(shù)據(jù)庫增量同步例子

假設(shè)我們還有一個(gè)db_src.t_users，它與db_tar.t_users結(jié)構(gòu)完全相同。現(xiàn)網(wǎng)的應(yīng)用策略，要求先操作db_src.t_users，確認(rèn)無誤后再同步到db_tar.t_users。所以兩邊數(shù)據(jù)只會(huì)存在三種情況，后面我們將分別介紹如何使用SQL連接在數(shù)據(jù)庫間增量同步數(shù)據(jù)。

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;

同步原理其實(shí)很簡單，核心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條件檢索數(shù)據(jù)，執(zhí)行SQL得到結(jié)果如下：   
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"這條記錄不在目標(biāo)數(shù)據(jù)表中，使用insert語句可以將其插入到目標(biāo)數(shù)據(jù)庫中，從而完成同步操作。

情況2：db_src.t_users存在某條記錄，db_tar.t_users中也存在。策略->如果兩者數(shù)據(jù)不同，則修改后者的記錄。

這里有個(gè)問題，就是如何判斷這兩條記錄是否不同。我們可以采用逐個(gè)字段比較的方法，但字段較多的時(shí)候就不好使用了。簡單點(diǎn)的方法是規(guī)定修改db_src.t_users時(shí)，必須同時(shí)將db_src.t_users.dtLastTime修改為當(dāng)前時(shí)間，以后就可以通過比較dtLastTime來判斷數(shù)據(jù)是否不同。

根據(jù)上述判斷方法，解決該問題的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;

直接執(zhí)行上述語句得到只是一組UPDATE的SQL，并不能直接修改db_tar.t_users，我們可以在shell命令行上執(zhí)行下面的這個(gè)命令完成同步功能：

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語句的情況和第二種情況相似，也不能直接執(zhí)行。所以需要采用同樣的方式使用SQL語句才能完成工作。可能已經(jīng)有人看出了，還有更為簡單的方法可以處理這種情況。對(duì)，確實(shí)存在！當(dāng)然可以使用下面更為簡潔的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);

以上的相關(guān)內(nèi)容就是對(duì)MySQL連接用法總結(jié)的介紹，望你能有所收獲。

【編輯推薦】

MySQL root密碼忘記的解決
MySQL導(dǎo)入導(dǎo)出.sql文件實(shí)踐演練
MySQL數(shù)據(jù)庫中的5種數(shù)據(jù)類型簡介
對(duì)MySQL DELETE語法的詳細(xì)解析
MySQL show的實(shí)際操作用法

責(zé)任編輯：佚名來源：互聯(lián)網(wǎng)

MySQL連接