MySQL简介与理论基础
一、MySQL简介与理论基础
MySQL是世界上最流行的开源关系型数据库管理系统之一，广泛应用于网站、应用程序和企业级系统。它采用客户端/服务器架构，支持多用户环境，并基于SQL（结构化查询语言）标准。
关系型数据库核心概念
1.关系模型：数据以表格（二维表）形式存储，表之间通过关系连接
2.ACID特性：
?原子性(Atomicity)：事务中的操作要么全部完成，要么全部不完成
?一致性(Consistency)：事务执行前后，数据库从一个一致状态变到另一个一致状态
?隔离性(Isolation)：并发执行的事务之间不会互相影响
?持久性(Durability)：事务一旦提交，其结果将永久保存
MySQL架构
MySQL采用多层架构设计：
1.连接层：处理客户端连接请求
2.服务层：包括查询解析、优化和缓存
3.存储引擎层：负责数据的存储和提取
4.文件系统层：将数据持久化到磁盘
存储引擎
MySQL支持多种存储引擎，每种都有特定的特性和用途：
?InnoDB：默认存储引擎，支持事务、外键和行级锁
`CREATE TABLEexample (idINT) ENGINE=InnoDB;`
?MyISAM：适合读密集型应用，支持全文索引
`CREATE TABLElogs (idINT, message TEXT) ENGINE=MyISAM;`
?Memory：将数据存储在内存中，速度极快但不持久
`CREATE TABLEtemp_data (idINT) ENGINE=MEMORY;`
?Archive：适合存储和检索大量很少被查询的历史数据
`CREATE TABLEold_logs (idINT, log_text TEXT) ENGINE=ARCHIVE;`
MySQL数据类型
1.数值类型：
? INT：整数类型，4字节
? TINYINT：小整数，1字节
? BIGINT：大整数，8字节
? FLOAT/DOUBLE：浮点数
? DECIMAL：精确小数
2.字符串类型：
? CHAR(n)：固定长度字符串
? VARCHAR(n)：可变长度字符串
? TEXT：长文本
3.日期和时间类型：
? DATE：日期，格式'YYYY-MM-DD'
? TIME：时间，格式'HHSS'
? DATETIME：日期和时间，格式'YYYY-MM-DD HHSS'
? TIMESTAMP：时间戳
4.其他类型：
? ENUM：枚举类型
? SET：集合类型
? BLOB：二进制大对象
二、数据库和表的基本操作
数据库操作语法详解
-- 创建数据库 CREATEDATABASE [IFNOTEXISTS] mydb [CHARACTER SETcharset_name] [COLLATEcollation_name]; -- 查看所有数据库 SHOWDATABASES; -- 使用数据库 USE mydb; -- 删除数据库 DROPDATABASE [IFEXISTS] mydb;
表操作语法详解
-- 创建表
CREATE TABLEstudents (
idINTAUTO_INCREMENTPRIMARY KEY, -- 主键，自动递增
nameVARCHAR(50)NOT NULL, -- NOT NULL约束
ageINTCHECK(age>0), -- CHECK约束
gender ENUM('男','女'), -- 枚举类型
classVARCHAR(20),
scoreFLOATDEFAULT0, -- 默认值
created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP, -- 自动记录创建时间
INDEX idx_class (class) -- 索引
) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;
-- 查看表结构
DESCRIBEstudents;
-- 或
SHOWCOLUMNSFROMstudents;
-- 修改表结构
ALTER TABLEstudentsADDCOLUMNemailVARCHAR(100);
ALTER TABLEstudents MODIFYCOLUMNnameVARCHAR(100);
ALTER TABLEstudentsDROPCOLUMNemail;
-- 删除表
DROPTABLE[IFEXISTS] students;
三、增：INSERT 插入数据
插入语法详解
基本语法：
INSERT INTOtable_name [(column1, column2, ...)] VALUES(value1, value2, ...)[, (value1, value2, ...), ...];
插入单条数据
INSERT INTOstudents (name, age, gender, class, score)
VALUES('张三',20,'男','计算机科学1班',89.5);
插入多条数据
INSERT INTOstudents (name, age, gender, class, score)VALUES
('李四',19,'男','计算机科学1班',76.0),
('王五',21,'男','计算机科学2班',92.5),
('赵六',20,'女','计算机科学2班',85.0),
('钱七',22,'女','计算机科学1班',79.5);
INSERT的高级用法
-- 插入或更新（如果主键存在则更新） INSERT INTOstudents (id, name, score) VALUES(1,'张三',95) ONDUPLICATE KEYUPDATEscore=95; -- 从其他表插入数据 INSERT INTOstudents_backup SELECT*FROMstudentsWHEREclass='计算机科学1班'; -- 忽略错误继续执行 INSERTIGNOREINTOstudents (id, name, age) VALUES(1,'张三',20);
实际应用场景
当新学生入学时，需要将学生信息录入系统：
-- 新学期开始，添加一批新生
INSERT INTOstudents (name, age, gender, class, score)VALUES
('刘备',18,'男','计算机1班',NULL), -- 新生还没有成绩
('关羽',19,'男','计算机1班',NULL),
('张飞',18,'男','计算机1班',NULL);
四、查：SELECT 查询数据
SELECT语法详解
基本语法：
SELECT[DISTINCT] column1, column2, ... FROMtable_name [JOINtable_name2ONjoin_condition] [WHEREcondition] [GROUPBYcolumn(s)] [HAVINGgroup_condition] [ORDERBYcolumn(s) [ASC|DESC]] [LIMIToffset, row_count];
查询所有记录
`SELECT*FROMstudents;`
查询特定列
`SELECTname, age, scoreFROMstudents;`
条件查询与WHERE子句详解
-- 查询计算机科学1班的学生 SELECT*FROMstudentsWHEREclass='计算机科学1班'; -- 查询成绩大于80的学生 SELECTname, scoreFROMstudentsWHEREscore>80; -- 查询年龄在19到21岁之间的学生 SELECT*FROMstudentsWHEREageBETWEEN19AND21; -- 复合条件：AND, OR, NOT SELECT*FROMstudents WHERE(class='计算机科学1班'ORclass='计算机科学2班') ANDscore>=80 ANDNOTgender='女'; -- NULL值处理 SELECT*FROMstudentsWHEREscoreISNULL; SELECT*FROMstudentsWHEREscoreISNOT NULL;
排序与ORDER BY子句
-- 按成绩降序排列 SELECT*FROMstudentsORDERBYscoreDESC; -- 先按班级升序，再按成绩降序 SELECT*FROMstudentsORDERBYclassASC, scoreDESC; -- 按字段位置排序（不推荐，但需了解） SELECTname, age, scoreFROMstudentsORDERBY3DESC;-- 按第3列(score)排序
分组和聚合函数
-- 计算每个班级的平均分 SELECTclass,AVG(score)asavg_scoreFROMstudentsGROUPBYclass; -- 查找每个班级的最高分和最低分 SELECT class, MAX(score)ashighest_score, MIN(score)aslowest_score, COUNT(*)asstudent_count, SUM(score)astotal_score, STDDEV(score)asscore_deviation -- 标准差 FROMstudents WHEREscoreISNOT NULL GROUPBYclass; -- HAVING子句（对分组结果进行筛选） SELECTclass,AVG(score)asavg_score FROMstudents GROUPBYclass HAVINGavg_score>80;
限制结果数量
-- 查询前3名学生 SELECT*FROMstudentsORDERBYscoreDESCLIMIT3; -- 分页查询：每页5条，查询第2页 SELECT*FROMstudents LIMIT5,5; -- 偏移量5，返回5条 -- 或使用更现代的语法 SELECT*FROMstudents LIMIT5OFFSET5;
模糊查询与LIKE操作符
-- 查询名字中包含"张"的学生 SELECT*FROMstudentsWHEREnameLIKE'%张%'; -- 查询以"计算机"开头的班级 SELECTDISTINCTclassFROMstudentsWHEREclassLIKE'计算机%'; -- 通配符说明 -- %：匹配任意数量的字符 -- _：匹配单个字符 SELECT*FROMstudentsWHEREnameLIKE'张_'; -- 匹配"张"后跟一个字符的名字
正则表达式查询
-- 查询名字中包含数字的学生 SELECT*FROMstudentsWHEREname REGEXP'[0-9]'; -- 查询名字以"张"或"王"开头的学生 SELECT*FROMstudentsWHEREname REGEXP'^[张王]';
实际应用场景
期末考试后，教师需要统计班级情况：
-- 查询每个班级的及格率 SELECT class, COUNT(*)astotal_students, SUM(CASEWHENscore>=60THEN1ELSE0END)aspassed_students, ROUND(SUM(CASEWHENscore>=60THEN1ELSE0END)/COUNT(*)*100,2)aspass_rate FROMstudents WHEREscoreISNOT NULL GROUPBYclass;
五、改：UPDATE 更新数据
UPDATE语法详解
基本语法：
UPDATEtable_name SETcolumn1=value1, column2=value2, ... [WHEREcondition] [ORDERBY...] [LIMIT row_count];
更新单个记录
-- 更新张三的成绩 UPDATEstudentsSETscore=92.0WHEREname='张三';
更新多个字段
-- 李四转班并更新信息 UPDATEstudents SETclass='计算机科学2班', age=20 WHEREname='李四';
条件更新与表达式
-- 给所有90分以上的学生加5分奖励（但不超过100分） UPDATEstudents SETscore=LEAST(score+5,100) WHEREscore>90; -- 所有学生年龄增加1岁 UPDATEstudentsSETage=age+1; -- 使用CASE表达式进行条件更新 UPDATEstudents SETscore=CASE WHENscore<60THEN?score?+5-- 不及格加5分 ? ??WHEN?score?>=60ANDscore<90THEN?score?+3-- 良好加3分 ? ??ELSE?score ?-- 优秀不变 END;
多表更新
-- 基于另一个表的数据更新当前表 UPDATEstudents s JOINstudent_extra_info seiONs.id=sei.student_id SETs.email=sei.email, s.phone=sei.phone WHEREsei.update_flag=1;
实际应用场景
期中考试后，某些学生参加了补考，需要更新成绩：
-- 批量更新补考成绩
UPDATEstudents
SETscore=CASE
WHENname='李四'THEN82.5
WHENname='钱七'THEN88.0
ELSEscore
END
WHEREnameIN('李四','钱七');
六、删：DELETE 删除数据
DELETE语法详解
基本语法：
DELETEFROMtable_name [WHEREcondition] [ORDERBY...] [LIMIT row_count];
删除特定记录
-- 删除指定学生 DELETEFROMstudentsWHEREname='赵六';
条件删除
-- 删除成绩不及格的学生 DELETEFROMstudentsWHEREscore
限制删除数量
-- 删除成绩最低的3名学生 DELETEFROMstudents ORDERBYscoreASC LIMIT3;
多表删除
-- 删除已经在毕业生表中的学生 DELETEsFROMstudents s JOINgraduated_students gONs.id=g.student_id;
清空表
-- 删除表中所有数据（逐行删除，可回滚） DELETEFROMstudents; -- 或者（直接删除表并重建，效率更高，不可回滚） TRUNCATETABLEstudents;
DELETE与TRUNCATE的区别
1.事务支持：DELETE支持事务回滚，TRUNCATE不支持
2.速度：TRUNCATE通常更快
3.自增值：TRUNCATE会重置AUTO_INCREMENT计数器
4.触发器：DELETE会触发DELETE触发器，TRUNCATE不会
实际应用场景
学期结束，需要清理临时学生数据：
-- 删除已经毕业的学生 DELETEFROMstudentsWHEREidIN( SELECTidFROMgraduated_students ); -- 假设要删除旧学期的数据并保留新学期数据 -- 创建备份 CREATE TABLEstudents_new_semesterAS SELECT*FROMstudentsWHEREentry_year=2025; -- 清空原表 TRUNCATETABLEstudents; -- 将新数据插回原表 INSERT INTOstudents SELECT*FROMstudents_new_semester; -- 删除临时表 DROPTABLEstudents_new_semester;
七、高级查询技巧
连接查询详解
MySQL支持多种连接类型：
? INNER JOIN（内连接）：返回两表中匹配的行
? LEFT JOIN（左连接）：返回左表所有行和右表匹配的行
? RIGHT JOIN（右连接）：返回右表所有行和左表匹配的行
? CROSS JOIN（交叉连接）：返回两表的笛卡尔积
假设我们有一个课程表：
CREATE TABLEcourses ( course_idINTAUTO_INCREMENTPRIMARY KEY, course_nameVARCHAR(50)NOT NULL, teacherVARCHAR(30), creditsINT ); CREATE TABLEstudent_courses ( idINTAUTO_INCREMENTPRIMARY KEY, student_idINT, course_idINT, FOREIGN KEY(student_id)REFERENCESstudents(id), FOREIGN KEY(course_id)REFERENCEScourses(course_id) ); -- 插入一些数据 INSERT INTOcourses (course_name, teacher, credits)VALUES ('数据库原理','陈教授',3), ('计算机网络','王教授',4), ('操作系统','李教授',4); INSERT INTOstudent_courses (student_id, course_id)VALUES (1,1), (1,2), (2,1), (3,3), (4,2), (5,3);
连接查询示例：
-- 内连接：查询学生及其选修的课程 SELECTs.name, c.course_name, c.teacher FROMstudents s JOINstudent_courses scONs.id=sc.student_id JOINcourses cONsc.course_id=c.course_id ORDERBYs.name; -- 左连接：查询所有学生，包括未选课的 SELECTs.name, IFNULL(c.course_name,'未选课')ascourse FROMstudents s LEFTJOINstudent_courses scONs.id=sc.student_id LEFTJOINcourses cONsc.course_id=c.course_id ORDERBYs.name; -- 右连接：查询所有课程，包括无人选修的 SELECTc.course_name, IFNULL(s.name,'无人选修')asstudent FROMstudent_courses sc RIGHTJOINcourses cONsc.course_id=c.course_id LEFTJOINstudents sONsc.student_id=s.id ORDERBYc.course_name; -- 自连接：查找同班同学 SELECTs1.name, s2.nameasclassmate FROMstudents s1 JOINstudents s2ONs1.class=s2.classANDs1.id!=s2.id ORDERBYs1.class, s1.name;
子查询详解
子查询是嵌套在另一个查询中的SELECT语句，可以用在：
? SELECT子句
? FROM子句
? WHERE子句
? HAVING子句
-- WHERE子句中的子查询 -- 查询选修了"数据库原理"课程的学生 SELECTname, age, class FROMstudents WHEREidIN( SELECTstudent_id FROMstudent_courses WHEREcourse_id=(SELECTcourse_idFROMcoursesWHEREcourse_name='数据库原理') ); -- FROM子句中的子查询（派生表） -- 查询每个班级的平均分，并与学生个人分数比较 SELECTs.name, s.score, c.avg_score, s.score-c.avg_scoreasdifference FROMstudents s JOIN( SELECTclass,AVG(score)asavg_score FROMstudents GROUPBYclass ) cONs.class=c.class ORDERBYdifferenceDESC; -- SELECT子句中的子查询（标量子查询） -- 查询每个学生选修的课程数量 SELECTs.name, s.class, (SELECTCOUNT(*)FROMstudent_coursesWHEREstudent_id=s.id)AScourse_count FROMstudents s ORDERBYcourse_countDESC; -- EXISTS子查询 -- 查询至少选修了一门课程的学生 SELECTname, class FROMstudents s WHEREEXISTS( SELECT1FROMstudent_courses WHEREstudent_id=s.id );
公用表表达式(CTE)
CTE是一种临时结果集，可以在单个SQL语句中多次引用：
-- 使用WITH子句定义CTE WITHClassAvgAS( SELECTclass,AVG(score)asavg_score FROMstudents GROUPBYclass ), ClassRankingAS( SELECTs.id, s.name, s.score, s.class, RANK()OVER(PARTITIONBYs.classORDERBYs.scoreDESC)asclass_rank FROMstudents s ) -- 使用定义的CTE SELECTr.name, r.score, r.class, r.class_rank, c.avg_score FROMClassRanking r JOINClassAvg cONr.class=c.class WHEREr.class_rank<=3 ORDERBY?r.class, r.class_rank;
窗口函数
窗口函数对一组行执行计算，返回每行的值：
-- 计算每个班级中学生的排名 SELECTname, score, class, RANK()OVER(PARTITIONBYclassORDERBYscoreDESC)asclass_rank, DENSE_RANK()OVER(PARTITIONBYclassORDERBYscoreDESC)asdense_rank, ROW_NUMBER()OVER(PARTITIONBYclassORDERBYscoreDESC)asrow_num FROMstudents; -- 计算累计总和 SELECTname, score, class, SUM(score)OVER(PARTITIONBYclassORDERBYscore)asrunning_total, AVG(score)OVER(PARTITIONBYclass)asclass_avg FROMstudents;
八、事务控制
事务是一组操作，要么全部成功，要么全部失败。
-- 开始事务 STARTTRANSACTION; -- 执行操作 UPDATEstudentsSETscore=score+10WHEREid=1; UPDATEcoursesSETcredits=credits+1WHEREcourse_id=2; -- 如果一切正常，提交事务 COMMIT; -- 如果出现问题，回滚事务 -- ROLLBACK;
事务隔离级别
MySQL支持四种事务隔离级别：
-- 查看当前隔离级别 SELECT@@TRANSACTION_ISOLATION; -- 设置隔离级别 SETSESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
1.READ UNCOMMITTED：可以读取未提交的数据（脏读）
2.READ COMMITTED：只能读取已提交的数据
3.REPEATABLE READ：MySQL默认级别，确保同一事务中多次读取结果一致
4.SERIALIZABLE：最高级别，完全串行执行
保存点
在长事务中，可以设置保存点，回滚到特定位置：
STARTTRANSACTION; UPDATEstudentsSETscore=score+5WHEREid=1; SAVEPOINTpoint1; UPDATEstudentsSETscore=score+10WHEREid=2; -- 如果需要，可以回滚到保存点 ROLLBACKTOSAVEPOINTpoint1; -- 继续事务 UPDATEstudentsSETscore=score+15WHEREid=3; COMMIT;
九、索引与性能优化
索引是提高查询性能的关键：
-- 创建索引 CREATEINDEX idx_student_nameONstudents(name); -- 创建复合索引 CREATEINDEX idx_class_scoreONstudents(class, score); -- 创建唯一索引 CREATEUNIQUEINDEX idx_emailONstudents(email); -- 查看表的索引 SHOWINDEXFROMstudents; -- 删除索引 DROPINDEX idx_student_nameONstudents;
索引类型
1.B-Tree索引：默认索引类型，适用于等值查询和范围查询
2.哈希索引：仅适用于等值比较，Memory引擎支持
3.全文索引：用于全文搜索
`CREATEFULLTEXT INDEX idx_fulltextONarticles(title, content);` AI写代码sql
4.空间索引：用于地理空间数据
`CREATESPATIAL INDEX idx_locationONplaces(location);` AI写代码sql
EXPLAIN分析查询
-- 分析查询执行计划 EXPLAINSELECT*FROMstudentsWHEREclass='计算机科学1班'ANDscore>80;
查询优化
-- 使用FORCE INDEX强制使用特定索引 SELECT*FROMstudents FORCE INDEX (idx_class_score) WHEREclass='计算机科学1班'ANDscore>80; -- 使用STRAIGHT_JOIN控制连接顺序 SELECTSTRAIGHT_JOIN s.name, c.course_name FROMstudents s JOINstudent_courses scONs.id=sc.student_id JOINcourses cONsc.course_id=c.course_id;
十、视图
视图是基于SQL查询的虚拟表，可以简化复杂查询：
-- 创建视图 CREATEVIEWstudent_course_viewAS SELECTs.id, s.name, s.class, c.course_name, c.teacher FROMstudents s JOINstudent_courses scONs.id=sc.student_id JOINcourses cONsc.course_id=c.course_id; -- 使用视图 SELECT*FROMstudent_course_viewWHEREclass='计算机科学1班'; -- 更新视图（如果基表允许） UPDATEstudent_course_viewSETname='张三丰'WHEREid=1; -- 删除视图 DROPVIEWstudent_course_view;
视图的优点
1.简化复杂查询：将复杂查询封装为视图
2.提高安全性：限制用户只能访问视图中的特定列
3.数据独立性：应用程序使用视图，底层表结构变化时不需要修改应用
十一、存储过程与函数
存储过程是一组预编译的SQL语句，可以接受参数并执行复杂操作：
-- 创建存储过程 DELIMITER// CREATEPROCEDUREupdate_student_score(INstudent_idINT,INnew_scoreFLOAT) BEGIN UPDATEstudentsSETscore=new_scoreWHEREid=student_id; -- 条件语句 IF new_score>=90THEN INSERT INTOhonor_students (student_id, honor_type) VALUES(student_id,'优秀学生'); ENDIF; END// DELIMITER ; -- 调用存储过程 CALLupdate_student_score(1,95); -- 创建函数 DELIMITER// CREATEFUNCTIONget_grade(scoreFLOAT)RETURNSCHAR(1) DETERMINISTIC BEGIN DECLAREgradeCHAR(1); IF score>=90THEN SETgrade='A'; ELSEIF score>=80THEN SETgrade='B'; ELSEIF score>=70THEN SETgrade='C'; ELSEIF score>=60THEN SETgrade='D'; ELSE SETgrade='F'; ENDIF; RETURNgrade; END// DELIMITER ; -- 使用函数 SELECTname, score, get_grade(score)asgradeFROMstudents;
存储过程的高级特性
DELIMITER// CREATEPROCEDUREprocess_new_students(INclass_nameVARCHAR(50)) BEGIN -- 声明变量 DECLAREdoneINTDEFAULTFALSE; DECLAREs_idINT; DECLAREs_nameVARCHAR(50); -- 声明游标 DECLAREstudent_cursorCURSORFOR SELECTid, nameFROMstudents WHEREclass=class_nameANDscoreISNULL; -- 声明异常处理 DECLARECONTINUE HANDLERFORNOTFOUNDSETdone=TRUE; -- 开始事务 STARTTRANSACTION; -- 打开游标 OPENstudent_cursor; -- 循环处理 read_loop: LOOP FETCHstudent_cursorINTOs_id, s_name; IF doneTHEN LEAVE read_loop; ENDIF; -- 为新生创建初始记录 INSERT INTOstudent_records (student_id, status) VALUES(s_id,'active'); ENDLOOP; -- 关闭游标 CLOSEstudent_cursor; -- 提交事务 COMMIT; END// DELIMITER ;
十二、触发器
触发器是在表上执行特定操作（INSERT、UPDATE、DELETE）时自动执行的特殊存储过程：
-- 创建触发器 DELIMITER// CREATETRIGGERafter_student_update AFTERUPDATEONstudents FOREACHROW BEGIN -- 记录成绩变化 IF OLD.score!=NEW.scoreTHEN INSERT INTOscore_changes (student_id, old_score, new_score, change_date) VALUES(NEW.id, OLD.score, NEW.score, NOW()); ENDIF; END// DELIMITER ; -- 查看触发器 SHOWTRIGGERS; -- 删除触发器 DROPTRIGGERafter_student_update;
触发器类型
1.BEFORE触发器：在操作执行前触发
2.AFTER触发器：在操作执行后触发
3.行级触发器：对每一行执行一次（FOR EACH ROW）
十三、用户管理与权限控制
MySQL提供了完善的用户管理和权限控制系统：
-- 创建用户 CREATEUSER'teacher'@'localhost'IDENTIFIEDBY'password123'; -- 授予权限 GRANTSELECT,UPDATEONschool.studentsTO'teacher'@'localhost'; -- 授予所有权限 GRANTALLPRIVILEGESONschool.*TO'admin'@'localhost'; -- 查看用户权限 SHOWGRANTSFOR'teacher'@'localhost'; -- 撤销权限 REVOKEUPDATEONschool.studentsFROM'teacher'@'localhost'; -- 删除用户 DROPUSER'teacher'@'localhost';
权限级别
MySQL的权限系统分为四个级别：
1.全局级别：适用于所有数据库
2.数据库级别：适用于特定数据库中的所有对象
3.表级别：适用于特定表
4.列级别：适用于特定列
十四、备份与恢复
数据备份是数据库管理的关键部分：
# 使用mysqldump备份数据库 mysqldump-u root-p school>school_backup.sql # 备份特定表 mysqldump-u root-p school students courses>tables_backup.sql # 恢复数据库 mysql-u root-p school
逻辑备份与物理备份
1.逻辑备份：
? 以SQL语句形式保存数据（如mysqldump）
? 优点：可跨版本、平台迁移，可选择性恢复
? 缺点：备份和恢复速度较慢，尤其是大型数据库
2.物理备份：
? 直接复制数据文件（如MySQL Enterprise Backup, Percona XtraBackup）
? 优点：备份和恢复速度快
? 缺点：通常依赖于特定MySQL版本和平台
备份策略
1.完整备份：备份整个数据库
# 完整备份 mysqldump-u root-p--all-databases > full_backup.sql
2.增量备份：仅备份自上次备份以来的变化
# 使用二进制日志进行增量备份 mysqlbinlog mysql-bin.000001>incremental_backup.sql
3.差异备份：备份自上次完整备份以来的所有变化
备份选项与参数
# 包含存储过程和函数 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 TABLEstudent_scores ( idINTNOT NULL, student_idINT, subjectVARCHAR(50), scoreINT, test_dateDATE, PRIMARY KEY(id, test_date) ) PARTITIONBYRANGE(YEAR(test_date)) ( PARTITIONp0VALUESLESS THAN (2020), PARTITIONp1VALUESLESS THAN (2021), PARTITIONp2VALUESLESS THAN (2022), PARTITIONp3VALUESLESS THAN (2023), PARTITIONp4VALUESLESS THAN MAXVALUE ); -- 创建按列表分区的表 CREATE TABLEregional_sales ( idINTNOT NULL, regionVARCHAR(50), salesDECIMAL(10,2), PRIMARY KEY(id, region) ) PARTITIONBYLIST (region) ( PARTITIONp_eastVALUESIN('东北','华东'), PARTITIONp_southVALUESIN('华南','西南'), PARTITIONp_northVALUESIN('华北','西北'), PARTITIONp_centralVALUESIN('华中') ); -- 创建按哈希分区的表 CREATE TABLEaccess_logs ( idINTNOT NULL, user_idINT, access_time DATETIME, urlVARCHAR(255), PRIMARY KEY(id, user_id) ) PARTITIONBYHASH (user_id) PARTITIONS4;
分区管理
-- 添加分区 ALTER TABLEstudent_scoresADDPARTITION( PARTITIONp5VALUESLESS THAN (2024) ); -- 删除分区 ALTER TABLEstudent_scoresDROPPARTITIONp0; -- 重组分区 ALTER TABLEstudent_scores REORGANIZEPARTITIONp1, p2INTO( PARTITIONp1_2VALUESLESS THAN (2022) ); -- 查看表分区信息 SELECT*FROMinformation_schema.partitions WHEREtable_name='student_scores';
分区优势
1.提高查询性能：查询可以只扫描相关分区
2.简化数据管理：可以单独备份、恢复或优化特定分区
3.提高可用性：不同分区可以存储在不同磁盘上
十六、复制与高可用
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
设置复制:
-- 在主服务器上创建复制用户 CREATEUSER'repl'@'%'IDENTIFIEDBY'password'; GRANTREPLICATION SLAVEON*.*TO'repl'@'%'; -- 获取主服务器状态 SHOWMASTER STATUS; -- 在从服务器上配置复制 CHANGE MASTERTO MASTER_HOST='master_host_ip', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=123; -- 启动从服务器复制 STARTSLAVE; -- 检查从服务器状态 SHOWSLAVE STATUSG
复制类型
1.异步复制：默认模式，主服务器不等待从服务器确认
2.半同步复制：主服务器等待至少一个从服务器确认接收事件
3.组复制：多主模式，提供高可用性和自动故障转移
高可用解决方案
1.MySQL Group Replication：内置的高可用解决方案
2.MySQL InnoDB Cluster：结合MySQL Shell、Group Replication和MySQL Router
3.第三方工具：如Percona XtraDB Cluster、MariaDB Galera Cluster
十七、性能调优
服务器配置优化
# my.cnf 关键参数 [mysqld] # 缓冲池大小（通常为系统内存的50-80%） innodb_buffer_pool_size=4G # 日志文件大小 innodb_log_file_size=256M # 并发连接数 max_connections=500 # 查询缓存（MySQL8.0已移除） query_cache_size=64M query_cache_type=1 # 临时表大小 tmp_table_size=64M max_heap_table_size=64M
查询优化
1.使用EXPLAIN分析查询：
`EXPLAINSELECT*FROMstudentsWHEREclass='计算机科学1班';` AI写代码sql
2.优化索引：
-- 添加适当的索引 CREATEINDEX idx_classONstudents(class); -- 使用复合索引 CREATEINDEX idx_class_scoreONstudents(class, score);
3.重写查询：
-- 优化前 SELECT*FROMstudentsWHEREYEAR(created_at)=2022; -- 优化后（可以使用索引） SELECT*FROMstudentsWHEREcreated_atBETWEEN'2022-01-01'AND'2022-12-31';
4.限制结果集大小：
-- 使用LIMIT避免返回过多数据 SELECT*FROMlogsORDERBYcreated_atDESCLIMIT1000;
表优化
-- 分析表 ANALYZETABLEstudents; -- 优化表（重建表和索引） OPTIMIZETABLEstudents; -- 检查表是否损坏 CHECKTABLEstudents; -- 修复表 REPAIRTABLEstudents;
监控工具
1.MySQL性能模式（Performance Schema）：
-- 启用性能模式 SETGLOBALperformance_schema=ON; -- 查询等待事件 SELECT*FROMperformance_schema.events_waits_summary_global_by_event_name ORDERBYSUM_TIMER_WAITDESCLIMIT10;
2.MySQL系统变量：
-- 查看系统变量 SHOWVARIABLESLIKE'innodb_buffer_pool_size'; -- 查看状态变量 SHOWSTATUSLIKE'Threads_connected';
3.慢查询日志：
# 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 TABLEuser_profiles ( idINTAUTO_INCREMENTPRIMARY KEY, user_idINT, profile JSON ); -- 插入JSON数据 INSERT INTOuser_profiles (user_id, profile)VALUES (1,'{"name": "张三", "age": 25, "interests": ["编程", "音乐", "旅行"]}'); -- 更新JSON数据 UPDATEuser_profiles SETprofile=JSON_SET(profile,'$.age',26,'$.email','zhangsan@example.com') WHEREuser_id=1; -- 查询JSON数据 SELECT user_id, JSON_EXTRACT(profile,'$.name')ASname, JSON_EXTRACT(profile,'$.age')ASage FROMuser_profiles; -- 使用简化语法（->） SELECT user_id, profile->'$.name'ASname, profile->'$.age'ASage FROMuser_profiles; -- 使用->>运算符（去除引号） SELECT user_id, profile->>'$.name'ASname, profile->>'$.age'ASage FROMuser_profiles;
JSON函数
-- 创建JSON对象 SELECTJSON_OBJECT('name','李四','age',30,'city','北京'); -- 创建JSON数组 SELECTJSON_ARRAY('编程','音乐','旅行'); -- 合并JSON文档 SELECTJSON_MERGE_PRESERVE('{"name": "张三"}','{"age": 25}'); -- 查找JSON数组中的元素 SELECTJSON_CONTAINS(profile->'$.interests','"音乐"') FROMuser_profiles; -- 提取JSON数组中的所有元素 SELECTJSON_EXTRACT(profile,'$.interests[*]') FROMuser_profiles; -- 获取JSON对象中的所有键 SELECTJSON_KEYS(profile) FROMuser_profiles;
十九、全文搜索
MySQL支持全文搜索功能，允许对文本内容进行高效搜索：
-- 创建带全文索引的表 CREATE TABLEarticles ( idINTAUTO_INCREMENTPRIMARY KEY, titleVARCHAR(200), content TEXT, FULLTEXT INDEX idx_content (title, content) ) ENGINE=InnoDB; -- 插入数据 INSERT INTOarticles (title, content)VALUES ('MySQL基础教程','本文介绍MySQL的基本操作，包括增删改查等内容...'), ('SQL高级技巧','本文详细讲解SQL的高级用法，包括子查询、存储过程等...'), ('数据库优化指南','如何优化MySQL数据库性能，包括索引优化、查询优化等...'); -- 自然语言模式搜索 SELECT*FROMarticles WHEREMATCH(title, content) AGAINST('MySQL 基础'INNATURALLANGUAGEMODE); -- 布尔模式搜索 SELECT*FROMarticles WHEREMATCH(title, content) AGAINST('+MySQL -高级'INBOOLEANMODE); -- 带有扩展查询的搜索 SELECT*FROMarticles WHEREMATCH(title, content) AGAINST('数据库'WITHQUERY EXPANSION);
全文搜索操作符（布尔模式）
?+：必须包含该词
?-：必须不包含该词
?>：增加相关性权重
?<：减少相关性权重
?*：通配符
?""：精确短语匹配
-- 使用布尔模式操作符 SELECT*FROMarticles WHEREMATCH(title, content) AGAINST('+MySQL +"基础教程"'INBOOLEANMODE);
二十、常见问题与最佳实践
安全最佳实践
1.定期更新MySQL：保持最新安全补丁
2.使用强密码：为所有用户设置强密码
3.最小权限原则：只授予用户必要的权限
4.加密连接：使用SSL/TLS加密客户端与服务器之间的通信
5.审计日志：启用审计日志记录关键操作
-- 启用SSL连接 CREATEUSER'secure_user'@'%'IDENTIFIEDBY'password'REQUIRE SSL; -- 设置密码策略 SETGLOBALvalidate_password.policy=STRONG;
性能最佳实践
1.适当使用索引：为常用查询条件创建索引，但避免过多索引
2. **避免SELECT ***：只查询需要的列
3.使用批量操作：批量插入比单条插入更高效
4.定期维护表：分析和优化表
5.合理设置缓存：根据系统内存配置适当的缓冲池大小
常见错误与解决方案
1.Too many connections：
-- 增加最大连接数 SETGLOBALmax_connections=500; -- 查看当前连接 SHOWPROCESSLIST; -- 终止空闲连接 KILL connection_id;
2.Deadlock found：
-- 查看最后一个死锁信息 SHOWENGINE INNODB STATUS; -- 调整事务隔离级别 SETSESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3.Table is full：
-- 增加临时表大小 SETGLOBALtmp_table_size=128M; SETGLOBALmax_heap_table_size=128M;
总结
本文通过理论与实例详细介绍了MySQL数据库的基础知识和高级特性：
1.基础操作：数据库和表的创建、修改和删除
2.CRUD操作：数据的增删改查
3.高级查询：连接、子查询、窗口函数和CTE
4.数据库管理：事务控制、索引优化、备份恢复
5.高级特性：存储过程、触发器、视图、分区表
6.性能与安全：性能调优、用户管理、安全最佳实践
熟练掌握这些知识点，可以帮助你构建高效、安全、可靠的MySQL数据库应用。随着对MySQL的深入学习和实践，你将能够解决更复杂的数据管理和分析需求，为应用程序提供强大的数据支持。
记住，在生产环境中使用MySQL时，务必关注数据安全性、性能优化和备份恢复等关键问题，确保数据库系统的稳定和可靠运行。持续学习和实践是成为MySQL专家的关键。
链接：https://lethehong.blog.csdn.net/article/details/147492039?spm=1001.2014.3001.5502
-
数据库
+关注
关注
7文章
3947浏览量
66788 -
开源
+关注
关注
3文章
3779浏览量
44121 -
MySQL
+关注
关注
1文章
875浏览量
28219
原文标题：MySQL教程：颠覆你对MySQL的认知（全）
文章出处：【微信号：magedu-Linux，微信公众号：马哥Linux运维】欢迎添加关注！文章转载请注明出处。
发布评论请先 登录
【理论】电磁兼容理论基础（55页PPT）
CT的理论基础及理论发展
工作环境准备及数据分析建模理论基础的学习课件免费下载
优化MySQL的理论基础是什么？
工商网监
评论