MySQL Create Tables

MySQL CREATE TABLE statement is used to create new table in database.

Syntax

CREATE TABLE [IF NOT EXISTS] table_name( column_list ) engine=table_type

Specification

  • First we need to specify the name of table that we want to create after the CREATE TABLE keywords.
  • IF NOT EXISTS is an optional part in create table syntax that check table already exists in the database or not. if table exixts, MySQL will ignore the whole statement.
  • After table_name we need to specify a list of columns for the table in the column_list section. Columns are separated by a comma (,).
  • In Last we need to specify the storage engine for the table. We can use any storage engine such as MyISAM, HEAP, EXAMPLE, CSV,InnoDB , MERGE FEDERATED or NDBCLUSTER,ARCHIVE. if we do not specify the storage engine . MySQL will use default storage engine depend on version.

InnoDB became the default storage engine since MySQL version 5.5.Before MySQL version 5.5, MyISAM is the default storage engine.

Example

Now we will create example table in tutsway database.

CREATE TABLE IF NOT EXISTS example ( example_id int(11) NOT NULL AUTO_INCREMENT, title varchar(45) DEFAULT NULL, date DATE DEFAULT NULL, description varchar(200) DEFAULT NULL, PRIMARY KEY (example_id) ) ENGINE=InnoDB ;

Explanation of above statement are give below.

  • Field attribute "NOT NULL" is being used because we do not want this field to be NULL.
  • Field attribute "AUTO_INCREMENT" tells MySQL add the next available number to the example_id field.
  • Keyword PRIMARY KEY is used to define a column as primary key.