Retrieving Individual Columns

We'll start with a simple SQL SELECT statement, as follows:

• Input

SELECT prod_name
FROM products;

• Analysis

The previous statement uses the SELECT statement to retrieve a single column called prod_name from the products table. The desired column name is specified right after the SELECT keyword, and the FROM keyword specifies the name of the table from which to retrieve the data. The output from this statement is shown in the following:

• Output

+-----------------+
| prod_name       |
+-----------------+
| .5 ton anvil    |
| 1 ton anvil     |
| 2 ton anvil     |
| Oil can         |
| Fuses           |
| Sling           |
| TNT (1 stick)   |
| TNT (5 sticks)  |
| Bird seed       |
| Carrots         |
| Safe            |
| Detonator       |
| JetPack 1000    |
| JetPack 2000    |
+-----------------+

Note

Unsorted Data If you tried this query yourself, you might have discovered that the data was displayed in a different order than shown here. If this is the case, don't worryit is working exactly as it is supposed to. If query results are not explicitly sorted (we'll get to that in the next chapter), data will be returned in no order of any significance. It might be the order in which the data was added to the table, but it might not. As long as your query returned the same number of rows, then it is working.

A simple SELECT statement like the one just shown returns all the rows in a table. Data is not filtered (so as to retrieve a subset of the results), nor is it sorted. We'll discuss these topics in the next few chapters.

Note

Terminating Statements Multiple SQL statements must be separated by semicolons (the ; character). MySQL (like most DBMSs) does not require that a semicolon be specified after single statements. Of course, you can always add a semicolon if you wish. It'll do no harm, even if it isn't needed.

If you are using the mysql command-line client, the semicolon is always needed (as was explained in Chapter 2, "Introducing MySQL").

Note

SQL Statements and Case It is important to note that SQL statements are not case sensitive, so SELECT is the same as select, which is the same as Select. Many SQL developers find that using uppercase for all SQL keywords and lowercase for column and table names makes code easier to read and debug.

However, be aware that while the SQL language is not case sensitive, identifiers (the names of databases, tables, and columns) might be. In MySQL 4.1 and earlier, identifiers were case sensitive by default, and as of MySQL 4.1.1, identifiers are not case sensitive by default.

As a best practice, pick a case convention, and use it consistently.

Tip

Use of White Space All extra white space within a SQL statement is ignored when that statement is processed. SQL statements can be specified on one long line or broken up over many lines. Most SQL developers find that breaking up statements over multiple lines makes them easier to read and debug.