2025年二级MySQL实操考试试题及答案

2025年二级MySQL实操考试试题及答案考试环境：MySQL8.0.36版本，操作系统为CentOS7.9，考试时长90分钟，总分100分，所有操作均需符合SQL规范及MySQL8.0语法要求。第一部分基础操作题（共20分）1.（10分）完成以下操作：（1）创建名为edu_course的数据库，要求字符集为utf8mb4，排序规则为utf8mb4_0900_ai_ci；（2）创建本地登录用户'test_op'@'localhost'，登录密码为MySQL@2025_op，密码有效期为180天；（3）为该用户授予edu_course数据库下所有表的CREATE、ALTER、INSERT、UPDATE、DELETE、SELECT权限，且允许该用户将自身权限授予其他用户。2.（6分）现有student表初始结构如下：```sqlCREATETABLEstudent(stu_idchar(10)PRIMARYKEY,stu_namevarchar(20)NOTNULL,stu_ageint,old_remarkvarchar(255))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;```要求对该表做结构修改：（1）新增stu_phonechar(11)字段，要求非空、唯一约束，备注为学生手机号；（2）新增remarktext字段，允许为空，备注为学生备注信息；（3）删除old_remark字段；（4）将stu_age字段类型修改为tinyintunsigned，新增检查约束要求stu_age取值范围为14~35。3.（4分）写出如下操作的命令：（1）使用mysqldump工具对edu_course数据库做逻辑备份，备份文件存储路径为/backup/edu_course_202506.sql，要求备份时包含存储过程、触发器、事件；（2）将上述备份文件恢复至新建的edu_course_bak数据库中。【参考答案及评分标准】1.（10分，每步3分、3分、4分）注意：MySQL8.0已废弃GRANT语句隐式创建用户的语法，需先创建用户再授权，否则判定语法错误。（1）创建数据库SQL：```sqlCREATEDATABASEIFNOTEXISTSedu_courseDEFAULTCHARACTERSETutf8mb4DEFAULTCOLLATEutf8mb4_0900_ai_ci;```（2）创建用户SQL：```sqlCREATEUSERIFNOTEXISTS'test_op'@'localhost'IDENTIFIEDBY'MySQL@2025_op'PASSWORDEXPIREINTERVAL180DAY;```（3）授权SQL：```sqlGRANTCREATE,ALTER,INSERT,UPDATE,DELETE,SELECTONedu_course.*TO'test_op'@'localhost'WITHGRANTOPTION;FLUSHPRIVILEGES;```2.（6分，每步1.5分）所有修改表结构操作均使用ALTERTABLE语句，符合MySQL8.0CHECK约束语法要求：（1）新增stu_phone字段：```sqlALTERTABLEstudentADDCOLUMNstu_phonechar(11)NOTNULLUNIQUECOMMENT'学生手机号';```（2）新增remark字段：```sqlALTERTABLEstudentADDCOLUMNremarktextCOMMENT'学生备注信息';```（3）删除old_remark字段：```sqlALTERTABLEstudentDROPCOLUMNold_remark;```（4）修改stu_age字段并添加检查约束：```sqlALTERTABLEstudentMODIFYCOLUMNstu_agetinyintunsignedCHECK(stu_ageBETWEEN14AND35);```3.（4分，每步2分）注意mysqldump参数的含义，-R表示备份存储过程和函数，-E表示备份事件，--triggers表示备份触发器（默认开启，显式添加更严谨）：（1）备份命令（命令行执行，无需进入MySQL客户端）：```bashmysqldump-uroot-p-R-E--triggersedu_course>/backup/edu_course_202506.sql```执行后输入root用户密码即可完成备份。（2）恢复命令：首先登录MySQL创建edu_course_bak数据库：`CREATEDATABASEedu_course_bakDEFAULTCHARSET=utf8mb4;`退出客户端后执行恢复命令：```bashmysql-uroot-pedu_course_bak</backup/edu_course_202506.sql```输入root用户密码即可完成恢复。第二部分SQL查询题（共30分）答题前已知3张业务表结构如下：```sql-学生表CREATETABLEstudent(stu_idchar(10)PRIMARYKEYCOMMENT'学号',stu_namevarchar(20)NOTNULLCOMMENT'姓名',stu_majorvarchar(50)NOTNULLCOMMENT'专业',enroll_datedateNOTNULLCOMMENT'入学日期',stu_agetinyintunsignedCHECK(stu_ageBETWEEN14AND35)COMMENT'年龄')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;-课程表CREATETABLEcourse(course_idchar(6)PRIMARYKEYCOMMENT'课程编号',course_namevarchar(50)NOTNULLCOMMENT'课程名',credittinyintunsignedNOTNULLCOMMENT'学分',teachervarchar(20)NOTNULLCOMMENT'授课教师',max_stutinyintunsignedNOTNULLDEFAULT50COMMENT'最大选课人数',current_stutinyintunsignedNOTNULLDEFAULT0COMMENT'当前选课人数')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;-成绩表CREATETABLEscore(stu_idchar(10)NOTNULLCOMMENT'学号',course_idchar(6)NOTNULLCOMMENT'课程编号',exam_scoredecimal(4,1)COMMENT'考试成绩，未考为NULL',exam_timedatetimeCOMMENT'考试时间',PRIMARYKEY(stu_id,course_id),FOREIGNKEY(stu_id)REFERENCESstudent(stu_id)ONDELETECASCADEONUPDATECASCADE,FOREIGNKEY(course_id)REFERENCEScourse(course_id)ONDELETECASCADEONUPDATECASCADE)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;```按要求编写SQL查询语句，每道题6分，共30分：1.查询2023级（入学日期>='2023-09-01'）计算机科学与技术专业的学生学号、姓名、年龄，查询结果按年龄降序排列，年龄相同则按学号升序排列。2.查询每门课程的课程名、选课人数、平均分、最高分、最低分，过滤掉选课人数不足5人的课程，结果按平均分降序排列，平均分相同按选课人数升序排列。3.查询所有选了“数据库原理”课程的学生的姓名、专业、考试成绩，以及该学生所在专业所有学生该门课程的平均分，结果按考试成绩降序排列。4.查询至少选修了3门课程，且所有选修课程成绩均>=70分的学生的姓名、总学分（仅成绩>=60分的课程计入总学分）、平均成绩，结果按总学分降序排列，总学分相同按平均成绩降序排列。5.使用窗口函数查询每个专业的学生“数据库原理”课程的成绩排名，要求排名相同不跳号，输出字段为专业、学生姓名、成绩、专业内排名，结果按专业升序、排名升序排列。【参考答案及评分标准】1.（6分，过滤条件正确2分，字段正确2分，排序正确2分）注意WHERE条件的先后顺序，匹配索引优化原则：```sqlSELECTstu_id,stu_name,stu_ageFROMstudentWHEREstu_major='计算机科学与技术'ANDenroll_date>='2023-09-01'ORDERBYstu_ageDESC,stu_idASC;```2.（6分，多表连接正确1分，聚合函数正确2分，HAVING过滤正确2分，排序正确1分）注意：MySQL8.0默认开启ONLY_FULL_GROUP_BY模式，非聚合字段必须出现在GROUPBY子句中，此处course_name唯一对应course_id，因此GROUPBYcourse_id、course_name符合规范：```sqlSELECTc.course_name,COUNT(s.stu_id)AS选课人数,AVG(s.exam_score)AS平均分,MAX(s.exam_score)AS最高分,MIN(s.exam_score)AS最低分FROMcoursecINNERJOINscoresONc.course_id=s.course_idGROUPBYc.course_id,c.course_nameHAVINGCOUNT(s.stu_id)>=5ORDERBY平均分DESC,选课人数ASC;```3.（6分，多表连接正确2分，子查询计算专业平均分正确2分，字段输出正确2分）可使用关联子查询或CTE实现，此处采用CTE写法可读性更高：```sqlWITHmajor_avgAS(SELECTst.stu_major,AVG(sc.exam_score)ASmajor_course_avgFROMstudentstINNERJOINscorescONst.stu_id=sc.stu_idINNERJOINcoursecoONsc.course_id=co.course_idWHEREco.course_name='数据库原理'GROUPBYst.stu_major)SELECTst.stu_name,st.stu_major,sc.exam_score,ma.major_course_avgAS专业该课程平均分FROMstudentstINNERJOINscorescONst.stu_id=sc.stu_idINNERJOINcoursecoONsc.course_id=co.course_idINNERJOINmajor_avgmaONst.stu_major=ma.stu_majorWHEREco.course_name='数据库原理'ORDERBYsc.exam_scoreDESC;```4.（6分，选课数量过滤正确2分，所有成绩>=70的条件正确2分，总学分计算正确2分）使用MIN聚合函数过滤所有成绩符合要求的学生，比ALL子查询效率更高：```sqlSELECTst.stu_name,SUM(IF(sc.exam_score>=60,c.credit,0))AS总学分,AVG(sc.exam_score)AS平均成绩FROMstudentstINNERJOINscorescONst.stu_id=sc.stu_idINNERJOINcoursecONsc.course_id=c.course_idGROUPBYst.stu_id,st.stu_nameHAVINGCOUNT(sc.course_id)>=3ANDMIN(sc.exam_score)>=70ORDERBY总学分DESC,平均成绩DESC;```5.（6分，窗口函数选择正确2分，分区排序逻辑正确2分，结果排序正确2分）要求排名相同不跳号，需使用DENSE_RANK()窗口函数，而非RANK()或ROW_NUMBER()：```sqlSELECTst.stu_majorAS专业,st.stu_nameAS姓名,sc.exam_scoreAS成绩,DENSE_RANK()OVER(PARTITIONBYst.stu_majorORDERBYsc.exam_scoreDESC)AS专业内排名FROMstudentstINNERJOINscorescONst.stu_id=sc.stu_idINNERJOINcoursecoONsc.course_id=co.course_idWHEREco.course_name='数据库原理'ORDERBY专业ASC,专业内排名ASC;```第三部分数据库设计与业务实现题（共25分）某社区图书借阅系统需搭建底层数据库，需求如下：1.读者信息管理：每个读者有唯一的读者编号，需存储姓名、手机号（唯一）、注册时间、读者类型（仅允许取值为学生/教师/社会读者三类），不同类型读者的最大可借阅图书数量不同：学生最多借10本，教师最多借20本，社会读者最多借5本；2.图书信息管理：每本图书有唯一的ISBN编号，需存储书名、作者、出版社、出版日期、馆藏总数量、当前可借数量；3.借阅记录管理：每条记录对应一个读者借阅一本图书，需存储借阅时间、应还时间（借阅时间+30天）、实际归还时间（未还为NULL）、逾期罚金（每逾期1天扣除0.5元，不足1天按1天计算，未逾期则为0）。要求完成以下三个任务：1.（12分）设计符合第三范式的3张表，写出建表SQL语句，要求明确所有字段的类型、约束（主键、外键、非空、唯一、检查约束、备注），存储引擎为InnoDB，字符集为utf8mb4；2.（8分）编写存储过程实现读者还书逻辑，输入参数为读者编号、ISBN编号，逻辑要求：首先校验该读者是否确实借阅了该图书且未归还，校验通过后自动计算逾期罚金，更新对应图书的可借数量+1，更新借阅记录的实际归还时间和逾期罚金字段，若校验不通过则返回错误提示；3.（5分）编写SQL查询当前所有逾期未还的读者姓名、图书名、借阅时间、应还时间、逾期天数、应付罚金，结果按逾期天数降序排列。【参考答案及评分标准】1.（12分，每张表4分，约束完整2分，字段设计合理1分，符合第三范式1分）设计说明：三张表分别为reader（读者表）、book（图书表）、borrow_record（借阅记录表），避免冗余字段，依赖主键，符合第三范式要求：```sql-读者表CREATETABLEreader(reader_idchar(8)PRIMARYKEYCOMMENT'读者编号',reader_namevarchar(20)NOTNULLCOMMENT'读者姓名',phonechar(11)NOTNULLUNIQUECOMMENT'手机号',reg_timedatetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'注册时间',reader_typeenum('学生','教师','社会读者')NOTNULLCOMMENT'读者类型',max_borrowtinyintunsignedNOTNULLCOMMENT'最大可借数量',CHECK((reader_type='学生'ANDmax_borrow=10)OR(reader_type='教师'ANDmax_borrow=20)OR(reader_type='社会读者'ANDmax_borrow=5))COMMENT'约束读者类型与最大可借数量匹配')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT'读者表';-图书表CREATETABLEbook(isbnchar(13)PRIMARYKEYCOMMENT'图书ISBN编号',book_namevarchar(100)NOTNULLCOMMENT'书名',authorvarchar(50)NOTNULLCOMMENT'作者',pressvarchar(50)NOTNULLCOMMENT'出版社',publish_datedateNOTNULLCOMMENT'出版日期',total_countintunsignedNOTNULLCOMMENT'馆藏总数量',available_countintunsignedNOTNULLCOMMENT'当前可借数量',CHECK(available_count<=total_countANDavailable_count>=0)COMMENT'约束可借数量取值范围')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT'图书表';-借阅记录表CREATETABLEborrow_record(record_idintunsignedAUTO_INCREMENTPRIMARYKEYCOMMENT'借阅记录ID',reader_idchar(8)NOTNULLCOMMENT'读者编号',isbnchar(13)NOTNULLCOMMENT'图书ISBN',borrow_timedatetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'借阅时间',due_timedatetimeGENERATEDALWAYSAS(DATE_ADD(borrow_time,INTERVAL30DAY))STOREDCOMMENT'应还时间，自动计算',return_timedatetimeCOMMENT'实际归还时间，未还为NULL',finedecimal(5,2)DEFAULT0COMMENT'逾期罚金',FOREIGNKEY(reader_id)REFERENCESreader(reader_id)ONDELETERESTRICTONUPDATECASCADE,FOREIGNKEY(isbn)REFERENCESbook(isbn)ONDELETERESTRICTONUPDATECASCADE,UNIQUEKEYuniq_reader_isbn(reader_id,isbn,return_time)COMMENT'约束同一读者同一时间不能重复借阅同一本未还的图书')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT'借阅记录表';```2.（8分，参数定义正确2分，校验逻辑正确2分，罚金计算正确2分，数据更新正确2分）存储过程采用事务封装，保证操作原子性：```sqlDELIMITER//CREATEPROCEDUREbook_return(INp_reader_idchar(8),INp_isbnchar(13),OUTp_msgvarchar(255))BEGINDECLAREv_record_idint;DECLAREv_overdue_daysint;STARTTRANSACTION;-校验借阅记录是否存在，加行锁避免并发修改SELECTrecord_idINTOv_record_idFROMborrow_recordWHEREreader_id=p_reader_idANDisbn=p_isbnANDreturn_timeISNULLFORUPDATE;IFv_record_idISNULLTHENSETp_msg='错误：该读者未借阅该图书或已归还';ROLLBACK;ELSE-计算逾期天数，最小为0SETv_overdue_days=GREATEST(0,DATEDIFF(NOW(),(SELECTdue_timeFROMborrow_recordWHERErecord_id=v_record_id)));-更新借阅记录UPDATEborrow_recordSETreturn_time=NOW(),fine=v_overdue_days*0.5WHERErecord_id=v_record_id;-更新图书可借数量UPDATEbookSETavailable_count=available_count+1WHEREisbn=p_isbn;COMMIT;SETp_msg=CONCAT('还书成功，逾期',v_overdue_days,'天，罚金',v_overdue_days*0.5,'元');ENDIF;END//DELIMITER;```3.（5分，过滤条件正确2分，字段计算正确2分，排序正确1分）```sqlSELECTr.reader_name,b.book_name,br.borrow_time,br.due_time,DATEDIFF(NOW(),br.due_time)AS逾期天数,DATEDIFF(NOW(),br.due_time)*0.5AS应付罚金FROMborrow_recordbrINNERJOINreaderrONbr.reader_id=r.reader_idINNERJOINbookbONbr.isbn=b.isbnWHEREbr.return_timeISNULLANDbr.due_time<NOW()ORDERBY逾期天数DESC;```第四部分高级特性与性能优化题（共25分）1.（8分）现有score表数据量为120万条，业务侧高频执行的查询语句为：`SELECTstu_id,exam_scoreFROMscoreWHEREcourse_id='C001'ANDexam_score>=80;`目前该查询平均耗时1.2秒，要求给出优化方案，写出对应的SQL操作语句，并说明优化原理。2.（8分）编写事务实现学生选课逻辑：输入参数为学号p_stu_id、课程编号p_course_id，逻辑要求：首先查询该课程当前选课人数是否已达最大选课人数，若未达上限则向score表插入选课记录（exam_score为NULL），同时更新course表的current_stu字段+1，若已达上限则返回选课失败。要求事务满足ACID特性，避免并发场景下出现超选、脏读、幻读问题，写出对应的SQL代码并说明事务相关设置的原因。3.（9分）现有慢查询日志捕获到如下慢SQL，平均执行耗时2.8秒，涉及表数据量：student表12万条，score表320万条，course表1200条：```sqlSELECTs.stu_name,c.course_name,sc.exam_scoreFROMstudentsLEFTJOINscorescONs.stu_id=sc.stu_idLEFTJOINcoursecONsc.course_id=c.course_idWHEREs.stu_major='会计学'ANDsc.exam_time>='2024-01-01';```分析该SQL执行慢的可能原因，给出至少3种可行的优化方案，写出对应的SQL操作语句并说明原理。【参考答案及评分标准】1.（8分，优化方案正确3分，SQL语句正确2分，原理说明正确3分）优化方案：为score表创建联合覆盖索引，避免回表查询。操作SQL：```sqlCREATEINDEXidx_course_score_stuONscore(course_id,exam_score,stu_id);```优化原理：（1）符合最左前缀匹配原则，索引首字段为查询过滤条件course_id='C001'，可快速定位到该课程的所有成绩记录；（2）第二个索引字段为exam_score，可直接在索引层面过滤掉exam_score<80的记录，无需回表；（3）索引包含查询所需的所有字段stu_id、exam_score、course_id，属于覆盖索引，查询时直接从索引读取数据，无需访问主键索引的聚簇索引页，IO开销降低90%以上，优化后查询耗时可降至0.05秒以内。2.（8分，事务隔离级别设置正确2分，锁逻辑正确2分，业务逻辑正确2分，原理说明正确2分）实现代码：```sql-设置事务隔离级别为REPEATABLEREAD（MySQL默认级别，可避免脏读、不可重复读，结合间隙锁避免幻读）SETTRANSACTIONISOLATIONLEVELREPEATABLEREAD;DELIMITER//CREATEPROCEDUREselect_course(INp_stu_idchar(10),INp_course_idchar(6),OUTp_msgvarchar(255))BEGINDECLAREv_currentint;DECLAREv_maxint;STARTTRANSACTION;-对课程记录加排他行锁，阻塞其他事务对该记录的修改，避免并发超选SELECTcurrent_stu,max_stuINTOv_current,v_maxFROMcourseWHEREcourse_id=p_course_idFORUPDATE;IFv_current>=v_maxTHENSETp_msg='选课失败：课程选课人数已满';ROLLBACK;ELSE-插入选课记录INSERTINTOscore(stu_id,course_id,exam_score)VALUES(p_stu_id,p_course_id,NULL);-更新选课人数UPDATEcourseSETcurrent_stu=current_stu+1WHEREcourse_id=p_course_id;COMMIT;SETp_msg='选课成功';ENDIF;END//DELIMITER;```原理说明：（1）REPEATABLEREAD隔离级别保证事务执行过程中读取的数据一致，避免脏读、不可重复读；（2）FORUPDATE对查询的course记录加排他锁，其他事务需等待当前事务提交后才能修改该记录，避免并发场景下多个事务同时读取到相同的current_stu值，导致超选；（3）InnoDB的间隙锁可避免幻读，保证不会有其他事务在当前事务执行过程中插入同一条选课记录。3.（9分，原因分析3分，每个优化方案2分，共3个方案）慢查询原因分析：（1）没有合适的索引，查询时触发全表扫描：st