簡介這份資源是北郵研一數(shù)據(jù)庫課程大作業(yè)的完整詳解文檔面向正在修讀數(shù)據(jù)庫系統(tǒng)課程、需要完成課程設計的研究生及高年級本科生。內(nèi)容圍繞學生成績管理系統(tǒng)展開覆蓋需求分析、數(shù)據(jù)庫設計、ER圖繪制、邏輯結(jié)構(gòu)設計與建表程序等完整流程可幫助讀者理解從需求到實現(xiàn)的規(guī)范化設計思路。壓縮包內(nèi)共1個docx文件約503KB以文字與圖表形式呈現(xiàn)便于直接參考與整理。文檔詳細給出Course、Student、Sc、Teacher四張表的字段定義、主外鍵約束與一對多關(guān)系并包含不及格學生名單統(tǒng)計、無教學任務教師查詢等典型功能設計以及局部與全局ER圖、數(shù)據(jù)字典和建表SQL語句。目前已有606人學習下載適合需要課程設計參考、數(shù)據(jù)庫建模練習或期末復習的讀者借鑒其設計框架與實現(xiàn)細節(jié)。1. 北郵研一數(shù)據(jù)庫大作業(yè)從選題到跑通一個能交差的完整路徑北郵研一數(shù)據(jù)庫大作業(yè)通常不是讓你從零寫一個數(shù)據(jù)庫內(nèi)核而是給定一個業(yè)務場景要求完成需求分析、E-R 建模、建庫建表、增刪改查、視圖/存儲過程/觸發(fā)器最后交一份報告加可運行的 SQL 腳本。熱詞里高頻出現(xiàn)的 MySQL、Navicat、學生成績管理系統(tǒng)恰好就是這類作業(yè)最常見的組合。我?guī)н^幾屆本科和研一的數(shù)據(jù)庫課設發(fā)現(xiàn)真正卡住大家的不是 SQL 語法而是選題太虛、表結(jié)構(gòu)反復改、數(shù)據(jù)對不上、報告寫不出設計取舍。這篇筆記按我實際帶學生做項目的順序把選題、建模、建庫、寫查詢、加高級對象、排錯、驗收串成一條能直接照著走的路徑。適合剛?cè)雽W、手上只有一臺筆記本、需要在兩三周內(nèi)交出一個像樣系統(tǒng)的研一同學。2. 選題與需求為什么學生成績管理系統(tǒng)是安全牌2.1 先判斷作業(yè)到底在考什么很多同學一上來就想做“校園二手交易平臺”或者“實驗室設備管理”覺得新鮮。但數(shù)據(jù)庫大作業(yè)的評分點集中在數(shù)據(jù)建模是否規(guī)范、約束是否完整、查詢是否覆蓋業(yè)務、高級對象是否用對而不是業(yè)務本身有多花哨。學生成績管理系統(tǒng)之所以成為經(jīng)典是因為它的實體和聯(lián)系天然清晰學生、課程、教師、班級、選課、成績一對多和多對多關(guān)系齊全正好能把主外鍵、唯一約束、級聯(lián)、聚合查詢?nèi)烤氁槐?。你換成別的題目如果實體關(guān)系沒這么干凈后面寫 SQL 會一直別扭。判斷標準很簡單你的系統(tǒng)里有沒有至少兩個多對多關(guān)系有沒有需要事務保證一致性的操作有沒有需要按維度統(tǒng)計的報表。學生成績管理系統(tǒng)里學生選課是多對多教師授課也是多對多成績錄入和修改需要事務班級平均分、課程及格率是典型報表。這三點齊了作業(yè)的技術(shù)含量就夠。2.2 需求清單要落到字段級別需求分析不要寫成散文。我一般讓學生直接列三張清單實體清單、屬性清單、業(yè)務操作清單。實體清單寫清楚有哪些對象屬性清單寫到字段名、類型、是否可空、默認值業(yè)務操作清單寫清楚誰會做什么比如“教務老師錄入成績”“學生查詢個人成績”“班主任查看班級排名”。以學生成績管理系統(tǒng)為例核心實體至少包括學生、班級、課程、教師、選課記錄。選課記錄這張表是關(guān)鍵它承載學生和課程的多對多關(guān)系同時掛成績字段。很多同學把成績直接放在學生表或課程表里這是典型建模錯誤會導致一個學生只能有一門課的成績。注意屬性清單里一定要標出哪些字段需要唯一約束比如學號、課程編號、教師工號。這些約束后面建表時直接寫進 DDL比在應用層判斷可靠得多。2.3 從需求到 E-R 圖的最小步驟E-R 圖不用畫得多漂亮但實體、屬性、聯(lián)系三要素必須齊全。我通常用 draw.io 或者紙筆先畫確認三件事每個實體有沒有主鍵每個多對多聯(lián)系有沒有拆成獨立的關(guān)系表每個一對多聯(lián)系的外鍵放在哪一側(cè)。確認完再轉(zhuǎn)成關(guān)系模式也就是最終的表結(jié)構(gòu)。關(guān)系模式寫出來大概是這個形式學生(學號, 姓名, 性別, 出生日期, 班級編號)班級(班級編號, 班級名稱, 入學年份)課程(課程編號, 課程名稱, 學分, 教師工號)教師(教師工號, 姓名, 職稱)選課(學號, 課程編號, 成績, 選課時間)這里選課表的主鍵是(學號, 課程編號)復合主鍵成績允許為空表示尚未錄入。這個設計決定了后面所有查詢的寫法所以務必在動手建表前定下來。3. 建庫建表MySQL 8 下的 DDL 與約束落地3.1 環(huán)境準備與字符集選擇本地跑 MySQL 8 是最省事的方案。安裝完成后第一件事是確認字符集否則中文姓名和課程名會出現(xiàn)亂碼。建庫時顯式指定 utf8mb4排序規(guī)則用 utf8mb4_0900_ai_ci這是 MySQL 8 的默認推薦對中文和 emoji 都友好。-- 創(chuàng)建數(shù)據(jù)庫顯式指定字符集避免中文亂碼 CREATE DATABASE score_mgmt DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_0900_ai_ci; USE score_mgmt;字符集這件事看起來小但每年都有學生因為庫、表、連接三層字符集不一致導致 Navicat 里看到問號。建庫時定好后面建表繼承庫的設置連接串里也寫 utf8mb4基本不會翻車。3.2 建表順序與外鍵依賴建表必須按依賴順序來先建被引用的表再建引用它們的表。班級和教師不依賴別人先建學生依賴班級課程依賴教師其次選課依賴學生和課程最后建。順序錯了會報外鍵約束錯誤。-- 班級表被學生表引用先建 CREATE TABLE class ( class_id INT PRIMARY KEY AUTO_INCREMENT, class_name VARCHAR(50) NOT NULL, enroll_year YEAR NOT NULL ) ENGINEInnoDB; -- 教師表被課程表引用 CREATE TABLE teacher ( teacher_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(30) NOT NULL, title VARCHAR(20) DEFAULT 講師 ) ENGINEInnoDB; -- 學生表外鍵指向班級 CREATE TABLE student ( student_id CHAR(10) PRIMARY KEY, -- 學號固定10位 name VARCHAR(30) NOT NULL, gender ENUM(男,女) DEFAULT 男, birth_date DATE, class_id INT, CONSTRAINT fk_student_class FOREIGN KEY (class_id) REFERENCES class(class_id) ON DELETE SET NULL ) ENGINEInnoDB; -- 課程表外鍵指向教師 CREATE TABLE course ( course_id CHAR(8) PRIMARY KEY, course_name VARCHAR(60) NOT NULL, credit DECIMAL(3,1) NOT NULL DEFAULT 2.0, teacher_id INT, CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id) ON DELETE SET NULL ) ENGINEInnoDB; -- 選課表復合主鍵兩個外鍵 CREATE TABLE enrollment ( student_id CHAR(10), course_id CHAR(8), score DECIMAL(5,2) DEFAULT NULL, -- 空表示未錄入 enroll_time DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (student_id, course_id), CONSTRAINT fk_enroll_student FOREIGN KEY (student_id) REFERENCES student(student_id) ON DELETE CASCADE, CONSTRAINT fk_enroll_course FOREIGN KEY (course_id) REFERENCES course(course_id) ON DELETE CASCADE ) ENGINEInnoDB;幾個參數(shù)值得說明。學號用 CHAR(10) 而不是 INT因為學號可能有前導零用整數(shù)會丟零。成績用 DECIMAL(5,2)能表示 0 到 999.99保留兩位小數(shù)比 FLOAT 精確避免 59.999 被顯示成 60 的玄學問題。選課表用復合主鍵天然保證同一個學生不能重復選同一門課。外鍵的 ON DELETE 行為要按業(yè)務定學生刪除后選課記錄跟著刪用 CASCADE班級刪除后學生保留但班級置空用 SET NULL。3.3 索引與默認值的取舍主鍵自動帶索引外鍵列 MySQL 也會自動建索引。除此之外經(jīng)常用來查詢的列可以手動加索引比如學生表的 name、選課表的 score。但索引不是越多越好每個索引都會拖慢插入速度。作業(yè)規(guī)模下加一兩個關(guān)鍵索引就夠。默認值方面熱詞里有人搜“mysql 設置默認值為 0”這在成績場景要小心。成績默認值如果設成 0未錄入和考零分會混淆。我一般把成績默認值設為 NULL用 IS NULL 判斷未錄入語義清晰。只有像學分這種一定有值的字段才給默認值。提示建完表后用SHOW CREATE TABLE enrollment;檢查一遍確認外鍵和字符集都符合預期比事后出問題再回頭查省事得多。4. 增刪改查與高級對象把作業(yè)的技術(shù)分拿滿4.1 基礎 CRUD 與批量數(shù)據(jù)生成建完表先灌數(shù)據(jù)。手工插幾條能驗證邏輯但要跑統(tǒng)計查詢至少需要幾十個學生、幾門課、上百條選課記錄。我一般寫一段 INSERT 批量插入或者用存儲過程循環(huán)生成。手工插的話注意外鍵順序先插班級、教師再插學生、課程最后插選課。-- 按依賴順序插入基礎數(shù)據(jù) INSERT INTO class (class_name, enroll_year) VALUES (通信2101, 2021), (通信2102, 2021), (計算機2101, 2021); INSERT INTO teacher (name, title) VALUES (張老師, 教授), (李老師, 副教授), (王老師, 講師); INSERT INTO student (student_id, name, gender, birth_date, class_id) VALUES (2021010101, 趙一, 男, 2003-05-12, 1), (2021010102, 錢二, 女, 2003-08-20, 1), (2021010103, 孫三, 男, 2003-02-01, 2); INSERT INTO course (course_id, course_name, credit, teacher_id) VALUES (CS101, 數(shù)據(jù)庫原理, 3.0, 1), (CS102, 數(shù)據(jù)結(jié)構(gòu), 4.0, 2), (MA101, 高等數(shù)學, 5.0, 3); INSERT INTO enrollment (student_id, course_id, score) VALUES (2021010101, CS101, 88.5), (2021010101, CS102, 76.0), (2021010102, CS101, 92.0), (2021010103, MA101, NULL); -- 未錄入插入時注意選課表的 score 允許 NULL所以最后一條表示孫三選了高數(shù)但成績還沒錄。這個 NULL 在后面統(tǒng)計平均分時會被自動忽略符合業(yè)務預期。4.2 多表連接與聚合查詢作業(yè)里最能體現(xiàn)水平的是統(tǒng)計類查詢。比如查每個學生的總分和平均分、每門課的及格率、班級排名。這些都要用 JOIN 加 GROUP BY。-- 查詢每個學生的總分、平均分和選課門數(shù) SELECT s.student_id, s.name, COUNT(e.course_id) AS course_count, SUM(e.score) AS total_score, ROUND(AVG(e.score), 2) AS avg_score FROM student s LEFT JOIN enrollment e ON s.student_id e.student_id GROUP BY s.student_id, s.name ORDER BY avg_score DESC;這里用 LEFT JOIN 而不是 INNER JOIN是為了讓沒選課的學生也出現(xiàn)在結(jié)果里顯示為 0 門課。如果用 INNER JOIN沒選課的學生直接消失報表就不完整。AVG 會自動跳過 NULL所以未錄入的成績不影響平均分計算。ROUND 保留兩位小數(shù)避免出現(xiàn)一長串浮點數(shù)。再比如查每門課的及格率-- 每門課的選課人數(shù)、及格人數(shù)、及格率 SELECT c.course_name, COUNT(e.student_id) AS total, SUM(CASE WHEN e.score 60 THEN 1 ELSE 0 END) AS passed, ROUND(SUM(CASE WHEN e.score 60 THEN 1 ELSE 0 END) / COUNT(e.student_id) * 100, 1) AS pass_rate FROM course c JOIN enrollment e ON c.course_id e.course_id WHERE e.score IS NOT NULL GROUP BY c.course_id, c.course_name;CASE WHEN 是行轉(zhuǎn)列的常用手法把滿足條件的行記 1不滿足記 0再 SUM 就得到計數(shù)。WHERE 里過濾掉 NULL 成績避免未錄入的被算成不及格。4.3 視圖、存儲過程與觸發(fā)器高級對象是拉開分差的地方。視圖適合封裝復雜查詢存儲過程適合封裝業(yè)務邏輯觸發(fā)器適合做自動校驗或日志。三個至少各用一個。-- 視圖學生成績明細簡化后續(xù)查詢 CREATE VIEW v_student_score AS SELECT s.student_id, s.name AS student_name, c.course_name, c.credit, e.score FROM student s JOIN enrollment e ON s.student_id e.student_id JOIN course c ON e.course_id c.course_id; -- 存儲過程錄入或更新成績帶存在性檢查 DELIMITER // CREATE PROCEDURE upsert_score( IN p_student CHAR(10), IN p_course CHAR(8), IN p_score DECIMAL(5,2) ) BEGIN IF p_score 0 OR p_score 100 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 成績必須在0到100之間; END IF; INSERT INTO enrollment (student_id, course_id, score) VALUES (p_student, p_course, p_score) ON DUPLICATE KEY UPDATE score p_score; END // DELIMITER ; -- 觸發(fā)器成績更新時記錄日志 CREATE TABLE score_log ( log_id INT PRIMARY KEY AUTO_INCREMENT, student_id CHAR(10), course_id CHAR(8), old_score DECIMAL(5,2), new_score DECIMAL(5,2), changed_at DATETIME DEFAULT CURRENT_TIMESTAMP ); DELIMITER // CREATE TRIGGER trg_score_update AFTER UPDATE ON enrollment FOR EACH ROW BEGIN IF OLD.score NEW.score OR (OLD.score IS NULL AND NEW.score IS NOT NULL) THEN INSERT INTO score_log (student_id, course_id, old_score, new_score) VALUES (OLD.student_id, OLD.course_id, OLD.score, NEW.score); END IF; END // DELIMITER ;存儲過程里用 SIGNAL 拋出自定義錯誤比在應用層判斷更靠近數(shù)據(jù)。ON DUPLICATE KEY UPDATE 利用主鍵沖突實現(xiàn)“有則更新無則插入”一條語句搞定。觸發(fā)器里判斷 OLD 和 NEW 的差異注意 NULL 的比較要用 IS NULL直接在 NULL 參與時結(jié)果未知這是很多人寫觸發(fā)器時的血淚坑。注意DELIMITER 只在 MySQL 命令行和部分客戶端里生效Navicat 里執(zhí)行存儲過程時要在查詢窗口單獨運行或者用它的函數(shù)/過程編輯器否則會因為分號提前結(jié)束而報語法錯誤。5. 避坑與排查那些讓作業(yè)返工的細節(jié)5.1 外鍵報錯 1452插入順序或數(shù)據(jù)類型不匹配現(xiàn)象插入選課記錄時報Cannot add or update a child row: a foreign key constraint fails。原因通常是兩種被引用的學生或課程還沒插入或者外鍵列和被引用列的數(shù)據(jù)類型、字符集不一致。比如學生表 student_id 是 CHAR(10)選課表里寫成了 VARCHAR(10)雖然看起來像但嚴格模式下可能不匹配。解決先確認被引用數(shù)據(jù)存在再用SHOW CREATE TABLE對比兩邊的列定義字符集和長度必須完全一致。5.2 中文亂碼三層字符集沒對齊現(xiàn)象Navicat 里中文顯示成問號或亂碼。原因庫、表、連接三層字符集不一致。解決建庫時指定 utf8mb4建表繼承庫設置Navicat 連接屬性里把編碼設為 utf8mb4連接串加?characterEncodingutf8mb4。已經(jīng)建好的表可以用ALTER TABLE student CONVERT TO CHARACTER SET utf8mb4;補救但數(shù)據(jù)可能已經(jīng)損壞最好重建。5.3 聚合查詢結(jié)果不對JOIN 后行數(shù)膨脹現(xiàn)象查學生總分時發(fā)現(xiàn)總分比實際高很多。原因多表 JOIN 時如果有一對多關(guān)系沒處理好會產(chǎn)生笛卡爾積式的行膨脹。比如學生 JOIN 選課 JOIN 課程如果課程表通過教師再 JOIN 一次行數(shù)會翻倍。解決先確認每個 JOIN 的連接條件唯一必要時用子查詢先聚合再 JOIN或者用 DISTINCT 去重。統(tǒng)計類查詢建議先在子查詢里算好再關(guān)聯(lián)。5.4 存儲過程在 Navicat 里執(zhí)行失敗現(xiàn)象在查詢窗口粘貼帶 DELIMITER 的存儲過程代碼報You have an error in your SQL syntax。原因Navicat 的查詢窗口不認 DELIMITER 指令它按自己的規(guī)則解析。解決用 Navicat 左側(cè)樹里的“函數(shù)”或“過程”右鍵新建在編輯器里只寫 BEGIN 到 END 之間的內(nèi)容DELIMITER 由工具處理。或者改用 MySQL 命令行客戶端執(zhí)行完整腳本。5.5 觸發(fā)器導致更新失敗或死循環(huán)現(xiàn)象更新成績時報錯或者更新一條記錄卻觸發(fā)了大量日志。原因觸發(fā)器里又去更新同一張表造成遞歸觸發(fā)或者觸發(fā)器邏輯里對 NULL 判斷有誤。解決MySQL 不允許觸發(fā)器直接更新自身表需要繞道用存儲過程NULL 比較一律用 IS NULL 和 IS NOT NULL不要用等號。寫完觸發(fā)器先用一條測試數(shù)據(jù)驗證再批量操作。6. 驗收與報告讓評分老師一眼看到你的設計取舍6.1 用一組固定查詢做回歸驗證交作業(yè)前我習慣準備一組固定查詢每次改完表結(jié)構(gòu)或數(shù)據(jù)都跑一遍確認結(jié)果符合預期。這組查詢覆蓋單表 CRUD、多表 JOIN、聚合統(tǒng)計、視圖查詢、存儲過程調(diào)用、觸發(fā)器日志檢查。跑通了說明系統(tǒng)是活的不是一堆死 SQL。-- 回歸驗證清單逐條執(zhí)行并核對結(jié)果 SELECT COUNT(*) FROM student; -- 學生總數(shù) SELECT * FROM v_student_score WHERE score IS NULL; -- 未錄入成績 CALL upsert_score(2021010103, MA101, 85.0); -- 錄入成績 SELECT * FROM score_log ORDER BY changed_at DESC; -- 檢查觸發(fā)器日志 SELECT c.course_name, ROUND(AVG(e.score),2) AS avg_score FROM course c JOIN enrollment e ON c.course_id e.course_id GROUP BY c.course_id; -- 課程平均分這組查詢同時也是報告里的“測試結(jié)果”章節(jié)素材直接截圖或貼結(jié)果表比空口說“系統(tǒng)運行正?！庇姓f服力。6.2 報告里必須寫清楚的三件事評分老師看報告的時間有限最想看到的是你的設計決策。第一E-R 圖到關(guān)系模式的轉(zhuǎn)換過程特別是多對多怎么拆的為什么這么拆。第二約束和索引的選擇理由比如為什么成績用 DECIMAL 不用 FLOAT為什么選課表用復合主鍵。第三高級對象的業(yè)務價值視圖簡化了什么查詢存儲過程保證了什么一致性觸發(fā)器記錄了哪些審計信息。這三點寫清楚報告的技術(shù)分基本就穩(wěn)了。6.3 一個容易被忽略的加分項慢 SQL 意識作業(yè)數(shù)據(jù)量小一般不會遇到性能問題但如果你在報告里主動提一句“選課表的 student_id 和 course_id 上有索引因為按學生查成績和按課程查名單是高頻操作”老師會認為你有工程意識。熱詞里“慢 SQL 優(yōu)化”不是白搜的哪怕只是加一個復合索引也值得在報告里說明理由。-- 為高頻查詢加復合索引覆蓋按學生查成績的場景 CREATE INDEX idx_enroll_student_course ON enrollment (student_id, course_id, score);這個索引讓“查某學生某門課成績”和“查某學生所有成績”都能走索引避免全表掃描。數(shù)據(jù)量大了以后這就是慢 SQL 和快 SQL 的分界線。我自己帶學生做這個作業(yè)最深的教訓是別在選題上追求新奇把學生成績管理系統(tǒng)做扎實比做一個半成品的花哨系統(tǒng)得分高得多。表結(jié)構(gòu)定稿前多花半天推敲后面能省兩天改 SQL 的時間。觸發(fā)器寫完一定用測試數(shù)據(jù)跑一遍別等到演示時才發(fā)現(xiàn)日志表是空的。希望幫到你。本文還有配套的精品資源點擊獲取