Database Reference
In-Depth Information
In these results, we've selected only rows in which
status
equals
1
(i.e., only records
that are active). We did this using the
WHERE
clause. It's part of the
SELECT
statement
and not an SQL statement on its own. Let's try another SQL statement like this one, but
ask for the
inactive
records:
SELECT * FROM books WHERE status = 0 \G
*************************** 1. row ***************************
book_id: 100
title: Heart of Darkness
status: 0
*************************** 2. row ***************************
book_id: 102
title: My Antonia
status: 0
Notice that this time we changed the ending of the SQL statement from a semicolon to
\G
. This was mentioned earlier in this chapter as an option. It shows the results not in a
table format, but as a batch of lines for each record. Sometimes this is easier to read, usu-
ally when the fields are so long that a tabular format would be too wide for your screen
and would wrap around. It's a matter of preference for each situation.
We've added data to this minimal table. Now let's change the data a little. Let's change
the
status
of one of the rows.To do this, we will use the
UPDATE
statement. It pro-
duces two lines of status output:
UPDATE books SET status = 1 WHERE book_id = 102;
Query OK, 1 row affected (0.18 sec)
Rows matched: 1 Changed: 1 Warnings: 0
You can learn how to read and remember SQL statement syntax better if you read and in-
terpret them in the way and order they're written. Let's do that with this SQL statement,
the first line in the preceding code block. It says to
update
books
by
setting
the value of
status
to
1
for all rows
where
book_id
equals
102
. In this case, there is only one re-
cord with that value, so the message that follows says that one row was affected, and only
one was changed or updated — however you want to say that. To see the results, run the
SELECT
statement shown earlier, the one where we check for active status:
SELECT * FROM books WHERE status = 1;
+---------+------------------------+--------+
| book_id | title | status |
+---------+------------------------+--------+
