MySQL Database Basics Learning notes (ii)

SQL Statement Combat--table Join ( Multi-table Query )

Standard The SQL statement format has the following 4 types:

ü in-table connections

Select Col1,col2...from tab1,tab2 where tab1.col3=tab2.col3;

Col1 column name tab1 table name where tab1.col3=tab2.col3 join Condition

ü out-of-table connections (two types)

Left connection

Select col1,col2 ... from tab1 Left join tab2 on tab1.col3=tab2.col3;

Right connection

Select col1,col2 ... from tab1 Right join tab2 on tab1.col3=tab2.col3;

ü Sub-query

Select *from Tab1 where col[in]| [=] (select *from tab2 where col ...);

Combat Scenario: Create two new tables and insert the specified data, using the two tables, the connection of the practice table (inside, left, right, child)

1) new Table A:

Create Table A

(ID smallint (5) unsigned auto_increment,

Name varchar (NOT NULL),

Primary Key (ID)

) Engine=innodb default Charset=utf8;

Desc A;

Execution Result:

the ID and name Two fields are visible .

2) new Table B:

Create Table B

(ID smallint (5) unsigned auto_increment,

Address varchar (NOT NULL),

a_id smallint (5) unsigned,

Primary Key (ID)

) Engine=innodb default Charset=utf8;

Desc B;

Explanation: a_id represents the ID associated with a

Execution Result:

ID,address,a_id Three fields are visible

3) insert data into A table:

Insert into A (name)

Values (' Zhang '), ( ' li '), ( ' king ');

Execution Result:

4) Insert data into the B table:

Insert into B (address,a_id)

Values (' Beijing ', 1), (' Shanghai ', 3) , (' Tianjin ', ten);

Execution Result:

5) execute INNER JOIN statement (this statement is the most

Select A.name, b.address from b where a.id = b.a_id;

Explanation: Find The name field in Table A and the address field in table B are provided in Table A the ID field and the a_id field in table B are connected.

Execution Result:

6) Execute left connection statement

Select A.name, b.address from A left join B on a.id = b.a_id;

Explanation: Left JOIN to the left column is the main, the right column to match the left side, if not match, then empty;

Execution Result:

as you can see from the results, the third line on the right address does not match on, so it is empty.

7) execute RIGHT JOIN statement

Select A.name, b.address from A right join B on a.id = b.a_id;

Explanation: The right connection is based on the right column, the left column to match the right side, if not match, then empty;

Execution Result:

as you can see from the results, the third line on the left name does not match on, so it is empty.

8) sub-query statement

SELECT * from A where ID in (Select a_id from B where address = ' Beijing ');

Explanation: The meaning in parentheses is from Find address in the B table is the corresponding a_idin Beijing,

and then again and the ID of a table to match.

MySQL Database Basics Learning notes (ii)