Creating Tables

Introduction

With the "school" database created and MySQL's data types understood, you are ready to create your first real table. This lesson walks through the CREATE TABLE statement and uses it to build the students table you will keep working with in upcoming lessons.

You will also learn two essential commands for checking your work: SHOW TABLES, which lists every table in a database, and DESCRIBE, which reveals the exact structure of one specific table.

The CREATE TABLE Statement

A table is created with CREATE TABLE, followed by the table name, and then a comma-separated list of column definitions inside parentheses. Each column definition has a name and a data type.

CREATE TABLE table_name (
    column1_name DATA_TYPE,
    column2_name DATA_TYPE,
    column3_name DATA_TYPE
);

Every column needs both a name (chosen by you) and a data type (from the types covered in the previous lesson). Column definitions are separated by commas, and the entire statement ends with a semicolon.

Creating the students Table

Using the "school" database from Lesson 9, here is the full statement to create a students table with four columns: id, name, age, and grade.

USE school;

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

Database changed
Query OK, 0 rows affected (0.03 sec)

This creates an empty table — no rows yet, only the structure. id and age and grade are whole numbers, so they use INT; name is variable-length text, so it uses VARCHAR(50), allowing up to 50 characters.

Listing Tables

To see every table that exists in the currently selected database, use SHOW TABLES.

SHOW TABLES;

+-------------------+
| Tables_in_school  |
+-------------------+
| students          |
+-------------------+

Since "school" currently has only one table, students is the only row returned. As you add more tables in later lessons — like teachers or courses — they would all appear here.

Inspecting a Table's Structure

To see the exact structure of one table — every column, its data type, and other details — use DESCRIBE followed by the table name.

DESCRIBE students;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
| grade | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

This confirms each column's name, its exact data type, whether NULL (empty) values are currently allowed, and other details you will fill in as you add constraints. Right now, every column allows NULL because no rules have been added yet.

A Note on Constraints

The students table created in this lesson has no rules yet — nothing stops you from inserting a duplicate id, a negative age, or leaving a name blank. That is intentional, so this lesson could focus purely on table structure.

Constraints, like PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, and CHECK, can be added directly inside a CREATE TABLE statement to enforce exactly these kinds of rules. Each one gets its own dedicated lesson soon, starting with Constraints and then Primary Key.

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

CREATE TABLE is how every table in MySQL begins its life, and SHOW TABLES and DESCRIBE are the two commands you will reach for constantly to confirm exactly what a database and its tables look like.

In this lesson, you created a real students table inside the "school" database, listed it with SHOW TABLES, and inspected its structure with DESCRIBE. Next, you will learn how to modify an existing table's structure using ALTER TABLE.