MySQL using notes (eight) statistical functions and packet data logging queries

by Francis_hao Dec 17,2016

Statistical function Data Record query

Statistical functions

Statistical functions	Describe
Count ()	COUNT (*): Number of records in the statistics table (including null value fields) Count (field): Number of records in the statistics table (excluding null value fields)
AVG ()	Calculate the average of field values
SUM ()	Calculate the sum of field values
Max ()	Query field value Maximum value
Min ()	Query field value Minimum value

mysql> Select function (field) [New_name] from table_name where conditions ;

You can take a name for the final field, or you can use the default.

Group Data Record Query

The meaning of grouping is to put together the same data records for a field.

Grouping for statistical functions

The first step is to introduce a function, Group_concat (), which implements the display of the specified field values in each group.

mysql> Select Group_concat (field) from table_name [where condition ] GROUP by field;

The following example shows the values of Row2 in each group when grouped with Row1.

Having clauses qualifying group queries

A display-like control that filters out data records that satisfy a condition.

The following example shows a record with a field data record greater than one in a group that is grouped by the Row1 field Row2.

Note

Take the last example to analyze the order in which SQL statements are executed.

1. Pass data records grouped in Row1 to 2 from table Tab_test

2. Select the required records from the data records received from 1 and pass to 3

3. Display data records from 2 that meet their own conditions

This article was authored by Liu Yinghao and licensed under the Creative Commons Attribution-NonCommercial use-Share 3.0 Chinese mainland license agreement in the same way. Welcome reprint, please specify the source:
Reprinted from: http://www.cnblogs.com/yinghao1991/

Reference

[1] Wang Feifei Triyang He Yaju "MySQL database application from getting started to mastering (second edition)" Beijing: China Railway Press, September 2014

MySQL using notes (eight) statistical functions and packet data logging queries