MySQL数据库基础教程：从入门到优化实战

MySQL是世界上最流行的开源关系型数据库，几乎所有中小型网站都在使用。无论是做Web开发、数据分析还是后端架构，掌握MySQL都是一项必备技能。本教程从零基础讲起，带你掌握MySQL的核心用法。

一、数据库基础概念

数据库（Database）：存储数据的仓库，类似一个Excel文件
数据表（Table）：数据库中的表，类似Excel中的工作表
行（Row）：表中的一条记录
列（Column）：表中的字段，定义数据类型
主键（Primary Key）：唯一标识每条记录的字段

二、数据库操作

-- 创建数据库
CREATE DATABASE IF NOT EXISTS myapp 
DEFAULT CHARACTER SET utf8mb4 
DEFAULT COLLATE utf8mb4_unicode_ci;

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

-- 使用数据库
USE myapp;

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

三、数据表设计

-- 创建用户表
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    age TINYINT UNSIGNED DEFAULT 0,
    status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
    avatar VARCHAR(255) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建文章表
CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    view_count INT UNSIGNED DEFAULT 0,
    published_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_published (published_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

四、CRUD操作

插入数据（Create）

-- 单条插入
INSERT INTO users (username, email, password_hash, age) 
VALUES ('zhangsan', 'zhangsan@example.com', 'hash_value', 25);

-- 批量插入
INSERT INTO users (username, email, password_hash, age) VALUES
('lisi', 'lisi@example.com', 'hash2', 28),
('wangwu', 'wangwu@example.com', 'hash3', 22),
('zhaoliu', 'zhaoliu@example.com', 'hash4', 30);

查询数据（Read）

-- 基础查询
SELECT * FROM users WHERE status = 'active' LIMIT 10;

-- 条件查询
SELECT username, email, age 
FROM users 
WHERE age BETWEEN 20 AND 30 
ORDER BY age DESC;

-- 模糊查询
SELECT * FROM users WHERE username LIKE '%张%';

-- 聚合查询
SELECT 
    status,
    COUNT(*) as count,
    AVG(age) as avg_age,
    MAX(age) as max_age
FROM users 
GROUP BY status;

-- 连表查询（JOIN）
SELECT u.username, a.title, a.view_count
FROM users u
INNER JOIN articles a ON u.id = a.user_id
WHERE a.published_at IS NOT NULL
ORDER BY a.view_count DESC
LIMIT 20;

-- 子查询
SELECT * FROM users 
WHERE id IN (
    SELECT user_id FROM articles 
    WHERE view_count > 1000
);

更新数据（Update）

-- 更新单条记录
UPDATE users SET age = 26 WHERE id = 1;

-- 批量更新
UPDATE users SET status = 'inactive' 
WHERE created_at < '2025-01-01' AND status = 'active';

-- 更新时关联其他表
UPDATE articles a 
INNER JOIN users u ON a.user_id = u.id
SET a.view_count = a.view_count + 1
WHERE u.username = 'zhangsan';

删除数据（Delete）

-- 删除单条
DELETE FROM users WHERE id = 1;

-- 按条件删除
DELETE FROM users WHERE status = 'banned' AND created_at < '2024-01-01';

-- 清空表（保留表结构）
TRUNCATE TABLE articles;

五、索引优化

索引是提升查询性能的关键手段，合理使用索引可以让查询速度提升数十倍甚至更多。

-- 创建普通索引
CREATE INDEX idx_email ON users(email);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_username ON users(username);

-- 创建复合索引（最左匹配原则）
CREATE INDEX idx_status_age ON users(status, age);

-- 查看索引使用情况
SHOW INDEX FROM users;

-- 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

索引使用原则：频繁查询的字段加索引，区分度高的字段加索引，WHERE/JOIN/ORDER BY中的字段考虑加索引。但不要过度使用索引，每个索引都会增加写入开销和存储空间。

六、事务处理

-- 开启事务
START TRANSACTION;

-- 执行操作（转账示例）
UPDATE users SET balance = balance - 100 WHERE id = 1;
UPDATE users SET balance = balance + 100 WHERE id = 2;

-- 提交事务
COMMIT;

-- 如果出错，回滚
-- ROLLBACK;

七、性能优化建议

避免 SELECT *：只查询需要的字段，减少数据传输量
合理使用索引：WHERE、ORDER BY、GROUP BY 的字段适当加索引
使用分页查询：大表查询使用 LIMIT + OFFSET 或游标分页
避免在WHERE中使用函数：如 WHERE YEAR(created_at) = 2026 会索引失效
适当使用缓存：高频读取的数据可以用Redis缓存
定期优化表：使用 OPTIMIZE TABLE 命令整理碎片

八、表设计实战：博客系统

-- 用户表
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    avatar VARCHAR(255) DEFAULT '',
    status TINYINT DEFAULT 1 COMMENT '1正常 0禁用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 文章表
CREATE TABLE articles (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    category VARCHAR(50) DEFAULT '未分类',
    status TINYINT DEFAULT 1 COMMENT '1已发布 0草稿',
    view_count INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_category (category),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 查询某个用户的所有已发布文章，按时间倒序
SELECT a.id, a.title, a.category, a.view_count, a.created_at
FROM articles a
WHERE a.user_id = 1 AND a.status = 1
ORDER BY a.created_at DESC
LIMIT 20;

九、常见SQL面试题

-- 1. 查询每个分类的文章数量
SELECT category, COUNT(*) as total
FROM articles WHERE status = 1
GROUP BY category
ORDER BY total DESC;

-- 2. 查询文章数最多的用户
SELECT u.username, COUNT(a.id) as article_count
FROM users u
LEFT JOIN articles a ON u.id = a.user_id AND a.status = 1
GROUP BY u.id
ORDER BY article_count DESC
LIMIT 1;

-- 3. 查询最近7天每天都有发文章的用户
SELECT user_id, COUNT(DISTINCT DATE(created_at)) as days
FROM articles
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY user_id
HAVING days = 7;

总结：MySQL是后端开发的基石。新手先掌握增删改查和索引，再逐步学习事务、锁、分库分表。建议用 EXPLAIN 分析慢查询，是优化的第一步。