The SELECT Statement and Basic Querying Techniques | 139

A simple
SELECT
statement has four components. The keyword SELECT. The columns to be displayed. In our first example, we asked for all columns by using the asterisk (
*
) symbol as a wildcard character. The keyword FROM. 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 inset 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 fora 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 Well 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

Download 4.24 Mb.

Share with your friends: