MySQL

MySQL 是全球最流行的开源关系型数据库管理系统（RDBMS），由瑞典 MySQL AB 公司开发，后被 Oracle 收购。它使用标准 SQL 查询语言，以高性能、高可靠性和易用性著称，是 LAMP/LEMP 技术栈的核心组件，广泛用于 Web 应用、电商平台、CMS 系统等。MySQL 支持事务 ACID、存储过程、触发器、视图、分区表等企业级特性，拥有庞大的社区和丰富的第三方工具生态。

1. 环境准备

• 操作系统：Linux (Ubuntu 20.04+/CentOS 7+)、macOS 12+、Windows 10+
• 硬件要求：最低 512MB RAM（推荐 2GB+），1GB 磁盘空间
• 端口：默认 3306，确保防火墙开放
• 依赖：libaio（Linux）、Visual C++ Redistributable（Windows）

2. 安装命令

Ubuntu/Debian

sudo apt update
sudo apt install mysql-server -y
sudo systemctl start mysql
sudo systemctl enable mysql
sudo mysql_secure_installation  # 设置 root 密码、移除匿名用户等

macOS (Homebrew)

brew install mysql
brew services start mysql
mysql_secure_installation

Windows

# 使用 Choco
choco install mysql

# 或使用 MSI 安装包（官方下载）
# https://dev.mysql.com/downloads/installer/

Docker（推荐开发环境）

docker run --name mysql-dev -e MYSQL_ROOT_PASSWORD=root123 \
  -p 3306:3306 -d mysql:8.0

验证安装

mysql -u root -p
# 输入密码后看到 mysql> 提示符即成功

3. 常见安装问题

Q: Ubuntu 安装后无法使用 root 登录

sudo mysql  # 用系统 root 进入
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
FLUSH PRIVILEGES;

Q: 端口 3306 被占用

sudo lsof -i :3306  # 查看占用进程
# 或修改配置文件 /etc/mysql/my.cnf 中的 port

Q: Docker 容器数据持久化

docker run --name mysql-dev \
  -v ~/mysql-data:/var/lib/mysql \
  -e MYSQL_ROOT_PASSWORD=root123 -p 3306:3306 -d mysql:8.0

目标

通过 MySQL 命令行和 Python 驱动，完成数据库创建、表创建、数据增删改查（CRUD）的完整流程。

环境准备

确保 MySQL 已安装并运行：

mysql -u root -p

第一步：命令行方式

-- 创建数据库
CREATE DATABASE IF NOT EXISTS hello_world CHARACTER SET utf8mb4;

-- 切换到该数据库
USE hello_world;

-- 创建学生表
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT CHECK(age > 0 AND age < 150),
    grade DECIMAL(3,1),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入数据
INSERT INTO students (name, age, grade) VALUES
    ('张三', 20, 88.5),
    ('李四', 21, 92.0),
    ('王五', 19, 76.5);

-- 查询
SELECT * FROM students;
SELECT name, grade FROM students WHERE grade >= 80 ORDER BY grade DESC;

-- 更新
UPDATE students SET grade = 90.0 WHERE name = '张三';

-- 删除
DELETE FROM students WHERE name = '王五';

-- 最终结果
SELECT * FROM students;

第二步：Python 驱动方式

import mysql.connector

# 连接数据库
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root123",
    database="hello_world"
)
cursor = conn.cursor()

# 创建表
cursor.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50) NOT NULL,
        age INT,
        grade DECIMAL(3,1)
    )
""")

# 批量插入
students = [("赵六", 22, 85.0), ("钱七", 20, 91.5)]
cursor.executemany(
    "INSERT INTO students (name, age, grade) VALUES (%s, %s, %s)",
    students
)
conn.commit()

# 查询
cursor.execute("SELECT * FROM students WHERE grade >= 85")
for row in cursor.fetchall():
    print(f"ID={row[0]}, Name={row[1]}, Grade={row[3]}")

# 清理
cursor.close()
conn.close()

预期输出

+----+------+------+-------+
| id | name | age | grade |
+----+------+------+-------+
|  1 | 张三 |  20 |  90.0 |
|  2 | 李四 |  21 |  92.0 |
+----+------+------+-------+

ID=1, Name=张三, Grade=90.0
ID=2, Name=李四, Grade=92.0

1. 什么是 MySQL？

MySQL 是客户端/服务器架构的关系型数据库管理系统。一台 MySQL 服务器可以管理多个数据库，每个数据库包含多张表。它使用 SQL（Structured Query Language）与客户端通信。

核心概念

• 数据库（Database）：类似一个文件夹，存放一组相关的表
• 表（Table）：类似一张 Excel 工作表，包含行和列
• 行（Row/Record）：一条完整数据记录
• 列（Column/Field）：一种数据字段类型
• 主键（Primary Key）：唯一标识每一行的字段
• 外键（Foreign Key）：关联另一张表主键的字段

2. 常用数据类型

类型	用途	示例
INT	整数	age INT
VARCHAR(N)	可变长字符串	name VARCHAR(100)
TEXT	长文本	content TEXT
DECIMAL(M,D)	精确小数	price DECIMAL(10,2)
DATE / DATETIME	日期时间	created_at DATETIME
BOOLEAN (TINYINT)	布尔值	is_active BOOLEAN
ENUM	枚举	gender ENUM('M','F')

3. 创建电商数据库实战

场景分析：一个简单电商需要三张表

• 用户表 users
• 商品表 products
• 订单表 orders（关联用户）

CREATE DATABASE shop CHARACTER SET utf8mb4;
USE shop;

-- 用户表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    balance DECIMAL(10,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 商品表
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0,
    description TEXT
);

-- 订单表
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    total_amount DECIMAL(10,2),
    status ENUM('pending','paid','shipped','done') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

4. CRUD 操作大全

-- === INSERT ===
INSERT INTO users (username, email, password_hash) VALUES
    ('alice', 'alice@example.com', 'hashed_pwd1'),
    ('bob', 'bob@example.com', 'hashed_pwd2');

INSERT INTO products (name, price, stock, description) VALUES
    ('机械键盘', 299.00, 50, 'Cherry MX 青轴'),
    ('无线鼠标', 89.00, 200, '蓝牙 5.0 静音'),
    ('27寸显示器', 1999.00, 10, '4K IPS HDR');

-- === SELECT ===
-- 基本查询
SELECT * FROM products WHERE price < 500;

-- 聚合函数
SELECT COUNT(*) AS total_users FROM users;
SELECT AVG(price) AS avg_price FROM products;

-- 排序 + 限制
SELECT name, price FROM products ORDER BY price DESC LIMIT 5;

-- === UPDATE ===
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock > 0;

-- === DELETE ===
DELETE FROM products WHERE id = 999;  -- 删除无效商品

5. 多表连接

-- 创建一条订单
INSERT INTO orders (user_id, product_id, quantity, total_amount)
VALUES (1, 1, 2, 598.00);

-- INNER JOIN：查看订单详情
SELECT
    orders.id AS order_id,
    users.username,
    products.name AS product_name,
    orders.quantity,
    orders.total_amount,
    orders.status
FROM orders
INNER JOIN users ON orders.user_id = users.id
INNER JOIN products ON orders.product_id = products.id;

6. 索引优化

-- 为经常查询的列创建索引
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

-- 查看查询是否使用索引
EXPLAIN SELECT * FROM orders WHERE user_id = 1;

7. 实战：查询"哪些商品卖得最好"

SELECT
    p.name,
    SUM(o.quantity) AS total_sold,
    SUM(o.total_amount) AS revenue
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.status IN ('paid', 'shipped', 'done')
GROUP BY p.id
ORDER BY total_sold DESC;

思考题

1. 为什么 password_hash 用 VARCHAR(255) 而非直接在数据库存明文密码？
2. 订单中为什么要冗余 total_amount 而不每次都从 product.price × quantity 计算？
3. INDEX 能加速查询，为什么不能给所有列都加索引？