count,sum,avg,max,min__mysql学习笔记

函数

Count

Sum

Max

Min

Avg

 唔，我开始看原版的了

1,count

Used tocount rows or valudes of a column that do not contain a NULL value;

Could beused with the DISTINCT;

All(opposite of DISTINCT) is the default;

Plus:count (*) counts all the rows of a table includingduplicates, whether a NULL value iscontained in a column or not ;

 select Count [(*) | (DISTINCT | ALL)] (COLUMN NAME) from table_name;

Attention:COUNT is used to count rows, not values!!!

 

Count(salary) from table_name;

Count (all salary) from table_name;

Count(*) from table_name;//If we use *,don't write column_name!!!

ANY datatype is OK;

 

2,sum

Returns atotal on the values of a column for a group of rows;

Can beused with distinct,only the distinct rows are totaled;

Select sum([DISTINCT]COLUMN_NAME) FROM TABLE_NAME;

The valudemust be numeric, or there must be somethingwrong;

 

3,avg

SELECT AVG ([DISTINCT]COLUMN_NAME) FROM TABLE_NAME;

Selectavg(salary),avg(money) from table_name;//it's ok;

Attention:insome implementions,the results of your query might be truncatedto the precision of the data type;

 

4,max,min

Return themaximum value from the values of a column in a group of rows;

NULL  values are ignored for both max and min;

Distinctcommand is an option but useless;

Select max([distinct]column_name) from table_name;

MAX andMIN can also be used on character datad.Most commonly your database collationis set to a dictionary order ,so the results arerankedaccording to that.

 

Attention:whenusing aggregate funcitons with the DISTINCTcommand ,our query might not return the desired result;

 

Wecan use them in this way:

Selectcount(C_1),sum(C_2),sum(C_3)/count (C_4) c_4

Fromtable_name;//c_4 is a column alias for the computation.

Itshows:

Count(C_1)   sum(C_2)   c_4

XXX                       XXX        XXXx