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.

6 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

Philips Huges said...



Thanks admin for sharing the unique content, you have done a great job

Installment loans in Mississippi
Payday loans in Mississippi
Title loans in Mississippi

Philips Huges said...

Its a wonderful post and very helpful, thanks for all this information. You are including better information regarding this topic in an effective way.Thank you so much

Installment loans
Payday loans
Title loans
Cash Advances