在 MySQL 的學習路徑中“表數(shù)據(jù)操作”是很容易被低估的一個環(huán)節(jié)。很多新手花了不少時間理解建表語句、字段類型、主鍵外鍵但到了真正寫業(yè)務代碼時每天接觸最多的反而是對表里數(shù)據(jù)進行增刪改查。更麻煩的是這類操作看起來簡單寫錯之后的代價卻可能非常大一條沒有 WHERE 的 UPDATE 會改掉整張表一條沒有事務保護的 DELETE 可能讓線上數(shù)據(jù)無法恢復。本文圍繞 MySQL 表數(shù)據(jù)操作展開系統(tǒng)梳理 INSERT、SELECT、UPDATE、DELETE 的使用方法、常見寫法、事務與鎖的影響并給出一個可以直接運行的完整案例。無論你是剛開始學數(shù)據(jù)庫的初學者還是已經(jīng)寫過一段時間 SQL、想補齊安全細節(jié)的開發(fā)者這篇文章都值得收藏對照。1. 表數(shù)據(jù)操作到底是什么1.1 DML 與 DDL、DCL 的分工要理解 MySQL 表數(shù)據(jù)操作先要分清 SQL 語句的幾個大類。數(shù)據(jù)庫日常執(zhí)行的語句通?？梢詣澐譃?DDL、DML、DCL、DQL類別英文全稱主要語句作用對象DDLData Definition LanguageCREATE、ALTER、DROP、TRUNCATE表結(jié)構(gòu)、數(shù)據(jù)庫結(jié)構(gòu)DMLData Manipulation LanguageINSERT、UPDATE、DELETE表中的數(shù)據(jù)DQLData Query LanguageSELECT查詢表中的數(shù)據(jù)DCLData Control LanguageGRANT、REVOKE用戶權(quán)限與訪問控制本文關(guān)注的重點是 DML也就是對表數(shù)據(jù)的增、刪、改操作。不過在日常開發(fā)中SELECT 查詢和 DML 的關(guān)系非常緊密因為我們需要先查出哪些數(shù)據(jù)滿足條件才敢放心執(zhí)行 UPDATE 或 DELETE。所以下文會把 SELECT 也納入“表數(shù)據(jù)操作”的完整講解范圍。1.2 為什么表數(shù)據(jù)操作如此重要建表只是搭建骨架真正驅(qū)動業(yè)務運轉(zhuǎn)的是表里的數(shù)據(jù)。用戶注冊后要插入一條用戶記錄下單后要更新訂單狀態(tài)商品下架后要刪除或標記失效記錄。這些都是典型的表數(shù)據(jù)操作。從另一個角度看后端開發(fā)中大量性能問題、數(shù)據(jù)一致性問題、線上事故也都集中在數(shù)據(jù)操作環(huán)節(jié)。比如 UPDATE 沒有走索引導致鎖范圍擴大DELETE 刪掉了重要業(yè)務數(shù)據(jù)INSERT 大批量逐條提交導致性能很差。掌握表數(shù)據(jù)操作不等于只會寫四條基本語句而是要理解條件過濾、事務邊界、索引影響、備份策略這樣在真實項目中才能安全落地。1.3 先理解行、列與記錄在 MySQL 中一張表由行和列組成。列定義了字段名稱與數(shù)據(jù)類型行則是一條具體的數(shù)據(jù)記錄。例如學生表里每一行代表一個學生每一列代表學號、姓名、班級等信息。學習 DML 時要始終帶著“操作對象是行”的意識。INSERT 是新增一行或多行UPDATE 是修改符合條件的行DELETE 是刪除符合條件的行SELECT 是篩選出符合條件的行。寫 SQL 時最重要的就是明確“哪些行會被影響”。2. 環(huán)境準備與測試表設計2.1 連接 MySQL開始操作前先確認已經(jīng)安裝并啟動了 MySQL 服務。MySQL 的安裝方式很多可以用官方安裝包、操作系統(tǒng)軟件源也可以用 Docker 快速啟動一個實例。本文的重點是表數(shù)據(jù)操作SQL 語法在 MySQL 5.7 和 MySQL 8.0 中基本通用如果你的版本較新個別細節(jié)以自己環(huán)境為準即可。連接本機 MySQL 最常用的命令是mysql -uroot -p輸入密碼后會出現(xiàn)mysql提示符?？吹教崾痉f明已經(jīng)進入 MySQL 客戶端可以執(zhí)行 SQL 語句了。如果 MySQL 不在當前機器的默認 socket 路徑也可以指定主機和端口mysql -h127.0.0.1 -P3306 -uroot -p這里-h指定主機-P指定端口-u指定用戶-p表示需要輸入密碼。生產(chǎn)環(huán)境不建議直接用 root 操作業(yè)務庫最好單獨創(chuàng)建業(yè)務賬號并授予最小權(quán)限。2.2 創(chuàng)建數(shù)據(jù)庫與成績表為了方便后續(xù)演示我們創(chuàng)建一個school_db數(shù)據(jù)庫并設計一張學生表和一張成績表。先創(chuàng)建數(shù)據(jù)庫CREATE DATABASE IF NOT EXISTS school_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;使用數(shù)據(jù)庫USE school_db;創(chuàng)建學生表CREATE TABLE student ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主鍵, student_no VARCHAR(20) NOT NULL COMMENT 學號, student_name VARCHAR(50) NOT NULL COMMENT 姓名, gender CHAR(1) DEFAULT NULL COMMENT 性別, class_no VARCHAR(20) DEFAULT NULL COMMENT 班級, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 創(chuàng)建時間, PRIMARY KEY (id), UNIQUE KEY uk_student_no (student_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT學生表;創(chuàng)建成績表CREATE TABLE score ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主鍵, student_no VARCHAR(20) NOT NULL COMMENT 學號, course_name VARCHAR(50) NOT NULL COMMENT 課程名稱, score DECIMAL(5,2) NOT NULL COMMENT 成績, exam_date DATE NOT NULL COMMENT 考試日期, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 創(chuàng)建時間, PRIMARY KEY (id), KEY idx_student_course (student_no, course_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT成績表;這里沒有創(chuàng)建物理外鍵而是通過student_no字段保持邏輯關(guān)聯(lián)。實際項目中是否使用外鍵需要根據(jù)業(yè)務設計權(quán)衡外鍵能保證完整性但也可能帶來額外的鎖與性能開銷。表數(shù)據(jù)操作示例中先使用邏輯外鍵更靈活。關(guān)于字段類型這里單獨強調(diào)一個常見誤區(qū)MySQL 中的INT(5)并不表示“只能存 5 位數(shù)字”。INT的存儲范圍是固定的括號里的數(shù)字通常只是配合ZEROFILL使用時的顯示寬度。比如INT(5)仍然可以存儲超過 5 位的整數(shù)只是某些場景下顯示效果不同。計算時也仍然按照整數(shù)類型處理并不會因為寫成了INT(5)就限制數(shù)值大小。2.3 準備初始測試數(shù)據(jù)插入幾條學生數(shù)據(jù)INSERT INTO student (student_no, student_name, gender, class_no) VALUES (1001, 張三, M, 2024-01班), (1002, 李四, F, 2024-01班), (1003, 王五, M, 2024-02班), (1004, 趙六, F, 2024-02班), (1005, 孫七, M, 2024-03班);插入成績數(shù)據(jù)INSERT INTO score (student_no, course_name, score, exam_date) VALUES (1001, 數(shù)據(jù)庫原理, 82.50, 2025-01-10), (1001, Java程序設計, 67.00, 2025-01-12), (1002, 數(shù)據(jù)庫原理, 58.00, 2025-01-10), (1002, Java程序設計, 74.00, 2025-01-12), (1003, 數(shù)據(jù)庫原理, 45.50, 2025-01-10), (1004, 網(wǎng)絡基礎(chǔ), 90.00, 2025-01-15), (1005, Java程序設計, 63.50, 2025-01-12);這樣我們就有了一個可以反復練習的表環(huán)境。后面的講解都會圍繞這兩張表展開。3. 插入數(shù)據(jù)INSERT 的多種寫法3.1 基礎(chǔ)的單行插入INSERT 的作用是向表中新增記錄。最基本的語法是INSERT INTO student (student_no, student_name, gender, class_no) VALUES (1006, 周八, M, 2024-03班);執(zhí)行后MySQL 會返回類似下面的信息Query OK, 1 row affected (0.01 sec)這說明成功插入了 1 行記錄。如果省略字段列表就需要按照建表時的字段順序依次提供所有值INSERT INTO student VALUES (NULL, 1007, 吳九, F, 2024-01班, NOW());但這種寫法要求調(diào)用方對表結(jié)構(gòu)非常清楚一旦表結(jié)構(gòu)調(diào)整這段 SQL 很容易出錯。實際開發(fā)中更推薦顯式寫出字段列表可讀性和穩(wěn)定性都更好。3.2 插入部分字段當某些字段有默認值或者允許為 NULL 時INSERT 語句可以不提供完整字段列表。例如gender允許為 NULLcreate_time有默認值那么可以這樣寫INSERT INTO student (student_no, student_name, class_no) VALUES (1008, 鄭十, 2024-01班);執(zhí)行后gender會是 NULLcreate_time會自動使用當前時間。靈活使用默認值可以減少業(yè)務代碼里不必要的賦值。3.3 一次插入多行當需要批量插入多條記錄時不要一條一條執(zhí)行 INSERT而是使用多行 VALUES 的寫法INSERT INTO score (student_no, course_name, score, exam_date) VALUES (1009, 數(shù)據(jù)庫原理, 88.00, 2025-03-01), (1010, Java程序設計, 72.50, 2025-03-05), (1011, 網(wǎng)絡基礎(chǔ), 91.00, 2025-03-08);一次插入多行可以減少客戶端與 MySQL 服務端之間的網(wǎng)絡交互次數(shù)也能讓導入效率明顯提升。在數(shù)據(jù)量較大時可以按每批 500 到 2000 行的量級分批執(zhí)行避免單條 SQL 過大。3.4 通過 SELECT 復制數(shù)據(jù)INSERT 的數(shù)據(jù)來源不一定只能手寫 VALUES也可以來自另一張表或同一張表的查詢結(jié)果。例如要把學生表中 2024-01 班的學生復制到一張臨時表中可以先建一張臨時表CREATE TABLE student_temp LIKE student;然后使用 INSERT INTO ... SELECT 把數(shù)據(jù)復制進去INSERT INTO student_temp (student_no, student_name, gender, class_no) SELECT student_no, student_name, gender, class_no FROM student WHERE class_no 2024-01班;這種寫法常用于數(shù)據(jù)歸檔、臨時表加工、表結(jié)構(gòu)升級等場景。需要注意SELECT 出來的字段順序必須和 INSERT 后面的字段列表一致。3.5 處理唯一鍵沖突如果表中已經(jīng)存在唯一約束插入重復記錄時會報錯。比如student_no有唯一索引再次插入學號 1001 就會提示ERROR 1062 (23000): Duplicate entry 1001 for key student.uk_student_no有兩種常見處理方式。第一種是使用INSERT IGNORE忽略沖突并保留原記錄INSERT IGNORE INTO student (student_no, student_name, gender, class_no) VALUES (1001, 張三, M, 2024-01班);執(zhí)行后返回Query OK, 0 rows affected說明重復數(shù)據(jù)被忽略。第二種是使用ON DUPLICATE KEY UPDATE在沖突時執(zhí)行更新操作INSERT INTO student (student_no, student_name, gender, class_no) VALUES (1001, 張三, M, 2024-05班) ON DUPLICATE KEY UPDATE class_no VALUES(class_no);這條語句的意思是如果學號 1001 不存在就正常插入如果已經(jīng)存在則把該學生的班級更新為2024-05班。高版本 MySQL 對VALUES()函數(shù)可能出現(xiàn)棄用提示但當前示例思路仍可運行生產(chǎn)環(huán)境請根據(jù)版本選擇更合適的別名寫法。4. 查詢數(shù)據(jù)SELECT 的核心用法4.1 基礎(chǔ)查詢與字段別名SELECT 是使用頻率最高的 SQL 語句。最簡單的查詢可以列出全部學生SELECT * FROM student;*表示返回所有字段適合快速查看數(shù)據(jù)但生產(chǎn)環(huán)境不建議在業(yè)務代碼里經(jīng)常使用SELECT *。因為表結(jié)構(gòu)可能增加字段返回過多無用數(shù)據(jù)會增加網(wǎng)絡傳輸和內(nèi)存開銷。更推薦按需列出字段并為字段設置可讀的別名SELECT student_no AS sno, student_name AS name, class_no FROM student;這里AS用于給字段或表起別名。在 Java、Python 等語言中接收查詢結(jié)果時清晰的別名能讓字段映射更直觀。4.2 WHERE 條件過濾與 NULL 判斷WHERE 子句用于篩選符合條件的行。例如查詢班級為2024-01班的學生SELECT student_no, student_name, class_no FROM student WHERE class_no 2024-01班;多個條件可以用 AND、OR 組合。查詢成績大于 80 分且考試日期在 2025 年之后的記錄SELECT student_no, course_name, score, exam_date FROM score WHERE score 80 AND exam_date 2025-01-01;新手經(jīng)常在 NULL 判斷上出錯。NULL 表示“未知值”不能用或!判斷。比如學生表中有學生的gender為空正確的寫法是SELECT student_no, student_name FROM student WHERE gender IS NULL;如果寫成gender NULLMySQL 不會報錯但查詢結(jié)果永遠是空因為 NULL 不等于任何值包括它自己。這一點在面試和日常開發(fā)中都經(jīng)常被考察。4.3 排序 ORDER BY使用 ORDER BY 可以讓查詢結(jié)果按照指定字段排序。默認是升序 ASC也可以指定降序 DESC。查詢成績從高到低排列SELECT student_no, course_name, score FROM score ORDER BY score DESC;需要按多個字段排序時可以寫多個排序鍵。比如先按成績降序成績相同則按考試日期升序SELECT student_no, course_name, score, exam_date FROM score ORDER BY score DESC, exam_date ASC;在 MySQL 默認字符集和排序規(guī)則下字符串比較通常不區(qū)分大小寫。如果你希望區(qū)分大小寫可以使用BINARY關(guān)鍵字或指定大小寫敏感的排序規(guī)則。這個問題經(jīng)常被忽視但在用戶名、編碼類字段查詢時可能造成不符合預期的結(jié)果。4.4 聚合統(tǒng)計與分組 GROUP BY聚合函數(shù)可以將多行數(shù)據(jù)匯總成一個或多個統(tǒng)計值。常用的聚合函數(shù)有 COUNT、SUM、AVG、MAX、MIN。例如統(tǒng)計成績表中共有多少條記錄SELECT COUNT(*) AS total_cnt FROM score;統(tǒng)計每個學生參加了幾門考試、平均分是多少SELECT student_no, COUNT(*) AS exam_cnt, ROUND(AVG(score), 2) AS avg_score FROM score GROUP BY student_no ORDER BY avg_score DESC;這里的ROUND(AVG(score), 2)表示對平均分四舍五入并保留兩位小數(shù)。執(zhí)行結(jié)果會按學生學號分組分別統(tǒng)計每個人的考試次數(shù)和平均成績。WHERE 和 GROUP BY 的執(zhí)行順序需要特別注意WHERE 是在分組之前過濾原始行GROUP BY 之后如果想對分組結(jié)果再做過濾必須使用 HAVING而不能使用 WHERE。例如只保留平均分大于 70 的學生SELECT student_no, COUNT(*) AS exam_cnt, ROUND(AVG(score), 2) AS avg_score FROM score GROUP BY student_no HAVING AVG(score) 70 ORDER BY avg_score DESC;把HAVING AVG(score) 70換成WHERE AVG(score) 70會直接報錯因為 WHERE 無法作用于聚合結(jié)果。4.5 分頁 LIMIT當查詢結(jié)果很多時可以用 LIMIT 限制返回條數(shù)也可以配合 OFFSET 實現(xiàn)分頁。例如查詢成績最高的前 3 條記錄SELECT student_no, course_name, score FROM score ORDER BY score DESC LIMIT 3;分頁查詢第 2 頁每頁 3 條SELECT student_no, course_name, score FROM score ORDER BY score DESC LIMIT 3 OFFSET 3;這里的OFFSET 3表示跳過前 3 條記錄。如果只寫LIMIT 3表示返回 3 條如果寫LIMIT 3, 3則第一個 3 表示偏移量第二個 3 表示返回條數(shù)容易混淆實際項目中建議寫清OFFSET可讀性更好。4.6 多表關(guān)聯(lián)查詢 JOIN業(yè)務數(shù)據(jù)往往分散在多個表中查詢時需要把表關(guān)聯(lián)起來。比如想查看學生的姓名和對應成績可以使用 JOINSELECT s.student_no, s.student_name, sc.course_name, sc.score FROM student s JOIN score sc ON s.student_no sc.student_no WHERE sc.course_name 數(shù)據(jù)庫原理 ORDER BY sc.score DESC;這里s和sc分別是 student、score 表的別名。JOIN 的作用是把兩個表中student_no相同的行連接在一起。如果希望查詢所有學生以及他們的成績情況即使某些學生沒有成績也要顯示可以使用 LEFT JOINSELECT s.student_no, s.student_name, sc.course_name, sc.score FROM student s LEFT JOIN score sc ON s.student_no sc.student_no;LEFT JOIN 會保留左表中的所有行右表中沒有匹配到數(shù)據(jù)時對應字段會顯示為 NULL。這種寫法在統(tǒng)計“哪些學生沒有考試記錄”時非常有用。5. 修改數(shù)據(jù)UPDATE 的正確姿勢5.1 UPDATE 基礎(chǔ)語法UPDATE 用于修改表中已有記錄基礎(chǔ)語法如下UPDATE score SET score 90.00 WHERE student_no 1001 AND course_name 數(shù)據(jù)庫原理 AND exam_date 2025-01-10;執(zhí)行后MySQL 會返回受影響的行數(shù)。如果該條件匹配到了 1 條記錄就會返回Query OK, 1 row affected (0.01 sec)這里最關(guān)鍵的是 WHERE 條件。UPDATE 如果沒有 WHERE會更新表中所有行。在開發(fā)環(huán)境和測試環(huán)境可能無所謂但在生產(chǎn)環(huán)境執(zhí)行全表 UPDATE幾乎等同于事故。5.2 更新字段自身進行計算修改數(shù)據(jù)時常需要基于原字段值進行計算。比如給某位學生的一門課程成績加 5 分UPDATE score SET score score 5 WHERE student_no 1002 AND course_name 數(shù)據(jù)庫原理;這里的score score 5表示讀取當前成績加上 5 后再寫回字段。MySQL 的字段計算不需要額外使用 SELECT 先把值取出來直接寫表達式即可。還有一種常見需求是根據(jù)條件給不同行設置不同的值可以用 CASE WHEN 實現(xiàn)。例如對 2025 年考試中的數(shù)據(jù)庫原理成績加 2 分對 Java 程序設計成績加 1 分UPDATE score SET score score CASE course_name WHEN 數(shù)據(jù)庫原理 THEN 2 WHEN Java程序設計 THEN 1 ELSE 0 END WHERE course_name IN (數(shù)據(jù)庫原理, Java程序設計);CASE WHEN 讓一條 UPDATE 可以處理更豐富的分支邏輯降低了在業(yè)務代碼中逐條 update 的需求。5.3 UPDATE 的安全風險UPDATE 是數(shù)據(jù)操作中最需要謹慎對待的語句。建議在更新之前先用 SELECT 確認 WHERE 條件命中了哪些數(shù)據(jù)。例如原本要執(zhí)行UPDATE score SET score 80 WHERE student_no 1002;可以先執(zhí)行SELECT student_no, course_name, score FROM score WHERE student_no 1002;確認這些記錄確實都是需要修改的目標。如果 SELECT 查出了意想不到的數(shù)據(jù)說明 WHERE 條件可能寫錯了需要立即停下來檢查。在生產(chǎn)環(huán)境修改重要數(shù)據(jù)時更好做法是放到事務中執(zhí)行更新后先查詢驗證確認無誤再提交事務。這部分內(nèi)容會在第 7 章詳細展開。6. 刪除數(shù)據(jù)DELETE 與 TRUNCATE 的區(qū)別6.1 按條件刪除記錄DELETE 用于刪除表中符合條件的記錄。例如刪除學號為 1005 的學生成績記錄DELETE FROM score WHERE student_no 1005;如果 DELETE 不寫 WHERE會清空整張表的數(shù)據(jù)DELETE FROM score;這種操作非常危險。雖然 InnoDB 引擎下不提交還能通過 ROLLBACK 回滾但在自動提交模式下相當于一瞬間清空整張表且恢復成本很高。平時練習和開發(fā)中一定要形成“DELET 前先 SELECT”的習慣。6.2 DELETE 與 TRUNCATE 的區(qū)別TRUNCATE TABLE 也可以清空表數(shù)據(jù)但它和 DELETE 有本質(zhì)區(qū)別DELETE 是 DML可以帶 WHERE可以配合事務回滾TRUNCATE 是 DDL不能帶 WHERE執(zhí)行后通常無法按事務回滾TRUNCATE 會重置 AUTO_INCREMENT 自增計數(shù)DELETE 默認不會TRUNCATE 清空大表的速度通常比 DELETE