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.

7 comments:

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

Merlin Kristianti said...

Cara yang selanjutnya bisa diperhatikan adalah tentang potensi atau peluang peruntungan anda. Nah ini sebagian diantaranya adalah dengan melakukan beberapa hal yang disebutkan dalam poin diatas
asikqq
dewaqq
sumoqq
interqq
pionpoker
bandar ceme terbaik
hobiqq
paito warna
forum prediksi

Minat Jesisca said...

BONUS YANG TERSEDIA :
* BONUS NEW MEMBER 20%
* BONUS NEXT DEPOSIT 5% ( TERBARU )
* Bonus Turn Over Mingguan 0.5% (ALL GAME TANPA SYARAT)
* BONUS REFERAL BERJALAN TERBESAR ( BONUS DOUBLE )

AYO DAFTARKAN DIRI ANDA SEKARANG JUGA & IKUTI BERBAGAI MACAM EVENT-EVENT MENARIK LAINNYA!!

Untuk Informasi lebih lanjut silahkan hubungi :

WA : +855887950794
Line : minatpoker
Facebook : Cathy Gerald Tan

minatpoker
dewapoker
pokeronline

Agen IDN Poker Online
Bonus Poker Terbesar
Poker Online Indonesia
Situs Poker Online Terpercaya
Agen IDN Play Terbaik

Unknown said...

paito china
data sydney
datahk
syair sydney
syairsgp
datasgp
paito warna
http://warungsgp.com/
live hk 6d
live sydney

Unknown said...

Nice blog on the web. Today, Emblix solutions as one of the best and top most service-oriented Digital Marketing Agency in Hyderabad and India, Which provides high-quality result-oriented Digital Services ranging from SEO to Web Design, Social Media Marketing and more, to a broad spectrum of clients from diverse industry segments. Through a well-oiled combination of Quality Solutions,

sara mohamed said...

شركة تنظيف بالرياض
تنظيف بالرياض
نظافة بالرياض
شركة تنظيف الخزانات بالرياض
شركة تنظيف خزانات بالرياض
تنظيف خزانات بالرياض
شركة تنظيف سجاد بالرياض
تنظيف سجاد بالرياض
شركة تنظيف كنب بالرياض
تنظيف كنب بالرياض
شركة تنظيف مساجد بالرياض
تنظيف مساجد بالرياض
شركة تنظيف مكيفات بالرياض
تنظيف مكيفات بالرياض
شركة تعقيم بالرياض
تعقيم بالرياض
شركة تنظيف شقق بالرياض
تنظيف شقق بالرياض
شركة تنظيف منازل بالرياض
تنظيف منازل بالرياض