Learning Mysql

CHAPTER 7
Advanced Querying
Over the previous two chapters, you’ve completed an introduction to the basic features of querying and modifying databases with SQL. You should now be able to create,
modify, and remove database structures, as well as work with data as you read, insert,
delete, and update entries. Over the next three chapters, we’ll look at more advanced concepts. You can skim these chapters and return to read them thoroughly when you’re comfortable with using MySQL.
This chapter teaches you more about querying, giving you skills to answer complex information needs. You’ll learn how to Use nicknames, or aliases, in queries to save typing and allow a table to be used more than once in a query Aggregate data into groups so you can discover sums, averages, and counts Join tables indifferent ways Use nested queries Save query results invariables so they can be reused in other queries Understand why MySQL supports several table types
Aliases
Aliases are nicknames. They give you a shorthand way of expressing a column, table,
or function name, allowing you to Write shorter queries Express your queries more clearly Use one table in two or more ways in a single query Access data more easily from programs (for example, from PHP scripts, as discussed in Chapter 14)
223

• 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 inset 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 fora 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

Download 4.24 Mb.

Share with your friends: