MySQL Learning notes (v)--sub-query and junction

Sub-query:

subqueries, which are queries nested within other queries. For example, we have such a few tables, customer tables, order forms, commodity tables, we want to know which customers have bought a product, then we need to check which orders are included in the product A, and then according to the order to find out which customers.

Mysql> Select cust_id from Orders where Order_num in (select Order_num from orderitems where prod_id = ' 1 ');

+---------+

| cust_id |

+---------+

| 1001 |

| 1002 |

| 1003 |

+---------+

3 rows in Set (0.01 sec)

Subqueries are always from the inside out, in the above SQL is actually performed two operations, first to have a product 1 of the order to find out, and then from these orders to find out the customer number.

In the where subquery can be nested multiple subqueries, can be written through the query of powerful and flexible SQL statements, and there is no limit to the number of sub-queries can be nested, but in peacetime daily use due to performance constraints, can not nest too many sub-queries.

Using subqueries in the WHERE clause, you should ensure that the SELECT statement has the same number of columns as the WHERE clause, typically the subquery will return a single column and match a single column, but you can also use more than one column.

Use a subquery as a calculated field:

Example: You need to display the total number of orders for each customer in the Customers table, and the order is stored in the Orders table with the corresponding customer ID.

Mysql> Select Cust_id,cust_name, (select COUNT (*) from orders where orders.cust_id = customers.cust_id) as orders from C Ustomers ORDER BY cust_id;

+---------+-----------+--------+

| cust_id | Cust_name | Orders |

+---------+-----------+--------+

| 1001 |      Zhang San | 1 |

| 1002 |      John Doe | 2 |

| 1003 |      Harry | 1 |

| 1004 |      Zhao Liu | 2 |

| 1005 |      Lou VII | 5 |

| 1006 |      Wu Eight | 2 |

+---------+-----------+--------+

6 rows in Set (0.00 sec)

The WHERE clause in the subquery is slightly different from the WHERE clause used earlier, select COUNT (*) from orders where orders.cust_id = customers.cust_id, This statement compares the cust_id in the Orders table and the cust_id that are currently being retrieved from the Customers table, a subquery that involves an external query called a correlated subquery. If we do not use this query, that is, the fully qualified column name is not applicable, what will happen.

Mysql> Select Cust_id,cust_name, (select COUNT (*) from orders where cust_id = cust_id) as orders from customers order by cust_id;

+---------+-----------+--------+

| cust_id | Cust_name | Orders |

+---------+-----------+--------+

| 1001 |     Zhang San | 13 |

| 1002 |     John Doe | 13 |

| 1003 |     Harry | 13 |

| 1004 |     Zhao Liu | 13 |

| 1005 |     Lou VII | 13 |

| 1006 |     Wu Eight | 13 |

+---------+-----------+--------+

6 rows in Set (0.00 sec)

This returns the total number of orders for the Orders table, because the cust_id in the two tables are not matched, and MySQL will assume that the cust_id in the Orders table will match itself so that each return is the total number of orders, because each time it is matched, Just as there is no increase in the where condition.

Join table:

First understand the next foreign key, the primary key has been learned before, but a table to distinguish each row of the unique indicator, then what is the foreign key? As can be seen from the above example, in the Customer table cust_id is the primary key of the customers, the Orders table in the primary key is Order_num, in the storage order information, but also stored the customer ID, using this ID can be found in the Customer table of the order customer information, then this Cust _ID is also called the foreign key of orders.

FOREIGN key: The foreign key is a column of a table that contains the primary key value of another table and defines the relationship between the two tables.

To create a join:

Mysql> Select Cust_name,order_num from Orders, customers where customers.cust_id = orders.cust_id order by Cust_name;

+-----------+-----------+

| Cust_name | Order_num |

+-----------+-----------+

|  Wu Eight | 10001012 |

|  Wu Eight | 10001013 |

|  Lou VII | 10001009 |

|  Lou VII | 10001006 |

|  Lou VII | 10001011 |

|  Lou VII | 10001010 |

|  Lou VII | 10001007 |

|  Zhang San | 10001001 |

|  John Doe | 10001003 |

|  John Doe | 10001002 |

|  Harry | 10001004 |

|  Zhao Liu | 10001008 |

|  Zhao Liu | 10001005 |

+-----------+-----------+

Rows in Set (0.00 sec)

Cartesian product: The result returned by a table relationship without a join condition is a Cartesian product. Number of rows retrieved the number of rows in the first table of zombies multiplied by the number of rows in the second table.

Mysql> select Cust_name,order_num from Customers,orders;

+-----------+-----------+

| Cust_name | Order_num |

+-----------+-----------+

|  Zhang San | 10001001 |

|  John Doe | 10001001 |

|  Harry | 10001001 |

|  Zhao Liu | 10001001 |

|  Lou VII | 10001001 |

|  Wu Eight | 10001001 |

|  Zhang San | 10001002 |

|  John Doe | 10001002 |

|  Harry | 10001002 |

|  Zhao Liu | 10001002 |

|  Lou VII | 10001002 |

|  Wu Eight | 10001002 |

|  Zhang San | 10001003 |

|  John Doe | 10001003 |

|  Harry | 10001003 |

|  Zhao Liu | 10001003 |

|  Lou VII | 10001003 |

|  Wu Eight | 10001003 |

|  Zhang San | 10001004 |

|  John Doe | 10001004 |

|  Harry | 10001004 |

|  Zhao Liu | 10001004 |

|  Lou VII | 10001004 |

|  Wu Eight | 10001004 |

|  Zhang San | 10001005 |

|  John Doe | 10001005 |

|  Harry | 10001005 |

|  Zhao Liu | 10001005 |

|  Lou VII | 10001005 |

|  Wu Eight | 10001005 |

|  Zhang San | 10001006 |

|  John Doe | 10001006 |

|  Harry | 10001006 |

|  Zhao Liu | 10001006 |

|  Lou VII | 10001006 |

|  Wu Eight | 10001006 |

|  Zhang San | 10001007 |

|  John Doe | 10001007 |

|  Harry | 10001007 |

|  Zhao Liu | 10001007 |

|  Lou VII | 10001007 |

|  Wu Eight | 10001007 |

|  Zhang San | 10001008 |

|  John Doe | 10001008 |

|  Harry | 10001008 |

|  Zhao Liu | 10001008 |

|  Lou VII | 10001008 |

|  Wu Eight | 10001008 |

|  Zhang San | 10001009 |

|  John Doe | 10001009 |

|  Harry | 10001009 |

|  Zhao Liu | 10001009 |

|  Lou VII | 10001009 |

|  Wu Eight | 10001009 |

|  Zhang San | 10001010 |

|  John Doe | 10001010 |

|  Harry | 10001010 |

|  Zhao Liu | 10001010 |

|  Lou VII | 10001010 |

|  Wu Eight | 10001010 |

|  Zhang San | 10001011 |

|  John Doe | 10001011 |

|  Harry | 10001011 |

|  Zhao Liu | 10001011 |

|  Lou VII | 10001011 |

|  Wu Eight | 10001011 |

|  Zhang San | 10001012 |

|  John Doe | 10001012 |

|  Harry | 10001012 |

|  Zhao Liu | 10001012 |

|  Lou VII | 10001012 |

|  Wu Eight | 10001012 |

|  Zhang San | 10001013 |

|  John Doe | 10001013 |

|  Harry | 10001013 |

|  Zhao Liu | 10001013 |

|  Lou VII | 10001013 |

|  Wu Eight | 10001013 |

+-----------+-----------+

+ Rows in Set (0.00 sec)

Internal coupling: INNER JOIN on

Mysql> Select Cust_name,order_num from orders inner joins customers on customers.cust_id = orders.cust_id ORDER by Cust_ Name

+-----------+-----------+

| Cust_name | Order_num |

+-----------+-----------+

|  Wu Eight | 10001012 |

|  Wu Eight | 10001013 |

|  Lou VII | 10001009 |

|  Lou VII | 10001006 |

|  Lou VII | 10001011 |

|  Lou VII | 10001010 |

|  Lou VII | 10001007 |

|  Zhang San | 10001001 |

|  John Doe | 10001003 |

|  John Doe | 10001002 |

|  Harry | 10001004 |

|  Zhao Liu | 10001008 |

|  Zhao Liu | 10001005 |

+-----------+-----------+

Rows in Set (0.00 sec)

The next chapter is a high-level connection, which requires more detailed and in-depth learning ....

---------------------------------------------

Learning is endless ...

---------------------------------------------

MySQL Learning notes (v)--sub-query and junction