Learning MySQL

The SELECT Statement and Basic Querying Techniques

Up to this point, you’ve learned how to install and configure MySQL, and how to use the MySQL monitor. Now that you understand the music database, you’re ready to start exploring its data and to learn the SQL language that’s used by all MySQL clients. In this section, we introduce the most commonly used SQL keyword, and the only one that reads data from a database: the SELECT keyword. We also explain some basic elements of style and syntax, and the features of the WHERE clause, Boolean operators, and sorting (much of this also applies to our later discussions of INSERT, UPDATE, and DELETE). This isn’t the end of our discussion of SELECT; you’ll find more in Chapter 7, where we show you how to use its advanced features.

Single Table SELECTs

The most basic form of SELECT reads the data in all rows and columns from a table. Start the monitor and choose the music database:

mysql> use music;
Database changed

Let’s retrieve all of the data in the artist table:

mysql> SELECT * FROM artist;
+-----------+---------------------------+
| artist_id | artist_name               |
+-----------+---------------------------+
|         1 | New Order                 |
|         2 | Nick Cave & The Bad Seeds |
|         3 | Miles Davis               |
|         4 | The Rolling Stones        |
|         5 | The Stone Roses           |
|         6 | Kylie Minogue             |
+-----------+---------------------------+
6 rows in set (0.08 sec)

The output has six rows, and each row contains the values for the artist_id and artist_name columns. We now know that there are six ...