MySQL: Introduction to MySQL and Basic Database Concepts

MySQL is the world's most popular open-source relational database management system—it makes data storage and querying simple and efficient.

This tutorial is designed for beginners with no prior experience. It starts with the basics of databases and progresses until you are proficient in using MySQL for data management.

1. What You'll Learn

• What Is a Database and Why Do We Need One?
• The Difference Between Relational and Non-Relational Databases
• The History and Version Evolution of MySQL
• Key Advantages and Use Cases of MySQL
• Basics of Installing and Connecting to MySQL

2. A Developer’s True Story

(1) Pain Point: Excel Can’t Handle It Anymore

An e-commerce operations manager was assigned an urgent task:

"Compile sales trends for the past 12 months, covering 5,000 customers and 30,000 orders—and present the report to the CEO tomorrow morning."

Open an Excel file: 100,000+ rows of data, file size 50 MB.

The problems came one after another:

• Excel takes 3 minutes to open, and it lags every time I apply a filter
• I want to link the "Customer Table" and the "Order Table," but I've been using VLOOKUP until I'm ready to pull my hair out.
• The data has been updated, but the report does not refresh automatically.
• Three colleagues editing at the same time? File conflicts, data loss

(2) Database Solutions

Redesigning with MySQL:

-- Create a Customer Table
CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(150)
);

-- Create the Orders Table
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    amount DECIMAL(10,2),
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- One SQL Identify Annual Sales Trends
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_revenue
FROM orders
WHERE order_date >= '2025-07-01'
GROUP BY month
ORDER BY month;

Yield Comparison:

3. What Is a Database?

A database is a repository for organizing, storing, and managing data according to a data structure.

(1) Databases vs. File Storage

graph TB
    A[Data Storage Methods] --> B[File Storage]
    A --> C[Database]
    B --> D[Excel / CSV / TXT]
    B --> E[Manual Management，Low efficiency]
    C --> F[Structured Storage]
    C --> G[Efficient Queries，Safe and Reliable]

graph TB
    A[Data Storage Methods] --> B[File Storage]
    A --> C[Database]
    B --> D[Excel / CSV / TXT]
    B --> E[Manual Management，Low efficiency]
    C --> F[Structured Storage]
    C --> G[Efficient Queries，Safe and Reliable]

(2) Database Categories

graph TB
    A[Database] --> B[Relational Database RDBMS]
    A --> C[Non-relational database NoSQL]
    B --> D[MySQL / PostgreSQL]
    B --> E[Oracle / SQL Server]
    B --> F[SQLite]
    C --> G[Key-value type: Redis]
    C --> H[Document-based: MongoDB]
    C --> I[Column Type: Cassandra]
    C --> J[Graph Database: Neo4j]

graph TB
    A[Database] --> B[Relational Database RDBMS]
    A --> C[Non-relational database NoSQL]
    B --> D[MySQL / PostgreSQL]
    B --> E[Oracle / SQL Server]
    B --> F[SQLite]
    C --> G[Key-value type: Redis]
    C --> H[Document-based: MongoDB]
    C --> I[Column Type: Cassandra]
    C --> J[Graph Database: Neo4j]

4. Core Concepts of Relational Databases

(1) Table

A table is the basic unit for storing data in a database and consists of rows and columns.

graph LR
    subgraph customers Table
        direction TB
        H[id | name | email | created_at]
        R1[1 | Alice | alice@email.com | 2025-01-15]
        R2[2 | Bob | bob@email.com | 2025-02-20]
        R3[3 | Charlie | charlie@email.com | 2025-03-10]
    end

graph LR
    subgraph customers Table
        direction TB
        H[id | name | email | created_at]
        R1[1 | Alice | alice@email.com | 2025-01-15]
        R2[2 | Bob | bob@email.com | 2025-02-20]
        R3[3 | Charlie | charlie@email.com | 2025-03-10]
    end

(2) Relationship Types

erDiagram
    CUSTOMERS ||--o{ ORDERS : "A customer has multiple orders"
    ORDERS ||--|{ ORDER_ITEMS : "An order contains multiple line items"
    PRODUCTS ||--o{ ORDER_ITEMS : "An item appears in multiple orders"

erDiagram
    CUSTOMERS ||--o{ ORDERS : "A customer has multiple orders"
    ORDERS ||--|{ ORDER_ITEMS : "An order contains multiple line items"
    PRODUCTS ||--o{ ORDER_ITEMS : "An item appears in multiple orders"

(3) Classification of SQL Languages

SQL (Structured Query Language) is the standard language for working with relational databases.

5. The History of MySQL

(1) Version History

timeline
    title MySQL Version History
    1995 : MySQL 1.0 Published
         : From Sweden MySQL AB Company Development
    2000 : MySQL 3.23
         : Supports transaction processing
    2003 : MySQL 4.0
         : Support UNION Search
    2005 : MySQL 5.0
         : Supports stored procedures、View、Trigger
    2008 : Sun Acquisition MySQL AB
    2010 : Oracle Acquisition Sun
         : Obtain MySQL Ownership
    2013 : MySQL 5.6
         : InnoDB Full-Text Search
    2016 : MySQL 5.7
         : JSON Support、Performance Improvements
    2018 : MySQL 8.0
         : Window Functions、CTE、Characters
    2024 : MySQL 8.4 LTS
         : Long-Term Support Version
    2024 : MySQL 9.0 Innovation
         : Latest Innovative Version

timeline
    title MySQL Version History
    1995 : MySQL 1.0 Published
         : From Sweden MySQL AB Company Development
    2000 : MySQL 3.23
         : Supports transaction processing
    2003 : MySQL 4.0
         : Support UNION Search
    2005 : MySQL 5.0
         : Supports stored procedures、View、Trigger
    2008 : Sun Acquisition MySQL AB
    2010 : Oracle Acquisition Sun
         : Obtain MySQL Ownership
    2013 : MySQL 5.6
         : InnoDB Full-Text Search
    2016 : MySQL 5.7
         : JSON Support、Performance Improvements
    2018 : MySQL 8.0
         : Window Functions、CTE、Characters
    2024 : MySQL 8.4 LTS
         : Long-Term Support Version
    2024 : MySQL 9.0 Innovation
         : Latest Innovative Version

(2) MySQL's Design Philosophy

MySQL's Core Design Principles:

Simplicity First

• Simple installation and configuration, with a gentle learning curve
• The syntax is intuitive and closely resembles natural language

Performance First

• Extremely fast read speeds, making it ideal for scenarios with heavy read and light write workloads
• The InnoDB storage engine provides high-performance transaction processing

Open Ecosystem

• Open source and free (GPL license)
• A wide variety of third-party tools and drivers

6. Key Advantages of MySQL

(1) Comparison of Advantages

(2) Typical Use Cases

mindmap
  root((MySQL Applications))
    Web Development
      Blog System
      E-commerce Platform
      Social Networks
      Content Management
    Enterprise Applications
      ERP System
      CRM System
      Inventory Management
      Order Processing
    Data Analysis
      Log Storage
      User Behavior
      Report Statistics
    Mobile Apps
      App Backend
      Game Data
      Push Notification System

mindmap
  root((MySQL Applications))
    Web Development
      Blog System
      E-commerce Platform
      Social Networks
      Content Management
    Enterprise Applications
      ERP System
      CRM System
      Inventory Management
      Order Processing
    Data Analysis
      Log Storage
      User Behavior
      Report Statistics
    Mobile Apps
      App Backend
      Game Data
      Push Notification System

Who Uses MySQL?

7. A Quick Introduction to MySQL

(1) Connecting to MySQL in Three Steps

📖 The next lesson will cover in detail: cross-platform installation (Windows/Mac/Linux/Docker), connecting to MySQL Workbench, and security configuration. This section provides only a quick overview.

▶ Example: Starting Docker with a single command

# Pull and Start MySQL 8.0 (see Lesson 2 for details)
docker run -d --name mysql-tutorial \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=MyPass123! \
  mysql:8.0

# Enter MySQL Command Line
docker exec -it mysql-tutorial mysql -u root -pMyPass123!

Output:

+---------+
| VERSION |
+---------+
| 8.0.35  |
+---------+
1 row in set

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| testdb             |
+--------------------+

▶ Example: Quick Verification After Connection

-- View MySQL Version
SELECT VERSION();

-- View All Databases
SHOW DATABASES;

Output:

+-----------+
| VERSION() |
+-----------+
| 8.0.36    |
+-----------+

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

▶ Example: Creating Your First Database and Table

Output:

Query OK, 1 row affected

Database changed

Query OK, 0 rows affected

Query OK, 1 row affected

Query OK, 1 row affected

---+---------+------------------
id | name    | email            
---+---------+------------------
1  | Alice   | alice@email.com  
2  | Bob     | bob@email.com    
3  | Charlie | charlie@email.com
---+---------+------------------
3 rows in set

-- 1. Create a Database
CREATE DATABASE my_first_db;

-- 2. Using the Database
USE my_first_db;

-- 3. Create a user table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 4. Insert Data
INSERT INTO users (username, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (username, email) VALUES ('Bob', 'bob@example.com');

-- 5. Query Data
SELECT * FROM users;

Output:

Output displayed

Output:

+----+----------+-----------------+---------------------+
| id | username | email           | created_at          |
+----+----------+-----------------+---------------------+
|  1 | Alice    | alice@example.com | 2026-07-03 10:30:00 |
|  2 | Bob      | bob@example.com   | 2026-07-03 10:30:01 |
+----+----------+-----------------+---------------------+

▶ Example: CRUD Operations on Data

-- Conditional Query
SELECT username, email FROM users WHERE username = 'Alice';

-- Update Data
UPDATE users SET email = 'alice_new@example.com' WHERE id = 1;

-- Delete Data
DELETE FROM users WHERE id = 2;

-- Count the number of users
SELECT COUNT(*) AS total_users FROM users;

Output:

Output displayed

❓ FAQ

📖 Summary

• A database is a repository for organizing, storing, and managing data according to a data structure.
• Relational databases store data in the form of tables (rows and columns) and support relationships between tables.
• SQL is the standard language for manipulating relational databases and is divided into five categories: DDL, DML, DQL, DCL, and TCL.
• MySQL is the world's most popular open-source relational database—it's simple, fast, and free.
• MySQL 8.0+ is the current mainstream version, supporting new features such as window functions, JSON, and roles.
• Docker is the easiest way to install MySQL

📝 Exercises

1. Basic Exercise (Difficulty: ⭐): Install MySQL 8.0 using Docker. Once connected, run SELECT VERSION(); to check the version number.

2. Advanced Problem (Difficulty ⭐⭐): Create a database named school that contains a table named students (id, name, age, grade), insert 5 records, and query all records.

3. Challenge Question (Difficulty: ⭐⭐⭐): Create the courses table and the student_courses associated table (many-to-many relationship), design appropriate fields, and use a JOIN to query all courses taken by a specific student.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us