Turn Off Ads?
Results 1 to 13 of 13

Thread: MySQL database questions

  1. #1
    Et tu, Brutus? Brutus's Avatar
    Join Date
    Jul 2006
    Location
    Atlanta, Ga.
    Posts
    10,565

    MySQL database questions

    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!
    "No matter how good you are, you're going to lose one-third of your games. No matter how bad you are you're going to win one-third of your games. It's the other third that makes the difference." ~Tommy Lasorda

  2. Turn Off Ads?
  3. #2
    Charlie Brown All-Star IslandRed's Avatar
    Join Date
    May 2001
    Location
    Melbourne, FL
    Posts
    4,850

    Re: MySQL database questions

    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.
    Not all who wander are lost

  4. #3
    Et tu, Brutus? Brutus's Avatar
    Join Date
    Jul 2006
    Location
    Atlanta, Ga.
    Posts
    10,565

    Re: MySQL database questions

    Quote Originally Posted by IslandRed View Post
    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.
    "No matter how good you are, you're going to lose one-third of your games. No matter how bad you are you're going to win one-third of your games. It's the other third that makes the difference." ~Tommy Lasorda

  5. #4
    Smells Like Teen Spirit jmcclain19's Avatar
    Join Date
    Apr 2004
    Location
    Phoenix
    Posts
    6,490

    Re: MySQL database questions

    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

  6. #5
    Et tu, Brutus? Brutus's Avatar
    Join Date
    Jul 2006
    Location
    Atlanta, Ga.
    Posts
    10,565

    Re: MySQL database questions

    Quote Originally Posted by jmcclain19 View Post
    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!
    "No matter how good you are, you're going to lose one-third of your games. No matter how bad you are you're going to win one-third of your games. It's the other third that makes the difference." ~Tommy Lasorda

  7. #6
    Charlie Brown All-Star IslandRed's Avatar
    Join Date
    May 2001
    Location
    Melbourne, FL
    Posts
    4,850

    Re: MySQL database questions

    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.
    Not all who wander are lost

  8. #7
    Et tu, Brutus? Brutus's Avatar
    Join Date
    Jul 2006
    Location
    Atlanta, Ga.
    Posts
    10,565

    Re: MySQL database questions

    Quote Originally Posted by IslandRed View Post
    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.
    "No matter how good you are, you're going to lose one-third of your games. No matter how bad you are you're going to win one-third of your games. It's the other third that makes the difference." ~Tommy Lasorda

  9. #8
    Charlie Brown All-Star IslandRed's Avatar
    Join Date
    May 2001
    Location
    Melbourne, FL
    Posts
    4,850

    Re: MySQL database questions

    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.
    Not all who wander are lost

  10. #9
    Et tu, Brutus? Brutus's Avatar
    Join Date
    Jul 2006
    Location
    Atlanta, Ga.
    Posts
    10,565

    Re: MySQL database questions

    Quote Originally Posted by IslandRed View Post
    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!
    "No matter how good you are, you're going to lose one-third of your games. No matter how bad you are you're going to win one-third of your games. It's the other third that makes the difference." ~Tommy Lasorda

  11. #10
    Vavasor TRF's Avatar
    Join Date
    Apr 2000
    Location
    Amarillo, TX
    Posts
    13,394

    Re: MySQL database questions

    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.
    Suck it up cupcake.

  12. #11
    Vavasor TRF's Avatar
    Join Date
    Apr 2000
    Location
    Amarillo, TX
    Posts
    13,394

    Re: MySQL database questions

    also there is a very good SQL tutorial at w3schools.com
    Suck it up cupcake.

  13. #12
    Et tu, Brutus? Brutus's Avatar
    Join Date
    Jul 2006
    Location
    Atlanta, Ga.
    Posts
    10,565

    Re: MySQL database questions

    Quote Originally Posted by TRF View Post
    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
    "No matter how good you are, you're going to lose one-third of your games. No matter how bad you are you're going to win one-third of your games. It's the other third that makes the difference." ~Tommy Lasorda

  14. #13
    Vavasor TRF's Avatar
    Join Date
    Apr 2000
    Location
    Amarillo, TX
    Posts
    13,394

    Re: MySQL database questions

    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.
    Suck it up cupcake.


Turn Off Ads?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Board Moderators may, at their discretion and judgment, delete and/or edit any messages that violate any of the following guidelines: 1. Explicit references to alleged illegal or unlawful acts. 2. Graphic sexual descriptions. 3. Racial or ethnic slurs. 4. Use of edgy language (including masked profanity). 5. Direct personal attacks, flames, fights, trolling, baiting, name-calling, general nuisance, excessive player criticism or anything along those lines. 6. Posting spam. 7. Each person may have only one user account. It is fine to be critical here - that's what this board is for. But let's not beat a subject or a player to death, please.

Thank you, and most importantly, enjoy yourselves!


RedsZone.com is a privately owned website and is not affiliated with the Cincinnati Reds or Major League Baseball


Contact us: Boss | GIK | BCubb2003 | dabvu2498 | Gallen5862 | LexRedsFan | Plus Plus | RedlegJake | redsfan1995 | The Operator | Tommyjohn25