mysql左连接查询面试题

菜鸟学mysql,左连接练习。


题目要求：
[img]
[img]http://dl.iteye.com/upload/attachment/0080/9612/d303c546-5520-35a9-9ffc-4c1d0f666ca3.png[/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]http://dl.iteye.com/upload/attachment/0080/9598/e34e486d-a130-3b6e-9cd8-0a40899a47e5.png[/img]
[/img]


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

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

);

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

[img]
[img]http://dl.iteye.com/upload/attachment/0080/9600/200972f6-6c78-334d-b596-f68722665385.png[/img]
[/img]


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

select hid, result, gid,mtime from m;

[img]
[img]http://dl.iteye.com/upload/attachment/0080/9602/0a828d3f-b62b-3f8c-b360-bb8830d41b30.png[/img]
[/img]


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

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

[img]
[img]http://dl.iteye.com/upload/attachment/0080/9604/b6950d42-5da7-3722-b510-105f5a090432.png[/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]http://dl.iteye.com/upload/attachment/0080/9606/0afddf02-5e90-3e41-bb6d-c9a46f8e60e5.png[/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]http://dl.iteye.com/upload/attachment/0080/9608/26f2da5d-a6e3-3317-b026-cc846e084ce3.png[/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]http://dl.iteye.com/upload/attachment/0080/9610/c6071a4b-dc7b-3506-a2cc-feb716668098.png[/img]
[/img]


over