2025年新版sql編程試題及答案一、單項(xiàng)選擇題（每題2分，共20分）1.以下關(guān)于SQL中UNION和UNIONALL的描述，正確的是（）。A.UNION會自動去重，UNIONALL不會去重B.UNIONALL執(zhí)行效率低于UNIONC.UNION要求兩個結(jié)果集列數(shù)不同，UNIONALL要求列數(shù)相同D.UNION支持WHERE過濾，UNIONALL不支持2.若要為表t_user的age字段創(chuàng)建哈希索引（HashIndex），適用于以下哪種場景？（）A.范圍查詢（如age>25）B.等值查詢（如age=30）C.排序查詢（如ORDERBYage）D.模糊查詢（如ageLIKE'2%'）3.事務(wù)隔離級別為“可重復(fù)讀（RepeatableRead）”時，可能出現(xiàn)的問題是（）。A.臟讀（DirtyRead）B.不可重復(fù)讀（Non-RepeatableRead）C.幻讀（PhantomRead）D.以上都不會出現(xiàn)4.以下關(guān)于窗口函數(shù)（WindowFunction）的說法，錯誤的是（）。A.窗口函數(shù)使用OVER子句定義窗口范圍B.窗口函數(shù)可以與GROUPBY子句同時使用C.窗口函數(shù)的結(jié)果不會減少行數(shù)D.ROW_NUMBER()和RANK()的區(qū)別在于是否處理并列值5.對于JSON類型字段的操作，以下SQL語句正確的是（）。A.SELECTdata->'name'FROMt_infoWHEREdata->>'age'='25'B.SELECTdata->>'name'FROMt_infoWHEREdata->'age'=25C.SELECTdata>'{address,city}'FROMt_infoWHEREdata->'age'::INT=25D.SELECTJSON_EXTRACT(data,'$.name')FROMt_infoWHEREdata->>'age'='25'（假設(shè)使用MySQL）6.若要查詢每個部門中工資最高的員工信息（可能有并列），最合理的方法是（）。A.使用GROUPBY部門，MAX(工資)，再通過子查詢關(guān)聯(lián)原始表B.使用窗口函數(shù)DENSE_RANK()按部門分區(qū)并按工資降序排序，取排名=1的記錄C.使用自連接，比較同部門員工的工資D.使用DISTINCTON（部門）按工資降序排序后去重7.以下關(guān)于CTE（公共表表達(dá)式）的描述，錯誤的是（）。A.普通CTE（非遞歸）在執(zhí)行時會被優(yōu)化為子查詢B.遞歸CTE必須包含錨點(diǎn)成員和遞歸成員C.CTE可以提高查詢可讀性，但無法提升性能D.CTE可以在同一個查詢中被多次引用8.執(zhí)行“UPDATEt_orderSETstatus='已完成'WHEREcreate_time<'2025-01-01'”時，若表t_order的create_time字段未建立索引，可能導(dǎo)致的問題是（）。A.鎖范圍過大，影響并發(fā)性能B.查詢結(jié)果錯誤C.事務(wù)自動回滾D.索引失效9.以下關(guān)于物化視圖（MaterializedView）的說法，正確的是（）。A.物化視圖會實(shí)時刷新，與普通視圖相同B.物化視圖存儲實(shí)際數(shù)據(jù)，適合復(fù)雜查詢的頻繁訪問C.物化視圖不支持索引D.物化視圖只能在PostgreSQL中使用10.若要統(tǒng)計(jì)2024年每個月的訂單數(shù)量（包括沒有訂單的月份），正確的做法是（）。A.按訂單日期分組，使用DATE_TRUNC('month',order_time)B.提供包含12個月的輔助表，與訂單表左連接后統(tǒng)計(jì)C.使用窗口函數(shù)按月份排序并累計(jì)D.使用FULLOUTERJOIN連接月份表和訂單表二、填空題（每空2分，共20分）1.在SQL中，使用______關(guān)鍵字為表添加外鍵約束。2.若要將查詢結(jié)果導(dǎo)出為CSV文件（假設(shè)使用PostgreSQL），可使用______命令。3.窗口函數(shù)中，______子句用于定義窗口的范圍（如ROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW）。4.事務(wù)的ACID特性中，______指事務(wù)執(zhí)行的結(jié)果必須使數(shù)據(jù)庫從一個一致性狀態(tài)變到另一個一致性狀態(tài)。5.對于表t_student（idINTPRIMARYKEY,nameVARCHAR(50),scoreDECIMAL(5,2)），若要查詢分?jǐn)?shù)最高的前3名學(xué)生（允許并列），應(yīng)使用窗口函數(shù)______。6.在MySQL中，JSON類型字段的路徑運(yùn)算符是______（如獲取data字段中key為“city”的值）。7.若要限制表t_user的age字段值在18到60之間，應(yīng)使用______約束。8.遞歸CTE的語法中，必須使用______關(guān)鍵字分隔錨點(diǎn)成員和遞歸成員。9.索引的類型中，______索引適用于全文搜索場景（如查找包含“SQL”的文章）。10.執(zhí)行“SELECTFROMt_aLEFTJOINt_bONt_a.id=t_b.a_id”時，若t_b中無匹配記錄，t_b的字段值會顯示為______。三、簡答題（每題8分，共40分）1.簡述索引的優(yōu)缺點(diǎn)。2.說明事務(wù)隔離級別“讀已提交（ReadCommitted）”與“可重復(fù)讀（RepeatableRead）”的區(qū)別及適用場景。3.子查詢和JOIN都可以實(shí)現(xiàn)多表數(shù)據(jù)關(guān)聯(lián)，二者的主要區(qū)別是什么？在實(shí)際開發(fā)中如何選擇？4.窗口函數(shù)與聚合函數(shù)（如SUM、AVG）的核心區(qū)別是什么？舉例說明窗口函數(shù)的典型應(yīng)用場景。5.對于高并發(fā)場景下的訂單表（t_order），包含order_id（主鍵）、user_id、amount、create_time等字段，如何優(yōu)化“查詢某用戶最近30天的訂單總金額”的性能？四、編程題（共70分）說明：以下題目基于某醫(yī)療預(yù)約系統(tǒng)的數(shù)據(jù)庫，表結(jié)構(gòu)如下（假設(shè)使用PostgreSQL）：t_patient（patient_idINTPRIMARYKEY,nameVARCHAR(50),genderVARCHAR(10),birth_dateDATE）t_doctor（doctor_idINTPRIMARYKEY,nameVARCHAR(50),departmentVARCHAR(30),titleVARCHAR(20)）t_appointment（appt_idSERIALPRIMARYKEY,patient_idINTREFERENCESt_patient(patient_id),doctor_idINTREFERENCESt_doctor(doctor_id),appt_timeTIMESTAMP,statusVARCHAR(20),amountDECIMAL(8,2)）1.基礎(chǔ)查詢（10分）查詢2024年1月1日至2024年12月31日期間，狀態(tài)為“已完成”的預(yù)約記錄，要求結(jié)果包含患者姓名、醫(yī)生姓名、科室、預(yù)約時間和金額，按預(yù)約時間降序排列。2.分組統(tǒng)計(jì)（15分）統(tǒng)計(jì)各科室2024年每個季度的總預(yù)約金額（季度劃分：Q1-1-3月，Q2-4-6月，Q3-7-9月，Q4-10-12月），結(jié)果需包含科室名稱、季度（如“Q1”）、總金額，無數(shù)據(jù)的季度顯示為0。3.窗口函數(shù)應(yīng)用（15分）查詢每個科室中，2024年預(yù)約金額排名前2的醫(yī)生（允許并列），結(jié)果包含科室、醫(yī)生姓名、總金額、排名（排名相同則并列，如第1名有2人則下一名為第3名）。4.遞歸CTE與JSON處理（15分）假設(shè)t_doctor表新增字段team_infoJSONB，存儲醫(yī)生所屬團(tuán)隊(duì)層級（如{"leader":101,"members":[102,103,{"sub_leader":104,"members":[105]}]}）。需查詢doctor_id為101的醫(yī)生及其所有下級成員（包括直接和間接成員）的姓名。5.性能優(yōu)化與事務(wù)（15分）（1）針對t_appointment表的高頻查詢“根據(jù)patient_id和appt_time范圍查詢預(yù)約記錄”，設(shè)計(jì)索引優(yōu)化方案，并說明理由。（2）編寫一個事務(wù)，實(shí)現(xiàn)以下邏輯：患者取消預(yù)約（狀態(tài)改為“已取消”），并將對應(yīng)的掛號金額退還至患者賬戶（假設(shè)存在t_account表，包含patient_id和balance字段）。要求處理可能的異常（如余額不足、鎖沖突），確保數(shù)據(jù)一致性。參考答案一、單項(xiàng)選擇題1.A2.B3.C4.B5.D6.B7.C8.A9.B10.B二、填空題1.FOREIGNKEY2.COPY3.RANGE/ROWS4.一致性（Consistency）5.DENSE_RANK()6.->>7.CHECK8.UNIONALL9.全文（Full-Text）10.NULL三、簡答題1.優(yōu)點(diǎn)：加速查詢（尤其是過濾、排序）；減少CPU和I/O消耗。缺點(diǎn)：增加寫操作（INSERT/UPDATE/DELETE）的開銷；占用額外存儲空間；可能因索引失效導(dǎo)致性能下降。2.區(qū)別：讀已提交允許事務(wù)讀取其他事務(wù)已提交的修改，可能出現(xiàn)不可重復(fù)讀；可重復(fù)讀保證事務(wù)內(nèi)多次讀取同一數(shù)據(jù)結(jié)果一致，但可能出現(xiàn)幻讀。場景：讀已提交適用于對一致性要求不高但需要高并發(fā)的場景（如普通查詢）；可重復(fù)讀適用于對數(shù)據(jù)一致性要求較高的場景（如財(cái)務(wù)統(tǒng)計(jì)）。3.區(qū)別：子查詢邏輯上先執(zhí)行內(nèi)層查詢再執(zhí)行外層，可能導(dǎo)致多次掃描表；JOIN通過連接操作一次性完成數(shù)據(jù)關(guān)聯(lián)，通常更高效。選擇：簡單關(guān)聯(lián)用JOIN（性能更好）；復(fù)雜條件過濾或需要依賴外層查詢參數(shù)時用子查詢（可讀性更高）。4.核心區(qū)別：聚合函數(shù)將多行數(shù)據(jù)合并為一行，窗口函數(shù)保留原始行數(shù)并在每行上計(jì)算聚合值。場景：如查詢每個員工的工資與部門平均工資的差值（需保留每個員工的行，同時顯示部門平均值）。5.優(yōu)化方案：①為user_id和create_time字段創(chuàng)建聯(lián)合索引（覆蓋查詢條件）；②將查詢結(jié)果緩存（如Redis），減少數(shù)據(jù)庫訪問；③分區(qū)表（按時間范圍分區(qū)），縮小查詢范圍。四、編程題1.基礎(chǔ)查詢```sqlSELECTASpatient_name,ASdoctor_name,d.department,a.appt_time,a.amountFROMt_appointmentaJOINt_patientpONa.patient_id=p.patient_idJOINt_doctordONa.doctor_id=d.doctor_idWHEREa.status='已完成'ANDa.appt_timeBETWEEN'2024-01-01'AND'2024-12-3123:59:59'ORDERBYa.appt_timeDESC;```2.分組統(tǒng)計(jì)```sqlWITHquartersAS(SELECTgenerate_series(1,4)ASq_num)SELECTd.department,'Q'||q.q_numASquarter,COALESCE(SUM(a.amount),0)AStotal_amountFROMquartersqCROSSJOIN(SELECTDISTINCTdepartmentFROMt_doctor)dLEFTJOINt_appointmentaONd.department=(SELECTdepartmentFROMt_doctorWHEREdoctor_id=a.doctor_id)ANDEXTRACT(QUARTERFROMa.appt_time)=q.q_numANDa.appt_timeBETWEEN'2024-01-01'AND'2024-12-31'GROUPBYd.department,q.q_numORDERBYd.department,q.q_num;```3.窗口函數(shù)應(yīng)用```sqlWITHdoctor_amountAS(SELECTd.department,d.doctor_id,,SUM(a.amount)AStotal_amount,DENSE_RANK()OVER(PARTITIONBYd.departmentORDERBYSUM(a.amount)DESC)ASrnkFROMt_doctordLEFTJOINt_appointmentaONd.doctor_id=a.doctor_idANDa.appt_timeBETWEEN'2024-01-01'AND'2024-12-31'GROUPBYd.department,d.doctor_id,)SELECTdepartment,name,total_amount,rnkFROMdoctor_amountWHERErnk<=2;```4.遞歸CTE與JSON處理```sqlWITHRECURSIVEteam_membersAS(SELECT101ASdoctor_id,team_info,1ASlevelFROMt_doctorWHEREdoctor_id=101UNIONALLSELECT(jsonb_array_elements_text(CASEWHENjsonb_typeof(m.member)='object'THENm.member->'members'ELSEARRAY[m.member]::jsonbEND))::INTASdoctor_id,CASEWHENjsonb_typeof(m.member)='object'THENm.memberELSENULLENDASteam_info,level+1FROMteam_memberstm,jsonb_array_elements(tm.team_info->'members')ASm(member)WHEREm.member::TEXT~'^\d+$'ORjsonb_typeof(m.member)='object')SELECTFROMteam_memberstmJOINt_doctordONtm.doctor_id=d.doctor_id;```5.性能優(yōu)化與事務(wù)（1）