#relational-database and MySQL overview

#Introduction

Whether it is e-commerce orders, personal blog content or enterprise core business data, relational-database has always been the cornerstone of persistent storage for modern applications. Among many relational-databases, MySQL occupies an absolutely dominant position in web development and Python projects by virtue of its advantages of open source, free, high performance, and mature ecology. This article will start from scratch and use an easy-to-understand method to guide you to master the core concepts of relational-database and the basic operations of MySQL. It will also demonstrate how to use Python to connect to the database through actual code, laying a solid foundation for the subsequent database development journey.

#1. relational-database core foundation

#1.1 What is relational-database?

relational-database is built on the relational model. The core idea is to organize data into two-dimensional tables (tables), and tables are connected through "relationships". You can think of it like a structured Excel workbook: each worksheet is a table, each row represents a complete record, and each column represents a field.

More specifically, it has the following key characteristics:

• Table storage data: Each table corresponds to a type of object in the real world, such as "employee table" and "department table".
• Clear rows and columns: Each row is a complete record (such as "Zhang San, the employee"), and each column is an attribute of the object (such as "Zhang San's mailbox").
• Unique identification: Each table has a primary key to ensure that records are not duplicated and can be accurately positioned.
• Inter-table association: Concatenate data from different tables through foreign keys to reduce redundant storage.
• Data Constraints: Ensure the accuracy and completeness of data through rules (such as "Email cannot be empty" "Salary must be greater than zero").

#1.2 Reliable guarantee: ACID characteristics

relational-database is capable of handling scenarios such as bank transfers and order payments that require extremely high data consistency. The core reason is that it strictly follows the four major characteristics of ACID:

#1.3 When to choose relational style? When to choose non-relational?

To help you make a choice based on actual needs, here is a simple comparison:

#2. Popular choice: MySQL Quick Start

#2.1 Why choose MySQL?

• Open source and free: The Community version is fully sufficient for individuals or small teams and complies with the GPL agreement.
• Cross-platform: Windows, Linux, macOS can be easily deployed.
• Rich Ecology: Python, Java, Go and other mainstream languages ​​​​have mature drivers, complete documentation and community support.
• Excellent performance: The default InnoDB engine supports transactions, row-level locks and high concurrency, with excellent read and write performance.

#2.2 Common storage engines

MySQL supports multiple storage engines. Newbies only need to remember: InnoDB is preferred in most scenarios.

-- 查看所有支持的存储引擎
SHOW ENGINES;

-- 查看当前默认引擎
SELECT @@default_storage_engine;

The characteristics of different engines are compared as follows:

#3. Local environment-setup

The following only introduces the quick installation method of Community Edition. For production environments, it is recommended to further refer to official documents to strengthen security configuration.

#3.1 Windows installation (MySQL Installer recommended)

1. Visit MySQL官网下载页面
2. Downloadmysql-installer-community(No need to register an Oracle account, just click "No thanks, just start my download." below)
3. Run the installer and select Server only (or manually select components through Custom)
4. Follow the prompts to set the root password and port (default 3306)
5. After the installation is complete, open the command line and entermysql -u root -p, enter the password to verify whether it is successful

#3.2 Ubuntu/Debian installation (start with one line of commands)

# 更新包列表
sudo apt update

# 安装MySQL Server
sudo apt install mysql-server -y

# 安全配置（必做！）
sudo mysql_secure_installation

# 验证安装
sudo mysql -u root -p

#3.3 Basic connection commands

# 本地连接（root用户）
mysql -u root -p

# 连接到指定数据库
mysql -u root -p -D company_db

# 连接远程服务器（替换hostname和username）
mysql -h 192.168.1.100 -u app_user -p

#4. Basic operations of database and tables

Next, we create a company management system database to practice, and gradually master the database creation, table creation and table structure modification.

#4.1 Database operations

-- 创建数据库（指定字符集，支持中文和emoji）
CREATE DATABASE IF NOT EXISTS company_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- 使用指定数据库
USE company_db;

-- 查看所有数据库
SHOW DATABASES;

-- 删除数据库（谨慎操作！）
DROP DATABASE IF EXISTS old_test_db;

#4.2 Table operations (create department tables and employee tables)

First create the related department table, then create the employee table and establish a foreign key association:

-- 先创建被关联的部门表
CREATE TABLE departments (
    dept_id INT AUTO_INCREMENT PRIMARY KEY, -- 主键，自增
    dept_name VARCHAR(100) UNIQUE NOT NULL, -- 部门名唯一且不能为空
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 自动记录创建时间
);

-- 再创建员工表（关联部门表）
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    salary DECIMAL(10, 2) NOT NULL CHECK (salary > 0), -- 薪资必须大于0
    hire_date DATE NOT NULL,
    dept_id INT, -- 外键，关联部门表的dept_id
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 自动更新修改时间
    
    -- 建立索引，加速查询
    INDEX idx_emp_email (email),
    INDEX idx_emp_dept (dept_id),
    
    -- 建立外键约束
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
        ON DELETE SET NULL -- 部门删除时，员工的dept_id设为NULL
        ON UPDATE CASCADE -- 部门dept_id修改时，同步更新员工dept_id
);

-- 查看表结构
DESCRIBE employees;

-- 修改表结构（例如增加手机号列）
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);

#5. Basic SQL: data addition, deletion, modification and query

#5.1 Insert data (INSERT)

-- 插入单个部门
INSERT INTO departments (dept_name) 
VALUES ('技术部');

-- 批量插入部门
INSERT INTO departments (dept_name)
VALUES ('产品部'), ('财务部'), ('运营部');

-- 插入单个员工
INSERT INTO employees (first_name, last_name, email, salary, hire_date, dept_id)
VALUES ('张', '三', 'zhangsan@tech.com', 15000.00, '2023-01-01', 1);

-- 批量插入员工
INSERT INTO employees (first_name, last_name, email, salary, hire_date, dept_id)
VALUES 
    ('李', '四', 'lisi@tech.com', 18000.00, '2023-03-15', 1),
    ('王', '五', 'wangwu@product.com', 12000.00, '2023-05-20', 2);

#5.2 Query data (SELECT)

-- 基本查询：查询技术部所有员工的姓名、邮箱、薪资，并按薪资降序排列
SELECT first_name, last_name, email, salary
FROM employees
WHERE dept_id = 1
ORDER BY salary DESC;

-- 关联查询：查询每个员工及其所属部门名称
SELECT 
    e.first_name, 
    e.last_name, 
    d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

-- 聚合查询：查询每个部门的平均薪资和人数，只显示人数大于1的部门
SELECT 
    d.dept_name,
    COUNT(e.emp_id) AS emp_count,
    AVG(e.salary) AS avg_salary
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id
HAVING COUNT(e.emp_id) > 1;

#6. Python quickly connects to MySQL

There are two mainstream ways for Python to operate MySQL: Native PyMySQL (lightweight and intuitive, suitable for quick docking) and SQLAlchemy ORM (object-oriented, suitable for complex projects). Here we first introduce the most basic usage of PyMySQL.

#6.1 Native PyMySQL (first choice for getting started)

Install dependencies first:

pip install pymysql

Sample code (contains secure context manager and parameterized queries):

import pymysql
from contextlib import contextmanager

# 数据库连接配置（请替换为你的实际密码）
DB_CONFIG = {
    "host": "localhost",
    "user": "root",
    "password": "your_password",
    "database": "company_db",
    "charset": "utf8mb4"
}

@contextmanager
def get_db_conn():
    """上下文管理器：自动处理提交、回滚和关闭连接"""
    conn = None
    try:
        conn = pymysql.connect(**DB_CONFIG)
        yield conn
        conn.commit()
    except Exception as e:
        if conn:
            conn.rollback()
        raise e
    finally:
        if conn:
            conn.close()

# 示例：查询技术部的所有员工
def get_tech_employees():
    with get_db_conn() as conn:
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            sql = """
                SELECT first_name, last_name, salary 
                FROM employees 
                WHERE dept_id = %s
            """
            cursor.execute(sql, (1,))  # 参数化查询，防止SQL注入
            return cursor.fetchall()

# 运行测试
print(get_tech_employees())

#Related tutorials

• SQL详解之DDL
• SQL详解之DML
• Python接入MySQL数据库

#Summarize

This article starts from the core concept of relational-database, introduces the characteristics, installation and basic operations of MySQL, and finally demonstrates how to interact with the database through Python code. After mastering these contents, you already have the basics of Python database development.

If you need in-depth study in the future, you can continue to pay attention to these directions:

• Complex SQL queries and subqueries
• Advanced usage of SQLAlchemy ORM
• Database performance optimization and index design
• Database security, backup and recovery