mysql多表联合查询
程序员文章站
2022-07-05 23:42:04
创建表a插入数据create table a( `id` int(11) primary key , `name` varchar(6) not null , `age` int(4) not null);insert into a values (1,'111',20);insert into a values (2,'222',20);insert into a values (3,'333',20);insert into a values (4,'444',2....
创建表a插入数据
create table a(
`id` int(11) primary key ,
`name` varchar(6) not null ,
`age` int(4) not null
);
insert into a values (1,'111',20);
insert into a values (2,'222',20);
insert into a values (3,'333',20);
insert into a values (4,'444',20);
创建表b插入数据
create table b(
`id` int(11) primary key ,
`result` float
);
insert into b values (1,11);
insert into b values (2,22);
insert into b values (5,11);
.内连接:INNER JOIN 求两个表的交集
select a.id,name,age,result
from a inner join b
on a.id = b.id;
id | name | age | result | |
---|---|---|---|---|
1 | 1 | 111 | 20 | 11 |
2 | 2 | 222 | 20 | 22 |
LEFT JOIN 求两个表的交集外加左表剩下的数据
select a.id,name,age,result
from a left join b
on a.id = b.id;
id | name | age | result | |
---|---|---|---|---|
1 | 1 | 111 | 20 | 11 |
2 | 2 | 222 | 20 | 22 |
3 | 3 | 333 | null | null |
4 | 4 | 444 | null | null |
RIGHT JOIN 两个表的交集外加右表剩下的数据
select a.id,name,age,result
from a right join b
on a.id = b.id;
id | name | age | result | |
---|---|---|---|---|
1 | 1 | 111 | 20 | 11 |
2 | 2 | 222 | 20 | 22 |
3 | null | null | null | 11 |
本文地址:https://blog.csdn.net/qq_43839730/article/details/107640265