MySql Tutorial 3 – Creating and Using a Database

Contents
1. Creating and Selecting a Database
2. Creating a Table
3. Loading Data into a Table

Once you know how to enter commands, you are ready to access a database.

Suppose that you have several pets in your home (your menagerie) and you would 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 you how to:

• Create a database


• Create a table


• Load data into the table

The menagerie database is 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. A menagerie distribution containing some of the queries and sample data used in the following sections can be obtained from the MySQL Web site. It is available in both compressed tar file and Zip formats at http://dev.mysql.com/doc/.

Use the SHOW statement to find out what databases currently exist on the server:

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
| tmp      |
+----------+

The mysql database describes user access privileges. The test database often is available as a workspace for users to try things out.

The list of databases displayed by the statement may be different on your machine; SHOW DATABASES does not show databases that you have no privileges for if you do not have the SHOW DATABASES privilege. See Section 12.5.4.8, “SHOW DATABASES Syntax”.

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 that you want to call yours menagerie. The administrator needs to execute a command like this:

mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';

where your_mysql_name is the MySQL user name assigned to you and your_client_host is the host from which you connect to the server.

Top

1. 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. However, for a variety of reasons, our
recommended best practice is always to use the same lettercase
that was used when the database was created.)

Note

If you get an error such as ERROR 1044 (42000):
Access denied for user 'monty'@'localhost' to database
'menagerie' when attempting to create a database,
this means that your user account does not have the necessary
privileges to do so. Discuss this with the administrator or
see Section 5.4, “The MySQL Access Privilege System”.

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 in the example. 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 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.
Top

2. Creating a Table

Creating the database is the easy part, but at this point it's
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'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 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.

Note

Prior to MySQL 5.0.3, the upper limit was 255.) 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'.

The use of the DATE data type for the
birth and death columns is
a fairly obvious choice.

Once 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 have.
Top

3. 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 that your pet records can be described as shown here.
(Observe that MySQL expects dates in
'YYYY-MM-DD' format; this may be different
from 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	1979-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
(backslash, capital-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 '/path/pet.txt' INTO TABLE pet;

Note that if you created the file on Windows with an editor that
uses \r\n as a line terminator, you should
use:

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
    -> LINES TERMINATED BY '\r\n';

(On an Apple machine running OS X, you would likely want to use
LINES TERMINATED BY '\r'.)

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.

If the statement fails, it is likely that your MySQL
installation does not have local file capability enabled by
default. See Section 5.3.4, “Security Issues with LOAD DATA LOCAL”, for information
on how to change this.

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 that 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.
Top