Shulou(Shulou.com)05/31 Report--

This article introduces you how to achieve sub-query in the MySql database, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Subqueries in the MySql database:

Subquery: nesting another select statement within a select query statement, its main function is to act as a query condition or to determine a data source.

The code example is as follows:

Example 1. Inquire about students who are older than average:

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

Example 2. Check the names of all 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. Look for 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:

Insert the results of the query into the new table as data using the subquery, using the keyword create table... Select... Implementation, code implementation:

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, to add data to the specified field in the table, you need to give the looked-up field the same alias as the field name in the table.

two。 Add the results of the query to the table:

Insert the results of the query into the table as data using the subquery, using the keyword insert into. Select... Implementation, code implementation:

Insert into table name (field name 1pm...) The name of the select field is 1J.. From table name where query condition

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

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

Update the field data in the table using the join, using 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 on how to achieve sub-query in the MySql database to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.