（15）连接查询概念与左连接语法、练习

集合set
集合的特性：无序性，唯一性
一张表就是一个集合，一行数据是集合的一个元素
理论上讲，不可能存在完全相同的两个行，但是表中可以有完全相同的行，因为表内部有个rowid（隐藏）

一、笛卡尔积

集合相乘是笛卡尔积，其实就是两个集合的完全组合
Q：设集合A有M个元素，M个元素各不相同
设集合B中，N个元素，N个元素个不相同
AxB得到的积，有M*N个元素，不可能重复
sql中两个表做笛卡尔积：select * from ta,tb;（不用乘号，用逗号）

二、左连接、右连接、内连接语法

①左连接 语法：select 列1，列2，列N from
tableA left join tableB
on tableA 列=tableB[此处表连接成一张大表，完全当成普通的表看]
where group by,having…..照常写

②右连接 语法：select 列1，列2，列N from
tableA right join tableB
on tableA 列=tableB[此处表连接成一张大表，完全当成普通的表看]
where group by,having…..照常写

③内连接 语法：select 列1，列2，列N from
tableA inner join tableB
on tableA 列=tableB[此处表连接成一张大表，完全当成普通的表看]
where group by,having…..照常写

三、左连接、右连接、内连接区别
左连接，以左表为准，去右表找匹配数据，找不到匹配，用NULL补齐，行数>=M（左表行数）
右连接，以右表为准，去左表找匹配数据，找不到匹配，用NULL补齐，行数>=M（右表行数）
内连接，查询左右表都有的数据，不要左右连接中null的那一部分
内连接是左右连接的交集

drop table if exists boy;
create table boy(
name varchar(4),
flower varchar(5)
)character set utf8;

insert into boy values('林书豪','玫瑰');
insert into boy values('刘翔','桃花');
insert into boy values ('周杰伦','茉莉花');
insert into boy values('鹿晗','荷花');
insert into boy values('刘德华','狗尾巴花');

drop table if exists girl;
create table girl(
name varchar(4),
flower varchar(5)
)character set utf8;

insert into girl values('艾薇儿','玫瑰');
insert into girl values('居里夫人','桃花');
insert into girl values('芙蓉姐','茉莉花');
insert into girl values('凤姐','茉莉花');
insert into girl values('林志玲','荷花');

select boy.*,girl.* from
boy left join girl
on girl.flower=boy.flower;


select girl.*,boy.* from
girl right join boy
on girl.flower=boy.flower;

select girl.*,boy.* from
boy inner join girl
on girl.flower=boy.flower;

外链接（不支持outer，但可以用union）
select boy.*,girl.* from boy left join girl on girl.flower=boy.flower
union 
select boy.*,girl.* from boy right join girl on girl.flower=boy.flower;

四、练习

create table mat (
matchID int,
hostTeamID int,
guestTeamID int,
matchResult varchar(20),
matchTime date
);

create table Team(
teamID int primary key,
teamName varchar(10)
);

insert into mat values(1,1,2,'1:2','2006-06-02');

insert into mat values(2,2,3,'4:2','2006-06-09');

insert into mat values(3,1,2,'9:2','2006-07-02');

insert into mat values(4,1,3,'1:2','2006-06-12');

insert into mat values(5,1,2,'3:2','2006-06-30');

insert into Team values(1,'拜仁');

insert into Team values(2,'不莱梅');

insert into Team values(3,'梅西');

//
select '主队名称',tmp.matchResult,tmp.guestTeamID,teamName as '客队名称' from 
(select hostTeamID,guestTeamID,matchResult,teamName as '主队名称'from
mat left join Team
on mat.hostTeamID=Team.teamID
where matchTime  )as tmp 
left join Team  on tmp.guestTeamID=Team.teamName;

1，先把顺序搞对
select hostTeamID,matchResult,guestTeamID from mat;

2,把主队的名称取到
select hostTeamID,matchResult,guestTeamID,teamName from
 mat left join Team 
on mat.hostTeamID=t.teamID;

3,把客队的名称取到（因为两次连接Team,所以都要取别名）
select hostTeamID,matchResult,guestTeamID,t1.teamName as hname, t2.teamName as gname from
mat left join Team as t1
on mat.hostTeamID=t1.teamID
left join Team as t2
on mat.guestTeamID=t2.teamID

4，时间限定，一些列去掉，无论怎么连接，连接后是一张表
select t1.teamName as hname,matchResult,t2.teamName as gname from
mat left join Team as t1
on mat.hostTeamID=t1.teamID
left join Team as t2
on mat.guestTeamID=t2.teamID
where mat.matchTime between '2006-06-01'and '2006-07-01';

//若有多个连接，先连接，再选取对应的列
select goods_id,goods_name,goods.cata_id,cat_name,goods.brand_id,brand_name from
goods left join catalog
on goods.cata=catalog.cata
left brand
on brand.brand_id=goods.brand_id;