MySQL multi-table connection query operation instance

In the actual project, there are multiple tables associated with the relationship. It is impossible to retrieve all the data in a single table. If there is no table connection, then we need a lot of action. For example, it is necessary to find the restrictive conditions from table A to retrieve data from table B. Not only need to be divided into multiple tables to operate, but also not high efficiency. For example in the book:

The code is as follows:

SELECT fidfrom t_customerwhere fname= ' MIKE '

This SQL statement returns 2, that is, the FID value of a customer named Mike is 2, so you can retrieve a record of Fcustomerid equals 2 in T_order:

The code is as follows:

SELECT Fnumber,fpricefrom T_orderwhere fcustomerid=2

Let's take a closer look at table joins. There are several different types of table joins, with Cross joins, Inner joins (INNER joins), and outer joins (Outter joins).

(1) Inner connection (INNER join): The INNER join combines two tables, and only obtains data that satisfies the two table join conditions.

The code is as follows:

SELECT O.fid,o.fnumber,o.fprice,c.fid,c.fname,c. Fagefrom t_order o JOIN t_customer cON o.fcustomerid= C.fid

Note: In most database systems, the INNER in the INNER join is optional and the INNER join is the default connection method.

When using a table connection, it is not limited to connecting only two tables, because there are many cases where many tables need to be contacted. For example, the T_order table also needs to connect the T_customer and T_ordertype two tables to retrieve the required information, and write the following SQL statement:

The code is as follows:

SELECT O.fid,o.fnumber,o.fprice,c.fid,c.fname,c. Fagefrom t_order o join T_customer cON o.fcustomerid= c.fidinner JOIN t_ordertypeon t_order.ftypeid= T_OrderType.FId

(2) Cross joins: Cross join all records in the table involved are included in the result set. There are two ways to define a cross-connection, namely, implicit and explicit.

Let's take a look at an implicit example:

The code is as follows:

SELECT T_customer.fid, T_customer.fname, T_customer.fage,t_order.fid, T_order.fnumber, T_order.fpricefrom T_Customer , T_order

Using an explicit connection requires the use of a cross JOIN, as in the following example:

The code is as follows:

SELECT T_customer.fid, T_customer.fname, T_customer.fage,t_order.fid, T_order.fnumber, T_order.fpricefrom T_ Customercross JOIN T_order

(3) Outer join (Outter join): The internal connection only obtains the data that satisfies the connection condition, but for the external connection, the main solution is such a scenario. The data that satisfies the condition is retrieved, there is no doubt that the external connection also retrieves another part of the data, which is to populate the data that does not satisfy the condition with NULL. Let's take a look at the classification of the outer joins: Left OUTER join (OUTER join), right outer join (OUTER join), and full outer join (Fullouter join).

I, left OUTER join: The front also says that data that does not satisfy the condition is filled with null. So specifically what needs to be filled with null, for left outer joins, if the data of the left table that satisfies the condition does not match in the right table, the corresponding right table field needs to be filled with null values. That is, the left outer joins the main body is the left table, the right table to match.

The code is as follows:

SELECT o.fnumber,o.fprice,o.fcustomerid,c.fname,c.fagefrom t_order oleft OUTER JOIN t_customer CON O.fcustomerid=c.fid

Note: If you use a LEFT outer join, you can filter the non-conforming data through the where statement

The code is as follows:

SELECT o.fnumber,o.fprice,o.fcustomerid,c.fname,c.fagefrom t_order oleft OUTER JOIN t_customer CON o.fcustomerid=c. Fidwhere o.fprice>=150

II, right external connection (right OUTER join): The left outer join is opposite to the left Lianbu, and the null value will be filled with the field of the table. That is, the right outer joins the body is the right table, the left table to match.

The code is as follows:

SELECT o.fnumber,o.fprice,o.fcustomerid,c.fname,c.fagefrom t_order oright OUTER JOIN t_customer CON o.fcustomerid=c. FId

Note: As with the left outer join, you can use the Where statement to filter

III, full external connection (Fullouter JOIN): The full outer join is a collection of left and right outer joins. That is, it includes both the result set of the left outer join and the result set of the right outer join.

The code is as follows:

SELECT o.fnumber,o.fprice,o.fcustomerid,c.fname,c.fagefrom t_order ofull OUTER JOIN t_customer CON O.fcustomerid=c.fid

The result is equivalent to:

SELECT o.fnumber,o.fprice,o.fcustomerid,c.fname,c.fagefrom t_order oleft OUTER JOIN t_customer CON o.fcustomerid=c. Fidunionselect o.fnumber,o.fprice,o.fcustomerid,c.fname,c.fagefrom t_order oright OUTER JOIN T_Customer cON o. Fcustomerid=c.fid

=======================================================================================

Multiple table Query SQL notation: (The following is a query from two tables < can also be seen from the three table query, show all the fields in V_goods, display the Name field of the Admin2 tables as the add person, show the table admin2 table Name field as the operator) Queries for multiple tables can be written in the following three examples of SQL

Select V.*, (select A.name from Admin2 a where a.adminid=v.loadinid) as Aname, (select A.name from Admin2 a where a.adminid= V.operatorid) as uname from V_goods v  where 1=1; SELECT v.*,a.name  aname,b.name uname from v_goods v,admin2 a,admin2 b WHERE A.adminid=v.loadinid and B.adminid=v.oper Atorid; SELECT v.*,a.name  aname,b.name uname from V_goods v left joins admin2 A on A.adminid=v.loadinid left joins Admin2 B on B . Adminid=v.operatorid;