SQL Query To Create Table In MySQL

This tutorial is part of the series Learn Basic SQL Queries Using MySQL. In this tutorial, we will discuss SQL queries to create a table in MySQL.

Create Table Query

The query CREATE TABLE can be used to create a database table. In case you are remotely logged in to the database, you will also need the CREATE privilege for the table in order to create a table.

Thebelow mentioned query creates the table user having an id column in the enterprise database. We need to specify at least one column in order to create a table.

# TODO - Create Table
# Query - CREATE TABLE <table name>(<column 1>, <column 2>)

# It might throw error in case database do not exist or table already exist with the same name
CREATE TABLE `enterprise`.`user`(`user_id` INT);

OR

# Good to go - there won't be any verification of identical structure of the existing table
CREATE TABLE IF NOT EXISTS `enterprise`.`user`(`user_id` INT);

This is the very basic query to create a table in MySQL database.

Column Definition

While adding a column, we have to specify at least its name and data type.  We can also specify optional details of the columns while adding the table as shown below.

<column name> <data type>(<size>) [NOT NULL] [DEFAULT <default value>] [AUTO_INCREMENT]

Column Name - It specifies the name of the column.

Data Type & Size - We have to provide the data type of each and every column with the optional size or display width. For example - INT(8) represents an integer data type having a display length of 8 digits. The display width has no impact on the minimum and maximum values of the data type.

NOT NULL - We can optionally specify whether the column accepts a null value.

Default Value - The DEFAULT keyword can be used to specify the default value in case the column value is not provided while inserting the row data.

Auto Increment - We can optionally specify to auto increment the column value for each new row added to the table.

This is how we can create a table in MySQL using the SQL query.