指南)
學MySQL有一段時間了今天想認認真真聊聊DML語句也就是數(shù)據(jù)操作語言。無論你是剛建好庫和表的新手還是已經(jīng)能寫簡單查詢的初級開發(fā)者DML都是每天接觸最多、出問題也最多的部分。INSERT、UPDATE、DELETE、SELECT這四條核心命令幾乎覆蓋了業(yè)務開發(fā)中90%的數(shù)據(jù)操作場景。這篇文章我打算用一套可以跟著敲的示例表從定義講到用法再講到實操中才遇得到的坑整體就是一篇可以當成“第三章”筆記來用的長文。如果你正好學到數(shù)據(jù)庫操作這一塊或者平時寫SQL總感覺哪里不夠踏實那這篇內(nèi)容應該能幫上忙。提示本文所有示例都基于MySQL 8.0環(huán)境使用的字符集是utf8mb4建議你也用同樣的配置避免后面演示時出現(xiàn)亂碼問題。1. 先把概念理清楚DML到底是什么1.1 DML在SQL家族里的位置SQL語言按功能劃分成幾大類DDL數(shù)據(jù)定義語言負責CREATE、ALTER、DROP這些建表改表的操作DCL數(shù)據(jù)控制語言負責GRANT、REVOKE這類權(quán)限管理TCL事務控制語言管的是COMMIT、ROLLBACK而DML就是數(shù)據(jù)操作語言核心成員就四個INSERT、UPDATE、DELETE、SELECT。這里值得說一下有些老資料會把SELECT單獨歸為DQL數(shù)據(jù)查詢語言但在MySQL官方手冊和絕大多數(shù)面試場景里SELECT依然被歸入DML范疇因為增刪改查本身就是圍繞“數(shù)據(jù)操作”這個整體概念展開的。你在寫DML時SELECT作為查詢操作反過來經(jīng)常嵌套在INSERT、UPDATE、DELETE里充當數(shù)據(jù)來源分開理解是正確的但放在一起學更順手。DML操作的對象是數(shù)據(jù)行不是表結(jié)構(gòu)。換句話說DDL影響的是表的“骨架長什么樣”DML影響的則是“表里到底裝了什么內(nèi)容”。我見過不少初學者把ALTER TABLE和UPDATE混在一起其實區(qū)別特別簡單你想給表加一列那是DDL你想把某行數(shù)據(jù)的某個字段值改一下那是DML。1.2 為什么建議先掌握DML很多教程喜歡先花大篇幅講數(shù)據(jù)類型、約束、索引這些內(nèi)容當然重要但如果你是零基礎一上來就背這些理論很容易勸退。更好的節(jié)奏是先會用DML把自己手頭的數(shù)據(jù)“玩轉(zhuǎn)”再回頭補理論知識理解會深很多。比如你寫一條INSERT語句時自然會接觸到字段類型、默認值、非空約束寫一條UPDATE時自然會碰到WHERE條件的威力寫SELECT時自然會遇到索引為什么會讓查詢變快。從這個角度看DML是打通整個MySQL學習路徑的關鍵節(jié)點。學完這一章你基本就能獨立完成“建表、灌數(shù)據(jù)、改數(shù)據(jù)、刪數(shù)據(jù)、查數(shù)據(jù)”的完整閉環(huán)這對后續(xù)學習事務、索引、優(yōu)化都特別有幫助。2. 準備一套能跟著練的示例表沒有數(shù)據(jù)支撐的SQL文章都是耍流氓所以先建兩張表一張學生表一張班級表。這兩張表之間有外鍵關聯(lián)后續(xù)演示多表操作時也用得上。-- 班級表 CREATE TABLE class_info ( id INT AUTO_INCREMENT PRIMARY KEY COMMENT 班級ID, class_name VARCHAR(50) NOT NULL COMMENT 班級名稱, create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 創(chuàng)建時間 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT班級信息表; -- 學生表 CREATE TABLE student_info ( id INT AUTO_INCREMENT PRIMARY KEY COMMENT 學生ID, name VARCHAR(50) NOT NULL COMMENT 學生姓名, age INT COMMENT 年齡, gender TINYINT DEFAULT 0 COMMENT 性別: 0未知 1男 2女, class_id INT COMMENT 所屬班級ID, score DECIMAL(5,2) COMMENT 總分, enroll_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 入學時間 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT學生信息表;這里用class_info和student_info是為了避免和MySQL的保留字沖突比如order這種詞就不能直接當表名新手如果習慣用student、class其實也可以但加個后綴更保險。兩張表建好之后先插入幾條班級數(shù)據(jù)作為鋪墊INSERT INTO class_info (class_name) VALUES (軟件工程1班), (計算機科學2班), (大數(shù)據(jù)3班);從這一節(jié)開始下面的所有DML示例都會圍繞這套表展開你可以直接復制到自己的測試庫里跑跟著看效果。3. INSERT插入語句向表里喂數(shù)據(jù)的幾種姿勢3.1 基礎單行插入INSERT最基礎的語法是INSERT INTO 表名 (字段1, 字段2, ...) VALUES (值1, 值2, ...);舉個例子向?qū)W生表插入一條記錄INSERT INTO student_info (name, age, gender, class_id, score) VALUES (張三, 20, 1, 1, 588.50);這里有幾個細節(jié)值得注意。字段列表可以不寫全沒寫的字段會使用默認值比如enroll_time沒寫就自動取當前時間id沒寫就自動生成自增值。但如果你把某個NOT NULL字段漏掉了并且該字段沒有默認值MySQL會直接報錯。所以新手在一開始就要養(yǎng)成“寫INSERT時明確字段列表”的習慣這比簡寫成INSERT INTO student_info VALUES (...)要穩(wěn)健得多因為后者要求值的數(shù)量和順序必須和表結(jié)構(gòu)完全一致表結(jié)構(gòu)一變你的SQL就崩了。字段的順序可以按自己的意愿排列只要值和字段一一對應就行。比如我把年齡和班級ID換一下順序也完全沒問題只要VALUES里跟著換就行。3.2 多行插入與SET寫法多行插入是MySQL一個很實用的特性一次插入多條記錄時性能會比循環(huán)執(zhí)行單條INSERT好很多。語法就是在VALUES后面用逗號分隔多組值INSERT INTO student_info (name, age, gender, class_id, score) VALUES (李四, 21, 1, 1, 552.00), (王五, 19, 2, 2, 621.50), (趙六, 22, 1, 3, 487.00), (錢七, 20, 2, 2, 599.00);執(zhí)行完這條語句我們可以順帶用SELECT驗證一下SELECT * FROM student_info;除了VALUES寫法MySQL還支持SET形式的插入INSERT INTO student_info SET name 孫八, age 18, gender 2, class_id 3;這種寫法更直觀尤其適合一次只插入一條記錄的場景寫起來不像VALUES那樣容易看花眼。但它的缺點是不太方便插入多行。所以我的建議是單條插入用SET批量插入用VALUES多行這個習慣可以一直保持下去。3.3 INSERT ... SELECT把查詢結(jié)果灌進表里有些場景下你需要把另一張表或者同一張表的某些行的數(shù)據(jù)復制到目標表里。這時候INSERT ... SELECT就是效率最高的方式不需要先把數(shù)據(jù)查出來再手動拼INSERT。-- 把班級ID為2的學生復制到班級ID為3 INSERT INTO student_info (name, age, gender, class_id, score) SELECT name, age, gender, 3, score FROM student_info WHERE class_id 2;這里有一個很重要的點SELECT子句返回的列數(shù)和類型必須和INSERT后面的字段列表匹配否則MySQL會報“Column count doesnt match value count”的錯誤。另外需要注意如果原表里有自增主鍵你復制數(shù)據(jù)時可以不把主鍵復制過去讓目標表自動生成新的主鍵。這種寫法在實際業(yè)務里特別常用比如你要把“舊表數(shù)據(jù)遷移到新表”“把正式環(huán)境的數(shù)據(jù)抽一份到測試環(huán)境”用一條SQL就搞定了。我在實際項目里遇到過好幾次因為人為復制數(shù)據(jù)導致的錯誤用INSERT ... SELECT配合WHERE條件反而更可控。3.4 主鍵沖突處理INSERT IGNORE和ON DUPLICATE KEY UPDATE給表里插入數(shù)據(jù)時最頭疼的問題之一就是主鍵沖突。比如你想插入一個ID1的學生但表里已經(jīng)有ID1的數(shù)據(jù)了直接執(zhí)行INSERT會報錯。解決辦法有三種。第一種是INSERT IGNORE遇到主鍵或唯一鍵沖突就自動跳過這條數(shù)據(jù)不報錯INSERT IGNORE INTO student_info (id, name, age) VALUES (1, 張三, 20);第二種是ON DUPLICATE KEY UPDATE它的邏輯是如果主鍵沖突了就執(zhí)行后面的更新操作不沖突才執(zhí)行插入。舉個實際用法INSERT INTO student_info (id, name, age, score) VALUES (1, 張三, 20, 590.00) ON DUPLICATE KEY UPDATE age VALUES(age), score VALUES(score);這里VALUES(age)和VALUES(score)指的是你INSERT語句中試圖插入的值。執(zhí)行效果是ID1的記錄存在則把age更新為20、score更新為590.00如果不存在就正常插入完整的記錄。這在做數(shù)據(jù)同步、每日批處理任務時幾乎是標配寫法。第三種是REPLACE INTO這種方式的邏輯是先嘗試插入如果遇到主鍵沖突就先刪除原來的行再插入新行。它看起來和ON DUPLICATE KEY UPDATE有點像但執(zhí)行代價更高因為刪除和重新插入會導致自增主鍵發(fā)生變化。除非你能接受這一點否則我不推薦新手習慣性用它。我在多個項目里都見過因為用REPLACE導致數(shù)據(jù)被重建、外鍵關聯(lián)出問題的案例盡量用INSERT ... ON DUPLICATE KEY UPDATE才是更穩(wěn)的選擇。3.5 INSERT相關的常見坑插入數(shù)據(jù)時最容易出問題的幾個點我單獨列出來字符串和日期必須加引號。數(shù)值類型可以裸寫但字符串和日期不加引號時MySQL會嘗試把它解析成數(shù)值或報錯。比如插入abc不寫引號直接就炸了。NULL和空字符串是兩回事。NULL表示未知表示空字符串。如果字段設置了NOT NULL插入NULL會報錯但插入不會。自增列不要手動賦值。除非你清楚自己在做什么否則讓MySQL自己生成主鍵就好。手動插入一個很大的ID可能會把自增序列搞得混亂。超長字段值會被截斷或報錯。MySQL在嚴格模式下8.0默認開啟會直接報錯非嚴格模式下會靜默截斷這也是很多“數(shù)據(jù)看起來不對”的根源。判斷當前模式可以用SELECT sql_mode;4. UPDATE更新語句改數(shù)據(jù)之前先想清楚4.1 基本UPDATE語法和條件過濾UPDATE的基本語法UPDATE 表名 SET 字段1 值1, 字段2 值2 WHERE 條件;比如把張三的年齡改成21UPDATE student_info SET age 21 WHERE name 張三;這里要單獨講講WHERE子句的重要性。UPDATE不帶WHERE就是對整張表的所有行執(zhí)行更新。有一次我在測試環(huán)境執(zhí)行UPDATE student_info SET score 600結(jié)果是全表學生的成績?nèi)兂?00分。在測試環(huán)境可能一笑了之如果在生產(chǎn)環(huán)境后果不堪設想。所以每次寫UPDATE都可以在心里默念一遍我的WHERE真的能精準圈定目標數(shù)據(jù)嗎建議在更新前先跑一遍同條件的SELECT確認影響范圍SELECT * FROM student_info WHERE name 張三;然后再放心執(zhí)行UPDATE。這個習慣一點都不麻煩但能省下無數(shù)后悔藥。4.2 關聯(lián)更新多表UPDATE有些場景下你要根據(jù)另一張表的數(shù)據(jù)來更新當前表。比如班級改名后你想把學生表里所有該班級學生的班級名稱同步更新就可以用多表UPDATEUPDATE student_info s JOIN class_info c ON s.class_id c.id SET s.class_id 2 WHERE c.class_name 計算機科學2班;這里JOIN是用來關聯(lián)兩張表的它的核心邏輯是先找出班級名為“計算機科學2班”的班級ID然后更新學生表中對應的行。這種寫法的好處是省去了子查詢讓SQL更清晰。更常見的一種寫法是利用子查詢UPDATE student_info SET score score 10 WHERE class_id IN (SELECT id FROM class_info WHERE class_name LIKE %計算機%);這句話會把計算機相關班級的所有學生成績加10分。注意MySQL里更新同一張表時子查詢直接引用同一張表可能會報“You cant specify target table for update in FROM clause”的錯誤這個后面在常見問題里我會詳細說解法。4.3 UPDATE時容易被忽略的細節(jié)第一字段值的合法性。UPDATE不會自動檢查你新賦的值是否合理比如把年齡改成-5MySQL不會攔你除非你建了CHECK約束。業(yè)務上的校驗應該由應用層負責或者在建表時加合理的CHECK約束。第二數(shù)據(jù)類型的隱式轉(zhuǎn)換。如果字段是字符串類型你給它賦一個數(shù)值MySQL會嘗試自動轉(zhuǎn)換雖然一般不報錯但可能導致索引失效查詢變慢。所以寫UPDATE時盡量保持值的類型和字段類型一致。第三唯一鍵沖突。更新時如果新值導致唯一鍵重復MySQL會報錯。比如你把某個學生的姓名改成和另一個學生相同而姓名列設置了UNIQUE約束這條UPDATE就會失敗。5. DELETE刪除語句刪數(shù)據(jù)其實是門技術活5.1 DELETE基本語法與注意事項DELETE的基本語法DELETE FROM 表名 WHERE 條件;比如刪除張三DELETE FROM student_info WHERE name 張三;同樣DELETE不帶WHERE就是清空整張表。而且DELETE和UPDATE還有一個區(qū)別有些數(shù)據(jù)庫系統(tǒng)對于不帶WHERE的DELETE會要求額外的權(quán)限或確認但MySQL默認不會攔你所以誤刪的可能性更高。我的建議是刪除前先SELECT確認刪除時盡量用主鍵或唯一鍵來圈定范圍不要只憑姓名這種可能重復的字段來刪。5.2 DELETE和TRUNCATE的區(qū)別很多新手問清空表的時候用DELETE還是TRUNCATE這兩者的核心區(qū)別如下對比維度DELETETRUNCATE操作類型DML逐行刪除DDL重建表結(jié)構(gòu)是否可帶WHERE可以按條件刪不可以直接清空執(zhí)行速度慢逐行操作并寫日志快直接釋放數(shù)據(jù)頁自增ID是否重置不重置繼續(xù)往下增長重置從1開始是否可以回滾在事務中可以默認不可回滾場景上如果只是刪掉部分數(shù)據(jù)必然用DELETE如果要清空整張臨時表并且不需要保留自增ID的連續(xù)性TRUNCATE更合適。需要注意TRUNCATE會觸發(fā)MySQL的隱式提交即使你把它包在事務里也回滾不了這一點一定要記牢。5.3 多表刪除同時刪兩張表的數(shù)據(jù)需要同時刪除多個表的數(shù)據(jù)時多表DELETE就派上用場了。比如刪除班級ID為3的班級同時刪除該班級下的所有學生DELETE s, c FROM student_info s JOIN class_info c ON s.class_id c.id WHERE c.id 3;這里要注意DELETE后面跟的是表別名表示你要刪的是哪幾個表的數(shù)據(jù)而不是刪JOIN的結(jié)果。如果只寫DELETE FROM student_info那就只刪學生表班級表還得再寫一條SQL。實際開發(fā)中多表刪除要特別謹慎尤其是存在外鍵關系時。如果表之間設置了ON DELETE CASCADE刪父表記錄時子表會自動刪如果沒有外鍵約束或者約束方式是RESTRICT刪父表會報錯得手動刪完子表再刪父表。5.4 誤刪除的補救思路雖然不想嚇唬人但每個DBA和開發(fā)者都遲早會遇到誤刪數(shù)據(jù)的情況。簡單說幾個補救方向。如果MySQL開啟了binlog可以通過binlog日志把刪除操作找回來這是比較標準的恢復手段。測試環(huán)境下很多實例默認沒開binlog生產(chǎn)環(huán)境一般都會開。如果你已經(jīng)把DELETE包在事務里并且還沒COMMIT直接ROLLBACK就能恢復。日常預防上可以給重要的表建定時備份任務甚至用軟刪除加一個is_deleted字段來代替物理刪除。軟刪除在業(yè)務系統(tǒng)里非常常見避免了數(shù)據(jù)真正丟失帶來的風險。另外MySQL 8.0里有UNDO日志保留時間的配置刪除的數(shù)據(jù)在短時間內(nèi)還是可恢復的但不要指望它最快最省事的還是“提前備份謹慎操作”。6. SELECT查詢語句DML里真正的主角6.1 基礎查詢與過濾排序SELECT的語法比前面三個都復雜因為它承載了最豐富的查詢需求?；窘Y(jié)構(gòu)是SELECT 列名 FROM 表名 WHERE 條件 GROUP BY 列 HAVING 條件 ORDER BY 列 LIMIT 數(shù)量;先看最基礎的用法-- 查詢所有學生 SELECT * FROM student_info; -- 只查姓名和成績 SELECT name, score FROM student_info; -- 按成績降序排列 SELECT name, score FROM student_info ORDER BY score DESC;ORDER BY默認為升序如果要降序要顯式加DESC。多字段排序時寫在前面的優(yōu)先級高。比如先按班級排序再按成績降序SELECT name, class_id, score FROM student_info ORDER BY class_id ASC, score DESC;6.2 條件過濾WHERE的進階玩法WHERE子句里除了、、、這些比較運算符還有一些很常用的寫法。-- 范圍查詢 SELECT * FROM student_info WHERE score BETWEEN 500 AND 600; -- 列表查詢 SELECT * FROM student_info WHERE class_id IN (1, 2, 3); -- 模糊查詢名字以張開頭 SELECT * FROM student_info WHERE name LIKE 張%; -- 空值判斷注意IS NULL不能寫成 NULL SELECT * FROM student_info WHERE class_id IS NULL;模糊查詢中%表示任意多個字符包括0個_表示任意單個字符。比如LIKE 張_只能匹配“張”加一個字的名字如“張三”匹配不了“張大壯”。這個細節(jié)在面試里也?？?。還有一個新手容易踩的坑NULL的判斷。NULL不是等于某個值也不是不等于某個值它是“未知”。所以任何 NULL的寫法都查詢不到數(shù)據(jù)只能用IS NULL或者IS NOT NULL。6.3 聚合函數(shù)與分組統(tǒng)計聚合函數(shù)是SELECT里非常核心的一塊。它把多行的值匯總成一個值常用的有COUNT、SUM、AVG、MAX、MIN。比如統(tǒng)計學生人數(shù)和平均分SELECT COUNT(*) AS total_count, AVG(score) AS avg_score FROM student_info;COUNT(*)統(tǒng)計的是所有行數(shù)包括值為NULL的行COUNT(score)只統(tǒng)計score列非空的記錄數(shù)。這個區(qū)別如果沒搞清楚統(tǒng)計結(jié)果可能會有偏差。分組統(tǒng)計要配合GROUP BY比如按班級統(tǒng)計人數(shù)和平均分SELECT class_id, COUNT(*) AS num, AVG(score) AS avg_score FROM student_info GROUP BY class_id;這里很自然地引出一個問題如果你想在分組之后再加篩選條件不能用WHERE而要用HAVING。比如只想看平均分大于550的班級SELECT class_id, AVG(score) AS avg_score FROM student_info GROUP BY class_id HAVING avg_score 550;WHERE和HAVING的區(qū)別可以簡單理解成WHERE在分組前過濾原始行HAVING在分組后過濾聚合結(jié)果。6.4 多表連接查詢JOIN的用法數(shù)據(jù)很少只存在一張表里實際業(yè)務里絕大多數(shù)查詢都要關聯(lián)多張表。最常用的是INNER JOIN它返回兩張表中滿足關聯(lián)條件的交集數(shù)據(jù)。SELECT s.name, c.class_name FROM student_info s INNER JOIN class_info c ON s.class_id c.id;這里INNER可以省略不寫直接寫JOIN效果一樣。ON后面寫的是關聯(lián)條件它決定了兩個表怎么拼在一起。LEFT JOIN則會把左表的所有數(shù)據(jù)都查出來即使右表沒有匹配的記錄右表字段會顯示為NULL。SELECT s.name, c.class_name FROM student_info s LEFT JOIN class_info c ON s.class_id c.id;LEFT JOIN在開發(fā)中使用頻率非常高比如你要查“每個學生的班級名即使這個學生沒有分配班級也要列出來”就必須用LEFT JOININNER JOIN會直接把沒分班的學生過濾掉。這兩種JOIN的區(qū)別一定要弄透面試和實際開發(fā)里都是重中之重。6.5 分頁查詢LIMIT的實用套路分頁查詢是列表頁功能的基本功。MySQL用LIMIT實現(xiàn)SELECT * FROM student_info ORDER BY id LIMIT 10 OFFSET 0;OFFSET 0表示從第1行開始取10行OFFSET 10就是從第11行開始。也可以簡寫成SELECT * FROM student_info ORDER BY id LIMIT 10, 10;這個寫法中LIMIT 10, 10的第一個10是偏移量第二個10是返回的行數(shù)。很多新手會記反所以建議直接用LIMIT 頁大小 OFFSET 偏移量這種寫法語義更清晰。分頁一定要配ORDER BY否則每次查出來的順序可能不一樣導致翻頁時數(shù)據(jù)出現(xiàn)重復或漏掉。7. 實戰(zhàn)小案例用DML完成一個完整需求把前面的知識串起來我們做一個模擬需求把計算機科學2班中成績低于550分的學生全部加10分然后統(tǒng)計各班人數(shù)和平均分。第一步先查詢一下目標數(shù)據(jù)看看影響范圍SELECT s.id, s.name, s.score, c.class_name FROM student_info s JOIN class_info c ON s.class_id c.id WHERE c.class_name 計算機科學2班 AND s.score 550;第二步執(zhí)行更新UPDATE student_info SET score score 10 WHERE class_id IN (SELECT id FROM class_info WHERE class_name 計算機科學2班) AND score 550;這里我想專門解釋一下最容易出問題的地方如果你直接用JOIN的方式更新就把目標表也關聯(lián)進去MySQL可能報錯用子查詢時如果子查詢和更新目標表是同一張表也會出現(xiàn)限制。上面的寫法是先把class_info查出來再更新student_info沒有直接引用被更新的表所以能正常執(zhí)行這是我實際開發(fā)中被MySQL坑過之后總結(jié)出來的穩(wěn)妥寫法。第三步驗證結(jié)果SELECT c.class_name, COUNT(*) AS student_num, AVG(s.score) AS avg_score FROM student_info s JOIN class_info c ON s.class_id c.id GROUP BY c.class_name;這個流程看著簡單但非常接近真實業(yè)務的處理邏輯先查再改再統(tǒng)計。每一步都在用前面講過的知識這就是DML的日常形態(tài)。8. 常見問題速查表與避坑指南8.1 高頻報錯與解決方法問題現(xiàn)象報錯信息原因解決方法插入數(shù)據(jù)時提示列數(shù)不匹配Column count doesnt match value countVALUES里的數(shù)量和字段列表數(shù)量不一致檢查字段列表和值列表一一對應字符串忘記加引號Unknown column abc字符串被當成列名解析字符串和日期必須加單引號更新同一張表時子查詢報錯You cant specify target table for update in FROM clauseMySQL不允許同一張表作為更新目標和子查詢來源子查詢外面再套一層臨時表或用JOIN使用保留字當表名/列名You have an error in your SQL syntaxorder、group等詞是保留字加反引號或者換個名字中文亂碼顯示為問號字符集不一致建表用utf8mb4連接串加characterEncodingutf8查詢結(jié)果NULL判斷不出查不到數(shù)據(jù)用了 NULL改用IS NULL或IS NOT NULL刪數(shù)據(jù)沒有生效Rows matched: 0 Changed: 0WHERE條件沒有匹配到數(shù)據(jù)先SELECT驗證條件是否成立8.2 新手最容易踩的5個坑第一個坑是UPDATE和DELETE忘了寫WHERE或者WHERE寫得過于寬松。我見過因為一個WHERE id 0導致全表數(shù)據(jù)被改寫的案例這種錯誤輕則數(shù)據(jù)錯亂重則直接刪庫跑路。第二個坑是混淆了和IS對NULL的判斷。NULL NULL的結(jié)果是NULL所以條件不成立。所有對NULL的判斷都必須用IS NULL或IS NOT NULL。第三個坑是在多表JOIN時搞不清驅(qū)動表和匹配表的角色。LEFT JOIN左邊的表是驅(qū)動表它會全量保留右邊是匹配表匹配不到就補NULL。腦子里有這層概念寫起多表查詢才不會邏輯混亂。第四個坑是分頁越翻越亂。沒有ORDER BY的LIMIT分頁MySQL返回的記錄順序是不確定的尤其在數(shù)據(jù)量大的時候翻頁會出現(xiàn)重復數(shù)據(jù)。分頁時一定要指定唯一性強的排序字段。第五個坑是執(zhí)行完DML后忘了提交事務。如果你在代碼里開啟了事務但忘記COMMIT數(shù)據(jù)在別人眼里一直是舊狀態(tài)這種現(xiàn)象在排查“為什么數(shù)據(jù)改了前端沒變化”的問題時特別容易遇到。8.3 我的一點小建議既然看到這里了最后分享一個我用了很多年的習慣每次寫DML尤其是UPDATE和DELETE都強制自己先寫一條同名SELECT大概花兩三秒的時間確認WHERE條件圈定的范圍再回頭執(zhí)行。這個習慣在壓力大、事情多的時候特別能救命。另外測試環(huán)境里練SQL時可以隨便造一些垃圾數(shù)據(jù)反復試把報錯都見一遍反而到了生產(chǎn)環(huán)境就不慌了。我自己剛學MySQL那會兒就是把一份數(shù)據(jù)翻來覆去地插入、更新、刪除、再查回來折騰到最后四條命令的各種邊緣情況基本都摸清了。后續(xù)如果你想深入可以再往事務、鎖、索引優(yōu)化這些方向走但DML這關打得越扎實后面這些東西學起來就越順暢。 MySQL DML語句全解析：INSERT、UPDATE、DELETE、SELECT實戰(zhàn)指南
學MySQL有一段時間了今天想認認真真聊聊DML語句也就是數(shù)據(jù)操作語言。無論你是剛建好庫和表的新手還是已經(jīng)能寫簡單查詢的初級開發(fā)者DML都是每天接觸最多、出問題也最多的部分。INSERT、UPDATE、DELETE、SELECT這四條核心命令幾乎覆蓋了業(yè)務開發(fā)中90%的數(shù)據(jù)操作場景。這篇文章我打算用一套可以跟著敲的示例表從定義講到用法再講到實操中才遇得到的坑整體就是一篇可以當成“第三章”筆記來用的長文。如果你正好學到數(shù)據(jù)庫操作這一塊或者平時寫SQL總感覺哪里不夠踏實那這篇內(nèi)容應該能幫上忙。提示本文所有示例都基于MySQL 8.0環(huán)境使用的字符集是utf8mb4建議你也用同樣的配置避免后面演示時出現(xiàn)亂碼問題。1. 先把概念理清楚DML到底是什么1.1 DML在SQL家族里的位置SQL語言按功能劃分成幾大類DDL數(shù)據(jù)定義語言負責CREATE、ALTER、DROP這些建表改表的操作DCL數(shù)據(jù)控制語言負責GRANT、REVOKE這類權(quán)限管理TCL事務控制語言管的是COMMIT、ROLLBACK而DML就是數(shù)據(jù)操作語言核心成員就四個INSERT、UPDATE、DELETE、SELECT。這里值得說一下有些老資料會把SELECT單獨歸為DQL數(shù)據(jù)查詢語言但在MySQL官方手冊和絕大多數(shù)面試場景里SELECT依然被歸入DML范疇因為增刪改查本身就是圍繞“數(shù)據(jù)操作”這個整體概念展開的。你在寫DML時SELECT作為查詢操作反過來經(jīng)常嵌套在INSERT、UPDATE、DELETE里充當數(shù)據(jù)來源分開理解是正確的但放在一起學更順手。DML操作的對象是數(shù)據(jù)行不是表結(jié)構(gòu)。換句話說DDL影響的是表的“骨架長什么樣”DML影響的則是“表里到底裝了什么內(nèi)容”。我見過不少初學者把ALTER TABLE和UPDATE混在一起其實區(qū)別特別簡單你想給表加一列那是DDL你想把某行數(shù)據(jù)的某個字段值改一下那是DML。1.2 為什么建議先掌握DML很多教程喜歡先花大篇幅講數(shù)據(jù)類型、約束、索引這些內(nèi)容當然重要但如果你是零基礎一上來就背這些理論很容易勸退。更好的節(jié)奏是先會用DML把自己手頭的數(shù)據(jù)“玩轉(zhuǎn)”再回頭補理論知識理解會深很多。比如你寫一條INSERT語句時自然會接觸到字段類型、默認值、非空約束寫一條UPDATE時自然會碰到WHERE條件的威力寫SELECT時自然會遇到索引為什么會讓查詢變快。從這個角度看DML是打通整個MySQL學習路徑的關鍵節(jié)點。學完這一章你基本就能獨立完成“建表、灌數(shù)據(jù)、改數(shù)據(jù)、刪數(shù)據(jù)、查數(shù)據(jù)”的完整閉環(huán)這對后續(xù)學習事務、索引、優(yōu)化都特別有幫助。2. 準備一套能跟著練的示例表沒有數(shù)據(jù)支撐的SQL文章都是耍流氓所以先建兩張表一張學生表一張班級表。這兩張表之間有外鍵關聯(lián)后續(xù)演示多表操作時也用得上。-- 班級表 CREATE TABLE class_info ( id INT AUTO_INCREMENT PRIMARY KEY COMMENT 班級ID, class_name VARCHAR(50) NOT NULL COMMENT 班級名稱, create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 創(chuàng)建時間 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT班級信息表; -- 學生表 CREATE TABLE student_info ( id INT AUTO_INCREMENT PRIMARY KEY COMMENT 學生ID, name VARCHAR(50) NOT NULL COMMENT 學生姓名, age INT COMMENT 年齡, gender TINYINT DEFAULT 0 COMMENT 性別: 0未知 1男 2女, class_id INT COMMENT 所屬班級ID, score DECIMAL(5,2) COMMENT 總分, enroll_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 入學時間 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT學生信息表;這里用class_info和student_info是為了避免和MySQL的保留字沖突比如order這種詞就不能直接當表名新手如果習慣用student、class其實也可以但加個后綴更保險。兩張表建好之后先插入幾條班級數(shù)據(jù)作為鋪墊INSERT INTO class_info (class_name) VALUES (軟件工程1班), (計算機科學2班), (大數(shù)據(jù)3班);從這一節(jié)開始下面的所有DML示例都會圍繞這套表展開你可以直接復制到自己的測試庫里跑跟著看效果。3. INSERT插入語句向表里喂數(shù)據(jù)的幾種姿勢3.1 基礎單行插入INSERT最基礎的語法是INSERT INTO 表名 (字段1, 字段2, ...) VALUES (值1, 值2, ...);舉個例子向?qū)W生表插入一條記錄INSERT INTO student_info (name, age, gender, class_id, score) VALUES (張三, 20, 1, 1, 588.50);這里有幾個細節(jié)值得注意。字段列表可以不寫全沒寫的字段會使用默認值比如enroll_time沒寫就自動取當前時間id沒寫就自動生成自增值。但如果你把某個NOT NULL字段漏掉了并且該字段沒有默認值MySQL會直接報錯。所以新手在一開始就要養(yǎng)成“寫INSERT時明確字段列表”的習慣這比簡寫成INSERT INTO student_info VALUES (...)要穩(wěn)健得多因為后者要求值的數(shù)量和順序必須和表結(jié)構(gòu)完全一致表結(jié)構(gòu)一變你的SQL就崩了。字段的順序可以按自己的意愿排列只要值和字段一一對應就行。比如我把年齡和班級ID換一下順序也完全沒問題只要VALUES里跟著換就行。3.2 多行插入與SET寫法多行插入是MySQL一個很實用的特性一次插入多條記錄時性能會比循環(huán)執(zhí)行單條INSERT好很多。語法就是在VALUES后面用逗號分隔多組值INSERT INTO student_info (name, age, gender, class_id, score) VALUES (李四, 21, 1, 1, 552.00), (王五, 19, 2, 2, 621.50), (趙六, 22, 1, 3, 487.00), (錢七, 20, 2, 2, 599.00);執(zhí)行完這條語句我們可以順帶用SELECT驗證一下SELECT * FROM student_info;除了VALUES寫法MySQL還支持SET形式的插入INSERT INTO student_info SET name 孫八, age 18, gender 2, class_id 3;這種寫法更直觀尤其適合一次只插入一條記錄的場景寫起來不像VALUES那樣容易看花眼。但它的缺點是不太方便插入多行。所以我的建議是單條插入用SET批量插入用VALUES多行這個習慣可以一直保持下去。3.3 INSERT ... SELECT把查詢結(jié)果灌進表里有些場景下你需要把另一張表或者同一張表的某些行的數(shù)據(jù)復制到目標表里。這時候INSERT ... SELECT就是效率最高的方式不需要先把數(shù)據(jù)查出來再手動拼INSERT。-- 把班級ID為2的學生復制到班級ID為3 INSERT INTO student_info (name, age, gender, class_id, score) SELECT name, age, gender, 3, score FROM student_info WHERE class_id 2;這里有一個很重要的點SELECT子句返回的列數(shù)和類型必須和INSERT后面的字段列表匹配否則MySQL會報“Column count doesnt match value count”的錯誤。另外需要注意如果原表里有自增主鍵你復制數(shù)據(jù)時可以不把主鍵復制過去讓目標表自動生成新的主鍵。這種寫法在實際業(yè)務里特別常用比如你要把“舊表數(shù)據(jù)遷移到新表”“把正式環(huán)境的數(shù)據(jù)抽一份到測試環(huán)境”用一條SQL就搞定了。我在實際項目里遇到過好幾次因為人為復制數(shù)據(jù)導致的錯誤用INSERT ... SELECT配合WHERE條件反而更可控。3.4 主鍵沖突處理INSERT IGNORE和ON DUPLICATE KEY UPDATE給表里插入數(shù)據(jù)時最頭疼的問題之一就是主鍵沖突。比如你想插入一個ID1的學生但表里已經(jīng)有ID1的數(shù)據(jù)了直接執(zhí)行INSERT會報錯。解決辦法有三種。第一種是INSERT IGNORE遇到主鍵或唯一鍵沖突就自動跳過這條數(shù)據(jù)不報錯INSERT IGNORE INTO student_info (id, name, age) VALUES (1, 張三, 20);第二種是ON DUPLICATE KEY UPDATE它的邏輯是如果主鍵沖突了就執(zhí)行后面的更新操作不沖突才執(zhí)行插入。舉個實際用法INSERT INTO student_info (id, name, age, score) VALUES (1, 張三, 20, 590.00) ON DUPLICATE KEY UPDATE age VALUES(age), score VALUES(score);這里VALUES(age)和VALUES(score)指的是你INSERT語句中試圖插入的值。執(zhí)行效果是ID1的記錄存在則把age更新為20、score更新為590.00如果不存在就正常插入完整的記錄。這在做數(shù)據(jù)同步、每日批處理任務時幾乎是標配寫法。第三種是REPLACE INTO這種方式的邏輯是先嘗試插入如果遇到主鍵沖突就先刪除原來的行再插入新行。它看起來和ON DUPLICATE KEY UPDATE有點像但執(zhí)行代價更高因為刪除和重新插入會導致自增主鍵發(fā)生變化。除非你能接受這一點否則我不推薦新手習慣性用它。我在多個項目里都見過因為用REPLACE導致數(shù)據(jù)被重建、外鍵關聯(lián)出問題的案例盡量用INSERT ... ON DUPLICATE KEY UPDATE才是更穩(wěn)的選擇。3.5 INSERT相關的常見坑插入數(shù)據(jù)時最容易出問題的幾個點我單獨列出來字符串和日期必須加引號。數(shù)值類型可以裸寫但字符串和日期不加引號時MySQL會嘗試把它解析成數(shù)值或報錯。比如插入abc不寫引號直接就炸了。NULL和空字符串是兩回事。NULL表示未知表示空字符串。如果字段設置了NOT NULL插入NULL會報錯但插入不會。自增列不要手動賦值。除非你清楚自己在做什么否則讓MySQL自己生成主鍵就好。手動插入一個很大的ID可能會把自增序列搞得混亂。超長字段值會被截斷或報錯。MySQL在嚴格模式下8.0默認開啟會直接報錯非嚴格模式下會靜默截斷這也是很多“數(shù)據(jù)看起來不對”的根源。判斷當前模式可以用SELECT sql_mode;4. UPDATE更新語句改數(shù)據(jù)之前先想清楚4.1 基本UPDATE語法和條件過濾UPDATE的基本語法UPDATE 表名 SET 字段1 值1, 字段2 值2 WHERE 條件;比如把張三的年齡改成21UPDATE student_info SET age 21 WHERE name 張三;這里要單獨講講WHERE子句的重要性。UPDATE不帶WHERE就是對整張表的所有行執(zhí)行更新。有一次我在測試環(huán)境執(zhí)行UPDATE student_info SET score 600結(jié)果是全表學生的成績?nèi)兂?00分。在測試環(huán)境可能一笑了之如果在生產(chǎn)環(huán)境后果不堪設想。所以每次寫UPDATE都可以在心里默念一遍我的WHERE真的能精準圈定目標數(shù)據(jù)嗎建議在更新前先跑一遍同條件的SELECT確認影響范圍SELECT * FROM student_info WHERE name 張三;然后再放心執(zhí)行UPDATE。這個習慣一點都不麻煩但能省下無數(shù)后悔藥。4.2 關聯(lián)更新多表UPDATE有些場景下你要根據(jù)另一張表的數(shù)據(jù)來更新當前表。比如班級改名后你想把學生表里所有該班級學生的班級名稱同步更新就可以用多表UPDATEUPDATE student_info s JOIN class_info c ON s.class_id c.id SET s.class_id 2 WHERE c.class_name 計算機科學2班;這里JOIN是用來關聯(lián)兩張表的它的核心邏輯是先找出班級名為“計算機科學2班”的班級ID然后更新學生表中對應的行。這種寫法的好處是省去了子查詢讓SQL更清晰。更常見的一種寫法是利用子查詢UPDATE student_info SET score score 10 WHERE class_id IN (SELECT id FROM class_info WHERE class_name LIKE %計算機%);這句話會把計算機相關班級的所有學生成績加10分。注意MySQL里更新同一張表時子查詢直接引用同一張表可能會報“You cant specify target table for update in FROM clause”的錯誤這個后面在常見問題里我會詳細說解法。4.3 UPDATE時容易被忽略的細節(jié)第一字段值的合法性。UPDATE不會自動檢查你新賦的值是否合理比如把年齡改成-5MySQL不會攔你除非你建了CHECK約束。業(yè)務上的校驗應該由應用層負責或者在建表時加合理的CHECK約束。第二數(shù)據(jù)類型的隱式轉(zhuǎn)換。如果字段是字符串類型你給它賦一個數(shù)值MySQL會嘗試自動轉(zhuǎn)換雖然一般不報錯但可能導致索引失效查詢變慢。所以寫UPDATE時盡量保持值的類型和字段類型一致。第三唯一鍵沖突。更新時如果新值導致唯一鍵重復MySQL會報錯。比如你把某個學生的姓名改成和另一個學生相同而姓名列設置了UNIQUE約束這條UPDATE就會失敗。5. DELETE刪除語句刪數(shù)據(jù)其實是門技術活5.1 DELETE基本語法與注意事項DELETE的基本語法DELETE FROM 表名 WHERE 條件;比如刪除張三DELETE FROM student_info WHERE name 張三;同樣DELETE不帶WHERE就是清空整張表。而且DELETE和UPDATE還有一個區(qū)別有些數(shù)據(jù)庫系統(tǒng)對于不帶WHERE的DELETE會要求額外的權(quán)限或確認但MySQL默認不會攔你所以誤刪的可能性更高。我的建議是刪除前先SELECT確認刪除時盡量用主鍵或唯一鍵來圈定范圍不要只憑姓名這種可能重復的字段來刪。5.2 DELETE和TRUNCATE的區(qū)別很多新手問清空表的時候用DELETE還是TRUNCATE這兩者的核心區(qū)別如下對比維度DELETETRUNCATE操作類型DML逐行刪除DDL重建表結(jié)構(gòu)是否可帶WHERE可以按條件刪不可以直接清空執(zhí)行速度慢逐行操作并寫日志快直接釋放數(shù)據(jù)頁自增ID是否重置不重置繼續(xù)往下增長重置從1開始是否可以回滾在事務中可以默認不可回滾場景上如果只是刪掉部分數(shù)據(jù)必然用DELETE如果要清空整張臨時表并且不需要保留自增ID的連續(xù)性TRUNCATE更合適。需要注意TRUNCATE會觸發(fā)MySQL的隱式提交即使你把它包在事務里也回滾不了這一點一定要記牢。5.3 多表刪除同時刪兩張表的數(shù)據(jù)需要同時刪除多個表的數(shù)據(jù)時多表DELETE就派上用場了。比如刪除班級ID為3的班級同時刪除該班級下的所有學生DELETE s, c FROM student_info s JOIN class_info c ON s.class_id c.id WHERE c.id 3;這里要注意DELETE后面跟的是表別名表示你要刪的是哪幾個表的數(shù)據(jù)而不是刪JOIN的結(jié)果。如果只寫DELETE FROM student_info那就只刪學生表班級表還得再寫一條SQL。實際開發(fā)中多表刪除要特別謹慎尤其是存在外鍵關系時。如果表之間設置了ON DELETE CASCADE刪父表記錄時子表會自動刪如果沒有外鍵約束或者約束方式是RESTRICT刪父表會報錯得手動刪完子表再刪父表。5.4 誤刪除的補救思路雖然不想嚇唬人但每個DBA和開發(fā)者都遲早會遇到誤刪數(shù)據(jù)的情況。簡單說幾個補救方向。如果MySQL開啟了binlog可以通過binlog日志把刪除操作找回來這是比較標準的恢復手段。測試環(huán)境下很多實例默認沒開binlog生產(chǎn)環(huán)境一般都會開。如果你已經(jīng)把DELETE包在事務里并且還沒COMMIT直接ROLLBACK就能恢復。日常預防上可以給重要的表建定時備份任務甚至用軟刪除加一個is_deleted字段來代替物理刪除。軟刪除在業(yè)務系統(tǒng)里非常常見避免了數(shù)據(jù)真正丟失帶來的風險。另外MySQL 8.0里有UNDO日志保留時間的配置刪除的數(shù)據(jù)在短時間內(nèi)還是可恢復的但不要指望它最快最省事的還是“提前備份謹慎操作”。6. SELECT查詢語句DML里真正的主角6.1 基礎查詢與過濾排序SELECT的語法比前面三個都復雜因為它承載了最豐富的查詢需求?；窘Y(jié)構(gòu)是SELECT 列名 FROM 表名 WHERE 條件 GROUP BY 列 HAVING 條件 ORDER BY 列 LIMIT 數(shù)量;先看最基礎的用法-- 查詢所有學生 SELECT * FROM student_info; -- 只查姓名和成績 SELECT name, score FROM student_info; -- 按成績降序排列 SELECT name, score FROM student_info ORDER BY score DESC;ORDER BY默認為升序如果要降序要顯式加DESC。多字段排序時寫在前面的優(yōu)先級高。比如先按班級排序再按成績降序SELECT name, class_id, score FROM student_info ORDER BY class_id ASC, score DESC;6.2 條件過濾WHERE的進階玩法WHERE子句里除了、、、這些比較運算符還有一些很常用的寫法。-- 范圍查詢 SELECT * FROM student_info WHERE score BETWEEN 500 AND 600; -- 列表查詢 SELECT * FROM student_info WHERE class_id IN (1, 2, 3); -- 模糊查詢名字以張開頭 SELECT * FROM student_info WHERE name LIKE 張%; -- 空值判斷注意IS NULL不能寫成 NULL SELECT * FROM student_info WHERE class_id IS NULL;模糊查詢中%表示任意多個字符包括0個_表示任意單個字符。比如LIKE 張_只能匹配“張”加一個字的名字如“張三”匹配不了“張大壯”。這個細節(jié)在面試里也?？?。還有一個新手容易踩的坑NULL的判斷。NULL不是等于某個值也不是不等于某個值它是“未知”。所以任何 NULL的寫法都查詢不到數(shù)據(jù)只能用IS NULL或者IS NOT NULL。6.3 聚合函數(shù)與分組統(tǒng)計聚合函數(shù)是SELECT里非常核心的一塊。它把多行的值匯總成一個值常用的有COUNT、SUM、AVG、MAX、MIN。比如統(tǒng)計學生人數(shù)和平均分SELECT COUNT(*) AS total_count, AVG(score) AS avg_score FROM student_info;COUNT(*)統(tǒng)計的是所有行數(shù)包括值為NULL的行COUNT(score)只統(tǒng)計score列非空的記錄數(shù)。這個區(qū)別如果沒搞清楚統(tǒng)計結(jié)果可能會有偏差。分組統(tǒng)計要配合GROUP BY比如按班級統(tǒng)計人數(shù)和平均分SELECT class_id, COUNT(*) AS num, AVG(score) AS avg_score FROM student_info GROUP BY class_id;這里很自然地引出一個問題如果你想在分組之后再加篩選條件不能用WHERE而要用HAVING。比如只想看平均分大于550的班級SELECT class_id, AVG(score) AS avg_score FROM student_info GROUP BY class_id HAVING avg_score 550;WHERE和HAVING的區(qū)別可以簡單理解成WHERE在分組前過濾原始行HAVING在分組后過濾聚合結(jié)果。6.4 多表連接查詢JOIN的用法數(shù)據(jù)很少只存在一張表里實際業(yè)務里絕大多數(shù)查詢都要關聯(lián)多張表。最常用的是INNER JOIN它返回兩張表中滿足關聯(lián)條件的交集數(shù)據(jù)。SELECT s.name, c.class_name FROM student_info s INNER JOIN class_info c ON s.class_id c.id;這里INNER可以省略不寫直接寫JOIN效果一樣。ON后面寫的是關聯(lián)條件它決定了兩個表怎么拼在一起。LEFT JOIN則會把左表的所有數(shù)據(jù)都查出來即使右表沒有匹配的記錄右表字段會顯示為NULL。SELECT s.name, c.class_name FROM student_info s LEFT JOIN class_info c ON s.class_id c.id;LEFT JOIN在開發(fā)中使用頻率非常高比如你要查“每個學生的班級名即使這個學生沒有分配班級也要列出來”就必須用LEFT JOININNER JOIN會直接把沒分班的學生過濾掉。這兩種JOIN的區(qū)別一定要弄透面試和實際開發(fā)里都是重中之重。6.5 分頁查詢LIMIT的實用套路分頁查詢是列表頁功能的基本功。MySQL用LIMIT實現(xiàn)SELECT * FROM student_info ORDER BY id LIMIT 10 OFFSET 0;OFFSET 0表示從第1行開始取10行OFFSET 10就是從第11行開始。也可以簡寫成SELECT * FROM student_info ORDER BY id LIMIT 10, 10;這個寫法中LIMIT 10, 10的第一個10是偏移量第二個10是返回的行數(shù)。很多新手會記反所以建議直接用LIMIT 頁大小 OFFSET 偏移量這種寫法語義更清晰。分頁一定要配ORDER BY否則每次查出來的順序可能不一樣導致翻頁時數(shù)據(jù)出現(xiàn)重復或漏掉。7. 實戰(zhàn)小案例用DML完成一個完整需求把前面的知識串起來我們做一個模擬需求把計算機科學2班中成績低于550分的學生全部加10分然后統(tǒng)計各班人數(shù)和平均分。第一步先查詢一下目標數(shù)據(jù)看看影響范圍SELECT s.id, s.name, s.score, c.class_name FROM student_info s JOIN class_info c ON s.class_id c.id WHERE c.class_name 計算機科學2班 AND s.score 550;第二步執(zhí)行更新UPDATE student_info SET score score 10 WHERE class_id IN (SELECT id FROM class_info WHERE class_name 計算機科學2班) AND score 550;這里我想專門解釋一下最容易出問題的地方如果你直接用JOIN的方式更新就把目標表也關聯(lián)進去MySQL可能報錯用子查詢時如果子查詢和更新目標表是同一張表也會出現(xiàn)限制。上面的寫法是先把class_info查出來再更新student_info沒有直接引用被更新的表所以能正常執(zhí)行這是我實際開發(fā)中被MySQL坑過之后總結(jié)出來的穩(wěn)妥寫法。第三步驗證結(jié)果SELECT c.class_name, COUNT(*) AS student_num, AVG(s.score) AS avg_score FROM student_info s JOIN class_info c ON s.class_id c.id GROUP BY c.class_name;這個流程看著簡單但非常接近真實業(yè)務的處理邏輯先查再改再統(tǒng)計。每一步都在用前面講過的知識這就是DML的日常形態(tài)。8. 常見問題速查表與避坑指南8.1 高頻報錯與解決方法問題現(xiàn)象報錯信息原因解決方法插入數(shù)據(jù)時提示列數(shù)不匹配Column count doesnt match value countVALUES里的數(shù)量和字段列表數(shù)量不一致檢查字段列表和值列表一一對應字符串忘記加引號Unknown column abc字符串被當成列名解析字符串和日期必須加單引號更新同一張表時子查詢報錯You cant specify target table for update in FROM clauseMySQL不允許同一張表作為更新目標和子查詢來源子查詢外面再套一層臨時表或用JOIN使用保留字當表名/列名You have an error in your SQL syntaxorder、group等詞是保留字加反引號或者換個名字中文亂碼顯示為問號字符集不一致建表用utf8mb4連接串加characterEncodingutf8查詢結(jié)果NULL判斷不出查不到數(shù)據(jù)用了 NULL改用IS NULL或IS NOT NULL刪數(shù)據(jù)沒有生效Rows matched: 0 Changed: 0WHERE條件沒有匹配到數(shù)據(jù)先SELECT驗證條件是否成立8.2 新手最容易踩的5個坑第一個坑是UPDATE和DELETE忘了寫WHERE或者WHERE寫得過于寬松。我見過因為一個WHERE id 0導致全表數(shù)據(jù)被改寫的案例這種錯誤輕則數(shù)據(jù)錯亂重則直接刪庫跑路。第二個坑是混淆了和IS對NULL的判斷。NULL NULL的結(jié)果是NULL所以條件不成立。所有對NULL的判斷都必須用IS NULL或IS NOT NULL。第三個坑是在多表JOIN時搞不清驅(qū)動表和匹配表的角色。LEFT JOIN左邊的表是驅(qū)動表它會全量保留右邊是匹配表匹配不到就補NULL。腦子里有這層概念寫起多表查詢才不會邏輯混亂。第四個坑是分頁越翻越亂。沒有ORDER BY的LIMIT分頁MySQL返回的記錄順序是不確定的尤其在數(shù)據(jù)量大的時候翻頁會出現(xiàn)重復數(shù)據(jù)。分頁時一定要指定唯一性強的排序字段。第五個坑是執(zhí)行完DML后忘了提交事務。如果你在代碼里開啟了事務但忘記COMMIT數(shù)據(jù)在別人眼里一直是舊狀態(tài)這種現(xiàn)象在排查“為什么數(shù)據(jù)改了前端沒變化”的問題時特別容易遇到。8.3 我的一點小建議既然看到這里了最后分享一個我用了很多年的習慣每次寫DML尤其是UPDATE和DELETE都強制自己先寫一條同名SELECT大概花兩三秒的時間確認WHERE條件圈定的范圍再回頭執(zhí)行。這個習慣在壓力大、事情多的時候特別能救命。另外測試環(huán)境里練SQL時可以隨便造一些垃圾數(shù)據(jù)反復試把報錯都見一遍反而到了生產(chǎn)環(huán)境就不慌了。我自己剛學MySQL那會兒就是把一份數(shù)據(jù)翻來覆去地插入、更新、刪除、再查回來折騰到最后四條命令的各種邊緣情況基本都摸清了。后續(xù)如果你想深入可以再往事務、鎖、索引優(yōu)化這些方向走但DML這關打得越扎實后面這些東西學起來就越順暢。