SQL Create Table
In this lesson, you will learn how to create a database table using SQL.
Create Table In SQL
The CREATE TABLE statement is used to create a new table.
Syntax
CREATE TABLE table_name (
column_name_1 datatype,
column_name_2 datatype,
....
);
Remember, You have to select the database that you want to create the table in it.
Create Table Example
Suppose we already have a selected database called "demo", the following SQL statement creates a new table called "books" in it.
Example
CREATE TABLE books (
title VARCHAR(200),
number_of_pages INT,
author_name VARCHAR(50),
publishing_date DATE
);
Output
The output means that the statement is executed succesfully.
To view the created table in MySQL Workbench, you have to refresh the Schemas Navigator.
After refreshing the Schemas Navigator, you can expand the "demo" database and view the "books" table structure as follows.
Create Table If Not Exists
If you try to create a new table in the database but there is already an existing table with the same name, you will get an error response saying:
To avoid the table already exists error, it's recommended to use the optional clause IF NOT EXISTS when creating a new table to be sure that the statement will execute only if the database is not already have an existing table with the same name.
If you try to create the "books" table again without the optional clause IF NOT EXISTS, you will get an error as follows.
Example
CREATE TABLE books (
title VARCHAR(200),
number_of_pages INT,
author_name VARCHAR(50),
publishing_date DATE
);
Output
However, if you try to recreate the "books" database with the optional clause IF NOT EXISTS, you will get a warning message.
Example
CREATE TABLE IF NOT EXISTS books (
title VARCHAR(200),
number_of_pages INT,
author_name VARCHAR(50),
publishing_date DATE
);
Output
Create Table Exercise
1- Write a query that creates a new database called "training".
Note: you should recreate it if it already exists.
2- Write a query that creates a table called "users" and check whether it exists or not. the table should contain the following fields:
username VARCHAR(50)password VARCHAR(100)earnings DECIMAL(7,2)birth_date DATE
3- Write a query that deletes the "training" database if it exists.
Solution
DROP DATABASE IF EXISTS training;
CREATE DATABASE training;
USE training;
CREATE TABLE IF NOT EXISTS users (
username VARCHAR(50),
password VARCHAR(100),
earnings DECIMAL(7,2),
birth_date DATE
);
DROP DATABASE IF EXISTS training;
