MySQL [05] connection usage Summary

1 -- MySQL connection Overview

MySQL supports the following connection types:
Cross-connection, internal connection, external connection (left outer connection and right outer connection), Self-connection, and Union

2 -- usage of various connections

Before demonstrating the usage of various connections, we should first define the following database tables, and then use them in subsequent demonstrations.

 
Mysql> select * From t_users; + --------- + ----------- + --------- + region + | iuserid | susername | istatus | dtlasttime | + --------- + ----------- + --------- + region + | 1 | Baidu | 0 | 15:04:03 | 2 | google | 0 | 15:04:03 | 3 | Yahoo | 0 | 15:04:03 | 4 | Tencent | 0 | 15:04:03 | + ----------- + --------- + ----------------------- +
 

 
Mysql> select * From t_groups; + ---------- + ------------ + signature + | igroupid | sgroupname | dtlasttime | + ---------- + ------------ + --------------------- + | 1 | spring | 15:04:03 | 2 | summer | 15:04:03 | 3 | autumn | 15:04:03 | 4 | winter | 15:04:03 | + ---------- + ------------ + --------------------------- +
 

Mysql> select * From t_users_groups; + --------- + ---------- + ------------------- + | iuserid | igroupid | dtlasttime | + --------- + ---------- + hour + | 1 | 1 | 15:04:03 | 2 | 1 | 15:04:03 | 4 | 3 | 15:04:03 | 6 | 4 | 15:04:03 | + --------- + ---------- + ------------------------- +
 

1. Cross join

2. Internal Connection

3. External Connection
What are the characteristics of external connections? In short, the external join function is used in two tables connected by a key. It first reads each row of data from table A and then to table B connected to it, search for records with the same key value. If there are matched rows, the corresponding records of A and B form a new result row. If not, a and a B record with null fields form a new result row.
Select all rows from which table. SQL standard defines the left Outer Join and right outer join.

Left Outer Join: mysql> select * From t_users left join t_users_groups on t_users.iuserid = t_users_groups.iuserid; + --------- + ----------- + --------- + certificate + --------- + ---------- + certificate + | iuserid | susername | istatus | region | iuserid | igroupid | region | + --------- + ------- + certificate + --------- + ---------- + ------------------- + | 1 | Baidu | 0 | 15:04:03 | 1 | 1 | 15:04:03 | 2 | Google | 1 | 15:46:51 | 2 | 15:04:03 | 3 | Yahoo | 1 | 15:46:51 | null | 4 | Tencent | 0 | 15:04:03 | 4 | 3 | 15:04:03 | + ----------- + --------- + ------------- + --------------------- + --------- + ---------- + --------------------- + 4 rows in SET (0.00 Sec)
 

T_users is table A in the preceding description, and t_users_groups is table B.

Right outer join: mysql> select * From t_users right join t_users_groups on t_users.iuserid = t_users_groups.iuserid; + --------- + ----------- + --------- + certificate + --------- + ---------- + certificate + | iuserid | susername | istatus | region | iuserid | igroupid | region | + --------- + ------- + certificate + --------- + ---------- + ------------------- + | 1 | Baidu | 0 | 15:04:03 | 1 | 1 | 15:04:03 | 2 | Google | 1 | 15:46:51 | 2 | 15:04:03 | 4 | Tencent | 0 | 15:04:03 | 4 | 3 | 15:04:03 | null | 6 | 4 | 15:04:03 | + ----------- + --------- + ----------- + --------------------- + --------- + ---------- + --------------------- + 4 rows in SET (0.00 Sec)
 

T_users_groups is table A in the preceding description, and t_users is table B.

4. Self-connection

5. Join
The Union operator indicates union. It is used to concatenate the results of multiple select queries into a separate result set, but duplicate rows are removed during the connection. You can use Union to join as many select queries as possible, but remember the two basic conditions. First, each SELECT query must return the same number of fields. Second, the Field Types of each SELECT query must be the same in sequence.
Let's take a joint example:

 
Mysql> select iuserid, susername, dtlasttime from t_users-> Union-> select igroupid, sgroupname, dtlasttime from t_groups; + --------- + ----------- + region + | iuserid | susername | dtlasttime | + --------- + ----------- + region + | 1 | Baidu | 15:04:03 | 2 | Google | 15:46:51 | 3 | yahoo | 15:46:51 | 4 | Tencent | 15:04:03 | 1 | spring | 15:04:03 | 2 | summer | 15:04:03 | 3 | autumn | 15:04:03 | 4 | winter | 15:04:03 | + --------- + ----------- + ------------------------- + 8 rows in SET (0.01 Sec)
 

Adding the order by clause to each select statement of union is meaningless. to sort the clause, it must be applied to the final result set. For example, to sort the iuserid in the preceding example, use the following SQL statement:

Mysql> (select iuserid, susername, dtlasttime from t_users)-> Union-> (select igroupid, sgroupname, dtlasttime from t_groups)-> order by iuserid ASC; + --------- + ----------- + region + | iuserid | susername | dtlasttime | + --------- + ----------- + region + | 1 | Baidu | 15:04:03 | 1 | spring | 15:04:03 | 2 | google | 15:46:51 | 2 | summer | 15:04:03 | 3 | Yahoo | 15:46:51 | 3 | autumn | 15:04:03 | 4 | Tencent | 15:04:03 | 4 | winter | 15:04:03 | + --------- + ----------- + ------------------------- + 8 rows in SET (0.02 Sec)
 

3 -- database incremental synchronization example

Suppose we have another db_src.t_users, which has the same structure as db_tar.t_users. For the current network application policy, you must first operate db_src.t_users, and then synchronize it to db_tar.t_users. Therefore, there are only three cases of data on both sides. We will introduce how to use SQL to connect to incremental data synchronization between databases.

Mysql> select * From t_users; + --------- + ----------- + --------- + region + | iuserid | susername | istatus | dtlasttime | + --------- + ----------- + --------- + region + | 1 | Baidu | 0 | 15:04:03 | 2 | google | 1 | 15:46:51 | 3 | Yahoo | 1 | 15:46:51 | 4 | Tencent | 0 | 15:04:03 | 5 | Netease | 0 | 15:04:03 | + --------- + ----------- + --------- + ------------------- + 5 rows in SET (0.01 Sec)
 

Case 1: db_src.t_users has a record, which does not exist in db_tar.t_users. Policy-> insert records in the former to the latter.

The SQL statement to solve the problem is:

 
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 = mongodb_tar.t_users.iuserid is null;
 

the synchronization principle is actually very simple. The core SQL statement is 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 condition = where conditions. If you do not use the where condition to retrieve data, run the SQL statement to obtain the following result:

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; + --------- + certificate + --------- + ----------- + --------- + certificate + | iuserid | susername | istatus | certificate | + --------- + ----------- + --------- + certificate + | 1 | Baidu | 1 | Baidu | 0 | 15:04:03 | 2 | Google | 2 | Google | 1 | 15:46:51 | 3 | Yahoo | 3 | Yahoo | 1 | 15:46:51 | 4 | Tencent | 4 | Tencent | 0 | 2010-06-27 15:04:03 | 5 | Netease | null | + --------- + ----------- + --------------------- + 5 rows in SET (0.00 Sec)
 

After adding the where statement, we can get that the record "iuserid = 5" is not in the target data table. You can use the insert statement to insert it into the target database to complete the synchronization operation.

Case 2: db_src.t_users has a record, and db_tar.t_users also exists. Policy-> if the data is different, modify the record of the latter.

The question is how to determine whether the two records are different. We can compare fields one by one, but it is not easy to use when there are many fields. The simple method is to set db_src.t_users to change db_src.t_users.dtlasttime to the current time at the same time, and then compare dtlasttime to determine whether the data is different.

Based on the above judgment method, the SQL statement to solve the problem is:

 
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 = \', region, '\'', 'where db_tar.t_users.iuserid = \', region, '\'; ') from db_src.t_users left join db_tar.t_users on db_src.t_users.iuserid = db_tar.t_users.iuseridwhere db_tar.t_users.iuserid is not null >db_tar.t_users.dtlasttime;
 

Directly execute the preceding statement to obtain a set of update SQL statements, and do not directly modify db_tar.t_users. We can execute the following command on the shell command line to complete the synchronization function:

Mysql-neuron 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 = \'', region, '\ '', '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 = mongodb_tar.t_users.iuserid is not null >db_tar.t_users.dtlasttime; | MySQL;
 

Case 3: db_src.t_users does not have a record, but it exists in db_tar.t_users. Policy-> Delete the record from the latter.
The SQL statement to solve the problem is:

 
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 users = Invalid users is null;
 

The SQL statement is similar to the second statement and cannot be executed directly. Therefore, you must use SQL statements in the same way to complete the work. Some people may have seen that there is a simpler way to deal with this situation. Yes, it does! Of course, you can use the following more concise SQL statement:

 
Delete from db_tar.t_userswhere db_tar.t_users.userid not in (select db_src.t_users.iuserid from db_src.t_users );