Specifications

This query can be read as, “Select name from customers, and then return 3 rows, starting from
row 2 in the output.” Note that row numbers are zero indexed—that is, the first row in the out-
put is row number zero.
This is very useful for Web applications, such as when the customer is browsing through prod-
ucts in a catalog, and we want to show 10 items on each page.
Updating Records in the Database
In addition to retrieving data from the database, we often want to change it. For example, we
might want to increase the prices of books in the database. We can do this using an UPDATE
statement.
The usual form of an
UPDATE statement is
UPDATE tablename
SET column1=expression1,column2=expression2,...
[WHERE condition]
[LIMIT number]
The basic idea is to update the table called tablename, setting each of the columns named to
the appropriate expression. You can limit an UPDATE to particular rows with a WHERE clause, and
limit the total number of rows to affect with a LIMIT clause.
Let’s look at some examples.
If we want to increase all the book prices by 10%, we can use an UPDATE statement without a
WHERE clause:
update books
set price=price*1.1;
If, on the other hand, we want to change a single row—say, to update a customer’s address—
we can do it like this:
update customers
set address = ‘250 Olsens Road’
where customerid = 4;
Altering Tables After Creation
In addition to updating rows, you might want to alter the structure of the tables within your
database. For this purpose, you can use the flexible ALTER TABLE statement. The basic form of
this statement is
ALTER TABLE tablename alteration [, alteration ...]
Working with Your MySQL Database
C
HAPTER 9
9
W
ORKING WITH
YOUR MYSQL
DATABASE
223
12 7842 CH09 3/6/01 3:36 PM Page 223