Creating a Table
199
you can select the database on the command line when you invoke
mysql
. Just specify its name after
any connection parameters that you might need to provide. For example:
shell>
mysql -h host -u user -p menagerie
Enter password:
********
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()
[960]
.
3.3.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.
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
