The previous section describes the composition and use of select statements.( select statement and the use of where, order by, group by clauses However, relying solely on the words and sentences of select can not give full play to the ability of select. This section will introduce the collocation of select and function.

The test table in this section is the student table, and the script is created as follows:

create table student (
id int not null default 1,
name varchar(20) not null default '',
chinese float not null default 0.0 comment 'grade scores of Chinese',
english float not null default 0.0 comment 'English achievement',
math float not null default 0.0 comment 'Mathematics achievement'
);

insert into student values(1, 'Li Bai', 89,78, 90);
insert into student values(2, 'Tang Seng', 67,98, 56);
insert into student values(3, 'Sun WuKong', 87,78, 77);
insert into student values(4, 'Witch', 88,98, 90);
insert into student values(5, 'Red child', 82,84, 67);
insert into student values(6, 'Buddha', 55,85, 45);
insert into student values(7, 'Bodhisattva', 75,65, 30);

1. count() aggregation function

The count() function returns the number of columns specified.

count(colName)

Use as follows:

-- Statistics on the total number of students
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

count() counts the total number of records in a column, but automatically ignores null because null participates in operations that automatically ignore null.

2. sum() summation function

Sum returns the sum of the specified row data with the following syntax:

select sum(colName) from tableName [where condition];

Examples are as follows:

-- Statistics of the Mathematics Achievements of a Class
mysql> select sum(math) from student;
+-----------+
| sum(math) |
+-----------+
|     455.0 |
+-----------+
1 row in set (0.07 sec)


-- Statistical average score of each subject in a class
-- When operating an alias, as It can be omitted.
mysql> select sum(chinese)/count(*) avg_chinese, sum(math)/count(*) as avg_math, sum(english)/count(*) from student;
+-------------+----------+-----------------------+
| avg_chinese | avg_math | sum(english)/count(*) |
+-------------+----------+-----------------------+
|    77.57143 | 65.00000 |              83.71429 |
+-------------+----------+-----------------------+
1 row in set (0.04 sec)

Note that sum only works with numerical data, and other types of data cannot use sum.

3. Average function of AVG ()

The agv function returns the average value of a column that satisfies where conditions.

-- Statistical average score of each subject in a class
mysql> select avg(chinese), avg(math), avg(english) from student;
+--------------+-----------+--------------+
| avg(chinese) | avg(math) | avg(english) |
+--------------+-----------+--------------+
|     77.57143 |  65.00000 |     83.71429 |
+--------------+-----------+--------------+
1 row in set (0.00 sec)

4. max()/min() extremum function

max()/min() returns a list of maximum / minimum values that satisfy the criteria.

-- Find the highest and lowest grades in a class
mysql> select max(chinese+math+english), min(chinese+math+english) from student;
+---------------------------+---------------------------+
| max(chinese+math+english) | min(chinese+math+english) |
+---------------------------+---------------------------+
|                     276.0 |                     170.0 |
+---------------------------+---------------------------+
1 row in set (0.00 sec)

In different scenarios and requirements, we can use select with different clauses and functions to search data.