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,

MySQL

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

MySQL

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

name ownerspeciesFluffyHaroldcatf1993-02-04GwenHarolddogfdogm1990-08-27##BowserDianem## ChirpyGwenbirdf##1998-09-11WhistlerGwen#bird##1997-12-09Slim1996-04-29

##sex

birth

death

##Claws

cat

m

1994-03-17

##Buffy

##1989-05-13

##Fang

Benny

dog

1979-08-31

1995-07-29

Benny

snake

m

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

你可以创建一个文本文件“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语句装载你的初始记录。

 以上就是MySQL入门教程4 —— 创建表并将数据装入表的内容，更多相关内容请关注PHP中文网（www.php.cn）！