mysql creates table and loads data into table

1. Create the tables

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

mysql> SHOW TABLES; Empty set (0.00 sec)

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

You will need a table with 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 arithmetic 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 this is the same problem as identifying customers from a business database to whom birthday wishes will be sent soon, because computers help personal contacts.)

· You can do more than just relative to dates is the current date to calculate age. For example, if you store the date of death in a database, you can easily calculate how old a pet was when it died.

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

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 you choose inappropriately and it turns out you need a longer field, MySQL provides an ALTER TABLE statement.)

Multiple types of values ​​can be used to represent gender in animal records, 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 | char(1) | YES | NULL | YES | date | YES | | NULL |
+---------+-------------+------+-----+---------+- ------+
You can use DESCRIBE at any time, for example if you forget the name or type of a column in a 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 MySQL is YYYY-MM-DD; this may be different from what you are used to.)

name owner species 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    

因为你是从一个空表开始的，填充它的一个简易方法是创建一个文本文件，每个动物各一行，然后用一个语句将文件的内容装载到表中。

你可以创建一个文本文件“pet.txt”，每行包含一个记录，用定位符(tab)把值分开，并且以CREATE TABLE语句中列出的列次序给出。对于丢失的值(例如未知的性别，或仍然活着的动物的死亡日期)，你可以使用NULL值。为了在你的文本文件中表示这些内容，使用\N（反斜线，字母N）。例如，Whistler鸟的记录应为(这里值之间的空白是一个定位符)：

name    owner    species    sex    birth    death    

Whistler    Gwen    bird    \N    1997-12-09    \N    

要想将文本文件“pet.txt”装载到pet表中，使用这个命令：

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

请注意如果用Windows中的编辑器（使用\r\n做为行的结束符）创建文件，应使用：

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

（在运行OS X的Apple机上，应使用行结束符'\r'。）

如果你愿意，你能明确地在LOAD DATA语句中指出列值的分隔符和行尾标记，但是默认标记是定位符和换行符。这对读入文件“pet.txt”的语句已经足够。

如果该语句失败，可能是你安装的MySQL不与使用默认值的本地文件兼容。

如果想要一次增加一个新记录，可以使用INSERT语句。最简单的形式是，提供每一列的值，其顺序与CREATE TABLE语句中列的顺序相同。假定Diane把一只新仓鼠命名为Puffball，你可以使用下面的INSERT语句添加一条新记录：

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

注意，这里字符串和日期值均为引号扩起来的字符串。另外，可以直接用INSERT语句插入NULL代表不存在的值。不能使用LOAD DATA中所示的的\N。

从这个例子，你应该能看到涉及很多的键入用多个INSERT语句而非单个LOAD DATA语句装载你的初始记录。