Introduction of the _mysql SELECT5 seed clause in Xiao Bei

MySQL select5 seed sentence introduction Brief
One or five kinds of words
Ii. Specific explanations of five sentences one or five kinds of words

where, group by, having, order by, limit

Ii. specific explanations of five sentences

2.1. Understanding WHERE clause

Understand select column 1. Column 2 ... fromtable where WHERE clause

A, consider the WHERE clause as an expression. Querying rows that satisfy an expression in a table

B, the order in which the above query statements are run. The table is first found. Run the WHERE clause again. Display after the expression is met.

C, comparison operators, and logical operators

2.2, GroupBy

2.2.1, understand the grouping effect, apply to statistics

2.2.2. Works with aggregation functions.

Max (column). Min (column). AVG (), sum (), COUNT ()

2.2.3, according to the group after. Can only get the first piece of data for each group

2.2.4, Case table and data such as the following:

typeID	Name	Price
1	A1	10
2	B1	11
1	A2	12
2	B2	22

Specific explanations 1:select Typeid,max (price) from the table group by typeID

2.2.4. 1, from table tables, grouped according to typeID

Data for typeID of 1

typeid	name	price
1	a1	10
Span style= "Font-family:microsoft Yahei; font-size:12px ">1	a2	12

typeid 2 data

typeid	name	price
2	b1	11
Span style= "Font-family:microsoft Yahei; font-size:12px ">2	b2	22

2.2. 4 . 2. From the data in the grouping, get the value of each grouping specific column according to the aggregation function

typeID	Price
1	12
2	22

Specifically explained 2:select typeID, Name,max (price) from the table group by typeID

typeid	name	price
1	a1	12
Span style= "Font-family:microsoft Yahei; font-size:12px ">2	b1	22

Note: When the control data is correct, the name is found incorrectly.

Because the price maximum value is 12 o'clock. Name is A2 instead of A1.

This is due to. In the results of the grouping, when using the aggregate function Max to get the maximum value, it is from the first line down, the first row of the name is A1, the lower is the only price value.

So. Name is still the value obtained for the first row.

2.3.

having

Having and where similarities and differences :

1, having and where similar, can filter the data; How to write the expression after the where.

2. Where the columns in the table play a role in querying the data. Having a function for the columns in the query results, filtering the data

Case: Finding the average score of two or more failed students

Selectname,avg (Score), SUM (score<60) as s from Stu Group by name have s>=2;

2.4. and limit

1, order by sorting function: The query results are sorted in ascending or descending order according to one or more fields. By default, ascending

Syntax: Select field 1, Field 2from table order by field 1 [Asc|desc]

2, limit on the query results to take the corresponding number of bars

Syntax: Select field 1, field 2from table limit offset,n

(The,0<=offset< summarizes the number of fruit bars.) N>0. Offset, n refers to the location of offset to fetch n data)

2.5. Summary

1. Syntax Order:

Select field 1, Field 2 ..... From table WHERE clause GROUP BY field HAVING clause order BY field [Asc|desc] limit n,m;

2. Order of operation

2.1. Source data found from table

2.2, WHERE clause, consider it an expression. A row on the basis of 2.1 to infer whether an expression is true

2.3, group by on the basis of 2.2, the fields are grouped to get query results

2.4, having on the basis of 2.3. The query result is also a row to infer whether an expression is true.

2.5, order by on the basis of 2.4. Sort the results of a query

2.6, limit on the basis of 2.5, take the corresponding number of bars

2.7, select on the basis of 2.6. Show the results

The quieter you become,the more is able to hear.

Introduction of the _mysql SELECT5 seed clause in Xiao Bei