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.

  • 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