Database Reference
In-Depth Information
common_name: Wood Duck
*************************** 2. row ***************************
bird_id: 6
scientific_name: Apteryx mantelli
common_name: North Island Brown Kiwi
Notice that in the
WHERE
clause of the
SELECT
statement we are selecting rows where
the
endangered
column has a value. For the column data type of
BIT
, this is all that's
needed, and it has the same effect as if we specified
WHERE endangered = 1
. To fil-
ter on the reverse — to select rows in which the bit for the
endangered
column is not
set — use the
NOT
operator like so:
SELECT
*
FROM
birds_new
WHERE NOT
endangered
\
G
After looking over the display for the Wood Duck and that Kiwi bird, maybe we should
allow for other values for the
endangered
column. There are several degrees of endan-
germent for birds. We could and should create a separate reference table for the possibilit-
ies, but let's just enumerate the choices in the column attributes so you can see how that's
done. While we're at it, we'll also relocate the column to just after the
family_id
column. For this, we'll use a newclause,
MODIFY COLUMN
:
ALTER TABLE
birds_new
MODIFY
COLUMN
endangered
ENUM
(
'Extinct'
,
'Extinct in Wild'
,
'Threatened - Critically Endangered'
,
'Threatened - Endangered'
,
'Threatened - Vulnerable'
,
'Lower Risk - Conservation Dependent'
,
'Lower Risk - Near Threatened'
,
'Lower Risk - Least Concern'
)
AFTER family_id
;
Notice that the syntax for the
MODIFY COLUMN
clause lists the name of the column
once. That's because the clause does not allow you to change thecolumn name. For that,
you must use the
CHANGE COLUMN
clause. Notice also that we used a new column data
type that lets us enumerate a list of acceptable values:the
ENUM
data type. The values are
enclosed in quotes, separated by commas, and the set is contained within a pair of paren-
theses.
Let's run the
SHOW COLUMNS
statementwith the
LIKE
clause to see just the column set-
tings for the
endangered
column:
