Analysis of MySQL query sorting and query aggregation function usage

This article uses examples to illustrate the use of MySQL query sorting and query aggregate functions. Share with you for your reference, the details are as follows:

Sorting

To facilitate viewing of data, you can sort the data

grammar:

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

illustrate

Sort the row data by column 1. If the values ​​of some rows and column 1 are the same, sort them by column 2, and so on.

• By default, the columns are sorted from small to large (asc)
• asc Arrange from small to large, that is, ascending order
• desc Sort from large to small, that is, descending order

Example 1: Query the information of undeleted male students in descending order by student ID

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

Example 2: Query the information of undeleted students in ascending order by name

select * from students where is_delete=0 order by name;

Example 3: Display all student information, sort by age from oldest to youngest, and then sort by height from tallest to shortest when the ages are the same

select * from students order by age desc,height desc;

Aggregate functions

In order to quickly obtain statistical data, the following five aggregation functions are often used:

total

count(*) means to calculate the total number of rows. The result is the same if you write a star and a column name in brackets.

Example 1: Query the total number of students

select count(*) from students;

Maximum

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

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

Example 3: Query the minimum number of students who have not been deleted

select min(id) from students where is_delete=0;

Sum

sum(column) means to find the sum of this column

Example 4: Query the total age of boys

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

average value

avg(column) means to find the average value of this column

Example 5: Query the average number of girls who have not been deleted

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

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.