Shulou(Shulou.com)06/01 Report--

Editor to share with you the method of statistical data in mysql, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Mysql statistical data methods: 1, the use of MAX () function statistics of a field the most big data; 2, the use of MIN () function statistics of a field of minimum data; 3, the use of COUNT () function statistics of the number of rows of query results; 4, the use of SUM () function statistical data sum; 5, the use of AVG () function statistics average.

MySQL MAX () function

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

To make it easier to understand, first create a student score sheet tb_students_score, the data contents of which are shown below.

Mysql > use test_db;Database changedmysql > 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, enter the SQL statement and the execution result is shown below.

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 maximum value of the student_score field queried by the MAX () function is 100.

The MAX () function applies not only to finding numeric types, but also to character types.

[example 2] look for the maximum value of student_name in the tb_students_score table, and the SQL statement entered and the execution result are shown below.

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 determines the size of the letters and returns the largest character or string value.

Note: the MAX () function can also return the maximum value in any column, including the maximum value of the character type. When comparing the data of character types, according to the value of the ASCII code of the character, the ASCII code of a character is the smallest and the ASCII code of z is the largest. When comparing, first compare the first character, and if equal, continue to compare the next character until the two characters are not equal or until the end of the character. For example, when b is compared to t, t is the maximum; when bcd is compared with bca, bcd is the maximum.

MySQL MIN () function

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

To make it easier to understand, you need to use the data table tb_students_score that you created when you talked about the MAX () function in the previous section.

[example] find the lowest score in the tb_students_score table, enter the SQL statement and the execution result is shown below.

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 result, the minimum value of the student_score field queried by the MIN () function is 88.

Tip: the MIN () function is similar to the MAX () function in that it applies not only to finding numeric types, but also to character types.

MySQL COUNT () function

The MySQL COUNT () function counts the total number of record rows contained in the data table, or returns the number of data rows in the column based on the query results, using the following two methods:

COUNT (*) calculates the total number of rows in the table, regardless of whether a column has a numeric value or is null.

COUNT (field name) calculates the total number of rows under the specified column, ignoring rows with null values.

You need to use the following table tb_students_score created when you introduce the MAX () function.

[instance] query the total number of rows in the tb_students_score table. The SQL statement entered and the execution result are shown below.

Mysql > SELECT COUNT (*)-> AS students_number-> FROM tb_students_score;+-+ | students_number | +-+ | 10 | +-+ 1 row in set (0.03 sec)

As you can see from the query results, COUNT (*) returns the total number of rows recorded in the tb_students_score table, regardless of the value. The name of the returned total is students_number.

Tip: the way to treat NULL values when calculating totals is that rows with empty values of the specified column are ignored by the COUNT () function, but if you do not specify a column and use the asterisk "*" in the COUNT () function, all records are not ignored.

MySQL SUM () function

MySQL SUM () is a summation function that returns the sum of the specified column values.

How does the SUM () function work?

If you use the SUM function in a SELECT statement that does not return a matching row, the SUM function returns NULL instead of 0.

The DISTINCT operator allows you to evaluate different values in the collection.

The SUM function ignores the NULL value in the calculation.

You need to use the following table tb_students_score created when you introduce the MAX () function.

[example] the total score of the student's score is calculated in the tb_students_score table, and the input SQL statement and the execution result are shown below.

Mysql > SELECT SUM (student_score)-> AS score_sum-> FROM tb_students_score;+-+ | score_sum | +-+ | 942 | +-+ 1 row in set (0.00 sec)

As you can see from the query results, the SUM () function returns the sum of all the students' scores as 942.

Tip: the SUM () function ignores rows with column values of NULL when calculating.

MySQL AVG () function

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

You need to use the following table tb_students_score created when you introduce the MAX () function.

[example] in the tb_students_score table, the average scores of all students are queried. The SQL statement entered and the execution results are shown below.

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 argument is the name of the column to be calculated, and to get the average of multiple columns, you need to use the AVG () function on each column.

These are all the contents of the statistical data method in mysql. Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.