Shulou(Shulou.com)06/01 Report--

This article mainly shows you the "MySQL aggregation query and federation query operation example analysis", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn the "MySQL aggregation query and federation query operation example analysis" this article.

one。 Aggregate query 1. Aggregate function (count,sum,avg...)

Common operations such as counting totals and calculating flat values can be realized by using aggregate functions. Common aggregate functions are:

Note that where,order by statements can be added at the end, and these aggregate functions will query based on the result set of these statements.

It is best not to add limit after that, because the limit of MySQL is different from the limit of other databases.

When using the aggregate function of statistical columns, avoid writing other columns, otherwise this meaningless display of the first column will occur.

Note:

1.count: you can use count (*), count (0) and count (1). To put it bluntly, it is the same as the whole table of select 1 from. In this count, 0 select 1 is just passed in as a parameter, first select 1, and then count the value of count.

2. Summmamax _ min _ min _ avg cannot be passed into *, but must be passed in fields or expressions to use

3.avg can be used in combination with sum, and aggregate functions can be used together.

2.GROUP BY clause

The GROUP BY clause in SELECT allows you to group queries against specified columns. Need to meet: when using GROUP BY for grouping queries, the fields specified by SELECT must be "grouped by fields", and other fields must be included in the aggregate function if they want to appear in the SELECT.

The aggregate function after Select is performed after Group by executes the grouping.

The essence of Group by statement is grouping, which is often used in conjunction with aggregate queries.

Syntax:

The fields specified by Select must follow the group by, and those that do not exist can only appear in the aggregate function, otherwise there will be a problem.

Considerations for GROUP BY

The essence of 1.Group by statement is grouping, which is often used in conjunction with aggregate queries.

two。 As long as there is an aggregate function, it is possible to group.

3. In grouping operation, the query allows grouping fields and aggregate functions, and other non-grouping fields need to ensure that there are no multiple lines after grouping (for example, students id grouping, query fields can have student names, because after students id grouping, there is only one row)

4. The filter condition is where before grouping and having after grouping (the code is executed sequentially)

Execution order: from > on > join > where > group by > with > having > select > distinct > order by > limit

The first execution starts from from, so aliases are usually named here in from, and you can also name them in select, but select is executed later, and you can't use those places in front of the execution.

Limit is always executed at the end.

5.---select classid,average (score) rows with from student where score > 60 group by classid havinng classid 60, then group them according to classid, and select class in the result.

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.