SQL Create Table

Table of Contents

Introduction

The CREATE TABLE command is used to create a new table in SQL.
This tutorial will show you the command syntax and several practical examples to best learn how to use it.

Preliminary operations

First of all, for this tutorial I assume that you have MySQL installed on your computer.
If not, you can download it and follow the instructions to install it.

You probably already know this, but I just want to remember that before creating a table you need to create a database and select it, otherwise you may run into some errors.
Here are the commands for these two operations:

CREATE DATABASE devinsimplewords;
USE devinsimplewords;

If you are in trouble check this post!

How to create a new table in SQL

Creating a new table in SQL 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 does not exist in SQL

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?
SQL does not allow us to create two tables with the same name, so an error is raised.

However in SQL 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 SQL

Using SQL 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 SQL.
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 “SQL Create Table”

Leave a Comment

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