How to use mysql subquery

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

-- 子查询
-- 查询的条件来自于另一查询的结果
SELECT * FROM t_user WHERE number=(SELECT number FROM t_user WHERE NAME='张三')

Of course, subqueries also have types, which are divided into the following types Type:

• scalar subquery (the result set has only one row and one column)

• column subquery (the result set has only one column and multiple rows)

• Row subquery (the result set has one row and multiple columns) (less)

• Table subquery (the result set usually has multiple rows and multiple columns)

Here we take the new t_user table as an example

It should be noted that the classification here is based on the results of the embedded subquery For example, the above sql statement is a scalar subquery

The subquery result has only one row and one column

Then we base the position of the subquery in the sql statement Let’s discuss:

After select: Only scalar subqueries are supported

-- select语句后面,将t1查询出的结果作为子查询的条件
SELECT t1.number,
  (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 the table, so it can be followed by column subquery and table subquery

-- insert into 后跟子查询
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 you need to pay attention to here is 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)

Mysql does not allow us to execute the above two SQLs in this way:

The underlying mechanism of mysql prevents us from operating this table. Changing the structure of this table

In other words, the subquery cannot query the table currently being operated on

where can be followed by scalar subquery, column subquery, and row subquery

-- where后跟标量子查询
-- 查询体重最大的人信息
SELECT * FROM t_user WHERE weight=(SELECT MAX(weight) FROM t_user)
-- where后跟列子查询
-- 查询体重大于或等于130的人信息
SELECT * FROM t_user WHERE weight IN (SELECT weight FROM t_user WHERE weight>=130)
-- where后跟行子查询
-- 查询年龄最大,体重最大的人的信息
SELECT * FROM t_user 
     WHERE (age,weight) = (SELECT MAX(age),MAX(weight) FROM t_user)

from Behind: Can be followed by table subquery

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

-- 表子查询
SELECT t.age FROM (SELECT age,weight FROM t_user)t

Result:

The above is the detailed content of How to use mysql subquery. For more information, please follow other related articles on the PHP Chinese website!