Linux DevCenter    
 Published on Linux DevCenter (http://www.linuxdevcenter.com/)
 See this if you're having trouble printing code examples


aboutSQL

Getting Started with SQL

10/20/2000

Related:

What's the Big Deal about SQL

Getting Started with SQL

OpenBSD as a Database Server

Uploading, Saving and Downloading Binary Data in a MySQL Database

MySQL.com


Previous Features

More from the Linux DevCenter

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:

  1. adding data,
  2. finding data, and
  3. 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:

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:

What's the Big Deal about SQL

OpenBSD as a Database Server

Uploading, Saving and Downloading Binary Data in a MySQL Database

MySQL.com


Discuss this article in the O'Reilly Network Linux Forum.

Return to the Linux DevCenter.

 

Copyright © 2009 O'Reilly Media, Inc.