How to Create Tables in MySQL: A Beginner’s Guide 2025

In our last guide, you learned how to install MySQL and log in for the first time. Now, it’s time to build a home for your data! This tutorial will walk you through creating databases and, most importantly, show you exactly how to create tables in MySQL.

The Foundation: Creating Your First Database

Before you can create tables, you need a database to hold them. Think of a database as a dedicated container or a project folder. All your related tables will live inside it.

Creating one is simple. The command is CREATE DATABASE followed by the name you want to give it. Let’s create a database for a blog:

CREATE DATABASE blog;

To start working inside your new database, you need to select it. Use the USE command:

USE blog;

From this point on, any tables you create will be placed inside the ‘blog’ database.

Building the Structure: How to Create Tables in MySQL

If the database is the filing cabinet, a table is the file folder, complete with labeled columns to keep everything organized. The CREATE TABLE command is where you define this structure, specifying each column’s name and the type of data it will hold.

CREATE TABLE Syntax Example

Let’s create a users table for our blog. This table will store user information like their username, email, and password. Here’s the full command:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  password VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Let’s break that down:

• id INT AUTO_INCREMENT PRIMARY KEY: This creates a numeric ID. AUTO_INCREMENT means it automatically counts up (1, 2, 3…) for each new user, and PRIMARY KEY makes it the unique identifier for each row.
• username VARCHAR(50) NOT NULL: This creates a text field for the username that can hold up to 50 characters. NOT NULL means this field cannot be empty.
• email VARCHAR(100) UNIQUE: Another text field for the email. UNIQUE ensures that no two users can have the same email address.
• password VARCHAR(255): A text field for storing the user’s password.
• created_at TIMESTAMP ...: This field automatically records the exact date and time when a user is created.

Understanding MySQL Data Types

Choosing the right data type is crucial for database efficiency and making sure your data is stored correctly. Here are the most common categories you’ll use. For a full list, check out the official MySQL documentation.

Numeric Types

Used for numbers. The most common is INT for whole numbers (e.g., 10, 45, -100). You might also see FLOAT or DECIMAL for numbers with decimal points.

String Types

Used for text. VARCHAR(size) is for variable-length text up to a defined size (e.g., a username). TEXT is for long-form text with no specified limit (e.g., the content of a blog post).

Date & Time Types

Used for temporal data. TIMESTAMP is great for tracking when something was created or modified. DATETIME stores both date and time, while DATE stores only the date.

Boolean (True/False)

MySQL doesn’t have a dedicated BOOLEAN type. By convention, developers use TINYINT(1), where a `1` represents TRUE and a `0` represents FALSE.

📝 Your Turn: Create a ‘posts’ Table

Time to practice! Using what you’ve learned, create a new table called posts for our blog. It should have the following fields:

• An ID that auto-increments and serves as the primary key.
• A `title` that can hold up to 255 characters.
• The `content` of the post.
• An `author_id` to link to the users table (this will be a whole number).
• A `created_at` timestamp.

Give it a try! The solution is below.

… ready?

Solution:

CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT,
  author_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Conclusion

Fantastic work! You now have the fundamental skills to structure a database. You can create databases to hold your projects and define tables with specific columns and data types to store your information logically.

In our next article, we’ll learn how to bring these tables to life by inserting, reading, and managing the actual data within them using commands like INSERT and SELECT. Stay tuned!

Read More: Advanced JavaScript Topics: A Deep Dive into Modules, Event Delegation, Storage, and Asynchronous Concepts

Read More: How to Style React Components: A 2025 Guide