Analysis of Subquery and Advanced Application in MySql Database
- 2021-12-21 05:21:41
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.