MySQL CREATE TABLE

What is the CREATE TABLE Statement in MySQL and Why Do We Use It?

When you want to create a new table in MySQL, you type the CREATE TABLE statement. The CREATE TABLE statement is used to create a new table with a defined structure where you can store data.

A table contains columns and rows. Before inserting data into a database, you first need to create a table using the CREATE TABLE statement.

Syntax:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype
);

Example:

CREATE TABLE Students (
    id INT,
    name VARCHAR(50),
    age INT
);

Output:

If the table is created successfully, MySQL displays the following message:

Query OK, 0 rows affected

If the table already exists, MySQL displays the following error message:

ERROR 1050 (42S01): Table 'Students' already exists

How the CREATE TABLE Statement Works Internally

When you want to create a table in MySQL, you type the CREATE TABLE statement. After pressing the Enter key, the CREATE TABLE statement is sent to the MySQL server. The server checks the table name and the given column information. If the table name does not exist, MySQL creates a new table with the defined structure. If the table already exists, MySQL displays an error message.

Remember that the CREATE TABLE statement only creates the structure of a table. It does not insert any data into the table. After creating a table, you can use SQL statements such as INSERT, UPDATE, DELETE, and SELECT to work with table data.

Flowchart:

👤 User
   |
   ▼
⌨️ CREATE TABLE table_name;
   |
   ▼
🖥️ MySQL Server
   |
   ▼
🔍 Check Table Already Exists
   |
 ┌───────┴───────┐
 ▼               ▼
✅ No        ❌ Yes
 |               |
 ▼               ▼
📁 Create   ⚠️ Error
 Table          Message
 |
 ▼
✅ Table Created

Precautions While Using CREATE TABLE Statement:

1. Before using the CREATE TABLE statement, you should know the correct table name and column names.
2. Before creating a table, make sure that the table name does not already exist in the selected database.
3. You should select the correct database before creating a table.
4. Before creating a table, choose the correct data type for each column.

How to Use the CREATE TABLE Statement in MySQL: Step-by-Step Guide

If you use the CREATE TABLE statement in MySQL, you first need to select the database where you want to create the table. After selecting the database, you use the CREATE TABLE statement to create a new table. The complete process is as follows:

Step 1: You connect to the MySQL server by using the MySQL login command.

mysql -u username -p

Step 2: You select the required database by using the USE statement.

USE database_name;

Step 3: You create a new table by typing the CREATE TABLE statement.

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype
);

Step 4: After executing the statement, MySQL creates the table with the defined columns and structure.

Note:

• The CREATE TABLE statement creates a new table inside the selected database.
• The CREATE TABLE statement only creates the table structure; it does not store any data.
• A table name must be unique inside a database.

Don't Confuse CREATE TABLE with CREATE DATABASE Statement

Many beginners get confused between the CREATE TABLE statement and the CREATE DATABASE statement because both are used to create objects in MySQL. However, they perform different tasks. The CREATE DATABASE statement creates a new database, whereas the CREATE TABLE statement creates a new table inside an existing database.

Therefore, when you want to create a new database, use the CREATE DATABASE statement. After creating and selecting a database, use the CREATE TABLE statement to create tables inside that database.