Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use set / aggregate function query in MySQL data query. I hope you will get something after reading this article. Let's discuss it together.

The MySQL aggregate function is as follows:

The function avg () returns the average value of a column count () returns the number of rows of a column max () returns the maximum value of a column min () returns the minimum value of a column sum () returns the sum of the values of a column

(1) count () function

(2) sum () function

(3) avg () function

(4) max () function

(5) min () function

(recommended for free study: mysql video tutorial)

(1) count () function

The count () function counts the total number of rows of records contained in the data table, or returns the number of rows of data contained in the column based on the query results. There are two ways to use it:

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

② count calculates the total number of rows under the specified column, ignoring rows with null values.

[example 1] 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)

As you can see from the query results, count (*) returns the total number of rows recorded in the customers table, regardless of its value. The name of the returned total is cust_num.

[example 2] query the total number of customers with e-mail 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 differences in the results of the above two examples show that the two methods treat null values differently when calculating the total, that is, rows with empty values of the specified column are ignored by the count () function, but if no column is specified and * is used in the count () function, all records are not ignored.

[example 3] in the orderitems table, the count () function is used with the group by keyword to calculate the total number of records in different groups.

Mysql > select select numjijiu 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 summation function that returns the sum of the values of a specified column.

[example] query the total amount of fruits purchased under order 30005 in the orderitems table. The SQL statement is as follows:

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

Sum () can be used with group by to calculate the sum of each packet.

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

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

The sum function ignores rows with a value of null when calculating.

(3) avg () function

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

When the avg () function is used, its argument is the name of the column to be calculated, and 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 suppliers of s_id=103. The SQL statement is as follows:

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

[example 2] in the fruits table, query the average fruit price of each supplier. The SQL statement is as follows:

Mysql > select as avg_price (f_price) as avg_price-> from fruits-> group by s_id +-+-+ | s_id | avg_price | +-+-+ | 7.000000 | 7.000000 | 6.200000 | 6.200000 | 5.700000 | 5.700000 | 3.600000 | 8.933333 | 8.933333 | 7.466667 | 15.700000 | +-+-+ 7 rows in set (0.00 sec)

The group by keyword groups records according to the s_id field, and then calculates the average of each group, which is very useful. For example, to calculate the average scores of students in different classes, the average wages of workers in different departments, the annual average temperature in various places, and so on.

(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 return 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 fruit value with the highest price provided by different suppliers in the fruits table. The SQL statement is as follows:

Mysql > select 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] look for the fruit value with the lowest price 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] look for the lowest-priced fruit juices provided by different suppliers in the fruits table. The SQL statement is as follows:

Mysql > select 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) finished reading this article I believe you have a certain understanding of "how to use set / aggregate function query in MySQL data query". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.