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.