Retrieving Data Using the MySQL SELECT Statement

• By Chris Newman
• Aug 18, 2006
• Sample Chapter is provided courtesy of Sams

This chapter is from the book
Sams Teach Yourself MySQL in 10 Minutes

In this lesson, you learn how to use a SELECT statement to fetch records from a MySQL database.

The SELECT Statement

The first SQL command you will learn, and the one you will use most frequently, is SELECT. In this lesson, you begin by learning how to fetch data records from a single table.

A SELECT statement begins with the SELECT keyword and is used to retrieve information from MySQL database tables. You must specify the table name to fetch data from—using the FROM keyword—and one or more columns that you want to retrieve from that table.

Retrieving Individual Columns

If you execute the following SQL statement using mysql, the output produced will be as shown:

mysql> SELECT name
    -> FROM customers;
+-------------------------+
| name                    |
+-------------------------+
| Presidents Incorporated |
| Science Corporation     |
| Musicians of America    |
+-------------------------+
3 rows in set (0.02 sec)

The customers table contains three records. In this statement, we tell MySQL to fetch the value of the name column; this is displayed for every record in the table.

The data displayed is not ordered. Usually records are retrieved in the same order in which they were inserted into the database. In this example, the company names are displayed in the order in which they were inserted in the sample table-creation script.

A SELECT statement will return every row from the table unless you tell it otherwise. You will learn how to do this, by putting a filter on the query, in the next lesson.

Retrieving Multiple Columns

Now you'll try another simple SELECT statement, this time on the products table. You can retrieve the values from two columns in the same query by specifying a list of columns after the SELECT keyword, separating them with a comma.

mysql> SELECT name, price
    -> FROM products;
+----------------+-------+
| name           | price |
+----------------+-------+
| Small product  |  5.99 |
| Medium product |  9.99 |
| Large product  | 15.99 |
+----------------+-------+
3 rows in set (0.01 sec)

The columns in the output appear in the order given in the query. To add the weight column to the data retrieved, add it to the end of the list of columns selected, as follows:

mysql> SELECT name, price, weight
    -> FROM products;
+----------------+-------+---------+
| name           | price |  weight |
+----------------+-------+---------+
| Small product  |  5.99 |    1.50 |
| Medium product |  9.99 |    4.50 |
| Large product  | 15.99 |    8.00 |
+----------------+-------+---------+
3 rows in set (0.00 sec)

Retrieving All Columns

If you want to retrieve the data from every column in a table, you do not need to specify each column name after the SELECT keyword. Use the asterisk character (*) in place of a column list in a SELECT statement to instruct MySQL to return every column from the specified table.

The following query retrieves every column and row from the products table:

mysql> SELECT *
    -> FROM products;
+------+----------------+--------+-------+
| code | name           | weight | price |
+------+----------------+--------+-------+
| MINI | Small product  |   1.50 |  5.99 |
| MIDI | Medium product |   4.50 |  9.99 |
| MAXI | Large product  |   8.00 | 15.99 |
+------+----------------+--------+-------+
3 rows in set (0.00 sec)

Note that the output produced is exactly the same, as if you had specified each column in the query by name, like this:

mysql> SELECT code, name, weight, price
    -> FROM products;
+------+----------------+--------+-------+
| code | name           | weight | price |
+------+----------------+--------+-------+
| MINI | Small product  |   1.50 |  5.99 |
| MIDI | Medium product |   4.50 |  9.99 |
| MAXI | Large product  |   8.00 | 15.99 |
+------+----------------+--------+-------+
3 rows in set (0.00 sec)

When you use SELECT *, columns are displayed in the order they occur in the database table—the order in which columns were specified when the table was created. You will learn how to create database tables in Lesson 14 "Creating and Modifying Tables."

Compare the order of the columns in the result of the previous query to the output produced by the DESCRIBE command for products.

mysql> DESCRIBE products;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| code   | varchar(10)  |      |     |         |       |
| name   | varchar(40)  |      |     |         |       |
| weight | decimal(6,2) |      |     | 0.00    |       |
| price  | decimal(6,2) |      |     | 0.00    |       |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Mistakes in a SELECT Statement

Before long, you will mistype a SELECT statement—if you have not done so already! Here you look at some of the error messages MySQL gives when you make a mistake.

If you try to select data from a table that does not exist, MySQL gives an error message. In this example, you attempted to select from a table named product instead of products:

mysql> SELECT *
    -> FROM product;
ERROR 1146 (42S02): Table sampdb.product' doesn't exist

If you specify a column name that does not exist in the selected table, you will see the following error message:

mysql> SELECT name
    -> FROM customer_contacts;
ERROR 1054 (42S22): Unknown column 'name' in 'field list'

In this case, the customer_contacts table does not have a name column—it has separate first_name and last_name columns.

If you make a syntax error—that is, when MySQL cannot understand the SELECT statement because things do not appear in the order that it expects them to—the error message looks like the following:

mysql> SELECT first_name, last name,
    -> FROM customer_contacts;
ERROR 1064 (42000): You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for
the right syntax to use near 'FROM customer_contacts' at line 2

In the previous example, note the comma after last_name. When MySQL sees this, it expects another column name to follow, but instead the next word is FROM. Because you cannot use a SQL keyword as a column name, this causes the syntax error as shown.

MySQL can also throw up a syntax error if you misspell a keyword. In the following example, the keyword FROM was mistyped as FORM. The error displayed indicates that MySQL does not know what purpose the word FORM serves in the SQL statement, so it cannot execute this query.

mysql> SELECT name
    -> FORM products;
ERROR 1064 (42000): You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for
the right syntax to use near 'products' at line 2

Consider the error in the following statement, in which you mistyped a two-word column name by leaving out the underscore character in the name. MySQL does not allow column names to contain a space, so an underscore is often used to separate words.

mysql> SELECT_first name, last name
    -> FROM customer_contacts;
ERROR 1054 (42S22): Unknown column 'last' in 'field list'

In this example, MySQL gives an unknown column error instead of a syntax error. The actual way MySQL interprets this is to select a column named last and give it an alias name, so there's actually no error in the statement syntax. Don't worry about the details of this for now—it's covered in Lesson 12, "Creating Advanced Joins."

Retrieving Database Information

To construct a valid SELECT statement, you need to know how a database is organized. The SHOW command is used to retrieve information about database components.

Retrieving a List of Databases

Use the SHOW DATABASES command to retrieve a list of databases that you have access to. Execute the SHOW command just like a SELECT statement from the mysql program.

mysql> SHOW DATABASES;
+-------------------+
| Database          |
+-------------------+
| mysqlin10         |
| mydb              |
+-------------------+
2 rows in set (0.00 sec)

Retrieving a List of Tables

When you have connected to a database with the use command in mysql, you can obtain a list of tables in that database with the SHOW TABLES command.

mysql> SHOW TABLES
+---------------------+
| Tables_in_mysqlin10 |
+---------------------+
| customer_contacts   |
| customers           |
| order_lines         |
| orders              |
| products            |
+---------------------+
5 rows in set (0.00 sec)

If you are connected to one database but want to list the tables in another, you can use a FROM clause with SHOW TABLES.

mysql> SHOW TABLES FROM sampdb;
+---------------------+
| Tables_in_sambdb    |
+---------------------+
| customer_contacts   |
| customers           |
| order_lines         |
| orders              |
| products            |
+---------------------+
5 rows in set (0.00 sec)

Retrieving a List of Columns

To retrieve the table structure for a database table, use the SHOW COLUMNS command using the table name in the FROM clause.

mysql> SHOW COLUMNS FROM products;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| code   | varchar(10)  |      |     |         |       |
| name   | varchar(40)  |      |     |         |       |
| weight | decimal(6,2) |      |     | 0.00    |       |
| price  | decimal(6,2) |      |     | 0.00    |       |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)