MySQL queries, subqueries, join queries, federated queries

MySQL queries, subqueries, join queries, federated queries

I. MySQL query five seed sentence
where (conditional query), having (filter), GROUP by (grouping), order by (sort), limit (limits the number of results)

Two. Sub-query
1.where Sub-query
SELECT * from Tb1 WHERE cat_id in (the Select Max (ID) from the TB2 GROUP by cat_id);

2.from Sub-query
Select t2_id from (select t2_id from Tb2 ORDER by t2_id DESC);

3.exists Sub-query (the outer query results to the inner layer, to see if the inner layer of the query is set up to return the data of the outer query)
SELECT * from Test5 where EXISTS (select ID from test2 where test2.id=test5.id);

Query Test5 in the first row of id=1, and then execute the Select ID from test2 where test5.id=1, if present, returns this row of data, does not exist, does not return

Three. Union usage, union query (Portrait/Row junction)
(combine two or more query results, ask for the same number of columns in the query, the corresponding column type of the recommended query is consistent, you can query multiple tables, multiple query statements if the column name is not the same, then take the first column name!) If the values of each column of the row taken in a different statement are the same, then the result will automatically repeat, and if you do not want to repeat it, add all to declare, that is, union ALL)

SELECT * FROM Test2 UNION SELECT * from TEST5;
Union of Test2 result sets and test5 result set rows (de-weight)

SELECT * from Test2 UNION all SELECT * from TEST5;
Unite the rows of the test2 result set and the TEST5 result set (no weight)

Four. Join query (Horizontal/column junction)
1. Inner coupling (INNER Join,inner can be omitted, the following three formulations are equivalent)
SELECT * from Test2,test5 WHERE test2.id=test5.id;
SELECT * from Test2 JOIN test5 on test2.id=test5.id;
SELECT * from Test2 JOIN TEST5 using (ID);//If the two tables have the same column name, use instead of on

Combining the columns of the result set of the test2 and test5 by conditions is a combination

2. Left Junction
SELECT * from Test2 left joins TEST5 on Test2.id=test5.id;

The left table Test2, and the Test5 junction, the data in the test2 will be taken out.

3. Right junction
SELECT * from Test2 right JOIN test5 on test2.id=test5.id;

To the right table Test5, and Test2 Junction, the data in the TEST5 will be taken out.

Daily use fixed one, with left junction instead of right junction.

Reference:

Http://www.cnblogs.com/rollenholt/archive/2012/05/15/2502551.html

MySQL queries, subqueries, join queries, federated queries