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

This article mainly explains "how to use MySQL sub-query". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to use MySQL sub-query".

Select statements that appear in other statements are called subqueries or inner queries; external queries are called master queries or outer queries.

-- subquery-- the condition of the query comes from the result of another query SELECT * FROM t_user WHERE number= (SELECT number FROM t_user WHERE NAME=' Zhang San')

Of course, there are also types of subqueries, which are divided into the following categories:

Scalar quantum query (result set has only one row and one column)

Column subquery (result set has only one column with multiple rows)

Row subquery (result set has one row with multiple columns) (less)

Table subquery (result set is usually multi-row and multi-column)

Here we take the new t_user table as an example.

It should be noted that the classification here is divided according to the results of the embedded subquery, for example, the above sql statement is a scalar quantum query.

The result of a subquery has only one row and one column

Then we discuss based on where the subquery appears in the sql statement:

After select: only scalar quantum queries are supported

After the select statement, take the result of T1 query as the condition SELECT t1.number of the subquery, (SELECT NAME FROM t_user T2 WHERE t1.name = t2.name) FROM t_user T1

Query results:

After insert into, update and delete:

Insert into is used to insert data into a table, so it can be followed by column subqueries and table subqueries

-- insert into followed by a subquery INSERT INTO t_user (number,NAME,age,birthday,weight,sex,opertime) SELECT number,NAME,age,birthday,weight,sex,NOW () FROM t_user WHERE id=3

What needs to be noted here are update and delete.

UPDATE t_user SET NAME='abc' WHERE number= (SELECT number FROM t_user WHERE weight=110)

DELETE FROM t_user WHERE id= (SELECT id FROM t_user WHERE id=7)

The above two sql, mysql does not allow us to implement in this way:

The underlying mechanism of mysql makes it impossible for us to change the table structure when we operate on the table.

That is, a subquery cannot query the table that is currently being operated on.

After where: scalable quantum query, column subquery, row subquery

-- Quantum query followed by where-- search for information on the person with the heaviest weight SELECT * FROM t_user WHERE weight= (SELECT MAX (weight) FROM t_user)-- where followed by a list of sub-queries-- query for information on people whose weight is greater than or equal to 30,130 (SELECT weight FROM t_user WHERE weight > = 130)-- where followed by sub-query-- query the information of the oldest and heaviest person SELECT * FROM t_user WHERE (age). Weight) = (SELECT MAX (age), MAX (weight) FROM t_user)

After from: you can follow the table for subquery

The result of a table word query is multiple rows and columns, that is, a table

-- Table subquery SELECT t.age FROM (SELECT age,weight FROM t_user) t

Results:

Thank you for your reading, the above is the content of "how to use MySQL sub-query". After the study of this article, I believe you have a deeper understanding of how to use MySQL sub-query, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.