2025年新版sql编程试题及答案

2025年新版sql编程试题及答案一、单项选择题（每题2分，共20分）1.以下关于SQL中UNION和UNIONALL的描述，正确的是（）。A.UNION会自动去重，UNIONALL不会去重B.UNIONALL执行效率低于UNIONC.UNION要求两个结果集列数不同，UNIONALL要求列数相同D.UNION支持WHERE过滤，UNIONALL不支持2.若要为表t_user的age字段创建哈希索引（HashIndex），适用于以下哪种场景？（）A.范围查询（如age>25）B.等值查询（如age=30）C.排序查询（如ORDERBYage）D.模糊查询（如ageLIKE'2%'）3.事务隔离级别为“可重复读（RepeatableRead）”时，可能出现的问题是（）。A.脏读（DirtyRead）B.不可重复读（Non-RepeatableRead）C.幻读（PhantomRead）D.以上都不会出现4.以下关于窗口函数（WindowFunction）的说法，错误的是（）。A.窗口函数使用OVER子句定义窗口范围B.窗口函数可以与GROUPBY子句同时使用C.窗口函数的结果不会减少行数D.ROW_NUMBER()和RANK()的区别在于是否处理并列值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'（假设使用MySQL）6.若要查询每个部门中工资最高的员工信息（可能有并列），最合理的方法是（）。A.使用GROUPBY部门，MAX(工资)，再通过子查询关联原始表B.使用窗口函数DENSE_RANK()按部门分区并按工资降序排序，取排名=1的记录C.使用自连接，比较同部门员工的工资D.使用DISTINCTON（部门）按工资降序排序后去重7.以下关于CTE（公共表表达式）的描述，错误的是（）。A.普通CTE（非递归）在执行时会被优化为子查询B.递归CTE必须包含锚点成员和递归成员C.CTE可以提高查询可读性，但无法提升性能D.CTE可以在同一个查询中被多次引用8.执行“UPDATEt_orderSETstatus='已完成'WHEREcreate_time<'2025-01-01'”时，若表t_order的create_time字段未建立索引，可能导致的问题是（）。A.锁范围过大，影响并发性能B.查询结果错误C.事务自动回滚D.索引失效9.以下关于物化视图（MaterializedView）的说法，正确的是（）。A.物化视图会实时刷新，与普通视图相同B.物化视图存储实际数据，适合复杂查询的频繁访问C.物化视图不支持索引D.物化视图只能在PostgreSQL中使用10.若要统计2024年每个月的订单数量（包括没有订单的月份），正确的做法是（）。A.按订单日期分组，使用DATE_TRUNC('month',order_time)B.提供包含12个月的辅助表，与订单表左连接后统计C.使用窗口函数按月份排序并累计D.使用FULLOUTERJOIN连接月份表和订单表二、填空题（每空2分，共20分）1.在SQL中，使用______关键字为表添加外键约束。2.若要将查询结果导出为CSV文件（假设使用PostgreSQL），可使用______命令。3.窗口函数中，______子句用于定义窗口的范围（如ROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW）。4.事务的ACID特性中，______指事务执行的结果必须使数据库从一个一致性状态变到另一个一致性状态。5.对于表t_student（idINTPRIMARYKEY,nameVARCHAR(50),scoreDECIMAL(5,2)），若要查询分数最高的前3名学生（允许并列），应使用窗口函数______。6.在MySQL中，JSON类型字段的路径运算符是______（如获取data字段中key为“city”的值）。7.若要限制表t_user的age字段值在18到60之间，应使用______约束。8.递归CTE的语法中，必须使用______关键字分隔锚点成员和递归成员。9.索引的类型中，______索引适用于全文搜索场景（如查找包含“SQL”的文章）。10.执行“SELECTFROMt_aLEFTJOINt_bONt_a.id=t_b.a_id”时，若t_b中无匹配记录，t_b的字段值会显示为______。三、简答题（每题8分，共40分）1.简述索引的优缺点。2.说明事务隔离级别“读已提交（ReadCommitted）”与“可重复读（RepeatableRead）”的区别及适用场景。3.子查询和JOIN都可以实现多表数据关联，二者的主要区别是什么？在实际开发中如何选择？4.窗口函数与聚合函数（如SUM、AVG）的核心区别是什么？举例说明窗口函数的典型应用场景。5.对于高并发场景下的订单表（t_order），包含order_id（主键）、user_id、amount、create_time等字段，如何优化“查询某用户最近30天的订单总金额”的性能？四、编程题（共70分）说明：以下题目基于某医疗预约系统的数据库，表结构如下（假设使用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.基础查询（10分）查询2024年1月1日至2024年12月31日期间，状态为“已完成”的预约记录，要求结果包含患者姓名、医生姓名、科室、预约时间和金额，按预约时间降序排列。2.分组统计（15分）统计各科室2024年每个季度的总预约金额（季度划分：Q1-1-3月，Q2-4-6月，Q3-7-9月，Q4-10-12月），结果需包含科室名称、季度（如“Q1”）、总金额，无数据的季度显示为0。3.窗口函数应用（15分）查询每个科室中，2024年预约金额排名前2的医生（允许并列），结果包含科室、医生姓名、总金额、排名（排名相同则并列，如第1名有2人则下一名为第3名）。4.递归CTE与JSON处理（15分）假设t_doctor表新增字段team_infoJSONB，存储医生所属团队层级（如{"leader":101,"members":[102,103,{"sub_leader":104,"members":[105]}]}）。需查询doctor_id为101的医生及其所有下级成员（包括直接和间接成员）的姓名。5.性能优化与事务（15分）（1）针对t_appointment表的高频查询“根据patient_id和appt_time范围查询预约记录”，设计索引优化方案，并说明理由。（2）编写一个事务，实现以下逻辑：患者取消预约（状态改为“已取消”），并将对应的挂号金额退还至患者账户（假设存在t_account表，包含patient_id和balance字段）。要求处理可能的异常（如余额不足、锁冲突），确保数据一致性。参考答案一、单项选择题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.优点：加速查询（尤其是过滤、排序）；减少CPU和I/O消耗。缺点：增加写操作（INSERT/UPDATE/DELETE）的开销；占用额外存储空间；可能因索引失效导致性能下降。2.区别：读已提交允许事务读取其他事务已提交的修改，可能出现不可重复读；可重复读保证事务内多次读取同一数据结果一致，但可能出现幻读。场景：读已提交适用于对一致性要求不高但需要高并发的场景（如普通查询）；可重复读适用于对数据一致性要求较高的场景（如财务统计）。3.区别：子查询逻辑上先执行内层查询再执行外层，可能导致多次扫描表；JOIN通过连接操作一次性完成数据关联，通常更高效。选择：简单关联用JOIN（性能更好）；复杂条件过滤或需要依赖外层查询参数时用子查询（可读性更高）。4.核心区别：聚合函数将多行数据合并为一行，窗口函数保留原始行数并在每行上计算聚合值。场景：如查询每个员工的工资与部门平均工资的差值（需保留每个员工的行，同时显示部门平均值）。5.优化方案：①为user_id和create_time字段创建联合索引（覆盖查询条件）；②将查询结果缓存（如Redis），减少数据库访问；③分区表（按时间范围分区），缩小查询范围。四、编程题1.基础查询```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.分组统计```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.窗口函数应用```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.性能优化与事务（1）