The MySQL CREATE TABLE Statement
The CREATE TABLE statement is used to create a new table in a database.
Syntax
CREATE TABLE table_name [
column1 datatype,
column2 datatype,
column3 datatype,
....
];
The column parameters specify the names of the columns of the table.
The datatype parameter specifies the type of data the column can hold [e.g. varchar, integer, date, etc.].
Tip: For an overview of the available data types, go to our complete Data Types Reference.
MySQL CREATE TABLE Example
The following example creates a table called "Persons" that contains five columns: PersonID, LastName, FirstName, Address, and City:
Example
CREATE TABLE Persons
[
PersonID int,
LastName varchar[255],
FirstName varchar[255],
Address varchar[255],
City varchar[255]
];
The PersonID column is of type int and will hold an integer.
The LastName, FirstName, Address, and City columns are of type varchar and will hold characters, and the maximum length for these fields is 255 characters.
The empty "Persons" table will now look like this:
Tip: The empty "Persons" table can now be filled with data with the SQL INSERT INTO statement.
Create Table Using Another Table
A copy of an existing table can also be created using CREATE TABLE.
The new table gets the same column definitions. All columns or specific columns can be selected.
If you create a new table using an existing table, the new table will be filled with the existing values from the old table.
Syntax
CREATE TABLE new_table_name AS
SELECT column1, column2,...
FROM existing_table_name
WHERE ....;
The following SQL creates a new table called "TestTables" [which is a copy of the "Customers" table]:
Example
CREATE TABLE TestTable AS
SELECT customername,
contactname
FROM customers;
Test Yourself With Exercises
Exercise:
Write the correct SQL statement to create a new table called Persons.
[ PersonID int, LastName varchar[255], FirstName varchar[255], Address varchar[255], City varchar[255] ];
Start the Exercise
3.3.2 Creating a Table
Creating the database is the easy part, but at this point it is 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 is 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 is 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. 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.
For more information about MySQL data types, see Chapter 11, Data Types.