MySQL database questions

[Brutus]

This is for anyone who's familiar with SQL.

I just recently, on the advice of wannabe-researchers, downloaded MySQL to do extensive querying and data analysis. As an Excel buff, I was told that working with large data and complex formulas was 10 times easier if I'd made the switch.

So far, that seems to be very true.

My question now: I've only been working with SQL files. This may seem like a stupid question, but how are csv or excel files converted into SQL so I can use the data tables?

After very rudimentary testing, I believe I'm convinced that I'm going to make the transition to SQL when working with large data sets. I'm amazed at the ease in which I can manipulate and analyze the information. But I need to know how to easily convert these tables so I can use more often.

Any help would be appreciated!

[IslandRed]

I haven't used it in awhile so I can't give exact commands off the top of my head, but you can start with CSV files and either the "mysqlimport" utility or the LOAD DATA INFILE command to read in the file in one shot.

[Brutus]

Originally Posted by IslandRed

I haven't used it in awhile so I can't give exact commands off the top of my head, but you can start with CSV files and either the "mysqlimport" utility or the LOAD DATA INFILE command to read in the file in one shot.

Thanks. I'm using SQLyog, the community edition, and under "tables" I found an "import CSV data using Load Local" option, so it seems that's what I needed.

I'll have to give it a whirl to find out.

[jmcclain19]

http://www.withdata.com/csv/csv-to-sql/

Not a stupid question at all. There are tons of third party tools that do it as well

[Brutus]

Originally Posted by jmcclain19

http://www.withdata.com/csv/csv-to-sql/

Not a stupid question at all. There are tons of third party tools that do it as well

Very much appreciate it.

Another question if you or anyone would be so kind...

Do you know of a line command in SQL that I would be able to calculate something based on conditional logic of another record?

For instance... let's say I have four fields in a record: apples, peaches, pears and bananas.

Based on a quantity of greater than 0 in two fields, I would want to calculate the quantity and also based on the quantity of the record immediately following. Do you know of a command that would make that possible? Obviously there are IF functions that probably accommodate this, but I'm still learning so I don't know what they might be LOL

I realize that might be a difficult question to answer, but it's worth a shot!

[IslandRed]

That kind of depends on what you mean by "immediately following." SQL queries are set-based and it doesn't have a concept of sequential records except where you might have set up a unique key field to hold the incrementing values.

If you can go into more detail on what you're trying to do, I'll help if I can.

[Brutus]

Originally Posted by IslandRed

That kind of depends on what you mean by "immediately following." SQL queries are set-based and it doesn't have a concept of sequential records except where you might have set up a unique key field to hold the incrementing values.

If you can go into more detail on what you're trying to do, I'll help if I can.

I am trying to extract information from PBP records from the Retrosheet baseball database, in this case.

In their records, it shows a playerID for each base occupied when the player is at-bat as well as the number of outs. What I want to do is find the total bases advanced by all baserunners from a plate appearance, which can only be done based on the circumstances of the plate appearance below it (as it's in sequential order).

So if a batter is up with one out and showing a runner occupying first, I would need to know the bases being occupied beginning the next plate appearance as well as the number of outs and updated score on the next line to know how many bases were gained.

I will send a more detailed example to you if this is still confusing. I might not be explaining it very well.

[IslandRed]

I'm not the world's foremost SQL expert, but it sounds like you might best get started with a self-join, something on the (simplified) order of:

select a.atbatid as thisatbat, a.field1 as thisbase, etc., b.atbatid as nextatbat, b.field1 as nextbase, etc.
from atbat a
left join atbat b
on b.plateappearance = a.plateappearance + 1
where a.thisgame = b.thisgame

Might not have that exactly right but you get the idea. You would get a record with the info of both an at-bat and its following at-bat, and run your calculations against that.

[Brutus]

Originally Posted by IslandRed

I'm not the world's foremost SQL expert, but it sounds like you might best get started with a self-join, something on the (simplified) order of:

select a.atbatid as thisatbat, a.field1 as thisbase, etc., b.atbatid as nextatbat, b.field1 as nextbase, etc.
from atbat a
left join atbat b
on b.plateappearance = a.plateappearance + 1
where a.thisgame = b.thisgame

Might not have that exactly right but you get the idea. You would get a record with the info of both an at-bat and its following at-bat, and run your calculations against that.

Thanks... this will probably get me going on the right track, which is what I was hoping to accomplish. I was just starting to read up on joins anyhow, so I'll try to put this basic template to the test and work with it as needed.

Really appreciate your assistance!

[TRF]

Love SQLYog, it's worth the money to buy the full version also Navicat is very good. the new gui from MySQL is much better than the previous versions. very robust and combines query analyzer with mySQL Administrator. but SQLyog is still the easiest to use. very easy to import and export data across databases as well as exporting to flat files.

[Brutus]

Originally Posted by TRF

Love SQLYog, it's worth the money to buy the full version also Navicat is very good. the new gui from MySQL is much better than the previous versions. very robust and combines query analyzer with mySQL Administrator. but SQLyog is still the easiest to use. very easy to import and export data across databases as well as exporting to flat files.

I'm using the SQLyog community edition, but like you, I was urged to purchase the premium version if I really wanted to make the most of it.

Another friend of mine is trying to get me to switch over to myPHPadmin, but I told him I have to crawl before I can walk lol

[TRF]

also there is a very good SQL tutorial at w3schools.com

[TRF]

myPHPadmin is good if you are going to be doing a lot of work with php. it will generate helpful scripts for you.

Navicat is better than yog when transferring data between databases IMO. both have free versions. yog overall just feels better though and more intuitive.