How to collect statistics in mysql?

Methods for statistical data in mysql: 1. Use the MAX() function to count the maximum data of a certain field; 2. Use the MIN() function to count the minimum data of a certain field; 3. Use the COUNT() function to count the query The number of rows of the result; 4. Use the SUM() function to count the sum of data; 5. Use the AVG() function to count the average.

(Recommended tutorial: mysql video tutorial)

MySQL MAX() function

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

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

In order to facilitate understanding, you need to use the data table tb_students_score created when talking about the MAX() function in the previous section.

[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 COUNT() function

MySQL COUNT() function counts the total number of record rows contained in the data table, or returns the number of data rows contained in the column based on the query results. The usage methods are as follows Two types:

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

COUNT (field name) calculates the total number of rows under the specified column. Rows with null values ​​will be ignored during calculation.

Here you need to use the following table tb_students_score created when introducing the MAX() function.

[Example] Query the total number of rows in the tb_students_score table. The input SQL statement and execution results are as follows.

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, no matter what the value is. The total number returned is named students_number.

Tip: The way NULL values ​​are treated when calculating totals is that rows with null values ​​in the specified column are ignored by the COUNT() function, but if the column is not specified and the star is used in the COUNT() function If marked "*", all records will not be 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 returns no matching rows, the SUM function returns NULL instead of 0.

• The DISTINCT operator allows calculation of distinct values ​​in a collection.

• The SUM function ignores NULL values ​​in calculations.

Here you need to use the following table tb_students_score created when introducing the MAX() function.

[Example] Calculate the total score of students' grades in the tb_students_score table. The input SQL statement and execution results are as follows.

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 student’s grades as 942.

Tip: The SUM() function ignores rows with NULL column values ​​when calculating.

MySQL AVG() function

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

Here you need to use the following table tb_students_score created when introducing the MAX() function.

[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 collect statistics in mysql?. For more information, please follow other related articles on the PHP Chinese website!