MySQL Learning (iii)-subqueries (where, from, exists) and connection queries (left JOIN, right join, INNER join, union join)

Similarly, using the goods table to practice subqueries, the table structure is as follows:

All data (cat_id associated with category.cat_id):

Category table:

Mingoods (test when connecting queries)

First, sub-query 1,where sub-query: The result of the inner query as the comparison condition of the outer query

1.1 One item with the largest query ID (implemented using sort + paging)

:mysql> SELECT goods_id,goods_name,shop_price from goods ORDER by goods_id DESC LIMIT 1;

　　

1.2 One item with the largest query ID (implemented using a where subquery)

:mysql> Select Goods_id,goods_name,shop_price from goods WHERE goods_id = (SELECT MAX (goods_id) from goods);

　　

1.3 Querying the item with the largest ID under each category (implemented using a where subquery)

:mysql> Select Goods_id,goods_name,cat_id,shop_price from goods WHERE goods_id in (SELECT MAX (goods_id) from goods Grou P by cat_id);

　　

2, from type sub-query: The query results of the inner layer as a temporary table, for the outer SQL query again. Query result sets can be viewed as tables。 A temporary table will use an alias.

2.1 Querying the item with the largest ID under each category (using the From type subquery)

: mysql > SELECT goods_id,goods_name,cat_id,shop_price from

(SELECT goods_id,goods_name,cat_id,shop_price from goods ORDER by cat_id asc,goods_id DESC) as tmp

GROUP by cat_id;

Subquery isolated result set look at the second graph, you can see that the item ID of the first article in each category is the maximum value under that category. This result set is then used as a temporary table, cleverly using group by to query the first record under each category, which is the largest item ID under each category.

　　

　　

3.exists Sub-query: The result of the outer SQL to get the inner layer of SQL to test, if the inner layer of SQL is established, then the row out. The inner query is the query after exists.

3.1 Remove the category of items under its category from the category table (if there are no items in the category, do not take out), [use where subquery]

:mysql> Select C.cat_id,c.cat_name from category C WHERE c.cat_id in (SELECT g.cat_id from goods g GROUP by g.cat_id);

　　

3.2 Remove the category of items under its category from the category table (if there are no items in the category, do not remove), [use exists subquery]

:mysql> Select C.cat_id,c.cat_name from category C where EXISTS (SELECT 1 from goods g where g.cat_id = c.cat_id); /c0>

　　exists subquery, if the inner query after exists can detect the data, it means that there is;

　　

4. Any, in sub-query

　　

4.1 Use any to isolate categories larger than any one num value.　

The Any keyword must be followed by a comparison operator. The Any keyword means "returns true for any value in the column returned by the subquery if the comparison evaluates to True."

:mysql> Select Cat_id,cat_name from category WHERE cat_id > any (SELECT num from nums);

　　

4.2 using in to isolate cat_id equals num category

:mysql> Select Cat_id,cat_name from category WHERE cat_id in (SELECT num from nums);

　　

The effect of 4.3 in is the same as the effect of =any.

　　

4.4 Using the all query

　　the word all must be followed by a comparison operator . All means "returns true for all values in the column returned by the subquery, if the comparison evaluates to True." ”

　　

4.5 Not in and <> any have the same effect

Not is an alias for <> any, but is an alias for <> all

　　

　　 Sub-query summary: 　　

1. where sub-query: The result of the inner query as the comparison condition of the outer query.

From sub-query: The query results of the inner layer as a temporary table for the outer SQL query again. Query result sets can be viewed as tables, and temporary tables require an alias.

Exists sub-query: The result of the outer SQL to get the inner layer of SQL to test, if the inner layer of SQL is established, then the row out. The inner SQL is the query after exists.

2. Subqueries can also be nested in other subqueries, which can be deeply nested. the subquery must be in parentheses .

3. The main advantages of sub-queries are:

Subqueries allow structured queries so that each part of a statement can be separated.

Some operations require complex unions and associations. Subqueries provide other ways to perform these operations.

　　

4. Any keyword must be followed by a comparison operator . The Any keyword means "returns true for any value in the column returned by the subquery if the comparison evaluates to True."

The word in is an alias of =any, and the effect is the same.

Not is not an alias for <> any, but an alias for <> all.

5. The word all must be followed by a comparison operator. All means "returns true for all values in the column returned by the subquery, if the comparison evaluates to True." ”

6. Refine the subquery

①. Some clauses affect the number and order of rows in a subquery, and limit the number of strips that a subquery can find by adding some restrictions. For example:

SELECT * from T1 WHERE t1.column1 in (SELECT column1 from T2 ORDER by Column1);

　　　　　　SELECT * from T1 WHERE t1.column1 in (SELECT DISTINCT column1 from T2);

SELECT * from T1 WHERE EXISTS (SELECT * from T2 LIMIT 1);

②. Replace the union with a subquery. For example:

Select DISTINCT column1 from T1 WHERE t1.column1 in (SELECT column1 from T2);

Instead of this: SELECT DISTINCT t1.column1 from t1, t2 WHERE t1.column1 = t2.column1;

Second, connection query

Learn the connection query, first understand the "Cartesian product", look at the explanation given by Baidu:

　　

in a database, a table is a collection, and each row is an element in the collection . A joint query between tables is a Cartesian product, such as a table has 5 data, B table has 8 data, if you do not filter, then two table query will have 5 X 8 = 40 data.

First look at the basic information of the test table used: We want to implement the function is to query the product, from the category table to the Product Category name Association query.

Number of rows: Category table 14, Product table 4

　　

Structure: Both the commodity table and the category table have a cat_id

　　

Small category table (contrast when left and right connected)

　　

　　

1.Full multiplication(not full connection, connection query), full multiplication is a Cartesian product

Two tables are all multiplied, is directly from the two table query, from the view of the query, a total of 4 X 14 = 56 Records, these records are the result of the Cartesian product, that is, 22 combinations;

But what we want is each product information display category name only, here found 56 records, of which 52 records are invalid data, full multiplication of the query efficiency is low .

:mysql> SELECT goods_id,goods_name,cat_name from Mingoods,category;

If you have the same field in both tables, make a joint query, distinguish the table name, or you will report an error (ambiguous)

:mysql> SELECT goods_name,cat_id,cat_name from mingoods,category;

　　　　

Add a condition so that two tables are associated with the query so that the product and category one by one correspond. Although there are 4 records found here, but full multiplication is inefficient, full multiplication will generate a very large amount of data in memory (temporary table), because there are a lot of unnecessary data .

　　If a table has 10,000 data, and the other table has 10,000 data, the two tables are all multiplied by 100W data, which is very memory-intensive . Moreover, full multiplication does not make good use of the index, because full multiplication generates a temporary table, the temporary tables are not indexed, greatly reducing the efficiency of the query.

:mysql> SELECT g.goods_name,g.cat_id as g_cat_id, c.cat_id as c_cat_id, c.cat_name from Mingoods G, category C WHERE G. cat_id = c.cat_id;

　　

　　

2. Left join query ... on ...

　　syntax : select a.filed, [A.filed2, .....,] b.filed, [b.filed4 ...,] from <left table> as A left join &L T;right table> as B on <expression>

Suppose there is a, b two tables, the left connection query is a table on the left, B table on the right, A and B table through a relationship to correlate rows, B to match a table.

　　

2.1 Take a look at the condition of on after the constant is true

:mysql> SELECT g.goods_name,g.cat_id, c.cat_id, c.cat_name from Mingoods G left JOIN category C on 1;

As you can see from full multiplication, the total number of records remains the same, or 4 X 14 = 56 records. But this time is the product table does not move, the category table to match, because each time is true, therefore will all the records to find out. left connection, actually can be regarded as the left table is the main table, the right table is from the table .

　　

2.2 To associate a row with two tables according to the CAT_ID

:mysql> SELECT g.goods_name,g.cat_id,c.cat_id,c.cat_name from Mingoods G left joins category C on g.cat_id = C.cat_i D;

Using the LEFT JOIN query achieves the same effect, but there are no other redundant data, fast queries, low memory consumption, and an index is used. Left JOIN query efficiency compared to full multiplication of query efficiency faster than more than more than.

Left JOIN, Mingoods table (left table) does not move, Category table (right table) according to the conditions to a line of matching, although the category table is also read a row of records, and then determine whether CAT_ID is the same as the Mingoods table, but the left connection using the index, Cat_ If the ID is indexed, the query is very fast, so the overall efficiency is much faster than full multiplication, and no index is used for full multiplication.

　　　　

2.3 Check out the product under the fourth category and ask for the product name

:mysql> SELECT g.goods_name,g.cat_id,c.cat_name,g.shop_price from goods G left joins category C on g.cat_id = c.cat_id W Here g.cat_id = 4;

　　

2.4 For LEFT JOIN queries, if there are no rows in the right table that meet the criteria, the default padding is null.

:mysql> SELECT g.goods_name,g.cat_id as g_cat_id, c.cat_id as c_cat_id,c.cat_id from Mingoods G left JOIN mincategory C on g.cat_id = c.cat_id;

　　

3. Right join query ... on ...

Syntax: Select A.field1,a.field2,..., b.field3,b.field4 from <left table> A right join <right table> B on <expression>

The right connection query is similar to the left JOIN query, except that the right connection is based on the right table, and all the data in the right table is queried, and the left table is matched according to the conditions, if the left table does not satisfy the condition, the left side shows null by default. The left and right connections can be interchangeable.

:mysql> SELECT g.goods_name,g.cat_id as g_cat_id, c.cat_id as C_cat_id,c.cat_name from Mingoods g right JOIN Mincatego Ry C on g.cat_id = c.cat_id;

　　

4. Internal connection inner join ... on ...

Syntax: Select A.field1,a.field2,.., b.field3, b.field4 from <left table> A inner join <right table> B on <expression>

Internal connection query, that is, the intersection of the left and right connections, if the two sides can not match the conditions, then do not take out.

:mysql> SELECT g.goods_name,g.cat_id, c.* from Mingoods G INNER joins mincategory c on g.cat_id = c.cat_id;

　　

5. Union Query Union

Syntax: Select A.field1 as F1, a.field2 as F2 from <table1> A Union (select B.field3 as F1, field4 as F2 from <table2& Gt B

Union is the set of two queries. Union merges the result set and does not distinguish which table it is from, so you can merge data that is queried by more than one table.

　　

5.1 Merging two tables of data into queries

:mysql> Select ID, content, user from comment UNION (SELECT ID, msg as content, user from feedback);

　　

5.2 Union query, when column names are inconsistent, align with the column name of the first SQL statement

:mysql> Select ID, content, user from comment UNION (SELECT ID, msg, user from feedback);

　　

5.3 Using a union query filters out duplicate rows

:mysql> Select Content,user from comment UNION (SELECT msg, user from feedback);

　　

5.4 Use UNION ALL to query all, duplicate rows are not filtered

:mysql> Select Content,user from comment UNION All (SELECT msg, user from feedback);

　　

5.5 Union query, if the number of columns is not equal, the column number is reported as unequal

　　

5.6 The result set after union can also be filtered

:mysql> Select Id,content,user from comment UNION all (SELECT ID, msg, user from feedback) ORDER by ID DESC;

　　

　　The order by is not functional in the inner SQL when the Union is queried, because the sorting of the result set found in the Union is meaningless, so the order by ordering of the inner layer is optimized by the MySQL Code Analyzer during execution.

:mysql> (select Id,content,user from comment order by ID DESC) UNION All (select ID, msg, user from feedback order by I D DESC);

　　

　　Order by if used with limit, it makes sense and is not optimized.

Mysql> (SELECT goods_name,cat_id,shop_price from goods WHERE cat_id = 3 ORDER by Shop_price DESC LIMIT 3)
UNION
(SELECT goods_name,cat_id,shop_price from goods WHERE cat_id = 4 ORDER by Shop_price DESC LIMIT 2);

　　

5.7 Practice

:mysql> select Name, SUM (Money) from ((SELECT * from A) UNION all (SELECT * from B)) TMP GROUP by name;

　　

　　

Connection Query Summary:

1. in a database, a table is a collection, and each row is an element in the collection . Connection query is a Cartesian product, such as a table has 1W data, b table has 1W data, then two table query will have 1W X 1W = 100W Bar Data

2. If you have the same field in both tables, make a joint query, distinguish the table name , or you will report an error (ambiguous ambiguous)

3. full multiplication is inefficient, and full multiplication generates a very large amount of data (temporary tables) in memory because there is a lot of unnecessary data .

If a table has 10,000 data, and the other table has 10,000 data, the two tables are all multiplied by 100W data, which is very memory-intensive.

Moreover, full multiplication does not make good use of the index, because full multiplication generates a temporary table, the temporary tables are not indexed, greatly reducing the efficiency of the query .

4. When the left connection query, take left table as the main table, will be the left table all the data query out; Left table does not move, the right table according to the conditions to go to a match, if there is no record to satisfy the condition, then the right return null.

Right connection query value, to the right table is the main table, will be the right table all the data query out, the right table does not move, the left table according to the conditions to match, if the left table does not meet the condition of the row, the left side returns NULL.

The left and right connections are interchangeable: A to join b = = B Right Join a (all in a primary table).

　　　since the left and right connections are interchangeable, for porting compatibility considerations, try to use the right connection.

5. Connection query, although also read a row of records, and then determine whether to meet the conditions, but the connection query using the index, the Condition column index, the query is very fast, so the overall efficiency compared to full multiplication is much faster, full multiplication is not using the index .

with connection queries, queries are fast, memory consumption is small, and indexes are used. Connection query efficiency is more than more than the full multiplication of query efficiency more than more than.

　　

6. Internal connection query, that is, the intersection of the left and right connections, if the two sides can not match the conditions, then do not take out.

7.MySQL does not support external connections , and, accordingly, MySQL can use Union (union query) to isolate the left and right connections.

　　　The union query filters for duplicate rows, and union all does not filter for duplicate rows .

　　　union query, the number of SQL columns between the Union must be equal, the column name is the first SQL column, and the column type can be different, but it doesn't make much sense.

The order by is not functional in the inner SQL when the Union is queried, because the sorting of the result set found in the Union is meaningless, so the order by ordering of the inner layer is optimized by the MySQL Code Analyzer during execution .

However,if the order by is used with limit, it will make sense and will affect the final result set and will not be optimized. Order BY is optimized for selectivity based on whether the result set will eventually be affected.

^_^

MySQL Learning (iii)-subqueries (where, from, exists) and connection queries (left JOIN, right join, INNER join, union join)