Aggregate functions in MySQL

Create student table IF not EXISTS ' student ' (  ' id ' int (4) unsigned not NULL auto_increment,  ' name ' varchar (12) Not null,  ' sex ' enum (' Male ', ' female ') not null default ' male ',  ' age ' tinyint (4) ' is not null default ' ' ',  ' edu ' enum (' Junior ', ' high Middle ', ' college ', ' Big Ben ', ' postgraduate ') NOT null default ' College ',  ' salary ' float (8,2) unsigned NOT NULL default ' 0.00 ',  ' bonus ' float (6, 2) unsigned NOT NULL default ' 0.00 ',  ' city ' varchar (+) default NULL,  PRIMARY KEY (' id ')) engine=myisam  Defau LT Charset=utf8 auto_increment=55;

　　

Inserting test data insert INTO ' student ' (' id ', ' name ', ' sex ', ' old ', ' edu ', ' Salary ', ' bonus ', ' City ') VALUES (1, ' Primitive Maiden ', ' female ', 24, ' Big Ben ') , 5000.00, 1200.00, ' Shandong Province '), (2, ' Cang teacher ', ' female ', 24, ' College ', 750.00, 700.00, ' Shaanxi Province '), (3, ' tfboy ', ' Male ', 28, ' College ', 8000.00, 800.00, ' Shanghai '), (4, ' Deng Chao ', ' Male ', 25, ' Big Ben ', 4000.00, 800.00, ' Shanxi Province '), (5, ' Anglababy ', ' female ', 21, ' Big Ben ', 6000.00, 2800.00, ' Shandong Province '), (6, ' Hu '), ' Male ', 24, ' Big Ben ', 12300.00, 1580.00, ' Beijing '), (7, ' actor ', ' Male ', 23, ' Big Ben ', 2000.00, 530.00, ' Shaanxi Province '), (8, ' big poplar ', ' male ', 24, ' Big Ben ', 8900. 00, 550.00, ' Hebei province '), (9, ' Shu ', ' female ', 30, ' postgraduate ', 24000.00, 8500.00, ' Heilongjiang Province '), (11, ' Cao Guangyang ', ' Male ', 34, ' Big Ben ', 9500.00, 1200.00, ' Shandong Province ') ), (12, ' Wang Kai ', ' Male ', 29, ' College ', 2000.00, 1400.00, ' Shandong Province '), (13, ' Zhengshuang ', ' Male ', 24, ' Junior College ', 2000.00, 1400.00, ' Henan Province '), (14, ' Missy ', ' Male ', 2 4, ' College ', 2000.00, 1400.00, ' Inner Mongolia '), (15, ' Chen Chen ', ' Male ', 24, ' Big Ben ', 2000.00, 1400.00, ' Yunnan Province '), (16, ' Changjiangpo ', ' Male ', 35, ' College ', 14000.00, 840.00, ' Hunan Province '), (17, ' Wang Zhangming ', ' Male ', 35, ' College ', 14000.00, 840.00, ' Hunan Province '), (18, ' Zhao Jinsheng ', ' Male ', 37, ' Big Ben ', 18000.00, 1440.00, ' Hunan Province '), (1 9, ' Shulin ', ' female ', 26, ' Big Ben ', 8900.00, 1280.00, ' Gansu Province '), (20, ' Du Qingqing ', ' female ', 27, ' College ', 14900.00, 2800.00, ' Jilin Province '), (21, ' Ma June ', ' Male ', 35, ' High school ', 23000.00, 3900. 00, ' Jiangxi Province '), (22, ' Lu Zhi ', ' male ', 24, ' postgraduate ', 2800.00, 1200.00, ' Shandong Province '), (23, ' Lu Nan ', ' female ', 38, ' postgraduate ', 7800.00, 1220.00, ' Beijing '), (24, ' Li Cong ', ' female ', 33, ' postgraduate ', 8800.00, 1320.00, ' Anhui '), (25, ' Li Ghost ', ' female ', 39, ' postgraduate ', 18800.00, 2320.00, ' Anhui '), (26, ' Cheillon ai ', ' female ', 25, ' Big Ben ', 9400.00, 589.00, ' Henan Province '), (27, ' Shei Yiyi ', ' female ', 26, ' High school ', 7000.00, 520.00, ' Hebei province '), (28, ' Ching ', ' Male ', 27, ' Junior ', 6800.00, 580.00, ' Hebei Province '), (29, ' alleges ', ' Male ', 24, ' College ', 58000.00, 2500.00, ' Shaanxi Province '), (30, ' Zhang Zhen ', ' Male ', 35, ' College ', 38000.00, 1700.00, ' Jiangxi Province '), (31, ' Han Zhenguo ') , ' Male ', 24, ' College ', 28000.00, 2700.00, ' Shandong ', (32, ' Han Jiajia ', ' female ', 29, ' Junior College ', 18000.00, 3000.00, ' Hebei province '), (46, ' Primitive Maiden ', ' female ', 24, ' College ') , 5000.00, 1200.00, ' Shandong Province '), (34, ' Han Pengfei ', ' Male ', 24, ' College ', 3000.00, 3700.00, ' Hunan Province '), (45, ' Primitive Maiden ', ' Male ', 24, ' Junior College ', 5000.00, 700.00 , NULL), (Panax Notoginseng, ' Mary ', ' female ', 29, ' College ', 3000.00, 700.00, ' Beijing '), (+, ' Kate ', ' female ', 29, ' College ', 3000.00, 700.00, ' Shanghai '), (36, ' Zhang ', ' Female ', 29, ' College ', 3000.00,800.00, ' Shanghai '), (39, ' Wang Xin Lao ', ' Male ', 35, ' Big Ben ', 7800.00, 956.00, ' Shanxi Province '), (40, ' Yao recruits ', ' male ', 39, ' Junior ', 6700.00, 800.00, ' Henan Province '), (41, ' Jia Yuan ', ' Male ', 28, ' Big Ben ', 8970.00, 845.00, ' Beijing '), (42, ' Jia Dao ', ' Male ', 28, ' College ', 23400.00, 2900.00, ' Beijing '), (43, ' Qi Shu ', ' female ', 30, ' postgraduate ', 24000.00, 8500.00, ' Heilongjiang Province '), (47, ' Primitive Maiden ', ' female ', 24, ' College ', 5000.00, 1200.00, ' Shandong '), (Anglababy, ' The ' Woman ', ' 24 ', ' Graduate ', 6000. 00, 2800.00, ' Shandong Province '), (+, ' anglababy ', ' female ', 24, ' postgraduate ', 6000.00, 2800.00, ' Shandong '), (+, ' anglababy ', ' female ', 24, ' postgraduate ', 6000.00,　 2800.00, ' Shandong Province '), (52, ' Nietzsche ', ' Male ', 24, ' College ', 0.00, 500.00, NULL);

1. Definition: An aggregate function in SQL evaluates a set of values and returns a single value

2. There are mainly:

AVG (): Returns the average of the specified group

COUNT (): Returns the total number of items in the specified group

Max (): Returns the maximum value of the data in the specified group

MIN (): Returns the minimum value of the data in the specified group

SUM (): Returns the data in the specified group and

3. Usage scenarios: Aggregate functions are often used with the GROUP BY clause of a SELECT statement

Example: Find out the information of the highest paying student

Analysis: First find out the highest wage value, and then according to the highest value as a condition to find out the corresponding student information.

First step:select Max (Salary) as highest wage from student;

Step Two: SELECT * from student where salary = (select Max (Salary) as highest wage from student);

Example: The maximum wage, the minimum wage, the average wage and the sum of the wages

　　　　Select Max (Salary) maximum wage, min (salary) minimum wage, AVG (salary) average wage, sum (salary) wage sum from student;

Example: Statistic the total quantity value of a city

Select count (city) from student;

4. Syntax: Select field list from table name [WHERE clause] GROUP BY clause;

Example: Find out how many men and women there are in the student table

Analysis: First, according to sex group, the number of boys, the total number of girls, the final statistics can be, that is, first grouped in the statistics;

Statement: The total number of select Sex,count (*) from student group by sex;

You can group multiple fields at the same time:

Example: To find out the average wage of male and female students in the same city

Analysis: You need to group by city and Sex and then count the average wage for both sexes

Statement: Select City,sex,avg (Salary) from student group by City,sex;

Aggregate functions in MySQL