MySQL database aggregate query and union query operations

1. Insert the queried results

grammar:

insert into the table to be inserted [(column 1, ..., column n)] select {* | (column 1, ..., column n)} from the table to be queried

The above statement can insert some columns of the table to be queried into some corresponding columns of the new table.

2. Aggregate Query

2.1 Introduction

Aggregate query: refers to a method of performing partial or complete statistical query on the data of a field in a data table (that is, a query that merges in the row dimension). For example, the average price of all books or the total number of books, etc., in these cases, the aggregation query method will be used.

2.2 Aggregation Functions

Aggregate queries can use the following common aggregate functions, which are equivalent to the "library functions" provided by SQL:

Replenish:

• When querying the number of rows for a certain column, if the value of a row is null, the query result does not count this row.
• When summing data, the data type must be numeric; strings and dates cannot be summed.
• If there is no syntax error, but a runtime error occurs, a warning message will be displayed. You can view the warning message by using the show warnings SQL statement.

Next, we will use the table named exam_result with the following data as an example.

2.3 group by clause

Using the previous aggregate function actually combines all the rows in the table. However, you can also use group by to perform group aggregation (add a specified column name after group by, and columns with the same value in that column will be grouped together)

Next, we will show an example of a table named emp with the following data

2.4 having

If you need to filter the grouped results by conditions after grouping them using group by clause, you cannot use where clause. Instead, use the having clause.

Notice:

• where statement is used to filter before grouping
• having statement is used to filter after grouping.
• where clause and having clause can be used at the same time

Example 1: Query positions with salary greater than 10,000

3. Joint query

3.1 Introduction

Union query : It can combine the result sets of multiple similar select queries. That is to perform multi-table query, the core idea is to use Cartesian product

Cartesian product idea:

The idea of ​​using Cartesian product is actually to permutate and combine the results of two tables. Next, we use the idea of ​​Cartesian product to get a new table C from two tables A and B.

Student Table A:

Class Table B:

New Table C:

Replenish:

• The result of the Cartesian product is still a table
• The number of columns in this table is the sum of the number of columns in the two tables.
• The number of rows in this table is the product of the number of rows in the two tables.

Through the newly obtained C table, we can link the two tables A and B, and the link in the above example is the class ID. At this point, although the two tables are linked, not every piece of data in the new table is reasonable. For example, the information in row 2 is actually incorrect. Therefore, after linking the two tables, some restrictions need to be added, such as the class IDs of tables A and B should be the same. At this time, a table D with more reasonable data can be obtained.

New Table D:

At this point we can perform a multi-table query

Notice:

Since the joint query uses the Cartesian product, the number of rows in the new table is the product of the union of all tables. Therefore, the data of the joint query result may be very large, so use it with caution.

The following examples are all operated and learned through the table created by the following SQL statement. If you want to operate in the following content, you can directly copy and use

drop table if classes exists;
drop table if exists student;
drop table if exists course;
drop table if exists score;

create table classes (id int primary key auto_increment, name varchar(20), `desc` varchar(100));

create table student (id int primary key auto_increment, sn varchar(20), name varchar(20), qq_mail varchar(20) ,
        classes_id int);

create table course(id int primary key auto_increment, name varchar(20));

create table score(score decimal(3, 1), student_id int, course_id int);

insert into classes(name, `desc`) values 
('Computer Science Department 2019 Class 1', 'Studied computer principles, C and Java languages, data structures and algorithms'),
('Chinese Department 2019 Class 3', 'Studied traditional Chinese literature'),
('Automation 2019 Class 5', 'Studied mechanical automation');

insert into student(sn, name, qq_mail, classes_id) values
('09982','Black Whirlwind Li Kui','[email protected]',1),
('00835','Bodhi Patriarch',null,1),
('00391','白素贞',null,1),
('00031','Xu Xian','[email protected]',1),
('00054','I don't want to graduate',null,1),
('51234','Talk well','[email protected]',2),
('83223','tellme',null,2),
('09527','Foreigners learn Chinese','[email protected]',2);

insert into course(name) values
('Java'),('Traditional Chinese Culture'),('Computer Principles'),('Chinese'),('Advanced Mathematics'),('English');

insert into score(score, student_id, course_id) values
-- Black Whirlwind Li Kui (70.5, 1, 1), (98.5, 1, 3), (33, 1, 5), (98, 1, 6),
-- Bodhi Patriarch (60, 2, 1), (59.5, 2, 5),
-- Bai Suzhen (33, 3, 1), (68, 3, 3), (99, 3, 5),
-- Xu Xian (67, 4, 1), (23, 4, 3), (56, 4, 5), (72, 4, 6),
-- Don't want to graduate (81, 5, 1), (37, 5, 5),
-- Speak nicely (56, 6, 2), (43, 6, 4), (79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);

3.2 Inner Join

grammar:

-- Method 1:
Select the displayed column names from Table 1 [Table 1 alias], Table 2 [Table 2 alias] where the join condition;

-- Method 2: Use [inner] join on
select displayed column name from table 1 [table 1 alias] [inner] join table 2 [table 2 alias] on join condition;

Replenish:

• When using multi-table query, since there are multiple tables, the columns in them are used in the following way: table name.column name
• You can use the table name alias method to give the table an alias
• Use the [inner] join on method. If inner is omitted, an inner join is performed by default.

Example 1: Query the grades of Xu Xian in each course

3.3 Outer Join

Outer join: divided into left outer join and right outer join. If you use a union query, the left table is fully displayed when a left outer join is used; the right table is fully displayed when a right outer join is used.

Outer joins are similar to inner joins in that they both use Cartesian products. The inner join is for each piece of data in the two tables to correspond one to one, so how come it is not a one-to-one correspondence? For example, the following two tables A and B

A table:

Table B:

We found that when the new table is created after the Cartesian product, the record with id 3 in table A has no corresponding data in table B, and the record with student_id 4 in table B has no corresponding data in table A. Therefore, these two tables cannot be queried using the inner join method, and an outer join must be used.

If the left join method is used, the new table C is:

If the right join method is used, the new table D is:

Replenish:

• When the data in the two tables can correspond one to one, using outer joins and inner joins is equivalent.
• In addition to inner join, left outer join, and right outer join, there is also a full outer join, but MySQL does not support full outer join operations.

grammar:

-- Left join, table 1 is fully displayed select displayed column names from table 1 [table 1 alias] [left] join table 2 [table 2 alias] on join condition;

-- Right join, table 2 is fully displayed select displayed column names from table 1 [table 1 alias] [right] join table 2 [table 2 alias] on join condition;

3.4 Self-join

Self-join: refers to joining the same table to itself for query. Using self-join can actually "convert rows into columns" for operation

Why can self-join convert rows into columns for operation? Assume there is a table A

If I want to find the information of students whose student_id is 1 and whose course 2 score is higher than course 3 in the original table, I need to compare rows, but this operation cannot be performed on a single table.

After performing a Cartesian product on itself, we get a new table B

At this point, we find that if we perform a Cartesian product on the original table, we have two identical tables and can perform operations between rows.

Example: Query students whose Java scores are higher than Computer Principles scores

3.5 Subqueries

Subquery: refers to a select statement embedded in other SQL statements, also called a nested query

Classification:

• Single-row subquery: A subquery that returns a single row of records
• Multi-row subquery: A subquery that returns multiple rows (using in or exists)

Replenish:

• Use in to perform multi-row query process: When using a subquery, execute the subquery first, store the query results in memory, and then execute the outer query to filter according to the results in memory.
• Use exists to perform multiple-row query process: First execute the outer loop, so that many records will be obtained, and then bring it into the subquery for each row of records, and retain those that meet the conditions (exists is to detect whether the subquery result is an empty set)

In summary:

Based on the in writing method, it is fast and suitable for situations where the subquery result set is relatively small (large memory cannot hold it)
Based on the exists writing method, the speed is slow and it is suitable for situations where the subquery result set is relatively large and the number of outer query results is relatively small.

Example 1: Query the classmates of the classmate who does not want to graduate (first you need to know the class of the classmate who does not want to graduate, and then filter the students by class)

3.6 Merge Query

Merge query: Use the set operator union or union all to merge the execution results of multiple selects. When using a merge query, the fields in the result sets of the previous and next queries need to be consistent

Replenish:

• union operator does not deduplicate the data in the result set, but union all does.
• The function of the set operator is actually similar to that of the operator or, but if you query different tables, then or cannot be used.

Example: View information for courses with id less than 3 or Java