2025數(shù)據(jù)庫(kù)系統(tǒng)工程師高級(jí)SQL編程技術(shù)試卷及答案一、單項(xiàng)選擇題（共10題，每題2分，共20分）1.以下關(guān)于窗口函數(shù)（WindowFunction）的描述中，錯(cuò)誤的是()A.窗口函數(shù)通過(guò)OVER子句定義分析窗口B.窗口函數(shù)可以使用PARTITIONBY實(shí)現(xiàn)分組計(jì)算C.窗口函數(shù)的結(jié)果會(huì)為每行生成一個(gè)計(jì)算值D.窗口函數(shù)必須與GROUPBY子句配合使用答案：D解析：窗口函數(shù)通過(guò)OVER子句定義窗口范圍，無(wú)需GROUPBY子句；GROUPBY會(huì)合并行，而窗口函數(shù)保留原始行。2.在PostgreSQL中，以下哪條語(yǔ)句可以正確創(chuàng)建一個(gè)物化視圖（MaterializedView）？()A.CREATEMATERIALIZEDVIEWmv_salesASSELECT*FROMsales;B.CREATEVIEWMATERIALIZEDmv_salesASSELECT*FROMsales;C.CREATEMATERIALIZEDmv_salesVIEWASSELECT*FROMsales;D.CREATEMVIEWmv_salesASSELECT*FROMsales;答案：A解析：PostgreSQL中物化視圖的創(chuàng)建語(yǔ)法為CREATEMATERIALIZEDVIEW名稱AS查詢，其他選項(xiàng)語(yǔ)法錯(cuò)誤。3.關(guān)于CTE（公共表表達(dá)式）的描述，正確的是()A.普通CTE（WITH子句）僅能在單次查詢中引用B.遞歸CTE的錨點(diǎn)成員必須放在遞歸成員之后C.CTE會(huì)永久存儲(chǔ)查詢結(jié)果，等同于臨時(shí)表D.CTE無(wú)法在UPDATE語(yǔ)句中使用答案：A解析：普通CTE的作用域僅限當(dāng)前查詢；遞歸CTE要求錨點(diǎn)成員在前；CTE是邏輯上的臨時(shí)結(jié)果集，不永久存儲(chǔ)；CTE可用于UPDATE的FROM子句。4.在MySQL8.0中，以下哪條語(yǔ)句可以為表t_user的name字段創(chuàng)建全文索引？()A.CREATEINDEXidx_nameONt_user(name)FULLTEXT;B.ALTERTABLEt_userADDFULLTEXTINDEXidx_name(name);C.CREATEFULLTEXTINDEXidx_nameONt_user(name(20));D.ALTERTABLEt_userADDINDEXidx_name(name)TYPEFULLTEXT;答案：B解析：MySQL全文索引創(chuàng)建語(yǔ)法為ALTERTABLE表名ADDFULLTEXTINDEX索引名(列名)；A選項(xiàng)語(yǔ)法順序錯(cuò)誤，C選項(xiàng)指定長(zhǎng)度無(wú)意義（全文索引不支持列長(zhǎng)度限制），D選項(xiàng)TYPE關(guān)鍵字錯(cuò)誤。5.以下關(guān)于事務(wù)隔離級(jí)別的描述中，能避免“幻讀”的是()A.讀未提交（READUNCOMMITTED）B.讀已提交（READCOMMITTED）C.可重復(fù)讀（REPEATABLEREAD）D.串行化（SERIALIZABLE）答案：D解析：串行化通過(guò)強(qiáng)制事務(wù)串行執(zhí)行，完全避免臟讀、不可重復(fù)讀和幻讀；可重復(fù)讀（如MySQL默認(rèn)級(jí)別）可能通過(guò)間隙鎖避免幻讀，但標(biāo)準(zhǔn)定義中僅串行化能嚴(yán)格避免。6.在SQLServer中，使用MERGE語(yǔ)句實(shí)現(xiàn)“當(dāng)訂單存在時(shí)更新金額，不存在時(shí)插入新訂單”，以下哪種子句是必須的？()A.WHENMATCHEDTHENUPDATEB.WHENNOTMATCHEDTHENINSERTC.USING源表ON匹配條件D.OUTPUT$ACTION答案：C解析：MERGE語(yǔ)句必須通過(guò)USING源表ON匹配條件定義目標(biāo)表與源表的關(guān)聯(lián)邏輯，其他子句可根據(jù)需求選擇是否添加。7.以下關(guān)于JSONB數(shù)據(jù)類型（PostgreSQL）的操作符中，用于判斷是否包含指定鍵的是()A.@>B.<@C.?D.?&答案：C解析：?操作符判斷JSONB是否包含指定鍵（文本）；@>判斷是否包含子JSON，?&判斷是否同時(shí)包含多個(gè)鍵。8.以下哪種索引類型無(wú)法在Oracle中創(chuàng)建？()A.位圖索引（BitmapIndex）B.函數(shù)索引（Function-BasedIndex）C.哈希索引（HashIndex）D.倒排索引（InvertedIndex）答案：D解析：倒排索引常見于全文檢索引擎（如Elasticsearch），Oracle支持位圖、函數(shù)、哈希索引（通過(guò)哈希分區(qū)或顯式創(chuàng)建），但不直接支持倒排索引。9.在SQL中，以下哪條語(yǔ)句可以正確查詢“每個(gè)部門薪資最高的員工”？()A.SELECTdept_id,emp_name,MAX(salary)FROMempGROUPBYdept_id;B.SELECTe1.dept_id,e1.emp_name,e1.salaryFROMempe1WHEREe1.salary=(SELECTMAX(salary)FROMempe2WHEREe2.dept_id=e1.dept_id);C.SELECTdept_id,emp_name,salaryFROMempQUALIFYROW_NUMBER()OVER(PARTITIONBYdept_idORDERBYsalaryDESC)=1;D.以上都不正確答案：B解析：A選項(xiàng)GROUPBY后SELECT非聚合列（emp_name）會(huì)報(bào)錯(cuò)（除非使用ANY_VALUE等函數(shù)）；C選項(xiàng)QUALIFY子句為某些數(shù)據(jù)庫(kù)（如Snowflake）專有，標(biāo)準(zhǔn)SQL不支持；B選項(xiàng)通過(guò)相關(guān)子查詢正確獲取各部門最高薪員工。10.關(guān)于存儲(chǔ)過(guò)程（StoredProcedure）和函數(shù)（Function）的區(qū)別，錯(cuò)誤的是()A.存儲(chǔ)過(guò)程可以有多個(gè)輸出參數(shù)，函數(shù)只能返回單一值B.函數(shù)可以在SELECT語(yǔ)句中調(diào)用，存儲(chǔ)過(guò)程通常需要CALL調(diào)用C.存儲(chǔ)過(guò)程支持事務(wù)控制（如COMMIT），函數(shù)不支持D.兩者都可以包含SQL語(yǔ)句和流程控制邏輯答案：C解析：部分?jǐn)?shù)據(jù)庫(kù)（如PostgreSQL）允許函數(shù)中使用事務(wù)控制（需聲明為VOLATILE），因此“函數(shù)不支持事務(wù)控制”的描述不準(zhǔn)確。二、填空題（共5題，每題3分，共15分）1.在SQL中，使用____語(yǔ)句可以將多個(gè)SELECT的結(jié)果合并，并自動(dòng)去重。答案：UNION2.Oracle中，用于查看當(dāng)前會(huì)話執(zhí)行計(jì)劃的動(dòng)態(tài)性能視圖是____。答案：V$SQL_PLAN3.PostgreSQL中，通過(guò)____關(guān)鍵字可以將臨時(shí)表的作用域設(shè)置為當(dāng)前會(huì)話（Session）。答案：TEMPORARY（或TEMP）4.在SQLServer中，____鎖模式用于防止其他事務(wù)修改當(dāng)前事務(wù)讀取的數(shù)據(jù)，直到當(dāng)前事務(wù)提交。答案：共享（Shared，或S）5.MySQL8.0支持的窗口函數(shù)中，____函數(shù)用于計(jì)算當(dāng)前行在分區(qū)內(nèi)的排名（相同值排名相同，后續(xù)排名跳過(guò)）。答案：DENSE_RANK三、判斷題（共5題，每題2分，共10分）1.索引覆蓋（CoveringIndex）是指查詢所需的所有列都包含在索引中，因此無(wú)需回表查詢。()答案：√解析：覆蓋索引通過(guò)索引本身滿足查詢需求，避免訪問(wèn)基表數(shù)據(jù)，提升查詢效率。2.事務(wù)的ACID特性中，“隔離性（Isolation）”確保事務(wù)執(zhí)行結(jié)果與串行執(zhí)行結(jié)果一致。()答案：×解析：隔離性要求事務(wù)間互不干擾，但嚴(yán)格一致性由“原子性（Atomicity）”和“持久性（Durability）”共同保證；串行化隔離級(jí)別才確保結(jié)果與串行執(zhí)行一致。3.在SQL中，TRUNCATETABLE語(yǔ)句會(huì)記錄完整的事務(wù)日志，因此可以回滾。()答案：×解析：TRUNCATE通常使用最小日志記錄（如SQLServer），且不可回滾（除非在事務(wù)中且數(shù)據(jù)庫(kù)支持），而DELETE會(huì)記錄完整日志。4.PostgreSQL的JSON和JSONB數(shù)據(jù)類型的區(qū)別在于，JSONB會(huì)對(duì)數(shù)據(jù)進(jìn)行解析并存儲(chǔ)為二進(jìn)制格式，支持索引。()答案：√解析：JSONB是PostgreSQL中優(yōu)化的JSON存儲(chǔ)類型，支持索引和高效查詢，而JSON僅存儲(chǔ)原始文本。5.在SQL中，HAVING子句用于過(guò)濾分組前的行，WHERE子句用于過(guò)濾分組后的行。()答案：×解析：WHERE過(guò)濾分組前的行，HAVING過(guò)濾分組后的聚合結(jié)果。四、簡(jiǎn)答題（共4題，每題7分，共28分）1.簡(jiǎn)述索引優(yōu)化的常見策略。(1).選擇高區(qū)分度列作為索引鍵（如用戶ID比性別列更適合）。

(2).避免冗余索引（如已有(a,b)索引時(shí)，無(wú)需單獨(dú)創(chuàng)建(a)索引）。

(3).對(duì)頻繁查詢的過(guò)濾、排序、連接列創(chuàng)建索引。

(4).合理使用復(fù)合索引（遵循“最左前綴”原則）。

(5).對(duì)大表考慮分區(qū)索引或覆蓋索引。

(6).定期重建/重組索引以維護(hù)碎片率。2.說(shuō)明游標(biāo)的使用場(chǎng)景及注意事項(xiàng)。(1).使用場(chǎng)景：需要逐行處理數(shù)據(jù)（如復(fù)雜業(yè)務(wù)邏輯、存儲(chǔ)過(guò)程中的循環(huán)操作）。

(2).注意事項(xiàng)：避免在大表上使用游標(biāo)（性能低下）；明確聲明游標(biāo)類型（如靜態(tài)/動(dòng)態(tài)）；及時(shí)關(guān)閉游標(biāo)釋放資源；盡量用集合操作替代游標(biāo)。3.對(duì)比分析普通視圖（View）和物化視圖（MaterializedView）的差異。(1).存儲(chǔ)方式：普通視圖是虛擬表（存儲(chǔ)SQL語(yǔ)句），物化視圖存儲(chǔ)實(shí)際數(shù)據(jù)。

(2).數(shù)據(jù)更新：普通視圖實(shí)時(shí)反映基表變化，物化視圖需手動(dòng)或定時(shí)刷新。

(3).性能：物化視圖查詢更快（直接訪問(wèn)物理數(shù)據(jù)），普通視圖依賴基表查詢性能。

(4).使用場(chǎng)景：物化視圖適用于讀多寫少、查詢復(fù)雜的場(chǎng)景；普通視圖用于簡(jiǎn)化查詢、數(shù)據(jù)安全（如列權(quán)限控制）。4.簡(jiǎn)述SQL注入攻擊的原理及防御方法。(1).原理：攻擊者通過(guò)輸入惡意SQL代碼，篡改原查詢邏輯，獲取或破壞數(shù)據(jù)庫(kù)數(shù)據(jù)。

(2).防御方法：使用預(yù)編譯語(yǔ)句（PreparedStatement）綁定參數(shù)；對(duì)用戶輸入進(jìn)行嚴(yán)格校驗(yàn)（如類型、長(zhǎng)度、特殊字符過(guò)濾）；最小化數(shù)據(jù)庫(kù)用戶權(quán)限；避免在應(yīng)用層拼接SQL字符串。五、論述題（共2題，每題10分，共20分）1.結(jié)合實(shí)際業(yè)務(wù)場(chǎng)景，論述分區(qū)表（PartitionedTable）的設(shè)計(jì)策略及優(yōu)勢(shì)。(1).設(shè)計(jì)策略：(1).選擇分區(qū)鍵：根據(jù)業(yè)務(wù)查詢模式（如時(shí)間范圍查詢選日期列，地域查詢選地區(qū)ID）。

(2).分區(qū)類型：范圍分區(qū)（RangePartition，如按月份分區(qū)訂單表）、列表分區(qū)（ListPartition，如按國(guó)家分區(qū)用戶表）、哈希分區(qū)（HashPartition，如按用戶ID取模均分?jǐn)?shù)據(jù)）。

(3).分區(qū)數(shù)量：避免過(guò)多分區(qū)（增加維護(hù)成本），確保單分區(qū)數(shù)據(jù)量合理（如100GB以內(nèi)）。

(2).優(yōu)勢(shì)：(1).提升查詢性能：通過(guò)分區(qū)剪枝（PartitionPruning）僅掃描目標(biāo)分區(qū)。

(2).簡(jiǎn)化數(shù)據(jù)維護(hù)：如按月份分區(qū)的日志表，可直接刪除過(guò)期分區(qū)。

(3).提高并發(fā)能力：不同分區(qū)可并行操作（如同時(shí)查詢不同月份的數(shù)據(jù)）。

(4).優(yōu)化存儲(chǔ)效率：分區(qū)可單獨(dú)設(shè)置存儲(chǔ)參數(shù)（如壓縮、表空間）。2.分析存儲(chǔ)過(guò)程在企業(yè)級(jí)數(shù)據(jù)庫(kù)中的應(yīng)用價(jià)值，并舉例說(shuō)明其典型使用場(chǎng)景。(1).應(yīng)用價(jià)值：(1).減少網(wǎng)絡(luò)開銷：將多次交互的SQL邏輯封裝為存儲(chǔ)過(guò)程，僅需一次調(diào)用。

(2).增強(qiáng)數(shù)據(jù)安全性：通過(guò)存儲(chǔ)過(guò)程控制數(shù)據(jù)訪問(wèn)權(quán)限，避免直接操作表。

(3).提高代碼復(fù)用性：公共業(yè)務(wù)邏輯（如訂單狀態(tài)更新）可重復(fù)調(diào)用。

(4).保證業(yè)務(wù)一致性：存儲(chǔ)過(guò)程中的事務(wù)控制可確保多步操作的原子性。

(2).典型場(chǎng)景：(1).銀行轉(zhuǎn)賬：存儲(chǔ)過(guò)程包含檢查余額、扣減轉(zhuǎn)出賬戶、增加轉(zhuǎn)入賬戶、記錄日志等步驟，通過(guò)事務(wù)保證原子性。

(2).電商大促庫(kù)存扣減：存儲(chǔ)過(guò)程中判斷庫(kù)存是否充足、鎖定庫(kù)存、生成訂單，避免超賣。

(3).定期數(shù)據(jù)歸檔：存儲(chǔ)過(guò)程按規(guī)則將歷史數(shù)據(jù)從生產(chǎn)表遷移到歸檔表，維護(hù)主表性能。六、編程題（共3題，每題9分，共27分）1.編寫SQL語(yǔ)句：查詢2024年Q4（10-12月）每個(gè)商品類別的銷售額（sales_amount），要求按銷售額降序排列，且只顯示銷售額超過(guò)10萬(wàn)元的類別。（表結(jié)構(gòu)：t_sales(sku_idINT,categoryVARCHAR(20),sale_dateDATE,sales_amountDECIMAL(10,2))）答案：SELECT

category,

SUM(sales_amount)AStotal_sales

FROM

t_sales

WHERE

sale_dateBETWEEN'2024-10-01'AND'2024-12-31'

GROUPBY

category

HAVING

SUM(sales_amount)>100000

ORDERBY

total_salesDESC;2.使用窗口函數(shù)編寫SQL語(yǔ)句：查詢每個(gè)部門中薪資排名前3的員工（允許并列），結(jié)果包含部門ID、員工姓名、薪資、部門內(nèi)排名。（表結(jié)構(gòu)：t_emp(emp_idINT,dept_idINT,emp_nameVARCHAR(20),salaryDECIMAL(10,2))）答案：SELECT

dept_id,

emp_name,

salary,

rank_num

FROM(

SELECT

dept_id,

emp_name,

salary,

DENSE_RANK()OVER(PARTITIONBYdept_idORDERBYsalaryDESC)ASrank_num

FROM

t_emp

)ASsub

WHERE

rank_num<=3;3.編寫存儲(chǔ)過(guò)程（PostgreSQL語(yǔ)法）：實(shí)現(xiàn)用戶積分扣減功能，要求：輸入?yún)?shù)：用戶ID（user_id）、扣減積分（points）

業(yè)務(wù)邏輯：若用戶當(dāng)前積分≥扣減積分，則扣減并記錄日志；否則回滾并拋出異常“積分不足”

日志表結(jié)構(gòu)：t_points_log(user_idINT,change_typeVARCHAR(10),change_pointsDECIMAL(10,2),operate_timeTIMESTAMP)答案：CREATEORREPLACEPROCEDUREsp_deduct_points(

INp_user_id