Create MySQL Tables

A table is a structure inside a database used to store related data.

For example, a student table can store student information such as name, email, mobile number, and course.

student_api
     ↓
students table

The database itself is a container. Tables are used to organize the actual application data.

Our REST API may need tables for:

• Users
• Students
• Courses
• Other application data

Before creating a table, select the database where the table should be created.

USE student_api;

This tells MySQL that the following table operations should be performed inside the student_api database.

The SQL command used to create a table is:

CREATE TABLE table_name (
    column_name data_type
);

We specify the table name, columns, and data types.

For our Student Management API, we can create a basic students table.

CREATE TABLE students (
    id INT,
    name VARCHAR(100),
    email VARCHAR(150),
    mobile VARCHAR(20),
    course VARCHAR(100)
);

This creates five columns for storing student information.

Columns define the type of information stored in a table.

students

id
name
email
mobile
course

Each student record will contain values for these columns.

The id column can be used to uniquely identify each student.

id INT

An integer is commonly used for an ID column.

The VARCHAR data type is commonly used for text values.

name VARCHAR(100)

The number specifies the maximum character length for that column.

The email address can be stored in a VARCHAR column.

email VARCHAR(150)

This provides enough space for most normal email addresses.

A mobile number can also be stored as text.

mobile VARCHAR(20)

Storing phone numbers as text can preserve values such as country codes and leading zeros.

The course name can be stored using a VARCHAR column.

course VARCHAR(100)

For example:

ADCA
Java
Python
React Native

A primary key uniquely identifies each record in a table.

For our students table, the id column is a suitable primary key.

id INT PRIMARY KEY

AUTO_INCREMENT allows MySQL to automatically generate a new numeric ID for each inserted record.

id INT AUTO_INCREMENT PRIMARY KEY

For example, the IDs can be generated as:

1
2
3
4
5

The NOT NULL constraint prevents a column from receiving a NULL value.

name VARCHAR(100) NOT NULL

This can be used when a value is required.

We can combine the primary key, AUTO_INCREMENT, and NOT NULL constraints.

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150),
    mobile VARCHAR(20),
    course VARCHAR(100)
);

After creating the table, its basic structure will look like:

students
-------------------------
id
name
email
mobile
course

The SHOW TABLES command displays tables inside the currently selected database.

SHOW TABLES;

You should see:

students

The DESCRIBE command displays the structure of a table.

DESCRIBE students;

It can show the column names, data types, NULL settings, keys, and other properties.

DESC is a shorter form of DESCRIBE.

DESC students;

It can be used to quickly inspect the structure of the table.

If a table may already exist, use IF NOT EXISTS.

CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150),
    mobile VARCHAR(20),
    course VARCHAR(100)
);

This prevents an error when the table already exists.

A REST API project can have multiple related tables.

student_api
│
├── users
├── students
└── courses

Each table can store a different category of information.

A users table can store login information for users of the application.

users
----------------
id
name
email
password

We will work with authentication-related tables later in the course.

A courses table can store information about available courses.

courses
----------------
id
course_name
duration
fee

Tables can be designed according to the requirements of the application.

The REST API will use SQL queries to work with the tables.

React Native
      ↓
PHP REST API
      ↓
SQL Query
      ↓
students table

For example, a GET API can retrieve student records from the table.

• Forgetting the database selection
• Missing commas between columns
• Using an invalid data type
• Missing closing parenthesis
• Using a duplicate table name
• Incorrect primary key definition

Carefully check the SQL syntax when creating a table.

After creating the table, use:

SHOW TABLES;

Then inspect the structure using:

DESCRIBE students;

These commands help verify that the table was created correctly.

Here is a complete example for creating our students table:

USE student_api;

CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150),
    mobile VARCHAR(20),
    course VARCHAR(100)
);

SHOW TABLES;

DESCRIBE students;

student_api
│
└── students
    ├── id
    ├── name
    ├── email
    ├── mobile
    └── course

Now the database has a table that can store student records.

Once the database and tables are ready, PHP can connect to them and perform CRUD operations.

Create → INSERT
Read   → SELECT
Update → UPDATE
Delete → DELETE

These operations will become the foundation of our REST API.

In this lesson, we created the structure required to store student data in MySQL. We learned about tables, columns, data types, primary keys, AUTO_INCREMENT, NOT NULL, and useful commands such as SHOW TABLES and DESCRIBE.

student_api
      ↓
students
      ↓
id
name
email
mobile
course