訓(xùn)答案速查：從建表到查詢(xún)的避坑指南)
簡(jiǎn)介這是一份針對(duì)頭歌平臺(tái)MySQL數(shù)據(jù)庫(kù)實(shí)訓(xùn)的全流程答案整理面向正在完成數(shù)據(jù)庫(kù)定義、表操作、約束應(yīng)用等關(guān)卡的學(xué)習(xí)者也適合需要快速鞏固數(shù)據(jù)庫(kù)基礎(chǔ)、備戰(zhàn)實(shí)訓(xùn)考試的高校學(xué)生。內(nèi)容按實(shí)訓(xùn)模塊逐關(guān)編排既有創(chuàng)建數(shù)據(jù)庫(kù)、創(chuàng)建數(shù)據(jù)表等入門(mén)操作也覆蓋主鍵、外鍵、唯一、默認(rèn)等約束的設(shè)置以及查看表結(jié)構(gòu)、修改表名、調(diào)整字段類(lèi)型與位置、增刪字段、插入更新數(shù)據(jù)等高頻場(chǎng)景每步配有可直接執(zhí)行的數(shù)據(jù)庫(kù)語(yǔ)句方便對(duì)照驗(yàn)證、快速定位錯(cuò)誤。正文以代碼塊形式組織命令前均標(biāo)注對(duì)應(yīng)關(guān)卡遇到報(bào)錯(cuò)時(shí)可逐條核對(duì)減少試錯(cuò)成本。資源包共包含一個(gè)PDF格式文件大小約四百三十三KB文件帶有清晰目錄可依據(jù)關(guān)卡編號(hào)直接跳轉(zhuǎn)查閱。目前已有一萬(wàn)八千三百六十九人學(xué)習(xí)下載對(duì)初學(xué)者和需要高效完成實(shí)訓(xùn)任務(wù)的學(xué)習(xí)者都有較強(qiáng)的參考價(jià)值。1. MySQL實(shí)訓(xùn)答案一份能跑通但別盲抄的速查手冊(cè)MySQL 數(shù)據(jù)庫(kù)實(shí)訓(xùn)向來(lái)不缺題目缺的是能一次跑通的答案。這份《頭歌MySQL數(shù)據(jù)庫(kù)實(shí)訓(xùn)答案》PDF 把建庫(kù)、建表、約束、增刪改查、連接查詢(xún)、子查詢(xún)、聚合函數(shù)、視圖、索引、存儲(chǔ)過(guò)程按關(guān)卡拆成了可直接粘貼的 SQL 片段適合正在趕實(shí)訓(xùn)進(jìn)度、又不想在環(huán)境配置上耗時(shí)間的從業(yè)者和學(xué)生。但我的建議是答案可以抄抄完必須做兩件事——理解每條語(yǔ)句的邊界條件再在本地用同樣的表結(jié)構(gòu)重跑一遍。這份資源解決的是「卡在第幾步、語(yǔ)法怎么拼、表名哪里搞錯(cuò)」這類(lèi)具體問(wèn)題而不是替你學(xué)會(huì) SQL。如果你只是想要一份按目錄檢索的參考手冊(cè)它比翻文檔快得多如果你指望粘貼完就徹底掌握那大概率會(huì)在第二個(gè)項(xiàng)目里翻車(chē)。2. 建庫(kù)建表與約束把 DDL 練成肌肉記憶的三類(lèi)寫(xiě)法2.1 最小可跑通的建庫(kù)建表從零到 t_emp實(shí)訓(xùn)里的每個(gè)新項(xiàng)目幾乎都以這三步開(kāi)場(chǎng)登錄、建庫(kù)、建表。資源里這條命令序列是整套答案里出現(xiàn)頻率最高的骨架。# 登錄實(shí)訓(xùn)沙箱 MySQL 服務(wù)-u 指定用戶(hù)-p 帶出密碼-h 指定主機(jī) mysql -uroot -p123123 -h127.0.0.1use MyDb; # 切換到 MyDb 數(shù)據(jù)庫(kù)后續(xù)建表都建在這個(gè)庫(kù)里 create table t_emp ( # 建員工表四個(gè)字段按題目順序排列 id int, # 整型員工編號(hào) name varchar(32), # 變長(zhǎng)字符串最多存 32 個(gè)字符 deptId int, # 部門(mén)編號(hào) salary float # 浮點(diǎn)型工資 );第一段是命令行登錄參數(shù)-u root指定用戶(hù)名為 root-p123123是密碼直接跟在 p 后面中間不留空格-h127.0.0.1表示連本機(jī)。實(shí)訓(xùn)沙箱里這套參數(shù)是固定的本地環(huán)境直接換密碼即可。第二段是建表語(yǔ)句varchar(32)不是固定占 32 字節(jié)而是最多存 32 個(gè)字符超過(guò)會(huì)截?cái)鄐alary float用浮點(diǎn)存工資實(shí)訓(xùn)里夠用但真實(shí)業(yè)務(wù)我一般用decimal(10,2)避免精度漂移。建表后第一時(shí)間執(zhí)行desc t_emp;或者show create table t_emp;看字段順序和類(lèi)型。實(shí)訓(xùn)評(píng)分經(jīng)常比對(duì)查詢(xún)結(jié)果的列順序id, name, deptId, salary和你寫(xiě)成id, salary, name, deptId在后面的查詢(xún)關(guān)卡里可能結(jié)果內(nèi)容一樣但列頭對(duì)不上評(píng)分腳本直接判定失敗。2.2 主鍵與外鍵單列、復(fù)合和命名約束的選擇主鍵約束在這份資源里出現(xiàn)了三次不同寫(xiě)法分別對(duì)應(yīng)單列主鍵、復(fù)合主鍵和外鍵引用。先看前兩種# 單列主鍵直接在列定義后面聲明 PRIMARY KEY create table t_user1( userId INT PRIMARY KEY, # 用戶(hù)編號(hào)作為唯一標(biāo)識(shí) name VARCHAR(32), password VARCHAR(11), phone VARCHAR(11), email VARCHAR(32) ); # 復(fù)合主鍵把 name 和 phone 組合成一個(gè)整體唯一標(biāo)識(shí) create table t_user2( name VARCHAR(32), phone VARCHAR(11), email VARCHAR(32), PRIMARY KEY(name, phone) # 兩列組合值必須唯一 );單列主鍵適合一張表里已經(jīng)有一個(gè)天然唯一字段的場(chǎng)景比如用戶(hù)編號(hào)。復(fù)合主鍵則用在單列都不唯一、但兩列組合才能唯一的情況。注意復(fù)合主鍵的判定規(guī)則name單獨(dú)可以重復(fù)phone單獨(dú)也可以重復(fù)但(name, phone)這對(duì)組合不能重復(fù)。實(shí)訓(xùn)里檢查你寫(xiě)得對(duì)不對(duì)用desc看 Key 列PRI 出現(xiàn)在哪一行、出現(xiàn)幾次立刻就知道是單列還是復(fù)合。外鍵約束才是這一節(jié)的重點(diǎn)資源里給的標(biāo)準(zhǔn)寫(xiě)法長(zhǎng)這樣# 先建父表 t_class CREATE TABLE t_class( id INT PRIMARY KEY, name VARCHAR(22) ); # 再建子表 t_studentclassId 引用 t_class 的 id CREATE TABLE t_student( id INT PRIMARY KEY, name VARCHAR(22), classId int, CONSTRAINT fk_stu_class1 FOREIGN KEY(classId) REFERENCES t_class(id) );這里CONSTRAINT fk_stu_class1是給外鍵起名后面FOREIGN KEY(classId) REFERENCES t_class(id)聲明子表 classId 引用父表 id。實(shí)訓(xùn)環(huán)境里最常翻車(chē)的點(diǎn)有兩個(gè)一是執(zhí)行順序顛倒必須先建父表再建子表反過(guò)來(lái)直接報(bào)「無(wú)法創(chuàng)建外鍵約束」二是兩側(cè)字段類(lèi)型必須完全一致t_class.id是 INTt_student.classId也必須是 INT寫(xiě)成 VARCHAR 或漏掉類(lèi)型都會(huì)失敗。2.3 添加常用約束非空、唯一、默認(rèn)值與字符集這一關(guān)的建表語(yǔ)句把實(shí)訓(xùn)要求的大部分約束一次集齊了CREATE TABLE t_user( id INT PRIMARY KEY AUTO_INCREMENT, # 主鍵自增插入時(shí)不需手動(dòng)給id username VARCHAR(32) NOT NULL UNIQUE, # 用戶(hù)名非空且唯一 sex VARCHAR(4) DEFAULT 男 # 不傳 sex 時(shí)默認(rèn)填“男” ) DEFAULT CHARSETutf8; # 表級(jí)字符集中文不亂碼AUTO_INCREMENT讓主鍵從 1 開(kāi)始自動(dòng)累加插入時(shí)不需要寫(xiě) id 字段。NOT NULL UNIQUE同時(shí)限制了非空和唯一兩項(xiàng)規(guī)則只要有一條記錄插入了 NULL 或重復(fù)的 username整條 INSERT 直接報(bào)錯(cuò)。DEFAULT 男是列級(jí)默認(rèn)值INSERT語(yǔ)句沒(méi)給 sex 字段時(shí)自動(dòng)補(bǔ)上。注意DEFAULT CHARSETutf8這一句經(jīng)常被忽略。實(shí)訓(xùn)平臺(tái)大多數(shù)默認(rèn) utf8mb4顯式寫(xiě) utf8 也能過(guò)但你本地導(dǎo)數(shù)據(jù)到其他庫(kù)時(shí)字符集不一致會(huì)導(dǎo)致中文變成問(wèn)號(hào)。我的習(xí)慣是建完表立刻執(zhí)行show create table t_user;重點(diǎn)看三處ENGINE 是不是 InnoDBCHARSET 是不是 utf8id 的 Extra 列是不是 auto_increment。這三個(gè)屬性決定了后續(xù)外鍵、自增、中文查詢(xún)能不能正常跑。2.4 表結(jié)構(gòu)維護(hù)ALTER TABLE 五類(lèi)高頻操作項(xiàng)目進(jìn)行到一半發(fā)現(xiàn)表結(jié)構(gòu)不對(duì)就要靠ALTER TABLE補(bǔ)救。資源里覆蓋了改名、改字段名、改類(lèi)型、增刪列、移動(dòng)位置我把這幾條命令理到一起ALTER TABLE tb_emp RENAME jd_emp; # 改表名 ALTER TABLE tb_emp change Id prod_id int(11); # 改列名同時(shí)改類(lèi)型 ALTER TABLE tb_emp MODIFY Name varchar(30); # 只改列類(lèi)型 ALTER TABLE tb_emp ADD Country varchar(20) AFTER Name; # 新增列并指定位置 ALTER TABLE tb_emp DROP Salary; # 刪除列 ALTER TABLE tb_emp MODIFY DeptId int(11) AFTER Salary; # 移動(dòng)列位置CHANGE和MODIFY的差別是這組命令里最值得記的對(duì)比點(diǎn)語(yǔ)句能改列名能改類(lèi)型適用場(chǎng)景CHANGE可以可以列改名加類(lèi)型調(diào)整MODIFY不可以可以只改類(lèi)型或移動(dòng)位置CHANGE后面必須帶著完整的列定義老名字和新名字都要寫(xiě)int(11)不能省MODIFY只需寫(xiě)列名加新類(lèi)型。ADD ... AFTER Name把新列插到 Name 后面FIRST則把列移到第一個(gè)位置。DROP Salary刪除列沒(méi)有后悔藥實(shí)訓(xùn)沙箱里刪完想恢復(fù)只能重新建表再插數(shù)據(jù)。提示多條 ALTER 語(yǔ)句不要擠在一個(gè)執(zhí)行框里連寫(xiě)。實(shí)訓(xùn)評(píng)分按行解析漏一個(gè)分號(hào)會(huì)把后面的語(yǔ)句當(dāng)成上一條的從句報(bào)語(yǔ)法錯(cuò)誤卻不告訴你錯(cuò)在哪一行。我一般每條 ALTER 單獨(dú)執(zhí)行確認(rèn)DESC結(jié)果符合預(yù)期再寫(xiě)下一條。3. 數(shù)據(jù)操作與單表查詢(xún)?cè)鰟h改查的四個(gè)邊界條件3.1 批量插入一條 INSERT 寫(xiě)多行表結(jié)構(gòu)就緒后的第一件事是造數(shù)據(jù)。資源里的批量插入示例非常典型# 一次插入三行員工記錄字段順序與表結(jié)構(gòu)保持一致 INSERT INTO tb_emp(Id,Name,DeptId,Salary) VALUES (1,Nancy,301,2300.00), (2,Tod,303,5600.00), (3,Carly,301,3200.00);一條 INSERT 后跟多組 VALUES每組用括號(hào)包住、逗號(hào)分隔這是標(biāo)準(zhǔn)的批量寫(xiě)入寫(xiě)法。字段列表(Id,Name,DeptId,Salary)限定了每組值的順序多寫(xiě)或少寫(xiě)一個(gè)值都會(huì)報(bào)「Column count doesnt match value count」。評(píng)分會(huì)統(tǒng)計(jì)表內(nèi)的總行數(shù)少插一條就少拿一分所以提交前用select count(*) from tb_emp;對(duì)比一下。這里有個(gè)容易被忽視的坑字符串用雙引號(hào)還是單引號(hào)。MySQL 默認(rèn)兩種都能識(shí)別但實(shí)訓(xùn)平臺(tái)開(kāi)啟了 ANSI_QUOTES 模式時(shí)雙引號(hào)會(huì)被當(dāng)成標(biāo)識(shí)符而不是字符串同樣的語(yǔ)句直接報(bào)錯(cuò)。穩(wěn)妥做法是統(tǒng)一用單引號(hào)這也是 SQL 標(biāo)準(zhǔn)推薦寫(xiě)法。3.2 更新與刪除WHERE 條件決定影響范圍數(shù)據(jù)插進(jìn)去之后實(shí)訓(xùn)馬上會(huì)考改數(shù)據(jù)。資源里這兩條語(yǔ)句看似簡(jiǎn)單邊界條件卻很值得拆開(kāi)看# 只更新 id3 的那一行SET 可以同時(shí)改多個(gè)字段 UPDATE tb_emp SET NameTracy, DeptId302, Salary4300.00 WHERE id3; # 刪除工資大于 3000 的所有記錄 DELETE FROM tb_emp WHERE Salary3000;UPDATE 的三個(gè)賦值之間用逗號(hào)分隔最后用 WHERE 限定行。這里如果把 WHERE 漏掉整張表所有行都會(huì)被改成同一組值在實(shí)訓(xùn)里可能顯示不出來(lái)但數(shù)據(jù)全毀了。DELETE 同樣依賴(lài) WHERE 控制刪除范圍Salary3000刪掉高工資Salary0則等于清空全表。真實(shí)業(yè)務(wù)里一條不帶 WHERE 的 DELETE 甚至可能觸發(fā)數(shù)據(jù)庫(kù)的批量刪除保護(hù)機(jī)制直接拒絕執(zhí)行。我的操作習(xí)慣是寫(xiě) UPDATE 或 DELETE 前先跑一遍同樣 WHERE 條件的 SELECT-- 先看會(huì)命中哪些行 SELECT * FROM tb_emp WHERE Salary3000;確認(rèn)結(jié)果里就是要?jiǎng)h的那幾條再執(zhí)行 DELETE。這個(gè)習(xí)慣在實(shí)訓(xùn)里能幫你提前發(fā)現(xiàn)條件寫(xiě)反在真實(shí)項(xiàng)目里能避免刪錯(cuò)數(shù)據(jù)。3.3 單表查詢(xún)IN、BETWEEN、LIKE、IS NULL 的判定細(xì)節(jié)查詢(xún)語(yǔ)句是實(shí)訓(xùn)占比最大的一塊。資源里四個(gè)關(guān)鍵字查詢(xún)放在一起剛好覆蓋最常見(jiàn)的判定邊界# NOT INid 不是 1 的所有員工 SELECT Name,Salary FROM tb_emp WHERE Id NOT IN (1); # BETWEEN 是閉區(qū)間包含 3000 和 5000 兩條邊界 SELECT Name,Salary FROM tb_emp WHERE Salary BETWEEN 3000 AND 5000; # LIKE 的 % 是任意長(zhǎng)度通配符C% 表示以 C 開(kāi)頭 SELECT Name,Salary FROM tb_emp WHERE Name LIKE C%; # NULL 只能 IS NULL 判斷寫(xiě)成 NULL 永遠(yuǎn)查不到數(shù)據(jù) SELECT * FROM tb_emp WHERE DeptId IS NULL;IN的列表里寫(xiě)的是 id1NOT IN取反返回所有 id 不是 1 的行。BETWEEN 3000 AND 5000是閉區(qū)間等價(jià)于3000 AND 5000。LIKE C%只匹配以 C 開(kāi)頭的名字%C%表示包含 CC_表示 C 后面正好一個(gè)字符。IS NULL專(zhuān)門(mén)查空值新手最常見(jiàn)的錯(cuò)誤是寫(xiě)DeptId NULL這個(gè)表達(dá)式的結(jié)果永遠(yuǎn)是 NULL不會(huì)返回任何行必須用IS NULL或IS NOT NULL。提示SQL 里 NULL 不是值而是「未定義」。任何 NULL 參與的等值比較結(jié)果都不是 true也不是 false而是未知。這就是為什么NULL查不出東西。去重也是一個(gè)高頻考點(diǎn)# DISTINCT 作用在整行而不是單個(gè)列 SELECT DISTINCT Name FROM tb_emp;DISTINCT如果寫(xiě)成SELECT DISTINCT Name, DeptId去重的是兩列的組合。只有當(dāng)你希望基于兩列組合去重復(fù)時(shí)才這么寫(xiě)否則結(jié)果會(huì)比你預(yù)期的多。3.4 排序、分組與 LIMIT三條語(yǔ)句的配合順序查詢(xún)的進(jìn)階形態(tài)是把排序、分組、限量三個(gè)動(dòng)作組合起來(lái)。資源里這三條語(yǔ)句恰好演示了最容易搞混的兩處# 按成績(jī)降序排列DESC 從高到低ASC 從低到高 SELECT * FROM tb_score WHERE class_id 1 ORDER BY score DESC; # GROUP BY 之后 SELECT 里最好只保留分組列和聚合函數(shù) SELECT * FROM tb_class GROUP BY class_id; # LIMIT 第一個(gè)參數(shù)是偏移量0 表示第一行第二個(gè)參數(shù)是取幾行 SELECT * FROM tb_score ORDER BY score DESC LIMIT 1,4;第一條是排序ORDER BY只影響展示順序不影響返回哪些行多個(gè)排序字段用逗號(hào)分隔。第二條是分組GROUP BY class_id之后執(zhí)行SELECT *在 MySQL 5.7 里通常返回每組的第一行但這是未定義行為換了版本結(jié)果可能不同。正經(jīng)寫(xiě)法是SELECT class_id, COUNT(*) FROM tb_class GROUP BY class_id;。第三條的 LIMIT 是所有新手必踩的坑LIMIT 1,4表示從第 2 行開(kāi)始取 4 行也就是第 2 到第 5 名不是第 1 到第 4 名。LIMIT 的第一個(gè)參數(shù)是偏移量從 0 開(kāi)始計(jì)數(shù)。如果題目要「第 2 名到第 5 名」LIMIT 1,4是對(duì)的如果只是「前 4 名」要寫(xiě)LIMIT 4或LIMIT 0,4。4. 連接查詢(xún)與子查詢(xún)多表場(chǎng)景的三種查詢(xún)路徑4.1 內(nèi)連接與外連接誰(shuí)保留誰(shuí)決定方向連接查詢(xún)是實(shí)訓(xùn)里拉開(kāi)差距的關(guān)卡。資源里三個(gè)題目把內(nèi)連接、左外連接、右外連接一網(wǎng)打盡# 內(nèi)連接只返回兩邊都匹配得上的行 select tb_student.name as studentName, tb_class.name as className from tb_student join tb_class on tb_class.id tb_student.class_id; # 右外連接保留右表 tb_student 全部行 select tb_student.name as studentName, tb_class.name as className from tb_class right join tb_student on tb_class.idtb_student.class_id; # 左外連接保留左表 tb_class 全部行 select tb_student.name as studentName, tb_class.name as className from tb_class left join tb_student on tb_class.idtb_student.class_id;第一段JOIN ... ON是內(nèi)連接學(xué)生表里如果有 class_id 指向不存在的班級(jí)這一行會(huì)被過(guò)濾掉。第二段和第三段的對(duì)比很有迷惑性代碼只差一個(gè) left/right語(yǔ)義完全相反。題目要求「查詢(xún)所有學(xué)生姓名和對(duì)應(yīng)的班級(jí)」意味著必須保留學(xué)生表的全部行哪怕某個(gè)學(xué)生的班級(jí) id 在班級(jí)表里不存在。第二段用tb_class RIGHT JOIN tb_student能正確保留右表 tb_student 全部行。第三段用tb_class LEFT JOIN tb_student保留的是班級(jí)表全部行沒(méi)學(xué)生的班級(jí)也會(huì)出現(xiàn)學(xué)生字段顯示 NULL方向就反了。我的判斷方法是先問(wèn)自己結(jié)果里必須出現(xiàn)哪張表的全部行那張表就要放在「保留表」的位置。LEFT JOIN保留左表RIGHT JOIN保留右表。實(shí)訓(xùn)題目里描述是「所有學(xué)生」時(shí)優(yōu)先考慮tb_student LEFT JOIN tb_class這樣語(yǔ)義最直白。4.2 復(fù)合條件連接與子查詢(xún)比較、ALL/ANY、IN多表關(guān)聯(lián)不一定要用 JOIN 關(guān)鍵字老式逗號(hào)寫(xiě)法在實(shí)訓(xùn)題目里也很常見(jiàn)# 三表關(guān)聯(lián)連接條件放在 WHERE 里過(guò)濾條件放在 AND 后面 select s1.name as studentName, score, s2.name as className from tb_student as s1, tb_class as s2 where s1.class_id s2.id and s1.score 90 order by score desc;s1和s2是表別名寫(xiě)長(zhǎng)查詢(xún)時(shí)能省大量字符也避免同名字段歧義。這段的等價(jià)寫(xiě)法是tb_student join tb_class on tb_student.class_id tb_class.id where score 90。and s1.score90是過(guò)濾條件漏掉的話(huà) 90 分以下的學(xué)生也會(huì)混進(jìn)結(jié)果。子查詢(xún)部分最考驗(yàn)對(duì)關(guān)鍵字語(yǔ)義的理解。資源里這組代碼是典型代表# 標(biāo)量子查詢(xún)內(nèi)層返回平均年齡外層逐行比較 select name, age from tb_emp where age (select avg(age) from tb_emp); # ANY大于子查詢(xún)結(jié)果里的任意一個(gè)值即可 SELECT position,salary FROM tb_salary WHERE salary ANY(SELECT max(salary) FROM tb_salary where positionjava);第一個(gè)是標(biāo)量子查詢(xún)內(nèi)層返回一個(gè)值外層用比較運(yùn)算符和它比較邏輯直白。第二個(gè)是 ANY 的語(yǔ)義salary ANY(...)只要大于子查詢(xún)返回集合里的任意一個(gè)值就滿(mǎn)足。這里子查詢(xún)只返回一個(gè) max(salary)ANY 和直接大于最大值等價(jià)。如果把 ANY 換成 ALL表示要大于所有值而沒(méi)有任何工資能大于自己的最大值這個(gè)條件永遠(yuǎn)為假。這兩種關(guān)鍵字放在一起就是用來(lái)考察你分不分得清集合邏輯。IN 子查詢(xún)?cè)趯?shí)訓(xùn)里更常用典型場(chǎng)景是「先查出符合條件的 id 集合再查這些 id 的完整記錄」# 查出 90 分以上的學(xué)生 id外層再取完整信息 select * from student where s_id in ( select s_id from score where s_score 90 );內(nèi)層select s_id from score where s_score 90返回一批學(xué)生編號(hào)外層用 IN 判斷 s_id 是否落在其中。這個(gè)寫(xiě)法比 JOIN 更直觀(guān)尤其適合一層套一層的統(tǒng)計(jì)題目。4.3 聚合函數(shù)與分組統(tǒng)計(jì)COUNT、SUM、AVG、MAX、MIN 的邊界聚合函數(shù)這一關(guān)是必考項(xiàng)。資源里把五個(gè)函數(shù)分開(kāi)練習(xí)但實(shí)際組合使用更常見(jiàn)。先看基礎(chǔ)用法# COUNT(*) 數(shù)行數(shù)COUNT(列名) 數(shù)列里非 NULL 的個(gè)數(shù) select count(*) from tb_class; select classid, count(*) from tb_class where classid367; # SUM 和 AVG 自動(dòng)忽略 NULL但列必須是數(shù)值類(lèi)型 select sum(score) from tb_class; select course, avg(score) from tb_class where course語(yǔ)文; # MAX/MIN 對(duì)字符串也有效按字典序取最大最小 select course, max(score) from tb_class where course語(yǔ)文; select course, min(score) from tb_class where course英語(yǔ);COUNT(*)統(tǒng)計(jì)整張表的行數(shù)COUNT(列名)統(tǒng)計(jì)該列非 NULL 的行數(shù)如果列允許 NULL兩個(gè)結(jié)果可能不一致。SUM、AVG自動(dòng)忽略 NULL 值但列里全是 NULL 時(shí)AVG返回 NULL 而不是 0。MAX、MIN對(duì)字符串按字典序比較不是按長(zhǎng)度這個(gè)細(xì)節(jié)在統(tǒng)計(jì)課程名稱(chēng)時(shí)容易踩。聚合函數(shù)最常見(jiàn)的坑發(fā)生在 WHERE 和 HAVING 混用時(shí)。記住一條規(guī)則WHERE 在分組前過(guò)濾行HAVING 在分組后過(guò)濾組。比如「統(tǒng)計(jì)平均分大于 90 的班級(jí)」# 先按班級(jí)分組再用 HAVING 過(guò)濾平均分大于 90 的組 select class_id, avg(score) as avg_score from tb_score group by class_id having avg_score 90;如果把having avg_score 90寫(xiě)成where avg_score 90數(shù)據(jù)庫(kù)會(huì)直接報(bào)錯(cuò)因?yàn)?WHERE 在分組之前執(zhí)行那時(shí)聚合結(jié)果還不存在。分組統(tǒng)計(jì)的完整形態(tài)是多個(gè)分組維度組合# 按年級(jí)和性別兩個(gè)維度分組每組統(tǒng)計(jì)人數(shù) select gradeId, sex, count(*) from student where gradeId in (2,3,4) group by gradeId, sex;結(jié)果里每個(gè)「年級(jí)-性別」組合占一行。注意SELECT查詢(xún)的列必須出現(xiàn)在GROUP BY中否則在 MySQL 5.7 的ONLY_FULL_GROUP_BY模式下會(huì)直接報(bào)錯(cuò)。4.4 經(jīng)典題型交換、排名與人流量的解題套路資源后半段的復(fù)雜查詢(xún)難度明顯上一個(gè)臺(tái)階。三道經(jīng)典題幾乎覆蓋了實(shí)訓(xùn)里最吃技巧的三類(lèi)寫(xiě)法CASE WHEN 做值交換、子查詢(xún)做排名、自連接做連續(xù)區(qū)間判斷。第一類(lèi)「交換工資」本質(zhì)是把某一列的值按條件互相替換# 用 CASE WHEN 把性別互換m 變 ff 變 m UPDATE tb_Salary SET sex CASE sex WHEN m THEN f ELSE m END;CASE sex WHEN m THEN f ELSE m END是一個(gè)表達(dá)式對(duì)每一行判斷當(dāng)前 sex 的值是 m 就改成 f否則改成 m。這種寫(xiě)法只執(zhí)行一次 UPDATE 就把兩類(lèi)值對(duì)調(diào)如果分兩步更新第一步把 m 改成 f第二步再按 f 改 m數(shù)據(jù)就會(huì)全部變成 m這就是為什么不能分步做交換。第二類(lèi)「分?jǐn)?shù)排名」用相關(guān)子查詢(xún)數(shù)比自己分?jǐn)?shù)高的行數(shù)# 去重排名數(shù)一數(shù)有多少個(gè)不同的分?jǐn)?shù)大于等于當(dāng)前分?jǐn)?shù) select Score, (select count(distinct score) from score where score s.score) as Rank from score as s order by Score desc;內(nèi)層子查詢(xún)對(duì)每一行都執(zhí)行一次統(tǒng)計(jì)score s.score的不同分?jǐn)?shù)個(gè)數(shù)。比如最高分 100 分統(tǒng)計(jì)出來(lái) count 是 1第二名 99 分統(tǒng)計(jì)出來(lái)是 2這樣就實(shí)現(xiàn)了排名。如果不用 DISTINCT會(huì)出現(xiàn)并列名次占位的問(wèn)題比如兩個(gè)第二第三就會(huì)從 4 開(kāi)始數(shù)。第三類(lèi)「體育館人流量」考的是自連接找連續(xù)區(qū)間-- 三表自連接判斷 id 連續(xù)的三天都超過(guò) 100 人 select distinct a.* from gymnasium a, gymnasium b, gymnasium c where a.visitors_flow100 and b.visitors_flow100 and c.visitors_flow100 and ( (a.id b.id-1 and b.id c.id - 1) -- a在中間b、c是后兩天 or (a.id b.id-1 and a.id c.id 1) -- b、c是前后兩天 or (a.id b.id1 and b.id c.id 1) -- a在最后c、b是前兩天 ) order by a.id;同一張表自己和自己連接三次通過(guò) id 相差 1 的關(guān)系找出「連續(xù)三天流量都達(dá)標(biāo)」的日期。distinct去掉重復(fù)行是因?yàn)橥惶炜赡艹霈F(xiàn)在多個(gè)三元組里。這道題難點(diǎn)不在 SQL 語(yǔ)法而在你能不能想到用自連接把「連續(xù)三天」翻譯成「a、b、c三個(gè) id 構(gòu)成三個(gè)連續(xù)整數(shù)」。5. 避坑檢查實(shí)訓(xùn)評(píng)分環(huán)境里最常翻車(chē)的五個(gè)位置5.1 語(yǔ)句結(jié)尾與分號(hào)多寫(xiě)一個(gè)分號(hào)被判錯(cuò)現(xiàn)象答案在 Navicat 里能跑通粘貼到實(shí)訓(xùn)平臺(tái)卻提示語(yǔ)法錯(cuò)誤。原因?qū)嵱?xùn)評(píng)分腳本經(jīng)常把整段代碼作為一條語(yǔ)句執(zhí)行BEGIN ... END塊內(nèi)部如果多了分號(hào)或者最后一行后面留了空行加額外分號(hào)會(huì)被解析成兩條語(yǔ)句第二條不完整就直接報(bào)錯(cuò)。解決每條語(yǔ)句末尾只保留一個(gè)分號(hào)自定義函數(shù)和存儲(chǔ)過(guò)程用delimiter包裹的代碼段結(jié)尾的分號(hào)和delimiter //要嚴(yán)格按題目示例寫(xiě)。本地復(fù)現(xiàn)時(shí)發(fā)現(xiàn)show create table ... \G這種以\G結(jié)尾的語(yǔ)句按行評(píng)分時(shí)尤其挑剔建議去掉\G用普通分號(hào)結(jié)尾。5.2 大小寫(xiě)與中文字符表名、列名和注釋的坑現(xiàn)象查詢(xún)結(jié)果和標(biāo)準(zhǔn)答案內(nèi)容一致評(píng)分仍然判定錯(cuò)誤。原因三個(gè)層面。第一實(shí)訓(xùn)庫(kù)在 Linux 下表名區(qū)分大小寫(xiě)TB_EMP和tb_emp是兩個(gè)表第二中文字符集不對(duì)插入的中文查詢(xún)條件變成亂碼后匹配不上第三代碼編輯器里的智能引號(hào)把 ASCII 引號(hào)替換成中文引號(hào)course語(yǔ)文的中文引號(hào)會(huì)讓 SQL 解析直接失敗。解決表名和列名嚴(yán)格按題目描述寫(xiě)統(tǒng)一小寫(xiě)建庫(kù)語(yǔ)句顯式加DEFAULT CHARSETutf8寫(xiě)中文字符串時(shí)只用英文單引號(hào)寫(xiě)完檢查一下引號(hào)兩邊的字重是否統(tǒng)一。5.3 外鍵約束與刪除順序先刪子表還是先刪父表現(xiàn)象刪除父表記錄時(shí)提示cannot delete or update a parent row。原因t_class 是父表t_student 通過(guò)外鍵引用 t_class.id只要子表里還有學(xué)生引用某個(gè)班級(jí) id直接刪父表記錄就被外鍵約束攔住。解決先刪子表里引用該班級(jí)的行再刪父表記錄。實(shí)訓(xùn)里如果題目只要求刪父表但環(huán)境里子表已有數(shù)據(jù)可以先執(zhí)行SET FOREIGN_KEY_CHECKS0;臨時(shí)禁用外鍵檢查刪完再設(shè)回 1。注意這個(gè)命令只在當(dāng)前會(huì)話(huà)有效不影響其他提交。5.4 GROUP BY 與 HAVING分組列沒(méi)查全導(dǎo)致報(bào)錯(cuò)現(xiàn)象執(zhí)行select class_id, count(*) from tb_score group by class_id;時(shí)報(bào)錯(cuò)提示列不在 GROUP BY 子句中。原因MySQL 5.7 及以上默認(rèn)開(kāi)啟ONLY_FULL_GROUP_BYSELECT 的列必須在 GROUP BY 里出現(xiàn)否則拒絕執(zhí)行。實(shí)訓(xùn)平臺(tái)恰好是 5.7這條規(guī)則卡掉了很多人。解決SELECT 里只保留分組列和聚合函數(shù)。如果確實(shí)需要查非分組列用子查詢(xún)或ANY_VALUE()繞過(guò)但實(shí)訓(xùn)里不推薦這種寫(xiě)法評(píng)分腳本可能不認(rèn)。正確做法是先group by分組再在子查詢(xún)外層取完整記錄。5.5 修改類(lèi)題目重復(fù)執(zhí)行第二次跑答案直接失敗現(xiàn)象同一個(gè)關(guān)卡在本機(jī)練習(xí)時(shí)第二次提交結(jié)果和第一次不一致甚至報(bào)錯(cuò)。原因評(píng)分沙箱每次提交會(huì)重置數(shù)據(jù)庫(kù)但本地練習(xí)時(shí)不會(huì)。帶AUTO_INCREMENT的表第二次插入id 會(huì)繼續(xù)累加UPDATE 把性別從 m 改成 f再跑一遍條件就匹配不上了。解決本地練習(xí)前先DROP TABLE再重新建表或者每次跑之前執(zhí)行TRUNCATE TABLE 表名;把數(shù)據(jù)清零。實(shí)訓(xùn)平臺(tái)的線(xiàn)上提交不用擔(dān)心這個(gè)但本地復(fù)現(xiàn)時(shí)一定要重置環(huán)境否則你會(huì)以為答案寫(xiě)錯(cuò)了實(shí)際是上一輪練習(xí)的數(shù)據(jù)殘留導(dǎo)致的。6. 用一份自檢 SQL 驗(yàn)證實(shí)訓(xùn)答案三分鐘定位對(duì)錯(cuò)拿到這份 PDF 答案之后最高效的用法不是逐條抄而是把它當(dāng)作「通關(guān)對(duì)比表」。每一關(guān)提交前我習(xí)慣先跑三條自檢 SQL確認(rèn)自己的語(yǔ)句和答案在邏輯上等價(jià)而不是在字符串上完全一致。第一類(lèi)自檢是結(jié)構(gòu)檢查。建表或改表類(lèi)關(guān)卡提交前確認(rèn)表存在、字段正確# 查看當(dāng)前庫(kù)里有哪些表 SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA DATABASE(); # 查看表字段類(lèi)型和鍵標(biāo)記 DESC t_emp; # 查看建表語(yǔ)句細(xì)節(jié)重點(diǎn)看 ENGINE 和 CHARSET SHOW CREATE TABLE t_emp;information_schema.TABLES查庫(kù)內(nèi)所有表名DESC看字段順序和 PRI/MUL 標(biāo)記SHOW CREATE TABLE看字符集和引擎。這三條合在一起能覆蓋 80% 的 DDL 類(lèi)關(guān)卡。第二類(lèi)自檢是等價(jià)條件比對(duì)。把答案里的 WHERE 改寫(xiě)成另一種表達(dá)確認(rèn)結(jié)果一致-- 兩種寫(xiě)法結(jié)果應(yīng)完全相同 SELECT * FROM tb_score WHERE score BETWEEN 3000 AND 5000; SELECT * FROM tb_score WHERE score 3000 AND score 5000;如果兩個(gè)結(jié)果的行數(shù)和內(nèi)容一樣說(shuō)明查詢(xún)條件沒(méi)問(wèn)題。如果不一樣優(yōu)先懷疑 BETWEEN 邊界、LIKE 通配符位置、NULL 判斷是不是用了等號(hào)。第三類(lèi)自檢是連接查詢(xún)的總數(shù)校驗(yàn)。多表連接最容易翻倍數(shù)據(jù)用保留表的行數(shù)做對(duì)照# 左外連接的行數(shù)應(yīng)該等于學(xué)生表總行數(shù) SELECT COUNT(*) FROM tb_student s LEFT JOIN tb_class c ON s.class_id c.id; # 學(xué)生表總行數(shù) SELECT COUNT(*) FROM tb_student;左外連接保留學(xué)生表全部行第一個(gè) COUNT 應(yīng)該等于第二個(gè) COUNT。如果第一個(gè)大于第二個(gè)說(shuō)明 ON 條件導(dǎo)致一對(duì)多膨脹如果第一個(gè)小于第二個(gè)說(shuō)明連接條件漏掉了某些匹配關(guān)系。這個(gè)技巧在真實(shí)業(yè)務(wù)里寫(xiě)報(bào)表時(shí)極其好用我每次完成帶 JOIN 的查詢(xún)都會(huì)用保留表的行數(shù)做一次總數(shù)校驗(yàn)?zāi)軘r下大量因?yàn)橐粚?duì)多連接導(dǎo)致的數(shù)據(jù)翻倍。從那以后我每次跑實(shí)訓(xùn)答案和實(shí)際項(xiàng)目 SQL 前都強(qiáng)制走一遍這三板斧結(jié)構(gòu)查一次、等價(jià)條件比對(duì)一次、連接總數(shù)校驗(yàn)一次。這套習(xí)慣幫我省下了大量查錯(cuò)時(shí)間也希望幫到你。本文還有配套的精品資源點(diǎn)擊獲取 MySQL實(shí)訓(xùn)答案速查：從建表到查詢(xún)的避坑指南
簡(jiǎn)介這是一份針對(duì)頭歌平臺(tái)MySQL數(shù)據(jù)庫(kù)實(shí)訓(xùn)的全流程答案整理面向正在完成數(shù)據(jù)庫(kù)定義、表操作、約束應(yīng)用等關(guān)卡的學(xué)習(xí)者也適合需要快速鞏固數(shù)據(jù)庫(kù)基礎(chǔ)、備戰(zhàn)實(shí)訓(xùn)考試的高校學(xué)生。內(nèi)容按實(shí)訓(xùn)模塊逐關(guān)編排既有創(chuàng)建數(shù)據(jù)庫(kù)、創(chuàng)建數(shù)據(jù)表等入門(mén)操作也覆蓋主鍵、外鍵、唯一、默認(rèn)等約束的設(shè)置以及查看表結(jié)構(gòu)、修改表名、調(diào)整字段類(lèi)型與位置、增刪字段、插入更新數(shù)據(jù)等高頻場(chǎng)景每步配有可直接執(zhí)行的數(shù)據(jù)庫(kù)語(yǔ)句方便對(duì)照驗(yàn)證、快速定位錯(cuò)誤。正文以代碼塊形式組織命令前均標(biāo)注對(duì)應(yīng)關(guān)卡遇到報(bào)錯(cuò)時(shí)可逐條核對(duì)減少試錯(cuò)成本。資源包共包含一個(gè)PDF格式文件大小約四百三十三KB文件帶有清晰目錄可依據(jù)關(guān)卡編號(hào)直接跳轉(zhuǎn)查閱。目前已有一萬(wàn)八千三百六十九人學(xué)習(xí)下載對(duì)初學(xué)者和需要高效完成實(shí)訓(xùn)任務(wù)的學(xué)習(xí)者都有較強(qiáng)的參考價(jià)值。1. MySQL實(shí)訓(xùn)答案一份能跑通但別盲抄的速查手冊(cè)MySQL 數(shù)據(jù)庫(kù)實(shí)訓(xùn)向來(lái)不缺題目缺的是能一次跑通的答案。這份《頭歌MySQL數(shù)據(jù)庫(kù)實(shí)訓(xùn)答案》PDF 把建庫(kù)、建表、約束、增刪改查、連接查詢(xún)、子查詢(xún)、聚合函數(shù)、視圖、索引、存儲(chǔ)過(guò)程按關(guān)卡拆成了可直接粘貼的 SQL 片段適合正在趕實(shí)訓(xùn)進(jìn)度、又不想在環(huán)境配置上耗時(shí)間的從業(yè)者和學(xué)生。但我的建議是答案可以抄抄完必須做兩件事——理解每條語(yǔ)句的邊界條件再在本地用同樣的表結(jié)構(gòu)重跑一遍。這份資源解決的是「卡在第幾步、語(yǔ)法怎么拼、表名哪里搞錯(cuò)」這類(lèi)具體問(wèn)題而不是替你學(xué)會(huì) SQL。如果你只是想要一份按目錄檢索的參考手冊(cè)它比翻文檔快得多如果你指望粘貼完就徹底掌握那大概率會(huì)在第二個(gè)項(xiàng)目里翻車(chē)。2. 建庫(kù)建表與約束把 DDL 練成肌肉記憶的三類(lèi)寫(xiě)法2.1 最小可跑通的建庫(kù)建表從零到 t_emp實(shí)訓(xùn)里的每個(gè)新項(xiàng)目幾乎都以這三步開(kāi)場(chǎng)登錄、建庫(kù)、建表。資源里這條命令序列是整套答案里出現(xiàn)頻率最高的骨架。# 登錄實(shí)訓(xùn)沙箱 MySQL 服務(wù)-u 指定用戶(hù)-p 帶出密碼-h 指定主機(jī) mysql -uroot -p123123 -h127.0.0.1use MyDb; # 切換到 MyDb 數(shù)據(jù)庫(kù)后續(xù)建表都建在這個(gè)庫(kù)里 create table t_emp ( # 建員工表四個(gè)字段按題目順序排列 id int, # 整型員工編號(hào) name varchar(32), # 變長(zhǎng)字符串最多存 32 個(gè)字符 deptId int, # 部門(mén)編號(hào) salary float # 浮點(diǎn)型工資 );第一段是命令行登錄參數(shù)-u root指定用戶(hù)名為 root-p123123是密碼直接跟在 p 后面中間不留空格-h127.0.0.1表示連本機(jī)。實(shí)訓(xùn)沙箱里這套參數(shù)是固定的本地環(huán)境直接換密碼即可。第二段是建表語(yǔ)句varchar(32)不是固定占 32 字節(jié)而是最多存 32 個(gè)字符超過(guò)會(huì)截?cái)鄐alary float用浮點(diǎn)存工資實(shí)訓(xùn)里夠用但真實(shí)業(yè)務(wù)我一般用decimal(10,2)避免精度漂移。建表后第一時(shí)間執(zhí)行desc t_emp;或者show create table t_emp;看字段順序和類(lèi)型。實(shí)訓(xùn)評(píng)分經(jīng)常比對(duì)查詢(xún)結(jié)果的列順序id, name, deptId, salary和你寫(xiě)成id, salary, name, deptId在后面的查詢(xún)關(guān)卡里可能結(jié)果內(nèi)容一樣但列頭對(duì)不上評(píng)分腳本直接判定失敗。2.2 主鍵與外鍵單列、復(fù)合和命名約束的選擇主鍵約束在這份資源里出現(xiàn)了三次不同寫(xiě)法分別對(duì)應(yīng)單列主鍵、復(fù)合主鍵和外鍵引用。先看前兩種# 單列主鍵直接在列定義后面聲明 PRIMARY KEY create table t_user1( userId INT PRIMARY KEY, # 用戶(hù)編號(hào)作為唯一標(biāo)識(shí) name VARCHAR(32), password VARCHAR(11), phone VARCHAR(11), email VARCHAR(32) ); # 復(fù)合主鍵把 name 和 phone 組合成一個(gè)整體唯一標(biāo)識(shí) create table t_user2( name VARCHAR(32), phone VARCHAR(11), email VARCHAR(32), PRIMARY KEY(name, phone) # 兩列組合值必須唯一 );單列主鍵適合一張表里已經(jīng)有一個(gè)天然唯一字段的場(chǎng)景比如用戶(hù)編號(hào)。復(fù)合主鍵則用在單列都不唯一、但兩列組合才能唯一的情況。注意復(fù)合主鍵的判定規(guī)則name單獨(dú)可以重復(fù)phone單獨(dú)也可以重復(fù)但(name, phone)這對(duì)組合不能重復(fù)。實(shí)訓(xùn)里檢查你寫(xiě)得對(duì)不對(duì)用desc看 Key 列PRI 出現(xiàn)在哪一行、出現(xiàn)幾次立刻就知道是單列還是復(fù)合。外鍵約束才是這一節(jié)的重點(diǎn)資源里給的標(biāo)準(zhǔn)寫(xiě)法長(zhǎng)這樣# 先建父表 t_class CREATE TABLE t_class( id INT PRIMARY KEY, name VARCHAR(22) ); # 再建子表 t_studentclassId 引用 t_class 的 id CREATE TABLE t_student( id INT PRIMARY KEY, name VARCHAR(22), classId int, CONSTRAINT fk_stu_class1 FOREIGN KEY(classId) REFERENCES t_class(id) );這里CONSTRAINT fk_stu_class1是給外鍵起名后面FOREIGN KEY(classId) REFERENCES t_class(id)聲明子表 classId 引用父表 id。實(shí)訓(xùn)環(huán)境里最常翻車(chē)的點(diǎn)有兩個(gè)一是執(zhí)行順序顛倒必須先建父表再建子表反過(guò)來(lái)直接報(bào)「無(wú)法創(chuàng)建外鍵約束」二是兩側(cè)字段類(lèi)型必須完全一致t_class.id是 INTt_student.classId也必須是 INT寫(xiě)成 VARCHAR 或漏掉類(lèi)型都會(huì)失敗。2.3 添加常用約束非空、唯一、默認(rèn)值與字符集這一關(guān)的建表語(yǔ)句把實(shí)訓(xùn)要求的大部分約束一次集齊了CREATE TABLE t_user( id INT PRIMARY KEY AUTO_INCREMENT, # 主鍵自增插入時(shí)不需手動(dòng)給id username VARCHAR(32) NOT NULL UNIQUE, # 用戶(hù)名非空且唯一 sex VARCHAR(4) DEFAULT 男 # 不傳 sex 時(shí)默認(rèn)填“男” ) DEFAULT CHARSETutf8; # 表級(jí)字符集中文不亂碼AUTO_INCREMENT讓主鍵從 1 開(kāi)始自動(dòng)累加插入時(shí)不需要寫(xiě) id 字段。NOT NULL UNIQUE同時(shí)限制了非空和唯一兩項(xiàng)規(guī)則只要有一條記錄插入了 NULL 或重復(fù)的 username整條 INSERT 直接報(bào)錯(cuò)。DEFAULT 男是列級(jí)默認(rèn)值INSERT語(yǔ)句沒(méi)給 sex 字段時(shí)自動(dòng)補(bǔ)上。注意DEFAULT CHARSETutf8這一句經(jīng)常被忽略。實(shí)訓(xùn)平臺(tái)大多數(shù)默認(rèn) utf8mb4顯式寫(xiě) utf8 也能過(guò)但你本地導(dǎo)數(shù)據(jù)到其他庫(kù)時(shí)字符集不一致會(huì)導(dǎo)致中文變成問(wèn)號(hào)。我的習(xí)慣是建完表立刻執(zhí)行show create table t_user;重點(diǎn)看三處ENGINE 是不是 InnoDBCHARSET 是不是 utf8id 的 Extra 列是不是 auto_increment。這三個(gè)屬性決定了后續(xù)外鍵、自增、中文查詢(xún)能不能正常跑。2.4 表結(jié)構(gòu)維護(hù)ALTER TABLE 五類(lèi)高頻操作項(xiàng)目進(jìn)行到一半發(fā)現(xiàn)表結(jié)構(gòu)不對(duì)就要靠ALTER TABLE補(bǔ)救。資源里覆蓋了改名、改字段名、改類(lèi)型、增刪列、移動(dòng)位置我把這幾條命令理到一起ALTER TABLE tb_emp RENAME jd_emp; # 改表名 ALTER TABLE tb_emp change Id prod_id int(11); # 改列名同時(shí)改類(lèi)型 ALTER TABLE tb_emp MODIFY Name varchar(30); # 只改列類(lèi)型 ALTER TABLE tb_emp ADD Country varchar(20) AFTER Name; # 新增列并指定位置 ALTER TABLE tb_emp DROP Salary; # 刪除列 ALTER TABLE tb_emp MODIFY DeptId int(11) AFTER Salary; # 移動(dòng)列位置CHANGE和MODIFY的差別是這組命令里最值得記的對(duì)比點(diǎn)語(yǔ)句能改列名能改類(lèi)型適用場(chǎng)景CHANGE可以可以列改名加類(lèi)型調(diào)整MODIFY不可以可以只改類(lèi)型或移動(dòng)位置CHANGE后面必須帶著完整的列定義老名字和新名字都要寫(xiě)int(11)不能省MODIFY只需寫(xiě)列名加新類(lèi)型。ADD ... AFTER Name把新列插到 Name 后面FIRST則把列移到第一個(gè)位置。DROP Salary刪除列沒(méi)有后悔藥實(shí)訓(xùn)沙箱里刪完想恢復(fù)只能重新建表再插數(shù)據(jù)。提示多條 ALTER 語(yǔ)句不要擠在一個(gè)執(zhí)行框里連寫(xiě)。實(shí)訓(xùn)評(píng)分按行解析漏一個(gè)分號(hào)會(huì)把后面的語(yǔ)句當(dāng)成上一條的從句報(bào)語(yǔ)法錯(cuò)誤卻不告訴你錯(cuò)在哪一行。我一般每條 ALTER 單獨(dú)執(zhí)行確認(rèn)DESC結(jié)果符合預(yù)期再寫(xiě)下一條。3. 數(shù)據(jù)操作與單表查詢(xún)?cè)鰟h改查的四個(gè)邊界條件3.1 批量插入一條 INSERT 寫(xiě)多行表結(jié)構(gòu)就緒后的第一件事是造數(shù)據(jù)。資源里的批量插入示例非常典型# 一次插入三行員工記錄字段順序與表結(jié)構(gòu)保持一致 INSERT INTO tb_emp(Id,Name,DeptId,Salary) VALUES (1,Nancy,301,2300.00), (2,Tod,303,5600.00), (3,Carly,301,3200.00);一條 INSERT 后跟多組 VALUES每組用括號(hào)包住、逗號(hào)分隔這是標(biāo)準(zhǔn)的批量寫(xiě)入寫(xiě)法。字段列表(Id,Name,DeptId,Salary)限定了每組值的順序多寫(xiě)或少寫(xiě)一個(gè)值都會(huì)報(bào)「Column count doesnt match value count」。評(píng)分會(huì)統(tǒng)計(jì)表內(nèi)的總行數(shù)少插一條就少拿一分所以提交前用select count(*) from tb_emp;對(duì)比一下。這里有個(gè)容易被忽視的坑字符串用雙引號(hào)還是單引號(hào)。MySQL 默認(rèn)兩種都能識(shí)別但實(shí)訓(xùn)平臺(tái)開(kāi)啟了 ANSI_QUOTES 模式時(shí)雙引號(hào)會(huì)被當(dāng)成標(biāo)識(shí)符而不是字符串同樣的語(yǔ)句直接報(bào)錯(cuò)。穩(wěn)妥做法是統(tǒng)一用單引號(hào)這也是 SQL 標(biāo)準(zhǔn)推薦寫(xiě)法。3.2 更新與刪除WHERE 條件決定影響范圍數(shù)據(jù)插進(jìn)去之后實(shí)訓(xùn)馬上會(huì)考改數(shù)據(jù)。資源里這兩條語(yǔ)句看似簡(jiǎn)單邊界條件卻很值得拆開(kāi)看# 只更新 id3 的那一行SET 可以同時(shí)改多個(gè)字段 UPDATE tb_emp SET NameTracy, DeptId302, Salary4300.00 WHERE id3; # 刪除工資大于 3000 的所有記錄 DELETE FROM tb_emp WHERE Salary3000;UPDATE 的三個(gè)賦值之間用逗號(hào)分隔最后用 WHERE 限定行。這里如果把 WHERE 漏掉整張表所有行都會(huì)被改成同一組值在實(shí)訓(xùn)里可能顯示不出來(lái)但數(shù)據(jù)全毀了。DELETE 同樣依賴(lài) WHERE 控制刪除范圍Salary3000刪掉高工資Salary0則等于清空全表。真實(shí)業(yè)務(wù)里一條不帶 WHERE 的 DELETE 甚至可能觸發(fā)數(shù)據(jù)庫(kù)的批量刪除保護(hù)機(jī)制直接拒絕執(zhí)行。我的操作習(xí)慣是寫(xiě) UPDATE 或 DELETE 前先跑一遍同樣 WHERE 條件的 SELECT-- 先看會(huì)命中哪些行 SELECT * FROM tb_emp WHERE Salary3000;確認(rèn)結(jié)果里就是要?jiǎng)h的那幾條再執(zhí)行 DELETE。這個(gè)習(xí)慣在實(shí)訓(xùn)里能幫你提前發(fā)現(xiàn)條件寫(xiě)反在真實(shí)項(xiàng)目里能避免刪錯(cuò)數(shù)據(jù)。3.3 單表查詢(xún)IN、BETWEEN、LIKE、IS NULL 的判定細(xì)節(jié)查詢(xún)語(yǔ)句是實(shí)訓(xùn)占比最大的一塊。資源里四個(gè)關(guān)鍵字查詢(xún)放在一起剛好覆蓋最常見(jiàn)的判定邊界# NOT INid 不是 1 的所有員工 SELECT Name,Salary FROM tb_emp WHERE Id NOT IN (1); # BETWEEN 是閉區(qū)間包含 3000 和 5000 兩條邊界 SELECT Name,Salary FROM tb_emp WHERE Salary BETWEEN 3000 AND 5000; # LIKE 的 % 是任意長(zhǎng)度通配符C% 表示以 C 開(kāi)頭 SELECT Name,Salary FROM tb_emp WHERE Name LIKE C%; # NULL 只能 IS NULL 判斷寫(xiě)成 NULL 永遠(yuǎn)查不到數(shù)據(jù) SELECT * FROM tb_emp WHERE DeptId IS NULL;IN的列表里寫(xiě)的是 id1NOT IN取反返回所有 id 不是 1 的行。BETWEEN 3000 AND 5000是閉區(qū)間等價(jià)于3000 AND 5000。LIKE C%只匹配以 C 開(kāi)頭的名字%C%表示包含 CC_表示 C 后面正好一個(gè)字符。IS NULL專(zhuān)門(mén)查空值新手最常見(jiàn)的錯(cuò)誤是寫(xiě)DeptId NULL這個(gè)表達(dá)式的結(jié)果永遠(yuǎn)是 NULL不會(huì)返回任何行必須用IS NULL或IS NOT NULL。提示SQL 里 NULL 不是值而是「未定義」。任何 NULL 參與的等值比較結(jié)果都不是 true也不是 false而是未知。這就是為什么NULL查不出東西。去重也是一個(gè)高頻考點(diǎn)# DISTINCT 作用在整行而不是單個(gè)列 SELECT DISTINCT Name FROM tb_emp;DISTINCT如果寫(xiě)成SELECT DISTINCT Name, DeptId去重的是兩列的組合。只有當(dāng)你希望基于兩列組合去重復(fù)時(shí)才這么寫(xiě)否則結(jié)果會(huì)比你預(yù)期的多。3.4 排序、分組與 LIMIT三條語(yǔ)句的配合順序查詢(xún)的進(jìn)階形態(tài)是把排序、分組、限量三個(gè)動(dòng)作組合起來(lái)。資源里這三條語(yǔ)句恰好演示了最容易搞混的兩處# 按成績(jī)降序排列DESC 從高到低ASC 從低到高 SELECT * FROM tb_score WHERE class_id 1 ORDER BY score DESC; # GROUP BY 之后 SELECT 里最好只保留分組列和聚合函數(shù) SELECT * FROM tb_class GROUP BY class_id; # LIMIT 第一個(gè)參數(shù)是偏移量0 表示第一行第二個(gè)參數(shù)是取幾行 SELECT * FROM tb_score ORDER BY score DESC LIMIT 1,4;第一條是排序ORDER BY只影響展示順序不影響返回哪些行多個(gè)排序字段用逗號(hào)分隔。第二條是分組GROUP BY class_id之后執(zhí)行SELECT *在 MySQL 5.7 里通常返回每組的第一行但這是未定義行為換了版本結(jié)果可能不同。正經(jīng)寫(xiě)法是SELECT class_id, COUNT(*) FROM tb_class GROUP BY class_id;。第三條的 LIMIT 是所有新手必踩的坑LIMIT 1,4表示從第 2 行開(kāi)始取 4 行也就是第 2 到第 5 名不是第 1 到第 4 名。LIMIT 的第一個(gè)參數(shù)是偏移量從 0 開(kāi)始計(jì)數(shù)。如果題目要「第 2 名到第 5 名」LIMIT 1,4是對(duì)的如果只是「前 4 名」要寫(xiě)LIMIT 4或LIMIT 0,4。4. 連接查詢(xún)與子查詢(xún)多表場(chǎng)景的三種查詢(xún)路徑4.1 內(nèi)連接與外連接誰(shuí)保留誰(shuí)決定方向連接查詢(xún)是實(shí)訓(xùn)里拉開(kāi)差距的關(guān)卡。資源里三個(gè)題目把內(nèi)連接、左外連接、右外連接一網(wǎng)打盡# 內(nèi)連接只返回兩邊都匹配得上的行 select tb_student.name as studentName, tb_class.name as className from tb_student join tb_class on tb_class.id tb_student.class_id; # 右外連接保留右表 tb_student 全部行 select tb_student.name as studentName, tb_class.name as className from tb_class right join tb_student on tb_class.idtb_student.class_id; # 左外連接保留左表 tb_class 全部行 select tb_student.name as studentName, tb_class.name as className from tb_class left join tb_student on tb_class.idtb_student.class_id;第一段JOIN ... ON是內(nèi)連接學(xué)生表里如果有 class_id 指向不存在的班級(jí)這一行會(huì)被過(guò)濾掉。第二段和第三段的對(duì)比很有迷惑性代碼只差一個(gè) left/right語(yǔ)義完全相反。題目要求「查詢(xún)所有學(xué)生姓名和對(duì)應(yīng)的班級(jí)」意味著必須保留學(xué)生表的全部行哪怕某個(gè)學(xué)生的班級(jí) id 在班級(jí)表里不存在。第二段用tb_class RIGHT JOIN tb_student能正確保留右表 tb_student 全部行。第三段用tb_class LEFT JOIN tb_student保留的是班級(jí)表全部行沒(méi)學(xué)生的班級(jí)也會(huì)出現(xiàn)學(xué)生字段顯示 NULL方向就反了。我的判斷方法是先問(wèn)自己結(jié)果里必須出現(xiàn)哪張表的全部行那張表就要放在「保留表」的位置。LEFT JOIN保留左表RIGHT JOIN保留右表。實(shí)訓(xùn)題目里描述是「所有學(xué)生」時(shí)優(yōu)先考慮tb_student LEFT JOIN tb_class這樣語(yǔ)義最直白。4.2 復(fù)合條件連接與子查詢(xún)比較、ALL/ANY、IN多表關(guān)聯(lián)不一定要用 JOIN 關(guān)鍵字老式逗號(hào)寫(xiě)法在實(shí)訓(xùn)題目里也很常見(jiàn)# 三表關(guān)聯(lián)連接條件放在 WHERE 里過(guò)濾條件放在 AND 后面 select s1.name as studentName, score, s2.name as className from tb_student as s1, tb_class as s2 where s1.class_id s2.id and s1.score 90 order by score desc;s1和s2是表別名寫(xiě)長(zhǎng)查詢(xún)時(shí)能省大量字符也避免同名字段歧義。這段的等價(jià)寫(xiě)法是tb_student join tb_class on tb_student.class_id tb_class.id where score 90。and s1.score90是過(guò)濾條件漏掉的話(huà) 90 分以下的學(xué)生也會(huì)混進(jìn)結(jié)果。子查詢(xún)部分最考驗(yàn)對(duì)關(guān)鍵字語(yǔ)義的理解。資源里這組代碼是典型代表# 標(biāo)量子查詢(xún)內(nèi)層返回平均年齡外層逐行比較 select name, age from tb_emp where age (select avg(age) from tb_emp); # ANY大于子查詢(xún)結(jié)果里的任意一個(gè)值即可 SELECT position,salary FROM tb_salary WHERE salary ANY(SELECT max(salary) FROM tb_salary where positionjava);第一個(gè)是標(biāo)量子查詢(xún)內(nèi)層返回一個(gè)值外層用比較運(yùn)算符和它比較邏輯直白。第二個(gè)是 ANY 的語(yǔ)義salary ANY(...)只要大于子查詢(xún)返回集合里的任意一個(gè)值就滿(mǎn)足。這里子查詢(xún)只返回一個(gè) max(salary)ANY 和直接大于最大值等價(jià)。如果把 ANY 換成 ALL表示要大于所有值而沒(méi)有任何工資能大于自己的最大值這個(gè)條件永遠(yuǎn)為假。這兩種關(guān)鍵字放在一起就是用來(lái)考察你分不分得清集合邏輯。IN 子查詢(xún)?cè)趯?shí)訓(xùn)里更常用典型場(chǎng)景是「先查出符合條件的 id 集合再查這些 id 的完整記錄」# 查出 90 分以上的學(xué)生 id外層再取完整信息 select * from student where s_id in ( select s_id from score where s_score 90 );內(nèi)層select s_id from score where s_score 90返回一批學(xué)生編號(hào)外層用 IN 判斷 s_id 是否落在其中。這個(gè)寫(xiě)法比 JOIN 更直觀(guān)尤其適合一層套一層的統(tǒng)計(jì)題目。4.3 聚合函數(shù)與分組統(tǒng)計(jì)COUNT、SUM、AVG、MAX、MIN 的邊界聚合函數(shù)這一關(guān)是必考項(xiàng)。資源里把五個(gè)函數(shù)分開(kāi)練習(xí)但實(shí)際組合使用更常見(jiàn)。先看基礎(chǔ)用法# COUNT(*) 數(shù)行數(shù)COUNT(列名) 數(shù)列里非 NULL 的個(gè)數(shù) select count(*) from tb_class; select classid, count(*) from tb_class where classid367; # SUM 和 AVG 自動(dòng)忽略 NULL但列必須是數(shù)值類(lèi)型 select sum(score) from tb_class; select course, avg(score) from tb_class where course語(yǔ)文; # MAX/MIN 對(duì)字符串也有效按字典序取最大最小 select course, max(score) from tb_class where course語(yǔ)文; select course, min(score) from tb_class where course英語(yǔ);COUNT(*)統(tǒng)計(jì)整張表的行數(shù)COUNT(列名)統(tǒng)計(jì)該列非 NULL 的行數(shù)如果列允許 NULL兩個(gè)結(jié)果可能不一致。SUM、AVG自動(dòng)忽略 NULL 值但列里全是 NULL 時(shí)AVG返回 NULL 而不是 0。MAX、MIN對(duì)字符串按字典序比較不是按長(zhǎng)度這個(gè)細(xì)節(jié)在統(tǒng)計(jì)課程名稱(chēng)時(shí)容易踩。聚合函數(shù)最常見(jiàn)的坑發(fā)生在 WHERE 和 HAVING 混用時(shí)。記住一條規(guī)則WHERE 在分組前過(guò)濾行HAVING 在分組后過(guò)濾組。比如「統(tǒng)計(jì)平均分大于 90 的班級(jí)」# 先按班級(jí)分組再用 HAVING 過(guò)濾平均分大于 90 的組 select class_id, avg(score) as avg_score from tb_score group by class_id having avg_score 90;如果把having avg_score 90寫(xiě)成where avg_score 90數(shù)據(jù)庫(kù)會(huì)直接報(bào)錯(cuò)因?yàn)?WHERE 在分組之前執(zhí)行那時(shí)聚合結(jié)果還不存在。分組統(tǒng)計(jì)的完整形態(tài)是多個(gè)分組維度組合# 按年級(jí)和性別兩個(gè)維度分組每組統(tǒng)計(jì)人數(shù) select gradeId, sex, count(*) from student where gradeId in (2,3,4) group by gradeId, sex;結(jié)果里每個(gè)「年級(jí)-性別」組合占一行。注意SELECT查詢(xún)的列必須出現(xiàn)在GROUP BY中否則在 MySQL 5.7 的ONLY_FULL_GROUP_BY模式下會(huì)直接報(bào)錯(cuò)。4.4 經(jīng)典題型交換、排名與人流量的解題套路資源后半段的復(fù)雜查詢(xún)難度明顯上一個(gè)臺(tái)階。三道經(jīng)典題幾乎覆蓋了實(shí)訓(xùn)里最吃技巧的三類(lèi)寫(xiě)法CASE WHEN 做值交換、子查詢(xún)做排名、自連接做連續(xù)區(qū)間判斷。第一類(lèi)「交換工資」本質(zhì)是把某一列的值按條件互相替換# 用 CASE WHEN 把性別互換m 變 ff 變 m UPDATE tb_Salary SET sex CASE sex WHEN m THEN f ELSE m END;CASE sex WHEN m THEN f ELSE m END是一個(gè)表達(dá)式對(duì)每一行判斷當(dāng)前 sex 的值是 m 就改成 f否則改成 m。這種寫(xiě)法只執(zhí)行一次 UPDATE 就把兩類(lèi)值對(duì)調(diào)如果分兩步更新第一步把 m 改成 f第二步再按 f 改 m數(shù)據(jù)就會(huì)全部變成 m這就是為什么不能分步做交換。第二類(lèi)「分?jǐn)?shù)排名」用相關(guān)子查詢(xún)數(shù)比自己分?jǐn)?shù)高的行數(shù)# 去重排名數(shù)一數(shù)有多少個(gè)不同的分?jǐn)?shù)大于等于當(dāng)前分?jǐn)?shù) select Score, (select count(distinct score) from score where score s.score) as Rank from score as s order by Score desc;內(nèi)層子查詢(xún)對(duì)每一行都執(zhí)行一次統(tǒng)計(jì)score s.score的不同分?jǐn)?shù)個(gè)數(shù)。比如最高分 100 分統(tǒng)計(jì)出來(lái) count 是 1第二名 99 分統(tǒng)計(jì)出來(lái)是 2這樣就實(shí)現(xiàn)了排名。如果不用 DISTINCT會(huì)出現(xiàn)并列名次占位的問(wèn)題比如兩個(gè)第二第三就會(huì)從 4 開(kāi)始數(shù)。第三類(lèi)「體育館人流量」考的是自連接找連續(xù)區(qū)間-- 三表自連接判斷 id 連續(xù)的三天都超過(guò) 100 人 select distinct a.* from gymnasium a, gymnasium b, gymnasium c where a.visitors_flow100 and b.visitors_flow100 and c.visitors_flow100 and ( (a.id b.id-1 and b.id c.id - 1) -- a在中間b、c是后兩天 or (a.id b.id-1 and a.id c.id 1) -- b、c是前后兩天 or (a.id b.id1 and b.id c.id 1) -- a在最后c、b是前兩天 ) order by a.id;同一張表自己和自己連接三次通過(guò) id 相差 1 的關(guān)系找出「連續(xù)三天流量都達(dá)標(biāo)」的日期。distinct去掉重復(fù)行是因?yàn)橥惶炜赡艹霈F(xiàn)在多個(gè)三元組里。這道題難點(diǎn)不在 SQL 語(yǔ)法而在你能不能想到用自連接把「連續(xù)三天」翻譯成「a、b、c三個(gè) id 構(gòu)成三個(gè)連續(xù)整數(shù)」。5. 避坑檢查實(shí)訓(xùn)評(píng)分環(huán)境里最常翻車(chē)的五個(gè)位置5.1 語(yǔ)句結(jié)尾與分號(hào)多寫(xiě)一個(gè)分號(hào)被判錯(cuò)現(xiàn)象答案在 Navicat 里能跑通粘貼到實(shí)訓(xùn)平臺(tái)卻提示語(yǔ)法錯(cuò)誤。原因?qū)嵱?xùn)評(píng)分腳本經(jīng)常把整段代碼作為一條語(yǔ)句執(zhí)行BEGIN ... END塊內(nèi)部如果多了分號(hào)或者最后一行后面留了空行加額外分號(hào)會(huì)被解析成兩條語(yǔ)句第二條不完整就直接報(bào)錯(cuò)。解決每條語(yǔ)句末尾只保留一個(gè)分號(hào)自定義函數(shù)和存儲(chǔ)過(guò)程用delimiter包裹的代碼段結(jié)尾的分號(hào)和delimiter //要嚴(yán)格按題目示例寫(xiě)。本地復(fù)現(xiàn)時(shí)發(fā)現(xiàn)show create table ... \G這種以\G結(jié)尾的語(yǔ)句按行評(píng)分時(shí)尤其挑剔建議去掉\G用普通分號(hào)結(jié)尾。5.2 大小寫(xiě)與中文字符表名、列名和注釋的坑現(xiàn)象查詢(xún)結(jié)果和標(biāo)準(zhǔn)答案內(nèi)容一致評(píng)分仍然判定錯(cuò)誤。原因三個(gè)層面。第一實(shí)訓(xùn)庫(kù)在 Linux 下表名區(qū)分大小寫(xiě)TB_EMP和tb_emp是兩個(gè)表第二中文字符集不對(duì)插入的中文查詢(xún)條件變成亂碼后匹配不上第三代碼編輯器里的智能引號(hào)把 ASCII 引號(hào)替換成中文引號(hào)course語(yǔ)文的中文引號(hào)會(huì)讓 SQL 解析直接失敗。解決表名和列名嚴(yán)格按題目描述寫(xiě)統(tǒng)一小寫(xiě)建庫(kù)語(yǔ)句顯式加DEFAULT CHARSETutf8寫(xiě)中文字符串時(shí)只用英文單引號(hào)寫(xiě)完檢查一下引號(hào)兩邊的字重是否統(tǒng)一。5.3 外鍵約束與刪除順序先刪子表還是先刪父表現(xiàn)象刪除父表記錄時(shí)提示cannot delete or update a parent row。原因t_class 是父表t_student 通過(guò)外鍵引用 t_class.id只要子表里還有學(xué)生引用某個(gè)班級(jí) id直接刪父表記錄就被外鍵約束攔住。解決先刪子表里引用該班級(jí)的行再刪父表記錄。實(shí)訓(xùn)里如果題目只要求刪父表但環(huán)境里子表已有數(shù)據(jù)可以先執(zhí)行SET FOREIGN_KEY_CHECKS0;臨時(shí)禁用外鍵檢查刪完再設(shè)回 1。注意這個(gè)命令只在當(dāng)前會(huì)話(huà)有效不影響其他提交。5.4 GROUP BY 與 HAVING分組列沒(méi)查全導(dǎo)致報(bào)錯(cuò)現(xiàn)象執(zhí)行select class_id, count(*) from tb_score group by class_id;時(shí)報(bào)錯(cuò)提示列不在 GROUP BY 子句中。原因MySQL 5.7 及以上默認(rèn)開(kāi)啟ONLY_FULL_GROUP_BYSELECT 的列必須在 GROUP BY 里出現(xiàn)否則拒絕執(zhí)行。實(shí)訓(xùn)平臺(tái)恰好是 5.7這條規(guī)則卡掉了很多人。解決SELECT 里只保留分組列和聚合函數(shù)。如果確實(shí)需要查非分組列用子查詢(xún)或ANY_VALUE()繞過(guò)但實(shí)訓(xùn)里不推薦這種寫(xiě)法評(píng)分腳本可能不認(rèn)。正確做法是先group by分組再在子查詢(xún)外層取完整記錄。5.5 修改類(lèi)題目重復(fù)執(zhí)行第二次跑答案直接失敗現(xiàn)象同一個(gè)關(guān)卡在本機(jī)練習(xí)時(shí)第二次提交結(jié)果和第一次不一致甚至報(bào)錯(cuò)。原因評(píng)分沙箱每次提交會(huì)重置數(shù)據(jù)庫(kù)但本地練習(xí)時(shí)不會(huì)。帶AUTO_INCREMENT的表第二次插入id 會(huì)繼續(xù)累加UPDATE 把性別從 m 改成 f再跑一遍條件就匹配不上了。解決本地練習(xí)前先DROP TABLE再重新建表或者每次跑之前執(zhí)行TRUNCATE TABLE 表名;把數(shù)據(jù)清零。實(shí)訓(xùn)平臺(tái)的線(xiàn)上提交不用擔(dān)心這個(gè)但本地復(fù)現(xiàn)時(shí)一定要重置環(huán)境否則你會(huì)以為答案寫(xiě)錯(cuò)了實(shí)際是上一輪練習(xí)的數(shù)據(jù)殘留導(dǎo)致的。6. 用一份自檢 SQL 驗(yàn)證實(shí)訓(xùn)答案三分鐘定位對(duì)錯(cuò)拿到這份 PDF 答案之后最高效的用法不是逐條抄而是把它當(dāng)作「通關(guān)對(duì)比表」。每一關(guān)提交前我習(xí)慣先跑三條自檢 SQL確認(rèn)自己的語(yǔ)句和答案在邏輯上等價(jià)而不是在字符串上完全一致。第一類(lèi)自檢是結(jié)構(gòu)檢查。建表或改表類(lèi)關(guān)卡提交前確認(rèn)表存在、字段正確# 查看當(dāng)前庫(kù)里有哪些表 SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA DATABASE(); # 查看表字段類(lèi)型和鍵標(biāo)記 DESC t_emp; # 查看建表語(yǔ)句細(xì)節(jié)重點(diǎn)看 ENGINE 和 CHARSET SHOW CREATE TABLE t_emp;information_schema.TABLES查庫(kù)內(nèi)所有表名DESC看字段順序和 PRI/MUL 標(biāo)記SHOW CREATE TABLE看字符集和引擎。這三條合在一起能覆蓋 80% 的 DDL 類(lèi)關(guān)卡。第二類(lèi)自檢是等價(jià)條件比對(duì)。把答案里的 WHERE 改寫(xiě)成另一種表達(dá)確認(rèn)結(jié)果一致-- 兩種寫(xiě)法結(jié)果應(yīng)完全相同 SELECT * FROM tb_score WHERE score BETWEEN 3000 AND 5000; SELECT * FROM tb_score WHERE score 3000 AND score 5000;如果兩個(gè)結(jié)果的行數(shù)和內(nèi)容一樣說(shuō)明查詢(xún)條件沒(méi)問(wèn)題。如果不一樣優(yōu)先懷疑 BETWEEN 邊界、LIKE 通配符位置、NULL 判斷是不是用了等號(hào)。第三類(lèi)自檢是連接查詢(xún)的總數(shù)校驗(yàn)。多表連接最容易翻倍數(shù)據(jù)用保留表的行數(shù)做對(duì)照# 左外連接的行數(shù)應(yīng)該等于學(xué)生表總行數(shù) SELECT COUNT(*) FROM tb_student s LEFT JOIN tb_class c ON s.class_id c.id; # 學(xué)生表總行數(shù) SELECT COUNT(*) FROM tb_student;左外連接保留學(xué)生表全部行第一個(gè) COUNT 應(yīng)該等于第二個(gè) COUNT。如果第一個(gè)大于第二個(gè)說(shuō)明 ON 條件導(dǎo)致一對(duì)多膨脹如果第一個(gè)小于第二個(gè)說(shuō)明連接條件漏掉了某些匹配關(guān)系。這個(gè)技巧在真實(shí)業(yè)務(wù)里寫(xiě)報(bào)表時(shí)極其好用我每次完成帶 JOIN 的查詢(xún)都會(huì)用保留表的行數(shù)做一次總數(shù)校驗(yàn)?zāi)軘r下大量因?yàn)橐粚?duì)多連接導(dǎo)致的數(shù)據(jù)翻倍。從那以后我每次跑實(shí)訓(xùn)答案和實(shí)際項(xiàng)目 SQL 前都強(qiáng)制走一遍這三板斧結(jié)構(gòu)查一次、等價(jià)條件比對(duì)一次、連接總數(shù)校驗(yàn)一次。這套習(xí)慣幫我省下了大量查錯(cuò)時(shí)間也希望幫到你。本文還有配套的精品資源點(diǎn)擊獲取