How to find the maximum, minimum and average value in mysql?

In mysql, you can use the MAX() function, MIN() function and AVG() function to find the maximum value, minimum value and average value respectively. The MAX() and MIN() functions can return the maximum and minimum values ​​in the specified column; the AVG() function calculates the average value of the specified column data by calculating the number of returned rows and the sum of each row of data.

(Recommended tutorial: mysql video tutorial)

MySQL MAX function: Query the maximum value of a specified column Value

MySQL MAX() function is used to return the maximum value in the specified column.

In order to facilitate understanding, first create a student score table tb_students_score. The data content of the student score table is as follows.

mysql> use test_db;
Database changed
mysql> SELECT * FROM tb_students_score;
+--------------+---------------+
| student_name | student_score |
+--------------+---------------+
| Dany         |            90 |
| Green        |            99 |
| Henry        |            95 |
| Jane         |            98 |
| Jim          |            88 |
| John         |            94 |
| Lily         |           100 |
| Susan        |            96 |
| Thomas       |            93 |
| Tom          |            89 |
+--------------+---------------+
10 rows in set (0.13 sec)

[Example 1] Find the highest score in the tb_students_score table. The input SQL statement and execution results are as follows.

mysql> SELECT MAX(student_score)
    -> AS max_score
    -> FROM tb_students_score;
+-----------+
| max_score |
+-----------+
|       100 |
+-----------+
1 row in set (0.06 sec)

As you can see from the running results, the MAX() function queries the maximum value of the student_score field as 100.

The MAX() function is not only suitable for finding numeric types, but also for character types.

[Example 2] Find the maximum value of student_name in the tb_students_score table. The input SQL statement and execution results are as follows.

mysql> SELECT MAX(student_name)
    -> AS max_name
    -> FROM tb_students_score;
+----------+
| max_name |
+----------+
| Tom      |
+----------+
1 row in set (0.03 sec)

As you can see from the running results, the MAX() function can judge the size of letters and return the largest character or string value.

Note: The MAX() function can also return the maximum value in any column, including returning the maximum value of character type. When comparing character type data, the comparison is based on the ASCII code value of the character. From a to z, a has the smallest ASCII code and z has the largest. When comparing, first compare the first character. If they are equal, continue to compare the next character until the two characters are not equal or the character ends. For example, when b is compared with t, t is the maximum value; when bcd is compared with bca, bcd is the maximum value.

MySQL MIN function: Query the minimum value of the specified column

MySQL MIN() function is used to return the minimum value in the query column.

[Example] Find the lowest score in the tb_students_score table. The input SQL statement and execution results are as follows.

mysql> SELECT MIN(student_score)
    -> AS min_score
    -> FROM tb_students_score;
+-----------+
| min_score |
+-----------+
|        88 |
+-----------+
1 row in set (0.00 sec)

As you can see from the results, the MIN() function queries the minimum value of the student_score field to 88.

Tip: The MIN() function is similar to the MAX() function and is not only suitable for finding numeric types, but also for character types.

MySQL AVG function: average value

MySQL AVG() function calculates the number of rows returned and the sum of each row of data to find the specified column The average of the data.

[Example] In the tb_students_score table, query the average score of all students. The input SQL statement and execution results are as follows.

mysql> SELECT AVG(student_score)
    -> AS score_avg
    -> FROM tb_students_score;
+-----------+
| score_avg |
+-----------+
|   94.2000 |
+-----------+
1 row in set (0.03 sec)

Tip: When using the AVG() function, the parameter is the column name to be calculated. If you want to get the average of multiple columns, you need to use the AVG() function in each column.

The above is the detailed content of How to find the maximum, minimum and average value in mysql?. For more information, please follow other related articles on the PHP Chinese website!