簡介數(shù)據(jù)庫課后實(shí)驗(yàn)崔巍編著資料包面向高校數(shù)據(jù)庫課程初學(xué)者用于配合教材完成課堂知識的實(shí)踐鞏固。壓縮包共5個(gè)文件全部為SQL腳本整體大小僅5KB內(nèi)容輕量、可直接導(dǎo)入數(shù)據(jù)庫運(yùn)行。腳本分別對應(yīng)建表、數(shù)據(jù)插入、更新刪除、單表查詢與多表關(guān)聯(lián)查詢等典型實(shí)驗(yàn)環(huán)節(jié)能夠幫助讀者系統(tǒng)訓(xùn)練結(jié)構(gòu)化查詢語言的基礎(chǔ)寫法與常見子句搭配。資源延續(xù)教材中關(guān)于關(guān)系數(shù)據(jù)庫設(shè)計(jì)范式、事務(wù)處理等知識點(diǎn)的講解通過具體語句展示如何將第一范式、第三范式以及原子性、一致性等理論落實(shí)到實(shí)驗(yàn)操作中。由于文件數(shù)目少、類型統(tǒng)一讀者可快速定位代碼片段對照課本習(xí)題查漏補(bǔ)缺目前已有220人學(xué)習(xí)下載尤其適合正在選修數(shù)據(jù)庫課程、需要參考實(shí)驗(yàn)答案或練習(xí)樣例的學(xué)生。1. 數(shù)據(jù)庫課后實(shí)驗(yàn)從抄答案到自己出答案的關(guān)鍵一步如果你正被數(shù)據(jù)庫課后實(shí)驗(yàn)拖著走大概率遇到過這種場景教材翻完了打開數(shù)據(jù)庫軟件卻不知道第一條語句從哪寫起抱著網(wǎng)上找份答案抄一抄的想法結(jié)果貼進(jìn)自己庫里全是語法報(bào)錯(cuò)。這套課后實(shí)驗(yàn)資源要解決的不是替你把代碼準(zhǔn)備好而是告訴你實(shí)驗(yàn)該怎么做、每道題做到什么程度算過關(guān)、實(shí)驗(yàn)報(bào)告怎么排版才不丟分。適合三類人正在修數(shù)據(jù)庫課要交實(shí)驗(yàn)報(bào)告的學(xué)生、準(zhǔn)備復(fù)試機(jī)試但想快速找回 SQL 手感的人、剛?cè)肼毿枰a(bǔ)數(shù)據(jù)庫基本功的前端和測試?？赐赀@篇你能照著把建庫、查詢、事務(wù)、權(quán)限四類實(shí)驗(yàn)完整跑通。2. 實(shí)驗(yàn)環(huán)境與報(bào)告模板裝庫建庫三步走別急著敲代碼拿到實(shí)驗(yàn)題目后最忌諱的事就是打開編輯器直接敲 CREATE TABLE。先花半小時(shí)把環(huán)境和驗(yàn)收標(biāo)準(zhǔn)定下來后面每個(gè)實(shí)驗(yàn)至少省兩小時(shí)。2.1 選 SQL Server 還是 MySQL先看教材再決定數(shù)據(jù)庫課后實(shí)驗(yàn)的教材多數(shù)基于 SQL Server 編寫里面的演示語句用的是 T-SQL 方言比如 GETDATE()、TOP n、PRINT如果課件里用的是 MySQL就要把建表語句里的 AUTO_INCREMENT 和 ENGINEInnoDB 單獨(dú)記下來。判斷依據(jù)很簡單打開實(shí)驗(yàn)指導(dǎo)書的章節(jié)標(biāo)題出現(xiàn)“T-SQL”“SQL Server Management Studio”“SSMS”字樣就沿用它出現(xiàn)“Navicat”“workbench”就按 MySQL 走。環(huán)境裝好之后第一步不是建庫而是驗(yàn)證能不能連上服務(wù)。Windows 下最穩(wěn)的方式是先用 SQL Server Management Studio 登錄一次確認(rèn)實(shí)例名和認(rèn)證模式連接字符串記成下面這種后面所有命令行操作都用它# 本機(jī)默認(rèn)實(shí)例Windows 身份驗(yàn)證 sqlcmd -S localhost -E # 本機(jī)命名實(shí)例SQL Server 身份驗(yàn)證 sqlcmd -S localhost\\SQLEXPRESS -U sa -P 你的密碼參數(shù)說明-S 指定服務(wù)器實(shí)例名localhost 是本機(jī)默認(rèn)實(shí)例SQLEXPRESS 是安裝時(shí)可選命名實(shí)例-E 表示 Windows 身份驗(yàn)證-U 和 -P 是 SQL Server 登錄名和密碼。第一次連接如果報(bào)“用戶 sa 登錄失敗”說明安裝時(shí)選了 Windows 身份驗(yàn)證模式需要用管理員身份打開 SSMS在服務(wù)器屬性里把身份驗(yàn)證模式改成“混合模式”。驗(yàn)證連通后執(zhí)行一條最簡單的語句確認(rèn)當(dāng)前庫和版本SELECT VERSION AS 版本信息;這條語句常用來做連通性測試因?yàn)槟呐陆◣鞕?quán)限都沒有SELECT 系統(tǒng)變量也不受影響能看到版本號就說明服務(wù)、端口、認(rèn)證三層都通了后面建庫報(bào)錯(cuò)就不是連接問題而是權(quán)限或語法問題。2.2 讀懂實(shí)驗(yàn)指導(dǎo)的目錄結(jié)構(gòu)每個(gè)實(shí)驗(yàn)對應(yīng)哪個(gè)知識點(diǎn)這套課后實(shí)驗(yàn)的資源目錄通常按教材章節(jié)排布常見結(jié)構(gòu)是這樣的實(shí)驗(yàn)序號實(shí)驗(yàn)內(nèi)容對應(yīng)章節(jié)驗(yàn)收標(biāo)準(zhǔn)實(shí)驗(yàn)一創(chuàng)建數(shù)據(jù)庫與基本表第 3 章 關(guān)系數(shù)據(jù)庫庫、表、約束齊全能查出表結(jié)構(gòu)實(shí)驗(yàn)二數(shù)據(jù)更新增刪改第 4 章 SQL 數(shù)據(jù)操作影響行數(shù)與預(yù)期一致實(shí)驗(yàn)三單表與多表查詢第 5 章 查詢能解釋每條 SQL 的查詢意圖實(shí)驗(yàn)四視圖與索引第 6 章 視圖與索引查詢走索引視圖可繼續(xù)查詢實(shí)驗(yàn)五事務(wù)與存儲過程第 7 章 事務(wù)能演示回滾前后數(shù)據(jù)變化實(shí)驗(yàn)六用戶與權(quán)限管理第 8 章 數(shù)據(jù)庫安全授予與撤銷權(quán)限生效表格里的驗(yàn)收標(biāo)準(zhǔn)是我反復(fù)對比后補(bǔ)出來的因?yàn)楹芏鄬?shí)驗(yàn)指導(dǎo)只寫了“運(yùn)行并觀察結(jié)果”沒有寫“做到什么程度算完成”。比如實(shí)驗(yàn)一光建表成功還不夠還要能看到主外鍵約束在錯(cuò)誤插入時(shí)攔截實(shí)驗(yàn)三能跑出結(jié)果只算一半還要能說清楚為什么用 INNER JOIN 而不是 LEFT JOIN。讀目錄時(shí)重點(diǎn)看兩處實(shí)驗(yàn)指導(dǎo)的“實(shí)驗(yàn)?zāi)康摹倍瓮ǔ懨鳌罢莆铡薄袄斫狻薄傲私狻比齻€(gè)層級帶“掌握”的知識點(diǎn)就是必考必交的內(nèi)容可以在報(bào)告里對應(yīng)寫出你的理解“了解”層級的可以不寫進(jìn)報(bào)告但答辯時(shí)老師可能追問。2.3 實(shí)驗(yàn)報(bào)告的四段式結(jié)構(gòu)代碼、截圖、問題、體會每次實(shí)驗(yàn)報(bào)告都遵循同樣的四段結(jié)構(gòu)這樣老師批起來不費(fèi)勁你的分?jǐn)?shù)也穩(wěn)定。第一段寫實(shí)驗(yàn)?zāi)康闹苯映笇?dǎo)書的原句控制在三行以內(nèi)第二段寫關(guān)鍵代碼和結(jié)果截圖這是唯一值得花時(shí)間的地方代碼一定要帶行號注釋截圖一定要有“影響行數(shù)”或者結(jié)果集第三段寫踩坑記錄哪怕一個(gè)標(biāo)點(diǎn)錯(cuò)誤都寫進(jìn)去第四段寫小結(jié)三句話實(shí)現(xiàn)了什么、沒用上什么、下次怎么做。這套模板不是形式主義。數(shù)據(jù)庫實(shí)驗(yàn)扣分最常見的原因是“有結(jié)果沒過程”老師從截圖看不出你這幾步是自己敲的還是復(fù)制的帶上注釋的代碼塊、帶上報(bào)錯(cuò)信息的排查記錄才是區(qū)分度所在。3. 四類核心實(shí)驗(yàn)建表、增刪改、查詢、視圖索引一次打通這四章是連續(xù)實(shí)驗(yàn)建庫建表、數(shù)據(jù)更新、數(shù)據(jù)查詢、視圖索引一次課一個(gè)題目但底層邏輯是串起來的。這里以“學(xué)生-課程-選課”三張表為例把每類實(shí)驗(yàn)的標(biāo)準(zhǔn)答案和變體都寫出來。3.1 實(shí)驗(yàn)一建庫建表主外鍵和約束一個(gè)都不能少建庫建表是后面所有實(shí)驗(yàn)的地基教材里的寫法通常是這樣CREATE DATABASE StudentDB; GO USE StudentDB; GO CREATE TABLE Student ( Sno CHAR(9) PRIMARY KEY, Sname VARCHAR(20) NOT NULL, Ssex CHAR(2) DEFAULT 男, Sage SMALLINT CHECK (Sage BETWEEN 15 AND 30), Sdept VARCHAR(20) ); CREATE TABLE Course ( Cno CHAR(4) PRIMARY KEY, Cname VARCHAR(40) NOT NULL, Cpno CHAR(4), -- 先修課 Ccredit SMALLINT CHECK (Ccredit 0) ); CREATE TABLE SC ( Sno CHAR(9), Cno CHAR(4), Grade DECIMAL(4,1), PRIMARY KEY (Sno, Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno) );邏輯說明Student 表用學(xué)號做主鍵Sage 字段加 CHECK 約束限制年齡范圍Ssex 字段設(shè)默認(rèn)值保證非法數(shù)據(jù)進(jìn)不來Course 表的 Cpno 是自引用外鍵指向自己的主鍵表示先修課程關(guān)系SC 表是典型的關(guān)聯(lián)表主鍵是 (Sno, Cno) 聯(lián)合主鍵兩條外鍵保證選課記錄必須指向真實(shí)存在的學(xué)生和課程。參數(shù)說明CHAR(9) 定長字符串學(xué)號固定 9 位時(shí)不浪費(fèi)空間VARCHAR(20) 變長姓名長度不一更省空間DECIMAL(4,1) 表示共 4 位、小數(shù) 1 位正好存 0.0 到 999.9 的成績。這些類型選擇本身就是考點(diǎn)報(bào)告里寫一句“學(xué)號定長、姓名變長”能體現(xiàn)你懂類型設(shè)計(jì)。建完表后一定要執(zhí)行下面這條語句自查這也常是老師檢查你是否自己動手的手段USE StudentDB; SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN (Student,Course,SC) ORDER BY TABLE_NAME, ORDINAL_POSITION;這條查詢從系統(tǒng)信息架構(gòu)視圖讀取表結(jié)構(gòu)如果結(jié)果集完整顯示了三個(gè)表的全部字段說明建表成功如果某個(gè)表缺失多半是 CREATE TABLE 語句里有語法錯(cuò)誤被跳過了。注意系統(tǒng)視圖名必須大寫小寫在某些 SQL Server 排序規(guī)則下能跑通但在 MySQL 5.7 以下版本會因?yàn)榇笮懨舾袌?bào)錯(cuò)。3.2 實(shí)驗(yàn)二數(shù)據(jù)更新INSERT、UPDATE、DELETE 的邊界條件要寫清數(shù)據(jù)更新實(shí)驗(yàn)看起來簡單但得分點(diǎn)全在“影響行數(shù)”和“是否誤操作”上。先看標(biāo)準(zhǔn)插入INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept) VALUES (20230001, 張三, 男, 20, 計(jì)算機(jī)系); INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept) VALUES (20230002, 李四, 女, 19, 數(shù)學(xué)系); SELECT * FROM Student;每條 VALUES 插入一行數(shù)據(jù)字段列表的順序可以和表結(jié)構(gòu)不同只要值對應(yīng)上即可。注意如果把 Sage 插入成 40會觸發(fā) CHECK 約束報(bào)錯(cuò)如果把 Sname 省略會觸發(fā) NOT NULL 約束報(bào)錯(cuò)。這兩類報(bào)錯(cuò)是實(shí)驗(yàn)報(bào)告里最好的踩坑素材。批量插入經(jīng)常被忽略但實(shí)驗(yàn)指導(dǎo)里常有“向選課表插入 20 條記錄”的題手寫 20 條太累可以用查詢插入INSERT INTO SC (Sno, Cno, Grade) SELECT Sno, C001, 85 FROM Student WHERE Sdept 計(jì)算機(jī)系;這段語句從 Student 表查出計(jì)算機(jī)系所有學(xué)生統(tǒng)一插入選課表成績 85 分SELECT 子查詢的結(jié)果集直接作為 INSERT 的數(shù)據(jù)源適合批量造數(shù)。注意 SELECT 查出來的列數(shù)必須和 INSERT 指定的列數(shù)一致這里固定一列 C001 和一列 85能匹配兩列運(yùn)行后消息框會顯示“影響 N 行”這個(gè)數(shù)字要寫進(jìn)報(bào)告。更新語句的坑在于 WHERE 條件漏寫就是全表更新UPDATE SC SET Grade Grade * 1.05 WHERE Cno C001; -- 危險(xiǎn)寫法謹(jǐn)防演示翻車 -- UPDATE SC SET Grade Grade * 1.05;第一段真的想更新 C001 課程成績加 5%第二段注釋里是不帶 WHERE 的危險(xiǎn)寫法一旦執(zhí)行,全表成績都變。我一般會在實(shí)驗(yàn)指導(dǎo)的醒目位置把這個(gè)對照寫在報(bào)告里讓老師知道你明白“不帶 WHERE 就是全表操作”這個(gè)后果。刪除操作的邊界條件也類似。DELETE 是 DML 操作可回滾TRUNCATE 是 DDL 操作不可回滾除非事務(wù)里。教科書常把二者對比作為簡答題實(shí)驗(yàn)時(shí)只需記住一句口訣要回滾用 DELETE要清空且不記錄日志用 TRUNCATE。3.3 實(shí)驗(yàn)三單表查詢、多表連接和子查詢的寫法對比查詢實(shí)驗(yàn)是整套課后實(shí)驗(yàn)里分值最大的部分也是期末機(jī)試的重災(zāi)區(qū)。先看單表查詢的基礎(chǔ)套路SELECT Sno, Sname, Sage FROM Student WHERE Sdept 計(jì)算機(jī)系 ORDER BY Sage DESC;這條查詢加了三個(gè)要點(diǎn)WHERE 做行篩選ORDER BY 做排序DESC 表示降序。如果結(jié)果需要不重復(fù)要寫 SELECT DISTINCT如果只想取前幾條SQL Server 用 SELECT TOP 3MySQL 用 LIMIT 3方言差異就在這里。多表連接是三張表之間最常見的考點(diǎn)教材的標(biāo)準(zhǔn)答案是內(nèi)連接SELECT Student.Sno, Sname, Cname, Grade FROM Student INNER JOIN SC ON Student.Sno SC.Sno INNER JOIN Course ON SC.Cno Course.Cno WHERE Course.Cname 數(shù)據(jù)庫邏輯說明先拿 Student 和 SC 按學(xué)號連接得到每個(gè)學(xué)生選了什么課再和 Course 按課程號連接得到課程名WHERE 最后過濾只留數(shù)據(jù)庫這門課。執(zhí)行順序是先 FROM 和 JOIN 生成虛擬表再 WHERE 過濾再 SELECT 投影所以別名能用 WHERE 而聚合結(jié)果不行。很多同學(xué)分不清 INNER JOIN 和 LEFT JOIN 的區(qū)別實(shí)驗(yàn)里最好的驗(yàn)證方式是查“沒選課的學(xué)生”SELECT Student.Sno, Sname FROM Student LEFT JOIN SC ON Student.Sno SC.Sno WHERE SC.Sno IS NULL;這段查詢用 LEFT JOIN 保留左表所有學(xué)生再用 SC.Sno IS NULL 過濾出沒在選課表出現(xiàn)過的學(xué)生也就是沒選任何課的人。如果這里用了 INNER JOIN沒選課的人根本不會出現(xiàn)在連接結(jié)果里IS NULL 條件永遠(yuǎn)不成立這種語義差別就是機(jī)試?yán)锏乃兔}。子查詢的花樣更多最常見的是 IN 版本的不相關(guān)子查詢和 EXISTS 版本的相關(guān)子查詢-- 查詢選修了課程號為 C001 的學(xué)生姓名不相關(guān)子查詢 SELECT Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno C001); -- 查詢選修了課程號為 C001 的學(xué)生姓名相關(guān)子查詢 SELECT Sname FROM Student WHERE EXISTS ( SELECT 1 FROM SC WHERE SC.Sno Student.Sno AND SC.Cno C001 );兩種寫法結(jié)果一樣但對新手來說性能差別很大IN 子查詢先執(zhí)行子查詢生成結(jié)果集再和外層比較EXISTS 是逐行掃描外層表到內(nèi)層驗(yàn)證是否存在。數(shù)據(jù)量小時(shí)感受不到SC 表超過十萬行時(shí) EXISTS 通常更快前提是 SC 表有索引。實(shí)驗(yàn)報(bào)告里把這兩種寫法都跑一遍并比較執(zhí)行時(shí)間是對“查詢優(yōu)化”知識點(diǎn)最好的呼應(yīng)。3.4 實(shí)驗(yàn)四視圖是保存的查詢索引要建在刀刃上視圖實(shí)驗(yàn)的核心結(jié)論就一句話視圖不存數(shù)據(jù)只是一條保存起來的 SELECT。教材通常會要求基于單表或兩表建視圖CREATE VIEW V_CS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept 計(jì)算機(jī)系; GO SELECT * FROM V_CS_Student;視圖建好后可以像表一樣查詢但它的數(shù)據(jù)實(shí)時(shí)來自基表。如果后續(xù) UPDATE Student 把某個(gè)學(xué)生的 Sdept 改成數(shù)學(xué)系再查詢視圖時(shí)這條記錄自動消失這是“視圖更新時(shí)基表聯(lián)動”的直接體現(xiàn)也是實(shí)驗(yàn)報(bào)告里寫“視圖是虛擬表”的證據(jù)。索引實(shí)驗(yàn)要從執(zhí)行計(jì)劃里看效果而不是只看有沒有建成功CREATE INDEX IX_SC_Grade ON SC(Grade); SET STATISTICS IO ON; SELECT * FROM SC WHERE Grade BETWEEN 80 AND 90; SET STATISTICS IO OFF;邏輯說明IX_SC_Grade 是在 Grade 列建的普通索引SET STATISTICS IO ON 打開 IO 統(tǒng)計(jì)執(zhí)行完查詢后消息面板會顯示“掃描計(jì)數(shù)”“邏輯讀取次數(shù)”。沒索引時(shí)是全表掃描邏輯讀取等于該表所在數(shù)據(jù)頁數(shù)量建索引后通常是索引查找邏輯讀取大幅下降。報(bào)告里把前后兩個(gè)數(shù)字放一起索引的價(jià)值就量化了。為什么說索引要建在刀刃上WHERE 和 JOIN 的列適合建索引而 SELECT 出來的列建索引往往浪費(fèi)一個(gè)表最多建個(gè)五六個(gè)索引寫操作頻繁的表建更多反而拖慢更新因?yàn)槊看卧鰟h改都要同步維護(hù)索引結(jié)構(gòu)。這是刪掉廢話后真正有信息量的選擇邏輯。4. 事務(wù)與權(quán)限實(shí)驗(yàn)教科書里一筆帶過期末卻愛考的 20 分很多同學(xué)把實(shí)驗(yàn)一到實(shí)驗(yàn)四做得漂漂亮亮卻在實(shí)驗(yàn)五、實(shí)驗(yàn)六翻車。原因很簡單事務(wù)和權(quán)限這兩章不寫 SQL 語句看不出效果必須在 SSMS 里手動開兩個(gè)查詢窗口一個(gè)模擬用戶 A一個(gè)模擬用戶 B來回切換才能演示。準(zhǔn)備實(shí)驗(yàn)前把下面幾個(gè)概念先盤清楚。4.1 事務(wù)的三個(gè)關(guān)鍵詞BEGIN TRAN、COMMIT、ROLLBACK事務(wù)實(shí)驗(yàn)最經(jīng)典的設(shè)計(jì)是模擬銀行轉(zhuǎn)賬從張三賬戶扣 100往李四賬戶加 100。教科書里通常只給你前半段我自己復(fù)現(xiàn)時(shí)會把“中途斷電”的場景補(bǔ)上這也是老師最愛追問的“如果第二條語句失敗怎么辦”USE StudentDB; BEGIN TRANSACTION; UPDATE Account SET Balance Balance - 100 WHERE AccountID A001; -- 模擬第二條語句失敗李四賬戶不存在 UPDATE Account SET Balance Balance 100 WHERE AccountID B999; IF ERROR 0 BEGIN ROLLBACK TRANSACTION; PRINT 轉(zhuǎn)賬失敗已回滾; END ELSE BEGIN COMMIT TRANSACTION; PRINT 轉(zhuǎn)賬成功已提交; END邏輯說明BEGIN TRANSACTION 開啟事務(wù)兩條 UPDATE 作為一個(gè)整體第二條更新一個(gè)不存在的賬戶影響行數(shù)為 0但 ERROR 不一定非零所以更穩(wěn)的判斷是用 ROWCOUNT 判斷影響行數(shù)。上面用 ERROR 的方式只是教材演示實(shí)際項(xiàng)目里要拿 ROWCOUNT 1 作為成功條件。ROLLBACK 之后第一條 UPDATE 的扣款也會撤銷。參數(shù)說明ERROR 是前一條 SQL 的錯(cuò)誤號0 表示成功ROWCOUNT 是前一條語句影響的行數(shù)。兩者都是會話級變量用完即失效所以要在關(guān)鍵語句后立即取值判斷。事務(wù)隔離級別是這張實(shí)驗(yàn)的另一個(gè)隱藏得分點(diǎn)。教材會給四個(gè)級別讀未提交、讀已提交、可重復(fù)讀、可串行化。我在實(shí)驗(yàn)報(bào)告里的演示腳本是這樣的-- 窗口 A開啟事務(wù)但不提交 USE StudentDB; BEGIN TRANSACTION; UPDATE Account SET Balance 500 WHERE AccountID A001; -- 此時(shí)不執(zhí)行 COMMIT保持事務(wù)打開 -- 窗口 B默認(rèn)隔離級別下查詢 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT Balance FROM Account WHERE AccountID A001;如果窗口 B 在 READ COMMITTED 下查詢會因?yàn)?A 的事務(wù)未提交而一直阻塞等待直到 A 提交或回滾才返回結(jié)果如果窗口 B 改成 READ UNCOMMITTED能立刻讀到 A 修改但未提交的 500也就是臟讀。這個(gè)阻塞和臟讀的對照比任何文字都直觀也是期末考試簡答題“解釋臟讀”的標(biāo)準(zhǔn)實(shí)驗(yàn)證據(jù)。4.2 權(quán)限控制GRANT、REVOKE、DENY 的優(yōu)先級要分清權(quán)限實(shí)驗(yàn)通常要求創(chuàng)建兩個(gè)用戶一個(gè)只讀用戶一個(gè)可更新用戶。先創(chuàng)建登錄名和數(shù)據(jù)庫用戶USE master; CREATE LOGIN U_Read WITH PASSWORD Read123; USE StudentDB; CREATE USER U_Read FOR LOGIN U_Read; GRANT SELECT ON Student TO U_Read; GRANT SELECT ON SC TO U_Read;這段操作分兩層CREATE LOGIN 在 master 庫創(chuàng)建服務(wù)器級登錄名CREATE USER 把登錄名映射為 StudentDB 的數(shù)據(jù)庫用戶GRANT SELECT 逐表授權(quán)。沒有映射的話登錄名能連上服務(wù)器但進(jìn)不了庫報(bào)“無法訪問數(shù)據(jù)庫 StudentDB”這是權(quán)限實(shí)驗(yàn)最典型的翻車點(diǎn)。撤銷權(quán)限和禁止權(quán)限的順序也容易踩坑。SQL Server 的權(quán)限判斷順序是 DENY REVOKE GRANT也就是說即使用戶同時(shí)擁有 GRANT 和 DENYDENY 生效。演示腳本可以這樣寫-- 先給權(quán)限 GRANT UPDATE ON SC TO U_Read; -- 再禁止 DENY UPDATE ON SC TO U_Read; -- 驗(yàn)證下面這條用 U_Read 登錄執(zhí)行會報(bào)錯(cuò) -- UPDATE SC SET Grade 60 WHERE Sno 20230001;邏輯說明GRANT 之后 U_Read 有 UPDATE 權(quán)限執(zhí)行 UPDATE 成功加 DENY 之后即使之前有 GRANTUPDATE 也被拒絕報(bào)權(quán)限不足。這個(gè)“DENY 優(yōu)先”的機(jī)制?；煸谶x擇題里考。做權(quán)限實(shí)驗(yàn)時(shí)有一個(gè)習(xí)慣值得養(yǎng)成每授予一個(gè)權(quán)限就開一個(gè)新的查詢窗口用對應(yīng)登錄名執(zhí)行一次驗(yàn)證語句。權(quán)限不是寫在語法上而是寫在“誰、對什么對象、能做什么操作”的三元組里只有實(shí)測能證明權(quán)限真的生效。4.3 存儲過程實(shí)驗(yàn)把事務(wù)和業(yè)務(wù)規(guī)則包在一個(gè)調(diào)用里后半個(gè)學(xué)期的實(shí)驗(yàn)通常會把存儲過程和觸發(fā)器加進(jìn)來。存儲過程不過是把一串 SQL 命名保存但它的得分點(diǎn)是“輸入?yún)?shù) 事務(wù) 錯(cuò)誤處理”三件套。下面這段腳本是我在教學(xué)項(xiàng)目里反復(fù)用的模板也能直接放進(jìn)實(shí)驗(yàn)報(bào)告CREATE PROCEDURE sp_Transfer FromAccount CHAR(10), ToAccount CHAR(10), Amount DECIMAL(10,2) AS BEGIN BEGIN TRY BEGIN TRANSACTION; UPDATE Account SET Balance Balance - Amount WHERE AccountID FromAccount; IF ROWCOUNT 0 THROW 50001, 轉(zhuǎn)出賬戶不存在, 1; UPDATE Account SET Balance Balance Amount WHERE AccountID ToAccount; IF ROWCOUNT 0 THROW 50002, 轉(zhuǎn)入賬戶不存在, 1; COMMIT TRANSACTION; END TRY BEGIN CATCH ROLLBACK TRANSACTION; THROW; END CATCH END GO EXEC sp_Transfer A001, B001, 100;參數(shù)說明三個(gè)輸入?yún)?shù)分別表示轉(zhuǎn)出賬戶、轉(zhuǎn)入賬戶、金額THROW 50001 是自定義錯(cuò)誤號50001 到 2147483647 之間可以自定義BEGIN TRY/CATCH 捕獲異常后強(qiáng)制回滾。執(zhí)行 EXEC 調(diào)用時(shí)如果轉(zhuǎn)入賬戶不存在會看到業(yè)務(wù)錯(cuò)誤“轉(zhuǎn)入賬戶不存在”且轉(zhuǎn)出賬戶的扣款被回滾可以查表驗(yàn)證。存儲過程實(shí)驗(yàn)有一個(gè)容易被忽略的細(xì)節(jié)設(shè)計(jì)參數(shù)時(shí)不能只想著“能跑通”要設(shè)計(jì)邊界值。比如金額傳 0 或負(fù)數(shù)上面的過程并不會攔截需要額外加 IF Amount 0 THROW把邊界判斷補(bǔ)上再提交報(bào)告的說服力完全不同。5. 實(shí)驗(yàn)避坑五個(gè)讓平時(shí)分縮水的經(jīng)典翻車現(xiàn)場數(shù)據(jù)庫實(shí)驗(yàn)的扣分點(diǎn)往往不在 SQL 語法而在于環(huán)境、字符集、提交狀態(tài)這些看起來跟代碼無關(guān)的細(xì)節(jié)。以下是踩過的五個(gè)坑每條都按現(xiàn)象、原因、解決寫清楚。5.1 現(xiàn)象中文顯示成問號或亂碼做數(shù)據(jù)更新實(shí)驗(yàn)時(shí)INSERT 中文姓名后 SELECT 查出來是“??”或“鏉庡洓”。原因是客戶端和數(shù)據(jù)庫的字符集不一致SQL Server 安裝時(shí)排序規(guī)則選了 Latin1_General而客戶端用 UTF-8 發(fā)送數(shù)據(jù)入庫時(shí)按錯(cuò)誤代碼頁解釋。解決方法是先查默認(rèn)排序規(guī)則再在庫級別強(qiáng)制使用中文排序規(guī)則-- 查看當(dāng)前排序規(guī)則 SELECT SERVERPROPERTY(Collation); -- 建庫時(shí)顯式指定 CREATE DATABASE StudentDB COLLATE Chinese_PRC_CI_AS;注意改庫排序規(guī)則要在建庫時(shí)一次性搞定已經(jīng)建好的庫改排序規(guī)則容易導(dǎo)致索引失效如果實(shí)驗(yàn)指導(dǎo)里沒提字符集建庫語句里加上 COLLATE Chinese_PRC_CI_AS 是保險(xiǎn)做法。5.2 現(xiàn)象刪除父表行時(shí)提示外鍵沖突刪不掉實(shí)驗(yàn)二要求刪除某個(gè)學(xué)生記錄DELETE FROM Student WHERE Sno20230001 報(bào)錯(cuò)“與外鍵約束沖突”。原因是 SC 表里還存著這個(gè)學(xué)生的選課記錄外鍵約束不允許刪除被引用的父行。正確順序是先在子表刪除選課記錄再刪學(xué)生DELETE FROM SC WHERE Sno 20230001; DELETE FROM Student WHERE Sno 20230001;如果實(shí)驗(yàn)指導(dǎo)明確寫了要演示“受外鍵約束不能刪除”那這個(gè)報(bào)錯(cuò)本身就是得分點(diǎn)把報(bào)錯(cuò)截圖放進(jìn)報(bào)告里并在旁邊解釋原因即可如果沒寫就按上面的順序操作避免白白扣印象分。5.3 現(xiàn)象視圖創(chuàng)建成功但 SELECT 視圖報(bào)“對象名無效”在 SSMS 里 CREATE VIEW 提示成功關(guān)閉查詢窗口后再次查詢 V_CS_Student 卻報(bào)對象名無效。原因是視圖建在了別的數(shù)據(jù)庫下最常見的是 USE StudentDB 沒有執(zhí)行CREATE VIEW 默認(rèn)建在 master 庫關(guān)閉窗口后再連默認(rèn)庫還是 master自然找不到。解決方法是創(chuàng)建視圖前檢查當(dāng)前庫或者把庫名寫進(jìn)查詢USE StudentDB; GO SELECT * FROM dbo.V_CS_Student;dbo. 前綴的作用是限定架構(gòu)避免在非 dbo 架構(gòu)下查詢時(shí)找不到對象加了 dbo. 前綴后即便當(dāng)前庫不對也會立即報(bào)錯(cuò)而不是“不報(bào)錯(cuò)但查不到”。5.4 現(xiàn)象事務(wù)沒提交關(guān)閉窗口后數(shù)據(jù)“消失”更新操作執(zhí)行完消息面板顯示“影響 1 行”但重新打開一個(gè)查詢窗口查詢卻發(fā)現(xiàn)數(shù)據(jù)沒變。原因是上一個(gè)窗口執(zhí)行了 BEGIN TRANSACTION 之后忘記 COMMIT關(guān)閉窗口時(shí)數(shù)據(jù)庫自動回滾了未提交事務(wù)。解決方法是檢查每個(gè) BEGIN 是否有對應(yīng)的 COMMIT 或 ROLLBACK最好的習(xí)慣是寫完事務(wù)代碼立即把 COMMIT 寫上再補(bǔ)中間的語句BEGIN TRANSACTION; UPDATE Account SET Balance 100 WHERE AccountID A001; COMMIT TRANSACTION;我一般會在事務(wù)代碼上方加一行注釋“寫完就提交絕不讓事務(wù)掛到窗口關(guān)閉”這是避免假數(shù)據(jù)丟失的笨但有效的方法。5.5 現(xiàn)象權(quán)限授予后另一登錄名仍無法訪問數(shù)據(jù)庫GRANT SELECT 成功執(zhí)行但用另一個(gè)登錄名連接時(shí)仍報(bào)“無法訪問數(shù)據(jù)庫 StudentDB”。原因是只創(chuàng)建了登錄名沒創(chuàng)建數(shù)據(jù)庫用戶映射GRANT 是授予數(shù)據(jù)庫用戶的而登錄名連進(jìn)庫之前必須先有 USER 映射。檢查腳本是否包含兩條配套語句CREATE USER U_Read FOR LOGIN U_Read; GRANT SELECT ON Student TO U_Read;沒有第一條第二條就是空操作。這屬于數(shù)據(jù)庫安全模型里“登錄名服務(wù)器級和用戶名數(shù)據(jù)庫級”分離的設(shè)計(jì)報(bào)告里把“先映射后授權(quán)”這個(gè)順序?qū)懬宄鼙荛_一半權(quán)限類扣分。6. 把六個(gè)實(shí)驗(yàn)串成一個(gè)驗(yàn)收腳本一個(gè)能自測的收尾技巧最后一個(gè)建議不是再多寫一道題而是把所有實(shí)驗(yàn)?zāi)_本合并成一個(gè)能從零跑通的驗(yàn)收腳本。我用一個(gè)起名 DBLab_CheckAll 的文件把建庫、建表、插入、查詢、視圖、索引、事務(wù)、權(quán)限全部按順序放進(jìn)去每段后面加 PRINT 輸出階段標(biāo)記。這樣每次實(shí)驗(yàn)前跑一遍就知道哪些環(huán)節(jié)被誤刪改過。驗(yàn)收腳本的關(guān)鍵是按“依賴順序”排列建庫先于建表建表先于插入插入先于查詢。任意一步失敗后面的腳本會連鎖報(bào)錯(cuò)但 PRINT 標(biāo)記會把失敗點(diǎn)定位到具體階段。比如 PRINT STEP2 建表完成 之后如果報(bào)錯(cuò)一定出在建表階段。我個(gè)人的習(xí)慣是驗(yàn)收腳本里故意保留一個(gè)“可注釋掉的危險(xiǎn)語句”區(qū)域比如不帶 WHERE 的 UPDATE、不帶 COMMIT 的事務(wù)跑通后把它注釋掉在下一次實(shí)驗(yàn)前再恢復(fù)用來測試自己是不是真的理解每條語句的作用。這個(gè)技巧來自一次實(shí)際教訓(xùn)某次我連續(xù)做了三次實(shí)驗(yàn)最后一次改動了一個(gè)表的字段類型結(jié)果前面實(shí)驗(yàn)的視圖和存儲過程全部失效但因?yàn)闆]有統(tǒng)一腳本直到要交報(bào)告時(shí)才一條一條排查白耗了一晚上。從那以后每次實(shí)驗(yàn)結(jié)束前我都強(qiáng)制走一遍 DBLab_CheckAll確認(rèn)前面所有章節(jié)的實(shí)驗(yàn)還能正常工作。希望幫到你。本文還有配套的精品資源點(diǎn)擊獲取