30 minutes review MySQL syntax (bottom)

Six. Aggregation function

Using the aggregation function, you can easily analyze the data, mainly have the following application scenarios:

1. Determine the number of rows in a table (statistics)

2. Sum the values of a column

3. Find the table column maximum, minimum or average value

Feature: Using the aggregate function, the returned result is a single row

Useful: typically used for subqueries or for use with groups. It's more of a combination of groups.

The following function distinct is an optional value

6.1 AVG ([DISTINCT] expr) #求某一列平均值 automatically removes null-content columns

For example: Select AVG (age) from student # to ask for the average ages of students

6.2 COUNT ([DISTINCT] expr) #统计某一列出现的行数 automatically removes null-content columns

Example: SELECT * FROM student # Statistics total number of student records

6.3 MAX ([DISTINCT] expr) #求出某一列的最大值

For example: select Max (age) From student # calculates the maximum ages of a student is

6.4 MIN ([DISTINCT] expr) #求出某一列的最小值

For example: select min From student # calculates the minimum age of a student is

Max and Min are usually used to find the maximum/small value of a number or date

6.5 sum ([DISTINCT] expr) # sums all the values of a column

Example: select SUM (age) from student # Statistics The sum of the students ' ages

Seven. Data grouping

The general aggregation function is used in combination with the grouping, which divides the data in the table into groups according to a certain characteristic, and then the aggregation operation after grouping separately.

6.1 Keyword: GROUP by Cloum

Select Sex,count (*) as total number from student group by sex #统计不同性别的人数

Feature: Group and gather first

Be aware that:

1. Although there is no explicit requirement, the columns that are generally used for grouping are queried in the SELECT statement

2. Keyword priority: GROUP by must precede the where statement, before ORDER by

3.group by after cannot be followed by aggregation function, not with Alias

4. If there is a null value in the grouping, NULL is separated as a single grouping. If there is more than one null, multiple null can be used as a grouping

6.2 Filtering Packet Data having

Where is the row data filtering, there is no concept of grouping, if you want to filter the data in the group, you need to use another keyword HAVING5

Having support for all the operators in the WHERE clause, the syntax is exactly the same.

For example: From the student table, the number of boys, girls, older than 20 years of age, and the number of more than 5

Select Sex,count (*) from student where age > Group by sex have count (*) > 5

The after-having expression must be a valid non-aliased expression that appears after the Select,

6.3 Sorting after grouping by number of people (the order of the individual keywords can not be wrong)

Select Sex,count (*) from student where age > Group by sex have count (*) > 5

6.4 Order of Keywords

From front to back: Select, from, where, group by, have, order by, limit

30 minutes review MySQL syntax (bottom)