MySQL Single table query

Aggregation Functions

aggregation : Aggregates the scatter together.
aggregate function : operation on the column, the returned result is a single value, except COUNT, will ignore null value

count: Counts the number of record rows for which the specified column is not null;
SUM: Calculates the value of the specified column and, if the specified column type is not a numeric type, evaluates to 0;
Max: Calculates the maximum value of the specified column, using string sorting operations if the specified column is a string type;
min: Calculates the minimum value of the specified column and, if the specified column is a string type, uses string sorting operations;
AVG: Calculates the average of the specified column, if the specified column type is not a numeric type, the result is 0;

Select  from table name;

For example:
Select avg (age), Min (ages), Max age, sum (aged), count from person #统计人员的平均年龄, Min .,    Max ages, total age, Number of rows that are not empty

Group

meaning of the grouping: classify some data with the same characteristics, such as gender, department, post, etc.

How do you differentiate when you need to group?

Characteristics: Encountered "every" word, generally need to do group operations.

For example: 1. How many people are in each department of the company.

2. How many male employees and how many female employees are there in the company?

#Group query format:SelectFields that are grouped fromtable name Group by Group Field [ having conditional field]PS: A group query can be used in combination with an aggregate function.#Check the average age of the classSelect AVG (age), name fromTB GROUP by class;#Check the average salary for each department and see who the employees in this department are?Select AVG (Salary), Dept,group_concat (name) fromtb1 GROUP by dept;#GROUP_CONCAT (expr): separates the expr strings by commas by grouping them together#search for departments with an average salary greater than 10000, and see who the employees in this department are? Select AVG (Salary), Dept,group_concat (name) fromPerson GROUP by Dept; Having avg (Salary) >10000;#这里的having后面跟条件与where相似, but there are different

where and having difference:
#执行优先级从高到低: where > Group by > have
#1. Where occurs before grouping group by, so there can be any field in the where, but the aggregate function must never be used.
#2. Having a group by, and having the ability to use grouped fields, cannot be taken directly to other fields, you can use aggregate functions

Paging Query

Keyword limit   

Limit (number of starting bars), (number of queries), if only one number after limit, is 0, ( number of queries)

# Query the first 5 data  from the person limit3# Query 3rd through 3rd data from the  person limit 3,6

Execution order of SQL statement keywords

1, the key words commonly used in the query are:

SELECT, from, WHERE, GROUP by, have, ORDER by

Where select and from are required, other keywords are optional.

The parsing order for standard SQL is: From->where->group By->having->select->order by
(1). From field statement to assemble data from different data sources
(2). WHERE table name to filter records based on specified criteria
(3). Group BY Group Field list, dividing data into multiple groupings
(4). Using aggregate functions for calculations
(5). Use the HAVING clause to filter the grouping
(6). Calculate All expressions
(7). Use order by to sort the result set ASC (ascending, default), DESC (Descending)

For example:

In the student's score table, the class for 1 classes of students in accordance with the student's name group, and the screening of the results of the group, a summary of the total number of students to select the overall score of more than 600 students list, and according to the total score from high to low ranking.

Python code

Select sum (score) as from tb1
where class_id=GROUP by namehaving  S>600
ORDER BY

In the example above, the SQL statements are executed in the following order:

① first executes the FROM clause, retrieving records from the TB1 table;

② executes the WHERE clause, filtering out all records class_id=1 in the TB1 table;

③ executes the GROUP BY clause, grouping the TB1 table by the Name column;

④ computes the sum () aggregation function to find the score of each name;

⑤ executes the HAVING clause, filtering out the grouping of sum (score) greater than 600;

⑥ extract Name,sum (Score) two fields, generating a new result set;

⑦ executes the ORDER BY clause, arranging the result set of ⑥ in orderby order of the sum (score) word.

MySQL Single table query