macOS About To Teach Myself SQL

[Darkroom]

i'm about to embark on the adventures ( woo ) 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?

 

[robbieduncan]

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...

 

[Cromulent]

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.

 

[garybUK]

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...

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

 

[robbieduncan]

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!

I imagine I'd be the same as I only use Sybase Transact SQL at work...

 

[Cromulent]

MySQL always favoured speed over compliance.

 

[Darkroom]

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?

 

[robbieduncan]

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.

 

[Cromulent]

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?

 

[ChrisA]

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?

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

 

[Darkroom]

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?

 

[plinden]

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.

 

[yeroen]

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.

 

[Darkroom]

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?

 

[Cromulent]

I've been using the PostgreSQL version of Navicat.

Here is the MySQL one: http://mysql.navicat.com/mac_detail.html

 

[plinden]

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.

 

[Cromulent]

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.

 

[lee1210]

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

 

[Cromulent]

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

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.

 

[robbieduncan]

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

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.

 

[lee1210]

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

 

[iShater]

www.w3schools.com

Look at their SQL tutorial, that is one of my favorite ones.