Creating and using a database
Now that you know how to enter commands, it's time to access a database.
Suppose you have several pets in your home (your "menagerie'') and you'd
like to keep track of various types of information about them. You can do so
by creating tables to hold your data and loading them with the desired information.
Then you can answer different sorts of questions about your animals by retrieving
data from the tables. This section shows how to do all that:
- How to create a database
- How to create a table
- How to load data into the table
- How to retrieve data from the table in various ways
- How to use multiple tables
The menagerie database will be simple (deliberately), but it is not difficult
to think of real-world situations in which a similar type of database might
be used. For example, a database like this could be used by a farmer to keep
track of livestock, or by a veterinarian to keep track of patient records.
Use the SHOW statement to find out what databases currently exist
on the server:
mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql |
| test |
| tmp |
+----------+
The list of databases is probably different on your machine, but the mysql
and test databases are likely to be among them. The mysql
database is required because it describes user access privileges. The test
database is often provided as a workspace for users to try things out.
If the test database exists, try to access it:
mysql> USE test
Database changed
Note that USE , like QUIT , does not require a semicolon.
(You can terminate such statements with a semicolon if you like; it does no
harm.) The USE statement is special in another way, too: it must
be given on a single line.
You can use the test database (if you have access to it) for the
examples that follow, but anything you create in that database can be removed
by anyone else with access to it. For this reason, you should probably ask your
MySQL administrator for permission to use a database of your
own. Suppose you want to call yours menagerie . The administrator
needs to execute a command like this:
mysql> GRANT ALL ON menagerie.* TO your_mysql_name;
where your_mysql_name is the MySQL user name
assigned to you.
Creating and selecting a database
If the administrator creates your database for you when setting up your permissions,
you can begin using it. Otherwise, you need to create it yourself:
mysql> CREATE DATABASE menagerie;
Under Unix, database names are case sensitive (unlike SQL keywords), so you
must always refer to your database as menagerie , not as Menagerie ,
MENAGERIE or some other variant. This is also true for table names.
(Under Windows, this restriction does not apply, although you must refer to
databases and tables using the same lettercase throughout a given query.)
Creating a database does not select it for use, you must do that explicitly.
To make menagerie the current database, use this command:
mysql> USE menagerie
Database changed
Your database needs to be created only once, but you must select it for use
each time you begin a mysql session. You can do this by issuing
a USE statement as shown above. Alternatively, 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: ********
Note that menagerie is not your password on the command just shown.
If you want to supply your password on the command line after the -p
option, you must do so with no intervening space (e.g., 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.
Creating a table
Creating the database is the easy part, but at this point it's empty, as SHOW
TABLES will tell 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 will need, and what columns will be in each of them.
You'll 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's 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's 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'll soon need to send out birthday greetings,
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 for
now: 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 will vary in length.
The lengths of those columns need not all be the same, and need not be 20 .
You can pick any length from 1 to 255 , 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.)
Animal sex can be represented in a variety of ways, for example, "m"
and "f" , or perhaps "male" and "female" .
It's simplest to use the single characters "m" and "f" .
The use of the DATE data type for the birth and death
columns is a fairly obvious choice.
Now that you have created a table, SHOW TABLES should produce
some output:
mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| pet |
+---------------------+
To verify that your table was created the way you expected, use a DESCRIBE
statement:
mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
You can use DESCRIBE any time, for example, if you forget the
names of the columns in your table or what types they are.
Loading data into a table
After creating your table, you need to populate it. The LOAD DATA
and INSERT statements are useful for this.
Suppose your pet records can be described as shown below. (Observe that MySQL
expects dates in YYYY-MM-DD format; this may be different than
what you are used to.)
name
|
owner
|
species
|
sex
|
birth
|
death
|
Fluffy
|
Harold
|
cat
|
f
|
1993-02-04
|
|
Claws
|
Gwen
|
cat
|
m
|
1994-03-17
|
|
Buffy
|
Harold
|
dog
|
f
|
1989-05-13
|
|
Fang
|
Benny
|
dog
|
m
|
1990-08-27
|
|
Bowser
|
Diane
|
dog
|
m
|
1998-08-31
|
1995-07-29
|
Chirpy
|
Gwen
|
bird
|
f
|
1998-09-11
|
|
Whistler
|
Gwen
|
bird
|
|
1997-12-09
|
|
Slim
|
Benny
|
snake
|
m
|
1996-04-29
|
|
Because you are beginning with an empty table, an easy way to populate it is
to create a text file containing a row for each of your animals, then load the
contents of the file into the table with a single statement.
You could create a text file `pet.txt' containing one record per line,
with values separated by tabs, and given in the order in which the columns were
listed in the CREATE TABLE statement. For missing values (such
as unknown sexes, or death dates for animals that are still living), you can
use NULL values. To represent these in your text file, use \N .
For example, the record for Whistler the bird would look like this (where the
whitespace between values is a single tab character):
Whistler
|
Gwen
|
bird
|
\N
|
1997-12-09
|
\N
|
To load the text file `pet.txt' into the pet table, use
this command:
mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;
You can specify the column value separator and end of line marker explicitly
in the LOAD DATA statement if you wish, but the defaults are tab
and linefeed. These are sufficient for the statement to read the file `pet.txt'
properly.
When you want to add new records one at a time, the INSERT statement
is useful. In its simplest form, you supply values for each column, in the order
in which the columns were listed in the CREATE TABLE statement.
Suppose Diane gets a new hamster named Puffball. You could add a new record
using an INSERT statement like this:
mysql> INSERT INTO pet
-> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Note that string and date values are specified as quoted strings here. Also,
with INSERT , you can insert NULL directly to represent
a missing value. You do not use \N like you do with LOAD
DATA .
From this example, you should be able to see that there would be a lot more
typing involved to load your records initially using several INSERT
statements rather than a single LOAD DATA statement.
Extracted from the MySQL
Documentation
|