Basic SQL - Learning MySQL - page 173

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 up-
dated. 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 in set (0.01 sec)