MySQL data query: query using set/aggregation functions

MySQL aggregate functions are as follows:

Function	Function
avg()	Returns the average value of a certain column
count()	Returns the number of rows in a certain column
max()	Returns the maximum value of a certain column
min()	Returns a certain The minimum value of the column
sum()	Returns the sum of the values ​​in a column

(1)count () function
(2)sum() function
(3)avg() function
(4)max() function
(5)min() function

---

(Free learning recommendation: mysql video tutorial)

(1)count() function

• The 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. There are two ways to use it:
  ①count(*) calculates the table The total number of rows in the column, 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.

[Example 1] To query the total number of rows in the customers table, the SQL statement is as follows:

mysql> select count(*) as cust_num    -> from customers;+----------+| cust_num |+----------+|        4 |+----------+1 row in set (0.06 sec)

It can be seen from the query results that count(*) returns the total number of rows recorded in the customers table. , no matter what its value is. The returned total is named cust_num.

[Example 2] Query the total number of customers with email addresses in the customers table. The SQL statement is as follows:

mysql> select count(c_email) as email_num    -> from customers;+-----------+| email_num |+-----------+|         3 |+-----------+1 row in set (0.00 sec)

The different results of the above two examples indicate that the two methods treat null when calculating the total number. The value method is different, that is, rows with empty values ​​in the specified column are ignored by the count() function, but if the column is not specified and * is used in the count() function, all records will not be ignored.

[Example 3] In the orderitems table, use the count() function together with the group by keyword to calculate the total number of records in different groups.

mysql> select o_num,count(f_id)
    -> from orderitems    -> group by o_num;+-------+-------------+| o_num | count(f_id) |+-------+-------------+| 30001 |           4 || 30002 |           1 || 30003 |           1 || 30004 |           1 || 30005 |           4 |+-------+-------------+5 rows in set (0.00 sec)

(2)sum() function

• sum is a sum function that returns the sum of the specified column values.

[Example] Query the total amount of fruits purchased for order No. 30005 in the orderitems table. The SQL statement is as follows:

mysql> select sum(quantity) as items_total    -> from orderitems    -> where o_num = 30005;+-------------+| items_total |+-------------+|          30 |+-------------+1 row in set (0.05 sec)

sum() can be used together with group by. Calculate the sum of each group.

[Example] In the orderitems table, use the sum() function to count the total amount of fruits ordered in different order numbers. The SQL statement is as follows:

mysql> select o_num,sum(quantity) as items_total    -> from orderitems    -> group by o_num;+-------+-------------+| o_num | items_total |+-------+-------------+| 30001 |          33 || 30002 |           2 || 30003 |         100 || 30004 |          50 || 30005 |          30 |+-------+-------------+5 rows in set (0.00 sec)

When the sum function calculates, the value is ignored. null row.

(3)avg() function

• The avg() function calculates the average of the specified column data by calculating the number of returned rows and the sum of the data in each column.
• When the avg() function is used, its parameter is the name of the column to be calculated. If you want to get multiple averages of multiple columns, you need to use the avg() function on each column.

[Example 1] In the fruits table, query the average fruit price of the supplier with s_id=103. The SQL statement is as follows:

mysql> select avg(f_price) as avg_price    -> from fruits    -> where s_id = 103;+-----------+| avg_price |+-----------+|  5.700000 |+-----------+1 row in set (0.05 sec)

[Example 2] In the fruits table , query the average price of fruit from each supplier, the SQL statement is as follows:

mysql> select s_id,avg(f_price) as avg_price    -> from fruits    -> group by s_id;+------+-----------+| s_id | avg_price |+------+-----------+|  104 |  7.000000 ||  101 |  6.200000 ||  103 |  5.700000 ||  107 |  3.600000 ||  102 |  8.933333 ||  105 |  7.466667 ||  106 | 15.700000 |+------+-----------+7 rows in set (0.00 sec)

group by keyword groups the records according to the s_id field, and then calculates the average value of each group. This grouping The averaging method is very useful. For example, find the average scores of students in different classes, find the average salary of workers in different departments, find the average annual temperature in various places, etc.

(4)max() function

• max() returns the maximum value in the specified column.
• In addition to finding the largest column value or date value, the max() function can also return the maximum value in any column, including the maximum value of the character type.

[Example 1] Find the fruit value with the highest price on the market in the fruits table. The SQL statement is as follows:

mysql> select max(f_price) as max_price from fruits;+-----------+| max_price |+-----------+|     15.70 |+-----------+1 row in set (0.05 sec)

[Example 2] Find the value provided by different suppliers in the fruits table The fruit value with the highest price, the SQL statement is as follows:

mysql> select s_id,max(f_price) as max_price    -> from fruits    -> group by s_id;+------+-----------+| s_id | max_price |+------+-----------+|  104 |      7.60 ||  101 |     10.20 ||  103 |      9.20 ||  107 |      3.60 ||  102 |     11.20 ||  105 |     11.60 ||  106 |     15.70 |+------+-----------+7 rows in set (0.00 sec)

[Example 3] Find the maximum value of f_name in the fruits table, the SQL statement is as follows:

mysql> select max(f_name) from fruits;+-------------+| max(f_name) |+-------------+| xxxx        |+-------------+1 row in set (0.00 sec)

(5)min() function

- min() returns the minimum value in the query column.

[Example 1] Find the lowest price fruit value on the market in the fruits table. The SQL statement is as follows:

mysql> select min(f_price) as min_price    -> from fruits;+-----------+| min_price |+-----------+|      2.20 |+-----------+1 row in set (0.00 sec)

[Example 2] Find the lowest price provided by different suppliers in the fruits table For fruit juice, the SQL statement is as follows:

mysql> select s_id,min(f_price) as min_price    -> from fruits    -> group by s_id;+------+-------------+| s_id | min_price |+------+-------------+|  104 |        6.40 ||  101 |        3.20 ||  103 |        2.20 ||  107 |        3.60 ||  102 |        5.30 ||  105 |        2.60 ||  106 |       15.70 |+------+-------------+7 rows in set (0.00 sec)

Related free learning recommendations: mysql database(Video)

The above is the detailed content of MySQL data query: query using set/aggregation functions. For more information, please follow other related articles on the PHP Chinese website!