A Brief Analysis of Subqueries and Advanced Applications in MySql Database

Subquery in MySql database:

Subquery: nesting another select statement in a select query statement. Its main function is to serve as a query condition or determine the data source.

The code example is as follows:

Example 1. Query students older than the average age:

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

Example 2. Query all the class names of the students in the class:

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 a subquery to insert the query results as data into a new table. This is achieved through the keywords create table ... select .... The code is as follows:

create table table name (field name 1, type constraint, ...) select field name from table name where query condition

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

Note: When using this method, if you want to add data to a specified field in the table, you need to give the found field an alias that is the same as the field name in the table.

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

Use a subquery to insert the query results into the table as data. This is done using the keywords insert into ... select .... The code is as follows:

insert into table name (field name 1,...) select field name 1,.. from table name where query condition

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

3. Use the connection to update the data of a field in the table:

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

update table1 join table2 on table1.field = table2.field set table1.field = table2.field

The execution process is to connect the two tables and then set the value of the field in Table 2 to the specified field in Table 1.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.