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 fromusing the FROM keywordand one or more columns that you want to retrieve from that table.

Keywords and Statements

A keyword is a word that is part of the SQL language. In the examples in this book, SQL keywords are always written in capitals, although they are not case sensitive.

A SQL statement begins with a keyword and can contain several more keywords that must appear in the correct, structured wayknown as the statement's syntax.

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) 

Terminating a Statement

Remember that the semicolon character is required to indicate the end of a SQL statement. Alternatively, you can use the go command or \g to tell mysql to execute a query.

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.

Order of Records

Although records are normally retrieved in the order in which they are inserted into the database, you cannot rely on a particular order being preserved. If your database is backed up and restored, or if a maintenance operation is performed on the database, MySQL might alter the order in which records are stored internally.

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) 

Formatting Queries

In the examples in this book, queries are formatted so that each clause of a SQL statement is on a separate line. The formatting does not affect the operation of a SQL statement; you can use carriage returns and whitespace to format a query however you want.

Although it might seem excessive to adopt a formatting style for these simple examples, as you add more clauses to a query and learn to build more complex SQL statements in subsequent lessons, formatting your queries in a readable way becomes very important.

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 tablethe 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) 

Query Results

The tabular layout that mysql produces when displaying the results of a query is a simple yet readable format for viewing small amounts of data. However, your screen has only a fixed width, so if you try to display too many columnsor if a SELECT * returns a lot of columnsthe characters that make up this table could wrap lines and create an unreadable mess.

Mistakes in a SELECT Statement

Before long, you will mistype a SELECT statementif 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 columnit has separate first_name and last_name columns.

Case Sensitivity

MySQL table names are case sensitive, but column names are not. A table named Products is different than products, and executing SELECT * FROM Products will produce an error in the sample database. However, specifying the name column as Name or NAME will not cause an error.

If you make a syntax errorthat is, when MySQL cannot understand the SELECT statement because things do not appear in the order that it expects them tothe 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 

Multiple Errors

If there are several errors in your query, MySQL will tell you only about the first one that it encounters. Of the three error types shown previously, MySQL will report a syntax error first, followed by a nonexistent table and finally a bad column name.

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 nowit's covered in Lesson 12, "Creating Advanced Joins."

Semantic Errors

The previous error is an example of a semantic error. The statement syntax is technically correct and is accepted by the MySQL interpreter, but the actual meaning of the statement is not what you intended it to be.

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) 

Describe

The DESCRIBE command is a shortcut for the SHOW COLUMNS command; DESCRIBE table is identical to SHOW COLUMNS FROM table.