MySQL 数据库实用教程

目录

1. 第一部分：入门基础

   
   （图片来源网络，侵删）
   • 1 什么是数据库？
   • 2 什么是MySQL？
   • 3 为什么选择MySQL？
   • 4 环境准备：安装MySQL
   • 5 连接到MySQL服务器

2. 第二部分：核心概念与SQL语言

   • 1 数据库核心概念
   • 2 SQL语言简介
   • 3 数据库操作
   • 4 表操作
   • 5 数据类型
   • 6 CRUD操作 (增删改查)

3. 第三部分：高级查询

   • 1 WHERE 子句：条件过滤
   • 2 ORDER BY 子句：排序
   • 3 LIMIT 子句：限制结果数量
   • 4 GROUP BY 和 HAVING：分组与聚合
   • 5 JOIN：多表查询
   • 6 子查询

4. 第四部分：数据完整性

   • 1 约束
   • 2 视图

5. 第五部分：高级特性

   • 1 索引
   • 2 事务
   • 3 用户与权限管理

6. 第六部分：性能优化与最佳实践

   • 1 EXPLAIN 分析查询
   • 2 常见优化建议
   • 3 备份与恢复

---

第一部分：入门基础

1 什么是数据库？

数据库是长期存储在计算机内、有组织的、可共享的大量数据的集合，你可以把它想象成一个高度电子化的、智能化的 filing cabinet（文件柜），它不仅存储数据，还能通过数据库管理系统对数据进行增、删、改、查等操作。

2 什么是MySQL？

MySQL 是目前最流行的开源关系型数据库管理系统之一，它使用结构化查询语言作为其数据操作语言，由于其高性能、稳定性和易用性，被广泛应用于网站后端（如著名的LAMP/LNMP架构）。

3 为什么选择MySQL？

• 开源免费：社区版完全免费。
• 性能卓越：处理速度快，能满足大多数应用场景。
• 简单易用：上手快，文档丰富，社区活跃。
• 兼容性好：与多种编程语言（如PHP, Python, Java, Node.js）完美集成。

4 环境准备：安装MySQL

Windows:

1. 访问 MySQL 官方下载页面。
2. 下载 "MySQL Installer for Windows"。
3. 运行安装程序,选择 "Full"（完整安装）或 "Custom"（自定义安装）以包含 MySQL Server 和 MySQL Workbench（图形化管理工具）。
4. 按照向导完成安装,记住你设置的 root用户密码。

macOS (使用 Homebrew):

1. 安装 Homebrew（如果尚未安装）：/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
2. 安装 MySQL：brew install mysql
3. 启动 MySQL 服务：brew services start mysql
4. 首次运行安全脚本：mysql_secure_installation，它会引导你设置 root 密码并进行安全配置。

Linux (Ubuntu/Debian):

1. 更新包列表：sudo apt update
2. 安装 MySQL Server：sudo apt install mysql-server
3. 运行安全脚本：sudo mysql_secure_installation

5 连接到MySQL服务器

安装完成后,你可以通过命令行或图形工具连接。

使用命令行: 打开终端，输入：

mysql -u root -p

然后输入你在安装时设置的 root 密码，如果连接成功，你会看到 mysql> 提示符。

使用图形工具 (推荐):

• MySQL Workbench: 官方图形界面，功能强大。
• Navicat: 商业软件，界面友好，功能丰富。
• DBeaver: 免费、开源，支持多种数据库。

---

第二部分：核心概念与SQL语言

1 数据库核心概念

• 数据库: 存储数据的仓库。
• 表: 数据库中存储数据的结构化集合，由行和列组成，类似于 Excel 表格。
• 行/记录: 表中的一条数据。
• 列/字段: 表中的一个属性，如 name, age。
• 主键: 表中唯一标识每一行记录的列，不能为空且必须唯一。
• 外键: 用于关联两个表，一个表中的外键对应另一个表的主键。

2 SQL语言简介

SQL (Structured Query Language) 是用于管理关系型数据库的标准语言，主要分为三类：

• DQL (Data Query Language): 数据查询语言，核心是 SELECT。
• DML (Data Manipulation Language): 数据操作语言，如 INSERT, UPDATE, DELETE。
• DDL (Data Definition Language): 数据定义语言，如 CREATE, ALTER, DROP。

3 数据库操作

创建数据库:

CREATE DATABASE my_shop;

如果数据库已存在,避免报错：

CREATE DATABASE IF NOT EXISTS my_shop;

使用数据库:

USE my_shop;

查看所有数据库:

SHOW DATABASES;

删除数据库:

DROP DATABASE my_shop;

警告：删除操作不可逆，会清空所有数据！

4 表操作

创建表:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT, -- 整数类型，主键，自动递增
    username VARCHAR(50) NOT NULL,   -- 可变长度字符串，不能为空
    email VARCHAR(100) UNIQUE NOT NULL, -- 唯一，不能为空
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 时间戳，默认值为当前时间
);

• INT: 整数
• VARCHAR(n): 可变长度字符串，最多n个字符
• PRIMARY KEY: 主键
• AUTO_INCREMENT: 自动递增
• NOT NULL: 不能为空
• UNIQUE: 值必须唯一
• TIMESTAMP: 时间戳类型
• DEFAULT CURRENT_TIMESTAMP: 默认值为当前时间

查看表结构:

DESCRIBE users;

修改表结构:

-- 添加一列
ALTER TABLE users ADD COLUMN age INT;
-- 修改列的数据类型
ALTER TABLE users MODIFY COLUMN age VARCHAR(3);
-- 删除一列
ALTER TABLE users DROP COLUMN age;

删除表:

DROP TABLE users;

5 数据类型

• 数值类型: INT, BIGINT, DECIMAL, FLOAT, DOUBLE
• 字符串类型: CHAR (定长), VARCHAR (变长), TEXT (长文本)
• 日期时间类型: DATE (YYYY-MM-DD), TIME (HH:MM:SS), DATETIME (YYYY-MM-DD HH:MM:SS), TIMESTAMP (时间戳)
• 布尔类型: TINYINT(1), 在MySQL中常用0和1表示false和true。

6 CRUD操作 (增删改查)

C - 创建:

INSERT INTO users (username, email, password) 
VALUES ('alice', 'alice@example.com', 'password123');

一次插入多行：

INSERT INTO users (username, email, password) 
VALUES ('bob', 'bob@example.com', 'password456'),
       ('charlie', 'charlie@example.com', 'password789');

R - 读取:

-- 查询所有列
SELECT * FROM users;
-- 查询指定列
SELECT username, email FROM users;
-- 查询所有用户并按ID降序排列
SELECT * FROM users ORDER BY id DESC;

U - 更新:

-- 将用户 'alice' 的邮箱更新
UPDATE users 
SET email = 'alice_new@example.com' 
WHERE username = 'alice';

重要：WHERE 子句至关重要！如果没有 WHERE，表中所有记录都会被更新！

D - 删除:

-- 删除用户 'bob'
DELETE FROM users 
WHERE username = 'bob';

重要：WHERE 子句至关重要！如果没有 WHERE，表中所有记录都会被删除！

---

第三部分：高级查询

1 WHERE 子句：条件过滤

-- 精确匹配
SELECT * FROM users WHERE username = 'charlie';
-- 模糊匹配 (LIKE)
-- 查找名字以 'a' 开头的用户
SELECT * FROM users WHERE username LIKE 'a%';
-- 查找名字包含 'li' 的用户
SELECT * FROM users WHERE username LIKE '%li%';
-- 比较运算符
SELECT * FROM users WHERE id > 2;
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
-- 逻辑运算符 (AND, OR, NOT)
SELECT * FROM users WHERE age > 20 AND username LIKE 'a%';

2 ORDER BY 子句：排序

-- 按 id 升序 (ASC 是默认的)
SELECT * FROM users ORDER BY id;
-- 按 username 降序
SELECT * FROM users ORDER BY username DESC;
-- 先按 age 升序，再按 username 降序
SELECT * FROM users ORDER BY age ASC, username DESC;

3 LIMIT 子句：限制结果数量

-- 只查询前5条记录
SELECT * FROM users LIMIT 5;
-- 分页查询：查询第6到10条记录 (跳过前5条，取5条)
-- 公式: LIMIT offset, count
SELECT * FROM users LIMIT 5, 5;

4 GROUP BY 和 HAVING：分组与聚合

假设我们有一个 orders 表：

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10, 2),
    order_date DATE
);

-- 计算每个用户的订单总金额
SELECT user_id, SUM(amount) AS total_spent
FROM orders
GROUP BY user_id;

HAVING 用于过滤分组后的结果，类似于 WHERE 用于过滤行。

-- 找出总花费超过1000的用户
SELECT user_id, SUM(amount) AS total_spent
FROM orders
GROUP BY user_id
HAVING total_spent > 1000;

区别: WHERE 在分组前过滤行，HAVING 在分组后过滤组。

5 JOIN：多表查询

假设我们有 users 和 orders 两个表，通过 user_id 关联。

-- 内连接：只返回在两个表中都匹配的记录
SELECT
    users.username,
    orders.amount,
    orders.order_date
FROM
    users
INNER JOIN
    orders ON users.id = orders.user_id;
-- 左连接：返回左表（users）的所有记录，以及右表中匹配的记录，如果右表没有匹配，则显示 NULL。
SELECT
    users.username,
    orders.amount
FROM
    users
LEFT JOIN
    orders ON users.id = orders.user_id;
-- 右连接：返回右表（orders）的所有记录，以及左表中匹配的记录。
SELECT
    users.username,
    orders.amount
FROM
    users
RIGHT JOIN
    orders ON users.id = orders.user_id;

6 子查询

一个查询嵌套在另一个查询的 WHERE, FROM, SELECT 或 HAVING 子句中。

-- 查询购买了订单金额最大的那个订单的用户信息
SELECT * FROM users
WHERE id = (SELECT user_id FROM orders ORDER BY amount DESC LIMIT 1);

---

第四部分：数据完整性

1 约束

约束是数据库中强制执行的数据规则,以确保数据的质量和准确性。

• PRIMARY KEY: 主键约束。
• FOREIGN KEY: 外键约束，保证关联表的引用完整性。
• UNIQUE: 唯一约束，保证列中的值唯一。
• NOT NULL: 非空约束，保证列必须有值。
• CHECK: 检查约束，保证列的值满足特定条件。

示例：使用外键约束

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10, 2),
    order_date DATE,
    -- 添加外键约束
    FOREIGN KEY (user_id) REFERENCES users(id)
);

这会确保 orders 表中的 user_id 必须是 users 表中 id 已存在的值。

2 视图

视图是一个虚拟的表,其内容由查询定义，它不存储数据，而是动态地从基表中生成数据。

创建视图:

CREATE VIEW user_order_summary AS
SELECT
    u.username,
    COUNT(o.id) AS order_count,
    SUM(o.amount) AS total_amount
FROM
    users u
LEFT JOIN
    orders o ON u.id = o.user_id
GROUP BY
    u.id, u.username;

使用视图:

SELECT * FROM user_order_summary WHERE total_amount > 500;

视图可以简化复杂查询,并隐藏底层表的结构，提高安全性。

---

第五部分：高级特性

1 索引

索引是数据库表中一列或多列值的排序结构,用于快速查询和检索数据。索引就像一本书的目录，可以让你快速找到所需信息，而不用逐页翻阅。

创建索引:

-- 为 username 列创建普通索引
CREATE INDEX idx_username ON users(username);
-- 为 email 列创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

何时创建索引？

• 经常作为查询条件的列（如 WHERE 子句中的列）。
• 作为外键的列。
• 经常需要排序的列（ORDER BY 子句中的列）。

何时避免索引？

• 数据量小的表。
• 更新非常频繁的表,因为每次更新数据都需要维护索引，会降低写入速度。
• 列的值区分度不高（性别列，只有 '男' 和 '女'，索引效果不佳）。

2 事务

事务是一个不可分割的工作单元，它包含了一系列操作，这些操作要么全部成功，要么全部失败回滚，这对于保证数据一致性至关重要，例如银行转账。

ACID 特性:

• 原子性: 事务中的所有操作，要么全部完成，要么全部不完成。
• 一致性: 事务必须使数据库从一个一致性状态转移到另一个一致性状态。
• 隔离性: 一个事务的执行不能被其他事务干扰。
• 持久性: 一个事务一旦提交，它对数据库中数据的改变就是永久性的。

事务示例 (使用 InnoDB 引擎):

-- 1. 开启事务
START TRANSACTION;
-- 2. 执行操作
-- 用户A转账100给用户B
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- 3. 提交事务 (如果所有操作都成功)
COMMIT;
-- 如果中间出现错误，可以回滚
-- ROLLBACK;

3 用户与权限管理

默认情况下,只有 root 用户拥有所有权限，在生产环境中，应该为不同应用创建不同的用户，并只授予其必要的权限。

创建用户:

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';

授予权限:

-- 授予 app_user 对 my_shop 数据库所有表的 SELECT, INSERT, UPDATE 权限
GRANT SELECT, INSERT, UPDATE ON my_shop.* TO 'app_user'@'localhost';
-- 授予所有权限 (谨慎使用)
-- GRANT ALL PRIVILEGES ON my_shop.* TO 'app_user'@'localhost';

刷新权限使生效:

FLUSH PRIVILEGES;

撤销权限:

REVOKE DELETE ON my_shop.* FROM 'app_user'@'localhost';

删除用户:

DROP USER 'app_user'@'localhost';

---

第六部分：性能优化与最佳实践

1 EXPLAIN 分析查询

EXPLAIN 是一个强大的工具，用于分析 MySQL 如何执行你的 SELECT 查询，它会显示查询的执行计划，包括是否使用了索引、扫描了多少行等。

EXPLAIN SELECT * FROM users WHERE username = 'alice';

关注点:

• type: 访问类型，从最好到最差依次是 system, const, eq_ref, ref, range, index, ALL，目标是避免 ALL（全表扫描）。
• key: 实际使用的索引名。
• rows: 预估需要扫描的行数，越少越好。
• Extra: 额外信息，如 Using where, Using filesort, Using temporary，这些都可能是性能瓶颈。

2 常见优化建议

1. 为合适的列创建索引：这是最有效的优化手段。
2. *避免使用 `SELECT `**：只查询你需要的列，减少数据传输量。
3. 避免在 WHERE 子句中对字段进行函数操作或表达式计算：这会导致索引失效。WHERE YEAR(created_at) = 2025 应该改为 WHERE created_at >= '2025-01-01' AND created_at < '2025-01-01'。
4. 合理使用 JOIN：确保 JOIN 的列上有索引。
5. 分页优化：对于深度分页（如 LIMIT 100000, 10），查询会先扫描前100010行，可以考虑基于上页最后ID的“键集分页”。
6. 使用连接池：在应用层使用数据库连接池，避免频繁创建和销毁连接。

3 备份与恢复

备份的重要性不言而喻，是最后的救命稻草。

使用 mysqldump 命令行工具备份:

# 备份单个数据库
mysqldump -u root -p my_shop > my_shop_backup.sql
# 备份所有数据库
mysqldump -u root -p --all-databases > all_databases_backup.sql
# 备份时包含数据结构和数据
mysqldump -u root -p my_shop > my_shop_structure_data.sql
# 只备份数据结构 (不包含数据)
mysqldump -u root -p --no-data my_shop > my_shop_structure.sql

恢复:

# 登录到 mysql，然后执行备份文件
mysql -u root -p my_shop < my_shop_backup.sql

---

这份教程涵盖了MySQL从入门到进阶的核心知识,学习数据库是一个持续的过程，除了理论学习，动手实践是掌握MySQL最有效的方法。

后续学习方向:

• 存储过程与函数：封装复杂的SQL逻辑。
• 触发器：在特定事件（如增删改）发生时自动执行操作。
• 分区表：将大表分割成小表，提高查询和管理效率。
• 主从复制：实现数据冗余和读写分离，提升性能和可用性。
• NoSQL数据库：了解MongoDB, Redis等，了解它们与MySQL的异同和应用场景。

希望这份教程能帮助你顺利开启MySQL的学习之旅！