🌴 1. Aggregation query
🌲1. Concept
Aggregation query: It is an operation that performs grouping statistics on data in SQL. Multiple rows of data can be combined and calculated according to specific conditions and returns the summary result.
🌲 2. Aggregation query function
| function | illustrate |
| COUNT() | Statistics of row count |
| SUM() | Statistical column sum |
| AVG() | Statistical column average sum |
| MAX() | Find the maximum value |
| MIN() | Find the minimum value |
- Except for the function COUNT(), other things are meaningless if not numbers;
- Except for the function COUNT(), you can perform full-column COUNT(*) queries, but nothing else is possible;
- null does not participate in the query;
- Multiple aggregate functions can be used simultaneously.
Example:
-- Create a student grade sheet
mysql> create table student_grade(
-> id bigint auto_increment primary key,
-> name varchar(20),
-> chinese bigint,
-> math bigint,
-> english bigint);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into student_grade(name,chinese,math,english) values('Zhang San',89,95,65),
-> ('Li Si',96,88,67),('Wang Feizi',78,91,75),('Zhang Liang',99,73,97);
Query OK, 4 rows affected (0.13 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into student_grade(name,chinese,math,english) values('Lili',null,56,89);
Query OK, 1 row affected (0.05 sec)
mysql> select* from student_grade;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | Zhang San | 89 | 95 | 65 |
| 2 | Li Si | 96 | 88 | 67 |
| 3 | Wang Shizi | 78 | 91 | 75 |
| 4 | Zhang Liang | 99 | 73 | 97 |
| 5 | Lili | NULL | 56 | 89 |
+----+-----------+---------+------+---------+
5 rows in set (0.00 sec)COUNT（）
mysql> select* from student_grade; +----+-----------+---------+------+---------+ | id | name | chinese | math | english | +----+-----------+---------+------+---------+ | 1 | Zhang San | 89 | 95 | 65 | | 2 | Li Si | 96 | 88 | 67 | | 3 | Wang Shizi | 78 | 91 | 75 | | 4 | Zhang Liang | 99 | 73 | 97 | | 5 | Lili | NULL | 56 | 89 | +----+-----------+---------+------+---------+ 5 rows in set (0.00 sec) -- Recommended useCOUNT(*)Query mysql> select count(*) from student_grade; +----------+ | count(*) | +----------+ | 5 | +----------+ 1 row in set (0.00 sec) -- certainly，Constants can also be used mysql> select count(1) from student_grade; +----------+ | count(1) | +----------+ | 5 | +----------+ 1 row in set (0.00 sec) -- 可以指定列Query，becausechineseThere isnull，This will not be counted mysql> select count(chinese) from student_grade; +----------------+ | count(chinese) | +----------------+ | 4 | +----------------+ 1 row in set (0.00 sec)
- Generally, COUNT (*) is used to query, and constants can also be used in it. * is more recommended;
- You can also specify column queries;
- When a column contains null, null will not be counted.
SUM（）
mysql> select* from student_grade; +----+-----------+---------+------+---------+ | id | name | chinese | math | english | +----+-----------+---------+------+---------+ | 1 | Zhang San | 89 | 95 | 65 | | 2 | Li Si | 96 | 88 | 67 | | 3 | Wang Shizi | 78 | 91 | 75 | | 4 | Zhang Liang | 99 | 73 | 97 | | 5 | Lili | NULL | 56 | 89 | +----+-----------+---------+------+---------+ 5 rows in set (0.00 sec) -- Query the sum of math scores mysql> select sum(math) from student_grade; +-----------+ | sum(math) | +-----------+ | 403 | +-----------+ 1 row in set (0.03 sec) -- Parameters can use expressions mysql> select sum(math+chinese+english) as total from student_grade; +-------+ | total | +-------+ | 1013 | +-------+ 1 row in set (0.04 sec) -- Query the sum of Chinese scores -- As mentioned beforenullAdding the result to any value isnull，chinesehavenullValue, but the result is notnull -- reason：existsum()When seeking sum，nullNot participating in operations mysql> select sum(chinese) from student_grade; +--------------+ | sum(chinese) | +--------------+ | 362 | +--------------+ 1 row in set (0.00 sec) -- *一般用来取所have列，不能直接用existsun()In the function mysql> select sum(*) from student_grade; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) from student_grade' at line 1
- *You cannot use the sum() function directly, which is generally used to calculate the sum of the values in a certain column, that is, SUM (column name);
- Parameters can use expressions;
- null does not participate in SUM() operation.
AVG（）
-- Check the average Chinese score mysql> select avg(chinese) from student_grade; +--------------+ | avg(chinese) | +--------------+ | 90.5000 | +--------------+ 1 row in set (0.00 sec) -- Query the average math score mysql> select avg(math) from student_grade; +-----------+ | avg(math) | +-----------+ | 80.6000 | +-----------+ 1 row in set (0.00 sec) -- Cannot be used* mysql> select avg(*) from student_grade; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) from student_grade' at line 1
The precautions are similar to SUM()
MAX() and MIN()
mysql> select* from student_grade; +----+-----------+---------+------+---------+ | id | name | chinese | math | english | +----+-----------+---------+------+---------+ | 1 | Zhang San | 89 | 95 | 65 | | 2 | Li Si | 96 | 88 | 67 | | 3 | Wang Shizi | 78 | 91 | 75 | | 4 | Zhang Liang | 99 | 73 | 97 | | 5 | Lili | NULL | 56 | 89 | +----+-----------+---------+------+---------+ 5 rows in set (0.00 sec) -- Check the maximum math score mysql> select max(math) from student_grade; +-----------+ | max(math) | +-----------+ | 95 | +-----------+ 1 row in set (0.04 sec) -- Check the minimum Chinese score mysql> select min(chinese) from student_grade; +--------------+ | min(chinese) | +--------------+ | 78 | +--------------+ 1 row in set (0.00 sec) -- Can query at the same time mysql> select max(chinese),min(chinese) from student_grade; +--------------+--------------+ | max(chinese) | min(chinese) | +--------------+--------------+ | 99 | 78 | +--------------+--------------+ 1 row in set (0.00 sec) -- Use an alias mysql> select max(chinese)Mathematical maximum ,min(chinese)Minimum value for Chinese from student_grade; +-----------------+-----------------+ | Mathematical maximum | Minimum value for Chinese | +-----------------+-----------------+ | 99 | 78 | +-----------------+-----------------+ 1 row in set (0.00 sec)
🌴 2. Group query
🍀 BY clause definition
Definition: GROUP BY is the core clause used in SQL for grouping and aggregation. It is used to group query results according to the values of one or more columns, and to group rows with the same column values into a group. To find data within the same group, you can use aggregate functions (such as COUNT, SUM, MAX, MIN).
grammar
select column1,sum(conumn2),... from table group by column1,colum3;
- column1: The column name of the group;
- sum(column2): There is no grouped column (column of required operations). If you want to display the result, you need to use an aggregate function;
- group by: keywords for group query;
- column1: The column name to group.
Example:
Statistics the number of students in each class
-- Create a student table
mysql> create table students(
-> class_id bigint, -- Student class
-> name varchar(20)); -- Student name
Query OK, 0 rows affected (0.04 sec)
-- insert
mysql> insert into students values(1,'Yang Yang'),(3,'Lili'),(1,'Little Beauty'),(2,'Little Handsome'),(3,'Wang Wu');
Query OK, 5 rows affected (0.04 sec)
Records: 5 Duplicates: 0 Warnings: 0
-- View the student table
mysql> select* from students;
+----------+--------+
| class_id | name |
+----------+--------+
| 1 | Yang Yang |
| 3 | Lili |
| 1 | Little Beauty |
| 2 | Little handsome |
| 3 | Wang Wu |
+----------+--------+
5 rows in set (0.00 sec)
-- Grouping：See how many students there are in each class
mysql> select class_id,count(class_id) as student_count from students group by class_id;
+----------+---------------+
| class_id | student_count |
+----------+---------------+
| 1 | 2 |
| 3 | 2 |
| 2 | 1 |
+----------+---------------+
3 rows in set (0.01 sec)
-- Sort it ascending order by class number
-- group byYou can follow laterorder by
mysql> select class_id,count(class_id) as student_count from students group by class_id order by class_id asc;
+----------+---------------+
| class_id | student_count |
+----------+---------------+
| 1 | 2 |
| 2 | 1 |
| 3 | 2 |
+----------+---------------+
3 rows in set (0.00 sec)🍀 Definition
Definition: Filter the grouping results. After grouping the group by clause, the where statement cannot be used, but HAVING is required.
-- After filtering and grouping, the class number is less than2Class mysql> select class_id,count(class_id) as student_count from students group by class_id having class_id<=2; +----------+---------------+ | class_id | student_count | +----------+---------------+ | 1 | 2 | | 2 | 1 | +----------+---------------+ 2 rows in set (0.00 sec)
having must be used with group by, having to follow group by;
The difference between having and where
| the difference | where | having |
| Object of action | Filter the original data before grouping | Filter the results after grouping |
| Limitations of use | Cannot use aggregate functions | Aggregation functions can be used |
🌴 3. Insert query results
Insert query results: Insert data from one table into another table
🍃 Syntax
insert into table_name [(column1,column2,...)] select column1,colum2,... from another_table
- able_name: The name of the table that is inserted;
- another_table : source table, that is, the table where the data comes from;
- []: means that it can be written or not. If it is written, then column1, column2,... need to be parentheses (), and the number of columns and data types inserted must be the same as the number of columns inserted and the data types.
Example:
Insert the student name from old student table 1 into another table
-- View old table student table1Find information from you：
mysql> select * from students;
+----------+--------+
| class_id | name |
+----------+--------+
| 1 | Yang Yang |
| 3 | Lili |
| 1 | Little Beauty |
| 2 | Little handsome |
| 3 | Wang Wu |
+----------+--------+
5 rows in set (0.02 sec)
-- Create a new table
mysql> create table new_student(
-> id bigint auto_increment primary key,
-> name varchar(20));
Query OK, 0 rows affected (0.09 sec)
-- Copy the student name from the old table to the new table
mysql> insert into new_student (name) select name from students;
Query OK, 5 rows affected (0.08 sec)
Records: 5 Duplicates: 0 Warnings: 0
-- View information in the new table
mysql> select * from new_student;
+----+--------+
| id | name |
+----+--------+
| 1 | Yang Yang |
| 2 | Lili |
| 3 | Little Beauty |
| 4 | Little handsome |
| 5 | Wang Wu |
+----+--------+
5 rows in set (0.00 sec)This is the article about MySQL aggregation query and group query. For more related contents of mysql aggregation query and group query, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!