log in


2008-04-09 18:10 UTC

Learning SQL

The main reason I started learning SQL was because I was bored, and to eradicate this condition, asked one of my far more experienced friends if he had a project in mind that I might be able to do. This project was a “relatively simple” one, which he believes he could finish in about a week; just some PHP and MySQL, and perhaps some AJAX in order to make some sort of webcomic portal (i.e. login, upload, commenting, etc).

To start me off, he recommending becoming friendly with MySQL and the PHP PDO object, through which he suggested all database transactions occur; armed with a link to that, and some access to his server, I dove in. At first, attempted a simple query with a PDO object, but then wanted to know more about MySQL.
Google provided a few links:
  • About.com: PHP & MySQL
    • I found this only slightly helpful; mostly in learning how to connect to the database with a DBO object, and the most basic of syntax (select, insert, etc)
  • Tizag.com: MySQL Tutorial
    • This link provided some concrete examples of working with MySQL via PHP, and instilled a sense of basic understanding how some code functions. However, it only approaches basics, which can give you a good foundation, but do leave one wondering about more advanced topics. (‘Advanced’ topics there: max, count, group by, etc)
  • MySQL Reference Documents
    • At some point, I went looking for a place to actually describe MySQL’s various datatypes; this proved to be it, and worked pretty well for that, as well as providing some excellent examples of code (HAVING clause, for one, can’t remember if used for anything else)
  • SQLzoo.net - Interactive Awesome
    • This site helped a ton. I found it on Webmark, but it was pretty much what I was looking for – a place online where I could test my slight knowledge against code, where it would also be checked for me. With this, I stumbled only once, hitting the ‘extremely hard’ double Select statements section, until getting to self-joins, where I remain. After going through these tutorials, I felt like I actually knew something about SQL queries; in the least, how to perform them.
  • Joins via Venn Diagram - cool
    • This article, also from Webmark, helped me to visualize the various joins, but I read this before the sqlzoo.net tutorial, so what I had at this point was only a vague framework in my head how the joins actually worked. However, I read most of the comments about the article and doubted it’s correctness, though intuitively it felt quite right.
  • Database Skills
    • These essays (currently 6) provide a guide on how to design a database, and focus on reducing code needed to support and query such a database, while maintaining scalability and flexibility. It's a nice read, and provides some great information about things like Primary Keys, Foreign Keys, and the normal forms of a database. While reading this, I noticed that the views I had often coincided with that of a "database programmer" rather than a "code grinder", which was surprising since I have never programmed a database; however, I do like clean code, and databases offer a unique solution to cleaning up the code that really provides flexibility.
    • Just finished the latest installment of this on A Sane Approach to Primary Keys, and came away mystified. There are about 8 rules of thumb presented in the article, leaving a relatively inexperienced database programmer gasping for metaphorical air after finishing. This article definitely covered a lot of important points for database design, but all those rules definitely hurt it's excellence as, well, there are just so many. Without having some experience at designing a system, these rules have no real-world impact to the reader at a level such as mine; they would imply a need for memorization, not provide a moment of inspiration. There is some great stuff in the article, backed up by examples, but the rules just killed me.

Definite times at retardism:
  • The HAVING clause. When attempting to do that section of the sqlzoo.net tutorial, I hit several syntax errors before looking it up on a few sites. The first to tell me that you needed a GROUP BY clause proceeding it were the MySQL Reference Docs, and then it clicked.
  • Self-joins. "huh? what is it doing?!? why does that work!?"
  • Third Normal Form?

Moments of Inspiration (AHAH!):
  • Hay WOW the * means ALL COLUMNS!
  • Joins. They're one thing to understand as sets and such, but quite another to actually use and comprehend. They clicked intellectually with the Visual Joins, and practically during How to do Joins
  • First Normal form means I don't have to repeat myself all over the place! That's so cool!