Databases Reference
In-Depth Information
Again, what's important is getting familiar with the columns in each table, as we'll make
use of these frequently later when we're learning about querying. Notice also that be-
cause all of these three entities are weak, each table contains the primary key columns
from the table it's related to. For example, the
track
table contains
artist_id
,
album_id
, and
track_id
, because the combination of all three is required to uniquely
identify a track.
In the next section, we show you how to explore the data that's stored in the
music
database and its tables.
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 ele-
ments 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 artists in our database and can
see the names and identifiers for these artists.
