`CREATE TABLE` Query in DBMS (MySQL) [ English ]

Languages

• English

Introduction

In a Database Management System (DBMS), data is stored in the form of tables. A table consists of rows and columns where each column represents a field (attribute) and each row represents a record.

Before storing any data in a database, it is necessary to create a table structure that defines the columns, their data types, and constraints. The CREATE TABLE command is used to create a new table in the selected database.

Definition

The CREATE TABLE query is a Data Definition Language (DDL) command used to create a new table in a database with specified columns, data types, and constraints.

Syntax

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

Where:

• table_name – The name of the table to be created.
• column1, column2, column3 – The names of the columns in the table.
• datatype – The type of data stored in the column (such as INT, VARCHAR, DATE, etc.).

How It Works

When the CREATE TABLE command is executed:

1. The DBMS checks whether the selected database exists.
2. A new table structure is created in the database.
3. Column names and their data types are stored in the system catalog (metadata).
4. The table becomes ready to store records.

Example

First select the database:

USE student_db;

Now create a table:

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

This command creates a table named students with four columns:

Example of Table Structure

Each row represents a record, and each column represents a field.

Using IF NOT EXISTS

To avoid errors if a table already exists, MySQL provides the IF NOT EXISTS option.

Syntax

CREATE TABLE IF NOT EXISTS table_name (
    column1 datatype,
    column2 datatype
);

Example

CREATE TABLE IF NOT EXISTS courses (
    course_id INT,
    course_name VARCHAR(50)
);

Common Data Types Used in Tables

Key Points

• CREATE TABLE is a DDL command.
• It is used to create a new table in a database.
• A table consists of columns and rows.
• Each column must have a data type.
• The table is created in the currently selected database.

Summary

The CREATE TABLE command is used to define the structure of a table in a database. It specifies the table name, column names, and data types. After creating a table, users can store and manage data using SQL commands such as INSERT, SELECT, UPDATE, and DELETE.