Small shell _mysql Select Connection Query

Select Connection Query Brief:
Union Union Query
Second, about the internal connection

Union Union Query

Function: Merge 2 or more query results together

Details: (table 1 Query results) Union (table 2 query results)

Execution: The results of the table 1 query, and then the table 2 query results. The result is then joined together by union.

Summarize:

a , left and right query result column number consistent

b , the final display results are based on the column name of the first table

C , the column type of the left and right query results is best consistent, otherwise it will be converted. from low to high. As shown in table 1 , result A is shaped, table 2 results A is floating point type. The result of table 1 in column A is converted to a floating-point type

D , can be a different table

e , if the rows taken in different statements are identical ( the values for each column are the same), then the same rows will be merged (DE-weighed)

Case:

Query for such results:

A16,b 21,c 14,d 41

Step 1:union to get query results for two tables

Step 2: Consider the query result of step 1 as a temporary table

Deficiencies: There is no identical column in table TA and TB in the above case (that is, the values of all columns in the query result are the same)

Modify the next TB table

At this point, execute the SQL statement for step 2

The sum of the values for ID A is found to be 5 instead of 10.

This is because if the rows taken in different statements are identical (the values for each column are the same), then the same rows will be merged (DE-weighed)

Treatment scenario: Use UNION ALL

Second, connection query

There is usually no relationship between a table and a table. But we can use a field to make the table relate to the table.

Consider a table as a collection.

2.1, LEFT JOIN Connect

Syntax: Select column 1, column 2, column n from TableA left join TableB on TableA. Column =tableb. Column

2.2. Right Connection

Syntax: Select column 1, column 2, column n from TableA right join TableB on TableA. Column =tableb. Column

2.3. Internal connection

Syntax: Select column 1, column 2, column n from TableA inner join TableB on tableA. Column =tableb. Column

2.4, the difference between the right and left inner connection

CREATE TABLE Boy

(Name char (3) NOT NULL,

Flower Char (5)

) Engine=myisam CharSet UTF8;

CREATE TABLE Girl

(Name char (3),

Flower Char (5)

) Engine=myisam CharSet UTF8;

INSERT into the boy values (' Lin ', ' Rose '), (' Liu Xiang ', ' peach Blossom '), (' Jay Chou ', ' Jasmine '), (' Brother Sharp ', ' Lotus '),

(' Andy Lau ', ' dog tail flower ');

INSERT INTO girl values (' Avril del ', ' Rose '), (' Madame Curie ', ' peach Blossom '), (' Sister Furong ', ' Jasmine '), (' Chicken sister ', ' Jasmine '), (' Lin Chi ', ' lotus ');

Case one: According to the flower, male looking for female

Left connection

To left table, the right table to find matching data, can not find a match, with NULL.

Summary: TableA is n; TableB is M. In the case of TableA as the left table, the number of data rows obtained is at least n rows.

Case two: According to flowers, female looking for male

Summary: the left and right connections are converted to each other.

A on the left of B = "B" to the right of a

A LEFT Join B = = "B rightjoin A

How to Remember:

1, left and right connections can be converted to each other

2, the right connection can be converted to left join access (compatible with other data versions)

Case three: men and women looking for companionship

Summary: Internal connection inner JOIN query left and right to connect all the data. That gets the intersection of the left and right connections

Think: Can you find the same set of left and right connections?

A: Currently not available directly, MySQL does not support external links.

But it can be combined with union.

The quieter become,the more you is able to hear!

Small shell _mysql Select Connection Query