2025年數(shù)據(jù)庫(kù)SQLSERVER期末考試題及答案一、單項(xiàng)選擇題（每題2分，共20分）1.以下關(guān)于SQLServer2022（假設(shè)2025年教學(xué)重點(diǎn)仍為該版本）數(shù)據(jù)庫(kù)文件的描述，錯(cuò)誤的是：A.主數(shù)據(jù)文件擴(kuò)展名.mdf，每個(gè)數(shù)據(jù)庫(kù)至少有一個(gè)B.次要數(shù)據(jù)文件擴(kuò)展名.ndf，用于存儲(chǔ)額外數(shù)據(jù)C.事務(wù)日志文件擴(kuò)展名.ldf，可存在多個(gè)D.所有數(shù)據(jù)文件必須存儲(chǔ)在同一物理磁盤(pán)答案：D2.某表包含字段：學(xué)生ID（主鍵，int）、姓名（nvarchar(20)）、入學(xué)時(shí)間（datetime）、班級(jí)ID（int），若需快速按班級(jí)ID+入學(xué)時(shí)間查詢(xún)學(xué)生，最優(yōu)索引策略是：A.在班級(jí)ID列創(chuàng)建非聚集索引B.在（班級(jí)ID,入學(xué)時(shí)間）上創(chuàng)建聚集索引C.在（入學(xué)時(shí)間,班級(jí)ID）上創(chuàng)建非聚集索引D.在班級(jí)ID列創(chuàng)建哈希索引答案：B3.執(zhí)行以下事務(wù)時(shí)，若在第二步執(zhí)行前數(shù)據(jù)庫(kù)崩潰，恢復(fù)后事務(wù)狀態(tài)為：BEGINTRANSACTIONUPDATE賬戶(hù)SET余額=余額-100WHERE賬戶(hù)ID=1（步驟1）UPDATE賬戶(hù)SET余額=余額+100WHERE賬戶(hù)ID=2（步驟2）COMMITA.步驟1回滾，步驟2未執(zhí)行B.步驟1提交，步驟2未執(zhí)行C.兩個(gè)步驟均提交D.事務(wù)處于掛起狀態(tài)答案：A4.關(guān)于SQLServer備份，以下說(shuō)法正確的是：A.差異備份僅備份自上次完整備份后變化的數(shù)據(jù)頁(yè)B.事務(wù)日志備份必須在完整備份之后執(zhí)行C.文件組備份適用于超大型數(shù)據(jù)庫(kù)的部分恢復(fù)D.簡(jiǎn)單恢復(fù)模式支持完整備份和事務(wù)日志備份答案：C5.某表數(shù)據(jù)量為5000萬(wàn)行，經(jīng)常執(zhí)行"SELECTFROM表WHERE狀態(tài)=1ORDERBY更新時(shí)間DESC"，最優(yōu)優(yōu)化措施是：A.添加狀態(tài)列的非聚集索引B.添加（狀態(tài),更新時(shí)間）的降序非聚集索引C.將更新時(shí)間列設(shè)為聚集索引D.添加（更新時(shí)間,狀態(tài)）的非聚集索引答案：B6.以下約束類(lèi)型中，不能通過(guò)CHECK約束實(shí)現(xiàn)的是：A.性別只能是'男'或'女'B.年齡必須大于18C.出生日期不能晚于當(dāng)前日期D.學(xué)生ID必須唯一答案：D7.執(zhí)行"EXECsp_helpdb'教學(xué)管理'"后，不會(huì)顯示的信息是：A.數(shù)據(jù)庫(kù)文件物理路徑B.數(shù)據(jù)庫(kù)所有者C.數(shù)據(jù)庫(kù)當(dāng)前連接數(shù)D.數(shù)據(jù)文件初始大小答案：C8.關(guān)于窗口函數(shù)，以下語(yǔ)法正確的是：A.SELECT學(xué)號(hào),成績(jī),RANK()OVER(ORDERBY成績(jī)DESC)排名FROM成績(jī)表B.SELECT學(xué)號(hào),成績(jī),SUM(成績(jī))OVER(PARTITIONBY班級(jí)ORDERBY學(xué)號(hào))累計(jì)FROM成績(jī)表C.SELECT學(xué)號(hào),成績(jī),TOP3成績(jī)OVER()最高成績(jī)FROM成績(jī)表D.SELECT學(xué)號(hào),成績(jī),ROW_NUMBER()OVER(GROUPBY班級(jí))行號(hào)FROM成績(jī)表答案：A9.以下關(guān)于索引視圖的描述，錯(cuò)誤的是：A.必須包含聚集索引B.查詢(xún)時(shí)自動(dòng)使用，無(wú)需顯式調(diào)用C.基礎(chǔ)表數(shù)據(jù)變更時(shí)自動(dòng)更新D.適用于頻繁更新的業(yè)務(wù)場(chǎng)景答案：D10.若需限制用戶(hù)只能查詢(xún)"學(xué)生表"的姓名和年齡字段，且不能查看其他列，最優(yōu)實(shí)現(xiàn)方式是：A.授予SELECT權(quán)限時(shí)指定列B.創(chuàng)建視圖僅包含目標(biāo)列并授予視圖查詢(xún)權(quán)限C.使用行級(jí)安全性策略D.為用戶(hù)設(shè)置數(shù)據(jù)庫(kù)角色答案：B二、填空題（每空1分，共15分）1.SQLServer數(shù)據(jù)庫(kù)的三種主要文件類(lèi)型是主數(shù)據(jù)文件、次要數(shù)據(jù)文件和（事務(wù)日志文件）。2.存儲(chǔ)過(guò)程參數(shù)中，使用（OUTPUT）關(guān)鍵字聲明的參數(shù)可以將值返回給調(diào)用方。3.事務(wù)的四大特性ACID分別是原子性、一致性、（隔離性）和持久性。4.若要查看當(dāng)前數(shù)據(jù)庫(kù)所有用戶(hù)表的DDL腳本，可使用（sp_helptext）系統(tǒng)存儲(chǔ)過(guò)程配合查詢(xún)，或通過(guò)（sys.tables）系統(tǒng)視圖結(jié)合（sys.sql_modules）視圖查詢(xún)。5.索引分為聚集索引和非聚集索引，其中（聚集）索引決定了表中數(shù)據(jù)的物理存儲(chǔ)順序。6.數(shù)據(jù)庫(kù)恢復(fù)模式包括簡(jiǎn)單恢復(fù)模式、完整恢復(fù)模式和（大容量日志恢復(fù)）模式。7.執(zhí)行"DBCCCHECKDB"命令的主要作用是（檢查數(shù)據(jù)庫(kù)完整性）。8.窗口函數(shù)中，用于返回連續(xù)排名（無(wú)重復(fù)值）的函數(shù)是（ROW_NUMBER()），返回有間隔排名的是（RANK()），返回?zé)o間隔排名的是（DENSE_RANK()）。9.若要將數(shù)據(jù)庫(kù)"舊系統(tǒng)"的結(jié)構(gòu)和數(shù)據(jù)復(fù)制到新服務(wù)器，且不影響原數(shù)據(jù)庫(kù)，可使用（分離/附加）或（備份/還原）操作。10.觸發(fā)器分為DML觸發(fā)器和（DDL）觸發(fā)器，其中AFTER觸發(fā)器在（數(shù)據(jù)操作完成后）執(zhí)行，INSTEADOF觸發(fā)器在（數(shù)據(jù)操作執(zhí)行前）替代原操作。三、簡(jiǎn)答題（每題6分，共30分）1.簡(jiǎn)述聚集索引與非聚集索引的區(qū)別及適用場(chǎng)景。答案：聚集索引決定數(shù)據(jù)物理存儲(chǔ)順序，一個(gè)表只能有一個(gè)，適合經(jīng)常按范圍查詢(xún)（如日期范圍）或主鍵列。非聚集索引存儲(chǔ)索引鍵和行定位器（RID或聚集索引鍵），一個(gè)表可多個(gè)，適合經(jīng)常單獨(dú)查詢(xún)的列或多列查詢(xún)條件（組合索引）。聚集索引適合高查詢(xún)低更新場(chǎng)景，非聚集索引適合頻繁查詢(xún)不同列的場(chǎng)景。2.說(shuō)明事務(wù)隔離級(jí)別"可重復(fù)讀"與"可串行化"的區(qū)別。答案：可重復(fù)讀（RepeatableRead）保證在事務(wù)期間多次讀取同一數(shù)據(jù)結(jié)果一致，防止臟讀和不可重復(fù)讀，但可能出現(xiàn)幻讀（新插入符合條件的行）?？纱谢⊿erializable）通過(guò)范圍鎖防止幻讀，事務(wù)執(zhí)行時(shí)如同串行執(zhí)行，隔離級(jí)別最高，但并發(fā)性最低，適合對(duì)數(shù)據(jù)一致性要求極高的場(chǎng)景（如金融交易）。3.簡(jiǎn)述視圖的作用及使用注意事項(xiàng)。答案：作用：簡(jiǎn)化復(fù)雜查詢(xún)、提供數(shù)據(jù)安全（限制列/行訪問(wèn)）、隱藏表結(jié)構(gòu)變化（封裝底層表變更）、實(shí)現(xiàn)邏輯數(shù)據(jù)分組。注意事項(xiàng)：視圖本身不存儲(chǔ)數(shù)據(jù)（索引視圖除外），頻繁通過(guò)視圖更新需確保視圖滿足可更新條件（如不包含聚合函數(shù)、DISTINCT等），大量視圖可能影響查詢(xún)性能，需合理設(shè)計(jì)。4.列舉三種常見(jiàn)的索引優(yōu)化策略，并說(shuō)明適用場(chǎng)景。答案：（1）組合索引：將常用查詢(xún)條件列按順序創(chuàng)建索引（左前綴匹配），適用于多列查詢(xún)（如WHEREa=1ANDb=2）。（2）覆蓋索引：索引包含查詢(xún)所需所有列，避免回表，適用于SELECT列較少且固定的查詢(xún)。（3）索引包含列：在非聚集索引中添加INCLUDE列，存儲(chǔ)額外列數(shù)據(jù)，適用于查詢(xún)需要非索引鍵列但不想創(chuàng)建寬組合索引的場(chǎng)景。（4）刪除冗余索引：移除被其他索引完全覆蓋的索引，減少維護(hù)開(kāi)銷(xiāo)（如已有(a,b)索引，刪除單獨(dú)的a索引）。5.說(shuō)明完整備份+差異備份+事務(wù)日志備份的恢復(fù)策略步驟。答案：（1）恢復(fù)最近一次完整備份（WITHNORECOVERY）；（2）恢復(fù)最新的差異備份（WITHNORECOVERY）；（3）按順序恢復(fù)差異備份之后的所有事務(wù)日志備份（每個(gè)日志備份均使用WITHNORECOVERY，最后一個(gè)日志備份使用WITHRECOVERY）；（4）數(shù)據(jù)庫(kù)進(jìn)入可用狀態(tài)。該策略支持恢復(fù)到故障前的任意時(shí)間點(diǎn)，適用于需要高數(shù)據(jù)保護(hù)的生產(chǎn)環(huán)境。四、操作題（共25分）【背景】某培訓(xùn)學(xué)校需設(shè)計(jì)"學(xué)員管理系統(tǒng)"數(shù)據(jù)庫(kù)，包含以下業(yè)務(wù)需求：-學(xué)員表（學(xué)員ID，姓名，性別，出生日期，聯(lián)系電話，報(bào)名時(shí)間）-課程表（課程ID，課程名稱(chēng)，課程類(lèi)型（枚舉：考證/技能/興趣），課時(shí)，單價(jià)）-報(bào)名記錄表（記錄ID，學(xué)員ID，課程ID，報(bào)名日期，優(yōu)惠金額，應(yīng)繳金額（=課時(shí)單價(jià)-優(yōu)惠金額））要求：根據(jù)以上需求完成以下操作。1.（5分）創(chuàng)建數(shù)據(jù)庫(kù)"TrainingDB"，主數(shù)據(jù)文件初始大小100MB，最大無(wú)限制，增長(zhǎng)步長(zhǎng)10%；事務(wù)日志文件初始大小50MB，最大200MB，增長(zhǎng)步長(zhǎng)5MB。答案：CREATEDATABASETrainingDBONPRIMARY(NAME=TrainingDB_Data,FILENAME='D:\Data\TrainingDB.mdf',SIZE=100MB,MAXSIZE=UNLIMITED,FILEGROWTH=10%)LOGON(NAME=TrainingDB_Log,FILENAME='D:\Log\TrainingDB.ldf',SIZE=50MB,MAXSIZE=200MB,FILEGROWTH=5MB);2.（6分）創(chuàng)建學(xué)員表，要求：-學(xué)員ID為主鍵，自增（起始值1001，增量1）-性別只能是'男'或'女'（CHECK約束）-聯(lián)系電話格式為11位數(shù)字（CHECK約束）-報(bào)名時(shí)間默認(rèn)值為當(dāng)前系統(tǒng)時(shí)間答案：CREATETABLE學(xué)員表(學(xué)員IDINTIDENTITY(1001,1)PRIMARYKEY,姓名NVARCHAR(20)NOTNULL,性別NCHAR(1)NOTNULLCHECK(性別IN('男','女')),出生日期DATETIMENOTNULL,聯(lián)系電話CHAR(11)NOTNULLCHECK(聯(lián)系電話LIKE'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),報(bào)名時(shí)間DATETIMEDEFAULTGETDATE()NOTNULL);3.（6分）編寫(xiě)SQL語(yǔ)句，查詢(xún)2023年1月1日后報(bào)名，且報(bào)名了"考證"類(lèi)型課程的學(xué)員姓名、課程名稱(chēng)、應(yīng)繳金額（要求使用多表連接，結(jié)果按應(yīng)繳金額降序排列）。答案：SELECTa.姓名,c.課程名稱(chēng),b.應(yīng)繳金額FROM學(xué)員表aINNERJOIN報(bào)名記錄表bONa.學(xué)員ID=b.學(xué)員IDINNERJOIN課程表cONb.課程ID=c.課程IDWHEREa.報(bào)名時(shí)間>='2023-01-01'ANDc.課程類(lèi)型='考證'ORDERBYb.應(yīng)繳金額DESC;4.（4分）為課程表的"課程類(lèi)型"列創(chuàng)建非聚集索引，要求索引名稱(chēng)為IX_課程_類(lèi)型。答案：CREATENONCLUSTEREDINDEXIX_課程_類(lèi)型ON課程表(課程類(lèi)型);5.（4分）創(chuàng)建存儲(chǔ)過(guò)程proc_GetStudentByAge，輸入?yún)?shù)為最小年齡和最大年齡，輸出符合條件的學(xué)員數(shù)量。答案：CREATEPROCEDUREproc_GetStudentByAge@MinAgeINT,@MaxAgeINT,@CountINTOUTPUTASBEGINSELECT@Count=COUNT()FROM學(xué)員表WHEREDATEDIFF(YEAR,出生日期,GETDATE())BETWEEN@MinAgeAND@MaxAge;END;五、綜合題（10分）【項(xiàng)目背景】某公司需要對(duì)"銷(xiāo)售管理系統(tǒng)"數(shù)據(jù)庫(kù)進(jìn)行性能優(yōu)化，當(dāng)前存在以下問(wèn)題：-訂單表（1.2億行）查詢(xún)"最近30天未付款訂單"耗時(shí)12秒（查詢(xún)條件：付款狀態(tài)=0AND下單時(shí)間>GETDATE()-30）-員工表（5000行）經(jīng)常執(zhí)行"SELECT姓名,部門(mén)FROM員工表WHERE員工ID=XXX"，響應(yīng)時(shí)間不穩(wěn)定-數(shù)據(jù)庫(kù)每天凌晨2點(diǎn)進(jìn)行完整備份，日志文件增長(zhǎng)過(guò)快（日均增長(zhǎng)50GB）請(qǐng)結(jié)合SQLServer知識(shí)，提出具體優(yōu)化方案（需說(shuō)明原因及操作步驟）。答案：1.訂單表優(yōu)化方案：（1）分析：查詢(xún)條件涉及"付款狀態(tài)"和"下單時(shí)間"，數(shù)據(jù)量極大，需索引支持。（2）優(yōu)化措施：創(chuàng)建組合非聚集索引IX_訂單_狀態(tài)_時(shí)間，包含列（付款狀態(tài),下單時(shí)間），并INCLUDE必要的返回列（如訂單ID、客戶(hù)ID等）。（3）原因：組合索引覆蓋查詢(xún)條件，避免全表掃描；INCLUDE列減少回表操作。（4）操作：CREATENONCLUSTEREDINDEXIX_訂單_狀態(tài)_時(shí)間ON訂單表(付款狀態(tài),下單時(shí)間DESC)INCLUDE(訂單ID,客戶(hù)ID);--根據(jù)實(shí)際查詢(xún)返回列調(diào)整2.員工表優(yōu)化方案：（1）分析：?jiǎn)T工表數(shù)據(jù)量小但查詢(xún)響應(yīng)不穩(wěn)定，可能是缺少索引或統(tǒng)計(jì)信息過(guò)時(shí)。（2）優(yōu)化措施：①檢查員工ID是否為主鍵或已有索引，若沒(méi)有則創(chuàng)建聚集索引（因員工ID是唯一標(biāo)識(shí)，聚集索引可提升單記錄查詢(xún)性能）；②更新統(tǒng)計(jì)信息：執(zhí)行UPDATESTATISTICS員工表;（3）原因：聚集索引按員工ID物理排序，單記錄查詢(xún)只需一次索引查找；更新統(tǒng)計(jì)信息幫助查詢(xún)優(yōu)化器提供更優(yōu)執(zhí)行計(jì)劃。（4）操作（假設(shè)無(wú)主鍵）：ALTERTABLE員工表ADDCONSTRAINTPK_員工IDPRIMARYKEYCLUSTERED(員工ID);UPDATESTATISTICS員工表;3.日志文件增長(zhǎng)優(yōu)化方案：（1）分析：完整備份間隔過(guò)長(zhǎng)（24小時(shí)），導(dǎo)致事務(wù)日志無(wú)法截?cái)啵ㄍ暾謴?fù)模式下需日志備份后才能重用空間）。（2）優(yōu)化措施：①修改備份策略：每天凌晨2點(diǎn)完整備份+每2小時(shí)事務(wù)日志備份；②檢查是否有長(zhǎng)事務(wù)未提交，終止不必要的長(zhǎng)事務(wù)；③若業(yè)務(wù)允許，考慮使用簡(jiǎn)單恢復(fù)模式（但會(huì)丟失日志備份點(diǎn)到故障點(diǎn)的數(shù)據(jù)）。（3）原因：增加日志備份頻率可及時(shí)截?cái)嗳罩?，減