|
Related: Uploading, Saving and Downloading Binary Data in a MySQL Database  
|
Hopefully, the inaugural column convinced you that SQL is a skill you want to have under your belt. In case you missed it, here's a quick synopsis: SQL is a database manipulation language, pure and simple. It is a rich language -- it has a number of commands for managing the database structure itself, powerful functions, many data types, and other useful features -- but at its most basic level, SQL is simply about three things:
Nothing too mind-bending there! Of course there is plenty of power under the hood for gurus, but even a newbie can get an enormous amount of work accomplished with a few simple commands. So where to begin...? A good first step is choosing a SQL environment.
Before we can talk about SQL commands, we need an environment that understands SQL commands -- basically, we need a relational database management system, or RDBMS. And you thought I was going to say we need a database! Well, we do need a database -- but databases (files or filesystems) are created by RDBMS tools like Oracle, MySQL, and all the other programs that you probably call a "database." An RDBMS provides an environment that can be used to create and manipulate databases as well as the tools to manipulate the data in the database. One ground rule for aboutSQL -- when I say database, I always mean a relational database. For the record, there are other models like network, hierarchical, and object, to name a few, but SQL is all about relational databases.
Almost all RDBMS tools implement some version of the SQL standard and, for our purposes, should be interchangeable. You already know the names of a lot of them (e.g. IBM DB2, Sybase). For our purposes I'll assume you're using some sort of personal database, as opposed to your production database server. In any case, the details shouldn't matter too much. But if you're looking for an RDBMS to use as a learning environment, here are the ones that I'd suggest, in order of preference:
Once you have one of these installed, you're almost ready to try your hand at SQL. But first I need to make sure you understand what I'm talking about when I start throwing around unfamiliar words. We'll start with the database basics.
Relational databases are pretty easy to understand if you think about each one as a set of one or more tables of data. In fact, a table is exactly the term used to describe a collection of data in a database. The table below is a representation of a simple database of musical recordings.
| ID | Title | Artist | Year |
| 1 | Pet Sounds | The Beach Boys | 1966 |
| 2 | Security | Peter Gabriel | 1990 |
| 3 | The Way it Is | Bruce Hornsby | 1986 |
| 4 | Joshua Judges Ruth | Lyle Lovett | 1992 |
The collection of data is a table that could be one of many related tables in a single database. The horizontal green row is typically called a record in the database. The columns of data, such as the years (in bold) are typically called fields. So '1996' is the value of the Year field of record number 1 in this database. There's plenty of other terminology to introduce, but that should do for now.
In the next series of columns, we'll start with the most important SQL command of all -- SELECT. In the meantime, get a database installed and be ready to start experimenting next week as you learn aboutSQL.
John Paul Ashenfelter is president and CTO of TransitionPoint.com, a technology development and analysis group focusing on web database applications.
Read more aboutSQL columns.
Related:
Uploading, Saving and Downloading Binary Data in a MySQL Database
Discuss this article in the O'Reilly Network Linux Forum.
Return to the Linux DevCenter.
Copyright © 2007 O'Reilly Media, Inc.