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

Today, I will talk to you about how to implement sub-query in mysql. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

MySQl has fully supported nested queries since version 4.11, so here are some examples of simple nested queries (the source program is from MySQL User Manual):

1. Subquery of SELECT statement

Syntax: SELECT... FROM (subquery) AS name...

Create a table first:

CREATE TABLE T1 (S1 INT, S2 CHAR (5), S3 FLOAT)

INSERT INTO T1 VALUES

INSERT INTO T1 VALUES (2)

We can then make the following nested queries:

SELECT sb1,sb2,sb3

FROM (SELECT S1 AS sb1, S2 AS sb2, s3x2 AS sb3 FROM T1) AS sb

WHERE sb1 > 1

The result is: 2,'2, 4.0.

We know that the following statement will not get the correct result, because averaging a set sorted by Group by will not get the correct answer:

SELECT AVG (SUM (column1)) FROM T1 GROUP BY column1

So we can achieve the same effect with the following nested query:

SELECT AVG (sum_column1)

FROM (SELECT SUM (column1) AS sum_column1

FROM T1 GROUP BY column1) AS T1

two。 Subquery of rows (Row Subquery)

Look at the following example:

SELECT * FROM T1 WHERE ROW (1jue 2) = (SELECT column1, column2 FROM T2)

This query returns the result row where column1 equals column2. 1 and 2 in the Row function are equivalent to construction parameters. Presumably the comrades on Blogjava should be more aware of these and will not introduce them in detail.

3. Use the Exist and Not Exist parameters

The uses and uses of Exist and Not Exist here are not much different from those of others, so I'll just give you a few examples:

Example 1: SELECT DISTINCT store_type FROM Stores

WHERE EXISTS (SELECT * FROM Cities_Stores

WHERE Cities_Stores.store_type = Stores.store_type)

Example 2: SELECT DISTINCT store_type FROM Stores

WHERE NOT EXISTS (SELECT * FROM Cities_Stores

WHERE Cities_Stores.store_type = Stores.store_type)

Example 3: the Not Exist syntax is nested in this example, so pay a little attention to:

SELECT DISTINCT store_type FROM Stores S1

WHERE NOT EXISTS (

SELECT * FROM Cities WHERE NOT EXISTS (

SELECT * FROM Cities_Stores

WHERE Cities_Stores.city = Cities.city

AND Cities_Stores.store_type = Stores.store_type))

4. Conditional association relation query

SELECT column1 FROM t1 AS x

WHERE x.column1 = (SELECT column1 FROM T2 AS x

WHERE x.column1 = (SELECT column1 FROM T3)

WHERE x.column2 = t3.column1))

It's the same as other databases.

5. Other usage and attention

In addition to the above, there are many, but do not go into details, because these are similar to other databases, just to give you a reference, mention is enough.

SELECT (SELECT S1 FROM T2) FROM T1

SELECT (SELECT S2 FROM T1)

The syntax that supports subqueries are: SELECT,INSERT,UPDATE,DELETE,SET and DO.

Subqueries can use the keywords used in any ordinary query: such as DINSTINCT,GROUP BY,LIMIT,ORDER BY,UNION,ALL,UNION ALL, etc. You can compare using the =, =, operator, or you can use ANY, IN, and SOME to match collections.

After reading the above, do you have any further understanding of how to implement subqueries in mysql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.