MySQL Getting Started Tutorial 4 - Create a table and load data into the table

1. Create the table

Creating the database is the easy part, but at this point it is empty, as SHOW What TABLES will tell you:

mysql> SHOW TABLES; Empty set (0.00 sec)

The harder part is deciding what your database structure should be: what database tables you need, and what columns are in each database table.

You will need a table containing a record for each of your pets. It may be called a pet table, and it should contain, at a minimum, the name of each animal. Since the name itself is not very interesting, the table should contain additional information. For example, if there is more than one person in your household with pets, you may want to list the owner of each animal. You may also want to record some basic descriptive information such as species and gender.

What’s your age? That might be fun, but storing into a database is not a good thing. Age changes over time, which means you'll want to keep updating your records. Instead, it's better to store a fixed value such as the birthday, so that whenever you need the age, you can calculate it as the difference between the current date and the date of birth. MySQL provides date operation functions, so this is not difficult. Storing date of birth instead of age has other advantages:

·         You can use the database for tasks such as generating reminders of upcoming pet birthdays. (If you think this kind of query is a bit silly, note that it's the same problem as identifying customers from a business database to whom birthday wishes will be sent soon, because computers facilitate personal contact.) Calculates age based on a date, not just the current date. For example, if you store death dates in a database, you can easily calculate how old a pet was when it died.

You might think of other useful types of information in the pet table, but these are enough for now: name, owner, species, gender, birth and death dates.

Use a CREATE TABLE statement to specify the layout of your database table:

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),     -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

VARCHAR is suitable for name, owner, and species columns because the column values ​​are variable-length. The columns don't all have to be the same length, and they don't have to be 20. You can pick any length from 1 to 65535 and choose a value that makes the most sense. (If the selection is inappropriate and it turns out that you need a longer field,

provides an ALTER TABLE statement.) Multiple types of values ​​can be used to represent the values ​​in the animal record. Gender, for example, "m" and "f", or "male" and "female". Using the single characters "m" and "f" is the easiest way.

Obviously, the birth and death columns should use the DATE data class.

After creating the database 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 at any time, for example, if you forget the name or type of a column in the table.

2. Load data into the table

After creating the table, you need to fill in the content. This task can be accomplished through the LOAD DATA and INSERT statements.

Suppose your pet record is described as follows. (Assume that the expected date format in

is YYYY-MM-DD; this may be different than what you are used to.)

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

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

mysql> INSERT INTO pet     -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);