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

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

Run: The first table 1 query results, 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 Finally, the result is displayed as the first table's column name mainly

C , the column type of the left and right query results is best also 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 , assuming that the rows taken in different statements are all the same ( the values of each column are the same), then the same rows will be merged (DE-weighed)

Case:

Query This result:

A16,b 21,c 14,d 41

Step 1:union to get query results for two tables

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

Deficiency: The same column does not exist in table TA and TB in the above case (i.e. all columns in the query result are the same value)

Change the next TB table

At this time Run the SQL statement for step 2

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

This is due to the assumption that the rows taken in different statements are exactly the same (the values of 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 get a table to have sex with a table through a field.

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 internal 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 flowers, men looking for women

Left connection

Take left table as the standard. Go to right table to find matching data, can't find match, with NULL to complement.

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 Flower, 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, the left and right connections can transform each other

2. Ability to convert right connection to left join (compatible with other data version numbers)

Case three: men and women, looking for someone to have a companion

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: Not directly available at the moment, MySQL does not support external links.

But can be combined with union

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

Small shell _mysql Select Connection Query