Record--select in Operation data table (where expression, GROUP by, having, LIMIT)

Original link: http://www.ifyao.com/2015/01/26/%E6%93%8D%E4%BD%9C%E6%95%B0%E6%8D%AE%E8%A1%A8%E4%B8%AD%E7%9A%84%E8%AE%B0% e5%bd%95-select-where%e8%a1%a8%e8%be%be%e5%bc%8f%e3%80%81group-by%e3%80%81having%e3%80%81limit/

Manipulating records in a data sheet--select
First, find records
1. Syntax:

Select select_expr [, select Expr2 ...]
[
From table_references query table name
[WHERE Where_conditon] Query criteria
[GROUP by {col_name|position} [asc| DESC],...] Group by a field, showing only the first one
[Having Where_conditon] when grouping, give the display condition
[ORDER by {col_name|expr|position} [asc| DESC],...] Sort
[Limit {[Offset,]row_count|row_count offset}] limits the number of returns
}

2. The order of query expressions affects the order of results
3. Use Tbl_name.col_name to represent column records, which can distinguish between columns with the same name when queried
4, using [as] alias_name to give it an alias, alias can be used for group by, ORDER by or having clauses, such as SELECT ID as userid,username as uname from users; The result of this query is represented by an alias.

Manipulating records in a data table--where an expression
1. Conditional expression where
2, filter the records, if you do not specify a WHERE clause, it means to operate on all records
3. You can use MySQL-supported functions or operators

Manipulating records in a data sheet--group by
1, the query results are grouped GROUP by
2. Syntax: You can specify column names and locations for columns [GROUP by {col_name|position} [asc| DESC],...]
3. For example:
Use col_name name: SELECT sex from the users GROUP by sex;
4. ASC Ascending Ascending desc Descending descending

Manipulating records in a data sheet--having
1. Grouping criteria: Grouping a subset of records
2. Grammar: [Having Where_conditon]
3. For example: SELECT * from the users GROUP by age have age > 30;
4, require the use of where_condition in the condition field, must appear in the query results

Use having to set up a grouping, either by guaranteeing that the grouping condition is an aggregate function or by guaranteeing that the fields in the grouping condition appear in the query

For example:
Select Sex,age from the Users group by 1 have age > 35;
Or
Select Sex from the Users group by 1 have count (ID) >= 2;

Manipulating records in a data sheet--order by
1. Sort the results of the query: [ORDER BY [col_name | expr | position} [asc| DESC],...]
2, can be sorted by more than one field at the same time, the rule is first by the previous field row, on the basis of the following field row.
3, such as: SELECT * from the users ORDER by Age,id DESC; Sort by age first, if age has duplicates, the duplicate fields are sorted by ID.
Sort the results of a query

SELECT * from the Users order by age Desc;age descending

SELECT * from the users order by age,id Desc; (If the age is the same, the age of the same point is sorted by ID)

Manipulating records in a data sheet--limit
1. Limit the number of query results returned: [Limit {[offset,] row_count|row_count offset offset}]
2. All results are returned by default and limit can be used to return a specified number of records
3. If select * from Users LIMIT 2, 3; The offset is 2, starting with the third and returning 3 results
4, the offset is not sorted by the ID and other fields, but in the order of the current query sort of the way to offset

Demo

Create a table to hold the records of the query
Writes the user name of the record age>30 in the users table to the test table
Create a new table test
CREATE TABLE Test (
ID tinyint unsigned primary key auto_increment,
Username varchar (20));
Insert the result of the query into test
Insert Test (username) Select username from users where age>=30;

Record--select in the action datasheet (where expression, GROUP by, having, LIMIT)