Analysis on the usage of MySQL query sorting and query aggregation function

This paper gives an example of the use of MySQL query sorting and query aggregate function. Share with you for your reference, the details are as follows:

Sort

To facilitate viewing the data, you can sort the data

Syntax:

Select * from table name order by column 1 asc | desc [, column 2 asc | desc,...]

Description

Sort row data by column 1, if some rows and columns have the same value, sort by column 2, and so on

By default, the column values are sorted from smallest to largest (asc) asc is sorted from small to large, that is, ascending desc is sorted from big to small, that is, descending order

Example 1: query undeleted male information, in descending order by student number

Select * from students where gender=1 and is_delete=0 order by id desc

Example 2: query undeleted student information, in ascending order by name

Select * from students where is_delete=0 order by name

Example 3: all student information is displayed, sorted first by age from big to small, and by height from tall to short when the age is the same.

Select * from students order by age desc,height desc

Aggregate function

In order to get statistics quickly, the following five aggregate functions are often used

Total

Count (*) indicates that the total number of rows is calculated. The star and column names are written in parentheses. The result is the same.

Example 1: query the total number of students

Select count (*) from students

Maximum value

Max (column) means to find the maximum value of this column

Example 2: query the maximum number of girls

Select max (id) from students where gender=2

Minimum value

Min (column) means to find the minimum value of this column

Example 3: query the minimum number of undeleted students

Select min (id) from students where is_delete=0

Summation

Sum (column) represents the sum of this column

Example 4: inquire about the total age of boys

Select sum (age) from students where gender=1;-- mean age select sum (age) / count (*) from students where gender=1

Average value

Avg (column) means to find the average of this column.

Example 5: query the average number of undeleted girls

Select avg (id) from students where is_delete=0 and gender=2

More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL Common function Summary", "MySQL Log Operation skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection" and "MySQL Database Lock related skills Summary"

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.