Querying the Database in MySQL

By: Sathya Narayana in MySQL Tutorials on 2010-10-24

You'll now have a closer look at the command you ran:

mysql> SELECT id, title, price, status FROM book;

Running this command produces output something like this:

+----+-------------------------------------------+-------+--------+
| id | title                                     | price | status |
+----+-------------------------------------------+-------+--------+
| 1 | Lord of the Things                         | 9.99  | P      |
| 1 | Mr Bunny's Guide to JDO                    | 14.99 | P      |
| 1 | Parachuting for You and Your Kangaroo      | 19.99 | P      |
+----+-------------------------------------------+-------+--------+
3 rows in set (0.04 sec)

The command asks for the columns called id, title, price, and status for all rows in the Book table. The general form for a SELECT statement that retrieves all of the rows in the table is as follows:

> SELECT Column1Name, .... , ColumnXName FROM TableName;

There's also a special form that returns all columns from a table, without you having to type the name for every column:

> SELECT * FROM TableName;

If you run this, you'll see that other columns for which you didn't specify a value are set to NULL.

Note

As a rule, you should avoid using SELECT * FROM except for testing or debugging purposes, unless you really do need every column from the table. Performance will be enhanced if you request only those fields you actually intend to use. Additionally, SELECT * offers no control over the order of the returned fields because they're returned in the order in which they were declared in the CREATE TABLE statement.

When retrieving data with a SELECT query, you can order the returned rows by adding an ORDER BY clause to the command. The ORDER BY statement is followed by the column that you want to sort on, and finally you specify whether to order highest to lowest (a descending sort as indicated by DESC) or lowest to highest (an ascending sort, as indicated by ASC). ASC is the default sort, so it's assumed if neither DESC nor ASC is specified. For instance, the following command displays books in order of price, highest price first:

mysql> SELECT price, title FROM book
          -> ORDER BY price DESC;

You'll see something like this:

+-------+-------------------------------------------+
| price | title                                     |
+-------+-------------------------------------------+
| 9.99  | Lord of the Things                        |
| 14.99 | Mr Bunny's Guide to JDO                   |
| 19.99 | Parachuting for You and Your Kangaroo     |
+-------+-------------------------------------------+

You'll see that the column order has changed because of how you've ordered the column names in the SELECT statement.

Previous
<< Inserting Data into Tables in MySQL

Next
Modifying data and using WHERE clause in MySQL >>