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

This article will explain in detail how to use the set function in MySQL. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

So far, you have only learned how to extract one or more records from the table according to specific conditions. However, if you want to count the records in a table. For example, if you want to count the results of a poll stored in a table. Or you want to know how much time a visitor spends on your site on average. To count any type of data in a table, you need to use aggregate functions. You can count the number of records, average, minimum, maximum, or summation. When you use an aggregate function, it returns only one number, which represents one of these statistics.

The most important feature of these functions is that they are often used with GROUP BY statements, and it should be noted that collection functions cannot be mixed with non-grouped columns.

Row count

Calculate the number of rows of records returned by the query statement

Directly calculate the value of the function COUNT (*), for example, calculate the number of cats in the pet table:

> SELECT count (*) FROM pet WHERE species='cat'

+-+

| | count (*) |

+-+

| | 2 |

+-+

Count the number of field values

For example, calculate the number of species columns in the pet table:

Mysql > SELECT count (species) FROM pet

+-+

| | count (species) |

+-+

| | 9 |

+-+

If the same category appears more than once, the category will be counted multiple times. If you want to know how many pets are of a certain value, you can use the WHERE clause, as shown in the following example:

Mysql > SELECT COUNT (species) FROM pet WHERE species=cat

Notice the result of this statement:

+-+

| | COUNT (species) |

+-+

| | 2 |

+-+

This example returns the number of authors of type cat. If this name appears twice in the table pet, the return value of the secondary function is 2. And it is consistent with the result of the statement mentioned above:

SELECT count (*) FROM pet WHERE species='cat'

In fact, these two statements are equivalent.

If you want to know how many different kinds of pets there are. You can get this number by using the keyword DISTINCT. As shown in the following example:

Mysql > SELECT COUNT (DISTINCT species) FROM pet

+-+

| | COUNT (DISTINCT species) |

+-+

| | 5 |

+-+

If the type cat appears more than once, it will only be calculated once. The keyword DISTINCT determines that only values that differ from each other are calculated.

Usually, when you use COUNT (), the null value in the field is ignored.

In addition, the COUNT () function is usually used in conjunction with the GROUP BY clause, for example, to return the number of pets of each type:

Mysql > SELECT species,count (*) FROM pet GROUP BY species

+-+ +

| | species | count (*) |

+-+ +

| | bird | 2 | |

| | cat | 2 | |

| | dog | 3 | |

| | hamster | 1 | |

| | snake | 1 | |

+-+ +

Calculate the average of a field

The average of these values needs to be calculated. Using the function AVG (), you can return the average of all values in a field.

Suppose you conduct a more complex opinion poll on your site. Visitors can vote between 1 and 10 to show how much they like your site. You save the voting results in an int field named vote. To calculate the average of your user's votes, you need to use the function AVG ():

SELECT AVG (vote) FROM opinion

The return value of this SELECT statement represents the average user's liking of your site. The function AVG () can only be used with numeric fields. This function also ignores null values when calculating averages.

To give another practical example, for example, if we want to calculate the average age of each animal in the pet table, use the AVG () function and the GROUP BY clause:

Mysql > SELECT species,AVG (CURDATE ()-birth) FROM pet GROUP BY species

The returned result is:

+-- +

| | species | AVG (CURDATE ()-birth) |

+-- +

| | bird | 34160 | |

| | cat | 74959.5 | |

| | dog | 112829.66666667 | |

| | hamster | 19890 | |

| | snake | 49791 | |

+-- +

Calculate the sum of field values

Suppose your site is used to sell a product and has been running it for two months. It's time to calculate how much money you've made. Suppose there is a table called orders that records the order information of all visitors. To calculate the sum of all orders, you can use the function SUM ():

SELECT SUM (purchase_amount) FROM orders

The return value of the function SUM () represents the sum of all the values in the field purchase_amount. The data type of the field purchase_amount may be of type DECIMAL, but you can also use the function SUM () for other numeric fields.

Using an inappropriate example, we calculate the sum of the ages of the same kind of pet in the pet table:

Mysql > SELECT species,SUM (CURDATE ()-birth) FROM pet GROUP BY species

You can view the results and compare them with the previous example:

+-- +

| | species | SUM (CURDATE ()-birth) |

+-- +

| | bird | 68320 | |

| | cat | 149919 | |

| | dog | 338489 | |

| | hamster | 19890 | |

| | snake | 49791 | |

+-- +

Calculate the extreme value of a field

Finding the extreme value of a field involves two functions, MAX () and MIN ().

For example, or the pet table, you want to know the earliest animal birth date, because the earliest date is the smallest, so you can use the MIN () function:

Mysql > SELECT MIN (birth) FROM pet

+-+

| | MIN (birth) |

+-+

| | 1989-05-13 | |

+-+

However, you only know the date, or you still don't know which pet it is, and you may want to do this:

SELECT name,MIN (birth) FROM pet

However, this is an incorrect SQL statement because aggregate functions cannot be mixed with non-grouped columns, where name columns are not grouped. So, you can't get the value of the name column and the extreme value of birth at the same time.

The MIN () function can also be used with the GROUP BY clause, for example, to find out the earliest date of birth of each pet:

Mysql > SELECT species,MIN (birth) FROM pet GROUP BY species

The following are satisfactory results:

+-+ +

| | species | MIN (birth) |

+-+ +

| | bird | 1997-12-09 |

| | cat | 1993-02-04 | |

| | dog | 1989-05-13 |

| | hamster | 1999-03-30 |

| | snake | 1996-04-29 | |

+-+ +

On the other hand, if you want to know the most recent date of birth, which is the maximum of the date, you can use the MAX () function, as shown in the following example:

Mysql > SELECT species,MAX (birth) FROM pet GROUP BY species

+-+ +

| | species | MAX (birth) |

+-+ +

| | bird | 1998-09-11 | |

| | cat | 1994-03-17 | |

| | dog | 1990-08-31 | |

| | hamster | 1999-03-30 |

| | snake | 1996-04-29 | |

+-+ +

This is the end of this article on "how to use set functions in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.