Analysis of Subquery and Advanced Application in MySql Database

Subqueries in the MySql database:

Subquery: Nesting another select statement within one select query statement, whose main function is to act as a query condition or determine the data source.

The code case is as follows:

Example 1. Query students who are older than the average age:

select * from students where age > (select avg(age) from students);

Example 2. Query all class names of students in their classes:

select name from classes where id in (select cls_id from students where cls_id is not null);

Example 3. Find the oldest and tallest student:

select * from students where (age, height) = (select max(age), max(height) from students);

Advanced applications of MySql:

1. Add the queried data to a new table:

Use subquery to insert the query results as data into the new table, through the keyword create table... select... implementation, code implementation:

create table  Table name ( Field name 1,  Type   Constraints, ...) select  Field name  from  Table name  where  Query criteria 

Its execution process is to execute select statement to determine the data source through where condition, and then insert the queried data into the newly created table.

Note: When using this method, to add data to a specified field in a table, you need to alias the found field with the same name as the field in the table.

2. Add the results of the query to the table:

Use subquery to insert the query results into the table as data, through the keyword insert into... select..., code implementation:

insert into  Table name ( Field name 1,...) select  Field name 1,.. from  Table name  where  Query criteria 

The execution process is that the select statement is executed first, and the specified data is screened out through the where condition, and then the insert into statement is executed to add data to the specified field name.

3. Update data for a field in a table using a join:

Use the join to update the field data in the table, through the keyword update... join... keyword implementation, code implementation:

update  Table 1 join  Table 2 on  Table 1. Field  =  Table 2. Field  set  Table 1. Field  =  Table 2. Field 

The execution process is to join the two tables and set the values of the fields in Table 2 to the specified fields in Table 1.

Summarize