Tuesday, 25 September 2007

Loading Premiership Data into MySQL


MySQL makes it nice and easy to load the CSV files in. First create a table as follows:


drop table if exists matches;

create table matches ( matchdate date not null,
hometeam varchar(25) not null,
homescore smallint not null,
awayteam varchar(25) not null,
awayscore smallint not null
);

create index id_matches on matches( matchdate, hometeam, awayteam);


It is then easy to load the date in using the load data command:


LOAD DATA LOCAL INFILE
'PATH/TO/CSV/FILE/allprem.csv'
into table rsssf.matches
fields terminated by ','
ignore 8 lines
( matchdate, hometeam, homescore, awayteam, awayscore );


It is then straightforward to perform simple queries on the database. For example all the home derbies for Liverpool:


select matchdate, hometeam, homescore, awayscore, awayteam
from matches
where hometeam = "Liverpool" and awayteam = "Everton"
order by 1;


gives the result at the top of this post.

4 comments:

ACE Soft said...

Nice blog for new generation


alstair
Kolkata Web Design Company

Alex Gillespie said...

Neil,

I sent you an email yesterday, as I have a career opportunity I eould like to discuss with you.

Can you please email me your contact details so I can get in touch to discuss?

Best regards,

Alex Gillespie
Director
Gillespie Manners Search and Selection
alex.gillespie@gillespiemanners.com

deeksha said...

Superb i really enjoyed very much with this article here. Really its a amazing article i had ever read. I hope it will help a lot for all. Thank you so much for this amazing posts and please keep update like this excellent article.

Digital Marketing Company in Chennai

Shalini said...

Superb. I really enjoyed very much with this article here. Really it is an amazing article I had ever read. I hope it will help a lot for all. Thank you so much for this amazing posts and please keep update like this excellent article.thank you for sharing such a great blog with us. expecting for your.
Digital Marketing Company in India
Seo Company in India