一、MySQL 基础认知与环境搭建

1.1 什么是 MySQL

MySQL 是一款开源的关系型数据库管理系统（RDBMS），广泛应用于 Web 开发、数据分析等领域。它支持多线程、高并发，并且拥有完善的事务处理能力，是目前最流行的数据库之一。

1.2 环境搭建与连接

• 安装：可通过官方下载包或 Docker 快速部署 MySQL 服务。
• 连接方式：
  • 命令行：mysql -u 用户名 -p，输入密码后进入交互模式。
  • 图形化工具：Navicat、DBeaver、MySQL Workbench 等。
  • 编程语言连接：Python（pymysql）、Java（JDBC）、PHP 等。

1.3 核心概念

• 数据库（Database）：存储数据的容器，包含多张表。
• 表（Table）：数据的二维结构，由行（记录）和列（字段）组成。
• 字段（Column）：表的列，定义数据类型和约束。
• 记录（Row）：表的行，代表一条完整数据。
• 主键（Primary Key）：唯一标识表中记录的字段，非空且唯一。

---

二、SQL 基础语法

2.1 数据库操作

-- 创建数据库
CREATE DATABASE IF NOT EXISTS mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 查看数据库
SHOW DATABASES;

-- 使用数据库
USE mydb;

-- 删除数据库
DROP DATABASE IF EXISTS mydb;

2.2 表操作

2.2.1 创建表

CREATE TABLE IF NOT EXISTS employee (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工ID',
    name VARCHAR(50) NOT NULL COMMENT '姓名',
    gender ENUM('男','女') DEFAULT '男' COMMENT '性别',
    age TINYINT UNSIGNED COMMENT '年龄',
    dept_id INT COMMENT '部门ID',
    salary DECIMAL(10,2) DEFAULT 0.00 COMMENT '薪资',
    hire_date DATE COMMENT '入职日期'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工表';

2.2.2 修改表结构

-- 添加字段
ALTER TABLE employee ADD COLUMN phone VARCHAR(11) COMMENT '手机号';

-- 修改字段类型
ALTER TABLE employee MODIFY COLUMN age TINYINT UNSIGNED NOT NULL;

-- 删除字段
ALTER TABLE employee DROP COLUMN phone;

-- 重命名表
RENAME TABLE employee TO emp;

2.2.3 删除表

DROP TABLE IF EXISTS emp;

---

三、数据增删改查（CRUD）

3.1 插入数据（INSERT）

-- 单条插入
INSERT INTO emp (name, gender, age, dept_id, salary, hire_date)
VALUES ('张三', '男', 25, 1, 8000.00, '2021-01-15');

-- 批量插入
INSERT INTO emp (name, gender, age, dept_id, salary, hire_date)
VALUES 
('李四', '女', 28, 2, 9500.00, '2020-05-20'),
('王五', '男', 32, 1, 12000.00, '2019-03-10');

3.2 查询数据（SELECT）

3.2.1 基础查询

-- 查询所有字段
SELECT * FROM emp;

-- 查询指定字段
SELECT name, salary FROM emp;

-- 条件查询
SELECT name, age FROM emp WHERE age > 30;

-- 排序
SELECT name, salary FROM emp ORDER BY salary DESC;

-- 分页
SELECT * FROM emp LIMIT 0, 2;

3.2.2 聚合函数与分组

-- 统计员工数量
SELECT COUNT(*) FROM emp;

-- 平均薪资
SELECT AVG(salary) FROM emp;

-- 按部门分组统计
SELECT dept_id, COUNT(*), AVG(salary) 
FROM emp 
GROUP BY dept_id 
HAVING AVG(salary) > 8000;

3.3 更新数据（UPDATE）

-- 更新单条记录
UPDATE emp SET salary = 8500.00 WHERE id = 1;

-- 批量更新
UPDATE emp SET age = age + 1 WHERE dept_id = 1;

3.4 删除数据（DELETE）

-- 删除单条记录
DELETE FROM emp WHERE id = 3;

-- 清空表（谨慎使用）
TRUNCATE TABLE emp;

---

四、高级查询与多表操作

4.1 多表连接（JOIN）

4.1.1 内连接（INNER JOIN）

SELECT e.name, d.dept_name
FROM emp e
INNER JOIN dept d ON e.dept_id = d.id;

4.1.2 左连接（LEFT JOIN）

SELECT e.name, d.dept_name
FROM emp e
LEFT JOIN dept d ON e.dept_id = d.id;

4.1.3 右连接（RIGHT JOIN）

SELECT e.name, d.dept_name
FROM emp e
RIGHT JOIN dept d ON e.dept_id = d.id;

4.2 子查询

-- 标量子查询
SELECT name FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);

-- 列子查询
SELECT name FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE dept_name = '技术部');

4.3 联合查询（UNION）

SELECT name FROM emp WHERE gender = '男'
UNION
SELECT name FROM emp WHERE age > 30;

---

五、约束与索引

5.1 常用约束

• 主键约束（PRIMARY KEY）：唯一标识记录，非空且唯一。
• 唯一约束（UNIQUE）：字段值不可重复。
• 非空约束（NOT NULL）：字段值不能为空。
• 默认约束（DEFAULT）：字段未赋值时使用默认值。
• 外键约束（FOREIGN KEY）：建立表与表之间的关联。

CREATE TABLE dept (
    id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50) UNIQUE NOT NULL,
    location VARCHAR(100) DEFAULT '北京'
);

5.2 索引优化

索引是提升查询效率的关键手段，常见类型：

• 普通索引（INDEX）：基础索引，加速查询。
• 唯一索引（UNIQUE INDEX）：字段值唯一，同时加速查询。
• 主键索引（PRIMARY KEY）：特殊的唯一索引，每张表只能有一个。
• 复合索引：多个字段组合的索引，适用于多条件查询。

-- 创建普通索引
CREATE INDEX idx_emp_name ON emp(name);

-- 创建复合索引
CREATE INDEX idx_emp_dept_salary ON emp(dept_id, salary);

-- 删除索引
DROP INDEX idx_emp_name ON emp;

---

六、事务与存储引擎

6.1 事务（Transaction）

事务是一组原子性的 SQL 操作，要么全部执行成功，要么全部失败。

• ACID 特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。
• 事务操作：

-- 开启事务
START TRANSACTION;

-- 执行 SQL 操作
UPDATE emp SET salary = salary - 1000 WHERE id = 1;
UPDATE emp SET salary = salary + 1000 WHERE id = 2;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

6.2 存储引擎

MySQL 支持多种存储引擎，最常用的是 InnoDB 和 MyISAM：

特性	InnoDB	MyISAM
事务支持	支持	不支持
外键	支持	不支持
行级锁	支持	表级锁
并发性能	高	低
适用场景	高并发、事务型业务	读多写少、统计分析

---

七、视图、存储过程与函数

7.1 视图（View）

视图是虚拟表，基于查询结果创建，简化复杂查询并保障数据安全。

-- 创建视图
CREATE VIEW emp_dept_view AS
SELECT e.id, e.name, e.salary, d.dept_name
FROM emp e
JOIN dept d ON e.dept_id = d.id;

-- 查询视图
SELECT * FROM emp_dept_view;

7.2 存储过程（Procedure）

存储过程是一组预编译的 SQL 语句，可重复调用，提升执行效率。

DELIMITER //
CREATE PROCEDURE get_emp_by_dept(IN dept_id INT)
BEGIN
    SELECT * FROM emp WHERE dept_id = dept_id;
END //
DELIMITER ;

-- 调用存储过程
CALL get_emp_by_dept(1);

7.3 函数（Function）

函数与存储过程类似，但必须返回一个值。

DELIMITER //
CREATE FUNCTION get_avg_salary(dept_id INT) 
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE avg_sal DECIMAL(10,2);
    SELECT AVG(salary) INTO avg_sal FROM emp WHERE dept_id = dept_id;
    RETURN avg_sal;
END //
DELIMITER ;

-- 调用函数
SELECT get_avg_salary(1);

---

八、用户权限与安全

8.1 创建用户

CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'password123';

8.2 授权与回收权限

-- 授予查询权限
GRANT SELECT ON mydb.* TO 'test_user'@'localhost';

-- 授予所有权限
GRANT ALL PRIVILEGES ON mydb.* TO 'admin_user'@'%';

-- 回收权限
REVOKE SELECT ON mydb.* FROM 'test_user'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

8.3 删除用户

DROP USER 'test_user'@'localhost';

---

九、备份与恢复

9.1 备份（mysqldump）

# 备份整个数据库
mysqldump -u root -p mydb > mydb_backup.sql

# 备份单张表
mysqldump -u root -p mydb emp > emp_backup.sql

9.2 恢复

# 恢复数据库
mysql -u root -p mydb < mydb_backup.sql

---

十、性能优化与常见问题

10.1 性能优化建议

• 合理设计索引：避免过度索引，优先为高频查询字段创建索引。
• 优化 SQL 语句：避免 SELECT *，减少子查询嵌套，使用 EXPLAIN 分析执行计划。
• 分库分表：大数据量场景下，通过水平 / 垂直分表提升性能。
• 配置优化：调整 my.cnf 中的缓冲池大小、连接数等参数。

10.2 常见问题排查

• 连接超时：检查网络、防火墙、MySQL 最大连接数配置。
• 慢查询：开启慢查询日志，定位低效 SQL。
• 死锁：通过 SHOW ENGINE INNODB STATUS 查看死锁信息，优化事务逻辑。

---

十一、实战案例：员工管理系统

11.1 需求分析

实现一个简单的员工管理系统，包含部门表和员工表，支持增删改查、薪资统计等功能。

11.2 表结构设计

-- 部门表
CREATE TABLE dept (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '部门ID',
    dept_name VARCHAR(50) UNIQUE NOT NULL COMMENT '部门名称',
    location VARCHAR(100) DEFAULT '北京' COMMENT '部门位置'
) COMMENT='部门表';

-- 员工表
CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工ID',
    name VARCHAR(50) NOT NULL COMMENT '姓名',
    gender ENUM('男','女') DEFAULT '男' COMMENT '性别',
    age TINYINT UNSIGNED COMMENT '年龄',
    dept_id INT COMMENT '部门ID',
    salary DECIMAL(10,2) DEFAULT 0.00 COMMENT '薪资',
    hire_date DATE COMMENT '入职日期',
    FOREIGN KEY (dept_id) REFERENCES dept(id) ON DELETE SET NULL
) COMMENT='员工表';

11.3 核心功能实现

• 查询各部门平均薪资：

SELECT d.dept_name, AVG(e.salary) AS avg_salary
FROM dept d
LEFT JOIN emp e ON d.id = e.dept_id
GROUP BY d.id;

• 统计入职时间超过 3 年的员工：

SELECT name, hire_date, DATEDIFF(CURDATE(), hire_date)/365 AS work_years
FROM emp
WHERE DATEDIFF(CURDATE(), hire_date) > 3*365;

---

总结

本文从 MySQL 基础环境搭建、SQL 语法、高级查询，到事务、索引、存储过程等高级特性，再到性能优化与实战案例，全面覆盖了 MySQL 开发的核心知识点。掌握这些内容，能够帮助开发者高效构建稳定、高性能的数据库应用。