Shulou(Shulou.com)06/02 Report--

This article mainly introduces "MySQL database aggregation query and federation query how to achieve", in the daily operation, I believe that many people in the MySQL database aggregation query and federation query how to achieve the problem, the editor consulted all kinds of information, sorted out a simple and easy to use method of operation, hope to answer the "MySQL database aggregation query and federation query how to achieve" the doubt is helpful! Next, please follow the editor to study!

1. Insert the result of the query

Syntax:

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

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

Example 1: insert all the contents of the student1 table (containing only id and name fields, and the table has already inserted content) into the student2 table (containing only id and name fields, which has not yet been inserted)

Example 2: insert the field name of the student1 table (contains only id and name fields, and the table has already inserted content) into the student2 table (contains only id and name fields, which has not yet been inserted)

two。 Aggregate query

2.1 introduction

Aggregate query: a way to query some or all of the data of a field in a data table (that is, a query that merges the dimensions of a row). For example, the average price of all books or the total number of books, etc., in these times will use the aggregate query method.

2.2 aggregate function

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

Add:

When querying only a column for the number of rows, if the value of a row is null, the query result does not calculate the row

When summing the data, the data type must be a number, and neither the string nor the date can be summed

If there is no syntax error, but a runtime error occurs, a warning warnings will be reported. At this point, you can view the warning message through the show warnings SQL statement

Next, show an example of a table named exam_result with specific data as follows

Idnamechinesemathenglish1 Tang Sanzang 67.098.056.02 Sun WuKong 87.578.077.03 Zhu Wuneng 88.098.590.04 Cao Mengde 82.084.067.05 Liu Xuande 55.585.045.06 Sun Quan 70.073.078.57 Song Gongming nullnullnull

Example 1: returns the number of rows of data queried

Example 2: returns the number of rows of column chinese data that have been queried

Example 3: returns the sum of the language scores queried

Example 4: returns the average score of each subject queried.

Example 5: returns the highest value of the total grade

Example 6: returns the lowest value of the total grade

2.3 group by clause

Using the previous aggregate function, you actually combine all the rows in the table. However, you can also use group by for grouping aggregation (add a specified column name after group by, and those with the same value in the column will be grouped into groups)

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

Idnamerolesalary1 Zhang San develops 100002 Li Si develops 110003 Wang Wu Test 90004 Zhao Liu Test 120005 Tianqi sales 70006 Devil King Boss 50000

Example 1: view the average salary for each position

Example 2: view the average, maximum, and minimum salary for each position

2.4 having

If the grouping results need to be conditionally filtered after grouping using the group by clause, you cannot use the where statement, but the having statement

Note:

Where statements are filtered before grouping

Having statements are filtered after grouping

Where statement and having statement can be used at the same time

Example 1: query positions with a salary greater than 10000

Example 2: after excluding Zhang San's salary, the position whose salary is more than 10000

3. Joint query

3.1 introduction

Federated query: a result set that can merge multiple similar selected queries. That is, to do multi-table query, the core idea is to use Cartesian product

Cartesian product thought:

Using the idea of Cartesian product is actually a permutation and combination of the results of two tables, and then we get a new table C from two tables An and B through the idea of Cartesian product.

Student form A:

Student number name class id1 Zhang San 20012 Li Si 20013 Wang Wu 2002

Class Table B:

Class id Class name 2001 Senior two (1) Class 2002 Senior two (2)

New Table C:

Student number class id class id class name 3 20012001 Senior two (1) Class 1 three 20012002 Senior two (2) Class 2 Li Si 20012001 Senior two (1) Class 2 Li Si 20012002 Senior two (2) Class 3 Wang Wu20022001 Senior two (1) Class 3 Wang Wu20022002 Senior two (2) Class

Add:

The result of Cartesian product is still a table.

The number of columns in the table is the sum of the columns in the two tables.

The number of rows in this table is the product of the number of rows in two tables.

With the newly obtained C table, we can connect An and B tables, 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 connecting the two tables, we still need to add some restrictions, such as the class id of tables An and B should be the same, so we can get a more reasonable table D.

New Table D:

Student number name Class id Class id Class name one three 20012001 Senior two (1) Class 2 Li Si 20012001 Senior two (1) Class 3 Wang Wu 20022001 Senior two (2) Class

At this point, we can do a multi-table query.

Note:

Because the Cartesian product is used in the federated query, the number of rows in the new table is the product of the join of all tables. Therefore, the data that uses federated query results may be large and should be used with caution.

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

Drop table if exists classes;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), `room` 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, `class`) values ('Class 1, Grade 2019,' learning computer principles, C and Java languages, data structures and algorithms'), (Class 3, level 2019, 'traditional Chinese Literature'), (Class 5, Automation level 2019, 'Mechanical Automation') Insert into student (sn, name, qq_mail, classes_id) values ('09982,' Black Cyclone Li Kui', 'xuanfeng@qq.com',1), (' 00835'Li Kui, 'null,1), (' 00391''Bai Suzhen', null,1), ('00031''Xu Xian', 'xuxian@qq.com',1), (' 00054'Li Li'I don't want to graduate', null,1), ('51234Zhi' speak well', 'say@qq.com'' 2), ('83223), (' 83223), ('09527) (' foreigners learn Chinese', 'foreigner@qq.com',2) Insert into course (name) values ('Java'), (' Chinese traditional Culture'), ('computer principles'), ('Chinese'), ('higher order Mathematics'), ('English') Insert into score (score, student_id, course_id) values-- Black Cyclone Li Kui (70.5,1,3), (33,1,5), (98,1,6),-Bodhi Laozu (60,2,1), (59.5,2,5),-Bai Suzhen (33,1), (68,3,3), (99,3,5),-Xu Xian (67,4,1), (23,4) 3), (56, 4, 5), (72, 4, 6),-- do not want to graduate (81, 5, 1), (37, 5, 5),-- speak well (56, 6, 2), (43, 6, 4), (79, 6, 6),-- tellme (80, 7, 2), (92, 7, 6) 3.2 Internal connection

Syntax:

Method 1: column names shown by select from Table 1 [Table 1 aliases], Table 2 [Table 2 aliases] where connection conditions; method 2: use the column names shown by [inner] join onselect from Table 1 [Table 1 aliases] [inner] join Table 2 [Table 2 aliases] on connection conditions

Add:

When using a multi-table query, because there are multiple tables, the columns are used as follows: table name. Column name

You can use a table alias to give a table name an alias to use

Use [inner] join on method. If inner is omitted, the internal connection is default.

Example 1: query Xu Xian's grades in each course

Example 2: query each student's total score

Example 3: query the grades of each student in each course

3.3 external connection

External connection: divided into left outer connection and right outer connection. If you use a federated query, the table on the left fully shows that the left outer join is used; the table on the right fully shows that the right outer join is used.

The outer connection is actually similar to the inner connection, using Cartesian product. Inner join is for each piece of data in the two tables is one-to-one correspondence, so why not one-to-one correspondence? For example, the following two tables An and B

Table A:

Idname1 Zhang San 2 Li Si 3 Wang Wu

Table B:

Student_idscore190280470

We find that when the new table is established after Cartesian product, there is no corresponding data between the record with id 3 in table An and table B, and the record with student_id 4 in table B and table A, so the two tables cannot be queried by inner join, but by outer join.

If you use the left join method, the new table C is:

Idnamestudent_ idscore1 Zhang San 1902 Li Si 2803 Wang Wu nullnull

If you use a right join, the new table D is:

Idnamestudent_ idscore1 Zhang San 1902 Li Si 280nullnull470

Add:

When the data in two tables can correspond one to one, the use of outer join and inner join is equivalent.

In addition to the inner connection, the left outer connection and the right outer connection, there is actually a full external connection, but the full external connection operation is not supported in MySQL

Syntax:

-- left join, Table 1 fully shows the column names displayed by select, Table 1 [Table 1 aliases] [left] join Table 2 [Table 2 aliases] on join conditions;-- right connection, Table 2 fully shows the column names displayed by select from Table 1 [Table 1 aliases] [right] join Table 2 [Table 2 aliases] on join conditions

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

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

Student_idcourse_idscore117012901380

If I want to find information about students in the original table whose student_id is 1 and whose scores in course 2 are higher than those in course 3, I want to compare rows to rows, but a table cannot do this.

After carrying on the Cartesian product to yourself, we get a new table B.

Student_idcourse_idscorestudent_idcourse_idscore117011701290129013801380

At this point, we find that if we have two identical tables after Cartesian product of the original table, we can implement row-to-row operations.

Example: query students whose Java scores are higher than those of computer principles.

3.5 subquery

Subquery: refers to select statements embedded in other SQL statements, also known as nested queries

Classification:

Single-row subquery: a subquery that returns a row of records

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

Add:

Multi-row query process with in: when using a subquery, execute the subquery first, store the results of the query in memory, and then execute the outer query to filter according to the results in memory.

Multi-row query process with exists: execute the outer loop first, so that you will get a lot of records, and if you bring it into the subquery for each row, those that meet the conditions will be retained (exists is to check whether the subquery result is an empty collection)

To sum up:

The writing method based on in is fast and suitable for situations where the set of subquery results is relatively small (larger memory cannot fit).

The writing method based on exists is slow and suitable for situations where the set of subquery results is large and the number of outer query results is relatively small.

Example 1: query classmates who do not want to graduate (first know the class of students who do not want to graduate, and then screen students through the class)

Example 2: query grade information for Chinese or English courses (using in)

Example 3: query grade information for Chinese or English courses (using exists)

3.6 merge query

Merge query: the execution result of merging multiple select using the collection operator union or union all. When using a merge query, the results of the front and back query are set, and the fields need to be consistent.

Add:

The union operator does not deduplicate the data of the result set, but union all does.

The function of the collection operator is actually similar to that of the operator or, but if you query against different tables, then or cannot use the

Example: view information with id less than 3 or course Java

At this point, the study on "how to implement aggregate query and federated query in MySQL database" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.