一、MySQL簡介與理論基礎(chǔ)

MySQL是世界上最流行的開源關(guān)系型數(shù)據(jù)庫管理系統(tǒng)之一，廣泛應(yīng)用于網(wǎng)站、應(yīng)用程序和企業(yè)級系統(tǒng)。它采用客戶端/服務(wù)器架構(gòu)，支持多用戶環(huán)境，并基于SQL（結(jié)構(gòu)化查詢語言）標(biāo)準(zhǔn)。

關(guān)系型數(shù)據(jù)庫核心概念

1.關(guān)系模型：數(shù)據(jù)以表格（二維表）形式存儲，表之間通過關(guān)系連接

2.ACID特性：

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

?一致性(Consistency)：事務(wù)執(zhí)行前后，數(shù)據(jù)庫從一個一致狀態(tài)變到另一個一致狀態(tài)

?隔離性(Isolation)：并發(fā)執(zhí)行的事務(wù)之間不會互相影響

?持久性(Durability)：事務(wù)一旦提交，其結(jié)果將永久保存

MySQL架構(gòu)

MySQL采用多層架構(gòu)設(shè)計(jì)：

1.連接層：處理客戶端連接請求

2.服務(wù)層：包括查詢解析、優(yōu)化和緩存

3.存儲引擎層：負(fù)責(zé)數(shù)據(jù)的存儲和提取

4.文件系統(tǒng)層：將數(shù)據(jù)持久化到磁盤

存儲引擎

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

?InnoDB：默認(rèn)存儲引擎，支持事務(wù)、外鍵和行級鎖

`CREATE TABLEexample (idINT) ENGINE=InnoDB;`

?MyISAM：適合讀密集型應(yīng)用，支持全文索引

`CREATE TABLElogs (idINT, message TEXT) ENGINE=MyISAM;`

?Memory：將數(shù)據(jù)存儲在內(nèi)存中，速度極快但不持久

`CREATE TABLEtemp_data (idINT) ENGINE=MEMORY;`

?Archive：適合存儲和檢索大量很少被查詢的歷史數(shù)據(jù)

`CREATE TABLEold_logs (idINT, log_text TEXT) ENGINE=ARCHIVE;`

MySQL數(shù)據(jù)類型

1.數(shù)值類型：

? INT：整數(shù)類型，4字節(jié)

? TINYINT：小整數(shù)，1字節(jié)

? BIGINT：大整數(shù)，8字節(jié)

? FLOAT/DOUBLE：浮點(diǎn)數(shù)

? DECIMAL：精確小數(shù)

2.字符串類型：

? CHAR(n)：固定長度字符串

? VARCHAR(n)：可變長度字符串

? TEXT：長文本

3.日期和時間類型：

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

? TIME：時間，格式'HHSS'

? DATETIME：日期和時間，格式'YYYY-MM-DD HHSS'

? TIMESTAMP：時間戳

4.其他類型：

? ENUM：枚舉類型

? SET：集合類型

? BLOB：二進(jìn)制大對象

二、數(shù)據(jù)庫和表的基本操作

數(shù)據(jù)庫操作語法詳解

-- 創(chuàng)建數(shù)據(jù)庫
CREATEDATABASE [IFNOTEXISTS] mydb
[CHARACTER SETcharset_name]
[COLLATEcollation_name];

-- 查看所有數(shù)據(jù)庫
SHOWDATABASES;

-- 使用數(shù)據(jù)庫
USE mydb;

-- 刪除數(shù)據(jù)庫
DROPDATABASE [IFEXISTS] mydb;

表操作語法詳解

-- 創(chuàng)建表
CREATE TABLEstudents (
  idINTAUTO_INCREMENTPRIMARY KEY, -- 主鍵，自動遞增
  nameVARCHAR(50)NOT NULL,     -- NOT NULL約束
  ageINTCHECK(age>0),      -- CHECK約束
  gender ENUM('男','女'),      -- 枚舉類型
  classVARCHAR(20),
  scoreFLOATDEFAULT0,       -- 默認(rèn)值
  created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP, -- 自動記錄創(chuàng)建時間
  INDEX idx_class (class)      -- 索引
) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;

-- 查看表結(jié)構(gòu)
DESCRIBEstudents;
-- 或
SHOWCOLUMNSFROMstudents;

-- 修改表結(jié)構(gòu)
ALTER TABLEstudentsADDCOLUMNemailVARCHAR(100);
ALTER TABLEstudents MODIFYCOLUMNnameVARCHAR(100);
ALTER TABLEstudentsDROPCOLUMNemail;

-- 刪除表
DROPTABLE[IFEXISTS] students;

三、增：INSERT 插入數(shù)據(jù)

插入語法詳解

基本語法：

INSERT INTOtable_name [(column1, column2, ...)]
VALUES(value1, value2, ...)[, (value1, value2, ...), ...];

插入單條數(shù)據(jù)

INSERT INTOstudents (name, age, gender, class, score)
VALUES('張三',20,'男','計(jì)算機(jī)科學(xué)1班',89.5);

插入多條數(shù)據(jù)

INSERT INTOstudents (name, age, gender, class, score)VALUES
('李四',19,'男','計(jì)算機(jī)科學(xué)1班',76.0),
('王五',21,'男','計(jì)算機(jī)科學(xué)2班',92.5),
('趙六',20,'女','計(jì)算機(jī)科學(xué)2班',85.0),
('錢七',22,'女','計(jì)算機(jī)科學(xué)1班',79.5);

INSERT的高級用法

-- 插入或更新（如果主鍵存在則更新）
INSERT INTOstudents (id, name, score)
VALUES(1,'張三',95)
ONDUPLICATE KEYUPDATEscore=95;

-- 從其他表插入數(shù)據(jù)
INSERT INTOstudents_backup
SELECT*FROMstudentsWHEREclass='計(jì)算機(jī)科學(xué)1班';

-- 忽略錯誤繼續(xù)執(zhí)行
INSERTIGNOREINTOstudents (id, name, age)
VALUES(1,'張三',20);

實(shí)際應(yīng)用場景

當(dāng)新學(xué)生入學(xué)時，需要將學(xué)生信息錄入系統(tǒng)：

-- 新學(xué)期開始，添加一批新生
INSERT INTOstudents (name, age, gender, class, score)VALUES
('劉備',18,'男','計(jì)算機(jī)1班',NULL), -- 新生還沒有成績
('關(guān)羽',19,'男','計(jì)算機(jī)1班',NULL),
('張飛',18,'男','計(jì)算機(jī)1班',NULL);

四、查：SELECT 查詢數(shù)據(jù)

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子句詳解

-- 查詢計(jì)算機(jī)科學(xué)1班的學(xué)生
SELECT*FROMstudentsWHEREclass='計(jì)算機(jī)科學(xué)1班';

-- 查詢成績大于80的學(xué)生
SELECTname, scoreFROMstudentsWHEREscore>80;

-- 查詢年齡在19到21歲之間的學(xué)生
SELECT*FROMstudentsWHEREageBETWEEN19AND21;

-- 復(fù)合條件：AND, OR, NOT
SELECT*FROMstudents
WHERE(class='計(jì)算機(jī)科學(xué)1班'ORclass='計(jì)算機(jī)科學(xué)2班')
ANDscore>=80
ANDNOTgender='女';

-- NULL值處理
SELECT*FROMstudentsWHEREscoreISNULL;
SELECT*FROMstudentsWHEREscoreISNOT NULL;

排序與ORDER BY子句

-- 按成績降序排列
SELECT*FROMstudentsORDERBYscoreDESC;

-- 先按班級升序，再按成績降序
SELECT*FROMstudentsORDERBYclassASC, scoreDESC;

-- 按字段位置排序（不推薦，但需了解）
SELECTname, age, scoreFROMstudentsORDERBY3DESC;-- 按第3列(score)排序

分組和聚合函數(shù)

-- 計(jì)算每個班級的平均分
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 -- 標(biāo)準(zhǔn)差
FROMstudents
WHEREscoreISNOT NULL
GROUPBYclass;

-- HAVING子句（對分組結(jié)果進(jìn)行篩選）
SELECTclass,AVG(score)asavg_score
FROMstudents
GROUPBYclass
HAVINGavg_score>80;

限制結(jié)果數(shù)量

-- 查詢前3名學(xué)生
SELECT*FROMstudentsORDERBYscoreDESCLIMIT3;

-- 分頁查詢：每頁5條，查詢第2頁
SELECT*FROMstudents LIMIT5,5; -- 偏移量5，返回5條
-- 或使用更現(xiàn)代的語法
SELECT*FROMstudents LIMIT5OFFSET5;

模糊查詢與LIKE操作符

-- 查詢名字中包含"張"的學(xué)生
SELECT*FROMstudentsWHEREnameLIKE'%張%';

-- 查詢以"計(jì)算機(jī)"開頭的班級
SELECTDISTINCTclassFROMstudentsWHEREclassLIKE'計(jì)算機(jī)%';

-- 通配符說明
-- %：匹配任意數(shù)量的字符
-- _：匹配單個字符
SELECT*FROMstudentsWHEREnameLIKE'張_'; -- 匹配"張"后跟一個字符的名字

正則表達(dá)式查詢

-- 查詢名字中包含數(shù)字的學(xué)生
SELECT*FROMstudentsWHEREname REGEXP'[0-9]';

-- 查詢名字以"張"或"王"開頭的學(xué)生
SELECT*FROMstudentsWHEREname REGEXP'^[張王]';

實(shí)際應(yīng)用場景

期末考試后，教師需要統(tǒng)計(jì)班級情況：

-- 查詢每個班級的及格率
SELECT
  class,
 COUNT(*)astotal_students,
 SUM(CASEWHENscore>=60THEN1ELSE0END)aspassed_students,
  ROUND(SUM(CASEWHENscore>=60THEN1ELSE0END)/COUNT(*)*100,2)aspass_rate
FROMstudents
WHEREscoreISNOT NULL
GROUPBYclass;

五、改：UPDATE 更新數(shù)據(jù)

UPDATE語法詳解

基本語法：

UPDATEtable_name
SETcolumn1=value1, column2=value2, ...
[WHEREcondition]
[ORDERBY...]
[LIMIT row_count];

更新單個記錄

-- 更新張三的成績
UPDATEstudentsSETscore=92.0WHEREname='張三';

更新多個字段

-- 李四轉(zhuǎn)班并更新信息
UPDATEstudents
SETclass='計(jì)算機(jī)科學(xué)2班', age=20
WHEREname='李四';

條件更新與表達(dá)式

-- 給所有90分以上的學(xué)生加5分獎勵（但不超過100分）
UPDATEstudents
SETscore=LEAST(score+5,100)
WHEREscore>90;

-- 所有學(xué)生年齡增加1歲
UPDATEstudentsSETage=age+1;

-- 使用CASE表達(dá)式進(jìn)行條件更新
UPDATEstudents
SETscore=CASE
 WHENscore<60THEN?score?+5-- 不及格加5分
? ??WHEN?score?>=60ANDscore<90THEN?score?+3-- 良好加3分
? ??ELSE?score ?-- 優(yōu)秀不變
END;

多表更新

-- 基于另一個表的數(shù)據(jù)更新當(dāng)前表
UPDATEstudents s
JOINstudent_extra_info seiONs.id=sei.student_id
SETs.email=sei.email, s.phone=sei.phone
WHEREsei.update_flag=1;

實(shí)際應(yīng)用場景

期中考試后，某些學(xué)生參加了補(bǔ)考，需要更新成績：

-- 批量更新補(bǔ)考成績
UPDATEstudents
SETscore=CASE
 WHENname='李四'THEN82.5
 WHENname='錢七'THEN88.0
 ELSEscore
END
WHEREnameIN('李四','錢七');

六、刪：DELETE 刪除數(shù)據(jù)

DELETE語法詳解

基本語法：

DELETEFROMtable_name
[WHEREcondition]
[ORDERBY...]
[LIMIT row_count];

刪除特定記錄

-- 刪除指定學(xué)生
DELETEFROMstudentsWHEREname='趙六';

條件刪除

-- 刪除成績不及格的學(xué)生
DELETEFROMstudentsWHEREscore

	

	限制刪除數(shù)量

	
-- 刪除成績最低的3名學(xué)生
DELETEFROMstudents
ORDERBYscoreASC
LIMIT3;


	

	多表刪除

	
-- 刪除已經(jīng)在畢業(yè)生表中的學(xué)生
DELETEsFROMstudents s
JOINgraduated_students gONs.id=g.student_id;


	

	清空表

	
-- 刪除表中所有數(shù)據(jù)（逐行刪除，可回滾）
DELETEFROMstudents;

-- 或者（直接刪除表并重建，效率更高，不可回滾）
TRUNCATETABLEstudents;


	

	DELETE與TRUNCATE的區(qū)別

	1.事務(wù)支持：DELETE支持事務(wù)回滾，TRUNCATE不支持

	2.速度：TRUNCATE通常更快

	3.自增值：TRUNCATE會重置AUTO_INCREMENT計(jì)數(shù)器

	4.觸發(fā)器：DELETE會觸發(fā)DELETE觸發(fā)器，TRUNCATE不會

	實(shí)際應(yīng)用場景

	學(xué)期結(jié)束，需要清理臨時學(xué)生數(shù)據(jù)：

	
-- 刪除已經(jīng)畢業(yè)的學(xué)生
DELETEFROMstudentsWHEREidIN(
 SELECTidFROMgraduated_students
);

-- 假設(shè)要刪除舊學(xué)期的數(shù)據(jù)并保留新學(xué)期數(shù)據(jù)
-- 創(chuàng)建備份
CREATE TABLEstudents_new_semesterAS
SELECT*FROMstudentsWHEREentry_year=2025;

-- 清空原表
TRUNCATETABLEstudents;

-- 將新數(shù)據(jù)插回原表
INSERT INTOstudents
SELECT*FROMstudents_new_semester;

-- 刪除臨時表
DROPTABLEstudents_new_semester;


	

	七、高級查詢技巧

	連接查詢詳解

	MySQL支持多種連接類型：

	? INNER JOIN（內(nèi)連接）：返回兩表中匹配的行

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

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

	? CROSS JOIN（交叉連接）：返回兩表的笛卡爾積

	假設(shè)我們有一個課程表：

	
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)
);

-- 插入一些數(shù)據(jù)
INSERT INTOcourses (course_name, teacher, credits)VALUES
('數(shù)據(jù)庫原理','陳教授',3),
('計(jì)算機(jī)網(wǎng)絡(luò)','王教授',4),
('操作系統(tǒng)','李教授',4);

INSERT INTOstudent_courses (student_id, course_id)VALUES
(1,1), (1,2), (2,1), (3,3), (4,2), (5,3);


	

	連接查詢示例：

	
-- 內(nèi)連接：查詢學(xué)生及其選修的課程
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;

-- 左連接：查詢所有學(xué)生，包括未選課的
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;

-- 自連接：查找同班同學(xué)
SELECTs1.name, s2.nameasclassmate
FROMstudents s1
JOINstudents s2ONs1.class=s2.classANDs1.id!=s2.id
ORDERBYs1.class, s1.name;


	

	子查詢詳解

	子查詢是嵌套在另一個查詢中的SELECT語句，可以用在：

	? SELECT子句

	? FROM子句

	? WHERE子句

	? HAVING子句

	
-- WHERE子句中的子查詢
-- 查詢選修了"數(shù)據(jù)庫原理"課程的學(xué)生
SELECTname, age, class
FROMstudents
WHEREidIN(
 SELECTstudent_id
 FROMstudent_courses
 WHEREcourse_id=(SELECTcourse_idFROMcoursesWHEREcourse_name='數(shù)據(jù)庫原理')
);

-- FROM子句中的子查詢（派生表）
-- 查詢每個班級的平均分，并與學(xué)生個人分?jǐn)?shù)比較
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子句中的子查詢（標(biāo)量子查詢）
-- 查詢每個學(xué)生選修的課程數(shù)量
SELECTs.name, s.class,
  (SELECTCOUNT(*)FROMstudent_coursesWHEREstudent_id=s.id)AScourse_count
FROMstudents s
ORDERBYcourse_countDESC;

-- EXISTS子查詢
-- 查詢至少選修了一門課程的學(xué)生
SELECTname, class
FROMstudents s
WHEREEXISTS(
 SELECT1FROMstudent_courses
 WHEREstudent_id=s.id
);




	

	公用表表達(dá)式(CTE)

	CTE是一種臨時結(jié)果集，可以在單個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;


	

	窗口函數(shù)

	窗口函數(shù)對一組行執(zhí)行計(jì)算，返回每行的值：

	
-- 計(jì)算每個班級中學(xué)生的排名
SELECTname, score, class,
   RANK()OVER(PARTITIONBYclassORDERBYscoreDESC)asclass_rank,
   DENSE_RANK()OVER(PARTITIONBYclassORDERBYscoreDESC)asdense_rank,
   ROW_NUMBER()OVER(PARTITIONBYclassORDERBYscoreDESC)asrow_num
FROMstudents;

-- 計(jì)算累計(jì)總和
SELECTname, score, class,
   SUM(score)OVER(PARTITIONBYclassORDERBYscore)asrunning_total,
   AVG(score)OVER(PARTITIONBYclass)asclass_avg
FROMstudents;


	

	八、事務(wù)控制

	事務(wù)是一組操作，要么全部成功，要么全部失敗。

	
-- 開始事務(wù)
STARTTRANSACTION;

-- 執(zhí)行操作
UPDATEstudentsSETscore=score+10WHEREid=1;
UPDATEcoursesSETcredits=credits+1WHEREcourse_id=2;

-- 如果一切正常，提交事務(wù)
COMMIT;

-- 如果出現(xiàn)問題，回滾事務(wù)
-- ROLLBACK;


	

	事務(wù)隔離級別

	MySQL支持四種事務(wù)隔離級別：

	
-- 查看當(dāng)前隔離級別
SELECT@@TRANSACTION_ISOLATION;

-- 設(shè)置隔離級別
SETSESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;


	

	1.READ UNCOMMITTED：可以讀取未提交的數(shù)據(jù)（臟讀）

	2.READ COMMITTED：只能讀取已提交的數(shù)據(jù)

	3.REPEATABLE READ：MySQL默認(rèn)級別，確保同一事務(wù)中多次讀取結(jié)果一致

	4.SERIALIZABLE：最高級別，完全串行執(zhí)行

	保存點(diǎn)

	在長事務(wù)中，可以設(shè)置保存點(diǎn)，回滾到特定位置：

	
STARTTRANSACTION;
UPDATEstudentsSETscore=score+5WHEREid=1;

SAVEPOINTpoint1;
UPDATEstudentsSETscore=score+10WHEREid=2;

-- 如果需要，可以回滾到保存點(diǎn)
ROLLBACKTOSAVEPOINTpoint1;

-- 繼續(xù)事務(wù)
UPDATEstudentsSETscore=score+15WHEREid=3;
COMMIT;


	

	九、索引與性能優(yōu)化

	索引是提高查詢性能的關(guān)鍵：

	
-- 創(chuàng)建索引
CREATEINDEX idx_student_nameONstudents(name);

-- 創(chuàng)建復(fù)合索引
CREATEINDEX idx_class_scoreONstudents(class, score);

-- 創(chuàng)建唯一索引
CREATEUNIQUEINDEX idx_emailONstudents(email);

-- 查看表的索引
SHOWINDEXFROMstudents;

-- 刪除索引
DROPINDEX idx_student_nameONstudents;


	

	索引類型

	1.B-Tree索引：默認(rèn)索引類型，適用于等值查詢和范圍查詢

	2.哈希索引：僅適用于等值比較，Memory引擎支持

	3.全文索引：用于全文搜索
`CREATEFULLTEXT INDEX idx_fulltextONarticles(title, content);`

AI寫代碼sql

	

	4.空間索引：用于地理空間數(shù)據(jù)
`CREATESPATIAL INDEX idx_locationONplaces(location);`


AI寫代碼sql

	

	EXPLAIN分析查詢

	
-- 分析查詢執(zhí)行計(jì)劃
EXPLAINSELECT*FROMstudentsWHEREclass='計(jì)算機(jī)科學(xué)1班'ANDscore>80;


	

	查詢優(yōu)化

	
-- 使用FORCE INDEX強(qiáng)制使用特定索引
SELECT*FROMstudents FORCE INDEX (idx_class_score)
WHEREclass='計(jì)算機(jī)科學(xué)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查詢的虛擬表，可以簡化復(fù)雜查詢：

	
-- 創(chuàng)建視圖
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='計(jì)算機(jī)科學(xué)1班';

-- 更新視圖（如果基表允許）
UPDATEstudent_course_viewSETname='張三豐'WHEREid=1;

-- 刪除視圖
DROPVIEWstudent_course_view;


	

	視圖的優(yōu)點(diǎn)

	1.簡化復(fù)雜查詢：將復(fù)雜查詢封裝為視圖

	2.提高安全性：限制用戶只能訪問視圖中的特定列

	3.數(shù)據(jù)獨(dú)立性：應(yīng)用程序使用視圖，底層表結(jié)構(gòu)變化時不需要修改應(yīng)用

	十一、存儲過程與函數(shù)

	存儲過程是一組預(yù)編譯的SQL語句，可以接受參數(shù)并執(zhí)行復(fù)雜操作：

	
-- 創(chuàng)建存儲過程
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,'優(yōu)秀學(xué)生');
 ENDIF;
END//
DELIMITER ;

-- 調(diào)用存儲過程
CALLupdate_student_score(1,95);

-- 創(chuàng)建函數(shù)
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 ;

-- 使用函數(shù)
SELECTname, score, get_grade(score)asgradeFROMstudents;


	

	存儲過程的高級特性

	
DELIMITER//
CREATEPROCEDUREprocess_new_students(INclass_nameVARCHAR(50))
BEGIN
 -- 聲明變量
 DECLAREdoneINTDEFAULTFALSE;
 DECLAREs_idINT;
 DECLAREs_nameVARCHAR(50);
 
 -- 聲明游標(biāo)
 DECLAREstudent_cursorCURSORFOR
   SELECTid, nameFROMstudents
   WHEREclass=class_nameANDscoreISNULL;
 
 -- 聲明異常處理
 DECLARECONTINUE HANDLERFORNOTFOUNDSETdone=TRUE;
 
 -- 開始事務(wù)
 STARTTRANSACTION;
 
 -- 打開游標(biāo)
 OPENstudent_cursor;
 
 -- 循環(huán)處理
  read_loop: LOOP
   FETCHstudent_cursorINTOs_id, s_name;
    IF doneTHEN
      LEAVE read_loop;
   ENDIF;
   
   -- 為新生創(chuàng)建初始記錄
   INSERT INTOstudent_records (student_id, status)
   VALUES(s_id,'active');
 ENDLOOP;
 
 -- 關(guān)閉游標(biāo)
 CLOSEstudent_cursor;
 
 -- 提交事務(wù)
 COMMIT;
END//
DELIMITER ;


	

	十二、觸發(fā)器

	觸發(fā)器是在表上執(zhí)行特定操作（INSERT、UPDATE、DELETE）時自動執(zhí)行的特殊存儲過程：

	
-- 創(chuàng)建觸發(fā)器
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 ;

-- 查看觸發(fā)器
SHOWTRIGGERS;

-- 刪除觸發(fā)器
DROPTRIGGERafter_student_update;


	

	觸發(fā)器類型

	1.BEFORE觸發(fā)器：在操作執(zhí)行前觸發(fā)

	2.AFTER觸發(fā)器：在操作執(zhí)行后觸發(fā)

	3.行級觸發(fā)器：對每一行執(zhí)行一次（FOR EACH ROW）

	十三、用戶管理與權(quán)限控制

	MySQL提供了完善的用戶管理和權(quán)限控制系統(tǒng)：

	
-- 創(chuàng)建用戶
CREATEUSER'teacher'@'localhost'IDENTIFIEDBY'password123';

-- 授予權(quán)限
GRANTSELECT,UPDATEONschool.studentsTO'teacher'@'localhost';

-- 授予所有權(quán)限
GRANTALLPRIVILEGESONschool.*TO'admin'@'localhost';

-- 查看用戶權(quán)限
SHOWGRANTSFOR'teacher'@'localhost';

-- 撤銷權(quán)限
REVOKEUPDATEONschool.studentsFROM'teacher'@'localhost';

-- 刪除用戶
DROPUSER'teacher'@'localhost';


	

	權(quán)限級別

	MySQL的權(quán)限系統(tǒng)分為四個級別：

	1.全局級別：適用于所有數(shù)據(jù)庫

	2.數(shù)據(jù)庫級別：適用于特定數(shù)據(jù)庫中的所有對象

	3.表級別：適用于特定表

	4.列級別：適用于特定列

	十四、備份與恢復(fù)

	數(shù)據(jù)備份是數(shù)據(jù)庫管理的關(guān)鍵部分：

	
# 使用mysqldump備份數(shù)據(jù)庫
mysqldump-u root-p school>school_backup.sql

# 備份特定表
mysqldump-u root-p school students courses>tables_backup.sql

# 恢復(fù)數(shù)據(jù)庫
mysql-u root-p school

	

	邏輯備份與物理備份

	1.邏輯備份：

	? 以SQL語句形式保存數(shù)據(jù)（如mysqldump）

	? 優(yōu)點(diǎn)：可跨版本、平臺遷移，可選擇性恢復(fù)

	? 缺點(diǎn)：備份和恢復(fù)速度較慢，尤其是大型數(shù)據(jù)庫

	2.物理備份：

	? 直接復(fù)制數(shù)據(jù)文件（如MySQL Enterprise Backup, Percona XtraBackup）

	? 優(yōu)點(diǎn)：備份和恢復(fù)速度快

	? 缺點(diǎn)：通常依賴于特定MySQL版本和平臺

	備份策略

	1.完整備份：備份整個數(shù)據(jù)庫
# 完整備份
mysqldump-u root-p--all-databases > full_backup.sql


	

	2.增量備份：僅備份自上次備份以來的變化
# 使用二進(jìn)制日志進(jìn)行增量備份
mysqlbinlog mysql-bin.000001>incremental_backup.sql


	

	3.差異備份：備份自上次完整備份以來的所有變化

	備份選項(xiàng)與參數(shù)

	
# 包含存儲過程和函數(shù)
mysqldump -u root -p --routines school > school_with_routines.sql

# 包含觸發(fā)器
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

# 使用單個事務(wù)進(jìn)行一致性備份
mysqldump -u root -p --single-transaction school > school_consistent.sql







	

	自動備份

	使用cron作業(yè)自動執(zhí)行備份：

	
# 在crontab中添加以下內(nèi)容，每天凌晨2點(diǎn)執(zhí)行備份
0 2 * * * /usr/bin/mysqldump -u root -p'password'school > /backup/school_$(date+\%Y\%m\%d).sql







	

	備份驗(yàn)證與恢復(fù)測試

	定期測試備份的有效性：

	
# 創(chuàng)建測試數(shù)據(jù)庫
mysql -u root -p -e"CREATE DATABASE school_test;"

# 恢復(fù)備份到測試數(shù)據(jù)庫
mysql -u root -p school_test < school_backup.sql
?
# 驗(yàn)證數(shù)據(jù)
mysql -u root -p -e?"SELECT COUNT(*) FROM school_test.students;"







	

	十五、分區(qū)表

	分區(qū)表允許將大表分割成更小的、更易管理的部分：

	
-- 創(chuàng)建按范圍分區(qū)的表
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
);

-- 創(chuàng)建按列表分區(qū)的表
CREATE TABLEregional_sales (
  idINTNOT NULL,
  regionVARCHAR(50),
  salesDECIMAL(10,2),
 PRIMARY KEY(id, region)
)
PARTITIONBYLIST (region) (
 PARTITIONp_eastVALUESIN('東北','華東'),
 PARTITIONp_southVALUESIN('華南','西南'),
 PARTITIONp_northVALUESIN('華北','西北'),
 PARTITIONp_centralVALUESIN('華中')
);

-- 創(chuàng)建按哈希分區(qū)的表
CREATE TABLEaccess_logs (
  idINTNOT NULL,
  user_idINT,
  access_time DATETIME,
  urlVARCHAR(255),
 PRIMARY KEY(id, user_id)
)
PARTITIONBYHASH (user_id)
PARTITIONS4;


	

	分區(qū)管理

	
-- 添加分區(qū)
ALTER TABLEstudent_scoresADDPARTITION(
 PARTITIONp5VALUESLESS THAN (2024)
);

-- 刪除分區(qū)
ALTER TABLEstudent_scoresDROPPARTITIONp0;

-- 重組分區(qū)
ALTER TABLEstudent_scores REORGANIZEPARTITIONp1, p2INTO(
 PARTITIONp1_2VALUESLESS THAN (2022)
);

-- 查看表分區(qū)信息
SELECT*FROMinformation_schema.partitions
WHEREtable_name='student_scores';


	

	分區(qū)優(yōu)勢

	1.提高查詢性能：查詢可以只掃描相關(guān)分區(qū)

	2.簡化數(shù)據(jù)管理：可以單獨(dú)備份、恢復(fù)或優(yōu)化特定分區(qū)

	3.提高可用性：不同分區(qū)可以存儲在不同磁盤上

	十六、復(fù)制與高可用

	MySQL復(fù)制允許數(shù)據(jù)從一個MySQL數(shù)據(jù)庫服務(wù)器（主服務(wù)器）復(fù)制到一個或多個MySQL數(shù)據(jù)庫服務(wù)器（從服務(wù)器）。

	主從復(fù)制配置

	主服務(wù)器配置:

	
# my.cnf 主服務(wù)器配置
[mysqld]
server-id=1
log_bin=mysql-bin
binlog_format=ROW


	

	從服務(wù)器配置:

	
# my.cnf 從服務(wù)器配置
[mysqld]
server-id=2
relay_log=mysql-relay-bin


	

	設(shè)置復(fù)制:

	
-- 在主服務(wù)器上創(chuàng)建復(fù)制用戶
CREATEUSER'repl'@'%'IDENTIFIEDBY'password';
GRANTREPLICATION SLAVEON*.*TO'repl'@'%';

-- 獲取主服務(wù)器狀態(tài)
SHOWMASTER STATUS;

-- 在從服務(wù)器上配置復(fù)制
CHANGE MASTERTO
  MASTER_HOST='master_host_ip',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=123;

-- 啟動從服務(wù)器復(fù)制
STARTSLAVE;

-- 檢查從服務(wù)器狀態(tài)
SHOWSLAVE STATUSG


	

	復(fù)制類型

	1.異步復(fù)制：默認(rèn)模式，主服務(wù)器不等待從服務(wù)器確認(rèn)

	2.半同步復(fù)制：主服務(wù)器等待至少一個從服務(wù)器確認(rèn)接收事件

	3.組復(fù)制：多主模式，提供高可用性和自動故障轉(zhuǎn)移

	高可用解決方案

	1.MySQL Group Replication：內(nèi)置的高可用解決方案

	2.MySQL InnoDB Cluster：結(jié)合MySQL Shell、Group Replication和MySQL Router

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

	十七、性能調(diào)優(yōu)

	服務(wù)器配置優(yōu)化

	
# my.cnf 關(guān)鍵參數(shù)
[mysqld]
# 緩沖池大?。ㄍǔ橄到y(tǒng)內(nèi)存的50-80%）
innodb_buffer_pool_size=4G

# 日志文件大小
innodb_log_file_size=256M

# 并發(fā)連接數(shù)
max_connections=500

# 查詢緩存（MySQL8.0已移除）
query_cache_size=64M
query_cache_type=1

# 臨時表大小
tmp_table_size=64M
max_heap_table_size=64M


	

	查詢優(yōu)化

	1.使用EXPLAIN分析查詢：
`EXPLAINSELECT*FROMstudentsWHEREclass='計(jì)算機(jī)科學(xué)1班';`


AI寫代碼sql

	

	2.優(yōu)化索引：
-- 添加適當(dāng)?shù)乃饕?CREATEINDEX idx_classONstudents(class);

-- 使用復(fù)合索引
CREATEINDEX idx_class_scoreONstudents(class, score);


	

	3.重寫查詢：
-- 優(yōu)化前
SELECT*FROMstudentsWHEREYEAR(created_at)=2022;

-- 優(yōu)化后（可以使用索引）
SELECT*FROMstudentsWHEREcreated_atBETWEEN'2022-01-01'AND'2022-12-31';


	

	4.限制結(jié)果集大小：
-- 使用LIMIT避免返回過多數(shù)據(jù)
SELECT*FROMlogsORDERBYcreated_atDESCLIMIT1000;


	

	表優(yōu)化

	
-- 分析表
ANALYZETABLEstudents;

-- 優(yōu)化表（重建表和索引）
OPTIMIZETABLEstudents;

-- 檢查表是否損壞
CHECKTABLEstudents;

-- 修復(fù)表
REPAIRTABLEstudents;


	

	監(jiān)控工具

	1.MySQL性能模式（Performance Schema）：
-- 啟用性能模式
SETGLOBALperformance_schema=ON;

-- 查詢等待事件
SELECT*FROMperformance_schema.events_waits_summary_global_by_event_name
ORDERBYSUM_TIMER_WAITDESCLIMIT10;


	

	2.MySQL系統(tǒng)變量：
-- 查看系統(tǒng)變量
SHOWVARIABLESLIKE'innodb_buffer_pool_size';

-- 查看狀態(tài)變量
SHOWSTATUSLIKE'Threads_connected';


	

	3.慢查詢?nèi)罩?/strong>：

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


	

	十八、JSON數(shù)據(jù)類型與操作

	MySQL 5.7及更高版本支持原生JSON數(shù)據(jù)類型：

	
-- 創(chuàng)建包含JSON字段的表
CREATE TABLEuser_profiles (
  idINTAUTO_INCREMENTPRIMARY KEY,
  user_idINT,
  profile JSON
);

-- 插入JSON數(shù)據(jù)
INSERT INTOuser_profiles (user_id, profile)VALUES
(1,'{"name": "張三", "age": 25, "interests": ["編程", "音樂", "旅行"]}');

-- 更新JSON數(shù)據(jù)
UPDATEuser_profiles
SETprofile=JSON_SET(profile,'$.age',26,'$.email','zhangsan@example.com')
WHEREuser_id=1;

-- 查詢JSON數(shù)據(jù)
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;

-- 使用->>運(yùn)算符（去除引號）
SELECT
  user_id,
  profile->>'$.name'ASname,
  profile->>'$.age'ASage
FROMuser_profiles;


	

	JSON函數(shù)

	
-- 創(chuàng)建JSON對象
SELECTJSON_OBJECT('name','李四','age',30,'city','北京');

-- 創(chuàng)建JSON數(shù)組
SELECTJSON_ARRAY('編程','音樂','旅行');

-- 合并JSON文檔
SELECTJSON_MERGE_PRESERVE('{"name": "張三"}','{"age": 25}');

-- 查找JSON數(shù)組中的元素
SELECTJSON_CONTAINS(profile->'$.interests','"音樂"')
FROMuser_profiles;

-- 提取JSON數(shù)組中的所有元素
SELECTJSON_EXTRACT(profile,'$.interests[*]')
FROMuser_profiles;

-- 獲取JSON對象中的所有鍵
SELECTJSON_KEYS(profile)
FROMuser_profiles;


	

	十九、全文搜索

	MySQL支持全文搜索功能，允許對文本內(nèi)容進(jìn)行高效搜索：

	
-- 創(chuàng)建帶全文索引的表
CREATE TABLEarticles (
  idINTAUTO_INCREMENTPRIMARY KEY,
  titleVARCHAR(200),
  content TEXT,
  FULLTEXT INDEX idx_content (title, content)
) ENGINE=InnoDB;

-- 插入數(shù)據(jù)
INSERT INTOarticles (title, content)VALUES
('MySQL基礎(chǔ)教程','本文介紹MySQL的基本操作，包括增刪改查等內(nèi)容...'),
('SQL高級技巧','本文詳細(xì)講解SQL的高級用法，包括子查詢、存儲過程等...'),
('數(shù)據(jù)庫優(yōu)化指南','如何優(yōu)化MySQL數(shù)據(jù)庫性能，包括索引優(yōu)化、查詢優(yōu)化等...');

-- 自然語言模式搜索
SELECT*FROMarticles
WHEREMATCH(title, content) AGAINST('MySQL 基礎(chǔ)'INNATURALLANGUAGEMODE);

-- 布爾模式搜索
SELECT*FROMarticles
WHEREMATCH(title, content) AGAINST('+MySQL -高級'INBOOLEANMODE);

-- 帶有擴(kuò)展查詢的搜索
SELECT*FROMarticles
WHEREMATCH(title, content) AGAINST('數(shù)據(jù)庫'WITHQUERY EXPANSION);


	

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

	?+：必須包含該詞

	?-：必須不包含該詞

	?>：增加相關(guān)性權(quán)重

	?<：減少相關(guān)性權(quán)重

	?*：通配符

	?""：精確短語匹配

	
-- 使用布爾模式操作符
SELECT*FROMarticles
WHEREMATCH(title, content) AGAINST('+MySQL +"基礎(chǔ)教程"'INBOOLEANMODE);


	

	二十、常見問題與最佳實(shí)踐

	安全最佳實(shí)踐

	1.定期更新MySQL：保持最新安全補(bǔ)丁

	2.使用強(qiáng)密碼：為所有用戶設(shè)置強(qiáng)密碼

	3.最小權(quán)限原則：只授予用戶必要的權(quán)限

	4.加密連接：使用SSL/TLS加密客戶端與服務(wù)器之間的通信

	5.審計(jì)日志：啟用審計(jì)日志記錄關(guān)鍵操作

	
-- 啟用SSL連接
CREATEUSER'secure_user'@'%'IDENTIFIEDBY'password'REQUIRE SSL;

-- 設(shè)置密碼策略
SETGLOBALvalidate_password.policy=STRONG;


	

	性能最佳實(shí)踐

	1.適當(dāng)使用索引：為常用查詢條件創(chuàng)建索引，但避免過多索引

	2. **避免SELECT ***：只查詢需要的列

	3.使用批量操作：批量插入比單條插入更高效

	4.定期維護(hù)表：分析和優(yōu)化表

	5.合理設(shè)置緩存：根據(jù)系統(tǒng)內(nèi)存配置適當(dāng)?shù)木彌_池大小

	常見錯誤與解決方案

	1.Too many connections：
-- 增加最大連接數(shù)
SETGLOBALmax_connections=500;

-- 查看當(dāng)前連接
SHOWPROCESSLIST;

-- 終止空閑連接
KILL connection_id;


	

	2.Deadlock found：
-- 查看最后一個死鎖信息
SHOWENGINE INNODB STATUS;

-- 調(diào)整事務(wù)隔離級別
SETSESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;


	

	3.Table is full：
-- 增加臨時表大小
SETGLOBALtmp_table_size=128M;
SETGLOBALmax_heap_table_size=128M;


	

	總結(jié)

	本文通過理論與實(shí)例詳細(xì)介紹了MySQL數(shù)據(jù)庫的基礎(chǔ)知識和高級特性：

	1.基礎(chǔ)操作：數(shù)據(jù)庫和表的創(chuàng)建、修改和刪除

	2.CRUD操作：數(shù)據(jù)的增刪改查

	3.高級查詢：連接、子查詢、窗口函數(shù)和CTE

	4.數(shù)據(jù)庫管理：事務(wù)控制、索引優(yōu)化、備份恢復(fù)

	5.高級特性：存儲過程、觸發(fā)器、視圖、分區(qū)表

	6.性能與安全：性能調(diào)優(yōu)、用戶管理、安全最佳實(shí)踐

	熟練掌握這些知識點(diǎn)，可以幫助你構(gòu)建高效、安全、可靠的MySQL數(shù)據(jù)庫應(yīng)用。隨著對MySQL的深入學(xué)習(xí)和實(shí)踐，你將能夠解決更復(fù)雜的數(shù)據(jù)管理和分析需求，為應(yīng)用程序提供強(qiáng)大的數(shù)據(jù)支持。

	記住，在生產(chǎn)環(huán)境中使用MySQL時，務(wù)必關(guān)注數(shù)據(jù)安全性、性能優(yōu)化和備份恢復(fù)等關(guān)鍵問題，確保數(shù)據(jù)庫系統(tǒng)的穩(wěn)定和可靠運(yùn)行。持續(xù)學(xué)習(xí)和實(shí)踐是成為MySQL專家的關(guān)鍵。

	

	

	鏈接：https://lethehong.blog.csdn.net/article/details/147492039?spm=1001.2014.3001.5502