Using the SELECT Command


Using the SELECT Command

SELECT is the SQL command used to retrieve records from your tables. This command syntax can be totally simplistic or very complicated, depending on which fields you want to select, if you want to select from multiple tables, and what conditions you plan to impose. 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.

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] 

Look at 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 found in the grocery_inventory table, your results will vary, but the results 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, formatted 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

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) 

Did you Know

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, and 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 fieldyou 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 from your SELECT query result. There are two requirements when using the LIMIT clause: the offset and the number of rows. The offset is the starting position, and the number of rows should be self-explanatory.

Suppose you had more than two or three records in the grocery_inventory table, and you wanted to select the ID, name, and quantity of the first three, ordered by curr_qty. In other words, you want to select the three 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 travel through results in steps (first three items, next three items, next three items after that):

  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 errorjust an empty result set. For example, if the grocery_inventory table contains only six 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.



Sams Teach Yourself PHP MySQL and Apache All in One
Sams Teach Yourself PHP, MySQL and Apache All in One (4th Edition)
ISBN: 067232976X
EAN: 2147483647
Year: 2003
Pages: 333
Authors: Julie Meloni

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net