Multi table relationship

One to many relationship

concept

The one to many relationship means that one row of data in the master table can correspond to multiple rows of data in the slave table at the same time. In turn, it means that multiple rows of data in the slave table point to the same row of data in the master table

Application scenario

Classification table and commodity table, class table and student table, user table and order table, etc

Table building principle

Take one party of one as the master table and the other party as the slave table. Specify a field in the slave table as the foreign key to point to the primary key of the master table

Many to many relationship

concept

Both tables are one of many tables. One row of data in table A can correspond to multiple rows of data in table B at the same time. Conversely, one row of data in table B can also correspond to multiple rows of data in table A at the same time

Application scenario

Order form and commodity form, student form and course form, etc

Table building principle

Because the two tables are one of many parties, foreign keys cannot be created in both tables. Therefore, it is necessary to create a new intermediate table and define two fields in the intermediate table, which are used as foreign keys to point to the primary keys of the two tables

One to many relationships can be displayed in the schema designer

One to one relationship

The first one-to-one relationship

We have learned the one to many relationship before. In the one to many relationship, one row of data in the master table can correspond to multiple rows of data in the slave table. On the contrary, one row of data in the slave table can only correspond to one row of data in the master table. This relationship of one row of data corresponding to one row of data can be regarded as a one-to-one relationship

The second one-to-one relationship

A row of data in table a corresponds to a row of data in table B. conversely, a row of data in table B also corresponds to a row of data in table A. at this time, we can use table a as the master table, table B as the slave table, or table B as the master table, and table a as the slave table

Table building principle

Specify a field in the slave table, create a foreign key and point to the primary key of the master table, and then add a unique constraint to the foreign key field of the slave table

Multi table Association query

Multi table Association query uses one SQL statement to query the data of multiple associated tables

Cross query

In fact, cross query is to display the data of multiple tables unconditionally (according to Cartesian product)

grammar

select a.column,a.column,b.column,b.column from a,b ;  

select a.*,b.* from a,b ;  
--perhaps 
select * from a,b;

Through the query results, we can see that cross query is actually a wrong practice. There are a large number of wrong data in the query result set. We call the cross query result set Cartesian product

Cartesian product

Assuming set A={a,b}, set B={0,1,2}, the Cartesian product of two sets is {(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}. It can be extended to multiple sets.

Internal connection query

Cross query is not what we want, so how to remove the wrong and unwanted records? Of course, it is through conditional filtering. Usually, there is an association relationship between multiple tables to be queried, so the Cartesian product is removed through the association relationship (primary foreign key relationship). This kind of query that removes Cartesian product through conditional filtering is called join query. Connection query can be divided into internal connection query and external connection query

Implicit join query

There is no inner join keyword in the implicit inner join query

select [field,field,field] from a,b where Connection conditions (b Foreign keys in the table = a Primary key in the table ) 

Which table displays which before

Explicit inner join query

There is an inner join keyword in the explicit inner join query

select [field,field,field] from a [inner] join b on Connection conditions [ where Other conditions]

Internal connection query exercise

Query the commodity information under all categories. If there are no commodities under this category, they will not be displayed

-- 2.1 Implicit inner connection 
select *from t_category  c, t_product  p WHERE c.cid = p.cno;

-- Query the information and classification information of all products under the category of mobile phone digital
SELECT * FROM t_product tp INNER JOIN t_category tc ON tp.cno = tc.cid WHERE tc.cname = 'Mobile digital';

-- 2.2 Display internal connection mode  
SELECT * from t_category c INNER JOIN t_product p ON c.cid = p.cno

Characteristics of internal connection query

The data in the master table and the slave table can be queried if they meet the connection conditions, and will not be queried if they do not meet the connection conditions

External connection query

We found that the internal connection query is the public part that meets the connection conditions. If you want to ensure that all the data of a table is queried, you can perform the connection query Then you need to use external connection query The external connection is divided into left external connection and right external connection

Left outer connection query

concept

Take the table on the left of the join as the main table to display all the data of the main table. Query and connect the data of the table on the right according to the conditions. If the conditions are met, it will be displayed; if not, it will be displayed as null. It can be understood as: on the basis of internal connection, ensure that all the data in the left table are displayed

grammar

select field from a left [outer] join b on condition

Right outer connection query

concept

Take the table on the right of the join as the main table to display all the data of the table on the right. Query the data of the table on the left of the join according to the conditions. If it is satisfied, it will be displayed; if it is not satisfied, it will be displayed as null. It can be understood as: on the basis of internal connection, ensure that all the data in the right table are displayed

grammar

select field from a right [outer] join b on condition

union joint query to realize all external connection query

First of all, it should be clear that joint query is not a way of multi table join query. Joint query is to combine the query results of multiple query statements into one result and remove duplicate data.

All external connection query means to query the data of the left table and the right table, and then connect according to the connection conditions

Syntax of union

Query statement 1 union Query statement 2 union Query statement 3 ...

Self join query

Self join query is a special multi table join query, because the tables of two associated queries are the same table, which is virtualized into two tables by taking aliases, and then the join query of the two tables is carried out

Subquery

If a query statement is nested in another query statement, the query statement is called a sub query. According to different locations, it can be divided into: where type, from type and exists type. Note: no matter where the subquery is, the subquery must be enclosed with ().

where type

① If the subquery is a single valued result, you can use (=, > and other comparison operators) for it

# Query the commodity information with the highest price
select * from t_product where price = (select max(price) from t_product)

② If a subquery is a multi valued result, you can use it ([not] in, or > all, or > = all, < all, < = all, or > any, or > = any, < any, < = any)

# Query the commodity information with the highest price
SELECT * FROM t_product WHERE price >=ALL(SELECT price FROM t_product)

from type

The result of subquery is the result of multiple rows and columns, which is similar to a table.

The sub query must be aliased, that is, the name of the temporary table. The alias of the table should not be added with "" and spaces.

-- Train of thought 1: Use join query
-- Use the external connection to query all the data of the classification table
SELECT tc.cname,COUNT(tp.pid) FROM t_category tc LEFT JOIN t_product tp ON tp.cno = tc.cid GROUP BY tc.cname

-- Train of thought II: Use subquery
-- First step:yes t_product according to cno Group query and count the quantity of goods in each category
SELECT cno,COUNT(pid) FROM t_product GROUP BY cno
-- Step two: use t_category Table to connect the results of the first step of the query, and perform the connection query,At this time, it is required to query all classifications
SELECT tc.cname,IFNULL(tn.total,0) 'Total quantity' FROM t_category tc LEFT JOIN (SELECT cno,COUNT(pid) total FROM t_product GROUP BY cno) tn ON tn.cno=tc.cid

exists type

# Query the categories of goods
SELECT cid,cname FROM t_category tc WHERE EXISTS (SELECT * FROM t_product tp WHERE tp.cno = tc.cid);