MySQL summarize and group data

1 Summary and grouping of data

Query statement ---> Result set (multiple pieces of data) ---> Aggregation function --- -> Single row record

1. Commonly used aggregate functions:

sum()                                                                                                                                                                                                    Yes Calculate the average of all non-null values ​​in the specified column

min() Numbers, characters, datetime Returns the smallest number, earliest date or smallest string in the specified column

max() Numbers, characters, datetime Returns the largest number in the specified column, the latest date or the largest character set

count() Any row-based data type Counts the number of all record rows in the result set

Example: Query how many players there are in the player table

select count (user_qq) from users

select count(*) from users

Example: Query the total score of the player whose QQ number is 12301

select sum(score) as 'total score' from scores where user_qq='12301'

Example: Query the QQ number is 12301 12302 Player's evaluation score

select avg(score) as 'average score' 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 the total score, average score and maximum score of the player whose QQ number is 12302

select sum( score) as 'total score', avg(score) as 'average score', max(score) as 'highest score' from scores where user_qq ='12302'

2. Use GROUP BY grouping

Example: Query the total score, average score, and highest score of each player

select sum(score) as 'total score',avg(score) as 'average score' ,max(score) as 'highest score' from scores group by user_qq

Example: Query the average score of each player, and display the player's 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 for group statistics Further set the statistical conditions. 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 in the HAVING clause, the aggregation The statistical results of the function are filter conditions.

Example: Query the QQ number, total score, average score of players whose average score is greater than 4000

select user_qq, sum(score) as'total score', avg(score) as 'average score' ' from scores group by user_qq having avg(score) > 4000

Example: Query the average score and total score of all users, and sort them in reverse order by the average score

select user_qq,avg( score) as 'average score', sun(score) as 'total score' from scores group by user_qq orde by avg(score) desc

4. Execution order of SELECT statement

The from clause specifies the data source

The where clause filters records based on specified conditions

The group by clause divides the data into multiple groups

Use aggregate functions for calculation

Use the having clause to filter grouping

Use the order by clause to sort the result set

Second connection query1. Multi-table connection

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

select user_name as 'Nickname', game as 'game name', score as 'score' from users.user_qq = scores.user_qq and game.gno= scores.gno

Connection queries are divided into two types: inner joins and outer joins

Features of inner joins: The two connected tables have equal status

If there is no corresponding data in one table in the other table, the connection will not be made

Directly after the from clause If multiple table names appear, this connection method is an inner join and an implicit inner join.

Display the inner join format: select col_list from table1[inner] join table2 on table1.col=table2.clo1

Example: Query score information, display the player's nickname, the game name and score

stelect user_name as' nickname ', g_name as' game name', score as' from game inner join scores on games .gno =scores.gno

inner join users on score.user_qq=user.user_qq

Example: Check the nickname of each player, the total score and equalization score

SELECT User_name as' Nickname ', SUM (SCORE) as' Total Society', AVG (SCORE) AS 'From Users u inner join scores s on s.User_qq = U.USER_QQ Group by U.USER_QQ, User_Name

## ：: Query the score information with an average score of more than 3500, shows the player's nickname, total score, average score, and follow according to The average scores are arranged

# SELECT User_Name as 'Nickname', Sum (SCORE) as 'Total Society', AVG (Score) as 'Average' FROM USERS U Inner Join SCORES S on S.USER_QQ = U. group by user_qq The status of the two connected tables is unequal. The base table of one of them

Every piece of data in the base table must appear. Even if there is no data matching it in the other table, it must be filled with NULL

The left table is the basic table when the left outside connection, and the right table on the right is the basis of the basics in the basis of the basics of the "left table" in the foundation table of

.

Outer join format: SELECT COL_LIST FROM TABLE1 LEFT/RIGHT[OUTER] JOIN TABLE2 ON TABLE1.COL=TABLE2.COL

Example: Query the score information of all players on game No. 5

select user_name as'nickname' gno as 'game number', score as 'score' from users U left join scores S on U.user_qq=S.user_qq and S.gno=5

The above is the detailed content of MySQL summarize and group data. For more information, please follow other related articles on the PHP Chinese website!