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.

  • amust

    informative, explained in an excellent way.

  • UroŇ°

    nice one

  • Gavin Towey

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

  • Prit_987

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

  • Anonymous

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

    • boonkm

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

  • Androider

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

  • grijesh

    Page should be link with INSERT QUERY after CREATE TABLE