Beckham _ mysql select connection query, _ mysqlselect

Beckham _ mysql select connection query, _ mysqlselect
Select connection QueryBrief:
1. union Joint Query
2. Left and Right connections

1. union Joint Query

Purpose: merge two or more query results

Details: (Table 1 query results) union (Table 2 query results)

Run: first calculate the query result of table 1 and then the query result of table 2. Then combine the results through union.

Summary:

A. The number of columns in the query results is consistent between the left and right.

B. The final result is based on the column name of the first table.

C. It is recommended that the column types of the left and right query results be consistent, otherwise the conversion will be performed. From low to high. For example, column A in table 1 is an integer, and column A in table 2 is A floating point. The result of Table 1 in column A is converted to the floating point type.

D. It can be a different table.

E. If the rows extracted from different statements are identical (the values of each column are the same), the same rows will be merged (deduplication)

Case:

Query the result as follows:

A16, B 21, c 14, d 41

Step 1: use union to obtain the query results of two tables.

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

 

Disadvantages: in the above case, the table ta and tb do not have exactly the same columns (that is, the values of all columns in the query results are the same)

Modify the tb table

Execute the SQL statement in step 2.

It is found that the sum of values with id a is 5 rather than 10.

This is becauseIf the rows extracted from different statements are identical (the values of each column are the same), the same rows are merged (deduplication)

Solution: use union all

 

 

Ii. Connection Query

Generally, the following table has no relationship with the table. However, we can use a field to associate the table with the table.

Consider a table as a set.

2.1 left join

Syntax: select column 1, column 2, column N from tableA left join tableB on tableA. Column = tableB. Column

2.2 right join

Syntax: select column 1, column 2, column N from tableA right join tableB on tableA. Column = tableB. Column

2.3 inner connection

Syntax: select column 1, column 2, column N from tableA inner join tableB on tableA. Column = tableB. Column

 

2.4 differences between left and right connections

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 boy values ('Jeremy Lin ', 'Rose'), ('Liu Xiang ', 'peach blossom'), ('Jay Chou ', 'Jasmine '), ('sirge', 'Lotus '),

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

Insert into girl values ('avel', 'Rose '), ('Mrs Curie', 'peach blossom '), ('Sister Furong', 'Jasmine '), ('Sister feng ', 'Jasmine '), ('lin Zhiling', 'Lotus ');

Case 1: men looking for female based on flowers

Left join

Take the left table as the standard. Go to the right table to find the matching data. If no matching is found, use NULL to complete it.

Conclusion: tableA is N, and tableB is M. When tableA is the left table, the number of data rows is at least N.

Case 2: Search for male in female

Conclusion: The left and right connections are mutually converted.

A is on the left of B =, B is on the right of

A left join B =, B rightjoin

How to remember:

1. The left and right connections can be converted to each other.

2. You can convert the right connection to the left connection (compatible with other data versions)

Case 3: search for people with partners, regardless of men and women

Conclusion: inner join queries all data of the left and right connections. Returns the intersection of left and right connections.

Think: Can we find the Union of left and right connections?

A: currently, it cannot be obtained directly. Currently, mysql does not support external links.

However, union can be used.

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