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
offset
Offset, can be omitted, default starting from 0.
row count
The 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);
union
For two result sets, duplicate data is displayed only once.
union all
For 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.)