Detailed description of MySQL nested query instances,

Detailed description of MySQL nested query instances,

This article analyzes MySQL nested queries. We will share this with you for your reference. The details are as follows:

MySQl 4.11 and later versions fully support nested queries. Here are some simple examples of nested queries (the source program is from MySQL User Manual ):

1. subquery of SELECT statement

Syntax:Copy codeThe Code is AS follows: SELECT... FROM (subquery) AS name...

Create a table first:

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);INSERT INTO t1 VALUES (1,'1',1.0);INSERT INTO t1 VALUES (2,'2',2.0);

We can perform the following nested queries:

SELECT sb1,sb2,sb3FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sbWHERE sb1 > 1;

Result: 2, '2', 4.0.

We know that the following statement won't get the correct result, because the average value of the Group by sorted set cannot get the correct answer:
Copy codeThe Code is as follows: select avg (SUM (column1) FROM t1 group by column1

So we can achieve the same effect through the nested query below:

SELECT AVG(sum_column1)FROM (SELECT SUM(column1) AS sum_column1FROM t1 GROUP BY column1) AS t1;

2. Row Subquery)

See the following example:
Copy codeThe Code is as follows: SELECT * FROM t1 where row (1, 2) = (SELECT column1, column2 FROM t2 );

This query returns the result line with column1 equal to column2. The values 1 and 2 in the Row function are equivalent to the constructor parameters. Presumably, the comrades on Blogjava should be clear about this and will not go into details.

3. Use the Exist and Not Exist Parameters

The usage and usage of Exist and Not Exist are similar to those of others. I will just give a few examples:

Example 1:

SELECT DISTINCT store_type FROM StoresWHERE EXISTS (SELECT * FROM Cities_StoresWHERE Cities_Stores.store_type = Stores.store_type);

Example 2:

SELECT DISTINCT store_type FROM StoresWHERE NOT EXISTS (SELECT * FROM Cities_StoresWHERE Cities_Stores.store_type = Stores.store_type);

Example 3: The Not Exist syntax is nested in this example. Pay attention to the following:

SELECT DISTINCT store_type FROM Stores S1WHERE NOT EXISTS (SELECT * FROM Cities WHERE NOT EXISTS (SELECT * FROM Cities_StoresWHERE Cities_Stores.city = Cities.cityAND Cities_Stores.store_type = Stores.store_type));

4. query conditional associations

SELECT column1 FROM t1 AS xWHERE x.column1 = (SELECT column1 FROM t2 AS xWHERE x.column1 = (SELECT column1 FROM t3WHERE x.column2 = t3.column1));

Same as other databases.

5. other usage methods and notes

There are a lot more than above, but I will not elaborate on it, because these are similar to other databases, just to give you a reference, it is enough to mention.

SELECT (SELECT s1 FROM t2) FROM t1;SELECT (SELECT s2 FROM t1);

The syntaxes that support subqueries include SELECT, INSERT, UPDATE, DELETE, SET, and DO.

Subqueries can use any keywords used in common queries, such as DINSTINCT, group by, LIMIT, order by, UNION, ALL, and union all. You can use the <,>, <=, >=, =, <> operator to compare data, or use ANY, IN, or SOME to match a set.

I hope this article will help you design MySQL database programs.

Articles you may be interested in:

• Mysql nested query and Table query optimization methods
• Detailed description of MySql basic queries, connection queries, subqueries, and regular expression queries
• Simple rewriting and optimization of MySQL subqueries
• Optimization Techniques for subqueries in MySQL
• Describes MySQL subquery operations in detail
• Why can't I use indexes in subqueries under mysql5.6.19?
• Example of using limit in Mysql subquery
• How to Use subqueries in MySQL notes
• Subquery instances in MySQL