内连接 inner join或者join
外连接
1,左连接 left join或者left outer join
2,右连接 right join或者right outer join
3,完全外连接 full join或者full outer join
创建数据库:
create database testjoin;
创建两个表:
person表:id,name,cardId
create table person(
id int,
name varchar(20),
cardId int
);
card表:id,name
create table card(
id int,
name varchar(20)
);
给card插入数据
insert into card values(1,'饭卡');insert into card values(2,'建行卡');insert into card values(3,'农行卡');
insert into card values(4,'工商卡');insert into card values(5,'邮政卡');
给person插入数据
insert into person values(1,'张三',1);insert into person values(2,'李四',3);
insert into person values(3,'王五',6);
mysql> select * from person; mysql> select * from card;
王五的卡号是6但是在card中没有6,本来是插入不进去6的,虽然我们没有创建外键,但是外键是存在的
1,inner join (内连接)
相当于把两个表中的数据,通过某个字段相对,查询出相关的记录数据
select * from person inner join card on person.cardId=card.id;
select * from person join card on person.cardId=card.id;
person 和card进行连接查询,id是一样的,可以都一一对应
2,left join (左外连接)
select * from person left join card on person.cardId=card.id;
select * from person left outer join card on person.cardId=card.id;
把左边的这个表全部选出来,右边的表有的话查询,没有的话补空
3,right join (右外连接)
select * from person right join card on person.cardId=card.id;
select * from person right outer join card on person.cardId=card.id;
把右边的这个表全部选出来,左边的表有的话查询,没有的话补空
4,full join(全外连接)
select * from person full join card on person.cardId=card.id;
----mysql有时不支持全外链接
全外连接就是左连接和右连接进行联合使用union
select * from person left join card on person.cardId=card.id
union
select * from person right join card on person.cardId=card.id;