Databases Reference
In-Depth Information
• Use special types of nested queries; these are the subject of “Nested Queries,”
discussed later in this chapter
Column Aliases
Column aliases are useful for improving the expression of your queries, reducing the
number of characters you need to type, and making it easier to work with languages
such as PHP. Consider a simple, not-very-useful example:
mysql>
SELECT artist_name AS artists FROM artist;
+---------------------------+
| artists |
+---------------------------+
| New Order |
| Nick Cave & The Bad Seeds |
| Miles Davis |
| The Rolling Stones |
| The Stone Roses |
| Kylie Minogue |
+---------------------------+
6 rows in set (0.00 sec)
The column
artist_name
is aliased as
artists
. You can see that in the output, the usual
column heading,
artist_name
, is replaced by the alias
artists
. The advantage is that
the alias
artists
might be more meaningful to users. Other than that, it's not very
useful, but it does illustrate the idea: for a column, you add the keyword
AS
and then
a string that represents what you'd like the column to be known as.
Now let's see column aliases doing something useful. Here's an example that uses a
MySQL function and an
ORDER BY
clause:
mysql>
SELECT CONCAT(artist_name, " recorded ", album_name) AS recording
-> FROM artist INNER JOIN album USING (artist_id)
-> ORDER BY recording;
+-------------------------------------------------------------+
| recording |
+-------------------------------------------------------------+
| Kylie Minogue recorded Light Years |
| Miles Davis recorded In A Silent Way |
| Miles Davis recorded Live Around The World |
| New Order recorded Brotherhood |
| New Order recorded Power, Corruption & Lies |
| New Order recorded Retro - John McCready FAN |
| New Order recorded Retro - Miranda Sawyer POP |
| New Order recorded Retro - New Order / Bobby Gillespie LIVE |
| New Order recorded Substance (Disc 2) |
| New Order recorded Substance 1987 (Disc 1) |
| Nick Cave & The Bad Seeds recorded Let Love In |
| The Rolling Stones recorded Exile On Main Street |
| The Stone Roses recorded Second Coming |
+-------------------------------------------------------------+
13 rows in set (0.03 sec)
