MySQL query clauses (group By,limit,union,order by, etc.)

Order by

Sort by field value, default ascending (ASC). The collation rule determines the sort relationship.
order by 字段 升序|降序（asc|desc）,

Order by allows multiple fields to be sorted.
Refers to the first field ordered, if not distinguished, the second field is used, and so on.

 Create table Test (id int primary key  not null auto_ Increment,name varchar(ten), group_id int ); Insert  into Test values(null,' Jason ',5); Insert  into Test values(null,' Mark ',3); Insert  into Test values(null,' Jason ',4); Insert  into Test values(null,' Ivy ',3); Insert  into Test values(null,' Jason ',3); Insert  into Test values(null,' Mark ',5); Insert  into Test values(null,' Mark ',5); Insert  into Test values(null,' Jason ',4); Select *  from Test order  by group_id ASC, ID desc ;

Note: If you are grouping, you should use the sorting of the grouped fields.

select * from test group by group_id order by group_id;

Limit

Limit the number of records obtained.
Syntax for limit:
limit offset, row count
offsetOffset, can be omitted, default starting from 0.
row countThe total number of records, if the number is greater than the number of records remaining, then the number of records remaining can be obtained.

select * from test limit 2;select * from test limit 2,3;select * from test limit 100;

distinct

To remove duplicate data, duplicate records refer to records that have the same field values , rather than the same records for the partial fields.

select distinct group_id from test;

Two fields are not duplicated

select distinct name,group_id from test;

The opposite of distinct is all, which means all, the default is all behavior.

Union

Merging the results of multiple SELECT statements together is called a union query.
Use the Union keyword to federate two SELECT statements.

 Select *  from Test where name=' Jason ' order  by ID desc limit 2; Select *  from Test where name=' Jason ' order  by group_id  Desc limit 1;( Select *  from Test order  by ID desc limit 2) Union (Select *  from Test order  by group_id desc limit 1); /c9>

Application Scenarios:
The conditions in which data is obtained are logically conflicting or are difficult to represent within a single logic, and can be split into multiple logic to achieve the final merging of the results.

If the result of the Union has duplicate records, it will be de-duplicated, similar to distinct.

(select id,name,group_id from test where name=‘mark‘ order by group_id desc limit 5)union(select id,name,group_id from test where name=‘jason‘ order by group_id asc limit 5);

unionFor two result sets, duplicate data is displayed only once.
union allFor the two result sets, the duplicate data is displayed.

 (select  id,name,group_id from  test where  name=< Span class= "hljs-string" > ' mark '  order  by  group_id desc  limit 5 ) union  all  (select  ID, name,group_id from  test where  name= ' Jason '  order  by  group_ ID asc  limit 5 );   

sorting of results obtained by the child statements of Union
1. Wrap the child statement inside the parentheses.
2. The child statement order by, only takes effect when order by with limit.
The reason is that when the Union is making a child statement, the order by is optimized (ignored) without the limit clause;

All results of union are sorted uniformly

(select id,name,group_id from test where name=‘mark‘)union all(select id,name,group_id from test where name=‘jason‘)order by group_id;

The parentheses of a child statement are not necessary, but the syntax does not look clear and is prone to ambiguity.

Note:
Specifies that the number of fields retrieved by multiple SELECT statements must be the same.
The data types should be consistent, and if MySQL field properties are inconsistent, MySQL will do the type conversion processing internally, but the requirement is to be able to convert successfully.
the name of the column in the result of the search is determined by the column name of the first SELECT statement.

Sub-query

Definition: A query statement inside a statement is a subquery.
Requirement: The subquery needs to be enclosed in parentheses.

Information for the record with the largest query ID

select id,name,group_id from test order by id desc limit 1;

There is no problem with the above logic, but what about the same maximum number of IDs?
Get the maximum ID first, and then decide if you have the same ID.
select id from test order by id desc limit 1;
MySQL allows the above query results to be used as a value.

select * from test where id = (select id from test order by id desc limit 1);

Sub-query categories:

Different classifications, there will be different ways to use.
Classification criteria: The location where the subquery appears and the return value form of the subquery.

return value classification:
Single value,
A column,
multiple columns,
Table (multi-row, multi-column)

Occurrence Location Classification:
where type
From type
exists type

How do I use subqueries? single value (scalar):

After getting a value, use the relational operator to make the decision.> < = >= <= !=

select * from test where id < (select max(id) from test);

query:

Gets a column, usually a column of values for multiple rows. Note that it must be a column.

select name from test where group_id=4;select id,name,group_id from test where name in (select name from test where group_id=5);

Must be done with the operator of the same collection class (in| Not in).

Other set operators:
Any (collection) –> any one of the collection
=any (set) –> equals any one in the set, equivalent to in.

All (collection) –> all elements in the collection
!=all (collection) –> is not equal to all elements in the collection, etc.

Some (some of the collection –> collection, syntactically the same as any.)

select id,name,group_id from test where name =any (select name from test where group_id=5);select id,name,group_id from test where name !=all (select name from test where group_id=5);

Summary:
=any<==> in
!=all <==>not in
some和any是同义词
All,any and some can use operators other than =，! the other.

return a row

select distinct id,group_id from test where name=‘mark‘ and group_id = 3 limit 1;

When participating in comparisons, use parentheses to build a row.
Note: By using limit 1, make sure that one row

select  id,name,group_id from  test where  (id,group_id) = (select  distinct  id,group_id  From  test where  name= ' mark '  and  group_id = 3 );  Select id,name,group_id  from Test where (id,group_id)  in (select  Distinct id,group_id  from Test where name=' mark '  and group_id = 3 );

Returns a table

select id,name from (select id,name,group_id from test where group_id>=4) as t where group_id=5 and name like ‘ma%‘;

If the FROM clause is used within the FROM clause, a table is required, and can be a result, an alias should be given to the result.

select newId,name from (select id as newId,name,group_id from test where group_id>=4) as t where group_id=5 and name like ‘ma%‘;

The alias used by the external query is specified by the subquery.

exists

exists(subquery)
Judging by: If the subquery can return data, it is considered that the exists expression returns true, otherwise false is returned.

select * from test where exists(select * from class where test.id=class_id);select * from testwhere id in(select class_id from class);

The above two statements accomplish the same thing, but the solution is not the same.

exists: first obtain the data of the test table , then get the ID field, then go to the class table to find the corresponding value, find, the description matches the condition.

In : get all the possibilities in the class table first class_id , and then, when retrieving the test table data, determine whether the current class_id is in the ID collection.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL query clauses (group By,limit,union,order by, etc.)