mysql 多表连接查询

创建所需要的表

-- 创建t1,t2表
create table t1(
	key1 varchar(20),
    v1 int(4)
    );
    
##导入数据，，数据不可以存储在有中文字符的路径下面
load data local infile 'E:/data/t1.csv' 
	into table t1
    fields terminated by ','
    ignore 1 lines;
    
select * from t1;
    
create table t2(
	key2 varchar(20),
    v2 int(4)
    );

load data local infile 'E:/data/t2.csv' 
	into table t2
    fields terminated by ','
    ignore 1 lines;

　　-- 左连接、右连接以及内连接

##左连接

select * from t1 left join t2 on t1.key1 = t2.key2;

##右连接

select *  from t1 right join t2 on  t1.key = t2.key2;

##内连接

select * from t1 inner join t2 on  t1.key1  =  t2.key2;


##合并查询

select * from t1
union     ##消除重复的字段  union     不消除重复字段用   union all
select * from t2;




-- 笛卡尔积：select 字段1[,…] from 表1,表2[,…];

select *  from t1,t2;


-- 消除笛卡尔积：select 字段1[,…] from 表1,表2[,…] where 表1.key=表2.key;
select  *  from  t1,t2
where  t1.key1=t2.key2;

select *  from t1  cross join  t2  on  t1.key1 = t2.key2;   ##交叉连接

##注意：：消除笛卡儿积后和交叉连接的结果相同

###全连接    在mysql中无全连接，但是可以通过左连接和右连接合并去重得到；
select * from t1  left join t2  on  t1.key1 = t2.key2
union 
select * from t1  right join t2  on t1.key = t2.key2;