Basic SQL - Learning MySQL - page 140

A simple SELECT statement has four components:
1. The keyword SELECT .
2. The columns to be displayed. In our first example, we asked for all columns by
using the asterisk ( * ) symbol as a wildcard character.
3. The keyword FROM .
4. The table name; in this example, the table name is artist .
Putting all this together, we've asked for all columns from the artist table, and that's
what MySQL has returned to us.
Let's try another simple SELECT . This time, we'll retrieve all columns from the album
table:
mysql> SELECT * FROM album;
+-----------+----------+------------------------------------------+
| artist_id | album_id | album_name |
+-----------+----------+------------------------------------------+
| 2 | 1 | Let Love In |
| 1 | 1 | Retro - John McCready FAN |
| 1 | 2 | Substance (Disc 2) |
| 1 | 3 | Retro - Miranda Sawyer POP |
| 1 | 4 | Retro - New Order / Bobby Gillespie LIVE |
| 3 | 1 | Live Around The World |
| 3 | 2 | In A Silent Way |
| 1 | 5 | Power, Corruption & Lies |
| 4 | 1 | Exile On Main Street |
| 1 | 6 | Substance 1987 (Disc 1) |
| 5 | 1 | Second Coming |
| 6 | 1 | Light Years |
| 1 | 7 | Brotherhood |
+-----------+----------+------------------------------------------+
13 rows in set (0.03 sec)
We have 13 albums in our database, and the output has the same basic structure as our
first example.
The second example gives you an insight into how the relationships between the tables
work. Consider the first row of the results—for the album “Let Love In,” which is by
the artist with the artist_id value of 2. If you inspect the output of our first example
that retrieved data from the artist table, you'll note that the matching artist is “Nick
Cave & The Bad Seeds.” So, Nick Cave recorded Let Love In . You'll also notice that
the albums we own for a given artist each have a number in the album_id column. You
can see, for example, that we own seven albums by the artist with an artist_id of 1.
We'll discuss how to write queries on relationships between tables later in this chapter
in “Joining Two Tables.”
Notice also that we have several different albums with the same album_id . This isn't a
problem, since album_id is only a weak key; an album is uniquely identified by the