Learning Mysql

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 notwork 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 speedup 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 fora 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 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 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 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,

Download 4.24 Mb.

Share with your friends: