MySQL Connection query Introduction

Scene:

Get a list of students from the table below. Requirements are, show: Student, gender, class name

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M00/76/C3/wKiom1Zb972RB7IiAABtL25HyKM619.jpg "title=" 01.jpg "alt=" Wkiom1zb972rb7iiaabtl25hykm619.jpg "/>

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M01/76/C3/wKioL1ZcAhDSKHSmAAAq76U2yhc343.jpg "title=" 01.jpg "alt=" Wkiol1zcahdskhsmaaaq76u2yhc343.jpg "/>

This query needs to get data not only from the student table, but also from the class table:

Connection query: Join

Connection: The connection between multiple table records!

Syntax: From table name 1 join table Name 2 on join condition

Select Stu_name,gender,class_id,class_name from select_student join Select_class on Select_student.class_id=select_ Class.id;

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M02/76/C4/wKiom1ZcAo2C51kOAAB1TPd6uVo770.jpg "title=" 01.jpg "alt=" Wkiom1zcao2c51koaab1tpd6uvo770.jpg "/>

The procedure is to first execute the FROM clause, which requires a join

Two special places:

Join, Connect

On, connection conditions

Join Connection:

In addition to the default connection, there are other forms of connection:

Internal connection

Outer connection, left outer connection, right outer connection, [full outer connection, also outside connection, but not supported by MySQL]

Cross Connect

Natural connection

• Internal connection: INNER JOIN

Record the connection to the real record, called the Inner connection! (Two connections of the actual existing record)

MySQL default connection is inner join

Select Stu_name,gender,class_id,class_name from select_student join Select_class on Select_student.class_id=select_ Class.id;

Select Stu_name,gender,class_id,class_name from Select_student inner joins Select_class on select_student.class_id= Select_class.id;

The above two syntax execution results are as finished!

The inner join can omit the condition:

On can be omitted: the equivalent of the connection conditions always set! The return value is a Cartesian product!

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M00/76/C4/wKioL1ZcCGng2k65AACS-Szy9Oc449.jpg "title=" 01.jpg "alt=" Wkiol1zccgng2k65aacs-szy9oc449.jpg "/>

• Outer joins: Left joins, right join

Divided into: Left outer connection, right outer connection!

The record of the connection may not exist on one side! (Two records, may not exist)

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M00/76/C5/wKiom1ZcCKTg4tYyAAB9XAf4zHc746.jpg "title=" 01.jpg "alt=" Wkiom1zccktg4tyyaab9xaf4zhc746.jpg "/>

Note the difference between left and right outside:

The difference is that the record of which table (refers to a record of failed connections) will eventually appear in the result of the connection!

What is the left table and the right table? The left table in front of the Join keyword (left), the Join keyword (right), right side table!

Left outer: If the left table record is not connected to the right table record, the left table record will appear in the final connection result! And the right table record is set to NULL as appropriate

Right outside: If the right table record is not connected to the left table record, the right table record will appear in the final connection result! And the left table record is set to NULL as appropriate

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M02/76/C4/wKioL1ZcC3HBgvVAAAC8MAFzLJA151.jpg "title=" 01.jpg "alt=" Wkiol1zcc3hbgvvaaac8mafzlja151.jpg "/>

Therefore, the position of the table can be exchanged to use left and right mixed with the purpose!

Problem:

Count the number of students in each class! , in the class list: class name, classroom number, number of students

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M00/76/C5/wKiom1ZcDYfjM-OTAABX55fRmAg693.jpg "title=" 01.jpg "alt=" Wkiom1zcdyfjm-otaabx55frmag693.jpg "/>

Summary: Within the connection, the outside connection is not very different, but the external connection will not be successful record, but also the result of the final connection, and the connection, the result of the connection is only successful (two records exist)

• Cross-joins: crosses join

The result is consistent with the internal connection! Sometimes, when a Cartesian product is obtained, the display uses a cross-join

Cross connections are equivalent to no conditions within the connection!

• Natural connection: Natural Join

MySQL automatically determine the connection conditions to help us complete the connection!

A typical condition is a field with the same name in the table!

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M00/76/C4/wKioL1ZcEReBoDQSAAAXWMEtYak829.jpg "title=" 01.jpg "alt=" Wkiol1zcerebodqsaaaxwmetyak829.jpg "/>

and natural connections are also connected to outer connections

Natural Inner Connection: natural Join

Natural LEFT outer: natural LETF Join

Natural right outside: natural-Join

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M02/76/C4/wKioL1ZcEYWCAtjcAAA23RUhyes077.jpg "title=" 01.jpg "alt=" Wkiol1zceywcatjcaaa23ruhyes077.jpg "/>

Summarize:

The final effect is only: inside, left outside, right outside!

Cross, special inside

Natural, equivalent to automatically determine the connection conditions, complete inside, left outside, right outside!

MySQL Connection query Introduction