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


aboutSQL

Introducing SELECT

10/27/2000

One of the most important functions of any database application is finding the data that's in the database. We're going to spend the next few columns exploring the SQL SELECT command, the workhorse of most database applications. Hopefully you've got a database up and running so you can try this yourself as we work through the command.

Simple SELECT

We'll be using the simple database we started with last week as a starting point for our experiments with the SELECT command. We'll be SELECTing data from our tiny music database table (which we'll name MusicCollection for ease of reference):

MusicCollection
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

As was mentioned in the original article, SQL is very English-like. Commands typically consist of a verb, an object, and possibly a set of clauses that modify the object. So to find all of the musical artists in the database, we could say something like "Choose all the values from the Artist field of the database." The SQL translation of this sentence is

SELECT Artist FROM MusicCollection;

The is the most basic version of the SELECT command. It returns an entire column of data from the database. In this case the results would look something like the following.

Artist
--------------------
The Beach Boys
Peter Gabriel
Bruce Hornsby
Lyle Lovett

So what else can you do with SELECT? Plenty! You can retrieve multiple columns.

SELECT Artist,Title FROM MusicCollection;

which would return

Artist Title
-------------------- --------------------
The Beach Boys Pet Sounds
Peter Gabriel Security
Bruce Hornsby The Way it Is
Lyle Lovett Joshua Judges Ruth

You can also use a shortcut command to return ALL the columns of a database, basically displaying the entire thing. This is accomplished by using an asterisk (*) instead of any column names.

SELECT * FROM MusicCollection;

which would return

ID Artist Title 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

You can even get really fancy and assign a field a new temporary name, or an alias. This technique is normally used when there are fields in two or more tables with the same name, which is a topic for a later column -- but just for fun (for the sake of completeness), we could do something like the following:

SELECT Title AS AlbumName FROM MusicCollection;


AlbumName
--------------------
Pet Sounds
Security
The Way it Is
Joshua Judges Ruth

So here's what we know so far about the SELECT statement:

  1. SELECT returns one or more columns from a data table chosen by field name.
  2. The FROM clause identifies the database table to use as a source of data.
  3. The asterisk (*) is a shortcut for returning all field names.
  4. Column names can be aliased.

And to whet your appetite for future columns, we can use the SELECT statement to display data from more than one table. But that's a story for another column. With the space that's left this time, we've got another important topic to cover.

Sorting data with SELECT

SQL is designed to manipulate data, so you can safely assume that SQL has built-in tools for doing almost any sort of common data manipulation task. One exceedingly simple example is sorting data alphabetically. We can take any of the SELECT queries from the previous section and sort them in alphabetical order using the ORDER BY clause.

SELECT Artist, Title FROM MusicCollection ORDER BY Title;


Artist Title
-------------------- --------------------
Lyle Lovett Joshua Judges Ruth
The Beach Boys Pet Sounds
Peter Gabriel Security
Bruce Hornsby The Way it Is

Now we've got Title-sorted data as a result of the SELECT statement. There are plenty of variations on this theme! One possibility is that we can sort in reverse order

SELECT Artist, Title FROM MusicCollection ORDER BY Title DESC;


Artist Title
-------------------- --------------------
Bruce Hornsby The Way it Is
Peter Gabriel Security
The Beach Boys Pet Sounds
Lyle Lovett Joshua Judges Ruth

The DESC keyword is short for DESCending order. The ASCending keyword can also be used, but since it is the default behavior, it is almost always left out.

You can sort on several different columns, each in its own order:

SELECT Artist, Title FROM MusicCollection ORDER BY Title DESC, Artist ASC;


Artist Title
-------------------- --------------------
Bruce Hornsby The Way it Is
Peter Gabriel Security
The Beach Boys Pet Sounds
Lyle Lovett Joshua Judges Ruth

In this example, Title is ordered in descending alphabetical order and is then further sorted by Artist in ascending order (which has no effect in this case since all the artists and titles are distinct). We can even sort by columns that aren't displayed:

SELECT Artist, Title FROM MusicCollection ORDER BY Year;


Artist Title
-------------------- --------------------
Pet Sounds The Beach Boys
The Way it Is Bruce Hornsby
Security Peter Gabriel
Joshua Judges Ruth Lyle Lovett

This selection is properly sorted by Year, even though it wasn't displayed in the output of the SELECT statement.

Next steps

We've only just started getting your feet wet with data selection. So far, the syntax is

SELECT column_name(s) [AS alias] FROM table_name [ORDER BY column_name(s) (ASC | DESC) ]

where the statements in square brackets are optional.

Next, we're going to start filtering the results of the SELECT command using the WHERE clause. This is one of the most powerful tools/capabilities you'll run into 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.

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

Return to the Linux DevCenter.

 

Copyright © 2009 O'Reilly Media, Inc.