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