Databases: I'm Not Just Your Excel‑Savvy Cousin

Databases: I'm Not Just Your Excel‑Savvy Cousin

"Database? Isn't it just a fancy Excel?" "Isn't it just a table that stores data?" If you think that, the database will be crying in the restroom.

Today we discuss the "data‑management marvel that is tens of thousands of times more complex than Excel".

1. What exactly is a database?

First, a real‑life analogy:

Excel

: your shoe cabinet

Storing dozens of shoes is fine

But what if you have 100,000 shoes?

How do you find "the blue sneakers you wore last summer"?

How does the whole family look for shoes at the same time?

Database

: a large warehouse‑logistics centre

Manage millions of items effortlessly

Millisecond‑level queries

Thousands of concurrent users

Automatic backup, reliable security

Database = "warehouse" + "system" + "engine"

2. Types of databases

1. Relational databases (RDBMS)

SQL databases store data in tables; tables can relate to each other.

Common players:
- MySQL: the heavyweight of open source
- PostgreSQL: the most feature‑complete open‑source DB
- Oracle: enterprise giant, wallet‑killer
- SQL Server: Microsoft’s flagship

2. NoSQL databases

Non‑relational databases do not use SQL and are not limited to tabular structures.

Document DB: MongoDB (stores JSON documents)
Key‑value DB: Redis (caching, leaderboards)
Column‑family DB: Cassandra (big data)
Graph DB: Neo4j (social graphs, knowledge graphs)

3. Core concepts of relational databases

Table — the Excel sheet

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    class_id INT
);

students table:
┌────┬───────┬─────┬─────────┐
│ id │ name  │ age │ class_id │
├────┼───────┼─────┼─────────┤
│ 1  │ 张三  │ 18  │ 1       │
│ 2  │ 李四  │ 17  │ 1       │
│ 3  │ 王五  │ 19  │ 2       │
└────┴───────┴─────┴─────────┘

Column — a field

# Field definition includes:
# name + data type + constraints
name VARCHAR(50)   -- variable string, up to 50 chars
age  INT           -- integer

Row/Record — a record

One row = a complete student record
id=1, name=张三, age=18, class_id=1

Primary Key — like an ID card

# Each student has a unique ID, like an ID card
id INT PRIMARY KEY   -- cannot duplicate, cannot be null

Foreign Key — linking tables

# class_id references classes table's id
FOREIGN KEY (class_id) REFERENCES classes(id)

4. SQL: the "Mandarin" of databases

SQL (Structured Query Language) is the standard language for operating databases.

CRUD (Create, Read, Update, Delete)

-- 1. Insert (Create)
INSERT INTO students (name, age, class_id) VALUES ('赵六', 18, 2);

-- 2. Query (Read)
SELECT * FROM students WHERE age >= 18;

-- 3. Update
UPDATE students SET age = 19 WHERE id = 1;

-- 4. Delete
DELETE FROM students WHERE id = 3;

Advanced queries

-- Multi‑table join: find students and their classes
SELECT students.name, classes.name
FROM students
JOIN classes ON students.class_id = classes.id;

-- Aggregation: count students per class
SELECT class_id, COUNT(*) as count
FROM students
GROUP BY class_id;

-- Subquery: class with most students
SELECT *
FROM classes
WHERE id = (
    SELECT class_id
    FROM students
    GROUP BY class_id
    ORDER BY COUNT(*) DESC
    LIMIT 1
);

5. Table relationships: the soul of a database

One‑to‑one (1:1)

Person ↔ ID card
One person has one ID card; one ID card belongs to one person

One‑to‑many (1:N)

Class ↔ Students
One class has many students; a student belongs to one class

Many‑to‑many (M:N)

Student ↔ Course
A student can take many courses; a course can have many students
-- Junction table needed
CREATE TABLE student_course (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

6. Why use a database? Excel isn’t enough

Data volume: Excel – millions; Database – billions+

Concurrent access: Excel – 1 person; Database – thousands+

Query speed: Excel – slow; Database – millisecond‑level

Data consistency: Excel – manual; Database – automatic

Permission control: Excel – weak; Database – fine‑grained

Backup/restore: Excel – manual; Database – automatic

Data integrity: Excel – none; Database – constraint‑enforced

Example

Excel: Find "all women over 18, sorted by name"
→ manual filter and sort

Database:
SELECT * FROM students
WHERE age >= 18 AND gender = '女'
ORDER BY name;
→ one SQL statement, instant result

7. Database "protective mechanisms"

Transaction

Transfer scenario:
A transfers 100 to B

Correct flow:
1. A -100
2. B +100
3. COMMIT

If an error occurs:
→ automatic ROLLBACK
→ both accounts unchanged

ACID properties

A - Atomicity: all succeed or all fail
C - Consistency: total amount unchanged after transfer
I - Isolation: concurrent transfers do not interfere
D - Durability: committed data is not lost

8. Hands‑on: Quick MySQL start

-- 1. Create database
CREATE DATABASE school_db;

-- 2. Use database
USE school_db;

-- 3. Create table
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    gender ENUM('男','女'),
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 4. Insert data
INSERT INTO students (name, gender, age) VALUES
('张三','男',18),
('李四','女',17),
('王五','男',19);

-- 5. Query
SELECT name, age FROM students
WHERE gender = '男'
ORDER BY age;

9. When should you use a database?

Use Excel when:
✅ Data < 100k rows
✅ Single user
✅ Simple tables, no complex queries
✅ No multi‑person collaboration

Use a database when:
✅ Data > 100k rows
✅ Complex queries needed
✅ Multiple concurrent users
✅ High data‑security requirements
✅ Need backup and recovery

Conclusion: Database is not Excel Plus

Excel = a family car
  Simple, easy, for everyday use

Database = a container ship
  Complex, powerful, for massive data

Choice principle:
- Small data, personal use → Excel
- Large data, team use → Database

Remember: a database is the weapon for handling "massive data", not just an "Excel that knows SQL".

Key takeaways :

Databases are professional data‑management systems far stronger than Excel.

Relational databases store data in tables that can be related.

SQL is the standard language for operating databases.

Databases provide transactions, concurrency, backup and other advanced features.