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