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. You can also follow MySQL Data Types Cheatsheet to know the available data types in MySQL. 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.