CREATE TABLE in MySQL

CREATE TABLE
In MySQL, the MySQL CREATE TABLE statement is used to create a new table in a database.

Syntax 1: To create a Table in MySQL.

CREATE TABLE table_name (

column_1 data_type column_constraint,

column_2 data_type column_constraint,

... );

CREATE TABLE table_name ( column_1 data_type column_constraint, column_2 data_type column_constraint, ... );

CREATE TABLE table_name (
    column_1 data_type column_constraint,
    column_2 data_type column_constraint,
    ... );

Syntax 2: To see all the already created Tables in a database.

SHOW tables;

SHOW tables;

Syntax 3: To see the structure of an already created Table.

DESCRIBE table_name;

DESCRIBE table_name;

Example 1: Creating a table with NULL and NOT NULL column constraints.

CREATE TABLE items

( id INT NOT NULL AUTO_INCREMENT,

name VARCHAR(100) NOT NULL,

number INT);

CREATE TABLE items ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, number INT);

CREATE TABLE items  
( id INT NOT NULL AUTO_INCREMENT,  
  name VARCHAR(100) NOT NULL,  
  number INT);

Explanation:
Here, ‘items’ is the name of the table, ‘id’ is the name of the first column, ‘name’ is the name of the second column, and ‘number’ is the name of the third column of the table. Also, INT is the data type of both the first and third columns while VARCHAR with a size of 100 is the data type of the second column. The NULL here is a default constraint which means that the column accepts null values while NOT NULL specifies that the column does not accept null values.

Example 2: Creating a table with a PRIMARY KEY column constraint.

CREATE TABLE items

( id INT NOT NULL AUTO_INCREMENT,

name VARCHAR(100) NOT NULL,

number INT,

PRIMARY KEY(id) );

CREATE TABLE items ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, number INT, PRIMARY KEY(id) );

CREATE TABLE items  
( id INT NOT NULL AUTO_INCREMENT,  
  name VARCHAR(100) NOT NULL,  
  number INT,
  PRIMARY KEY(id) );