第一阶段：MySQL 基础入门

1.1 MySQL 核心概念

1.1.1 什么是关系型数据库？

关系型数据库（RDBMS，Relational Database Management System）是基于关系模型组织数据的数据库系统。数据以二维表（行和列）的形式存储，表与表之间通过外键建立关联。

核心思想：用数学中的"关系"（集合论）来描述数据之间的联系。

关系型数据库的核心要素：

┌──────────────────────────────────────────────────────┐
│  Table: users（表）                                    │
│  ┌──────┬──────────┬────────────────┬──────────────┐  │
│  │  id  │  name    │  email         │  created_at  │  │  ← 列（Column/Field）
│  ├──────┼──────────┼────────────────┼──────────────┤  │
│  │  1   │  Alice   │  alice@ex.com  │  2026-01-01  │  │  ← 行（Row/Record）
│  │  2   │  Bob     │  bob@ex.com    │  2026-01-02  │  │
│  │  3   │  Charlie │  charlie@ex.com│  2026-01-03  │  │
│  └──────┴──────────┴────────────────┴──────────────┘  │
│      ↑                                                │
│   主键（Primary Key）                                   │
└──────────────────────────────────────────────────────┘

1.1.2 MySQL 的定位与特点

MySQL 是全球最流行的开源关系型数据库之一，由 Oracle 公司维护。

特点	说明
开源免费	社区版（Community Edition）完全免费
高性能	适合 OLTP（在线事务处理）场景，支持高并发读写
跨平台	支持 Linux、Windows、macOS 等
丰富生态	大量工具、驱动、ORM 框架支持
可靠稳定	被 Facebook、Twitter、GitHub、淘宝等大规模使用
插件式引擎	支持多种存储引擎（InnoDB、MyISAM 等）

MySQL vs 其他数据库：

对比	MySQL	PostgreSQL	SQLite	MongoDB
类型	关系型	关系型	嵌入式关系型	文档型（NoSQL）
适用场景	Web 应用、OLTP	复杂查询、GIS、OLAP	移动端、小型应用	灵活 Schema、大数据
事务支持	✅ InnoDB 支持	✅ 完整支持	✅ 基本支持	✅ 4.0+ 支持
性能特点	读性能优秀	写性能和复杂查询强	轻量极快	水平扩展能力强
学习曲线	低	中	极低	低

1.1.3 MySQL 架构概览

MySQL 采用分层架构，自上而下分为四层：

┌─────────────────────────────────────────────────────────────────┐
│                        客户端层                                  │
│   ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐          │
│   │ mysql CLI│ │Workbench │ │ DBeaver  │ │ App(JDBC)│          │
│   └────┬─────┘ └────┬─────┘ └────┬─────┘ └────┬─────┘          │
│        └─────────────┴─────────────┴─────────────┘              │
│                         TCP/IP / Socket                          │
├─────────────────────────────────────────────────────────────────┤
│                       Server 层（MySQL 服务）                     │
│                                                                  │
│   ┌──────────────┐                                              │
│   │  连接器       │  ← 认证、权限、连接管理                       │
│   │ (Connector)  │                                              │
│   └──────┬───────┘                                              │
│          ▼                                                      │
│   ┌──────────────┐                                              │
│   │  解析器       │  ← 词法分析、语法分析，生成语法树（AST）        │
│   │ (Parser)     │                                              │
│   └──────┬───────┘                                              │
│          ▼                                                      │
│   ┌──────────────┐                                              │
│   │  优化器       │  ← 选择索引、确定 JOIN 顺序、生成执行计划       │
│   │ (Optimizer)  │                                              │
│   └──────┬───────┘                                              │
│          ▼                                                      │
│   ┌──────────────┐                                              │
│   │  执行器       │  ← 调用存储引擎接口，执行查询                   │
│   │ (Executor)   │                                              │
│   └──────┬───────┘                                              │
├──────────┼──────────────────────────────────────────────────────┤
│          ▼            存储引擎层（可插拔）                         │
│   ┌──────────────┐ ┌──────────────┐ ┌──────────────┐            │
│   │   InnoDB     │ │   MyISAM     │ │   Memory     │            │
│   │  (默认引擎)   │ │  (旧版默认)   │ │  (内存引擎)   │            │
│   └──────┬───────┘ └──────────────┘ └──────────────┘            │
├──────────┼──────────────────────────────────────────────────────┤
│          ▼            文件系统层                                  │
│   ┌─────────────────────────────────────────┐                    │
│   │  数据文件 (.ibd)  日志文件 (redo/undo)    │                    │
│   │  Binlog          配置文件 (my.cnf)       │                    │
│   └─────────────────────────────────────────┘                    │
└─────────────────────────────────────────────────────────────────┘

SQL 执行流程：

┌──────┐    ┌──────┐    ┌──────┐    ┌──────┐    ┌───────────┐
│客户端│───▶│连接器│───▶│解析器│───▶│优化器│───▶│ 执行器    │
│      │    │      │    │      │    │      │    │ ↕ 存储引擎│
└──────┘    └──────┘    └──────┘    └──────┘    └───────────┘

1. 连接器：验证用户名密码，获取权限
2. 解析器：词法分析 + 语法分析，检查 SQL 语法
3. 优化器：选择最优执行方案（用哪个索引、JOIN 顺序等）
4. 执行器：调用存储引擎接口，逐行或批量读写数据

1.1.4 核心概念术语表

概念	说明	类比
Database（数据库）	数据的逻辑容器，包含多张表	文件夹
Table（表）	数据的二维结构，由行和列组成	Excel 工作表
Row（行/记录）	表中的一条数据	Excel 中的一行
Column（列/字段）	表中的一个属性	Excel 中的一列
Primary Key（主键）	唯一标识每一行的字段	身份证号
Foreign Key（外键）	引用其他表主键的字段，建立关联	指针/引用
Index（索引）	加速查询的数据结构	书的目录
Schema（模式）	数据库结构的定义（在 MySQL 中等同于 Database）	建筑蓝图
SQL	结构化查询语言，操作数据库的标准语言	数据库的"编程语言"

1.1.5 字符集与排序规则

MySQL 8.0 默认字符集为 utf8mb4，支持完整的 Unicode（包括 emoji 表情）。

sql

-- 查看 MySQL 默认字符集
SHOW VARIABLES LIKE 'character_set%';
-- character_set_server    | utf8mb4

-- 查看排序规则
SHOW VARIABLES LIKE 'collation%';
-- collation_server        | utf8mb4_0900_ai_ci

-- 排序规则命名规则：
-- utf8mb4          → 字符集
-- 0900             → Unicode 版本 9.0
-- ai               → Accent Insensitive（重音不敏感）
-- ci               → Case Insensitive（大小写不敏感）

字符集	说明	每字符字节数	推荐
`utf8`（utf8mb3）	最多 3 字节的 UTF-8，不支持 emoji	1-3	❌ 已过时
`utf8mb4`	完整的 UTF-8，支持所有 Unicode 字符	1-4	✅ 推荐
`latin1`	西欧字符集	1	仅英文场景
`gbk`	中文编码	1-2	❌ 历史遗留

⚠️ 重要：始终使用 utf8mb4，不要使用 utf8（MySQL 的 utf8 实际上是 utf8mb3，不支持 emoji 和部分汉字）。

sql

-- 创建数据库时指定字符集
CREATE DATABASE mydb
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

-- 创建表时指定
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1.2 环境安装与配置

1.2.1 Docker 方式安装（推荐，最简单）

bash

# ===== 方式一：直接 docker run（快速体验）=====
docker run -d \
  --name mysql-learn \
  -e MYSQL_ROOT_PASSWORD=root123 \
  -e MYSQL_DATABASE=learn_db \
  -p 3306:3306 \
  -v mysql-data:/var/lib/mysql \
  mysql:8.0

# 等待 MySQL 启动
sleep 10

# 连接 MySQL
docker exec -it mysql-learn mysql -uroot -proot123

# ===== 方式二：Docker Compose（推荐）=====
# 创建 docker-compose.yml
cat > docker-compose.yml <<'EOF'
services:
  mysql:
    image: mysql:8.0
    container_name: mysql-learn
    environment:
      MYSQL_ROOT_PASSWORD: root123
      MYSQL_DATABASE: learn_db
      MYSQL_USER: learner
      MYSQL_PASSWORD: learn123
    ports:
      - "3306:3306"
    volumes:
      - mysql-data:/var/lib/mysql
      - ./init:/docker-entrypoint-initdb.d  # 初始化 SQL
    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_0900_ai_ci
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost"]
      interval: 10s
      timeout: 5s
      retries: 10
      start_period: 30s
    restart: unless-stopped

volumes:
  mysql-data:
EOF

# 启动
docker compose up -d

# 连接
docker exec -it mysql-learn mysql -uroot -proot123

1.2.2 Linux 直接安装（Ubuntu/Debian）

bash

# ===== APT 安装 MySQL 8.0 =====

# 1. 更新包索引
sudo apt-get update

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

# 3. 启动 MySQL
sudo systemctl start mysql
sudo systemctl enable mysql

# 4. 查看运行状态
sudo systemctl status mysql

# 5. 安全初始化
sudo mysql_secure_installation
# 会询问：
# - 是否启用密码验证插件 → 建议 Yes
# - 设置 root 密码
# - 删除匿名用户 → Yes
# - 禁止 root 远程登录 → 生产环境 Yes，学习环境可 No
# - 删除测试数据库 → Yes
# - 重新加载权限表 → Yes

# 6. 登录 MySQL
sudo mysql -u root -p
# 或者（Ubuntu 默认使用 auth_socket 认证）
sudo mysql

1.2.3 CentOS/RHEL 安装

bash

# ===== YUM 安装 MySQL 8.0 =====

# 1. 添加 MySQL 官方仓库
sudo rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el8-1.noarch.rpm

# 2. 安装
sudo yum install -y mysql-server

# 3. 启动
sudo systemctl start mysqld
sudo systemctl enable mysqld

# 4. 获取临时密码
sudo grep 'temporary password' /var/log/mysqld.log
# 2026-03-13T10:00:00.000000Z 6 [Note] A temporary password is generated for root@localhost: xKj8!mLq#2Pn

# 5. 安全初始化（用临时密码登录后修改）
sudo mysql_secure_installation

1.2.4 MySQL 配置文件 `my.cnf`

MySQL 的配置文件通常位于 /etc/mysql/my.cnf 或 /etc/my.cnf。

ini

# /etc/mysql/my.cnf 或 /etc/my.cnf

[mysqld]
# ===== 基础配置 =====
port = 3306
bind-address = 0.0.0.0              # 监听地址（0.0.0.0 允许远程连接）
datadir = /var/lib/mysql             # 数据目录
socket = /var/run/mysqld/mysqld.sock

# ===== 字符集配置 =====
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci

# ===== InnoDB 配置 =====
innodb_buffer_pool_size = 1G         # Buffer Pool 大小（建议物理内存的 50-75%）
innodb_log_file_size = 256M          # Redo Log 文件大小
innodb_flush_log_at_trx_commit = 1   # 每次事务提交都刷盘（最安全）
innodb_file_per_table = ON           # 每张表一个 ibd 文件

# ===== 连接配置 =====
max_connections = 200                # 最大连接数
wait_timeout = 28800                 # 非交互连接超时（秒）
interactive_timeout = 28800          # 交互连接超时

# ===== 日志配置 =====
log_error = /var/log/mysql/error.log
slow_query_log = ON                  # 开启慢查询日志
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2                  # 超过 2 秒记录为慢查询

# ===== 二进制日志（主从复制需要）=====
server-id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800  # Binlog 保留 7 天

[mysql]
# 客户端默认字符集
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4
port = 3306

bash

# 查看配置文件位置
mysql --help | grep "Default options" -A 1
# 读取顺序：/etc/my.cnf → /etc/mysql/my.cnf → ~/.my.cnf

# 查看当前生效的配置
mysql -uroot -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -uroot -p -e "SHOW VARIABLES LIKE 'max_connections';"

# 修改配置后需要重启 MySQL
sudo systemctl restart mysql

1.2.5 连接 MySQL 的方式

bash

# ===== 方式一：mysql 命令行客户端 =====
mysql -u root -p                         # 本地连接，交互式输入密码
mysql -u root -proot123                  # 直接带密码（不安全，仅测试用）
mysql -h 192.168.1.100 -P 3306 -u root -p  # 远程连接
mysql -u root -p learn_db                # 连接并切换到指定数据库

# 常用参数
mysql -u root -p \
  --default-character-set=utf8mb4 \      # 指定字符集
  -e "SELECT VERSION();"                 # 执行单条 SQL 后退出

# ===== 方式二：GUI 工具 =====
# - MySQL Workbench（官方，免费）
# - DBeaver（开源，支持多种数据库）
# - Navicat（商业，功能强大）
# - DataGrip（JetBrains，付费）

# ===== 方式三：编程语言连接 =====
# Python: pip install pymysql / mysql-connector-python
# Node.js: npm install mysql2
# Go: go get -u github.com/go-sql-driver/mysql
# Java: JDBC mysql-connector-java

1.2.6 用户管理与权限

sql

-- ==================== 用户管理 ====================

-- 查看所有用户
SELECT user, host, plugin FROM mysql.user;

-- 创建用户
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'StrongP@ss123';
CREATE USER 'appuser'@'%' IDENTIFIED BY 'StrongP@ss123';  -- % 表示允许任何主机连接
CREATE USER 'appuser'@'192.168.1.%' IDENTIFIED BY 'StrongP@ss123';  -- 指定网段

-- 修改密码
ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'NewP@ss456';

-- 删除用户
DROP USER 'appuser'@'localhost';

-- ==================== 权限管理 ====================

-- 授予权限
GRANT ALL PRIVILEGES ON mydb.* TO 'appuser'@'%';           -- 某数据库的所有权限
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'readonly'@'%';  -- 指定权限
GRANT SELECT ON mydb.users TO 'analyst'@'%';               -- 精确到表级别

-- 查看用户权限
SHOW GRANTS FOR 'appuser'@'%';

-- 撤销权限
REVOKE INSERT ON mydb.* FROM 'appuser'@'%';
REVOKE ALL PRIVILEGES ON mydb.* FROM 'appuser'@'%';

-- 刷新权限（使权限立即生效）
FLUSH PRIVILEGES;

-- ==================== 权限级别 ====================
-- 全局权限：GRANT ALL ON *.* TO ...      （所有数据库）
-- 数据库权限：GRANT ALL ON mydb.* TO ... （指定数据库的所有表）
-- 表级权限：GRANT SELECT ON mydb.users TO ... （指定表）
-- 列级权限：GRANT SELECT(name, email) ON mydb.users TO ... （指定列）

常用权限说明：

权限	说明
`SELECT`	查询数据
`INSERT`	插入数据
`UPDATE`	更新数据
`DELETE`	删除数据
`CREATE`	创建数据库/表
`DROP`	删除数据库/表
`ALTER`	修改表结构
`INDEX`	创建/删除索引
`ALL PRIVILEGES`	所有权限
`GRANT OPTION`	可以将权限授予其他用户

1.3 数据类型详解

1.3.1 整数类型

类型	字节	有符号范围	无符号范围	使用场景
`TINYINT`	1	-128 ~ 127	0 ~ 255	状态码、布尔值
`SMALLINT`	2	-32768 ~ 32767	0 ~ 65535	年龄、数量
`MEDIUMINT`	3	-8388608 ~ 8388607	0 ~ 16777215	中等数值
`INT`	4	-21亿 ~ 21亿	0 ~ 42亿	最常用，主键、ID
`BIGINT`	8	-922亿亿 ~ 922亿亿	0 ~ 1844亿亿	大数值、雪花 ID

sql

-- 使用建议
CREATE TABLE example (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  -- 无符号自增主键
  age TINYINT UNSIGNED,                        -- 年龄 0-255 足够
  status TINYINT DEFAULT 0,                    -- 状态码
  amount BIGINT,                               -- 大数值
  is_active TINYINT(1) DEFAULT 1               -- 布尔值（MySQL 没有 BOOLEAN）
);

-- ⚠️ INT(11) 中的 11 是显示宽度，不影响存储范围（MySQL 8.0.17 已废弃显示宽度）

1.3.2 浮点与定点类型

类型	字节	精度	使用场景
`FLOAT`	4	~7 位有效数字	科学计算（不要求精确）
`DOUBLE`	8	~15 位有效数字	科学计算
`DECIMAL(M,D)`	M+2	精确计算	金额、价格（推荐）

sql

-- ⚠️ 金额计算必须使用 DECIMAL，不能用 FLOAT/DOUBLE
CREATE TABLE products (
  id INT PRIMARY KEY,
  price DECIMAL(10, 2),      -- 最多 10 位数，其中 2 位小数（如 99999999.99）
  weight FLOAT               -- 重量用 FLOAT 即可
);

-- FLOAT 的精度问题演示
SELECT CAST(0.1 + 0.2 AS FLOAT);          -- 可能不等于 0.3！
SELECT CAST(0.1 + 0.2 AS DECIMAL(10,2));  -- 精确等于 0.30

1.3.3 字符串类型

类型	最大长度	存储方式	使用场景
`CHAR(N)`	255 字符	定长，不足补空格	固定长度：手机号、MD5、UUID
`VARCHAR(N)`	65535 字节	变长，按实际长度存储	最常用，名称、描述
`TEXT`	65535 字节	变长，单独存储	长文本：文章内容
`MEDIUMTEXT`	16MB	变长	大文本
`LONGTEXT`	4GB	变长	超大文本
`ENUM`	—	1-2 字节	枚举值：性别、状态
`SET`	—	1-8 字节	多选值

sql

CREATE TABLE articles (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(200) NOT NULL,           -- 标题，变长
  slug CHAR(36),                         -- UUID，固定 36 位
  content TEXT,                          -- 文章内容
  status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
  phone CHAR(11)                         -- 手机号，固定 11 位
);

-- CHAR vs VARCHAR 对比
-- CHAR(10) 存储 "abc" → 实际占用 10 字节（补 7 个空格）
-- VARCHAR(10) 存储 "abc" → 实际占用 4 字节（3 字节数据 + 1 字节长度前缀）

-- VARCHAR(N) 的 N 是字符数，实际字节数取决于字符集
-- utf8mb4 下 VARCHAR(100) 最多存 100 个字符（最多 400 字节）

1.3.4 日期时间类型

类型	格式	范围	字节	使用场景
`DATE`	YYYY-MM-DD	1000-01-01 ~ 9999-12-31	3	生日、日期
`TIME`	HH:MM:SS	-838:59:59 ~ 838:59:59	3	时间段、时长
`DATETIME`	YYYY-MM-DD HH:MM:SS	1000-01-01 ~ 9999-12-31	8	精确时间，不受时区影响
`TIMESTAMP`	YYYY-MM-DD HH:MM:SS	1970-01-01 ~ 2038-01-19	4	创建时间、更新时间（推荐）
`YEAR`	YYYY	1901 ~ 2155	1	年份

sql

CREATE TABLE events (
  id INT PRIMARY KEY AUTO_INCREMENT,
  event_name VARCHAR(100),
  event_date DATE,                       -- 只需要日期
  start_time TIME,                       -- 只需要时间
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 自动记录创建时间
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  -- 自动更新
);

-- DATETIME vs TIMESTAMP
-- DATETIME：不受时区影响，存什么取什么
-- TIMESTAMP：内部存储 UTC，查询时转换为当前时区
-- 建议：记录时间用 TIMESTAMP（省空间+时区感知），存特定日期用 DATETIME

1.3.5 JSON 类型（MySQL 5.7.8+）

sql

CREATE TABLE configs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  settings JSON                          -- 存储 JSON 数据
);

-- 插入 JSON 数据
INSERT INTO configs (name, settings) VALUES
  ('app', '{"theme": "dark", "lang": "zh-CN", "notifications": {"email": true, "sms": false}}');

-- 查询 JSON 字段
SELECT settings->'$.theme' FROM configs WHERE name = 'app';
-- → "dark"（带引号）

SELECT settings->>'$.theme' FROM configs WHERE name = 'app';
-- → dark（不带引号，推荐）

-- 查询嵌套字段
SELECT settings->>'$.notifications.email' FROM configs;
-- → true

-- 修改 JSON 字段
UPDATE configs SET settings = JSON_SET(settings, '$.theme', 'light') WHERE name = 'app';

-- JSON 函数
SELECT JSON_EXTRACT(settings, '$.lang') FROM configs;
SELECT JSON_KEYS(settings) FROM configs;
SELECT JSON_LENGTH(settings) FROM configs;

1.4 基础 SQL 操作

1.4.1 数据库操作

sql

-- ==================== 创建数据库 ====================
CREATE DATABASE learn_db;
CREATE DATABASE IF NOT EXISTS learn_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

-- ==================== 查看数据库 ====================
SHOW DATABASES;                          -- 列出所有数据库
SHOW CREATE DATABASE learn_db;           -- 查看建库语句

-- ==================== 选择数据库 ====================
USE learn_db;
SELECT DATABASE();                       -- 查看当前使用的数据库

-- ==================== 删除数据库 ====================
DROP DATABASE learn_db;
DROP DATABASE IF EXISTS learn_db;        -- 不存在也不报错

1.4.2 表操作

sql

-- ==================== 创建表 ====================
CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
  username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
  email VARCHAR(100) NOT NULL COMMENT '邮箱',
  password_hash CHAR(60) NOT NULL COMMENT '密码哈希',
  age TINYINT UNSIGNED COMMENT '年龄',
  status ENUM('active', 'inactive', 'banned') DEFAULT 'active' COMMENT '状态',
  bio TEXT COMMENT '个人简介',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  INDEX idx_email (email),               -- 普通索引
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- ==================== 查看表 ====================
SHOW TABLES;                             -- 列出当前数据库的所有表
DESCRIBE users;                          -- 查看表结构（简写 DESC）
SHOW CREATE TABLE users;                 -- 查看建表语句
SHOW TABLE STATUS LIKE 'users'\G         -- 查看表状态信息

-- ==================== 修改表 ====================
-- 添加列
ALTER TABLE users ADD COLUMN phone CHAR(11) AFTER email;

-- 修改列类型
ALTER TABLE users MODIFY COLUMN bio VARCHAR(500);

-- 修改列名
ALTER TABLE users CHANGE COLUMN bio description TEXT;

-- 删除列
ALTER TABLE users DROP COLUMN phone;

-- 添加索引
ALTER TABLE users ADD INDEX idx_username (username);
ALTER TABLE users ADD UNIQUE INDEX uk_email (email);

-- 删除索引
ALTER TABLE users DROP INDEX idx_username;

-- 重命名表
ALTER TABLE users RENAME TO members;
-- 或
RENAME TABLE members TO users;

-- ==================== 删除表 ====================
DROP TABLE users;
DROP TABLE IF EXISTS users;

-- 清空表数据（保留表结构，重置 AUTO_INCREMENT）
TRUNCATE TABLE users;
-- ⚠️ TRUNCATE 比 DELETE 快，但不可回滚，不触发触发器

1.4.3 数据操作（CRUD）

sql

-- ==================== INSERT 插入 ====================

-- 单行插入
INSERT INTO users (username, email, password_hash, age)
VALUES ('alice', 'alice@example.com', '$2b$12$xxxxx', 25);

-- 多行插入（批量插入效率更高）
INSERT INTO users (username, email, password_hash, age) VALUES
  ('bob', 'bob@example.com', '$2b$12$xxxxx', 30),
  ('charlie', 'charlie@example.com', '$2b$12$xxxxx', 28),
  ('diana', 'diana@example.com', '$2b$12$xxxxx', 22);

-- 插入时忽略重复键错误
INSERT IGNORE INTO users (username, email, password_hash)
VALUES ('alice', 'alice@example.com', '$2b$12$xxxxx');

-- 插入或更新（UPSERT）
INSERT INTO users (username, email, password_hash)
VALUES ('alice', 'alice_new@example.com', '$2b$12$xxxxx')
ON DUPLICATE KEY UPDATE email = VALUES(email);

-- 从另一张表插入
INSERT INTO users_backup (username, email)
SELECT username, email FROM users WHERE status = 'active';

-- ==================== SELECT 查询 ====================

-- 基础查询
SELECT * FROM users;                     -- 查询所有列（不推荐在生产中使用 *）
SELECT username, email, age FROM users;  -- 查询指定列（推荐）
SELECT username AS '用户名', age AS '年龄' FROM users;  -- 列别名

-- 条件查询
SELECT * FROM users WHERE age > 25;
SELECT * FROM users WHERE age >= 25 AND status = 'active';
SELECT * FROM users WHERE age < 25 OR status = 'inactive';
SELECT * FROM users WHERE age BETWEEN 20 AND 30;  -- 包含边界值
SELECT * FROM users WHERE status IN ('active', 'inactive');
SELECT * FROM users WHERE username LIKE 'a%';     -- 以 a 开头
SELECT * FROM users WHERE email LIKE '%@gmail.com';-- 以 @gmail.com 结尾
SELECT * FROM users WHERE bio IS NULL;             -- 查询 NULL 值
SELECT * FROM users WHERE bio IS NOT NULL;

-- 排序
SELECT * FROM users ORDER BY age ASC;              -- 升序（默认）
SELECT * FROM users ORDER BY age DESC;             -- 降序
SELECT * FROM users ORDER BY status ASC, age DESC; -- 多字段排序

-- 分页
SELECT * FROM users LIMIT 10;                      -- 前 10 条
SELECT * FROM users LIMIT 10 OFFSET 20;            -- 跳过 20 条，取 10 条
SELECT * FROM users LIMIT 20, 10;                  -- 等效写法（offset, count）

-- 去重
SELECT DISTINCT status FROM users;

-- ==================== UPDATE 更新 ====================

-- 单条更新
UPDATE users SET age = 26 WHERE username = 'alice';

-- 多字段更新
UPDATE users SET age = 26, status = 'active' WHERE username = 'alice';

-- 批量更新
UPDATE users SET status = 'inactive' WHERE age > 50;

-- ⚠️ 安全提示：UPDATE 和 DELETE 一定要带 WHERE 条件！
-- 没有 WHERE 会更新/删除所有行！

-- ==================== DELETE 删除 ====================

-- 条件删除
DELETE FROM users WHERE username = 'alice';
DELETE FROM users WHERE status = 'banned' AND created_at < '2025-01-01';

-- 删除所有数据（但保留表结构）
DELETE FROM users;     -- 可回滚，不重置 AUTO_INCREMENT
TRUNCATE TABLE users;  -- 不可回滚，重置 AUTO_INCREMENT，更快

1.5 实践 Demo

Demo 1：图书管理数据库 — 建库建表与基本操作

sql

-- ===== Step 1: 创建数据库 =====
CREATE DATABASE IF NOT EXISTS bookstore
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

USE bookstore;

-- ===== Step 2: 创建表 =====

-- 作者表
CREATE TABLE authors (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  country VARCHAR(50),
  birth_year SMALLINT UNSIGNED,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) COMMENT='作者表';

-- 分类表
CREATE TABLE categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  description VARCHAR(200)
) COMMENT='图书分类表';

-- 图书表
CREATE TABLE books (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author_id INT UNSIGNED,
  category_id INT UNSIGNED,
  isbn CHAR(13) UNIQUE,
  price DECIMAL(8, 2) NOT NULL,
  stock INT UNSIGNED DEFAULT 0,
  publish_date DATE,
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (author_id) REFERENCES authors(id),
  FOREIGN KEY (category_id) REFERENCES categories(id),
  INDEX idx_title (title),
  INDEX idx_price (price)
) COMMENT='图书表';

-- ===== Step 3: 插入测试数据 =====

-- 插入作者
INSERT INTO authors (name, country, birth_year) VALUES
  ('鲁迅', '中国', 1881),
  ('村上春树', '日本', 1949),
  ('J.K. Rowling', '英国', 1965),
  ('刘慈欣', '中国', 1963),
  ('Stephen King', '美国', 1947);

-- 插入分类
INSERT INTO categories (name, description) VALUES
  ('文学', '小说、散文、诗歌等'),
  ('科幻', '科幻小说与科普读物'),
  ('技术', '编程、计算机科学'),
  ('历史', '历史读物与传记'),
  ('哲学', '哲学与思想类书籍');

-- 插入图书
INSERT INTO books (title, author_id, category_id, isbn, price, stock, publish_date) VALUES
  ('呐喊', 1, 1, '9787020008735', 25.00, 100, '1923-08-01'),
  ('挪威的森林', 2, 1, '9787532725694', 36.00, 80, '1987-09-04'),
  ('Harry Potter and the Philosopher''s Stone', 3, 1, '9780747532699', 68.00, 50, '1997-06-26'),
  ('三体', 4, 2, '9787536692930', 23.00, 200, '2008-01-01'),
  ('三体II：黑暗森林', 4, 2, '9787536693968', 32.00, 150, '2008-05-01'),
  ('三体III：死神永生', 4, 2, '9787536694002', 38.00, 120, '2010-11-01'),
  ('闪灵', 5, 1, '9787532754281', 45.00, 60, '1977-01-28'),
  ('朝花夕拾', 1, 1, '9787020008742', 18.00, 90, '1928-09-01');

-- ===== Step 4: 基本查询练习 =====

-- 查询所有图书
SELECT id, title, price, stock FROM books;

-- 查询价格大于 30 的图书
SELECT title, price FROM books WHERE price > 30 ORDER BY price DESC;

-- 查询中国作者的图书
SELECT b.title, a.name AS author, b.price
FROM books b
JOIN authors a ON b.author_id = a.id
WHERE a.country = '中国';

-- 查询库存不足 100 的图书
SELECT title, stock FROM books WHERE stock < 100 ORDER BY stock ASC;

-- 模糊搜索包含"三体"的图书
SELECT title, price FROM books WHERE title LIKE '%三体%';

-- 查询价格在 20-40 之间的图书
SELECT title, price FROM books WHERE price BETWEEN 20 AND 40;

-- 分页查询（第 2 页，每页 3 条）
SELECT title, price FROM books ORDER BY id LIMIT 3 OFFSET 3;

Demo 2：用户订单系统 — 完整 CRUD

sql

-- ===== 创建订单表 =====
CREATE TABLE orders (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_name VARCHAR(50) NOT NULL,
  book_id INT UNSIGNED NOT NULL,
  quantity INT UNSIGNED NOT NULL DEFAULT 1,
  total_price DECIMAL(10, 2) NOT NULL,
  status ENUM('pending', 'paid', 'shipped', 'completed', 'cancelled') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (book_id) REFERENCES books(id)
) COMMENT='订单表';

-- ===== INSERT: 创建订单 =====
INSERT INTO orders (user_name, book_id, quantity, total_price) VALUES
  ('张三', 4, 2, 46.00),
  ('李四', 3, 1, 68.00),
  ('王五', 1, 3, 75.00),
  ('张三', 5, 1, 32.00);

-- ===== SELECT: 查询订单 =====
-- 查看所有订单及书名
SELECT o.id, o.user_name, b.title, o.quantity, o.total_price, o.status
FROM orders o
JOIN books b ON o.book_id = b.id
ORDER BY o.created_at DESC;

-- 查看张三的所有订单
SELECT o.id, b.title, o.quantity, o.total_price, o.status
FROM orders o
JOIN books b ON o.book_id = b.id
WHERE o.user_name = '张三';

-- ===== UPDATE: 更新订单状态 =====
UPDATE orders SET status = 'paid' WHERE id = 1;
UPDATE orders SET status = 'shipped' WHERE id = 2;
UPDATE orders SET status = 'cancelled' WHERE id = 4;

-- 同时更新库存（下单后扣减）
UPDATE books SET stock = stock - 2 WHERE id = 4;

-- ===== DELETE: 删除取消的订单 =====
DELETE FROM orders WHERE status = 'cancelled';

-- 验证
SELECT * FROM orders;

1.6 常见问题 QA

Q1: `ERROR 1045 (28000): Access denied for user 'root'@'localhost'`

原因：密码错误或认证方式不匹配。

bash

# 方案一：Ubuntu 下 root 使用 auth_socket 认证
sudo mysql                               # 用 sudo 直接登录

# 修改为密码认证
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';
FLUSH PRIVILEGES;

# 方案二：忘记密码，跳过认证重置
sudo systemctl stop mysql
sudo mysqld_safe --skip-grant-tables &
mysql -u root
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;
sudo systemctl restart mysql

Q2: 远程连接失败 — `Can't connect to MySQL server`

bash

# 排查步骤：

# 1. 确认 MySQL 正在运行
sudo systemctl status mysql

# 2. 确认监听地址不是只监听 127.0.0.1
sudo grep bind-address /etc/mysql/mysql.conf.d/mysqld.cnf
# 修改为 0.0.0.0 或注释掉

# 3. 确认用户有远程连接权限
SELECT user, host FROM mysql.user;
-- host 为 'localhost' 只能本地连接
-- 需要创建 host 为 '%' 的用户

# 4. 确认防火墙开放 3306 端口
sudo ufw allow 3306
# 或
sudo iptables -A INPUT -p tcp --dport 3306 -j ACCEPT

# 5. 重启 MySQL
sudo systemctl restart mysql

Q3: 中文乱码问题

sql

-- 排查：检查各级字符集设置
SHOW VARIABLES LIKE 'character_set%';
-- 确保以下都是 utf8mb4：
-- character_set_server
-- character_set_database
-- character_set_client
-- character_set_connection
-- character_set_results

-- 解决方案：在 my.cnf 中配置
-- [mysqld]
-- character-set-server = utf8mb4
-- [client]
-- default-character-set = utf8mb4

-- 或连接时指定
mysql --default-character-set=utf8mb4 -u root -p

-- 修改已有数据库的字符集
ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Q4: `AUTO_INCREMENT` 不连续？

原因：删除行后 AUTO_INCREMENT 不会回退；事务回滚后也不回退；批量插入预分配 ID。

sql

-- 查看当前自增值
SHOW TABLE STATUS LIKE 'users'\G
-- 或
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'users';

-- 重置自增值
ALTER TABLE users AUTO_INCREMENT = 1;
-- ⚠️ 只能设置为大于当前最大 ID 的值

-- 结论：AUTO_INCREMENT 不连续是正常行为，不要依赖它的连续性！

Q5: `TRUNCATE` 和 `DELETE` 有什么区别？

对比	`DELETE`	`TRUNCATE`
语法	DML（数据操作语言）	DDL（数据定义语言）
条件	支持 WHERE	不支持 WHERE
回滚	✅ 可回滚	❌ 不可回滚
触发器	触发 DELETE 触发器	不触发触发器
AUTO_INCREMENT	不重置	重置为 1
速度	慢（逐行删除）	快（直接删除表数据文件）
日志	记录每行删除	只记录页的释放

sql

-- 需要清空表且不需要回滚时用 TRUNCATE（快）
TRUNCATE TABLE logs;

-- 需要条件删除或需要回滚时用 DELETE
DELETE FROM logs WHERE created_at < '2025-01-01';

1.7 命令速查表

┌──────────────────────────────────────────────────────────────────┐
│                    MySQL 基础命令速查表                            │
├──────────────────┬───────────────────────────────────────────────┤
│                  │                                               │
│  数据库操作       │  CREATE DATABASE db;         创建数据库       │
│                  │  USE db;                     切换数据库       │
│                  │  SHOW DATABASES;             列出数据库       │
│                  │  DROP DATABASE db;           删除数据库       │
│                  │                                               │
├──────────────────┼───────────────────────────────────────────────┤
│                  │                                               │
│  表操作          │  CREATE TABLE t (...);       创建表           │
│                  │  DESCRIBE t;                 查看表结构       │
│                  │  SHOW CREATE TABLE t;        查看建表语句      │
│                  │  ALTER TABLE t ADD col type; 添加列           │
│                  │  ALTER TABLE t DROP col;     删除列           │
│                  │  DROP TABLE t;               删除表           │
│                  │  TRUNCATE TABLE t;           清空表           │
│                  │                                               │
├──────────────────┼───────────────────────────────────────────────┤
│                  │                                               │
│  数据操作(CRUD)  │  INSERT INTO t VALUES (...); 插入             │
│                  │  SELECT * FROM t WHERE ...;  查询             │
│                  │  UPDATE t SET col=val WHERE; 更新             │
│                  │  DELETE FROM t WHERE ...;    删除             │
│                  │                                               │
├──────────────────┼───────────────────────────────────────────────┤
│                  │                                               │
│  条件与排序       │  WHERE col > val             条件过滤         │
│                  │  AND / OR / NOT              逻辑运算         │
│                  │  IN (v1, v2, v3)             范围匹配         │
│                  │  BETWEEN a AND b             区间匹配         │
│                  │  LIKE '%pattern%'            模糊匹配         │
│                  │  IS NULL / IS NOT NULL       空值判断         │
│                  │  ORDER BY col ASC/DESC       排序             │
│                  │  LIMIT n OFFSET m            分页             │
│                  │                                               │
├──────────────────┼───────────────────────────────────────────────┤
│                  │                                               │
│  用户与权限       │  CREATE USER 'u'@'h' ...;   创建用户         │
│                  │  GRANT ... ON db.* TO ...;   授予权限         │
│                  │  REVOKE ... ON db.* FROM;    撤销权限         │
│                  │  SHOW GRANTS FOR ...;        查看权限         │
│                  │  DROP USER 'u'@'h';          删除用户         │
│                  │                                               │
├──────────────────┼───────────────────────────────────────────────┤
│                  │                                               │
│  系统信息         │  SELECT VERSION();           版本信息         │
│                  │  SHOW VARIABLES LIKE '...';  查看变量         │
│                  │  SHOW STATUS;                运行状态         │
│                  │  SHOW PROCESSLIST;           当前连接         │
│                  │                                               │
└──────────────────┴───────────────────────────────────────────────┘

📝 学习建议：本阶段以动手实操为主，建议用 Docker 快速搭建 MySQL 环境，然后逐一练习每个 SQL 命令。完成 Demo 1 和 Demo 2 后，可以尝试在 LeetCode 上做几道简单的数据库题目来巩固基础。

第一阶段：MySQL 基础入门 ​

1.1 MySQL 核心概念 ​

1.1.1 什么是关系型数据库？ ​

1.1.2 MySQL 的定位与特点 ​

1.1.3 MySQL 架构概览 ​

1.1.4 核心概念术语表 ​

1.1.5 字符集与排序规则 ​

1.2 环境安装与配置 ​

1.2.1 Docker 方式安装（推荐，最简单） ​

1.2.2 Linux 直接安装（Ubuntu/Debian） ​

1.2.3 CentOS/RHEL 安装 ​

1.2.4 MySQL 配置文件 my.cnf ​

1.2.5 连接 MySQL 的方式 ​

1.2.6 用户管理与权限 ​

1.3 数据类型详解 ​

1.3.1 整数类型 ​

1.3.2 浮点与定点类型 ​

1.3.3 字符串类型 ​

1.3.4 日期时间类型 ​

1.3.5 JSON 类型（MySQL 5.7.8+） ​

1.4 基础 SQL 操作 ​

1.4.1 数据库操作 ​

1.4.2 表操作 ​

1.4.3 数据操作（CRUD） ​

1.5 实践 Demo ​

Demo 1：图书管理数据库 — 建库建表与基本操作 ​

Demo 2：用户订单系统 — 完整 CRUD ​

1.6 常见问题 QA ​

Q1: ERROR 1045 (28000): Access denied for user 'root'@'localhost' ​

Q2: 远程连接失败 — Can't connect to MySQL server ​

Q3: 中文乱码问题 ​

Q4: AUTO_INCREMENT 不连续？ ​

Q5: TRUNCATE 和 DELETE 有什么区别？ ​

1.7 命令速查表 ​

第一阶段：MySQL 基础入门

1.1 MySQL 核心概念

1.1.1 什么是关系型数据库？

1.1.2 MySQL 的定位与特点

1.1.3 MySQL 架构概览

1.1.4 核心概念术语表

1.1.5 字符集与排序规则

1.2 环境安装与配置

1.2.1 Docker 方式安装（推荐，最简单）

1.2.2 Linux 直接安装（Ubuntu/Debian）

1.2.3 CentOS/RHEL 安装

1.2.4 MySQL 配置文件 `my.cnf`

1.2.5 连接 MySQL 的方式

1.2.6 用户管理与权限

1.3 数据类型详解

1.3.1 整数类型

1.3.2 浮点与定点类型

1.3.3 字符串类型

1.3.4 日期时间类型

1.3.5 JSON 类型（MySQL 5.7.8+）

1.4 基础 SQL 操作

1.4.1 数据库操作

1.4.2 表操作

1.4.3 数据操作（CRUD）

1.5 实践 Demo

Demo 1：图书管理数据库 — 建库建表与基本操作

Demo 2：用户订单系统 — 完整 CRUD

1.6 常见问题 QA

Q1: `ERROR 1045 (28000): Access denied for user 'root'@'localhost'`

Q2: 远程连接失败 — `Can't connect to MySQL server`

Q3: 中文乱码问题

Q4: `AUTO_INCREMENT` 不连续？

Q5: `TRUNCATE` 和 `DELETE` 有什么区别？

1.7 命令速查表