Using the SELECT Command

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.

The most basic SELECT syntax looks like this:

 SELECT expressions_and_columns FROM table_name [WHERE some_condition_is_true] [ORDER BY some_column [ASC | DESC]] [LIMIT offset, rows] 

Start with the first line:

 SELECT expressions_and_columns FROM table_name 

One handy expression is the * symbol, which stands for "everything." So, to select "everything" (all rows, all columns) from the grocery_inventory table, your SQL statement would be

 SELECT * FROM grocery_inventory; 

Depending on how much data you inserted into the grocery_inventory table during the previous hour, your results will vary, but it might look something like this:

 mysql> select * from grocery_inventory; +---+-----------------------+------------------------+-----------+---------+ | id| item_name             | item_desc              | item_price| curr_qty| +---+-----------------------+------------------------+-----------+---------+ |  1| Apples                | Beautiful, ripe apples.|       0.25|     1000| |  2| Bunches of Grapes     | Seedless grapes.       |       2.99|      500| |  3| Bottled Water (6-pack)| 500ml spring water.    |       2.29|      250| +---+-----------------------+------------------------+-----------+---------+ 3 rows in set (0.00 sec) 

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

 mysql> select id, item_name, curr_qty from grocery_inventory; +----+------------------------+----------+ | id | item_name              | curr_qty | +----+------------------------+----------+ |  1 | Apples                 |     1000 | |  2 | Bunches of Grapes      |      500 | |  3 | Bottled Water (6-pack) |      250 | +----+------------------------+----------+ 3 rows in set (0.00 sec) 

Ordering SELECT Results

By default, results of SELECT queries are ordered as they were inserted into the table, and shouldn't be relied upon as a meaningful ordering system. 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 item_name:

 mysql> select id, item_name, curr_qty from grocery_inventory     -> order by item_name; +----+------------------------+----------+ | id | item_name              | curr_qty | +----+------------------------+----------+ |  1 | Apples                 |     1000 | |  3 | Bottled Water (6-pack) |      250 | |  2 | Bunches of Grapes      |      500 | +----+------------------------+----------+ 3 rows in set (0.04 sec) 

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, delete the record with ID number 4, then add another record (ID number 6), the records might 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:

 mysql> select id, item_name, curr_qty from grocery_inventory     -> order by item_name desc; +----+------------------------+----------+ | id | item_name              | curr_qty | +----+------------------------+----------+ |  2 | Bunches of Grapes      |      500 | |  3 | Bottled Water (6-pack) |      250 | |  1 | Apples                 |     1000 | +----+------------------------+----------+ 3 rows in set (0.00 sec) 

You're not limited to sorting by just one field you can specify as many fields as you want, separated by a comma. The sorting priority is the order in which you list the fields.

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.

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.

Suppose you had more than 2 or 3 records in the grocery_inventory table, and you wanted to select the id, name, and quantity of the first 3, ordered by curr_qty. In other words, you want to select the 3 items with the least inventory, the following single-parameter limit will start at the 0 position and go to the third record:

 mysql> select id, item_name, curr_qty from grocery_inventory     -> order by curr_qty limit 3; +----+------------------------+----------+ | id | item_name              | curr_qty | +----+------------------------+----------+ |  4 | Bananas                |      150 | |  3 | Bottled Water (6-pack) |      250 | |  2 | Bunches of Grapes      |      500 | +----+------------------------+----------+ 3 rows in set (0.00 sec) 

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:

1. SELECT * FROM grocery_inventory ORDER BY curr_qty LIMIT 0, 3;

2. SELECT * FROM grocery_inventory ORDER BY curr_qty LIMIT 3, 3;

3. SELECT * FROM grocery_inventory ORDER BY curr_qty LIMIT 6, 3;

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 grocery_inventory table contains only 6 records, a query with a LIMIT offset of 6 will produce no results:

 mysql> select id, item_name, curr_qty from grocery_inventory     -> order by curr_qty limit 6, 3; Empty set (0.00 sec) 

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