Mysql nested Query instance detailed _mysql

This article analyzes the MySQL nested query. Share to everyone for your reference, specific as follows:

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

1. Subqueries for SELECT statements

Grammar:

Copy Code code 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 then do the following nested query:

Select Sb1,sb2,sb3
from (select S1 as SB1, S2 as SB2, s3*2 as SB3 from T1) as SB
WHERE sb1 > 1;

The results are: 2, ' 2 ', 4.0.

We know that the following statements do not get the correct result, because the mean value of a set that is sorted by a group by is not the correct answer:

Copy Code code as follows:

SELECT AVG (SUM (column1)) from T1 GROUP by column1

So we can do the same with the following nested query:

Select AVG (sum_column1) from
(select sum (column1) as sum_column1 to
T1 GROUP by column1) as T1;

2. Subquery for row (row subquery)

Look at the following example:

Copy Code code as follows:

SELECT * from T1 WHERE ROW (1,2) = (select Column1, column2 from T2);

This query is the result row that returns Column1 equals Column2. 1 and 2 in the row function are equivalent to the construction parameters. Presumably the comrades on the Blogjava should be more clear about these, do not go to detailed introduction.

3. Use exist and not exist parameters

The use and usage of exist and not exist here are no different from any other, so I'll just give you a few examples:

Example one:

Select DISTINCT store_type from Stores
where EXISTS (SELECT * from Cities_stores
where Cities_stores.store_type = Stores.store_type);

Example two:

Select DISTINCT store_type from Stores
where not EXISTS (SELECT * from Cities_stores
where cities_stores.store_ty PE = stores.store_type);

Example three: This example is nested with the not exist syntax, with a little attention:

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 Relationship Query

Select Column1 from T1 as x
where x.column1 = (select Column1 to T2 as x
where x.column1 = (select Column1 from T3
WHERE x.column2 = t3.column1));

Is the same as other database practices.

5. Other methods of use and attention

In addition to these there are many many, but not to detail, because these are similar to other databases, just to give everyone a reference, the mention is enough.

Select (select S1 from T2) from T1;
Select (select s2 from t1);

The syntax for supporting subqueries is: Select,insert,update,delete,set and do.

Subqueries can use keywords used in any common query: such as Dinstinct,group By,limit,order by,union,all,union all. You can use <,>, <=, >=, =, <> operators to compare, or you can use any, in, and some to match collections.

I hope this article will help you with MySQL database program design.