CREATE TABLE in MySQL

CREATE TABLE is a SQL statement used to create a new table.

CREATE TABLE students (
    id INT,
    name VARCHAR(100)
);

This creates a table named students with two columns.

The basic syntax is:

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

Each column definition contains a column name and a data type.

Normally, select the database before creating the table.

USE school;

CREATE TABLE students (
    id INT,
    name VARCHAR(100)
);

The students table is created inside the selected school database.

A simple table can contain only a few columns.

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

The table contains id, name, and age.

The name after CREATE TABLE is the table name.

CREATE TABLE employees (
    id INT,
    name VARCHAR(100)
);

Here, employees is the table name.

Column names identify the type of information stored in each column.

CREATE TABLE students (
    id INT,
    name VARCHAR(100),
    mobile VARCHAR(15),
    course VARCHAR(100)
);

Each column should have a suitable data type.

id       INT
name     VARCHAR(100)
fee      DECIMAL(10,2)
dob      DATE

The data type determines the kind of value that can be stored in a column.

INT is commonly used for whole numbers.

CREATE TABLE students (
    id INT,
    age INT
);

Values such as 1, 25, and 100 can be stored in INT columns.

VARCHAR is commonly used for variable-length text.

CREATE TABLE students (
    name VARCHAR(100),
    course VARCHAR(100)
);

The number inside VARCHAR represents the maximum length for the column definition.

DECIMAL is useful for exact decimal values such as fees or prices.

CREATE TABLE fees (
    id INT,
    amount DECIMAL(10,2)
);

The DATE data type can be used to store calendar dates.

CREATE TABLE students (
    id INT,
    name VARCHAR(100),
    admission_date DATE
);

A primary key uniquely identifies each row in a table.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    course VARCHAR(100)
);

Here, id is the primary key.

The NOT NULL constraint prevents a column from storing NULL values.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Every student record must have a value for the name column.

A DEFAULT value is automatically used when a value is not supplied for the column.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    status VARCHAR(20) DEFAULT 'Active'
);

A column can have more than one constraint.

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    status VARCHAR(20) DEFAULT 'Active'
);

Here, the id column has both PRIMARY KEY and AUTO_INCREMENT.

AUTO_INCREMENT automatically generates a new numeric value for a column when a new row is inserted.

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

You can use IF NOT EXISTS to avoid an error when a table with the same name already exists.

CREATE TABLE IF NOT EXISTS students (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

After creating a table, use SHOW TABLES to see the tables in the selected database.

SHOW TABLES;

The newly created table should appear in the result.

Use DESCRIBE to inspect the table structure.

DESCRIBE students;

You can also use the shorter form:

DESC students;

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150),
    salary DECIMAL(10,2)
);

This table stores basic employee information.

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(150) NOT NULL,
    price DECIMAL(10,2),
    quantity INT
);

This structure can be used for a basic inventory or shopping application.

CREATE TABLE courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL,
    duration VARCHAR(50),
    fee DECIMAL(10,2)
);

This table can store course information.

CREATE TABLE admissions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100) NOT NULL,
    admission_date DATE,
    course VARCHAR(100)
);

The admission_date column can store the admission date.

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT,
    fee DECIMAL(10,2),
    admission_date DATE,
    active BOOLEAN
);

A table can contain columns using different data types.

You can specify the database name directly when creating a table.

CREATE TABLE school.students (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

This creates the students table inside the school database.

Common problems include:

• No database selected
• Table already exists
• Invalid column definition
• Incorrect data type
• Missing comma between columns
• Missing closing parenthesis
• Insufficient privileges

Column definitions inside CREATE TABLE are separated by commas.

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

Do not forget the commas between column definitions.

1. Create database
2. Select database
3. Plan table columns
4. Select suitable data types
5. Add primary key if required
6. Add constraints
7. Execute CREATE TABLE
8. Run SHOW TABLES
9. Run DESCRIBE table_name

CREATE DATABASE IF NOT EXISTS school;

USE school;

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    mobile VARCHAR(15),
    course VARCHAR(100),
    fee DECIMAL(10,2),
    admission_date DATE
);

SHOW TABLES;

DESCRIBE students;

This example creates a database, selects it, creates a complete students table, and checks the table structure.

Let's create a practical student table for a training institute.

CREATE DATABASE IF NOT EXISTS training_db;

USE training_db;

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100) NOT NULL,
    mobile VARCHAR(15) NOT NULL,
    email VARCHAR(150),
    course VARCHAR(100) NOT NULL,
    fee DECIMAL(10,2) DEFAULT 0.00,
    admission_date DATE,
    status VARCHAR(20) DEFAULT 'Active'
);

INSERT INTO students
(student_name, mobile, email, course, fee, admission_date)
VALUES
('Rahul Kumar', '9876543210', 'rahul@example.com',
 'Python Full Stack', 15000.00, '2026-09-21');

SELECT * FROM students;

This example demonstrates how CREATE TABLE can be used to build a practical table for a real-world application.