MySQL connection usage example (1)

The following articles mainly introduce the usage of MySQL connections, the concept of MySQL connections, the specific usage of various connections, and the introduction of database incremental synchronization instances, if you are curious about the content, you can read the following articles.

1. MySQL connection Overview

MySQL supports the following connection types:

Cross-connection, internal connection, external connection left MySQL connection and external connection), Self-connection, and Union

2. Usage of various connections

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

 
 

  
  
Mysql> select * from t_users;
  
  
+ --------- + ----------- + --------- + --------------------- +
  
  
| IUserID | sUserName | iStatus | dtLastTime |
  
  
+ --------- + ----------- + --------- + --------------------- +
  
  
| 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;
  
  
+ ---------- + ------------ + --------------------- +
  
  
| 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 |
  
  
+ --------- + ---------- + --------------------- +
  
  
| 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;  
  
  
+---------+-----------+---------+---------------------+---------+----------+---------------------+  
  
  
| 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 is table A in the preceding description, and t_users_groups is table B.

Outer right connection:

 
 

  
  
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 is table A in the preceding description, and t_users is table B.

4. Self-MySQL 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 when MySQL is connected. 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;  
  
  
+---------+-----------+---------------------+  
  
  
| 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)

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;  
  
  
+---------+-----------+---------------------+  
  
  
| 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) 
 
 

The above content is an introduction to the usage of MySQL connections and various connections. I hope you will gain some benefits.