Mysql connection usage instance _ MySQL

This article mainly introduces the use of mysql connections between left and right, and analyzes the use of mysql connections between left and right in detail using a complete instance, which has some reference value, for more information about mysql connection, see the following example. Share it with you for your reference. The details are as follows:

Use an example to parse the left, right, and inner connections of mysql.

The code is as follows:

Create table user_id (id decimal (18 ));
Create table user_profile (id decimal (18), name varchar (255 ));
Insert into user_id values (1 );
Insert into user_id values (2 );
Insert into user_id values (3 );
Insert into user_id values (4 );
Insert into user_id values (5 );
Insert into user_id values (6 );
Insert into user_id values (1 );

Insert into user_profile values (1, "aa ");
Insert into user_profile values (2, "bb ");
Insert into user_profile values (3, "cc ");
Insert into user_profile values (4, "dd ");
Insert into user_profile values (5, "ee ");
Insert into user_profile values (5, "EE ");
Insert into user_profile values (8, 'zz ');

1. left join:

The code is as follows:

Mysql> select a. id, ifnull (B. name, 'n'/A') name from user_id A left join user_profile B on a. id = B. id;
Mysql> select a. id, ifnull (B. name, 'n'/A') name from user_id A left join user_profile B on a. id = B. id;
+ ------ +
| Id | name |
+ ------ +
| 1 | aa |
| 2 | bb |
| 3 | cc |
| 4 | dd |
| 5 | ee |
| 5 | EE |
| 6 | N/A |
| 1 | aa |
+ ------ +
8 rows in set (0.00 sec)

User_id is left, so it is the left join. In this case, all records in user_id are listed. There are three situations:

1. if the id of each record in user_id also exists in user_profile and there is only one record, a new record will be generated in the returned result. For example, 1, 2, 3, and 4.
2. if the id of each record in user_id also exists in user_profile and there are N records, N new records will be generated in the returned results. As shown in figure 5 above.
3. if the id of each record in user_id does not exist in user_profile, a new record is generated in the returned result, and all the records on the right are NULL. As shown in Figure 6 above.

Records that do not comply with the preceding three rules will not be listed.

For example, to query data that does not exist in a related table, use id Association to find records that do not exist in user_profile in the user_id table:

The code is as follows:

Select count (*) from user_id left join user_profile on user_id.id = user_profile.id where user_profile.id is null;

2. right join

User_profile is the right link. In this case, all records of user_profile are listed. There are three situations:

1. if the id of each record in user_profile also exists in user_id and there is only one record, a new record will be generated in the returned result. As shown in the above 2, 3, 4, 5.
2. if the id of each record in user_profile also exists in user_id and there are N records, N new records will be generated in the returned results. As shown in Figure 1 above.
3. if the id of each record in user_profile does not exist in user_id, a new record is generated in the returned result, and all the records on the left are NULL. As shown in figure 8 above.

Records that do not comply with the preceding three rules will not be listed.

III. internal connection

There is no NULL field in the data records connected in MySQL. It can be simply considered that the result of the inner link is the result obtained after the record with the NULL field is removed from the result of the left or right join. In addition, MySQL does not support full join.

The code is as follows:

Mysql> select * from user_id a inner join user_profile B on a. id = B. id;
+ ------ +
| Id | name |
+ ------ +
| 1 | 1 | aa |
| 1 | 1 | aa |
| 2 | 2 | bb |
| 3 | 3 | cc |
| 4 | 4 | dd |
| 5 | 5 | ee |
| 5 | 5 | EE |
+ ------ +
7 rows in set (0.00 sec)
Mysql> select * from user_id a, user_profile B where a. id = B. id;
+ ------ +
| Id | name |
+ ------ +
| 1 | 1 | aa |
| 1 | 1 | aa |
| 2 | 2 | bb |
| 3 | 3 | cc |
| 4 | 4 | dd |
| 5 | 5 | ee |
| 5 | 5 | EE |
+ ------ +
7 rows in set (0.00 sec)
Mysql> select * from user_id a join user_profile B on a. id = B. id;
+ ------ +
| Id | name |
+ ------ +
| 1 | 1 | aa |
| 1 | 1 | aa |
| 2 | 2 | bb |
| 3 | 3 | cc |
| 4 | 4 | dd |
| 5 | 5 | ee |
| 5 | 5 | EE |
+ ------ +
7 rows in set (0.00 sec)

I hope this article will help you with MySQL program design.