MySQL database-summarize and group data

A summary and grouped data

Query statement---> Result set (multiple data)---> Aggregation function----> single row Records

1. Commonly used aggregation functions:

SUM () Number sums all non-null values in the specified column

AVG () number averages all non-null values in the specified column

Min () number, character, DateTime returns the smallest number in the specified column, the earliest date, or the smallest string

Max () number, character, DateTime returns the maximum number in the specified column, the nearest date, or the largest character set

Count () any row-based data type statistic result collection The number of all record rows

Example: Query the number of players in the player's table information

Select COUNT (user_qq) from users

Select COUNT (*) from users

Example: Query QQ number is 12301 of the total number of players in the game

Select SUM (Score) as ' total score ' from scores where user_qq= ' 12301 '

Example: Query QQ number is 12302 player's rating score

Select AVG (Score) as ' average fraction ' from scores where user_qq= ' 12302 '

Example: Query the highest score of game number 1

Select Max (score) as ' highest score ' from score where gno=1

Example: Query QQ number is 12302 of the total number of players, the average score and the highest score

Select SUM (Score) as ' total Score ', AVG (score) as ' average ', max (score) as ' highest score ' from scores where user_qq = ' 12302 '

2. Using GROUP BY grouping

Example: Query each player's total score, the average fraction, the highest score

Select SUM (Score) as ' total Score ', AVG (score) as ' average ', max (score) as ' highest score ' from scores group by USER_QQ

Example: Query the average score of each player and show the player QQ number and average score

Select User_qq, AVG (score) as ' average score ' from scores group by USER_QQ

3. Filter group results

When using the GROUP BY clause, you can use the HAVING clause to further set statistical conditions for grouping statistics, and the relationship between the HAVING clause and the GROUP BY clause is equivalent to the relationship between the WHERE clause and the SELECT clause

The difference from the WHERE clause is that the statistical result of the aggregate function is the filter condition in the HAVING clause.

Example: Query average score greater than 4000 players QQ number, total number, average score

Select User_qq, SUM (score) as ' total scores ', AVG (score) as ' average score ' from scores group by USER_QQ have AVG (score) > 4000

Example: Query all users for average scores, and total scores, and in reverse order of average fractions

Select User_qq,avg (Score) as ' average score ', Sun (score) as ' total number ' from scores group by USER_QQ Orde by AVG (SCORE) desc

Order of execution of 4.SELECT statements

The FROM clause specifies the data source

The Where clause filters records based on the specified criteria

The GROUP BY clause divides the data into multiple groupings

Using aggregate functions for calculations

Filtering groupings using the HAVING clause

To sort the result set by using the ORDER BY clause

Two connection query

1. Multi-table Connection

Example: Query score information, show player nickname, game name and score

Select User_name as ' nickname ', game as ' games name ', score as ' score ' from users.user_qq = scores.user_qq and game.gno= Scores.gno

Connection query is divided into two kinds of inner and outer connections

Internal connection features: two tables connected to equal status

If there is no corresponding data in one table in another table, no connection is made

Multiple table names appear immediately after the FROM clause, which is an inner join, an implicit inner join

Show INNER JOIN format: Select col_list from Table1[inner] join table2 on TABLE1.COL=TABLE2.CLO1

Example: Query score information, show player nickname, game name and score

Select User_name as ' nickname ', G_name as ' game name ', score as ' score ' from games inner join scores on Games.gno =scores.gno

Inner JOIN users on SCORE.USER_QQ=USER.USER_QQ

Example: Query each player's nickname, total score and average

Select User_name as ' nickname ', sum (score) as ' total Score ', AVG (score) as ' average ' from the users U inner join scores S on s.user_qq = U.USER_QQ GROUP BY U.user_qq,user_name

Example: Query score information with average score greater than 3500, show player nickname, total score, average scores, and sort by average score descending

Select User_name as ' nickname ', sum (score) as ' total Score ', AVG (score) as ' average ' from the users U inner join scores S on s.user_qq = U.USER_QQ GROUP BY U.user_qq,user_name have AVG (score) >3500 ORDER by AVG (SCORE) desc

Outer joins divided into left and right outer joins

External connection features: Make a connection to the two table status inequality, which has one of the base tables

Each piece of data in the underlying table must appear, even if no data in the other table matches it, and is null-padded

The left table is the base table when the left outer joins, the right table is the base table when the right table is connected outside.

The table that appears first in the statement is ' left table ', and the table that appears is ' right table '

Outer JOIN format: SELECT col_list from TABLE1 Left/right[outer] joins TABLE2 on TABLE1. Col=table2. COL

Example: Query all players about number 5th game score Information

Select User_name as ' nickname ' Gno as ' game number ', score as ' score ' from the users U left join scores S on U.USER_QQ=S.USER_QQ and s.gno=5

MySQL database-summarize and group data