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.
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
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)
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
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)
We always add a record to the table product.
Before running the code,
we find that the average value is (27+34+4)/3=21.6667, ignoring the fourth row.
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.
When count counts, you can consider NULL values or not, depending on your usage.
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
MAX() returns the maximum value in the specified column. MAX() requires the name of the column to be specified.
max can only count the maximum value of a column .
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.
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() is used to return the sum (total) of the specified column value.
Aggregate different values
Look at the following example
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.
Combined aggregate function
We can use multiple aggregate functions
at the same time. When using multiple aggregate functions, the last aggregate function cannot be followed by a comma. As
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~