Shulou(Shulou.com)06/01 Report--

The following content mainly brings the author's MySql learning trip, the knowledge mentioned here, which is slightly different from books, is summed up by professional and technical personnel in the process of contact with users, and has a certain value of experience sharing. I hope to bring help to the majority of readers.

Premise: create two relational tables:

CREATE TABLE t_blog (id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR (50), typeId INT); CREATE TABLE t_type (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR (20))

The table data is as follows:

1. Get the An and B public parts

MySql provides an inner join way to get data that conforms to on expressions in both An and B tables:

Mysql > select * from t_blog b inner join t_type t on b.typeId = t.id +-id | title | typeId | id | name | +-java basic type | | 1 | 1 | JAVA | | 2 | java programming idea | 1 | 1 | JAVA | | 3 | java from getting started to abandoning | 1 | 1 | JAVA | | 4 | intensive C language | 2 | 2 | C | 5 | C language from getting started to giving up | 2 | 2 | C | 6 | mysql basis | 4 | 4 | MYSQL | + -. | -+ 6 rows in set

Through internal joins, the "graphical http" data in blog and the "C++" data in the type table are filtered.

2. The uniqueness of An and the sharing of AB

Use the left outer concatenation to obtain all the data of A, filter out the data in table B that conform to the on expression, and supplement the corresponding fields of table B with NULL if they do not match.

Mysql > select * from t_blog b left join t_type t on b.typeId = t.id +-+ | id | title | typeId | id | name | +-+ | 1 | java base Basic type | 1 | 1 | JAVA | | 2 | java programming ideas | 1 | 1 | JAVA | | 3 | java from getting started to abandoning | 1 | 1 | JAVA | | 4 | intensive C language | 2 | 2 | C | 5 | C language from getting started to giving up | 2 | 2 | C | 6 | mysql Foundation | 4 | 4 | MYSQL | | 7 | graphic http | NULL | +-+ 7 rows in set

Through the left outer join, all the data in the left table is queried, the "C++" data unique to the right table is filtered, and the right table position of the "graphical http" data unique to the left table is supplemented with NULL.

3. B exclusive and AB co-owned

As above, use the right outer join to filter out the data in table A that does not meet the on conditions, and query all the data in table B. the fields that do not meet the requirements in table A will supplement the NULL.

Mysql > select * from t_blog b right join t_type t on b.typeId = t.id +-+-+ | id | title | typeId | id | name | +-+-+ | 1 | java Basic type | 1 | 1 | JAVA | | 2 | java programming ideas | 1 | 1 | JAVA | | 3 | java from getting started to abandoning | 1 | 1 | JAVA | | 4 | intensive C language | 2 | 2 | C | 5 | C language from getting started to giving up | 2 | 2 | C | | NULL | 3 | C++ | 6 | mysql Foundation | 4 | 4 | MYSQL | +-+ 7 rows in set

Through the right outer join, all the data in the right table is found, and the unique "C++" data is supplemented with NULL in the left table to filter out the "graphical http" data in the left table.

4. Unique to A.

It is known that through the left outer join, you will get the uniqueness of An and the common AB. In the unique part of A, the position of table B will complement NULL, that is to say, the data in which B is NULL in the result of left outer join is unique to A.

Mysql > select * from t_blog b left join t_type t on b.typeId = t.id where t.name is null +-+ | id | title | typeId | id | name | +-+ | 7 | schematic http | NULL | +-+-+ -+ 1 row in set

5. B unique

As above, the right outer join will obtain the unique and AB share of B, and in the unique part of B, the position of table A will complement NULL, that is to say, the data in table An of the left outer join result is unique to B.

Mysql > select * from t_blog b right join t_type t on b.typeId = t.id where b.title is null +-+-+ | id | title | typeId | id | name | +-+-+ | NULL | 3 | C++ | +- -+-+ 1 row in set

6. Union set

The "union" command is provided in MySql for union and automatic de-duplication. If the opposite table does not match, make up the NULL

Mysql > select * from t_blog b left join t_type t on b.typeId = t.id-> union-> select * from t_blog b right join t_type t on b.typeId = t.id +-+ | id | title | typeId | id | name | +- + | 1 | java basic type | 1 | 1 | JAVA | | 2 | java programming ideas | 1 | 1 | JAVA | | 3 | java from getting started to abandoning | 1 | 1 | JAVA | | 4 | intensive C language | 2 | 2 | C | | 5 | C language from getting started to giving up | 2 | 2 | C | 6 | mysql Basics | | | 4 | 4 | MYSQL | | 7 | graphic http | NULL | | NULL | 3 | C++ | +-+ 8 rows in set |

7. Difference set

The so-called difference set is the unique union of An and B.

Mysql > select * from t_blog b left join t_type t on b.typeId = t.id where t.name is null-> union-> select * from t_blog b right join t_type t on b.typeId = t.id where b.title is null +-+ | id | title | typeId | id | name | +-+ | 7 | graphic http | NULL | | NULL | NULL | NULL | 3 | C++ | +-+ 2 rows in set

For the above about the author's MySql learning trip, if you need to know more, you can continue to pay attention to the innovation of our industry, if you need to get professional answers, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.