Shulou(Shulou.com)06/01 Report--

Let's talk about the basics of MySQL database. The secret of the text is that it is close to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading this article on the basics of MySQL database.

Sql statement practice-table join (multi-table query)

There are four standard SQL statement formats:

U intra-table connection

Select col1,col2...from tab1,tab2 where tab1.col3=tab2.col3

Col1 column name tab1 table name where tab1.col3=tab2.col3 connection condition

U off-balance sheet connection (divided into 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

U sub-query

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

Practical scenario: create two new tables and insert the specified data, using these two tables, the connection of the exercise table (inside, left, right, sub)

1) create Table A:

Create table A

(id smallint (5) unsigned auto_increment

Name varchar (50) not null

Primary key (id)

) engine=innodb default charset=utf8

Desc A

Execution result:

You can see that there are two fields id and name.

2) create Table B:

Create table B

(id smallint (5) unsigned auto_increment

Address varchar (50) 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:

It can be seen that there are three fields: id, address and A_id

3) insert data into Table A:

Insert into A (name)

Values (Zhang), (Li), (Wang)

Execution result:

4) insert data into Table B:

Insert into B (address,A_id)

Values ('Beijing', 1), ('Shanghai', 3), ('Tianjin', 10)

Execution result:

5) execute the inner connection statement (this statement is recommended first)

Select A.name, B.address from A, B where A.id = B.A_id

Explanation: look for the name field in table An and the address field in table B. the condition is that the id field in table An is connected with the A_id field in table B.

Execution result:

6) execute the left join statement

Select A.name, B.address from A left join B on A.id = B.A_id

Explanation: the left connection is dominated by the left column, and the right column matches the left column. If the match does not match, it will be empty.

Execution result:

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

7) execute the right join statement

Select A.name, B.address from A right join B on A.id = B.A_id

Explanation: the right connection is dominated by the column on the right, and the column on the left matches the column on the right. If there is no match, it will be empty.

Execution result:

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

8) Sub-query statement

Select * from A where id in (select A_id from B where address = 'Beijing')

Explanation: the parentheses mean that address is the corresponding A_id of Beijing from Table B.

Then match it with the id of Table A.

Is there anything you don't understand about the above basic knowledge of MySQL database? Or if you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.