（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.* fromboy left join girlon girl.flower=boy.flower;select girl.*,boy.* fromgirl right join boyon girl.flower=boy.flower;select girl.*,boy.* fromboy inner join girlon girl.flower=boy.flower;外链接（不支持outer，但可以用union）select boy.*,girl.* from boy left join girl on girl.flower=boy.flowerunion 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 '主队名称'frommat left join Teamon mat.hostTeamID=Team.teamIDwhere 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 frommat left join Team as t1on mat.hostTeamID=t1.teamIDleft join Team as t2on mat.guestTeamID=t2.teamID4，时间限定，一些列去掉，无论怎么连接，连接后是一张表select t1.teamName as hname,matchResult,t2.teamName as gname frommat left join Team as t1on mat.hostTeamID=t1.teamIDleft join Team as t2on mat.guestTeamID=t2.teamIDwhere 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 fromgoods left join catalogon goods.cata=catalog.cataleft brandon brand.brand_id=goods.brand_id;