-> WHERE artist_id = 1 AND album_id = 2

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 fora 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 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, the ORDER 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 mysqlshow command-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 inset sec)

Download 4.24 Mb.

Share with your friends: