![](https://csdnimg.cn/release/download_crawler_static/5208234/bg11.jpg)
Creating a Table
11
Important
menagerie in the command just shown is not your password. If you want to supply
your password on the command line after the -p option, you must do so with no
intervening space (for example, as -pmypassword, not as -p mypassword).
However, putting your password on the command line is not recommended,
because doing so exposes it to snooping by other users logged in on your machine.
Note
You can see at any time which database is currently selected using SELECT
DATABASE().
4.2. Creating a Table
Creating the database is the easy part, but at this point it is empty, as SHOW TABLES tells you:
mysql> SHOW TABLES;
Empty set (0.00 sec)
The harder part is deciding what the structure of your database should be: what tables you need and what
columns should be in each of them.
You want a table that contains a record for each of your pets. This can be called the pet table, and
it should contain, as a bare minimum, each animal's name. Because the name by itself is not very
interesting, the table should contain other information. For example, if more than one person in your
family keeps pets, you might want to list each animal's owner. You might also want to record some basic
descriptive information such as species and sex.
How about age? That might be of interest, but it is not a good thing to store in a database. Age changes
as time passes, which means you'd have to update your records often. Instead, it is better to store a fixed
value such as date of birth. Then, whenever you need age, you can calculate it as the difference between
the current date and the birth date. MySQL provides functions for doing date arithmetic, so this is not
difficult. Storing birth date rather than age has other advantages, too:
• You can use the database for tasks such as generating reminders for upcoming pet birthdays. (If you
think this type of query is somewhat silly, note that it is the same question you might ask in the context
of a business database to identify clients to whom you need to send out birthday greetings in the current
week or month, for that computer-assisted personal touch.)
• You can calculate age in relation to dates other than the current date. For example, if you store death
date in the database, you can easily calculate how old a pet was when it died.
You can probably think of other types of information that would be useful in the pet table, but the ones
identified so far are sufficient: name, owner, species, sex, birth, and death.
Use a CREATE TABLE statement to specify the layout of your table:
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
VARCHAR is a good choice for the name, owner, and species columns because the column values vary
in length. The lengths in those column definitions need not all be the same, and need not be 20. You can
normally pick any length from 1 to 65535, whatever seems most reasonable to you. If you make a poor
choice and it turns out later that you need a longer field, MySQL provides an ALTER TABLE statement.
Several types of values can be chosen to represent sex in animal records, such as 'm' and 'f', or
perhaps 'male' and 'female'. It is simplest to use the single characters 'm' and 'f'.