How to Create a Basic MySQL Table

Posted February 18, 2012 by Quinn McHenry in MySQL

Creating tables in databases is an important first step for storing data. The CREATE TABLE statement is rich and sometimes confusing. This tech-recipe with an example 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 tech-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.

The name column holds the full name of a contact. We suppose this 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 does not 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. Additional details about the syntax can referenced in the official MySQL documentation.

 

About Quinn McHenry

Quinn was one of the original co-founders of Tech-Recipes. He is currently crafting iOS applications as a senior developer at Small Planet Digital in Brooklyn, New York.
View more articles by Quinn McHenry

The Conversation

Follow the reactions below and share your own thoughts.