mysql左连接查询面试题

菜鸟学mysql,左连接练习。


题目要求：
[img]

[/img]


第一步：首先创建第一个表，并添加数据，如下图：

create table m(
mid int primary key auto_increment,
hid int,
gid int,
result varchar(50),
mtime date

);

insert into m
(hid,gid,result,mtime)
values
(1,2,'2:0','2006-05-21'),
(2,3,'1:2','2006-06-21'),
(3,1,'2:5','2006-05-25'),
(2,1,'3:2','2006-07-21');

[img]

[/img]



第二步：创建第二张表，并模拟添加数据，如下：

create table t(
tid int,
tname varchar(10)

);

insert into t
values
(1,'巴萨'),
(2,'皇马'),
(3,'野马队');

[img]

[/img]



第三步，先把输出的顺序搞定，如下：

select hid, result, gid,mtime from m;

[img]

[/img]



第四步：把主队的名字取出来，如下：

select hid,tname, result,gid,mtime 
from
m left join t
on m.hid = t.tid;

[img]

[/img]



第五步：把客队的名字也取出来，如下：

select hid,t1.tname as '主队', result,gid,t2.tname as '客队',mtime 
from
m left join t as t1
on m.hid = t1.tid
left join t as t2
on m.gid = t2.tid;

[img]

[/img]



第六步：再查询一次，这次不需要 hid 与 gid 这两列， ok let's go

select t1.tname as '主队',result as '比分',t2.tname as '客队',mtime as '比赛时间'
from
m left join t as t1
on m.hid = t1.tid
left join t as t2
on m.gid = t2.tid;

[img]

[/img]



第七步：添加查询条件：查询2006-06-01  到2006-07-01之间的比赛

select t1.tname as '主队',result as '比分',t2.tname as '客队',mtime as '比赛时间'
from
m left join t as t1
on m.hid = t1.tid
left join t as t2
on m.gid = t2.tid
where mtime between '2006-06-01' and '2006-07-01';

[img]

[/img]


over