Getting Started with SQL
10/20/2000Hopefully, 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:
- adding data,
- finding data, and
- changing data.
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.
Database tools
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:
|
Related: Uploading, Saving and Downloading Binary Data in a MySQL Database  
|
- Microsoft Access -- Turn off the flamethrower! Yes, it's Microsoft; and yes, it's a toy database. But Access is cheap, probably on your desktop if you're a Windows user, and easy to install and use. There's no shame in learning SQL with Access ... just don't use it as a production database!
- MySQL -- This is one of the most popular open source (GPL) database tools there is. It is also a natural companion to PHP for web-enabled database sites. You've got no excuse for not giving this one a chance.
- PostgreSQL -- Probably the most sophisticated open source SQL database there is. It may be a bit intimidating, but it promises to be one serious database contender in the real world production environment. (We'll be focusing specifically on PostgreSQL in a future article)
- Oracle -- It looks pricey, but it is also one of the industry standards for the database community. Plus there's a cheaper Personal Edition as well as the developer license for Oracle development partners. Neither is free, but knowing Oracle will pay for itself many times over in the commercial world.
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.
Database terms
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.
Next steps
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.
