Create a basic MySQL table

Contributed by qmchenry  
Tagged: MySQL  

Creating tables in databases is an important first step to storing data. The CREATE TABLE statement is rich and sometimes confusing. This recipe describes the basics of creating a table in MySQL.

Creating a table involves describing the columns and their attributes, whether they contain text, numbers, dates, and so on. In this recipe, we will create a table to hold contact information with four columns: contact_id, name, email, and birthdate.

The contact_id column is an integer number that is 10 decimal places long (therefore, it is created with an INT(10) datatype). This column will act as the primary key for this table, although that is another recipe.

The name column holds the full name of a contact, which we guess will be no longer than 40 characters long, so the datatype is VARCHAR(40).

The contact’s birthdate will be stored as a DATE datatype.

The following SQL command will create a table called contacts as described above:

CREATE TABLE contacts (
contact_id INT(10),
name VARCHAR(40),
birthdate DATE,
);

MySQL doesn’t care if the command includes carriage returns (thoughtfully placed, of course, like not in the middle of a keyword) If you are entering this command from the mysql command-line interface, it will need to be terminated with a semicolon. If it is being submitted through a programming interface, as from a PHP script, the semicolon is optional.