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','[email protected]') 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文章
3900瀏覽量
65745 -
開源
+關注
關注
3文章
3582瀏覽量
43451 -
MySQL
+關注
關注
1文章
849瀏覽量
27506
原文標題：MySQL教程：顛覆你對MySQL的認知（全）
文章出處：【微信號：magedu-Linux，微信公眾號：馬哥Linux運維】歡迎添加關注！文章轉載請注明出處。
發布評論請先 登錄
【理論】電磁兼容理論基礎（55頁PPT）
CT的理論基礎及理論發展
工作環境準備及數據分析建模理論基礎的學習課件免費下載
優化MySQL的理論基礎是什么？
工商網監
評論