Analysis of Query Sorting and Query Aggregation Function Usage in MySQL

This article illustrates the use of MySQL query sorting and query aggregate functions. Share it for your reference, as follows:

Sort

You can sort the data for easy viewing

Syntax:

select * from  Table name  order by  Column 1 asc|desc [, Column 2 asc|desc,...]

Description

Sort the row data by Column 1, if some of the values of Column 1 are the same, by Column 2, and so on

By default, it is arranged from small to large by column value (asc) asc is arranged from small to large, that is, ascending order desc is sorted from large to small, that is, descending

Example 1: Query the information of boys who have not been deleted, in descending order according to the student number

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

Example 2: Query the information of students who have not been deleted, in ascending order by name

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

Example 3: Display all the student information, first according to the age from the older- > Small sort, when the age is the same, according to the height from taller- > Dwarf sort

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 the total number of rows, and the stars and column names are written in parentheses, and 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 most undeleted students with this site number

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

Summation

sum (column) means finding 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

avg (column) means averaging 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;

For more readers interested in MySQL related content, please check the topics on this site: "MySQL Query Skills Encyclopedia", "MySQL Common Function Summary", "MySQL Log Operation Skills Encyclopedia", "MySQL Transaction Operation Skills Summary", "MySQL Stored Procedure Skills Encyclopedia" and "MySQL Database Lock Related Skills Summary"

I hope this article is helpful to everyone's MySQL database.