簡介這是一份配合《數(shù)據(jù)庫系統(tǒng)概論》課程的SQL建表與數(shù)據(jù)插入練習(xí)文檔面向正在學(xué)習(xí)數(shù)據(jù)庫原理與SQL基礎(chǔ)操作的學(xué)生。PDF內(nèi)完整給出了student、course、sc三張經(jīng)典表的建表語句包含主鍵、唯一約束、默認(rèn)值及外鍵關(guān)聯(lián)并附帶覆蓋學(xué)生選課場景的示例插入數(shù)據(jù)可幫助讀者理解關(guān)系模型、參照完整性及SC復(fù)合主鍵的設(shè)計(jì)思路。整個(gè)壓縮包僅含1個(gè)PDF文件大小48KB輕量易用適合課前預(yù)習(xí)、實(shí)驗(yàn)參考或考前快速復(fù)習(xí)。文檔將創(chuàng)建數(shù)據(jù)庫、建表、插入數(shù)據(jù)與更新預(yù)修課程學(xué)分等步驟按順序拆解并在course表插入中特別說明了外鍵約束下不能一次性插入的原因便于讀者避開常見操作誤區(qū)。目前已有1907人學(xué)習(xí)下載對初學(xué)數(shù)據(jù)庫系統(tǒng)概論、需要?jiǎng)邮执罱ň毩?xí)環(huán)境的學(xué)習(xí)者具有直接參考價(jià)值。1. 數(shù)據(jù)庫系統(tǒng)概論里的 student、sc、course一套練習(xí)表為什么能講透 SQL數(shù)據(jù)庫系統(tǒng)概論課程里流傳最廣的一套SQL練習(xí)就是用 student、sc、course 這三張表反復(fù)折騰查詢很多學(xué)校的實(shí)驗(yàn)課、期末復(fù)習(xí)題、甚至面試前突擊都在用它。這套表的數(shù)據(jù)量不大結(jié)構(gòu)卻把主鍵、外鍵、多對多關(guān)系、聚合、連接、子查詢這些核心概念全串起來了比去 LeetCode 刷零散題目要完整得多。網(wǎng)上流傳的配套 PDF 往往只給題目和答案缺了建表腳本和邊界情況的說明新手拿到手第一反應(yīng)是“表長什么樣”都不知道。我按最常見的教材結(jié)構(gòu)把整套環(huán)境重建一遍從建庫到驗(yàn)證一條龍講清楚學(xué)生能照著跑準(zhǔn)備 SQL 面試的能當(dāng)復(fù)習(xí)提綱想自己出練習(xí)題的也能直接參考這套設(shè)計(jì)。2. 先建庫再建表student、sc、course 的結(jié)構(gòu)設(shè)計(jì)與建表 SQL2.1 三張表的字段設(shè)計(jì)外鍵怎么連、主鍵怎么定先看這套表的數(shù)據(jù)模型。student 表存學(xué)生基本信息course 表存課程信息sc 表是學(xué)生選課成績表用來連接前兩者。之所以需要 sc 這張中間表是因?yàn)橐粋€(gè)學(xué)生可以選多門課一門課也可以被多個(gè)學(xué)生選學(xué)生和課程之間是多對多關(guān)系直接在一個(gè)表里加字段會(huì)造成大量數(shù)據(jù)冗余和更新異常。sc 表把學(xué)號和課程號各存一條記錄再加上成績字段就把多對多關(guān)系拆成了兩個(gè)一對多關(guān)系。student 表的常見字段是學(xué)號、姓名、性別、年齡、系別主鍵用學(xué)號 sno。course 表的主鍵是課程號 cno同時(shí)會(huì)有一個(gè) cpno 字段表示先修課程號這個(gè)字段指向課程表自身的主鍵形成自引用外鍵用來做“查先修課”這類的遞歸查詢練習(xí)。sc 表的主鍵是 (sno, cno) 聯(lián)合主鍵sno 和 cno 分別引用 student 和 course 的主鍵grade 字段存百分制成績。這個(gè)設(shè)計(jì)的巧妙之處在于兩個(gè)字段的聯(lián)合主鍵能防止同一學(xué)生同一課程出現(xiàn)兩條重復(fù)選課記錄這是數(shù)據(jù)完整性約束里的典型考點(diǎn)。練習(xí)時(shí)還會(huì)用到 cpno 為 NULL 的“沒有先修課”的記錄這就把 SQL 里的 NULL 處理難題也帶出來了。字段類型也要留意學(xué)號和課程號雖然是數(shù)字但常見教材把它們定義成 CHAR 而不是 INT目的是讓前導(dǎo)零能保留也方便練習(xí)字符串比較。2.2 標(biāo)準(zhǔn)建表腳本MySQL 與 SQL Server 的語法差異很多初學(xué)者從 PDF 里抄答案時(shí)發(fā)現(xiàn)在自己電腦上建表總是報(bào)錯(cuò)原因往往是教材示例用的 SQL Server 語法而本機(jī)裝的是 MySQL。兩種數(shù)據(jù)庫的建表語法大體相似但在字符集、自增列、外鍵約束這些細(xì)節(jié)上有差異。下面給出兩套腳本先看 MySQL 版本這也是目前自學(xué)環(huán)境最常見的配置。CREATE DATABASE IF NOT EXISTS school DEFAULT CHARSET utf8mb4; USE school; CREATE TABLE student ( sno CHAR(9) PRIMARY KEY, sname VARCHAR(20) NOT NULL, ssex CHAR(2) DEFAULT 男, sage SMALLINT, sdept VARCHAR(20) ) ENGINEInnoDB; CREATE TABLE course ( cno CHAR(4) PRIMARY KEY, cname VARCHAR(40) NOT NULL, cpno CHAR(4), ccredit SMALLINT ) ENGINEInnoDB; CREATE TABLE sc ( sno CHAR(9) NOT NULL, cno CHAR(4) NOT NULL, grade DECIMAL(5,1), PRIMARY KEY (sno, cno), FOREIGN KEY (sno) REFERENCES student(sno), FOREIGN KEY (cno) REFERENCES course(cno) ) ENGINEInnoDB;這段腳本里幾個(gè)參數(shù)要單獨(dú)說明。DEFAULT CHARSET utf8mb4 是為了讓中文姓名和課程名正常存儲如果使用默認(rèn)的 latin1 字符集插入中文時(shí)會(huì)變成亂碼或者直接報(bào)錯(cuò)utf8mb4 比 utf8 多支持 emoji 等四字節(jié)字符對練習(xí)環(huán)境來說更保險(xiǎn)。表引擎指定 InnoDB是因?yàn)橥怄I約束只有 InnoDB 支持MyISAM 引擎雖然查詢快但建外鍵時(shí) MySQL 會(huì)靜默忽略不報(bào)錯(cuò)也不生效這是最隱蔽的一個(gè)翻車點(diǎn)。sage 用 SMALLINT 而不是 INT是教材里的經(jīng)典設(shè)計(jì)用來讓大家理解“夠用就好”的字段長度設(shè)計(jì)思路。grade 用 DECIMAL(5,1)意思是總長度 5 位、小數(shù) 1 位最高能存 999.9百分制成績足夠用而且能保留一位小數(shù)方便練習(xí) AVG 聚合時(shí)看到非整數(shù)值。如果是 SQL Server 環(huán)境比如學(xué)校機(jī)房或者公司里的老項(xiàng)目建表腳本要微調(diào)。SQL Server 不區(qū)分 utf8mb4默認(rèn)就是在排序規(guī)則屬性里處理中文自增列需要 IDENTITY 關(guān)鍵字CHAR 字段在 SQL Server 里默認(rèn)非 Unicode插入中文要改成 NCHAR 或者設(shè)置合適的排序規(guī)則。CREATE DATABASE school; GO USE school; GO CREATE TABLE student ( sno CHAR(9) PRIMARY KEY, sname NVARCHAR(20) NOT NULL, ssex NCHAR(1) DEFAULT N男, sage SMALLINT, sdept NVARCHAR(20) ); GO CREATE TABLE course ( cno CHAR(4) PRIMARY KEY, cname NVARCHAR(40) NOT NULL, cpno CHAR(4), ccredit SMALLINT ); GO CREATE TABLE sc ( sno CHAR(9) NOT NULL, cno CHAR(4) NOT NULL, grade DECIMAL(5,1), PRIMARY KEY (sno, cno), FOREIGN KEY (sno) REFERENCES student(sno), FOREIGN KEY (cno) REFERENCES course(cno) ); GO這里把姓名、系別、課程名都換成了 NVARCHAR原因很實(shí)在SQL Server 的 VARCHAR 只能存英文字符N 前綴類型才能存中文給 ssex 字段加 DEFAULT N男 是因?yàn)?SQL Server 默認(rèn)值里如果直接寫中文字符常量在有些中文排序規(guī)則下會(huì)報(bào)“對象名無效”一類的怪異錯(cuò)誤實(shí)測最穩(wěn)的方式就是加 N 前綴。用了 GO 分隔批次是因?yàn)?SQL Server 的 CREATE DATABASE 之后必須 USE 切換上下文如果兩個(gè)語句在同一個(gè)批次里執(zhí)行后面的 CREATE TABLE 可能仍然被解析到 master 庫。這塊屬于 SQL Server 特有的玄學(xué)問題按上面分批次寫就不會(huì)出錯(cuò)。2.3 插入練習(xí)數(shù)據(jù)覆蓋基礎(chǔ)查詢需要的典型取值建完表之后必須插入數(shù)據(jù)才能練習(xí)。數(shù)據(jù)量不用大但要覆蓋邊界情況NULL 成績、先修課缺失、同系別多人、男女都有、成績重復(fù)這些都會(huì)在后續(xù)查詢練習(xí)里反復(fù)用到。下面這套插入數(shù)據(jù)是經(jīng)典教材里的標(biāo)準(zhǔn)樣本數(shù)值我按常用版本保持基本一致。INSERT INTO student (sno, sname, ssex, sage, sdept) VALUES (201215121, 李勇, 男, 20, CS), (201215122, 劉晨, 女, 19, CS), (201215123, 王敏, 女, 18, MA), (201215125, 張立, 男, 19, IS); INSERT INTO course (cno, cname, cpno, ccredit) VALUES (1, 數(shù)據(jù)庫, NULL, 4), (2, 數(shù)學(xué), NULL, 2), (3, 信息系統(tǒng), 1, 4), (4, 操作系統(tǒng), 6, 3), (5, 數(shù)據(jù)結(jié)構(gòu), 7, 4), (6, 數(shù)據(jù)處理, NULL, 2), (7, PASCAL語言, 6, 4); INSERT INTO sc (sno, cno, grade) VALUES (201215121, 1, 92), (201215121, 2, 85), (201215121, 3, 88), (201215122, 2, 90), (201215122, 3, 80), (201215123, 2, 85), (201215123, 1, NULL);這段插入腳本里有兩處需要細(xì)看。course 表里課程 3 的 cpno 是課程 1課程 5 的 cpno 是課程 7形成了一個(gè)有向的“先修課鏈條”練習(xí)自連接時(shí)可以查“間接先修課”這類題目。sc 表里最后一條記錄 grade 為 NULL表示王敏選了數(shù)據(jù)庫課但還沒出成績這就是聚合函數(shù)和 WHERE 條件練習(xí)里最容易出錯(cuò)的地方COUNT(grade) 會(huì)忽略 NULL而 COUNT(*) 不會(huì)。INSERT 語句用多值逗號分隔語法比逐條 INSERT 效率高很多而且一旦中間某行出錯(cuò)整個(gè)語句會(huì)回滾不會(huì)出現(xiàn)“插了一半”的臟數(shù)據(jù)。數(shù)據(jù)插入后建議先跑兩條驗(yàn)證語句確認(rèn)環(huán)境可用。SELECT COUNT() FROM student; 應(yīng)該返回 4SELECT COUNT() FROM sc; 應(yīng)該返回 7。如果數(shù)字對不上優(yōu)先檢查是否漏跑了某個(gè)批次或者外鍵約束攔截了部分插入。3. 練習(xí)題的梯度設(shè)計(jì)從單表查詢到窗口函數(shù)3.1 第一梯度單表查詢、排序與去重表建好、數(shù)據(jù)插完就可以開始真正的練習(xí)。第一梯度只涉及單張表目標(biāo)是練 SELECT 的基礎(chǔ)功投影、選擇、排序、去重。這類題目在面試?yán)锟粗唵蔚芏嗳藢懗鰜淼?SQL 在邊界情況下是錯(cuò)誤的尤其是去重和 NULL 處理。先看一個(gè)最基礎(chǔ)的查詢查所有學(xué)生的學(xué)號和姓名。這道題沒有難度但能驗(yàn)證表結(jié)構(gòu)是否正常。SELECT sno, sname FROM student;接著看一個(gè)稍微有考點(diǎn)的題目查詢所有學(xué)生的系別。如果直接寫 SELECT sdept FROM student結(jié)果里會(huì)出現(xiàn)重復(fù)的 CS。要得到唯一的系別列表得去重。SELECT DISTINCT sdept FROM student;DISTINCT 在這里的語義是“對查詢結(jié)果整體去重”也就是說只有兩行所有選中列的值都相同才合并。它作用于整個(gè) SELECT 的投影列表而不是只作用于緊挨著的那個(gè)字段。很多人以為 SELECT DISTINCT sname, sdept 的意思是“只對 sname 去重保留 sdept”實(shí)際上它是把 (sname, sdept) 作為一個(gè)整體判斷重復(fù)。這是一個(gè)高頻面試題也解釋了為什么同樣一句 DISTINCT 在不同組合列下結(jié)果差異巨大。第二梯度之前還要過一個(gè)排序練習(xí)查全體學(xué)生名單按年齡降序排列。SELECT sno, sname, sage FROM student ORDER BY sage DESC;ORDER BY 的執(zhí)行順序在 SQL 邏輯中排在 SELECT 之后所以它可以使用 SELECT 列表里的別名比如 ORDER BY 年齡。這個(gè)順序問題很多人混淆實(shí)際上 SQL 語句的書寫順序是 SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY但邏輯執(zhí)行順序是 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY。搞清楚這個(gè)順序后面理解“為什么 WHERE 里不能使用別名”才不會(huì)靠死記硬背。3.2 第二梯度多表連接、聚合與分組第二梯度是這套練習(xí)表的重頭戲也是 SQL 能力分水嶺。連接查詢要把兩張或多張表按條件拼接聚合查詢則把多行匯總成一行。兩者經(jīng)常組合出現(xiàn)。最常見的連接練習(xí)查詢每個(gè)學(xué)生的選課成績需要 student 表和 sc 表通過 sno 連接。SELECT student.sno, sname, cno, grade FROM student, sc WHERE student.sno sc.sno;這是隱式連接寫法教材里用得最多但實(shí)際項(xiàng)目里我更推薦顯式 JOIN 寫法結(jié)構(gòu)更清晰也更容易排查漏連接條件的問題。SELECT s.sno, s.sname, sc.cno, sc.grade FROM student s JOIN sc ON s.sno sc.sno;這兩條 SQL 邏輯等價(jià)但顯式寫法把連接條件從 WHERE 中分離出來WHERE 里只留過濾條件。這種拆分的實(shí)際意義是當(dāng)查詢超過三張表時(shí)隱式連接特別容易漏寫一個(gè)連接條件導(dǎo)致產(chǎn)生笛卡爾積結(jié)果行數(shù)爆炸到幾十萬條這就是俗稱的“翻車現(xiàn)場”排查時(shí)還不好定位。顯式 JOIN 配合縮進(jìn)每個(gè)連接關(guān)系一眼就能看全。連接之后接聚合查詢每門課的選課人數(shù)和平均成績。SELECT cno, COUNT(*) AS cnt, AVG(grade) AS avg_grade FROM sc GROUP BY cno;這段 SQL 的執(zhí)行順序是先 FROM sc 拿到全表GROUP BY cno 把相同課程號的記錄分到一組然后 SELECT 對每組執(zhí)行 COUNT 和 AVG。這里有一個(gè)關(guān)鍵細(xì)節(jié)AVG(grade) 會(huì)自動(dòng)忽略 NULL所以王敏那條沒有成績的記錄不會(huì)拉低平均值。如果想把 NULL 當(dāng)作 0 參與平均就得先寫 CASE WHEN grade IS NULL THEN 0 ELSE grade END 把 NULL 轉(zhuǎn)換掉但這在實(shí)際業(yè)務(wù)中是否合理要看場景一般成績表里 NULL 代表“缺考/未出分”忽略比當(dāng) 0 處理更符合業(yè)務(wù)語義。再進(jìn)階一步查詢選了 2 門及以上課程的學(xué)生學(xué)號。這要用 HAVING 對分組結(jié)果做過濾。SELECT sno FROM sc GROUP BY sno HAVING COUNT(*) 2;HAVING 和 WHERE 的區(qū)別必須在這里說透WHERE 在分組之前過濾行HAVING 在分組之后過濾組。同一個(gè)條件寫在 WHERE 和寫在 HAVING 里語義完全不同。比如查出平均成績大于 85 的課程AVG(grade) 85 這個(gè)條件只能放在 HAVING 里因?yàn)樗恰敖M”的特征不是“行”的特征。很多初學(xué)者把聚合條件誤寫在 WHERE 里報(bào)錯(cuò)就是因?yàn)闆]理解這個(gè)執(zhí)行順序。3.3 第三梯度子查詢、EXISTS 與窗口函數(shù)第三梯度是進(jìn)階內(nèi)容也是 PDF 練習(xí)里區(qū)分“會(huì)寫”和“寫得優(yōu)雅”的部分。子查詢分相關(guān)子查詢和非相關(guān)子查詢兩類。非相關(guān)子查詢的結(jié)果不依賴外層查詢先執(zhí)行一次即可相關(guān)子查詢每處理一行都要執(zhí)行一次子查詢性能差異很大。經(jīng)典題目查詢選修了課程 1 且成績高于該課程平均分的學(xué)生學(xué)號。SELECT sno FROM sc WHERE cno 1 AND grade ( SELECT AVG(grade) FROM sc WHERE cno 1 );這個(gè)子查詢是典型的非相關(guān)子查詢內(nèi)層執(zhí)行一次得到課程 1 的平均分外層再逐行比較。注意如果 AVG 結(jié)果帶多位小數(shù)和外層 grade 比較時(shí)不會(huì)因類型問題出錯(cuò)因?yàn)?MySQL 會(huì)按 DECIMAL 精度自動(dòng)比較。這道題練習(xí)的價(jià)值在于它要求先理解聚合結(jié)果是標(biāo)量值然后才能把它當(dāng)作 WHERE 的右值。再來看 EXISTS 子查詢這也是 SQL 面試題的常駐嘉賓。查出所有選了課程 2 的學(xué)生姓名。SELECT sname FROM student s WHERE EXISTS ( SELECT 1 FROM sc WHERE sc.sno s.sno AND sc.cno 2 );EXISTS 子查詢只關(guān)心“有沒有返回行”不關(guān)心返回什么列所以子查詢里寫 SELECT 1 是業(yè)界慣例比 SELECT * 性能略優(yōu)且語義清晰。這個(gè)查詢是相關(guān)子查詢對 student 表每一行都要執(zhí)行一次子查詢但數(shù)據(jù)庫優(yōu)化器通常會(huì)把它改寫成 JOIN 或 semi-join 來提升性能。練習(xí)時(shí)可以把 EXISTS、IN、JOIN 三種寫法都試一遍觀察執(zhí)行計(jì)劃差異這是理解優(yōu)化器行為的很好素材。窗口函數(shù)是近幾年 SQL 面試的高頻考點(diǎn)這套表同樣能練。比如查每門課成績排名前 2 的學(xué)生。SELECT cno, sno, grade FROM ( SELECT cno, sno, grade, ROW_NUMBER() OVER (PARTITION BY cno ORDER BY grade DESC) AS rk FROM sc WHERE grade IS NOT NULL ) t WHERE rk 2 ORDER BY cno;窗口函數(shù)的邏輯是OVER 子句定義窗口范圍PARTITION BY 劃分組ORDER BY 組內(nèi)排序ROW_NUMBER() 在窗口內(nèi)生成序號。和 GROUP BY 不同的是窗口函數(shù)不會(huì)把多行合并成一行每行仍然保留自己的身份。這個(gè)特性讓“分組 top N”這類查詢變得非常簡潔。注意內(nèi)層先 WHERE grade IS NOT NULL 過濾掉 NULL 成績否則排序時(shí)空值會(huì)默認(rèn)排最前面結(jié)果就錯(cuò)了。3.4 練習(xí)答案的驗(yàn)證方式用 EXPLAIN 看執(zhí)行計(jì)劃光寫出能跑出結(jié)果的 SQL 不算完還得知道它是怎么跑的。數(shù)據(jù)庫提供了 EXPLAIN 命令來查看執(zhí)行計(jì)劃這是從“會(huì)寫 SQL”到“能調(diào) SQL”的必經(jīng)一步。以 MySQL 為例EXPLAIN SELECT s.sno, s.sname, sc.grade FROM student s JOIN sc ON s.sno sc.sno WHERE sc.cno 1;執(zhí)行計(jì)劃里最需要關(guān)注的是 type 列和 rows 列。type 顯示訪問表的方式從好到差依次是 system、const、eq_ref、ref、range、index、ALL。如果看到 ALL說明這條 SQL 正在做全表掃描。rows 列是預(yù)估掃描行數(shù)兩張表 join 的最終掃描行數(shù)異常大時(shí)要檢查外鍵列上有沒有索引。sc 表的 sno 和 cno 列如果沒建索引JOIN 就會(huì)退化成嵌套循環(huán)全表掃數(shù)據(jù)量小的時(shí)候感覺不到到了生產(chǎn)環(huán)境幾百萬行就徹底卡死了。做練習(xí)時(shí)每寫完一道題都順手把執(zhí)行計(jì)劃拉出來看一眼。等把 system→const→ref→range→ALL 這幾檔都親眼見過一遍才算真正對 SQL 優(yōu)化有了體感。到這一步student、sc、course 這套練習(xí)表的價(jià)值才算被完全榨干——它不僅教你寫 SQL還教你診斷 SQL。4. 做這套練習(xí)常見的 5 個(gè)坑從建表失敗到結(jié)果對不上4.1 主鍵重復(fù)與 NULL插入數(shù)據(jù)時(shí)的第一道坎現(xiàn)象執(zhí)行 INSERT 語句報(bào)錯(cuò)提示 duplicate entry 或者不能為 NULL。原因聯(lián)合主鍵的任意重復(fù)都會(huì)觸發(fā)沖突比如向 sc 表重復(fù)插入 (201215121, 1) 這條選課記錄。另外student 表的 sno 是主鍵插入 NULL 也會(huì)被拒絕但 sc 表的 sno 和 cno 是外鍵同時(shí)又是聯(lián)合主鍵的一部分如果這兩列有一個(gè)為 NULLInnoDB 對主鍵列有隱式 NOT NULL 約束同樣報(bào)錯(cuò)。解決插入前先用 SELECT 確認(rèn)數(shù)據(jù)是否存在。習(xí)慣性在插入腳本里加 INSERT IGNORE 或者 ON DUPLICATE KEY UPDATE 確實(shí)能跳過錯(cuò)誤但練習(xí)場景不建議這么做因?yàn)檫@會(huì)掩蓋數(shù)據(jù)本身的重復(fù)問題。更推薦的方法是直接面對報(bào)錯(cuò)通過 SELECT sno, cno, COUNT() FROM sc GROUP BY sno, cno HAVING COUNT() 1 查重復(fù)數(shù)據(jù)再?zèng)Q定保留哪一條。4.2 字符集與排序規(guī)則中文亂碼的根源現(xiàn)象插入中文后查詢顯示亂碼或者 CREATE TABLE 直接報(bào)錯(cuò)字符集不識別。原因MySQL 默認(rèn)字符集可能是 latin1中文無法存儲。SQL Server 則可能是排序規(guī)則不匹配導(dǎo)致數(shù)據(jù)庫兼容級別或排序規(guī)則沖突。解決MySQL 建庫時(shí)顯式指定 DEFAULT CHARSET utf8mb4并且連接字符串里也要加上 characterEncodingutf8 或 utf8mb4。一個(gè)容易被忽略的坑是即使數(shù)據(jù)庫和表的字符集都對了如果客戶端連接的字符集不對插入時(shí)仍會(huì)出現(xiàn)亂碼。先執(zhí)行 SHOW VARIABLES LIKE character_set%; 看當(dāng)前會(huì)話的字符集再把連接參數(shù)統(tǒng)一。SQL Server 那邊建庫時(shí)把排序規(guī)則指定為 Chinese_PRC_CI_AS 基本可以一勞永逸。4.3 外鍵約束與刪除順序先刪 sc 還是先刪 student現(xiàn)象執(zhí)行 DELETE FROM student WHERE sno201215121; 報(bào)錯(cuò)提示 foreign key constraint fails。原因sc 表里有引用這個(gè)學(xué)號的記錄而 sc 表的外鍵約束默認(rèn)行為是 NO ACTION即存在子記錄時(shí)不允許刪除父記錄。解決先刪子表再刪父表。正確順序是 DELETE FROM sc WHERE sno201215121; 然后 DELETE FROM student WHERE sno201215121;。這個(gè)順序問題的價(jià)值不僅在于完成刪除操作它本身就是關(guān)系模型里“引用完整性”的絕佳教學(xué)案例。實(shí)際業(yè)務(wù)中還有一種做法是給外鍵加上 ON DELETE CASCADE讓數(shù)據(jù)庫自動(dòng)刪子記錄但這在生產(chǎn)環(huán)境非常危險(xiǎn)一不小心就級聯(lián)刪掉大量業(yè)務(wù)數(shù)據(jù)我一般建議顯式控制刪除順序而不是依賴級聯(lián)。4.4 連接條件漏寫笛卡爾積的翻車現(xiàn)場現(xiàn)象多表連接查詢返回的結(jié)果行數(shù)異常多比如 student 4 行和 sc 7 行連接后返回 28 行。原因連接條件沒寫或者寫錯(cuò)數(shù)據(jù)庫把兩表做了笛卡爾積每行和另一表的每行配對。解決連接查詢時(shí)先確認(rèn)結(jié)果行數(shù)是否等于符合業(yè)務(wù)邏輯的行數(shù)。經(jīng)驗(yàn)判斷標(biāo)準(zhǔn)是如果多表連接沒有 WHERE 或 JOIN ON 條件返回行數(shù)是各表行數(shù)乘積必然有問題。連接條件漏寫時(shí)數(shù)據(jù)庫不會(huì)報(bào)錯(cuò)因?yàn)榈芽柗e在語法上是合法的這是最讓人頭疼的地方。實(shí)踐中寫多表查詢時(shí)先分別跑一遍 SELECT COUNT(*) FROM 各表估算出預(yù)期結(jié)果行數(shù)范圍再執(zhí)行連接查詢。另外養(yǎng)成每張表用別名、JOIN 條件顯式寫在 ON 子句里的習(xí)慣能顯著降低漏寫概率。4.5 聚合與非聚合混用GROUP BY 的經(jīng)典報(bào)錯(cuò)現(xiàn)象執(zhí)行 SELECT sno, cno, AVG(grade) FROM sc GROUP BY sno; 報(bào)錯(cuò)提示 cno 不在 GROUP BY 子句中。原因SQL 標(biāo)準(zhǔn)要求 SELECT 列表中出現(xiàn)的非聚合列必須全部出現(xiàn)在 GROUP BY 中。這是因?yàn)榉纸M后每組只有一行非聚合列如果不在分組鍵里數(shù)據(jù)庫不知道該取組內(nèi)哪行的值。MySQL 的 ONLY_FULL_GROUP_BY 模式默認(rèn)開啟所以直接報(bào)錯(cuò)如果關(guān)掉這個(gè)模式雖然能執(zhí)行但返回的 cno 是組內(nèi)隨機(jī)一行結(jié)果不可預(yù)測。解決搞清楚查詢意圖。如果確實(shí)想查每個(gè)學(xué)生的學(xué)號和他每門課的成績就不該用 GROUP BY如果只想查每個(gè)學(xué)生平均分SELECT 里就不該出現(xiàn) cno。這個(gè)報(bào)錯(cuò)其實(shí)是數(shù)據(jù)庫在保護(hù)你比暗中返回隨機(jī)值好得多。練習(xí)時(shí)遇到這種錯(cuò)誤先停下來理清“我要的是明細(xì)還是匯總”再動(dòng)手改 SQL是最快的路徑。5. 把練習(xí)成果接到真項(xiàng)目驗(yàn)證方法、索引設(shè)計(jì)與一條實(shí)用技巧5.1 用索引設(shè)計(jì)與執(zhí)行計(jì)劃對比來驗(yàn)證練習(xí)效果練習(xí)做完后建議做一個(gè)加索引前后的對比實(shí)驗(yàn)。以 sc 表為例在不建任何輔助索引的情況下執(zhí)行按 cno 過濾的查詢EXPLAIN 結(jié)果會(huì)顯示 typeALL 全表掃描。然后手動(dòng)加一個(gè)索引CREATE INDEX idx_sc_cno ON sc(cno);再執(zhí)行同樣的查詢EXPLAIN 里 type 會(huì)變成 refrows 預(yù)估數(shù)明顯下降。這個(gè)對比就是把前面練習(xí)里學(xué)到的東西真正用到性能優(yōu)化上的關(guān)鍵一步。實(shí)際業(yè)務(wù)中聯(lián)合索引的設(shè)計(jì)比單列索引更值得關(guān)注。sc 表最常見的查詢模式是按 (sno, cno) 查成績或者按 cno 查選課名單。如果業(yè)務(wù)偏向前者聯(lián)合主鍵已經(jīng)覆蓋如果后面要頻繁按 cno 查單建 idx_sc_cno 就能避免每次回表。5.2 把三張表遷移到真實(shí)業(yè)務(wù)場景邊界在哪里student、sc、course 畢竟是教學(xué)模型遷移到真實(shí)項(xiàng)目時(shí)有三個(gè)明顯的邊界要注意。一是數(shù)據(jù)量生產(chǎn)環(huán)境單表百萬行起步這套練習(xí)表沒有分區(qū)、沒有數(shù)據(jù)生命周期管理直接照搬會(huì)有性能問題。二是缺失審計(jì)字段真實(shí)業(yè)務(wù)表幾乎都要有 create_time、update_time、操作人這幾個(gè)字段教學(xué)表里完全沒有上線前要補(bǔ)。三是沒有軟刪除和狀態(tài)字段真實(shí)業(yè)務(wù)不會(huì)物理刪除記錄而是用 status 字段標(biāo)記有效性練習(xí)表的 DELETE 操作要改成 UPDATE status。一個(gè)比較實(shí)用的落地方式是把這三張表當(dāng)作一個(gè)“最小可運(yùn)行的數(shù)據(jù)模型”在公司內(nèi)部做技術(shù)分享或者新人培訓(xùn)時(shí)用來演示慢 SQL 優(yōu)化和連接查詢原理。數(shù)據(jù)量小、字段簡單、問題復(fù)現(xiàn)快比拿生產(chǎn)庫演練安全得多。我有一個(gè)習(xí)慣每次講 SQL 優(yōu)化之前先在本地用這套表造 10 萬行數(shù)據(jù)然后故意寫幾條不帶 WHERE 的連接查詢讓新人親眼看到全表掃描導(dǎo)致查詢時(shí)間從毫秒級變成秒級再讓他們自己用 EXPLAIN 定位問題。這種直觀對比比講半小時(shí)理論都有用。希望幫到你。本文還有配套的精品資源點(diǎn)擊獲取