How to Create a Basic MySQL Table

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.

8 Responses to “How to Create a Basic MySQL Table”

  1. January 08, 2009 at 3:29 am, amust said:

    informative, explained in an excellent way.

    Reply

  2. February 14, 2009 at 11:26 pm, UroŇ° said:

    nice one

    Reply

  3. May 29, 2009 at 12:57 am, Gavin Towey said:

    The create table definition you have has a syntax error. There should be no comma after “birthdate DATE”

    Reply

  4. December 09, 2010 at 1:42 pm, Prit_987 said:

    i want to add new field in between two field in sql then????

    Reply

  5. June 11, 2011 at 6:39 am, Anonymous said:

    i am created but ” 1046 no database selected ” plz tell me how to created

    Reply

    • June 15, 2012 at 8:28 am, boonkm said:

      either you didnt select a database or there is no existing datase…
      to create a database:
      CREATE DATABASE databasename;
      >

      Reply

  6. April 10, 2012 at 5:48 pm, Androider said:

    Hmm, nice and simple, I’ve been meaning to get into SQL for some time now…. Cheers!

    Reply

  7. March 11, 2013 at 6:11 am, grijesh said:

    Page should be link with INSERT QUERY after CREATE TABLE

    Reply

Leave a Reply