How to Create a Table in MySQL: Complete Step-by-Step Guide for Beginners

on

 

Introduction

Creating a database is only the beginning of working with MySQL. Once you have a database, you need tables to organize and store your actual information.

In our previous tutorial, we created a database called StudentDB. Now we'll take the next practical step and create a Students table inside that database.

This guide is designed for beginners. You don't need advanced SQL knowledge. We'll create the table using MySQL Workbench, write the SQL ourselves, add some sample data, and check the final result.

If you are new to MySQL? You may want to start with our guide on What Is MySQL? before continuing.

Installation reminder 

If you do not install and download MySQL before in your computer then you can also learn this from our tutorial about How to install and download MySQL in window 11 and 10, and also in MacOS.

What Is a Table in MySQL?

A table is a structured place inside a database where related information is stored.

Think of a table like a spreadsheet. It contains:

  • Columns, which describe the type of information being stored
  • Rows, which contain individual records

For example, a Students table could look like this:

StudentID

Name

Age

Department

1

Ali

20

Computer Science

2

Sara

21

Information Technology

3

Ahmed

19

Software Engineering

Each row represents one student, while each column represents a particular piece of information.

What You Need Before Creating a Table

Before following the steps, make sure:

  • MySQL Server is installed
  • MySQL Workbench is installed
  • You can connect to your MySQL Server
  • You have already created a database

If you followed our previous tutorial, you should already have:

StudentDB

If you haven't created it yet, see our guide:

How to Create a MySQL Database: Step-by-Step Tutorial

Step 1: Open MySQL Workbench

Open MySQL Workbench on your computer.

Select your local MySQL connection and enter your password if requested.

Once connected, you'll see the SQL Editor.

If you're following along, keep MySQL Workbench open because we'll use the SQL Editor throughout this tutorial.

Step 2: Select Your Database

Before creating the table, tell MySQL which database you want to work with.

Run:

USE StudentDB;

Then click the Execute button.

This tells MySQL that StudentDB is the database where we want to create our table.

You can confirm the available databases with:

SHOW DATABASES;

Step 3: Understand the CREATE TABLE Statement

Now we're ready to create our first table.

The basic syntax is:

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

Here:

  • CREATE TABLE tells MySQL to create a new table.
  • table_name is the name of your table.
  • Each column needs a name and a data type.
  • The semicolon marks the end of the SQL statement.

Step 4: Create the Students Table

Let's create a practical example.

Enter:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT,
    Department VARCHAR(100)
);

Now click Execute.

If everything is correct, MySQL will create the Students table inside StudentDB.

Understanding the Columns

Let's break down what we just created.

StudentID INT PRIMARY KEY

StudentID stores the student's identification number.

INT means the column stores whole numbers.

PRIMARY KEY makes each student ID unique.

Name VARCHAR(100)

This column stores the student's name.

VARCHAR(100) allows variable-length text with a maximum length of 100 characters.

Age INT

The Age column stores whole numbers such as 18, 20, or 25.

Department VARCHAR(100)

This column stores the student's department, such as Computer Science or Information Technology.

Why choose the right data type?
Data types tell MySQL what kind of values a column is designed to store. Choosing an appropriate type makes your table easier to manage and helps prevent invalid data.

Step 5: Check Whether the Table Was Created

Now let's check our database.

Run:

SHOW TABLES;

You should see:

Students

This confirms that the table exists inside StudentDB.

Step 6: View the Table Structure

You can also inspect the structure of your table.

Run:

DESCRIBE Students;

MySQL will display information about the columns, including their names, data types, keys, and other properties.

Step 7: Add Data to Your Table

Our table exists, but it doesn't contain any student records yet.

Let's add three students.

Run:

INSERT INTO Students
(StudentID, Name, Age, Department)
VALUES
(1, 'Ali', 20, 'Computer Science'),
(2, 'Sara', 21, 'Information Technology'),
(3, 'Ahmed', 19, 'Software Engineering');

Click Execute.

Step 8: Display the Data

Now let's see what we have stored.

Run:

SELECT * FROM Students;

The result should display your records in a table.

StudentID

Name

Age

Department

1

Ali

20

Computer Science

2

Sara

21

Information Technology

3

Ahmed

19

Software Engineering

Your first MySQL table is now created and populated with data.

Creating a Table Using MySQL Workbench's GUI

You can also create a table without writing the entire SQL statement manually.

In MySQL Workbench:

  1. Find Schemas on the left.
  2. Expand StudentDB.
  3. Right-click Tables.
  4. Select Create Table.
  5. Enter the table name.
  6. Add your columns.
  7. Select appropriate data types.
  8. Set the primary key where required.
  9. Click Apply.

Workbench will generate the SQL statement for you.

Which method should beginners use?

Both methods are useful.

The GUI method is convenient when you're just starting, while writing the CREATE TABLE statement yourself helps you understand SQL and is an important skill for database development.

Common Mistakes When Creating MySQL Tables

1. Forgetting the semicolon

Incorrect:

CREATE TABLE Students (
    StudentID INT
)

Better:

CREATE TABLE Students (
    StudentID INT
);

2. Using the wrong data type

Don't use a numeric type for information that is primarily text.

For example, a department name should normally use a character type such as VARCHAR.

3. Creating a table in the wrong database

Always check the selected database:

USE StudentDB;

4. Using duplicate primary key values

A primary key must uniquely identify each record.

For example, you shouldn't have two students with:

StudentID = 1

5. Forgetting quotation marks around text

Text values normally need single quotation marks:

'Computer Science'

not:

Computer Science

Useful MySQL Commands You've Learned

At this point, you have learned several important SQL commands:

USE StudentDB;

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT,
    Department VARCHAR(100)
);

SHOW TABLES;

DESCRIBE Students;

INSERT INTO Students
(StudentID, Name, Age, Department)
VALUES
(1, 'Ali', 20, 'Computer Science');

SELECT * FROM Students;

Don't just read these commands. Try changing the examples yourself. For example, create an Employees, Books, or Products table and experiment with different columns.

What's Next?

You now know how to create a MySQL table, define columns, choose basic data types, insert records, and view the stored information.

But there's one important topic behind all of this: data types.

Why did we use INT for age?
Why did we use VARCHAR for names?
When should we use DATE, DECIMAL, or other types?

In our next tutorial, we'll explain MySQL data types with simple examples so you can choose the right type for each column.

If you're building your MySQL skills step by step, bookmark this tutorial and return to it while practicing your SQL commands.

Frequently Asked Questions

What is a table in MySQL?

A table is a structured part of a database used to store related information. It organizes data into rows and columns, making it easier to store, retrieve, and manage records.

Which SQL command is used to create a table?

The CREATE TABLE statement is used to create a new table. You specify the table name, columns, and data types within the statement.

CREATE TABLE Students (
    StudentID INT,
    Name VARCHAR(100)
);

Can I create multiple tables in one MySQL database?

Yes. A single database can contain many tables. For example, a university database could have separate tables for Students, Courses, Teachers, Departments, and Results.

What is a primary key in a MySQL table?

A primary key is a column, or combination of columns, used to uniquely identify records in a table. A primary key cannot contain duplicate values.

Can I create a MySQL table without MySQL Workbench?

Yes. You can create tables using the MySQL command-line client or other compatible database management tools. MySQL Workbench simply provides a convenient graphical environment for working with MySQL.

What should I learn after creating a MySQL table?

A good next step is learning MySQL data types. After that, you can learn how to insert, retrieve, update, and delete records using SQL.

Conclusion

Creating a table is one of the first important practical skills you need when learning MySQL. In this tutorial, we created a Students table inside StudentDB, defined columns with appropriate data types, added a primary key, inserted sample records, and retrieved the information using SQL.

The best way to learn is to practice. Try creating your own table and change the column names, data types, and records.

Once you're comfortable with this process, you're ready to move on to MySQL data types, which will help you design tables more accurately.

Comments

Post a Comment