2025年数据库SQL操作题库及答案

2025年数据库SQL操作题库及答案一、基础SQL操作1.现有某在线教育平台数据库，需创建以下三张表：学生表（student）：包含字段s_id（学生ID，主键，INT类型，自增）、s_name（姓名，VARCHAR(50)，非空）、s_gender（性别，ENUM('男','女','其他')）、s_birth（出生日期，DATE）、enroll_time（入学时间，DATETIME，默认当前时间）、is_graduated（是否毕业，TINYINT(1)，默认0）。课程表（course）：包含字段c_id（课程ID，主键，CHAR(6)）、c_name（课程名，VARCHAR(100)，非空）、credit（学分，TINYINT，CHECK(creditBETWEEN1AND5)）、c_type（课程类型，VARCHAR(20)，如“必修”“选修”）。成绩表（score）：包含字段sc_id（记录ID，主键，INT，自增）、s_id（学生ID，外键，REFERENCESstudent(s_id)ONDELETECASCADE）、c_id（课程ID，外键，REFERENCEScourse(c_id)ONUPDATECASCADE）、score（分数，DECIMAL(5,2)，CHECK(scoreBETWEEN0AND100)）、exam_date（考试日期，DATE）、exam_type（考试类型，VARCHAR(20)，如“期中”“期末”）。请写出创建这三张表的SQL语句（要求包含所有约束）。答案：```sqlCREATETABLEstudent(s_idINTAUTO_INCREMENTPRIMARYKEY,s_nameVARCHAR(50)NOTNULL,s_genderENUM('男','女','其他'),s_birthDATE,enroll_timeDATETIMEDEFAULTCURRENT_TIMESTAMP,is_graduatedTINYINT(1)DEFAULT0);CREATETABLEcourse(c_idCHAR(6)PRIMARYKEY,c_nameVARCHAR(100)NOTNULL,creditTINYINTCHECK(creditBETWEEN1AND5),c_typeVARCHAR(20));CREATETABLEscore(sc_idINTAUTO_INCREMENTPRIMARYKEY,s_idINT,c_idCHAR(6),scoreDECIMAL(5,2)CHECK(scoreBETWEEN0AND100),exam_dateDATE,exam_typeVARCHAR(20),FOREIGNKEY(s_id)REFERENCESstudent(s_id)ONDELETECASCADE,FOREIGNKEY(c_id)REFERENCEScourse(c_id)ONUPDATECASCADE);```2.向student表插入以下数据（要求使用一条INSERT语句）：姓名“张小雨”，性别“女”，出生日期“2003-05-12”，入学时间使用默认值；姓名“李阳”，性别“男”，出生日期“2002-08-20”，已毕业（is_graduated=1）。答案：```sqlINSERTINTOstudent(s_name,s_gender,s_birth,is_graduated)VALUES('张小雨','女','2003-05-12',0),('李阳','男','2002-08-20',1);```3.将course表中所有学分小于3的“选修”课程的学分统一增加0.5，要求仅修改未结课的课程（假设课程是否结课通过c_id是否以“XK”结尾判断，如“XK001”为已结课）。答案：```sqlUPDATEcourseSETcredit=credit+0.5WHEREc_type='选修'ANDcredit<3ANDc_idNOTLIKE'XK%';```4.删除student表中未毕业（is_graduated=0）且最近1年内无考试记录的学生（考试记录关联score表，考试日期exam_date在最近1年内视为有记录）。答案：```sqlDELETEFROMstudentWHEREis_graduated=0ANDs_idNOTIN(SELECTs_idFROMscoreWHEREexam_date>=DATE_SUB(CURRENT_DATE,INTERVAL1YEAR));```5.查询student表中年龄在18-22岁之间（含边界）的女性学生，结果按入学时间从早到晚排序，显示姓名、性别、年龄（计算方式：当前年份-出生年份，忽略具体月份）。答案：```sqlSELECTs_name,s_gender,YEAR(CURRENT_DATE)YEAR(s_birth)ASageFROMstudentWHEREs_gender='女'ANDYEAR(CURRENT_DATE)YEAR(s_birth)BETWEEN18AND22ORDERBYenroll_timeASC;```二、复杂查询与多表关联6.查询每门课程的平均分、及格率（及格分数≥60）及参考人数，结果包含课程名、平均分（保留2位小数）、及格率（百分比形式，保留1位小数）、参考人数，按平均分降序排列。答案：```sqlSELECTc.c_name,ROUND(AVG(sc.score),2)ASavg_score,CONCAT(ROUND(SUM(CASEWHENsc.score>=60THEN1ELSE0END)/COUNT()100,1),'%')ASpass_rate,COUNT()AStotal_studentsFROMcoursecJOINscorescONc.c_id=sc.c_idGROUPBYc.c_id,c.c_nameORDERBYavg_scoreDESC;```7.查询所有选修了“数据库原理”（课程名）且成绩高于该课程平均分的学生姓名、成绩及排名（使用DENSE_RANK，按成绩降序）。答案：```sqlWITHcourse_avgAS(SELECTc_id,AVG(score)ASavg_scFROMscoreWHEREc_id=(SELECTc_idFROMcourseWHEREc_name='数据库原理'))SELECTs.s_name,sc.score,DENSE_RANK()OVER(ORDERBYsc.scoreDESC)ASrnkFROMstudentsJOINscorescONs.s_id=sc.s_idJOINcourse_avgcaONsc.c_id=ca.c_idWHEREsc.c_id=(SELECTc_idFROMcourseWHEREc_name='数据库原理')ANDsc.score>ca.avg_sc;```8.查询每个学生的总学分（仅计算已及格课程的学分），并按总学分降序排列，显示姓名、总学分（保留1位小数）。答案：```sqlSELECTs.s_name,ROUND(SUM(c.credit),1)AStotal_creditFROMstudentsJOINscorescONs.s_id=sc.s_idJOINcoursecONsc.c_id=c.c_idWHEREsc.score>=60GROUPBYs.s_id,s.s_nameORDERBYtotal_creditDESC;```9.查询2024年入学且未毕业的学生中，至少选修了3门必修课的学生姓名及选修必修课程数量。答案：```sqlSELECTs.s_name,COUNT(DISTINCTsc.c_id)ASrequired_course_cntFROMstudentsJOINscorescONs.s_id=sc.s_idJOINcoursecONsc.c_id=c.c_idWHEREYEAR(s.enroll_time)=2024ANDs.is_graduated=0ANDc.c_type='必修'GROUPBYs.s_id,s.s_nameHAVINGCOUNT(DISTINCTsc.c_id)>=3;```10.使用窗口函数查询每个学生的每门课程成绩与该课程平均分的差值，结果显示姓名、课程名、成绩、差值（保留2位小数）。答案：```sqlSELECTs.s_name,c.c_name,sc.score,ROUND(sc.scoreAVG(sc.score)OVER(PARTITIONBYsc.c_id),2)ASdiff_from_avgFROMstudentsJOINscorescONs.s_id=sc.s_idJOINcoursecONsc.c_id=c.c_id;```三、表结构设计与优化11.某电商平台需设计“订单表”（order），要求记录订单号、用户ID、下单时间、总金额、支付状态（待支付、已支付、已取消）、物流状态（未发货、已发货、已签收）。请结合第三范式，说明是否需要拆分表，并给出最终表结构设计（包含约束）。答案：根据第三范式，支付状态和物流状态属于状态枚举值，若直接存储文本会导致数据冗余（如多个订单的支付状态均为“已支付”），因此应拆分为字典表。最终设计如下：支付状态表（pay_status）：ps_id（主键，TINYINT）、ps_name（状态名，VARCHAR(20)，唯一）。物流状态表（logistics_status）：ls_id（主键，TINYINT）、ls_name（状态名，VARCHAR(20)，唯一）。订单表（order）：order_id（主键，VARCHAR(32)）、user_id（用户ID，INT，外键REFERENCESuser(user_id)）、order_time（下单时间，DATETIME）、total_amount（总金额，DECIMAL(10,2)）、ps_id（支付状态ID，外键REFERENCESpay_status(ps_id)）、ls_id（物流状态ID，外键REFERENCESlogistics_status(ls_id)）。表创建语句示例：```sqlCREATETABLEpay_status(ps_idTINYINTPRIMARYKEY,ps_nameVARCHAR(20)UNIQUENOTNULL);CREATETABLElogistics_status(ls_idTINYINTPRIMARYKEY,ls_nameVARCHAR(20)UNIQUENOTNULL);CREATETABLE`order`(order_idVARCHAR(32)PRIMARYKEY,user_idINTNOTNULL,order_timeDATETIMENOTNULL,total_amountDECIMAL(10,2)NOTNULL,ps_idTINYINTNOTNULL,ls_idTINYINTNOTNULL,FOREIGNKEY(user_id)REFERENCESuser(user_id),FOREIGNKEY(ps_id)REFERENCESpay_status(ps_id),FOREIGNKEY(ls_id)REFERENCESlogistics_status(ls_id));```四、事务与索引12.模拟用户A向用户B转账500元的事务操作，要求：用户表（user）包含user_id（主键）、balance（余额，DECIMAL(10,2)）；需检查转出用户余额是否足够；若余额不足，回滚并返回错误信息；若余额足够，扣除转出用户余额，增加转入用户余额，提交事务。答案：```sqlDELIMITER$$CREATEPROCEDUREtransfer(INfrom_userINT,INto_userINT,INamountDECIMAL(10,2))BEGINDECLAREEXITHANDLERFORSQLEXCEPTIONBEGINROLLBACK;SELECT'交易失败：发生异常'ASresult;END;STARTTRANSACTION;-检查转出用户余额IF(SELECTbalanceFROMuserWHEREuser_id=from_user)<amountTHENROLLBACK;SELECT'交易失败：余额不足'ASresult;RETURN;ENDIF;-扣除转出用户余额UPDATEuserSETbalance=balanceamountWHEREuser_id=from_user;-增加转入用户余额UPDATEuserSETbalance=balance+amountWHEREuser_id=to_user;COMMIT;SELECT'交易成功'ASresult;END$$DELIMITER;```13.某电商订单表（order）有1000万条数据，常见查询条件为：根据user_id查询近30天的订单（order_time>=当前时间-30天）；根据order_id精确查询订单详情。请为该表设计索引，并说明原因。答案：为order_id字段创建主键索引（已默认存在），因order_id是主键且需精确查询，主键索引可快速定位单条记录。为(user_id,order_time)创建复合索引。原因：查询常按user_id筛选近30天订单，复合索引可先按user_id过滤，再按order_time范围查询，减少扫描行数；索引顺序符合最左匹配原则，user_id在前可快速定位用户，order_time在后支持范围查询。五、高级应用14.创建一个存储过程，输入学生ID和学期（如“2024-2025-1”），输出该学生本学期所有课程的成绩及GPA（GPA计算：Σ(课程学分×成绩/100×4)/Σ课程学分，保留2位小数）。答案：```sqlDELIMITER$$CREATEPROCEDUREcalc_gpa(INstu_idINT,INsemesterVARCHAR(20))BEGINDECLAREtotal_creditDECIMAL(10,2);DECLAREtotal_gpa_pointsDECIMAL(10,2);-计算总学分和GPA积分SELECTSUM(c.credit),SUM(c