MySQL knowledge learning-the twelfth day: summary data

Preface

It's been twelve days without knowing it. We already have a preliminary understanding of MySQL and have mastered some basic usage. The exercises on Niuke.com can basically be solved, but our next goal is how to solve better.

Aggregate function

We often need to summarize data without actually retrieving them. For this purpose, MySQL
provides special functions.

Aggregate functions (aggregate function) running in the row group, and the calculation returns a single value function
Insert picture description here

AVG function

AVG() finds the average value of the column by counting the number of rows in the table and calculating the sum of the values ​​of a specific column.
AVG() can be used to return the average value of all columns, or it can be used to return the average value of a specific column or row.

Consider the following table (product)

Insert picture description here


Insert picture description here


The above is to return the average value in the number column. Similarly, the AVG function can be used with the where clause to calculate the average value of a specific column value. The

Insert picture description here


Insert picture description here


above means that only the average value of a column can be calculated (the data type of the column must be a numeric value, otherwise the returned result is wrong)

Insert picture description here


We always add a record to the table product.

Insert picture description here


Before running the code,

Insert picture description here


we find that the average value is (27+34+4)/3=21.6667, ignoring the fourth row.

Count function

The COUNT() function counts. You can use COUNT() to determine the number of rows in the table or the number of rows that meet specific conditions.

Insert picture description here


When count counts, you can consider NULL values ​​or not, depending on your usage.

Insert picture description here


The number of rows in the statistics table (that is, the number of records, regardless of whether the record uses a null value, it will be recorded)

The following is the statistics column, if the column value is null, it will not be counted

Insert picture description here


Insert picture description here

MAX function

MAX() returns the maximum value in the specified column. MAX() requires the name of the column to be specified.

Insert picture description here


max can only count the maximum value of a column .

Insert picture description here

MIN function

The function of MIN() is just the opposite of the function of MAX(), which returns the minimum value of the specified column. Like MAX(), MIN() requires the column name to be specified.

Insert picture description here


Insert picture description here


The MAX and MIN functions can also be used in conjunction with the WHERE clause to return the maximum and minimum values ​​of the specified column.

SUM function

SUM() is used to return the sum (total) of the specified column value.

Insert picture description here


Insert picture description here

Aggregate different values

Insert picture description here


Insert picture description here


Insert picture description here


Look at the following example

Insert picture description here

The above average value is (27+34+4)/3=21.6667, because 27 appears twice, the DISTINCT keyword tells the AVG function to only count different values, so 27 only counts once.

Insert picture description here

Combined aggregate function

We can use multiple aggregate functions

Insert picture description here


at the same time. When using multiple aggregate functions, the last aggregate function cannot be followed by a comma. As

Insert picture description here


mentioned above, aliasing can be implemented by yourself. Please pay attention to keep the column names in the table as unique as possible.

The happy weekend is almost over, sad~