设计一个系统,保存乒乓球联赛的比赛信息!
要求:在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;