Writing MySQL-Based Programs - MySQL - page 66

as NOT NULL because they're each required to have a value. The other columns are im‐
plicitly permitted to be NULL (and that is also their default value) because we might not
know the value to assign them for any given individual. That is, NULL signifies “un‐
known.”
Notice that although we want to keep track of age, there is no age column in the table.
Instead, there is a birth column of DATE type. Ages change, so if we store age values,
we'd have to keep updating them. Storing birth dates is better: they don't change and
can be used to calculate age any time (see Recipe 6.13 ). color is an ENUM column; color
values can be any one of the listed values. foods is a SET , which permits the value to be
any combination of the individual set members. That way we can record multiple fa‐
vorite foods for any buddy.
To create the table, use the profile.sql script in the tables directory of the recipes dis‐
tribution. Change location into that directory, then run this command:
% mysql cookbook < profile.sql
The script also loads sample data into the table. You can experiment with the table, then
restore it if you change its contents by running the script again. (See the final section of
this chapter on the importance of restoring the profile table after modifying it.)
The contents of the profile table as loaded by the profile.sql script look like this:
mysql> SELECT * FROM profile;
+----+---------+------------+-------+-----------------------+------+
| id | name | birth | color | foods | cats |
+----+---------+------------+-------+-----------------------+------+
| 1 | Sybil | 1970-04-13 | black | lutefisk,fadge,pizza | 0 |
| 2 | Nancy | 1969-09-30 | white | burrito,curry,eggroll | 3 |
| 3 | Ralph | 1973-11-02 | red | eggroll,pizza | 4 |
| 4 | Lothair | 1963-07-04 | blue | burrito,curry | 5 |
| 5 | Henry | 1965-02-14 | red | curry,fadge | 1 |
| 6 | Aaron | 1968-09-17 | green | lutefisk,fadge | 1 |
| 7 | Joanna | 1952-08-20 | green | lutefisk,fadge | 0 |
| 8 | Stephen | 1960-05-01 | white | burrito,pizza | 0 |
+----+---------+------------+-------+-----------------------+------+
Although most of the columns in the profile table permit NULL values, none of the
rows in the sample dataset actually contain NULL yet. (I want to defer the complications
of NULL value processing to Recipes 2.5 and 2.7 .)
SQL statement categories
SQL statements can be grouped into two broad categories, depending on whether they
return a result set (a set of rows):
• Statements that return no result set, such as INSERT , DELETE , or UPDATE . As a general
rule, statements of this type generally change the database in some way. There are