MySQL基础教程：全面理论与实践指南

原创

Lethehong

发布于 2025-04-28 19:20:32

1.2K0

文章被收录于专栏：MySQLMySQL

一、MySQL简介与理论基础

MySQL是世界上最流行的开源关系型数据库管理系统之一，广泛应用于网站、应用程序和企业级系统。它采用客户端/服务器架构，支持多用户环境，并基于SQL（结构化查询语言）标准。

关系型数据库核心概念

关系模型：数据以表格（二维表）形式存储，表之间通过关系连接

ACID特性：

原子性(Atomicity)：事务中的操作要么全部完成，要么全部不完成

一致性(Consistency)：事务执行前后，数据库从一个一致状态变到另一个一致状态

隔离性(Isolation)：并发执行的事务之间不会互相影响

持久性(Durability)：事务一旦提交，其结果将永久保存

MySQL架构

MySQL采用多层架构设计：

连接层：处理客户端连接请求

服务层：包括查询解析、优化和缓存

存储引擎层：负责数据的存储和提取

文件系统层：将数据持久化到磁盘

存储引擎

MySQL支持多种存储引擎，每种都有特定的特性和用途：

InnoDB：默认存储引擎，支持事务、外键和行级锁

CREATE TABLE example (id INT) ENGINE=InnoDB;

MyISAM：适合读密集型应用，支持全文索引

CREATE TABLE logs (id INT, message TEXT) ENGINE=MyISAM;

Memory：将数据存储在内存中，速度极快但不持久

CREATE TABLE temp_data (id INT) ENGINE=MEMORY;

Archive：适合存储和检索大量很少被查询的历史数据

CREATE TABLE old_logs (id INT, log_text TEXT) ENGINE=ARCHIVE;

MySQL数据类型

数值类型：

INT：整数类型，4字节

TINYINT：小整数，1字节

BIGINT：大整数，8字节

FLOAT/DOUBLE：浮点数

DECIMAL：精确小数

字符串类型：

CHAR(n)：固定长度字符串

VARCHAR(n)：可变长度字符串

TEXT：长文本

日期和时间类型：

DATE：日期，格式'YYYY-MM-DD'

TIME：时间，格式'HH:MM:SS'

DATETIME：日期和时间，格式'YYYY-MM-DD HH:MM:SS'

TIMESTAMP：时间戳

其他类型：

ENUM：枚举类型

SET：集合类型

BLOB：二进制大对象

二、数据库和表的基本操作

数据库操作语法详解

-- 创建数据库
CREATE DATABASE [IF NOT EXISTS] mydb 
[CHARACTER SET charset_name] 
[COLLATE collation_name];

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

 
-- 使用数据库
USE mydb;
 
-- 删除数据库
DROP DATABASE [IF EXISTS] mydb;

表操作语法详解

-- 创建表
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 主键，自动递增
    name VARCHAR(50) NOT NULL,          -- NOT NULL约束
    age INT CHECK (age > 0),            -- CHECK约束
    gender ENUM('男', '女'),            -- 枚举类型
    class VARCHAR(20),
    score FLOAT DEFAULT 0,              -- 默认值
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 自动记录创建时间
    INDEX idx_class (class)             -- 索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- 查看表结构
DESCRIBE students;
-- 或
SHOW COLUMNS FROM students;
 
-- 修改表结构
ALTER TABLE students ADD COLUMN email VARCHAR(100);
ALTER TABLE students MODIFY COLUMN name VARCHAR(100);
ALTER TABLE students DROP COLUMN email;
 
-- 删除表
DROP TABLE [IF EXISTS] students;

三、增：INSERT 插入数据

插入语法详解

基本语法：

INSERT INTO table_name [(column1, column2, ...)]
VALUES (value1, value2, ...)[, (value1, value2, ...), ...];
插入单条数据

INSERT INTO students (name, age, gender, class, score) 
VALUES ('张三', 20, '男', '计算机科学1班', 89.5);
插入多条数据

INSERT INTO students (name, age, gender, class, score) VALUES 
('李四', 19, '男', '计算机科学1班', 76.0),
('王五', 21, '男', '计算机科学2班', 92.5),
('赵六', 20, '女', '计算机科学2班', 85.0),
('钱七', 22, '女', '计算机科学1班', 79.5);
INSERT的高级用法

-- 插入或更新（如果主键存在则更新）
INSERT INTO students (id, name, score)
VALUES (1, '张三', 95)
ON DUPLICATE KEY UPDATE score = 95;
 
-- 从其他表插入数据
INSERT INTO students_backup
SELECT * FROM students WHERE class = '计算机科学1班';
 
-- 忽略错误继续执行
INSERT IGNORE INTO students (id, name, age)
VALUES (1, '张三', 20);
实际应用场景
当新学生入学时，需要将学生信息录入系统：

-- 新学期开始，添加一批新生
INSERT INTO students (name, age, gender, class, score) VALUES 
('刘备', 18, '男', '计算机1班', NULL),  -- 新生还没有成绩
('关羽', 19, '男', '计算机1班', NULL),
('张飞', 18, '男', '计算机1班', NULL);

四、查：SELECT 查询数据

SELECT语法详解

基本语法：

SELECT [DISTINCT] column1, column2, ...
FROM table_name
[JOIN table_name2 ON join_condition]
[WHERE condition]
[GROUP BY column(s)]
[HAVING group_condition]
[ORDER BY column(s) [ASC|DESC]]
[LIMIT offset, row_count];

查询所有记录

SELECT * FROM students;

查询特定列

SELECT name, age, score FROM students;

条件查询与WHERE子句详解

-- 查询计算机科学1班的学生
SELECT * FROM students WHERE class = '计算机科学1班';
 
-- 查询成绩大于80的学生
SELECT name, score FROM students WHERE score > 80;
 
-- 查询年龄在19到21岁之间的学生
SELECT * FROM students WHERE age BETWEEN 19 AND 21;
 
-- 复合条件：AND, OR, NOT
SELECT * FROM students 
WHERE (class = '计算机科学1班' OR class = '计算机科学2班')
AND score >= 80
AND NOT gender = '女';
 
-- NULL值处理
SELECT * FROM students WHERE score IS NULL;
SELECT * FROM students WHERE score IS NOT NULL;

排序与ORDER BY子句

-- 按成绩降序排列
SELECT * FROM students ORDER BY score DESC;
 
-- 先按班级升序，再按成绩降序
SELECT * FROM students ORDER BY class ASC, score DESC;
 
-- 按字段位置排序（不推荐，但需了解）
SELECT name, age, score FROM students ORDER BY 3 DESC; -- 按第3列(score)排序
分组和聚合函数 
-- 计算每个班级的平均分
SELECT class, AVG(score) as avg_score FROM students GROUP BY class;
 
-- 查找每个班级的最高分和最低分
SELECT 
    class, 
    MAX(score) as highest_score, 
    MIN(score) as lowest_score,
    COUNT(*) as student_count,
    SUM(score) as total_score,
    STDDEV(score) as score_deviation  -- 标准差
FROM students 
WHERE score IS NOT NULL 
GROUP BY class;
 
-- HAVING子句（对分组结果进行筛选）
SELECT class, AVG(score) as avg_score 
FROM students 
GROUP BY class
HAVING avg_score > 80;

限制结果数量

-- 查询前3名学生
SELECT * FROM students ORDER BY score DESC LIMIT 3;
 
-- 分页查询：每页5条，查询第2页
SELECT * FROM students LIMIT 5, 5;  -- 偏移量5，返回5条
-- 或使用更现代的语法
SELECT * FROM students LIMIT 5 OFFSET 5;
模糊查询与LIKE操作符 
-- 查询名字中包含"张"的学生
SELECT * FROM students WHERE name LIKE '%张%';
 
-- 查询以"计算机"开头的班级
SELECT DISTINCT class FROM students WHERE class LIKE '计算机%';
 
-- 通配符说明
-- %：匹配任意数量的字符
-- _：匹配单个字符
SELECT * FROM students WHERE name LIKE '张_';  -- 匹配"张"后跟一个字符的名字

正则表达式查询

-- 查询名字中包含数字的学生
SELECT * FROM students WHERE name REGEXP '[0-9]';
 
-- 查询名字以"张"或"王"开头的学生
SELECT * FROM students WHERE name REGEXP '^[张王]';

实际应用场景

期末考试后，教师需要统计班级情况：


-- 查询每个班级的及格率
SELECT 
    class,
    COUNT(*) as total_students,
    SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END) as passed_students,
    ROUND(SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) as pass_rate
FROM students
WHERE score IS NOT NULL
GROUP BY class;

五、改：UPDATE 更新数据

UPDATE语法详解

基本语法：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
[WHERE condition]
[ORDER BY ...]
[LIMIT row_count];

更新单个记录

-- 更新张三的成绩
UPDATE students SET score = 92.0 WHERE name = '张三';

更新多个字段

-- 李四转班并更新信息
UPDATE students 
SET class = '计算机科学2班', age = 20 
WHERE name = '李四';

条件更新与表达式

-- 给所有90分以上的学生加5分奖励（但不超过100分）
UPDATE students 
SET score = LEAST(score + 5, 100) 
WHERE score > 90;
 
-- 所有学生年龄增加1岁
UPDATE students SET age = age + 1;
 
-- 使用CASE表达式进行条件更新
UPDATE students
SET score = CASE
    WHEN score < 60 THEN score + 5  -- 不及格加5分
    WHEN score >= 60 AND score < 90 THEN score + 3  -- 良好加3分
    ELSE score  -- 优秀不变
END;

多表更新

-- 基于另一个表的数据更新当前表
UPDATE students s
JOIN student_extra_info sei ON s.id = sei.student_id
SET s.email = sei.email, s.phone = sei.phone
WHERE sei.update_flag = 1;

实际应用场景

期中考试后，某些学生参加了补考，需要更新成绩：


-- 批量更新补考成绩
UPDATE students
SET score = CASE
    WHEN name = '李四' THEN 82.5
    WHEN name = '钱七' THEN 88.0
    ELSE score
END
WHERE name IN ('李四', '钱七');

六、删：DELETE 删除数据

DELETE语法详解

基本语法：

DELETE FROM table_name
[WHERE condition]
[ORDER BY ...]
[LIMIT row_count];

删除特定记录

-- 删除指定学生
DELETE FROM students WHERE name = '赵六';

条件删除

-- 删除成绩不及格的学生
DELETE FROM students WHERE score < 60;

限制删除数量

-- 删除成绩最低的3名学生
DELETE FROM students
ORDER BY score ASC
LIMIT 3;

多表删除

-- 删除已经在毕业生表中的学生
DELETE s FROM students s
JOIN graduated_students g ON s.id = g.student_id;

清空表

-- 删除表中所有数据（逐行删除，可回滚）
DELETE FROM students;
 
-- 或者（直接删除表并重建，效率更高，不可回滚）
TRUNCATE TABLE students;
DELETE与TRUNCATE的区别

事务支持：DELETE支持事务回滚，TRUNCATE不支持

速度：TRUNCATE通常更快

自增值：TRUNCATE会重置AUTO_INCREMENT计数器

触发器：DELETE会触发DELETE触发器，TRUNCATE不会

实际应用场景

学期结束，需要清理临时学生数据：


-- 删除已经毕业的学生
DELETE FROM students WHERE id IN (
    SELECT id FROM graduated_students
);
 
-- 假设要删除旧学期的数据并保留新学期数据
-- 创建备份
CREATE TABLE students_new_semester AS 
SELECT * FROM students WHERE entry_year = 2025;
 
-- 清空原表
TRUNCATE TABLE students;
 
-- 将新数据插回原表
INSERT INTO students 
SELECT * FROM students_new_semester;
 
-- 删除临时表
DROP TABLE students_new_semester;

七、高级查询技巧

连接查询详解

MySQL支持多种连接类型：

INNER JOIN（内连接）：返回两表中匹配的行

LEFT JOIN（左连接）：返回左表所有行和右表匹配的行

RIGHT JOIN（右连接）：返回右表所有行和左表匹配的行

CROSS JOIN（交叉连接）：返回两表的笛卡尔积

假设我们有一个课程表：

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(50) NOT NULL,
    teacher VARCHAR(30),
    credits INT
);
 
CREATE TABLE student_courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
 
-- 插入一些数据
INSERT INTO courses (course_name, teacher, credits) VALUES
('数据库原理', '陈教授', 3),
('计算机网络', '王教授', 4),
('操作系统', '李教授', 4);
 
INSERT INTO student_courses (student_id, course_id) VALUES
(1, 1), (1, 2), (2, 1), (3, 3), (4, 2), (5, 3);

连接查询示例：


-- 内连接：查询学生及其选修的课程
SELECT s.name, c.course_name, c.teacher
FROM students s
JOIN student_courses sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id
ORDER BY s.name;
 
-- 左连接：查询所有学生，包括未选课的
SELECT s.name, IFNULL(c.course_name, '未选课') as course
FROM students s
LEFT JOIN student_courses sc ON s.id = sc.student_id
LEFT JOIN courses c ON sc.course_id = c.course_id
ORDER BY s.name;
 
-- 右连接：查询所有课程，包括无人选修的
SELECT c.course_name, IFNULL(s.name, '无人选修') as student
FROM student_courses sc
RIGHT JOIN courses c ON sc.course_id = c.course_id
LEFT JOIN students s ON sc.student_id = s.id
ORDER BY c.course_name;
 
-- 自连接：查找同班同学
SELECT s1.name, s2.name as classmate
FROM students s1
JOIN students s2 ON s1.class = s2.class AND s1.id != s2.id
ORDER BY s1.class, s1.name;

子查询详解

子查询是嵌套在另一个查询中的SELECT语句，可以用在：

SELECT子句

FROM子句

WHERE子句

HAVING子句

-- WHERE子句中的子查询
-- 查询选修了"数据库原理"课程的学生
SELECT name, age, class
FROM students
WHERE id IN (
    SELECT student_id
    FROM student_courses
    WHERE course_id = (SELECT course_id FROM courses WHERE course_name = '数据库原理')
);
 
-- FROM子句中的子查询（派生表）
-- 查询每个班级的平均分，并与学生个人分数比较
SELECT s.name, s.score, c.avg_score,
       s.score - c.avg_score as difference
FROM students s
JOIN (
    SELECT class, AVG(score) as avg_score
    FROM students
    GROUP BY class
) c ON s.class = c.class
ORDER BY difference DESC;
 
-- SELECT子句中的子查询（标量子查询）
-- 查询每个学生选修的课程数量
SELECT s.name, s.class, 
    (SELECT COUNT(*) FROM student_courses WHERE student_id = s.id) AS course_count
FROM students s
ORDER BY course_count DESC;
 
-- EXISTS子查询
-- 查询至少选修了一门课程的学生
SELECT name, class
FROM students s
WHERE EXISTS (
    SELECT 1 FROM student_courses
    WHERE student_id = s.id
);

公用表表达式(CTE)

CTE是一种临时结果集，可以在单个SQL语句中多次引用：

-- 使用WITH子句定义CTE
WITH ClassAvg AS (
    SELECT class, AVG(score) as avg_score
    FROM students
    GROUP BY class
),
ClassRanking AS (
    SELECT s.id, s.name, s.score, s.class,
           RANK() OVER (PARTITION BY s.class ORDER BY s.score DESC) as class_rank
    FROM students s
)
-- 使用定义的CTE
SELECT r.name, r.score, r.class, r.class_rank, c.avg_score
FROM ClassRanking r
JOIN ClassAvg c ON r.class = c.class
WHERE r.class_rank <= 3
ORDER BY r.class, r.class_rank;

窗口函数

窗口函数对一组行执行计算，返回每行的值：


-- 计算每个班级中学生的排名
SELECT name, score, class,
       RANK() OVER (PARTITION BY class ORDER BY score DESC) as class_rank,
       DENSE_RANK() OVER (PARTITION BY class ORDER BY score DESC) as dense_rank,
       ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) as row_num
FROM students;
 
-- 计算累计总和
SELECT name, score, class,
       SUM(score) OVER (PARTITION BY class ORDER BY score) as running_total,
       AVG(score) OVER (PARTITION BY class) as class_avg
FROM students;

八、事务控制

事务是一组操作，要么全部成功，要么全部失败。


-- 开始事务
START TRANSACTION;
 
-- 执行操作
UPDATE students SET score = score + 10 WHERE id = 1;
UPDATE courses SET credits = credits + 1 WHERE course_id = 2;
 
-- 如果一切正常，提交事务
COMMIT;
 
-- 如果出现问题，回滚事务
-- ROLLBACK;
事务隔离级别
MySQL支持四种事务隔离级别：

-- 查看当前隔离级别
SELECT @@TRANSACTION_ISOLATION;
 
-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
READ UNCOMMITTED：可以读取未提交的数据（脏读）

READ COMMITTED：只能读取已提交的数据

REPEATABLE READ：MySQL默认级别，确保同一事务中多次读取结果一致

SERIALIZABLE：最高级别，完全串行执行

保存点

在长事务中，可以设置保存点，回滚到特定位置：


START TRANSACTION;
UPDATE students SET score = score + 5 WHERE id = 1;
 
SAVEPOINT point1;
UPDATE students SET score = score + 10 WHERE id = 2;
 
-- 如果需要，可以回滚到保存点
ROLLBACK TO SAVEPOINT point1;
 
-- 继续事务
UPDATE students SET score = score + 15 WHERE id = 3;
COMMIT;

九、索引与性能优化

索引是提高查询性能的关键：


-- 创建索引
CREATE INDEX idx_student_name ON students(name);
 
-- 创建复合索引
CREATE INDEX idx_class_score ON students(class, score);
 
-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON students(email);
 
-- 查看表的索引
SHOW INDEX FROM students;
 
-- 删除索引
DROP INDEX idx_student_name ON students;

索引类型

B-Tree索引：默认索引类型，适用于等值查询和范围查询

哈希索引：仅适用于等值比较，Memory引擎支持

全文索引：用于全文搜索

CREATE FULLTEXT INDEX idx_fulltext ON articles(title, content);

空间索引：用于地理空间数据


CREATE SPATIAL INDEX idx_location ON places(location);
EXPLAIN分析查询 
-- 分析查询执行计划
EXPLAIN SELECT * FROM students WHERE class = '计算机科学1班' AND score > 80;
查询优化
-- 使用FORCE INDEX强制使用特定索引
SELECT * FROM students FORCE INDEX (idx_class_score)
WHERE class = '计算机科学1班' AND score > 80;
 
-- 使用STRAIGHT_JOIN控制连接顺序
SELECT STRAIGHT_JOIN s.name, c.course_name
FROM students s
JOIN student_courses sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id;

十、视图

视图是基于SQL查询的虚拟表，可以简化复杂查询：


-- 创建视图
CREATE VIEW student_course_view AS
SELECT s.id, s.name, s.class, c.course_name, c.teacher
FROM students s
JOIN student_courses sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id;
 
-- 使用视图
SELECT * FROM student_course_view WHERE class = '计算机科学1班';
 
-- 更新视图（如果基表允许）
UPDATE student_course_view SET name = '张三丰' WHERE id = 1;
 
-- 删除视图
DROP VIEW student_course_view;

视图的优点

简化复杂查询：将复杂查询封装为视图

提高安全性：限制用户只能访问视图中的特定列

数据独立性：应用程序使用视图，底层表结构变化时不需要修改应用

十一、存储过程与函数

存储过程是一组预编译的SQL语句，可以接受参数并执行复杂操作：


-- 创建存储过程
DELIMITER //
CREATE PROCEDURE update_student_score(IN student_id INT, IN new_score FLOAT)
BEGIN
    UPDATE students SET score = new_score WHERE id = student_id;
    
    -- 条件语句
    IF new_score >= 90 THEN
        INSERT INTO honor_students (student_id, honor_type) 
        VALUES (student_id, '优秀学生');
    END IF;
END //
DELIMITER ;
 
-- 调用存储过程
CALL update_student_score(1, 95);
 
-- 创建函数
DELIMITER //
CREATE FUNCTION get_grade(score FLOAT) RETURNS CHAR(1)
DETERMINISTIC
BEGIN
    DECLARE grade CHAR(1);
    
    IF score >= 90 THEN
        SET grade = 'A';
    ELSEIF score >= 80 THEN
        SET grade = 'B';
    ELSEIF score >= 70 THEN
        SET grade = 'C';
    ELSEIF score >= 60 THEN
        SET grade = 'D';
    ELSE
        SET grade = 'F';
    END IF;
    
    RETURN grade;
END //
DELIMITER ;
 
-- 使用函数
SELECT name, score, get_grade(score) as grade FROM students;

存储过程的高级特性

DELIMITER //
CREATE PROCEDURE process_new_students(IN class_name VARCHAR(50))
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE s_id INT;
    DECLARE s_name VARCHAR(50);
    
    -- 声明游标
    DECLARE student_cursor CURSOR FOR 
        SELECT id, name FROM students 
        WHERE class = class_name AND score IS NULL;
    
    -- 声明异常处理
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 开始事务
    START TRANSACTION;
    
    -- 打开游标
    OPEN student_cursor;
    
    -- 循环处理
    read_loop: LOOP
        FETCH student_cursor INTO s_id, s_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 为新生创建初始记录
        INSERT INTO student_records (student_id, status) 
        VALUES (s_id, 'active');
    END LOOP;
    
    -- 关闭游标
    CLOSE student_cursor;
    
    -- 提交事务
    COMMIT;
END //
DELIMITER ;

十二、触发器

触发器是在表上执行特定操作（INSERT、UPDATE、DELETE）时自动执行的特殊存储过程：


-- 创建触发器
DELIMITER //
CREATE TRIGGER after_student_update
AFTER UPDATE ON students
FOR EACH ROW
BEGIN
    -- 记录成绩变化
    IF OLD.score != NEW.score THEN
        INSERT INTO score_changes (student_id, old_score, new_score, change_date)
        VALUES (NEW.id, OLD.score, NEW.score, NOW());
    END IF;
END //
DELIMITER ;
 
-- 查看触发器
SHOW TRIGGERS;
 
-- 删除触发器
DROP TRIGGER after_student_update;

触发器类型

BEFORE触发器：在操作执行前触发

AFTER触发器：在操作执行后触发

行级触发器：对每一行执行一次（FOR EACH ROW）

十三、用户管理与权限控制

MySQL提供了完善的用户管理和权限控制系统：


-- 创建用户
CREATE USER 'teacher'@'localhost' IDENTIFIED BY 'password123';
 
-- 授予权限
GRANT SELECT, UPDATE ON school.students TO 'teacher'@'localhost';
 
-- 授予所有权限
GRANT ALL PRIVILEGES ON school.* TO 'admin'@'localhost';
 
-- 查看用户权限
SHOW GRANTS FOR 'teacher'@'localhost';
 
-- 撤销权限
REVOKE UPDATE ON school.students FROM 'teacher'@'localhost';
 
-- 删除用户
DROP USER 'teacher'@'localhost';

权限级别

MySQL的权限系统分为四个级别：

全局级别：适用于所有数据库

数据库级别：适用于特定数据库中的所有对象

表级别：适用于特定表

列级别：适用于特定列

十四、备份与恢复

数据备份是数据库管理的关键部分：


# 使用mysqldump备份数据库
mysqldump -u root -p school > school_backup.sql
 
# 备份特定表
mysqldump -u root -p school students courses > tables_backup.sql
 
# 恢复数据库
mysql -u root -p school < school_backup.sql

逻辑备份与物理备份

逻辑备份：

以SQL语句形式保存数据（如mysqldump）

优点：可跨版本、平台迁移，可选择性恢复

缺点：备份和恢复速度较慢，尤其是大型数据库

物理备份：

直接复制数据文件（如MySQL Enterprise Backup, Percona XtraBackup）

优点：备份和恢复速度快

缺点：通常依赖于特定MySQL版本和平台

备份策略

完整备份：备份整个数据库


# 完整备份
mysqldump -u root -p --all-databases > full_backup.sql
增量备份：仅备份自上次备份以来的变化

# 使用二进制日志进行增量备份
mysqlbinlog mysql-bin.000001 > incremental_backup.sql
差异备份：备份自上次完整备份以来的所有变化

备份选项与参数 
# 包含存储过程和函数
mysqldump -u root -p --routines school > school_with_routines.sql
 
# 包含触发器
mysqldump -u root -p --triggers school > school_with_triggers.sql
 
# 包含事件
mysqldump -u root -p --events school > school_with_events.sql
 
# 锁定表以确保一致性
mysqldump -u root -p --lock-tables school > school_locked.sql
 
# 使用单个事务进行一致性备份
mysqldump -u root -p --single-transaction school > school_consistent.sql

自动备份

使用cron作业自动执行备份：


# 在crontab中添加以下内容，每天凌晨2点执行备份
0 2 * * * /usr/bin/mysqldump -u root -p'password' school > /backup/school_$(date +\%Y\%m\%d).sql
备份验证与恢复测试
定期测试备份的有效性：

# 创建测试数据库
mysql -u root -p -e "CREATE DATABASE school_test;"
 
# 恢复备份到测试数据库
mysql -u root -p school_test < school_backup.sql
 
# 验证数据
mysql -u root -p -e "SELECT COUNT(*) FROM school_test.students;"

十五、分区表

分区表允许将大表分割成更小的、更易管理的部分：


-- 创建按范围分区的表
CREATE TABLE student_scores (
    id INT NOT NULL,
    student_id INT,
    subject VARCHAR(50),
    score INT,
    test_date DATE,
    PRIMARY KEY (id, test_date)
)
PARTITION BY RANGE (YEAR(test_date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN (2023),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);
 
-- 创建按列表分区的表
CREATE TABLE regional_sales (
    id INT NOT NULL,
    region VARCHAR(50),
    sales DECIMAL(10,2),
    PRIMARY KEY (id, region)
)
PARTITION BY LIST (region) (
    PARTITION p_east VALUES IN ('东北', '华东'),
    PARTITION p_south VALUES IN ('华南', '西南'),
    PARTITION p_north VALUES IN ('华北', '西北'),
    PARTITION p_central VALUES IN ('华中')
);
 
-- 创建按哈希分区的表
CREATE TABLE access_logs (
    id INT NOT NULL,
    user_id INT,
    access_time DATETIME,
    url VARCHAR(255),
    PRIMARY KEY (id, user_id)
)
PARTITION BY HASH (user_id)
PARTITIONS 4;

分区管理


-- 添加分区
ALTER TABLE student_scores ADD PARTITION (
    PARTITION p5 VALUES LESS THAN (2024)
);
 
-- 删除分区
ALTER TABLE student_scores DROP PARTITION p0;
 
-- 重组分区
ALTER TABLE student_scores REORGANIZE PARTITION p1, p2 INTO (
    PARTITION p1_2 VALUES LESS THAN (2022)
);
 
-- 查看表分区信息
SELECT * FROM information_schema.partitions 
WHERE table_name = 'student_scores';

分区优势

提高查询性能：查询可以只扫描相关分区

简化数据管理：可以单独备份、恢复或优化特定分区

提高可用性：不同分区可以存储在不同磁盘上

十六、复制与高可用

MySQL复制允许数据从一个MySQL数据库服务器（主服务器）复制到一个或多个MySQL数据库服务器（从服务器）。

主从复制配置

主服务器配置:


# my.cnf 主服务器配置
[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW

从服务器配置:


# my.cnf 从服务器配置
[mysqld]
server-id = 2
relay_log = mysql-relay-bin

设置复制:


-- 在主服务器上创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
 
-- 获取主服务器状态
SHOW MASTER STATUS;
 
-- 在从服务器上配置复制
CHANGE MASTER TO
    MASTER_HOST='master_host_ip',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=123;
 
-- 启动从服务器复制
START SLAVE;
 
-- 检查从服务器状态
SHOW SLAVE STATUS\G

复制类型

异步复制：默认模式，主服务器不等待从服务器确认

半同步复制：主服务器等待至少一个从服务器确认接收事件

组复制：多主模式，提供高可用性和自动故障转移

高可用解决方案

MySQL Group Replication：内置的高可用解决方案

MySQL InnoDB Cluster：结合MySQL Shell、Group Replication和MySQL Router

第三方工具：如Percona XtraDB Cluster、MariaDB Galera Cluster

十七、性能调优

服务器配置优化

# my.cnf 关键参数
[mysqld]
# 缓冲池大小（通常为系统内存的50-80%）
innodb_buffer_pool_size = 4G
 
# 日志文件大小
innodb_log_file_size = 256M
 
# 并发连接数
max_connections = 500
 
# 查询缓存（MySQL 8.0已移除）
query_cache_size = 64M
query_cache_type = 1
 
# 临时表大小
tmp_table_size = 64M
max_heap_table_size = 64M

查询优化

使用EXPLAIN分析查询：


EXPLAIN SELECT * FROM students WHERE class = '计算机科学1班';

优化索引：


-- 添加适当的索引
CREATE INDEX idx_class ON students(class);
 
-- 使用复合索引
CREATE INDEX idx_class_score ON students(class, score);

重写查询：


-- 优化前
SELECT * FROM students WHERE YEAR(created_at) = 2022;
 
-- 优化后（可以使用索引）
SELECT * FROM students WHERE created_at BETWEEN '2022-01-01' AND '2022-12-31';

限制结果集大小：


-- 使用LIMIT避免返回过多数据
SELECT * FROM logs ORDER BY created_at DESC LIMIT 1000;

表优化

-- 分析表
ANALYZE TABLE students;
 
-- 优化表（重建表和索引）
OPTIMIZE TABLE students;
 
-- 检查表是否损坏
CHECK TABLE students;
 
-- 修复表
REPAIR TABLE students;

监控工具

MySQL性能模式（Performance Schema）：


-- 启用性能模式
SET GLOBAL performance_schema = ON;
 
-- 查询等待事件
SELECT * FROM performance_schema.events_waits_summary_global_by_event_name
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
MySQL系统变量：

-- 查看系统变量
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
 
-- 查看状态变量
SHOW STATUS LIKE 'Threads_connected';

慢查询日志：


# my.cnf 配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2

十八、JSON数据类型与操作

MySQL 5.7及更高版本支持原生JSON数据类型：


-- 创建包含JSON字段的表
CREATE TABLE user_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    profile JSON
);
 
-- 插入JSON数据
INSERT INTO user_profiles (user_id, profile) VALUES
(1, '{"name": "张三", "age": 25, "interests": ["编程", "音乐", "旅行"]}');
 
-- 更新JSON数据
UPDATE user_profiles 
SET profile = JSON_SET(profile, '$.age', 26, '$.email', 'zhangsan@example.com')
WHERE user_id = 1;
 
-- 查询JSON数据
SELECT 
    user_id,
    JSON_EXTRACT(profile, '$.name') AS name,
    JSON_EXTRACT(profile, '$.age') AS age
FROM user_profiles;
 
-- 使用简化语法（->）
SELECT 
    user_id,
    profile->'$.name' AS name,
    profile->'$.age' AS age
FROM user_profiles;
 
-- 使用->>运算符（去除引号）
SELECT 
    user_id,
    profile->>'$.name' AS name,
    profile->>'$.age' AS age
FROM user_profiles;

JSON函数
-- 创建JSON对象
SELECT JSON_OBJECT('name', '李四', 'age', 30, 'city', '北京');
 
-- 创建JSON数组
SELECT JSON_ARRAY('编程', '音乐', '旅行');
 
-- 合并JSON文档
SELECT JSON_MERGE_PRESERVE('{"name": "张三"}', '{"age": 25}');
 
-- 查找JSON数组中的元素
SELECT JSON_CONTAINS(profile->'$.interests', '"音乐"')
FROM user_profiles;
 
-- 提取JSON数组中的所有元素
SELECT JSON_EXTRACT(profile, '$.interests[*]')
FROM user_profiles;
 
-- 获取JSON对象中的所有键
SELECT JSON_KEYS(profile)
FROM user_profiles;

十九、全文搜索

MySQL支持全文搜索功能，允许对文本内容进行高效搜索：


-- 创建带全文索引的表
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX idx_content (title, content)
) ENGINE=InnoDB;
 
-- 插入数据
INSERT INTO articles (title, content) VALUES
('MySQL基础教程', '本文介绍MySQL的基本操作，包括增删改查等内容...'),
('SQL高级技巧', '本文详细讲解SQL的高级用法，包括子查询、存储过程等...'),
('数据库优化指南', '如何优化MySQL数据库性能，包括索引优化、查询优化等...');
 
-- 自然语言模式搜索
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('MySQL 基础' IN NATURAL LANGUAGE MODE);

 
-- 布尔模式搜索
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL -高级' IN BOOLEAN MODE);
 
-- 带有扩展查询的搜索
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('数据库' WITH QUERY EXPANSION);

全文搜索操作符（布尔模式）

+：必须包含该词

-：必须不包含该词

>：增加相关性权重

<：减少相关性权重

*：通配符

""：精确短语匹配

-- 使用布尔模式操作符
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL +"基础教程"' IN BOOLEAN MODE);

二十、常见问题与最佳实践

安全最佳实践

定期更新MySQL：保持最新安全补丁

使用强密码：为所有用户设置强密码

最小权限原则：只授予用户必要的权限

加密连接：使用SSL/TLS加密客户端与服务器之间的通信

审计日志：启用审计日志记录关键操作


-- 启用SSL连接
CREATE USER 'secure_user'@'%' IDENTIFIED BY 'password' REQUIRE SSL;
 
-- 设置密码策略
SET GLOBAL validate_password.policy = STRONG;

性能最佳实践

适当使用索引：为常用查询条件创建索引，但避免过多索引

避免SELECT ：只查询需要的列

使用批量操作：批量插入比单条插入更高效

定期维护表：分析和优化表

合理设置缓存：根据系统内存配置适当的缓冲池大小

常见错误与解决方案

Too many connections：

-- 增加最大连接数
SET GLOBAL max_connections = 500;
 
-- 查看当前连接
SHOW PROCESSLIST;
 
-- 终止空闲连接
KILL connection_id;
Deadlock found：

-- 查看最后一个死锁信息
SHOW ENGINE INNODB STATUS;
 
-- 调整事务隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Table is full：

-- 增加临时表大小
SET GLOBAL tmp_table_size = 128M;
SET GLOBAL max_heap_table_size = 128M;