About To Teach Myself SQL

Discussion in 'Mac Programming' started by Darkroom, Nov 17, 2008.

  1. Darkroom Guest

    Darkroom

    Joined:
    Dec 15, 2006
    Location:
    Montréal, Canada
    #1
    i'm about to embark on the adventures ( woo :rolleyes: ) of studying/teaching myself SQL...

    i managed to land "Sams Teach Yourself PHP, MySQL and Apache" from a friend, and i'm planning on using that as a base. however, the book is a few years old so i'm not sure what will be relevant and what will not.

    any suggestions for software, tutorials or any advice for my oh-so-exciting journey ahead?
     
  2. robbieduncan Moderator emeritus

    robbieduncan

    Joined:
    Jul 24, 2002
    Location:
    London
    #2
    Core ANSI SQL has been standard for years. The vendor specific dialects do change over time, but not normally too drastically. Learning the concepts is probably more important than the language though...
     
  3. Cromulent macrumors 603

    Cromulent

    Joined:
    Oct 2, 2006
    Location:
    The Land of Hope and Glory
    #3
    Been working on this myself recently to get a website business up and running, although I have been using PostgreSQL 8.3.3 and PHP 5.2.6 I'm pretty sure the concepts are the same. If you have any questions as you go I'll be happy to help. I'll probably learn more myself.

    PHP is a god awful language though. I wish Python was more useful as web programming language.
     
  4. garybUK Guest

    garybUK

    Joined:
    Jun 3, 2002
    #4
    This is true, though Oracle's PL/SQL is completely different to ISO!! SQL , from working on Oracle for a few years solely I cannot get my head around the horribleness that is MySQL's SQL formatting, it's so picky too!

    But the concepts from one can be transferred to another
     
  5. robbieduncan Moderator emeritus

    robbieduncan

    Joined:
    Jul 24, 2002
    Location:
    London
    #5
    I imagine I'd be the same as I only use Sybase Transact SQL at work...
     
  6. Cromulent macrumors 603

    Cromulent

    Joined:
    Oct 2, 2006
    Location:
    The Land of Hope and Glory
  7. Darkroom thread starter Guest

    Darkroom

    Joined:
    Dec 15, 2006
    Location:
    Montréal, Canada
    #7
    right, so i'm kinda confused...

    i have MySQL installed on my webserve, so i'm planning on using that for my testing/learning instead of installing it locally... although i did install it locally and started trying to understand an app called "Sequel Pro", but that only confused me more...

    i'm very comfortable with .PHP and i'm assuming that .PHP can be used to modify the data, or to call results so i'm hoping that learning SQL will be a breeze, but i'm missing some basics. what file extensions to SQL databases have? is it .sql? is it normal to create databases using a webcode editor like Dreamweaver, or is there something more standard?
     
  8. robbieduncan Moderator emeritus

    robbieduncan

    Joined:
    Jul 24, 2002
    Location:
    London
    #8
    You certainly are confused: SQL is a language on it's own right. How a particular database is stored is defined by the RDMS in use.

    Whilst there may be PHP wrappers/APIs to make using a SQL database easier this is not learning SQL: this is learning a PHP API.
     
  9. Cromulent macrumors 603

    Cromulent

    Joined:
    Oct 2, 2006
    Location:
    The Land of Hope and Glory
    #9
    As robbie said above, SQL is a programming language itself just like C, Java, PHP and C++.

    MySQL is a database implementation that uses the SQL standard to store data in the database. There are many SQL databases out there for instance MySQL, PostgreSQL, Oracle and Microsoft SQL (MSSQL). Each take the SQL standard and implement it (often in there own slightly different ways rather infuriatingly).

    You access the database either using a client or one you write yourself. In this case you will be writing a client in PHP. But there would be nothing stopping you from picking another language such as C or Python to write the client in.

    The SQL server then takes the SQL commands you send it and executes them and thus manipulates the database for you.

    Hope that is somewhat useful. Just what I have managed to scrape together while I've been learning myself.

    Edit: What version of MySQL do you have on your server?
     
  10. ChrisA macrumors G4

    Joined:
    Jan 5, 2006
    Location:
    Redondo Beach, California
    #10
    SQL databases typically are stored as many, many files in a directory tree. They are not files. and you wouldalmost never actually even see these files. the server tries to hide this from you. You would only use SQL to access the data

    However you might put a set of SQL commands into a file and name the file with .sql on the end. But there is no convention.

    The normal way to create database is to type in the SQL query commands. You can also write PHP, Perl or C code to create the SQL comands (after all SQL is just ext) and then pass those commands off to the database. Your PHP. Perl or C then uses the data to populate a web page.

    This stuff is all easy. The thing you really must learn is how to design databases. It is very easy to get this wrong. Yes you need to know the nuts and bolts suff about how to write a SQL querry but you need to step back and learn how to design the larger, big picture

    Here is a very basic introduction
    http://databases.about.com/od/specificproducts/a/firstnormalform.htm
     
  11. Darkroom thread starter Guest

    Darkroom

    Joined:
    Dec 15, 2006
    Location:
    Montréal, Canada
    #11
    i understand theory is important so i've been studying normalization... but isn't there some sort of GUI app i can use just to test/practice sql queries and see the database change? it would help to be able to follow this code in the book, and my understanding is that MySQL is used in terminal?
     
  12. plinden macrumors 68040

    plinden

    Joined:
    Apr 8, 2004
    #12
    I use Froq when I'm looking at databases on my Mac. It supports Oracle, MySQL, PostgreSQL, SQL Server and SQLite and has a decent Cocoa interface.

    It costs 50 Euros, but you can download it and use it for free - you just get nag screens on start up and shut down.
     
  13. yeroen macrumors 6502a

    yeroen

    Joined:
    Mar 8, 2007
    Location:
    Cambridge, MA
    #13
    For learning the core concepts, I'd take the easy, lightweight approach and just use SQLite. If you need a GUI, Firefox has a free SQLite front-end plugin.
     
  14. Darkroom thread starter Guest

    Darkroom

    Joined:
    Dec 15, 2006
    Location:
    Montréal, Canada
    #14
    i've tried using Sequel Pro but i couldn't connect to my webserve and the database i set up using the cPanel... after downloading Froq i guess it is the same type of app?
     
  15. Cromulent macrumors 603

    Cromulent

    Joined:
    Oct 2, 2006
    Location:
    The Land of Hope and Glory
    #15
  16. plinden macrumors 68040

    plinden

    Joined:
    Apr 8, 2004
    #16
    Yes, but all db frontend GUIs are going to need a connection set up.

    Actually, I just took a closer look at Froq, and it no longer has unlimited free access. Froq 1.3 (the version I use) is shareware but supports only Oracle. The latest Froq, that supports multiple dbs, has a 25 day trial.

    I might upgrade - it's definitely worth 50 Euros.

    Anyway, since I'm using Froq 1.3, I haven't tried to connect to MySQL. But it should be easy enough. What kind of problems were you having? If you can't connect from such a GUI, you'd also have problems using the db in a web app. The frontend PHP part is going to try to connect to the db in the same way as the GUI.
     
  17. Cromulent macrumors 603

    Cromulent

    Joined:
    Oct 2, 2006
    Location:
    The Land of Hope and Glory
    #17
    Hmm Froq looks better than Navicat and is cheaper too, plus it supports PostgreSQL. Thanks for the link :).

    Edit: Think I'll stick with Navicat having just played around with Froq.
     
  18. lee1210 macrumors 68040

    lee1210

    Joined:
    Jan 10, 2005
    Location:
    Dallas, TX
    #18
    You whippersnappers with your filthy graphical frontends. What's wrong with good old psql, mysql, or sqlplus command line interfaces that are free or come with the DB?

    -Lee
     
  19. Cromulent macrumors 603

    Cromulent

    Joined:
    Oct 2, 2006
    Location:
    The Land of Hope and Glory
    #19
    psql is okay I guess. It's just nicer sometimes to have all your tables laid out at the same time in front of you without having to query them all the time because they have been pushed off the edge of the screen.
     
  20. robbieduncan Moderator emeritus

    robbieduncan

    Joined:
    Jul 24, 2002
    Location:
    London
    #20
    I'm more of a sqsh kind of guy. I'm not sure I could survive at work without it. isql is just too poor.
     
  21. lee1210 macrumors 68040

    lee1210

    Joined:
    Jan 10, 2005
    Location:
    Dallas, TX
    #21
    I'm just kidding, i prefer psql and a nice big scrollback buffer to any of the shiny graphical things. I am also anti-navicat because it has led many people at my company to believe that they know sql, and has put "drop database" (yes, the roles issue is a separate one) two clicks away.

    -Lee
     
  22. iShater macrumors 604

    iShater

    Joined:
    Aug 13, 2002
    Location:
    Chicagoland
    #22

Share This Page