设计一个系统,保存乒乓球联赛的比赛信息!
要求:在mysql命令行输出如下信息:
1. 创建一个数据库(本例以ginvip数据库为例)
create database ginvip;
2. 创建队员信息表
create table player ( id int unsigned primary key auto_increment, player_name varchar(20) default '', gender enum('male','female','secret') );
在player表中插入队员信息数据:
insert into player values (1,'Cameron Poe','male'), (2,'Casey Poe','female'), (3,'John J.Rambo','male'), (4,'Jason Bourne','male'), (5,'Marie Kreutz','female'), (6,'James Bond','male');
这样得到以下队员信息:
添加其他更多的字段信息(随意添加,仅为了熟悉mysql语法):
alter table player add column `money` decimal(10,2) default 2536.02;
alter table player add column `height` float(5,2) default 175.00;
这样就得到如下队员信息:
修改money , height 字段信息:
update player set money=money+rand()*1000;
update player set height=165+rand()*20;
修改完后的信息如下:
3. 创建比赛信息表
create table player_match( match_id int primary key auto_increment, player_1 int unsigned comment '选手1的ID', player_2 int unsigned comment '选手2的ID', match_time datetime, match_result char(3), match_address varchar(10) default 'London' );
插入比赛数据:
insert into player_match values (null,2,5,'2015-11-1 18:00:00','5:3','barcelona'), (null,1,3,'2015-10-19 19:00:00','3:5','real mardrid'), (null,2,4,'2015-10-25 20:00:00','2:6','beijing'), (null,3,6,'2015-12-1 19:30:00','8:0','london');
查询:
select match_time,player_1,match_result,player_2 from player_match;
将player_1字段换成player_name字段,将选手名字显示出来
select match_time,player_name,match_result,player_2 from player_match left join player on player_match.player_1=player.id;
此时,可以对选手表进行再次连接,输出player_2的选手名字
select match_time,player_name,match_result,player_name from player_match left join player on player_match.player_1=player.id left join player on player_match.player_2=player.id;
由以上语句可以看出,出现一个表在一次查询时,被多次使用!注意,保证使用时没有歧义!
为产生歧义的表起别名!所以以上语句可改为如下语句,最终输出:
select match_time,p1.player_name,match_result,p2.player_name from player_match left join player as p1 on player_match.player_1=p1.id left join player as p2 on player_match.player_2=p2.id;