Creating tables is a fundamental step in designing any relational database. In this chapter, you’ll learn how to create tables in MySQL using SQL commands.

🔰 What is a Table?

A table is a structured format for storing data in rows and columns. Each column has a specific data type, and each row represents a record.

✅ Syntax for Creating a Table

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

Example:

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(150) UNIQUE,
    birthdate DATE,
    is_active BOOLEAN DEFAULT TRUE
);

🔍 Explanation of the Example

🛠 Common Data Types

🧱 Adding Constraints

🔗 Example with Foreign Key

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);

CREATE TABLE enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

📋 View Table Structure

DESCRIBE students;

🧹 Drop Table (Use with Caution)

DROP TABLE students;

📝 Best Practices

• Use snake_case for table and column names (e.g., first_name)
• Always define a PRIMARY KEY
• Use appropriate data types and constraints
• Use FOREIGN KEYS to maintain relationships

🎯 Summary