Learning MySQL (2007)
Part II. Using MySQL
Chapter 5. Basic SQL
SQL is the only database language in widespread use. Since it was first proposed in the early 1970s, it has been criticized, changed, extended, and finally adopted by all the players in the database market. The latest standard is SQL-2003—the 2003 denotes its release year—but the version supported by most database servers is more closely related to its predecessors, SQL-1999 and SQL-1992. MySQL supports most of the features of SQL-1992 and many from the newer SQL standards, but it also includes many nonstandard features that give more control over the database server and how it evaluates queries and returns results.
This chapter introduces the basics of MySQL’s implementation of SQL. We show you how to read data from a database with the SELECT statement, and how to choose what data is returned and the order it is displayed in. We also show you the basics of modifying your databases with theINSERT statement to add data, UPDATE to change, and DELETE to remove it. We also explain how to use the nonstandard SHOW TABLES and SHOW COLUMNS statements to explore your database.
Following our example-based approach, we use the music database designed in Chapter 4 to show you how to work with an existing database, and use basic SQL to read and write data. In Chapter 6, we’ll explain how to create the music database on your MySQL server. We’ll also show how you can create your own database and tables, and modify the structure of existing ones. In Chapters 7 and 8, you’ll learn about some advanced features of the SQL variant used by MySQL.
Using the Music Database
In Chapter 4, we showed you how we understood the requirements for storing a music collection and how we designed the music ER model. We also introduced the steps you take to convert an ER model to a format that makes sense for constructing a relational database. For convenience, we’ve reproduced the music database ER diagram in Figure 5-1. In this section, we show you the structure of the MySQL database that we created after converting the ER model into SQL statements. We don’t explain the SQL statements we used to create the database; that’s the subject ofChapter 6.
Figure 5-1. The ER diagram of the music database
To begin exploring the music database, connect to the MySQL monitor using the root MySQL account. For Mac OS X or Linux, run a terminal program, and in the terminal window type:
$ mysql --user=root --password=the_mysql_root_password
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.22
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
For Windows, click on the Start menu, then on the Run option, and then type cmd and press Enter. In the DOS or command window, type:
C:\> mysql --user=root --password=the_mysql_root_password
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.22
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
If you find that the monitor doesn’t start, check the instructions in Error Message About MySQL Executable Programs Not Being Found or Recognized” in Chapter 2 to see how to run it.
The structure of the music database is straightforward; it’s the simplest of our three sample databases. Let’s use the MySQL monitor to explore it. If you haven’t already, start the monitor using the instructions in Loading the Sample Databases” in Chapter 3. To choose the music database as your current database, type the following:
mysql> USE music;
Database changed
mysql>
You can check that this is the active database by typing in the SELECT DATABASE(); command:
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| music |
+------------+
1 row in set (0.00 sec)
mysql>
Now, let’s explore what tables make up the music database using the SHOW TABLES statement:
mysql> SHOW TABLES;
+-----------------+
| Tables_in_music |
+-----------------+
| album |
| artist |
| played |
| track |
+-----------------+
4 rows in set (0.01 sec)
MySQL reports that there are four tables, which map exactly to the four entities in Figure 5-1. The SHOW statement is discussed in more detail later in Exploring Databases and Tables with SHOW and mysqlshow.”
So far, there have been no surprises. Let’s find out more about each of the tables that make up the music database. First, let’s use the SHOW COLUMNS statement to explore the artist table:
mysql> SHOW COLUMNS FROM artist;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| artist_id | smallint(5) | NO | PRI | 0 | |
| artist_name | char(128) | NO | | | |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
The DESCRIBE keyword is identical to SHOW COLUMNS FROM, and can be abbreviated to just DESC, so we can write the previous query as follows:
mysql> DESC artist;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| artist_id | smallint(5) | NO | PRI | 0 | |
| artist_name | char(128) | NO | | | |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
Let’s examine the table structure more closely. As you’d expect from the ER model in Figure 5-1, the artist table contains two columns, artist_id and artist_name. The other information in the output shows the types of the columns—an integer of length 5 for artist_id and a character string of length 128 for artist_name—and whether the column is allowed to be NULL (empty), whether it’s part of a key, and the default value for it. You’ll notice that the artist_id has PRI in the Key column, meaning it’s part of the primary key for the table. Don’t worry about the details; all that’s important right now is the column names, artist_id and artist_name.
We’ll now explore the other three tables. Here are the SHOW COLUMNS statements you need to type:
mysql> SHOW COLUMNS FROM album;
+------------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------+------+-----+---------+-------+
| artist_id | int(5) | | PRI | 0 | |
| album_id | int(4) | | PRI | 0 | |
| album_name | char(128) | YES | | NULL | |
+------------+-----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> SHOW COLUMNS FROM track;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| track_id | int(3) | | PRI | 0 | |
| track_name | char(128) | YES | | NULL | |
| artist_id | int(5) | | PRI | 0 | |
| album_id | int(4) | | PRI | 0 | |
| time | decimal(5,2) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.02 sec)
mysql> SHOW COLUMNS FROM played;
+-----------+-----------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+-------------------+-------+
| artist_id | int(5) | | PRI | 0 | |
| album_id | int(4) | | PRI | 0 | |
| track_id | int(3) | | PRI | 0 | |
| played | timestamp | YES | PRI | CURRENT_TIMESTAMP | |
+-----------+-----------+------+-----+-------------------+-------+
4 rows in set (0.00 sec)
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 because 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 elements of style and syntax, and the features of the WHERE clause, Boolean operators, and sorting (much of this also applies to our later discussions ofINSERT, 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.
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 theartist 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 combination of its album_id and the primary key of its owning entity, which is artist_id.
You should now feel comfortable about choosing a database, listing its tables, and retrieving all of the data from a table using the SELECT statement. To practice, you might want to experiment with the university or flight databases you loaded in Chapter 3 in Loading the Sample Databases.” Remember that you can use the SHOW TABLES statement to find out the table names in these databases.
Choosing Columns
You’ve so far used the * wildcard character to retrieve all columns in a table. If you don’t want to display all the columns, it’s easy to be more specific by listing the columns you want, in the order you want them, separated by commas. For example, if you want only the artist_namecolumn from the artist table, you’d type:
mysql> SELECT artist_name FROM artist;
+---------------------------+
| artist_name |
+---------------------------+
| New Order |
| Nick Cave & The Bad Seeds |
| Miles Davis |
| The Rolling Stones |
| The Stone Roses |
| Kylie Minogue |
+---------------------------+
6 rows in set (0.01 sec)
If you want both the artist_name and the artist_id, in that order, you’d use:
mysql> SELECT artist_name,artist_id FROM artist;
+---------------------------+-----------+
| artist_name | artist_id |
+---------------------------+-----------+
| New Order | 1 |
| Nick Cave & The Bad Seeds | 2 |
| Miles Davis | 3 |
| The Rolling Stones | 4 |
| The Stone Roses | 5 |
| Kylie Minogue | 6 |
+---------------------------+-----------+
6 rows in set (0.00 sec)
You can even list columns more than once:
mysql> SELECT artist_id, artist_id FROM artist;
+-----------+-----------+
| artist_id | artist_id |
+-----------+-----------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
+-----------+-----------+
6 rows in set (0.06 sec)
Even though this appears pointless, it can be useful when combined with aliases in more advanced queries, as we show in Chapter 7.
You can specify databases, tables, and column names in a SELECT statement. This allows you to avoid the USE command and work with any database and table directly with SELECT; it also helps resolve ambiguities, as we show later in Joining Two Tables.” Consider an example: suppose you want to retrieve the album_name column from the album table in the music database. You can do this with the following command:
mysql> SELECT album_name FROM music.album;
+------------------------------------------+
| album_name |
+------------------------------------------+
| Let Love In |
| Retro - John McCready FAN |
| Substance (Disc 2) |
| Retro - Miranda Sawyer POP |
| Retro - New Order / Bobby Gillespie LIVE |
| Live Around The World |
| In A Silent Way |
| Power, Corruption & Lies |
| Exile On Main Street |
| Substance 1987 (Disc 1) |
| Second Coming |
| Light Years |
| Brotherhood |
+------------------------------------------+
13 rows in set (0.01 sec)
The music.album component after the FROM keyword specifies the music database and its album table. There’s no need to enter USE music before running this query. This syntax can also be used with other SQL statements, including the UPDATE, DELETE, INSERT, and SHOW statements we discuss later in this chapter.
Choosing Rows with the WHERE Clause
This section introduces the WHERE clause and explains how to use the Boolean operators to write expressions. You’ll see these in most SELECT statements, and often in other statements such as UPDATE and DELETE; we’ll show you examples later in this chapter.
WHERE basics
The WHERE clause is a powerful tool that allows you to choose which rows are returned from a SELECT statement. You use it to return rows that match a condition, such as having a column value that exactly matches a string, a number greater or less than a value, or a string that is a prefix of another. Almost all our examples in this and later chapters contain WHERE clauses, and you’ll become very familiar with them.
The simplest WHERE clause is one that exactly matches a value. Consider an example where we want to find out the details of the artist with the name “New Order.” Here’s what you type:
mysql> SELECT * FROM artist WHERE artist_name = "New Order";
+-----------+-------------+
| artist_id | artist_name |
+-----------+-------------+
| 1 | New Order |
+-----------+-------------+
1 row in set (0.00 sec)
MySQL returns all rows that match our search criteria—in this case, just the one row and all its columns. From this, you can see that the artist “New Order” has an artist_id of 1.
Let’s try another exact-match example. Suppose you want to find out the name of the artist with an artist_id value of 4. You type:
mysql> SELECT artist_name FROM artist WHERE artist_id = 4;
+--------------------+
| artist_name |
+--------------------+
| The Rolling Stones |
+--------------------+
1 row in set (0.00 sec)
In this example, we’ve chosen both a column and a row: we’ve included the column name artist_name after the SELECT keyword, as well as WHERE artist_id = 4.
If a value matches more than one row, the results will contain all matches. Suppose we ask for the names of all tracks with a track_id of 13; this retrieves the thirteenth song on every album that has at least that many songs. You type in:
mysql> SELECT track_name FROM track WHERE track_id = 13;
+------------------------------------------+
| track_name |
+------------------------------------------+
| Every Little Counts |
| Everyone Everywhere |
| Turn My Way [Olympia, Liverpool 18/7/01] |
| Let It Loose |
+------------------------------------------+
4 rows in set (0.02 sec)
The results show the names of the thirteenth track of different albums, so there must be 4 albums that contain at least 13 tracks If we could join the information we get from the track table with information we get from the album table, we could display the names of these albums. We’ll see how to perform this type of query later in Joining Two Tables.”
Now let’s try retrieving values in a range. This is simplest for numeric ranges, so let’s start by finding the names of all artists with an artist_id less than 5. To do this, type:
mysql> SELECT artist_name FROM artist WHERE artist_id < 5;
+---------------------------+
| artist_name |
+---------------------------+
| New Order |
| Nick Cave & The Bad Seeds |
| Miles Davis |
| The Rolling Stones |
+---------------------------+
4 rows in set (0.06 sec)
For numbers, the frequently used operators are equals (=), greater than (>), less than (<), less than or equal (<=), greater than or equal (>=), and not equal (<> or !=).
Consider one more example. If you want to find all albums that don’t have an album_id of 2, you’d type:
mysql> SELECT album_name FROM album WHERE album_id <> 2;
+------------------------------------------+
| album_name |
+------------------------------------------+
| Let Love In |
| Retro - John McCready FAN |
| Retro - Miranda Sawyer POP |
| Retro - New Order / Bobby Gillespie LIVE |
| Live Around The World |
| Power, Corruption & Lies |
| Exile On Main Street |
| Substance 1987 (Disc 1) |
| Second Coming |
| Light Years |
| Brotherhood |
+------------------------------------------+
11 rows in set (0.01 sec)
This shows us the first, third, and all subsequent albums for all artists. Note that you can use either <> or != for not-equal.
You can use the same operators for strings. For example, if you want to list all artists whose name appears earlier alphabetically than (is less than) 'M', use:
mysql> SELECT artist_name FROM artist WHERE artist_name < 'M';
+---------------+
| artist_name |
+---------------+
| Kylie Minogue |
+---------------+
1 row in set (0.00 sec)
Since Kylie Minogue begins with a letter alphabetically less than 'M', she’s reported as an answer; the names of our six other artists all come later in the alphabet. Note that by default MySQL doesn’t care about case; we’ll discuss this in more detail later in ORDER BY Clauses.” Of course, we haven’t stored the surname and the given names separately, and MySQL isn’t smart enough to know that Kylie Minogue is a person’s name that should ordinarily be sorted by surname (in phonebook order).
Another very common task you’ll want to perform with strings is to find matches that begin with a prefix, contain a string, or end in a suffix. For example, you might want to find all album names beginning with the word “Retro.” You can do this with the LIKE operator in a WHERE clause:
mysql> SELECT album_name FROM album WHERE album_name LIKE "Retro%";
+------------------------------------------+
| album_name |
+------------------------------------------+
| Retro - John McCready FAN |
| Retro - Miranda Sawyer POP |
| Retro - New Order / Bobby Gillespie LIVE |
+------------------------------------------+
3 rows in set (0.00 sec)
Let’s discuss in detail how this works.
The LIKE clause is used only with strings and means that a match must meet the pattern in the string that follows. In our example, we’ve used LIKE "Retro%", which means the string Retro followed by zero or more characters. Most strings used with LIKE contain the percentage character (%) as a wildcard character that matches all possible strings. You can also use it to define a string that ends in a suffix—such as "%ing"—or a string that contains a particular substring, such as %Corruption%.
For example, "John%" would match all strings starting with "John", such as John Smith and John Paul Getty. The pattern "%Paul" matches all strings that have "Paul" at the end. Finally, the pattern "%Paul%" matches all strings that have "Paul" in them, including at the start or at the end.
If you want to match exactly one wildcard character in a LIKE clause, you use the underscore character (_). For example, if you want all tracks that begin with a three-letter word that starts with 'R', you use:
mysql> SELECT * FROM track WHERE track_name LIKE "R__ %";
+----------+----------------+-----------+----------+----------+
| track_id | track_name | artist_id | album_id | time |
+----------+----------------+-----------+----------+----------+
| 4 | Red Right Hand | 2 | 1 | 00:06:11 |
| 14 | Run Wild | 1 | 1 | 00:03:57 |
| 1 | Rip This Joint | 4 | 1 | 00:02:23 |
+----------+----------------+-----------+----------+----------+
3 rows in set (0.00 sec)
The specification "R__ %" means a three-letter word beginning with 'R'—for example "Red", "Run" and "Rip"—followed by a space character, and then any string.
Combining conditions with AND, OR, NOT, and XOR
So far, we’ve used the WHERE clause to test one condition, returning all rows that meet it. You can combine two or more conditions using the Boolean operators AND, OR, NOT, and XOR.
Let’s start with an example. Suppose you want to find all albums with a title that begins with a character greater than C but less than M. This is straightforward with the AND operator:
mysql> SELECT album_name FROM album WHERE
-> album_name > "C" AND album_name < "M";
+-----------------------+
| album_name |
+-----------------------+
| Let Love In |
| Live Around The World |
| In A Silent Way |
| Exile On Main Street |
| Light Years |
+-----------------------+
5 rows in set (0.06 sec)
The AND operation in the WHERE clause restricts the results to those rows that meet both conditions.
The OR operator is used to find rows that meet at least one of several conditions. To illustrate, imagine you want a list of all albums that have a title beginning with L, S, or P. You can do this with two OR and three LIKE clauses:
mysql> SELECT album_name FROM album WHERE
-> album_name LIKE "L%" OR
-> album_name LIKE "S%" OR
-> album_name LIKE "P%";
+--------------------------+
| album_name |
+--------------------------+
| Let Love In |
| Substance (Disc 2) |
| Live Around The World |
| Power, Corruption & Lies |
| Substance 1987 (Disc 1) |
| Second Coming |
| Light Years |
+--------------------------+
7 rows in set (0.00 sec)
The OR operations in the WHERE clause restrict the answers to those that meet any of the three conditions. As an aside, it’s particularly obvious in this example that the results are reported without sorting; in this case, they’re reported in the order they were added to the database. We’ll return to sorting output later in ORDER BY Clauses.”
You can combine AND and OR, but you need to make it clear whether you want to first AND the conditions or OR them. Consider an example where the function isn’t obvious from the query:
mysql> SELECT album_name FROM album WHERE
-> album_name LIKE "L%" OR
-> album_name LIKE "S%" AND
-> album_name LIKE "%g";
+-----------------------+
| album_name |
+-----------------------+
| Let Love In |
| Live Around The World |
| Second Coming |
| Light Years |
+-----------------------+
4 rows in set (0.00 sec)
When you inspect the results, it becomes clear what’s happened: the answers either begin with L, or they have S at the beginning and g at the end. An alternative interpretation of the query would be that the answers must begin with L or S, and all end with g; this is clearly not how the MySQL server has handled the query, since one of the displayed answers, “Let Love In,” doesn’t end in a g. To make queries containing several Boolean conditions easier to read, group conditions within parentheses.
Parentheses cluster parts of a statement together and help make expressions readable; you can use them just as you would in basic math. Our previous example can be rewritten as follows:
mysql> SELECT album_name FROM album WHERE
-> album_name LIKE "L%" OR
-> (album_name LIKE "S%" AND album_name LIKE "%g");
+-----------------------+
| album_name |
+-----------------------+
| Let Love In |
| Live Around The World |
| Second Coming |
| Light Years |
+-----------------------+
4 rows in set (0.00 sec)
The parentheses make the evaluation order clear: we want albums beginning with 'L', or those beginning with 'S' and ending with 'g'. We’ve also typed the query over three lines instead of four, making the intention even clearer through careful layout; just as when writing program code, spacing, indentation, and careful layout help make readable queries.
You can also use parentheses to force a different evaluation order. If you did want albums having names with 'L' or 'S' at the beginning and 'g' at the end, you’d type:
mysql> SELECT album_name FROM album WHERE
-> (album_name LIKE "L%" OR album_name LIKE "S%") AND
-> album_name LIKE "%g";
+---------------+
| album_name |
+---------------+
| Second Coming |
+---------------+
1 row in set (0.00 sec)
Both examples with parentheses are much easier to understand. We recommend that you use parentheses whenever there’s a chance the intention could be misinterpreted; there’s no good reason to rely on MySQL’s implicit evaluation order.
The unary NOT operator negates a Boolean statement. Suppose you want a list of all albums except the ones having an album_id of 1 or 3. You’d write the query:
mysql> SELECT * FROM album WHERE NOT (album_id = 1 OR album_id = 3);
+-----------+----------+------------------------------------------+
| artist_id | album_id | album_name |
+-----------+----------+------------------------------------------+
| 1 | 2 | Substance (Disc 2) |
| 1 | 4 | Retro - New Order / Bobby Gillespie LIVE |
| 3 | 2 | In A Silent Way |
| 1 | 5 | Power, Corruption & Lies |
| 1 | 6 | Substance 1987 (Disc 1) |
| 1 | 7 | Brotherhood |
+-----------+----------+------------------------------------------+
6 rows in set (0.00 sec)
The expression in the parentheses says we want:
(album_id = 1 OR album_id = 3)
and the NOT operation negates it so we get everything but those that meet the condition in the parentheses. There are several other ways you can write a WHERE clause with the same function, and it really doesn’t matter which you choose. For example the following three expressions have the same effect:
WHERE NOT (album_id = 1) AND NOT (album_id = 3)
WHERE album_id != 1 AND album_id != 3
WHERE album_id != 1 AND NOT (album_id = 3)
Consider another example using NOT and parentheses. Suppose you want to get a list of all albums with an album_id greater than 2, but not those numbered 4 or 6:
mysql> SELECT * FROM album WHERE album_id > 2
-> AND NOT (album_id = 4 OR album_id = 6);
+-----------+----------+----------------------------+
| artist_id | album_id | album_name |
+-----------+----------+----------------------------+
| 1 | 3 | Retro - Miranda Sawyer POP |
| 1 | 5 | Power, Corruption & Lies |
| 1 | 7 | Brotherhood |
+-----------+----------+----------------------------+
3 rows in set (0.01 sec)
Again, the expression in parentheses lists albums that meet a condition—those that are numbered 4 or 6—and the NOT operator negates it so that we get everything else.
The NOT operator’s precedence can be a little tricky. Formally, if you apply it to any statement that evaluates to a Boolean FALSE or arithmetic zero, you’ll get TRUE (and TRUE is defined as 1). If you apply it to a statement that is nonzero, you’ll get FALSE (and FALSE is defined as 0). We’ve so far considered examples with clauses where the NOT is followed by a expression in parentheses, such as NOT (album_id = 4 OR album_id = 6). You should write your NOT expressions in this way, or you’ll get unexpected results. For example, the previous expression isn’t the same as this one:
mysql> SELECT * FROM album WHERE album_id > 2
-> AND (NOT album_id) = 4 OR album_id = 6;
+-----------+----------+-------------------------+
| artist_id | album_id | album_name |
+-----------+----------+-------------------------+
| 1 | 6 | Substance 1987 (Disc 1) |
+-----------+----------+-------------------------+
1 row in set (0.00 sec)
This returns unexpected results: just those albums with an album_id of 6. To understand what happened, try just the part of the statement with the NOT operator:
mysql> SELECT * FROM album WHERE (NOT album_id) = 4;
Empty set (0.00 sec)
What has happened is that MySQL has evaluated the expression NOT album_id, and then checked if it’s equal to 4. Since the album_id is always nonzero, NOT album_id is always zero and, therefore, never equal to 4, and you get no results! Now, try this:
mysql> SELECT * FROM album WHERE (NOT album_id) != 4;
+-----------+----------+------------------------------------------+
| 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.00 sec)
Again album_id is always nonzero, and so NOT album_id is 0. Since 0 isn’t equal to 4, we see all albums as answers. So be careful to use those parentheses: if you don’t, NOT’s high priority (or precedence) means it is applied to whatever immediately follows it, and not to the whole expression!
You can combine the NOT operator with LIKE. Suppose you want all albums that don’t begin with an L. To do this, type:
mysql> SELECT album_name FROM album WHERE album_name NOT LIKE "L%";
+------------------------------------------+
| album_name |
+------------------------------------------+
| Retro - John McCready FAN |
| Substance (Disc 2) |
| Retro - Miranda Sawyer POP |
| Retro - New Order / Bobby Gillespie LIVE |
| In A Silent Way |
| Power, Corruption & Lies |
| Exile On Main Street |
| Substance 1987 (Disc 1) |
| Second Coming |
| Brotherhood |
+------------------------------------------+
10 rows in set (0.01 sec)
The result is all albums, except those beginning with L.
You can combine NOT LIKE with AND and OR. Suppose you want albums beginning with S, but not those ending with a closing parenthesis, ')'. You can do this with:
mysql> SELECT album_name FROM album WHERE
-> album_name LIKE "S%" AND album_name NOT LIKE "%)";
+---------------+
| album_name |
+---------------+
| Second Coming |
+---------------+
1 row in set (0.00 sec)
MySQL also supports the exclusive-OR operation through the XOR operator. An exclusive OR evaluates as true if only one—but not both—of the expressions is true. To be precise, a XOR b is equivalent to (a AND (NOT b)) OR ((NOT a) AND b). For example, suppose you want to find artists whose names end in “es” or start with “The,” but not both. You’d need to type:
mysql> SELECT artist_name FROM artist WHERE
-> artist_name LIKE "The%" XOR
-> artist_name LIKE "%es";
Empty set (0.00 sec)
There are no matching entries in the database, since both “The Stone Roses” and “The Rolling Stones” meet both criteria.
Before we move on to sorting, we’ll discuss syntax alternatives. If you’re familiar with a programming language such as PHP, C, Perl, or Java, you’ll be used to using ! for NOT, || for OR, and && for AND. MySQL also supports these, and you can use them interchangeably with the word-based alternatives if you want to. However, we always use the word-based versions, as that’s what you’ll see used in most SQL statements.
ORDER BY Clauses
We’ve so far discussed how to choose the columns and rows that are returned as part of the query result, but not how to control how the result is displayed. In a relational database, the rows in a table form a set; there is no intrinsic order between the rows, and so we have to ask MySQL to sort the results if we want them in a particular order. In this section, we explain how to use the ORDER BY clause to do this. Sorting has no effect on what is returned, and only affects what order the results are returned.
Suppose you want to return a list of the artists in the music database, sorted in alphabetical order by the artist_name. Here’s what you’d type:
mysql> SELECT * FROM artist ORDER BY artist_name;
+-----------+---------------------------+
| artist_id | artist_name |
+-----------+---------------------------+
| 6 | Kylie Minogue |
| 3 | Miles Davis |
| 1 | New Order |
| 2 | Nick Cave & The Bad Seeds |
| 4 | The Rolling Stones |
| 5 | The Stone Roses |
+-----------+---------------------------+
6 rows in set (0.03 sec)
The ORDER BY clause indicates that sorting is required, followed by the column that should be used as the sort key. In this example, we’re sorting by alphabetically-ascending artist_name. The default sort is case-insensitive and in ascending order, and MySQL automatically sorts alphabetically because the columns are character strings. The way strings are sorted is determined by the character set and collation order that are being used. We discuss these in Collation and Character Sets.” For most of this book, we assume that you’re using the default settings.
Consider a second example. This time, let’s sort the output from the track table by ascending track length—that is, by the time column. Since it’s likely that two or more tracks have the same length, we’ll add a second sort key to resolve collisions and determine how such ties should be broken. In this case, when the track times are the same, we’ll sort the answers alphabetically by track_name. Here’s what you type:
mysql> SELECT time, track_name FROM track ORDER BY time, track_name;
+------+------------------------------------------------------------+
| time | track_name |
+------+------------------------------------------------------------+
| 1.34 | Intermission By Alan Wise [Olympia, Paris 12/11/01] |
| 1.81 | In A Silent Way |
| 2.38 | Rip This Joint |
| 2.78 | Jangling Jack |
| 2.81 | Full Nelson |
| 2.90 | I Just Want To See His Face |
| 2.97 | Sweet Black Angel |
| 2.99 | Your Star Will Shine |
| 3.00 | Shake Your Hips |
| 3.08 | Happy |
| 3.20 | Dreams Never End |
| 3.26 | Straight To The Man |
| 3.40 | Under The Influence Of Love |
| 3.40 | Ventilator Blues |
| 3.42 | Cries And Whispers |
| 3.44 | Mesh
...
We’ve shown only part of the 153-row output. Notice that there’s a collision of track times where the length is 3.40. In this case, the second sort key, track_name, is used to resolve the collision so that “Under the Influence of Love” appears before “Ventilator Blues.” You’ll find you often use multiple columns in an ORDER BY clause when you’re sorting people’s names, where typically you’ll use something like ORDER BY surname, firstname, secondname.
You can also sort in descending order, and you can control this behavior for each sort key. Suppose you want to sort the artists by descending alphabetical order. You type this:
mysql> SELECT artist_name FROM artist ORDER BY artist_name DESC;
+---------------------------+
| artist_name |
+---------------------------+
| The Stone Roses |
| The Rolling Stones |
| Nick Cave & The Bad Seeds |
| New Order |
| Miles Davis |
| Kylie Minogue |
+---------------------------+
6 rows in set (0.00 sec)
The DESC keyword specifies that the preceding sort key (in this case, artist_name) should be sorted in descending order. You can use a mixture of ascending and descending orders when multiple sort keys are used. For example, you can sort by descending time and alphabetically increasing track_name:
mysql> SELECT time, track_name FROM track
-> WHERE time < 3.6
-> ORDER BY time DESC, track_name ASC;
+------+------------------------------------------------------------+
| time | track_name |
+------+------------------------------------------------------------+
| 3.57 | Casino Boogie |
| 3.57 | Procession [Polytechnic of Central London, London 6/12/85] |
| 3.56 | Your Disco Needs You |
| 3.55 | I'm So High |
| 3.55 | On A Night Like This |
| 3.54 | Mr. Pastorius |
| 3.46 | Spinning Around |
| 3.44 | Mesh |
| 3.42 | Cries And Whispers |
| 3.40 | Under The Influence Of Love |
| 3.40 | Ventilator Blues |
| 3.26 | Straight To The Man |
| 3.20 | Dreams Never End |
| 3.08 | Happy |
| 3.00 | Shake Your Hips |
| 2.99 | Your Star Will Shine |
| 2.97 | Sweet Black Angel |
| 2.90 | I Just Want To See His Face |
| 2.81 | Full Nelson |
| 2.78 | Jangling Jack |
| 2.38 | Rip This Joint |
| 1.81 | In A Silent Way |
| 1.34 | Intermission By Alan Wise [Olympia, Paris 12/11/01] |
+------+------------------------------------------------------------+
24 rows in set (0.06 sec)
In this example, the rows are sorted by descending time and, when there’s a collision, by ascending track_name. We’ve used the optional keyword ASC to indicate an ascending sort key. Whenever we sort, ascending order is assumed if the DESC keyword isn’t used. You don’t need to explicitly include the ASC keyword, but including it does help to make the statement’s behavior more obvious. Notice also that we’ve included a WHERE clause; using WHERE and ORDER BY together is very common, and WHERE always appears before ORDER BY in the SELECT statement.
If a collision of values occurs, and you don’t specify another sort key, the sort order is undefined. This may not be important for you; you may not care about the order in which two customers with the identical name “John A. Smith” appear. A common source of collisions is string sorting, where MySQL ignores the case of characters. For example, the strings john, John, and JOHN are treated as identical in the ORDER BY process. If you do want sorting to behave like ASCII does (where uppercase comes before lowercase), then you can add a BINARY keyword to your sort as follows:
mysql> SELECT * FROM artist ORDER BY BINARY artist_name;
+-----------+---------------------------+
| artist_id | artist_name |
+-----------+---------------------------+
| 6 | Kylie Minogue |
| 3 | Miles Davis |
| 1 | New Order |
| 2 | Nick Cave & The Bad Seeds |
| 4 | The Rolling Stones |
| 5 | The Stone Roses |
+-----------+---------------------------+
6 rows in set (0.01 sec)
Because there are no case collisions in the music database, this example doesn’t do anything different from the example without the BINARY keyword.
Note you can use the BINARY keyword in many places; for example, you can use it in string comparisons. For example, searching for tracks with names alphabetically earlier than the letter b returns 12 tracks:
mysql> SELECT track_name FROM track WHERE track_name < 'b';
+----------------------------------------------------+
| track_name |
+----------------------------------------------------+
| Ain't Gonna Rain Anymore |
| All Day Long |
| 1963 |
| Age Of Consent [Spectrum Arena, Warrington 1/3/86] |
| As It Is When It Was [Reading Festival 29/8/93] |
| Amandla |
| Age Of Consent |
| 5 8 6 |
| All Down The Line |
| Angel Dust |
| All Day Long |
| As It Is When It Was |
+----------------------------------------------------+
12 rows in set (0.00 sec)
However, if we specify that we want to perform the search in ASCII order, we get all 153 tracks, since they all start with an uppercase letter, and uppercase letters appear before lowercase letters in the ASCII table:
mysql> SELECT track_name FROM track WHERE track_name < BINARY 'b';
+----------------------------------------------------------------------+
| track_name |
+----------------------------------------------------------------------+
| Do You Love Me? |
| Nobody's Baby Now |
| Loverman |
| Jangling Jack |
| Red Right Hand |
| I Let Love In |
...
| Broken Promise |
| As It Is When It Was |
| Weirdo |
| Paradise |
+----------------------------------------------------------------------+
153 rows in set (0.00 sec)
Sorting is performed as appropriate to the column type. For example, if you’re sorting dates, it organizes the rows in ascending date order. You can force the sort to behave differently, using the CAST() function and the AS keyword. Suppose, for example, you want to sort the track table by ascending time, but you want the times to be treated as strings. Here’s how you do it:
mysql> SELECT time, track_name FROM track ORDER BY CAST(time AS CHAR);
+-------+-----------------------------------------------------+
| time | track_name |
+-------+-----------------------------------------------------+
| 1.34 | Intermission By Alan Wise [Olympia, Paris 12/11/01] |
| 1.81 | In A Silent Way |
| 11.37 | Breaking Into Heaven |
| 12.80 | Human Nature |
| 16.67 | Shhh/Peaceful |
| 16.67 | In A Silent Way/It's About That Time |
| 2.38 | Rip This Joint |
| 2.78 | Jangling Jack |
| 2.81 | Full Nelson |
...
The results are ordered alphabetically, so that, for example, numbers beginning with 1 appear before numbers beginning with 2. The CAST() function forces a column to be treated as a different type, in this example as a character string using the AS CHAR clause. You can specify:
§ AS BINARY, to sort as binary, which has the same effect as ORDER BY BINARY
§ AS SIGNED, to sort as a signed integer
§ AS UNSIGNED, to sort as an unsigned integer
§ AS CHAR, to sort as a character string
§ AS DATE, to sort as a date
§ AS DATETIME, to sort as a date and time
§ AS TIME, to sort as a time
The types of columns are discussed in detail in Column Types” in Chapter 6.
The LIMIT Clause
The LIMIT clause is a useful, nonstandard SQL tool that allows you to control which rows are output. Its basic form allows you to limit the number of rows returned from a SELECT statement, which is useful when you want to limit the amount of data communicated over a network or output to the screen. You might use it, for example, in a web database application, where you want to find the rows that match a condition but only want to show the user the first 10 rows in a web page. Here’s an example:
mysql> SELECT track_name FROM track LIMIT 10;
+----------------------------+
| track_name |
+----------------------------+
| Do You Love Me? |
| Nobody's Baby Now |
| Loverman |
| Jangling Jack |
| Red Right Hand |
| I Let Love In |
| Thirsty Dog |
| Ain't Gonna Rain Anymore |
| Lay Me Low |
| Do You Love Me? (Part Two) |
+----------------------------+
10 rows in set (0.00 sec)
The LIMIT clause in this example restricts the output to the first 10 rows, saving the cost of buffering, communicating, and displaying the remaining 143 tracks.
The LIMIT clause can be used to return a fixed number of rows beginning anywhere in the result set. Suppose you want five rows, but you want the first one displayed to be the sixth row of the answer set. You do this by starting from after the fifth answer:
mysql> SELECT track_name FROM track LIMIT 5,5;
+----------------------------+
| track_name |
+----------------------------+
| I Let Love In |
| Thirsty Dog |
| Ain't Gonna Rain Anymore |
| Lay Me Low |
| Do You Love Me? (Part Two) |
+----------------------------+
5 rows in set (0.00 sec)
The output is rows 6 to 10 from the SELECT query.
If you want all rows after a start point, and you don’t know how many rows are in the table, then you need to choose a large integer as the second parameter. Suppose you want all rows after row 150 in the track table. Use the following command:
mysql> SELECT track_name FROM track LIMIT 150,999999999;
+----------------------+
| track_name |
+----------------------+
| As It Is When It Was |
| Weirdo |
| Paradise |
+----------------------+
3 rows in set (0.01 sec)
Since there are likely to be at most tens of thousands of rows in the track table, providing 999999999 as the second parameter guarantees all rows are returned. Technically, the largest number you can use is 18446744073709551615; this is the maximum value that can be stored in MySQL’s unsigned BIGINT variable type. MySQL will complain if you try to use a larger value. We discuss variable types in Other integer types” in Chapter 6.
There’s an alternative syntax that you might see for the LIMIT keyword: instead of writing LIMIT 10,5, you can write LIMIT 10 OFFSET 5.
Joining Two Tables
We’ve so far worked with just one table in our SELECT queries. However, you know that a relational database is all about working with the relationships between tables to answer information needs. Indeed, as we’ve explored the tables in the music database, it’s become obvious that by using these relationships, we can answer more interesting queries. For example, it’d be useful to know what tracks make up an album, what albums we own by each artist, or how long an album plays for. This section shows you how to answer these queries by joining two tables. We’ll return to this issue as part of a longer, more advanced discussion of joins in Chapter 7.
We use only one join syntax in this chapter. There are several more, and each gives you a different way to bring together data from two or more tables. The syntax we use here is the INNER JOIN, which hides some of the detail and is the easiest to learn. Consider an example, and then we’ll explain more about how it works:
mysql> SELECT artist_name, album_name FROM artist INNER JOIN album
-> USING (artist_id);
+---------------------------+------------------------------------------+
| artist_name | album_name |
+---------------------------+------------------------------------------+
| New Order | Retro - John McCready FAN |
| New Order | Substance (Disc 2) |
| New Order | Retro - Miranda Sawyer POP |
| New Order | Retro - New Order / Bobby Gillespie LIVE |
| New Order | Power, Corruption & Lies |
| New Order | Substance 1987 (Disc 1) |
| New Order | Brotherhood |
| Nick Cave & The Bad Seeds | Let Love In |
| Miles Davis | Live Around The World |
| Miles Davis | In A Silent Way |
| The Rolling Stones | Exile On Main Street |
| The Stone Roses | Second Coming |
| Kylie Minogue | Light Years |
+---------------------------+------------------------------------------+
13 rows in set (0.00 sec)
The output shows the artists and their albums. You can see for the first time how many albums we own by each artist and who made each one.
How does the INNER JOIN work? The statement has two parts: first, two table names separated by the INNER JOIN keywords; second, the USING keyword that indicates which column (or columns) holds the relationship between the two tables. In our first example, the two tables to be joined are artist and album, expressed as artist INNER JOIN album (for the basic INNER JOIN, it doesn’t matter what order you list the tables in, and so using album INNER JOIN artist would have the same effect). The USING clause in the example is USING (artist_id), which tells MySQL that the column that holds the relationship between the tables is artist_id; you should recall this from our design and our previous discussion in The Music Database,” in Chapter 4.
The data comes from 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.01 sec)
and 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.00 sec)
In response to our query, MySQL finds the artist_name and album_name value pairs that have the same artist_id values. For each artist_id in the artist table (let’s use 1 as an example):
+-----------+---------------------------+
| artist_id | artist_name |
+-----------+---------------------------+
| 1 | New Order |
+-----------+---------------------------+
the server finds all the entries in the album table that have this value of artist_id:
+-----------+----------+------------------------------------------+
| artist_id | album_id | album_name |
+-----------+----------+------------------------------------------+
| 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 |
| 1 | 5 | Power, Corruption & Lies |
| 1 | 6 | Substance 1987 (Disc 1) |
| 1 | 7 | Brotherhood |
+-----------+----------+------------------------------------------+
It can then form a new temporary table from these two sets:
+-----------+-------------+----------+------------------------------------------+
| artist_id | artist_name | album_id | album_name |
+-----------+-------------+----------+------------------------------------------+
| 1 | New Order | 1 | Retro - John McCready FAN |
| 1 | New Order | 2 | Substance (Disc 2) |
| 1 | New Order | 3 | Retro - Miranda Sawyer POP |
| 1 | New Order | 4 | Retro - New Order / Bobby Gillespie LIVE |
| 1 | New Order | 5 | Power, Corruption & Lies |
| 1 | New Order | 6 | Substance 1987 (Disc 1) |
| 1 | New Order | 7 | Brotherhood |
+-----------+-------------+----------+------------------------------------------+
Once it has processed all the different artist_id values, it selects the colums you asked for—artist_name and album_name—to display:
+---------------------------+------------------------------------------+
| artist_name | album_name |
+---------------------------+------------------------------------------+
| New Order | Retro - John McCready FAN |
| New Order | Substance (Disc 2) |
| New Order | Retro - Miranda Sawyer POP |
| New Order | Retro - New Order / Bobby Gillespie LIVE |
| New Order | Power, Corruption & Lies |
| New Order | Substance 1987 (Disc 1) |
| New Order | Brotherhood |
...
+---------------------------+------------------------------------------+
There are a few important issues you need to know about when using the basic INNER JOIN syntax:
§ It works only when two tables share a column with the same name that you can use as the join condition; otherwise, you must use an alternative syntax described in Chapter 7. Note that MySQL can’t automatically determine the column you want to use for the join, (even if there are columns with the same name in the two tables), so you have to specify it explicitly.
§ The result rows shown are those where the join column (or columns) match between the tables; rows from one table that don’t have a match in the other table are ignored. In the previous example, any artist who had no albums would be ignored.
§ With the exception of the join column or columns after the USING keyword, any columns you specify must be unambiguous. For example, if you want to SELECT the artist_name, you can use just artist_name because it exists only in the artist table. However, if you wantartist_id, then you need to specify it explicitly as artist.artist_id or album.artist_id because both tables have a column of the same name.
§ Don’t forget the USING clause. MySQL won’t complain if you omit it, but the results won’t make sense because you’ll get a Cartesian product. We discuss this further in Chapter 7.
§ The column or columns following the USING clause must be surrounded by parentheses. If you want to join on more than one column, separate the column names with a comma. We’ll show you an example in a moment.
If you remember these rules, you’ll find joins with INNER JOIN are reasonably straightforward. Let’s now consider a few more examples that illustrate these ideas.
Suppose you want to list the track names for all your albums. Examining the album and track tables, you identify that you would have to join two columns, artist_id and album_id. Let’s try the join operation:
mysql> SELECT album_name, track_name FROM album INNER JOIN track
-> USING (artist_id, album_id) LIMIT 15;
+---------------------------+----------------------------+
| album_name | track_name |
+---------------------------+----------------------------+
| Let Love In | Do You Love Me? |
| Let Love In | Nobody's Baby Now |
| Let Love In | Loverman |
| Let Love In | Jangling Jack |
| Let Love In | Red Right Hand |
| Let Love In | I Let Love In |
| Let Love In | Thirsty Dog |
| Let Love In | Ain't Gonna Rain Anymore |
| Let Love In | Lay Me Low |
| Let Love In | Do You Love Me? (Part Two) |
| Retro - John McCready FAN | Elegia |
| Retro - John McCready FAN | In A Lonely Place |
| Retro - John McCready FAN | Procession |
| Retro - John McCready FAN | Your Silent Face |
| Retro - John McCready FAN | Sunrise |
+---------------------------+----------------------------+
15 rows in set (0.00 sec)
We’ve specified the two join columns in the USING clause separated by commas as USING (artist_id, album_id). The results show the tracks for the album Let Love In, and the first few from Retro - John McReady FAN. To fit the results into the book, we’ve limited the output to 15 rows, using the LIMIT clause we discussed earlier in The LIMIT Clause.”
We can improve our previous example by adding an ORDER BY clause. It makes sense that we’d want to see the albums in alphabetical order, with the tracks shown in the order they occur on the album, so we could modify our previous query to be:
mysql> SELECT album_name, track_name FROM album INNER JOIN track
-> USING (artist_id, album_id)
-> ORDER BY album_name, track_id LIMIT 15;
+----------------------+-----------------------+
| album_name | track_name |
+----------------------+-----------------------+
| Brotherhood | State of the Nation |
| Brotherhood | Every Little Counts |
| Brotherhood | Angel Dust |
| Brotherhood | All Day Long |
| Brotherhood | Bizarre Love Triangle |
| Brotherhood | Way of Life |
| Brotherhood | Broken Promise |
| Brotherhood | As It Is When It Was |
| Brotherhood | Weirdo |
| Brotherhood | Paradise |
| Exile On Main Street | Rocks Off |
| Exile On Main Street | Rip This Joint |
| Exile On Main Street | Shake Your Hips |
| Exile On Main Street | Casino Boogie |
| Exile On Main Street | Tumbling Dice |
+----------------------+-----------------------+
15 rows in set (0.00 sec)
You can see that the ORDER BY clause sorts the albums and tracks in the required order, and that it’s listed last in the query after the join condition.
Let’s try a different query. Suppose you want to find out which tracks you’ve played. You can do this with a join between the track and played tables, using the artist_id, album_id, and track_id columns in the join condition. Here’s the query:
mysql> SELECT played, track_name FROM
-> track INNER JOIN played USING (artist_id, album_id, track_id)
-> ORDER BY track.artist_id, track.album_id, track.track_id, played;
+---------------------+-----------------------+
| played | track_name |
+---------------------+-----------------------+
| 2006-08-14 10:21:03 | Fine Time |
| 2006-08-14 10:25:22 | Temptation |
| 2006-08-14 10:30:25 | True Faith |
| 2006-08-14 10:36:54 | The Perfect Kiss |
| 2006-08-14 10:41:43 | Ceremony |
| 2006-08-14 10:43:37 | Regret |
| 2006-08-14 10:47:21 | Crystal |
| 2006-08-14 10:54:02 | Bizarre Love Triangle |
| 2006-08-15 14:00:03 | In A Silent Way |
| 2006-08-15 14:26:12 | Intruder |
| 2006-08-15 14:33:57 | New Blues |
+---------------------+-----------------------+
11 rows in set (0.00 sec)
We’ve sorted the results by artist, then album, then track, and then the play date and time. Notice we’ve also had to unambiguously specify the columns in the ORDER BY clause using the table name, since the first three columns occur in both tables. In practice, if columns are used in the join condition, it doesn’t matter whether you sort or select using the column from either table; for example, in this query, track.artist_id and played.artist_id are interchangeable because they’re always the same for each row.
Before we leave SELECT, we’ll give you a taste of one of the functions you can use to aggregate values. Suppose you want to find out how long New Order’s Brotherhood album takes to play. You can do this by summing the times of the individual tracks with the SQL SUM() function. Here’s how it works:
mysql> SELECT SUM(time) FROM
-> album INNER JOIN track USING (artist_id, album_id)
-> WHERE album.artist_id = 1 AND album.album_id = 7;
+-----------+
| SUM(time) |
+-----------+
| 43.78 |
+-----------+
1 row in set (0.00 sec)
You can see the album runs for just under 44 minutes. The SUM() function reports the sum of all values for the column enclosed in the parentheses—in this case, time—and not the individual values themselves. Because we’ve used a WHERE clause to choose only rows for the Brotherhoodalbum, the sum of the time values is the total play time of the album. Of course, to run this query, we needed to know that New Order’s artist_id is 1 and that the album_id of “Brotherhood” is 7. We discovered this by running two other SELECT queries beforehand:
mysql> SELECT artist_id FROM artist WHERE artist_name = "New Order";
+-----------+
| artist_id |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT album_id FROM album
-> WHERE artist_id = 1 AND album_name = "Brotherhood";
+----------+
| album_id |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)
We explain more features of SELECT and aggregate functions in Chapter 7.
The INSERT Statement
The INSERT statement is used to add new data to tables. In this section, we explain its basic syntax and show you simple examples that add new rows to the music database. In Chapter 6, we’ll discuss how to load data from existing tables or from external data sources.
INSERT Basics
Inserting data typically occurs in two situations: when you bulk-load in a large batch as you create your database, and when you add data on an ad hoc basis as you use the database. In MySQL, there are different optimizations built into the server for each situation and, importantly, different SQL syntaxes available to make it easy for you to work with the server in both cases. We explain a basic INSERT syntax in this section, and show you examples of how to use it for bulk and single record insertion.
Let’s start with the basic task of inserting one new row into the artist table. To do this, you need to understand the table’s structure. As we explained in Chapter 4 in The Music Database,” you can discover this with the SHOW COLUMNS statement:
mysql> SHOW COLUMNS FROM artist;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| artist_id | smallint(5) | NO | PRI | 0 | |
| artist_name | char(128) | NO | | | |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
This tells you that the two columns occur in the order artist_id and then artist_name, and you need to know this for the basic syntax we’re about to use.
Our new row is for a new artist, “Barry Adamson.” But what artist_id value do we give him? You might recall that we already have six artists, so we should probably use 7. You can check this with:
mysql> SELECT MAX(artist_id) FROM artist;
+----------------+
| MAX(artist_id) |
+----------------+
| 6 |
+----------------+
1 row in set (0.04 sec)
The MAX() function is an aggregate function, and it tells you the maximum value for the column supplied as a parameter. This is a little cleaner than SELECT artist_id FROM artist, which prints out all rows and requires you to inspect the rows to find the maximum value; adding anORDER BY makes it easier. Using MAX() is also much simpler than SELECT artist_id FROM artist ORDER BY artist_id DESC LIMIT 1, which also returns the correct answer. You’ll learn more about the AUTO_INCREMENT shortcut to automatically assign the next available identifier in Chapter 6, and about aggregate functions in Chapter 7.
We’re now ready to insert the row. Here’s what you type:
mysql> INSERT INTO artist VALUES (7, "Barry Adamson");
Query OK, 1 row affected (0.00 sec)
A new row is created—MySQL reports that one row has been affected—and the value 7 is inserted as the artist_id and Barry Adamson as the artist_name. You can check with a query:
mysql> SELECT * FROM artist WHERE artist_id = 7;
+-----------+---------------+
| artist_id | artist_name |
+-----------+---------------+
| 7 | Barry Adamson |
+-----------+---------------+
1 row in set (0.01 sec)
You might be tempted to try out something like this:
mysql> INSERT INTO artist
VALUES((SELECT 1+MAX(artist_id) FROM artist), "Barry Adamson");
However, this won’t work because you can’t modify a table while you’re reading from it. The query would work if you wanted to INSERT INTO a different table (here, a table other than artist).
To continue our example, and illustrate the bulk-loading approach, let’s now insert Barry Adamson’s album The Taming of the Shrewd and its tracks. First, check the structure of the album table:
mysql> SHOW COLUMNS FROM album;
+------------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------+------+-----+---------+-------+
| artist_id | int(5) | | PRI | 0 | |
| album_id | int(4) | | PRI | 0 | |
| album_name | char(128) | YES | | NULL | |
+------------+-----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
Second, insert the album using the approach we used previously:
mysql> INSERT INTO album VALUES (7, 1, "The Taming of the Shrewd");
Query OK, 1 row affected (0.00 sec)
The first value is the artist_id, the value of which we know from creating the artist, and the second value is the album_id, which must be 1 because this is the first album we’ve added for Barry Adamson.
Third, check the track table structure:
mysql> SHOW COLUMNS FROM track;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| track_id | int(3) | | PRI | 0 | |
| track_name | char(128) | YES | | NULL | |
| artist_id | int(5) | | PRI | 0 | |
| album_id | int(4) | | PRI | 0 | |
| time | decimal(5,2) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
Finally, insert the tracks:
mysql> INSERT INTO track VALUES (1, "Diamonds", 7, 1, 4.10),
-> (2, "Boppin Out / Eternal Morning", 7, 1, 3.22),
-> (3, "Splat Goes the Cat", 7, 1, 1.39),
-> (4, "From Rusholme With Love", 7, 1, 3.59);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
Here, we’ve used a different INSERT style to add all four tracks in a single SQL query. This style is recommended when you want to load more than one row. It has a similar format to the single-insertion style, except that the values for several rows are collected together in a comma-separated list. Giving MySQL all the data you want to insert in one statement helps it optimize the insertion process, allowing queries that use this syntax to be typically many times faster than repeated insertions of single rows. There are other ways to speed up insertion, and we discuss several inChapter 6.
The single-row INSERT style is unforgiving: if it finds a duplicate, it’ll stop as soon as it finds a duplicate key. For example, suppose we try to insert the same tracks again:
mysql> INSERT INTO track VALUES (1, "Diamonds", 7, 1, 4.10),
-> (2, "Boppin Out / Eternal Morning", 7, 1, 3.22),
-> (3, "Splat Goes the Cat", 7, 1, 1.39),
-> (4, "From Rusholme With Love", 7, 1, 3.59);
ERROR 1062 (23000): Duplicate entry '7-1-1' for key 1
The INSERT operation stops on the first duplicate key. You can add an IGNORE clause to prevent the error if you want:
mysql> INSERT IGNORE INTO track VALUES (1, "Diamonds", 7, 1, 4.10),
-> (2, "Boppin Out / Eternal Morning", 7, 1, 3.22),
-> (3, "Splat Goes the Cat", 7, 1, 1.39),
-> (4, "From Rusholme With Love", 7, 1, 3.59);
Query OK, 0 rows affected (0.01 sec)
Records: 4 Duplicates: 4 Warnings: 0
However, in most cases, you want to know about possible problems (after all, primary keys are supposed to be unique), and so this IGNORE syntax is rarely used.
You’ll notice that MySQL reports the results of bulk insertion differently from single insertion. From our initial bulk insertion, it reports:
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
The first line tells you how many rows were inserted, while the first entry in the final line tells you how many rows (or records) were actually processed. If you use INSERT IGNORE and try to insert a duplicate record—for which the primary key matches that of an existing row—then MySQL will quietly skip inserting it and report it as a duplicate in the second entry on the final line:
Query OK, 0 rows affected (0.01 sec)
Records: 4 Duplicates: 4 Warnings: 0
We discuss causes of warnings—shown as the third entry on the final line—in Chapter 6.
Alternative Syntaxes
There are several alternatives to the VALUES syntax we’ve shown you so far. This section shows you these and explains the advantages and drawbacks of each. If you’re happy with the basic syntax we’ve described so far, and want to move on to a new topic, feel free to skip ahead to The DELETE Statement.”
There are three disadvantages of the VALUES syntax we’ve shown you. First, you need to remember the order of the columns. Second, you need to provide a value for each column. Last, it’s closely tied to the underlying table structure: if you change the table’s structure, you need to change the INSERT statements, and the function of the INSERT statement isn’t obvious unless you have the table structure at hand. However, the three advantages of the approach are that it works for both single and bulk inserts, you get an error message if you forget to supply values for all columns, and you don’t have to type in column names. Fortunately, the disadvantages are easily avoided by varying the syntax.
Suppose you know that the album table has three columns and you recall their names, but you forget their order. You can insert using the following approach:
mysql> INSERT INTO album (artist_id, album_id, album_name)
-> VALUES (7, 2, "Oedipus Schmoedipus");
Query OK, 1 row affected (0.00 sec)
The column names are included in parentheses after the table name, and the values stored in those columns are listed in parentheses after the VALUES keyword. So, in this example, a new row is created and the value 7 is stored as the artist_id, 2 is stored as the album_id, and Oedipus Schmoedipus is stored as the album_name. The advantages of this syntax are that it’s readable and flexible (addressing the third disadvantage we described) and order-independent (addressing the first disadvantage). The disadvantage is that you need to know the column names and type them in.
This new syntax can also address the second disadvantage of the simpler approach—that is, it can allow you to insert values for only some columns. To understand how this might be useful, let’s explore the played table:
mysql> SHOW COLUMNS FROM played;
+-----------+-----------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+-------------------+-------+
| artist_id | int(5) | | PRI | 0 | |
| album_id | int(4) | | PRI | 0 | |
| track_id | int(3) | | PRI | 0 | |
| played | timestamp | YES | PRI | CURRENT_TIMESTAMP | |
+-----------+-----------+------+-----+-------------------+-------+
4 rows in set (0.00 sec)
Notice that the played column has a default value of CURRENT_TIMESTAMP. This means that if you don’t insert a value for the played column, it’ll insert the current date and time by default. This is just what we want: when we play a track, we don’t want to bother checking the date and time and typing it in. Here’s how you insert an incomplete played entry:
mysql> INSERT INTO played (artist_id, album_id, track_id)
-> VALUES (7, 1, 1);
Query OK, 1 row affected (0.00 sec)
We didn’t set the played column, so MySQL defaults it to the current date and time. You can check this with a query:
mysql> SELECT * FROM played WHERE artist_id = 7
-> AND album_id = 1;
+-----------+----------+----------+---------------------+
| artist_id | album_id | track_id | played |
+-----------+----------+----------+---------------------+
| 7 | 1 | 1 | 2006-08-09 12:03:00 |
+-----------+----------+----------+---------------------+
1 row in set (0.00 sec)
You can also use this approach for bulk insertion as follows:
mysql> INSERT INTO played (artist_id, album_id, track_id)
-> VALUES (7,1,2),(7,1,3),(7,1,4);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
The disadvantages of this approach are that you can accidentally omit values for columns, and you need to remember and type column names. The omitted columns will be set to the default values.
All columns in a MySQL table have a default value of NULL unless another default value is explicitly assigned when the table is created or modified. Because of this, defaults can often cause duplicate rows: if you add a row with the default primary key values and repeat the process, you’ll get a duplicate error. However, the default isn’t always sensible; for example, in the played table, the artist_id, album_id, and track_id columns all default to 0, which doesn’t make sense in the context of our music collection. Let’s try adding a row to played with only default values:
mysql> INSERT INTO played () VALUES ();
Query OK, 1 row affected (0.00 sec)
The () syntax is used to represent that all columns and values are to be set to their defaults. Let’s find our new row by asking for the most recent played time:
mysql> SELECT * FROM played ORDER BY played DESC LIMIT 1;
+-----------+----------+----------+---------------------+
| artist_id | album_id | track_id | played |
+-----------+----------+----------+---------------------+
| 0 | 0 | 0 | 2006-08-09 12:20:40 |
+-----------+----------+----------+---------------------+
1 row in set (0.00 sec)
The process worked, but the row doesn’t make any sense. We’ll discuss default values further in Chapter 6.
You can set defaults and still use the original INSERT syntax with MySQL 4.0.3 or later by using the DEFAULT keyword. Here’s an example that adds a played row:
mysql> INSERT INTO played VALUES (7, 1, 2, DEFAULT);
Query OK, 1 row affected (0.00 sec)
The keyword DEFAULT tells MySQL to use the default value for that column, and so the current date and time are inserted in our example. The advantages of this approach are that you can use the bulk-insert feature with default values, and you can never accidentally omit a column.
There’s another alternative INSERT syntax. In this approach, you list the column name and value together, giving the advantage that you don’t have to mentally map the list of values to the earlier list of columns. Here’s an example that adds a new row to the played table:
mysql> INSERT INTO played
-> SET artist_id = 7, album_id = 1, track_id = 1;
Query OK, 1 row affected (0.00 sec)
The syntax requires you list a table name, the keyword SET, and then column-equals-value pairs, separated by commas. Columns that aren’t supplied are set to their default values. The disadvantages are again that you can accidentally omit values for columns, and that you need to remember and type in column names. A significant additional disadvantage is that you can’t use this method for bulk insertion.
You can also insert using values returned from a query. We discuss this in Chapter 8.
The DELETE Statement
The DELETE statement is used to remove one or more rows from a database. We explain single-table deletes here, and discuss multi-table deletes—which remove data from two or more tables through one statement—in Chapter 8.
If you want to try out the steps in this section on your MySQL server, you’ll need to reload your music database afterwards so that you can follow the examples in later sections. To do this, follow the steps you used in Loading the Sample Databases” in Chapter 3 to load it in the first place.
DELETE Basics
The simplest use of DELETE is to remove all rows in a table. Suppose you want to empty your played table, perhaps because it’s taking too much space or because you want to share your music database with someone else and they don’t want your played data. You do this with:
mysql> DELETE FROM played;
Query OK, 19 rows affected (0.07 sec)
This removes all rows, including those we just added in The INSERT Statement”; you can see that 19 rows have been affected.
The DELETE syntax doesn’t include column names, since it’s used to remove whole rows and not just values from a row. To reset or modify a value in a row, you use the UPDATE statement, described later in this chapter in The UPDATE Statement.” The DELETE statement doesn’t remove the table itself. For example, having deleted all rows in the played table, you can still query the table:
mysql> SELECT * FROM played;
Empty set (0.00 sec)
Of course, you can also continue to explore its structure using DESCRIBE or SHOW CREATE TABLE, and insert new rows using INSERT. To remove a table, you use the DROP statement described in Chapter 6.
Using WHERE, ORDER BY, and LIMIT
If you’ve deleted rows in the previous section, reload your music database now. You need the rows in the played table restored for the examples in this section.
To remove one or more rows, but not all rows in a table, you use a WHERE clause. This works in the same way as it does for SELECT. For example, suppose you want to remove all rows from the played table with played dates and times earlier than August 15, 2006. You do this with:
mysql> DELETE FROM played WHERE played < "2006-08-15";
Query OK, 8 rows affected (0.00 sec)
The result is that the eight played rows that match the criteria are removed. Note that the date is enclosed in quotes and that the date format is year, month, day, separated by hyphens. MySQL supports several different ways of specifying times and dates but saves dates in this internationally friendly, easy-to-sort format (it’s actually an ISO standard). MySQL can also reasonably interpret two-digit years, but we recommend against using them; remember all the work required to avoid the Y2K problem?
Suppose you want to remove an artist, his albums, and his album tracks. For example, let’s remove everything by Miles Davis. Begin by finding out the artist_id from the artist table, which we’ll use to remove data from all four tables:
mysql> SELECT artist_id FROM artist WHERE artist_name = "Miles Davis";
+-----------+
| artist_id |
+-----------+
| 3 |
+-----------+
1 row in set (0.00 sec)
Next, remove the row from the artist table:
mysql> DELETE FROM artist WHERE artist_id = 3;
Query OK, 1 row affected (0.00 sec)
Then, do the same thing for the album, track, and played tables:
mysql> DELETE FROM album WHERE artist_id = 3;
Query OK, 2 rows affected (0.01 sec)
mysql> DELETE FROM track WHERE artist_id = 3;
Query OK, 13 rows affected (0.01 sec)
mysql> DELETE FROM played WHERE artist_id = 3;
Query OK, 3 rows affected (0.00 sec)
Since all four tables can be joined using the artist_id column, you can accomplish this whole deletion process in a single DELETE statement; we show you how in Chapter 8.
You can use the ORDER BY and LIMIT clauses with DELETE. You usually do this when you want to limit the number of rows deleted, either so that the statement doesn’t run for too long or because you want to keep a table to a specific size. Suppose your played table contains 10,528 rows, but you want to have at most 10,000 rows. In this situation, it may make sense to remove the 528 oldest rows, and you can do this with the following statement:
mysql> DELETE FROM played ORDER BY played LIMIT 528;
Query OK, 528 rows affected (0.23 sec)
The query sorts the rows by ascending play date and then deletes at most 528 rows, starting with the oldest. Typically, when you’re deleting, you use LIMIT and ORDER BY together; it usually doesn’t make sense to use them separately. Note that sorting large numbers of entries on a field that doesn’t have an index can be quite slow. We discuss indexes in detail in Keys and Indexes” in Chapter 6.
Removing All Rows with TRUNCATE
If you want to remove all rows in a table, there’s a faster method than removing them with DELETE. By using the TRUNCATE TABLE statement, MySQL takes the shortcut of dropping the table—that is, removing the table structures and then re-creating them. When there are many rows in a table, this is much faster.
If you want to remove the data in the played table, you can write this:
mysql> TRUNCATE TABLE played;
Query OK, 0 rows affected (0.00 sec)
Notice that the number of rows affected is shown as zero: to quickly delete all the data in the table, MySQL doesn’t count the number of rows that are deleted, so the number shown (normally zero, but sometimes nonzero) does not reflect the actual number of rows deleted.
The TRUNCATE TABLE statement has two other limitations:
§ It’s actually identical to DELETE if you use InnoDB tables.
§ It does not work with locking or transactions.
Table types, transactions, and locking are discussed in Chapter 7. In practice, none of these limitations affect most applications, and you can use TRUNCATE TABLE to speed up your processing. Of course, it’s not common to delete whole tables during normal operation. An exception is temporary tables, which are used to temporarily store query results for a particular user session and can be deleted without losing the original data.
The UPDATE Statement
The UPDATE statement is used to change data. In this section, we show you how to update one or more rows in a single table. Multitable updates are discussed in Chapter 8.
If you’ve deleted rows from your music database, reload it by following the instructions in Loading the Sample Databases” in Chapter 3. You need a copy of the unmodified music database to follow the examples in this section.
Examples
The simplest use of the UPDATE statement is to change all rows in a table. There isn’t much need to change all rows from a table in the music database—any example is a little contrived—but let’s do it anyway. To change the artist names to uppercase, you can use:
mysql> UPDATE artist SET artist_name = UPPER(artist_name);
Query OK, 6 rows affected (0.04 sec)
Rows matched: 6 Changed: 6 Warnings: 0
The function UPPER() is a MySQL function that returns the uppercase version of the text passed as the parameter; for example, New Order is returned as NEW ORDER. You can see that all six artists are modified, since six rows are reported as affected. The function LOWER() performs the reverse, converting all the text to lowercase.
The second row reported by an UPDATE statement shows the overall effect of the statement. In our example, you see:
Rows matched: 6 Changed: 6 Warnings: 0
The first column reports the number of rows that were retrieved as answers by the statement; in this case, since there’s no WHERE or LIMIT clause, all six rows in the table match the query. The second column reports how many rows needed to be changed, and this is always equal to or less than the number of rows that match; in this example, since none of the strings are entirely in uppercase, all six rows are changed. If you repeat the statement, you’ll see a different result:
mysql> UPDATE artist SET artist_name = UPPER(artist_name);
Query OK, 0 rows affected (0.00 sec)
Rows matched: 6 Changed: 0 Warnings: 0
This time, since all of the artists are already in uppercase, six rows still match the statement but none are changed. Note also the number of rows changed is always equal to the number of rows affected, as reported on the first line of the output.
Our previous example updates each value relative to its current value. You can also set columns to a single value. For example, if you want to set all played dates and times to the current date and time, you can use:
mysql> UPDATE played SET played = NULL;
Query OK, 11 rows affected (0.00 sec)
Rows matched: 11 Changed: 11 Warnings: 0
You’ll recall from Alternative Syntaxes” that since the default value of the played column is CURRENT_TIMESTAMP, passing a NULL value causes the current date and time to be stored instead. Since all rows match and all rows are changed (affected), you can see three 11s in the output.
Using WHERE, ORDER BY, and LIMIT
Often, you don’t want to change all rows in a table. Instead, you want to update one or more rows that match a condition. As with SELECT and DELETE, the WHERE clause is used for the task. In addition, in the same way as with DELETE, you can use ORDER BY and LIMIT together to control how many rows are updated from an ordered list.
Let’s try an example that modifies one row in a table. If you browse the album database, you’ll notice an inconsistency for the two albums beginning with “Substance”:
mysql> SELECT * FROM album WHERE album_name LIKE
-> "Substance%";
+-----------+----------+-------------------------+
| artist_id | album_id | album_name |
+-----------+----------+-------------------------+
| 1 | 2 | Substance (Disc 2) |
| 1 | 6 | Substance 1987 (Disc 1) |
+-----------+----------+-------------------------+
2 rows in set (0.00 sec)
They’re actually part of the same two CD set, and the first-listed album is missing the year 1987, which is part of the title. To change it, you use an UPDATE command with a WHERE clause:
mysql> UPDATE album SET album_name = "Substance 1987 (Disc 2)"
-> WHERE artist_id = 1 AND album_id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
As expected, one row was matched, and one row was changed.
To control how many updates occur, you can use the combination of ORDER BY and LIMIT. As with DELETE, you would do this because you either want the statement to run for a controlled amount of time, or you want to modify only some rows. Suppose you want to set the 10 most recent played dates and times to the current date and time (the default). You do this with:
mysql> UPDATE played SET played = NULL ORDER BY played DESC LIMIT 10;
Query OK, 10 rows affected (0.00 sec)
Rows matched: 10 Changed: 10 Warnings: 0
You can see that 10 rows were matched and were changed.
The previous query also illustrates an important aspect of updates. As you’ve seen, updates have two phases: a matching phase—where rows are found that match the WHERE clause—and a modification phase, where the rows that need changing are updated. In our previous example, theORDER BY played is used in the matching phase, to sort the data after it’s read from the table. After that, the modification phase processes the first 10 rows, updating those that need to be changed. Since MySQL 4.0.13, the LIMIT clause controls the maximum number of rows that are matched. Prior to this, it controlled the maximum number of rows that were changed. The new implementation is better; under the old scheme, you had little control over the update processing time when many rows matched but few required changes.
Exploring Databases and Tables with SHOW and mysqlshow
We’ve already explained how you can use the SHOW command to obtain information on the structure of a database, its tables, and the table columns. In this section, we’ll review the most common types of SHOW statement with brief examples using the music database. The mysqlshowcommand-line program performs the same function as several SHOW command variants, but without needing to start the monitor.
The SHOW DATABASES statement lists the databases you can access. If you’ve followed our sample database installation steps in Chapter 3 in Loading the Sample Databases,” your output should be as follows:
mysql> SHOW DATABASES;
+------------+
| Database |
+------------+
| flight |
| music |
| mysql |
| test |
| university |
+------------+
5 rows in set (0.01 sec)
These are the databases that you can access with the USE command; as we explain in Chapter 9, you can’t see databases for which you have no access privileges unless you have the global SHOW DATABASES privilege. You can get the same effect from the command line using the mysqlshowprogram:
$ mysqlshow --user=root --password=the_mysql_root_password
You can add a LIKE clause to SHOW DATABASES. This is useful only if you have many databases and want a short list as output. For example, to see databases beginning with m, type:
mysql> SHOW DATABASES LIKE "m%";
+---------------+
| Database (m%) |
+---------------+
| music |
| mysql |
+---------------+
2 rows in set (0.00 sec)
The syntax of the LIKE statement is identical to that in its use in SELECT.
To see the statement used to create a database, you can use the SHOW CREATE DATABASE statement. For example, to see how music was created, type:
mysql> SHOW CREATE DATABASE music;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| music | CREATE DATABASE music /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
This is perhaps the least exciting SHOW statement; it only displays the statement:
CREATE DATABASE music
There are some additional keywords that are enclosed between the comment symbols /*! and */:
40100 DEFAULT CHARACTER SET latin1
These instructions contain MySQL-specific extensions to standard SQL that are unlikely to be understood by other database programs. A database server other than MySQL would ignore this comment text, and so the syntax is usable by both MySQL and other database server software. The optional number 40100 indicates the minimum version of MySQL that can process this particular instruction—in this case, version 4.01.00; older versions of MySQL ignore such instructions. You’ll learn about creating databases in Chapter 6.
The SHOW TABLES statement lists the tables in a database. To check the tables in music, type:
mysql> SHOW TABLES FROM music;
+-----------------+
| Tables_in_music |
+-----------------+
| album |
| artist |
| played |
| track |
+-----------------+
4 rows in set (0.01 sec)
If you’ve already selected the music database with the USE music command, you can use the shortcut:
mysql> SHOW TABLES;
+-----------------+
| Tables_in_music |
+-----------------+
| album |
| artist |
| played |
| track |
+-----------------+
4 rows in set (0.01 sec)
You can get a similar result by specifying the database name to the mysqlshow program:
$ mysqlshow --user=root --password=the_mysql_root_password music
As with SHOW DATABASES, you can’t see tables that you don’t have privileges for. This means you can’t see tables in a database you can’t access, even if you have the SHOW DATABASES global privilege.
The SHOW COLUMNS statement lists the columns in a table. For example, to check the columns of track, type:
mysql> SHOW COLUMNS FROM track;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| track_id | int(3) | | PRI | 0 | |
| track_name | char(128) | YES | | NULL | |
| artist_id | int(5) | | PRI | 0 | |
| album_id | int(4) | | PRI | 0 | |
| time | decimal(5,2) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
The output reports all column names, their types and sizes, whether they can be NULL, whether they are part of a key, their default value, and any extra information. Types, keys, NULL values, and defaults are discussed further in Chapter 6. If you haven’t already chosen the music database with the USE command, then you can add the database name before the table name, as in music.track. Unlike the previous SHOW statements, you can always see all column names if you have access to a table; it doesn’t matter that you don’t have certain privileges for all columns. You can get a similar result by using mysqlshow with the database and table name:
$ mysqlshow --user=root --password=the_mysql_root_password music track
You can see the statement used to create a particular table using the SHOW CREATE TABLE statement; creating tables is a subject of Chapter 6. Some users prefer this output to that of SHOW COLUMNS, since it has the familiar format of a CREATE TABLE statement. Here’s an example for thetrack table:
mysql> SHOW CREATE TABLE track;
+-------+---------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------+
| track | CREATE TABLE `track` ( |
| | `track_id` int(3) NOT NULL default '0', |
| | `track_name` char(128) default NULL, |
| | `artist_id` int(5) NOT NULL default '0', |
| | `album_id` int(4) NOT NULL default '0', |
| | `time` decimal(5,2) default NULL, |
| | PRIMARY KEY (`artist_id`,`album_id`,`track_id`) |
| | ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------+
We’ve reformatted the output slightly so it fits better in the book.
Exercises
All exercises here concern the music database. You’ll find the table structures in The Music Database” are a useful reference, or you can practice using the SHOW statement as you work your way through the tasks:
1. Use one or more SELECT statements to find out how many tracks are on New Order’s Brotherhood album.
2. Using a join, list the albums that we own by the band New Order.
3. With INSERT statements, add the artist Leftfield to the database. For this new artist, add the album Leftism that has the following tracks:
a. Release the Pressure (Time: 7.39)
b. Afro-Melt (Time: 7.33)
c. Melt (Time: 5.21)
d. Song of Life (Time: 6.55)
e. Original (Time: 6.00)
f. Black Flute (Time: 3.46)
g. Space Shanty (Time: 7.15)
h. Inspection Check One (Time: 6.30)
i. Storm 3000 (Time: 5.44)
j. Open Up (Time: 6.52)
k. 21st Century Poem (Time: 5.42)
l. Bonus Track (Time: 1.22)
4. How long in minutes is the Leftism album you added in Question 3? Hint: use the SUM() aggregate function.
5. Change the time for the Original track on the Leftism album to 6.22.
6. Remove the Bonus Track from the Leftism album.