Selecting Data from Your MySQL Tables

The fun part of using a database is getting the data out of your
tables. This sample chapters is part of a 6-part set of lessons that teach you
how to get data into, out of, and removed from your tables. No
knowledge of the SQL syntax is assumed, but when you're through with
this lesson you will be able to get everything out of your tables,
sorted and delineated in exactly the manner you wanted.

This chapter is from the book

This chapter is from the book

You've finally populated your tables with some data; now it's time
to learn how to get it back out! The SQL command for retrieving data is SELECT,
and in this hour you'll learn how to use it to select everything in your
table or just specific pieces of information from that table.

In this hour, you will learn

The SELECT command syntax

How to order and limit your results

How to use basic functions in SELECT expressions

How to use the WHERE clause

Using the SELECT Command

SELECT is the SQL command used to retrieve records. This command
syntax can be totally simplistic or very complicated. As you become more
comfortable with database programming, you will learn to enhance your
SELECT statements, ultimately making your database do as much work as
possible and not overworking your programming language of choice.

As you can see, MySQL creates a lovely table as part of the result set, with
the names of the columns along the first row. If you want to select only
specific columns, replace the * with the names of the columns,
separated by commas. The following statement selects just the name_id,
firstname and lastname fields from the master_name
table.

A useful expression used with SELECT is DISTINCT, which
(not surprisingly) will return distinct occurrences in a result set. For
example, the master_name table has more than one person with the last
name of "Smith". If you wanted to select last names without
repeating results, you would use DISTINCT:

Ordering SELECT Results

By default, results of SELECT queries are ordered as they appear in
the table. If you want to order results a specific way, such as by date, ID,
name, and so on, specify your requirements using the ORDER BY clause.
In the following statement, results are ordered by lastname:

When selecting results from a table without specifying a sort order, the
results may or may not be ordered by their key value. This occurs because MySQL
reuses the space taken up by previously deleted rows. In other words, if you add
records with ID values of 1 through 5, then delete the record
with ID number 4, then add another record (ID number 6), the
records may appear in the table in this order: 1, 2,
3, 6, 5.

The default sorting of ORDER BY results is ascending (ASC);
strings sort from A to Z, integers start at 0, dates sort from oldest to newest.
You can also specify a descending sort, using DESC:

You're not limited to sorting by just one fieldyou can specify as
many fields as you want, separated by commas. The sorting priority is by list
order, so if you use ORDER BY lastname, firstname, the results will be
sorted by lastname, then by firstname:

Limiting Your Results

You can use the LIMIT clause to return only a certain number of
records in your SELECT query result. There are two requirements when
using the LIMIT clause: offset and number of rows. The offset is the
starting position, and the number of rows should be self-explanatory.

TIP

For the most part, counting while programming always starts at 0, not 1. For
example: 0, 1, 2, 3 instead of 1, 2, 3, 4.

An example would be to select only the first 5 records from
master_name, ordered by lastname:

The LIMIT clause can be quite useful in an actual application. For
example, you can use the LIMIT clause within a series of
SELECT statements to essentially page through results in steps:

SELECT * FROM master_name ORDER BY lastname LIMIT 0, 5;

SELECT * FROM master_name ORDER BY lastname LIMIT 6, 5;

SELECT * FROM master_name ORDER BY lastname LIMIT 11,
5;

If you specify an offset and number of rows in your query and no results are
found, you won't see an errorjust an empty result set. For example,
if the master_name table contains only 9 records, a query with a
LIMIT offset of 11 will produce no results:

In Web-based applications, when lists of data are displayed with links like
"previous 10" and "next 10," it's a safe bet that a
LIMIT clause is at work.

Using Some Aggregate Functions with SELECT

MySQL has many built-in functions that allow you to perform all sorts of
operations on integers, strings, and dates. These functions will be explained in
detail in later hours, but a few of the simpler functions described here should
whet your appetite.

If you want to know how many records are in your table, you could just select
all records and look for the response that says "5 rows in set," or
however many there are. If you have a million rows, selecting all one million
rows just to see how many there are is not the speediest way to find your
answer. Instead, you can use the COUNT() function, which counts all
non-NULL values in a given column. If you have a primary key defined in
your table, you know that field can't be NULL. Knowing this, if
you use COUNT() on the primary key, you can obtain a count of all the
records in the table.

Using a test table containing almost 4000 rows of integer and string data, a
select of all the rows took 0.03 seconds.

COUNT() returns only one row in the result set. This result is equal
to the actual number of rows in the table, which is what your query was asking.
The column header for the result set is COUNT(id), the expression used
in the SELECT query.

You can use AS to produce your own column headersnot just when
using functions but during any SELECT statement. For example, to name
your column row_count, use:

You can use COUNT() in conjunction with the DISTINCT
expression you learned earlier. Instead of selecting the actual distinct last
names from the master_name table, you can select the count of those
names:

Using MAX(name_id) would not produce an accurate count of the number
of records in the master_name table. Although the primary key
auto-increments, records could have been deleted throughout the table's
existence. In the current master_name table, there are only 9 records,
but the value of MAX(name_id) is 45 because numerous dummy records have
been added and deleted. Always use COUNT() and not MAX() to
find the number of records in a table.

If the field is a date, MIN() will return the earliest date, while
MAX() returns the latest date: