mysql数据库7种_Mysql的7种join解析

Mysql的7种join，各种项目中，数据库经常用到，也是必不可少的，数据库查询中经常使用到两个表之间的查询甚至是多表之间的查询，以下列举了mysql的7种join语句。

建表语句

建两个表，有外键相关联的表。

create table user

(

id int primary key,

name char(11)

)engine=innodb;

create table role

(

rid int primary key,

rname char(11),

uid int,

constraint foreign key (uid) references user(id)

)engine=innodb;

insert into user values(1,'xiaohai');

insert into user values(2,'xiaolan');

insert into user values(3,'xiaoxiao');

insert into user values(4,'xiaolong');

insert into user values(5,'xiaolei');

insert into role values(1,'admin',1);

insert into role values(2,'user',2);

insert into role values(3,'vip1',3);

insert into role values(4,'vip2',4);

insert into role(rid,rname) values(5,'admin');

1.文氏图左外连接

SQL语句

select u.*,r.* from user u left join role r on u.id = r.uid ;

查询结果：

2.文氏图右外连接

SQL语句

select u.*,r.* from user u right join role r on u.id = r.uid ;

查询结果

3.文氏图左连接

SQL语句:去掉表A表B都有的，单独显示表A的元素

select u.*,r.* from user u left join role r on u.id = r.uid where r.rid is null;

查询结果

4.文氏图右连接

SQL语句

select u.*,r.* from user u right join role r on u.id = r.uid where u.id is null;

查询结果

5.文氏图内连接

sql语句

select u.*,r.* from user u inner join role r on u.id = r.uid;

查询结果

6.全连接：oracle数据库有full join ...on语句，mysql没有。

SQL语句

select u.*,r.* from user u left join role r on u.id = r.uid where r.rid is null

union

select u2.*,r2.* from user u2 right join role r2 on u2.id = r2.uid;

查询结果

7.两张表中都没有出现的数据集：就是两张表各自独立的那部分数据集，没有相同部分的数据集。

文氏图

SQL语句

select u.*,r.* from user u left join role r on u.id = r.uid where r.rid is null

union

select u2.*,r2.* from user u2 right join role r2 on u2.id = r2.uid where u2.id is null;

查询结果