MySQL Create Table

Table of Contents

Introduction

MySQL provides a command to create a new table; this command is called CREATE TABLE.
This tutorial will show you the command syntax and several practical examples to best learn how to use it.

Preliminary operations

Since we are going to be using MySQL it is important that it is installed on your computer.
If you haven’t done it yet and don’t know how to do it, you can download and install it with these instructions.

Once this is done, you must have at least one database, otherwise it will not be possible to create any tables.
To create a database and select it you can use the following commands:

CREATE DATABASE devinsimplewords;
USE devinsimplewords;

If you are in trouble check this post!

How to create a new table in MySQL

Creating a new table in MySQL is a simple task if you know how to do it.
The command has the following syntax:

CREATE TABLE table_name (
    column_name_1 type,
    column_name_2 type,
    column_name_3 type,
    ....
    column_name_n type
);

Starting from this syntax, suppose we want to create a Users table like this:

Column NameColumn Type
idInteger
nameString
surnameString
cityString
Users table description

Given this information we can create the table using this command:

CREATE TABLE Users (
    id int,
    name varchar(255),
    surname varchar(255),
    city varchar(255)
);

How to create a new table if it already exists in MySQL

In the previous section we saw how to create a table, in particular we created the Users table.
But what happens if we try to repeat the same command?
MySQL does not allow us to create two tables with the same name, so an error is raised.

However in MySQL it is possible to slightly modify the CREATE TABLE command to work around this problem.

CREATE TABLE if not exists Users(
    id int,
    name varchar(255),
    surname varchar(255),
    city varchar(255)
);

By adding if not exists in fact we can run the same command several times and the table will be created only the first time.
Mistakes are gone too!

How to create a new table from another table in MySQL

Using MySQL it is possible to create a new table starting from an existing one, basically we are talking about a copy.

IMPORTANT:
Keep in mind that not only the structure will be duplicated, but also the data (if any).

The syntax is the following:

CREATE TABLE table_name AS
    SELECT column_1, column_2,..., column_n | *
    FROM existing_table_name
    WHERE <condition>;

Let’s now see the command to execute:

CREATE TABLE Users2 AS (
SELECT * 
FROM Users
);

Conclusion

Here we are at the end of this tutorial, as always I hope it was useful to you and that now you know how to best manage each scenario for creating new tables using MySQL.
If you are in trouble, do not hesitate to write me in the comments, otherwise take a look at the latest posts!

2 thoughts on “MySQL Create Table”

Leave a Comment

Your email address will not be published. Required fields are marked *