How to create table in MySQL

In this tutorials we will learn how to create tables in MySQL. 

Learn SQL syntax to create mysql table at command line.

MySQL CREATE TABLE syntax

CREATE TABLE [IF NOT EXISTS] table_name(
column_1_definition,
column_2_definition,
…,
table_constraints
) ENGINE=storage_engine;

CREATE TABLE  is reserve word in SQL,Create table will create table in database.[IF NOT EXISTS] is optional ,this will check if table not exists in database ,then the table will be created with the name you specifiy after the [IF NOT EXISTS] .

(Column_1_deinition) define the list of column in the table  you requied for table to store information.table column name will be seprated with comma .

(ENGINE=storage_engine )is  optionally specify  for the table in  the ENGINE clause. At the time of table creation you can use  storage engine such as InnoDB and MyISAM. If you don’t define the storage engine at the time of CREATE TABLE then   MySQL will use InnoDB by default.

Syntax for Create table column

CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT] column_constraint;,
column_2_definition,
…,
table_constraints
) ENGINE=storage_engine;

Below is the details:

  • Thecolumn_name give the name of the column. Each column has a  data type and optional size e.g.,VARCHAR(30) 
  • When column has define with  NOT NULL constraint ensures that the column will not contain NULL.Or black 
  • CHECK  constraint will check the condition which speified in the column , e.g.,check (age>10)  .
  • UNIQUE constraint will check for uniqueness .But in column domain one column will contain null value. 
  • The DEFAULT clouse will put defult value for the column at the time of insert the row .
  • The AUTO_INCREMENT constraint   is used for when we want the column will automatically increments  value of the column  whenever a new row is inserted into the table. Each table has a maximum one AUTO_INCREMENT column.