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

This article mainly introduces the common ways of MySQL table association, the contents of the article are carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand the common ways of MySQL table association there are several kinds of it.

Create tables and insert data

CREATE TABLE school (

Sch_id int (11) NOT NULL AUTO_INCREMENT

Sch_name varchar (50) NOT NULL

Sch_addr varchar (100) DEFAULT NULL

PRIMARY KEY (sch_id)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

CREATE TABLE student (

St_id int (11) NOT NULL AUTO_INCREMENT

St_name varchar (20) NOT NULL

Age smallint (6) DEFAULT NULL

Hight int (5) DEFAULT NULL

Sch_id int (11) DEFAULT NULL

PRIMARY KEY (st_id)

KEY sch_id (sch_id)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

INSERT INTO school VALUES (1m 'Nankai University', 'Nankai'), (2 'Central University of Finance and Economics', 'Beijing'), (3 'Hong Kong Polytechnic University', 'Hong Kong'), (4'Xi'an Jiaotong University','Xi'an'), (5 'Sydney University', 'Sydney'), (6 'Manchester University', 'Manchester'), (8) 'Yan'an Anti-Japanese military and political University', 'Yan'an')

INSERT INTO student VALUES (1 recorder 'Wang Xiaoyang', 26 mine168 6), (2 recorder 'Wang Nan', 28 mine162 2), (3 recorder 'Yang Zhenyu', 30 pierced 178) 1), (4 pyrrine 'Miao Xin', 28 mine162 3), (5 meme 'Zhang Shiyu', 27 record171 5), (8pint'Li Qian', 28pence162 4), (9 mum 'Jiang Shi', 26pr 178)

1. Left association: take the left table as the center and find out all the data in the left table. If the values of the associated fields are not equal, the data found in the right table is empty.

Select * from school a left join student b on a.sch_id=b.sch_id

two。 Right association: take the right table as the center and find out all the data in the right table. If the values of the associated fields are not equal, the data found in the left table is empty.

Select * from school a right join student b on a.sch_id=b.sch_id

3. Internal association: find out the equivalent data of the associated fields of the two tables

Select * from school an inner join student b on a.sch_id=b.sch_id

4. Find out the data that only belongs to the left table

Select * from school a left join student b on a.sch_id=b.sch_id where b.st_id is null

5. Find out the data that only belongs to the right table.

Select * from school a right join student b on a.sch_id=b.sch_id where a.sch_id is null

6. Find out all the data

Select from school a left join student b on a.sch_id=b.sch_id union select from school a right join student b on a.sch_id=b.sch_id

7. Find out the unequal data associated with the left and right tables

Select from school a left join student b on a.sch_id=b.sch_id where b.st_id is null union select from school a right join student b on a.sch_id=b.sch_id where a.sch_id is null

After reading the above about the common ways of MySQL table association, many readers must have some understanding, if you need to get more industry knowledge and information, you can continue to pay attention to our industry information column.

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.