Difference between revisions of "Retrieving Data From a MySQL Database"

Revision as of 19:07, 9 October 2007

Just a database system would be useless without some way to write data to the database files, a database would be similarly useless if there were no way to extract the stored data. Amongst the available SQL statements, one of the most frequently used is the SELECT statement. The purpose of the SELECT statement is to retrieve data from a database table based on specified criteria. In this chapter we will cover the use of the SELECT statement in detail.

Retrieving a Single Column

The most basic of SELECT statements simply retrieves a single column of all the rows of a table. The following SQL statements select a database named extract all the product_description column entries in the product table:

USE MySampleDB;
SELECT product_description FROM product;

Once executed, this command will display a list of every product description contained in the product table:

+-------------------+
| prod_desc         |
+-------------------+
| CD Writer         |
| Cordless Mouse    |
| SATA Disk Drive   |
| Ergonomic Keyboard|
+-------------------+
4 rows in set (0.00 sec)

Using SELECT to Retrieve Mutiple Columns

So far we have seen how easy it is to extract a single column from each row of a table. In the real world, it is more likely that information from more than one column will need to be retrieved. Fortunately the SELECT statement makes this task easy too. In fact, all that needs to be done is to specify the columns names after the SELECT statement, each separated by a comma. For example, to retrieve data from three columns in our database table:

SELECT prod_code, prod_name, prod_desc FROM product;

The above command will generate the following output if executed from within the mysql tool:

+-----------+--------------------------+-------------------+
| prod_code | prod_name                | prod_desc         |
+-----------+--------------------------+-------------------+
|         1 | CD-RW Model 4543         | CD Writer         |
|         2 | EasyTech Mouse 7632      | Cordless Mouse    |
|         3 | WildTech 250Gb 1700      | SATA Disk Drive   |
|         4 | Microsoft 10-20 Keyboard | Ergonomic Keyboard|
+-----------+--------------------------+-------------------+
4 rows in set (0.01 sec)

Whilst this approach works fine if you do not want to display all columns in a table, it can become cumbersome in situations where a table contains many columns and you want to list all columns. An easier way to achieve this than specifying every column is to use the wildcard symbol (*) in place of the column names. For example:

SELECT * FROM product;

Restricting Number of Results

When retrieving data from a table it is possible to limnit the number of results that are returned by the SELECT statement using the LIMIT keyword. This keyword is followed by a number indicating how may rows are to be retrieved:

SELECT * FROM product LIMIT 10;

The above command will retrieve only 10 rows from the database table. Both the start and end rows can be specified for reading further into a table. The following example, therefore, extracts rows 10 through 15 of the table:

SELECT * FROM product LIMIT 10, 15;

Eliminating Duplicate Values from Results

It is not unusual for a column value combination to be duplicated throughout multiple rows. This means that when retrieving data, and particularly when retrieving data for a single column in a table, that duplicated values may appear in the results. For example:

SELECT prod_desc FROM product;
+-------------------+
| prod_desc         |
+-------------------+
| CD Writer         |
| SATA Disk Drive   |
| Cordless Mouse    |
| SATA Disk Drive   |
| Ergonmoc Keyboard |
| CD Writer         |
+-------------------+

As we can see from in the above output, our fictitious on-line store sells more than one type of disk drive and CD writer. Whilst the product codes and names are likely to be unique, the descriptions have duplications. If we wanted to get a list of product descriptions devoid of duplications we would use the DISTINCT keyword:

SELECT DISTINCT prod_desc FROM product;

This would result in the following output:

+-------------------+
| prod_desc         |
+-------------------+
| CD Writer         |
| SATA Disk Drive   |
| Cordless Mouse    |
| Ergonmoc Keyboard |
+-------------------+

Summary

In the chapter we have looked at the ease with which data can be retrieved from a database using the SELECT statement. With the basics covered, it is time to move on to more advance data retrieval topics.

Now that we have covered the basics of retrieving data using the SELECT statement, the next task is to look at Sorting Data Retrieved from a MySQL Database.