2025年MySQL数据库应用微课版危光辉习题答案

2025年MySQL数据库应用微课版危光辉习题答案1.数据库与表的基本操作问题：假设某高校需构建学生信息管理系统，要求创建名为"student_manage"的数据库（若不存在则创建），并在其中创建"students"表。表结构要求如下：学号（s_id，主键，长度10的定长字符串）、姓名（s_name，长度20的可变字符串，非空）、性别（s_sex，长度2的可变字符串，默认值'男'）、出生日期（s_birth，日期类型）、入学时间（enroll_date，日期时间类型）、专业编号（major_id，长度6的定长字符串，外键关联至"majors"表的m_id字段）。请写出对应的创建数据库和表的SQL语句，并说明各约束的作用。解答：创建数据库语句：`CREATEDATABASEIFNOTEXISTSstudent_manageDEFAULTCHARACTERSETutf8mb4DEFAULTCOLLATEutf8mb4_unicode_ci;`使用`IFNOTEXISTS`避免数据库已存在时的报错；指定字符集为`utf8mb4`支持四字节字符（如emoji），校对规则`utf8mb4_unicode_ci`用于字符串比较。创建表语句：```sqlUSEstudent_manage;CREATETABLEstudents(s_idCHAR(10)PRIMARYKEYCOMMENT'学号（主键）',s_nameVARCHAR(20)NOTNULLCOMMENT'姓名（非空）',s_sexVARCHAR(2)DEFAULT'男'COMMENT'性别（默认值男）',s_birthDATECOMMENT'出生日期',enroll_dateDATETIMECOMMENT'入学时间',major_idCHAR(6)COMMENT'专业编号',FOREIGNKEY(major_id)REFERENCESmajors(m_id)ONDELETESETNULLONUPDATECASCADE)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT'学生信息表';```约束说明：`PRIMARYKEY`：通过`s_id`唯一标识学生记录，保证实体完整性；`NOTNULL`：强制`s_name`字段必须填写，避免无效姓名数据；`DEFAULT'男'`：若插入时未指定性别，自动填充为'男'，减少数据录入工作量；`FOREIGNKEY`：`major_id`与`majors`表的`m_id`关联，保证参照完整性；`ONDELETESETNULL`表示当关联的专业被删除时，学生专业编号置为NULL（需表允许NULL）；`ONUPDATECASCADE`表示专业编号更新时，学生表中对应记录自动同步更新。2.数据插入与修改操作问题：向"students"表插入以下记录（需处理可能的约束冲突）：('S20250001','张小明','男','2005-03-15','2025-09-0108:00:00','MJ0001'),('S20250002','李雨欣',NULL,'2006-07-20','2025-09-0108:30:00','MJ0002'),('S20250001','王磊','男','2005-11-02','2025-09-0109:00:00','MJ0003')。随后将学号'S20250002'的学生性别修正为'女'，并将所有2005年1月1日以后出生的学生的入学时间提前至'2025-08-3108:00:00'。解答：插入数据时需注意主键唯一约束和默认值规则：第一条记录：主键`s_id='S20250001'`无冲突，`s_sex`明确指定为'男'，正常插入；第二条记录：`s_sex`为NULL，但表定义中`s_sex`无`NOTNULL`约束，且默认值仅在未指定时生效，因此允许插入（最终`s_sex`为NULL）；第三条记录：`s_id='S20250001'`与第一条主键重复，触发主键唯一约束，插入失败（需删除或修改该`s_id`后重新插入）。插入语句（排除冲突记录）：```sqlINSERTINTOstudents(s_id,s_name,s_sex,s_birth,enroll_date,major_id)VALUES('S20250001','张小明','男','2005-03-15','2025-09-0108:00:00','MJ0001'),('S20250002','李雨欣',NULL,'2006-07-20','2025-09-0108:30:00','MJ0002');```修正性别语句：`UPDATEstudentsSETs_sex='女'WHEREs_id='S20250002';`调整入学时间语句（使用日期函数`STR_TO_DATE`或直接比较）：`UPDATEstudentsSETenroll_date='2025-08-3108:00:00'WHEREs_birth>'2005-01-01';`3.单表与多表查询问题：（1）查询所有2005年出生的男生信息，按入学时间从早到晚排序；（2）统计各专业的学生人数（显示专业编号、人数，过滤人数小于2的专业）；（3）查询姓名包含"雨"字的学生及其所属专业名称（假设"majors"表包含m_id（专业编号）和m_name（专业名称）字段）。解答：（1）单表查询：```sqlSELECTFROMstudentsWHEREYEAR(s_birth)=2005ANDs_sex='男'ORDERBYenroll_dateASC;````YEAR(s_birth)=2005`筛选2005年出生；`ORDERBYenroll_dateASC`按入学时间升序排列。（2）分组统计：```sqlSELECTmajor_id,COUNT()ASstudent_countFROMstudentsGROUPBYmajor_idHAVINGstudent_count>=2;````GROUPBYmajor_id`按专业分组；`HAVING`过滤分组后人数≥2的专业（需注意若表中某专业人数不足2则不显示）。（3）多表连接查询（内连接）：```sqlSELECTs.s_id,s.s_name,m.m_nameFROMstudentssINNERJOINmajorsmONs.major_id=m.m_idWHEREs.s_nameLIKE'%雨%';````INNERJOIN`确保仅返回两表关联成功的记录；`LIKE'%雨%'`匹配姓名中包含"雨"的学生；`s`和`m`为表别名，简化语句。4.索引与查询优化问题：某系统中"orders"表（包含order_id（主键）、user_id、order_time、amount）数据量达1000万条，常见查询为`SELECTFROMordersWHEREuser_id=12345ORDERBYorder_timeDESCLIMIT20;`。当前查询响应时间为2秒，需优化。请分析可能的原因并给出优化方案，写出创建索引的SQL语句。解答：慢查询原因分析：未对`user_id`和`order_time`建立索引，导致全表扫描；排序`ORDERBYorder_time`需额外的临时文件或内存排序，无索引时复杂度高。优化方案：创建复合索引，包含`user_id`（过滤条件）和`order_time`（排序字段），利用索引的有序性避免全表扫描和额外排序。创建索引语句：`CREATEINDEXidx_user_timeONorders(user_id,order_timeDESC);`原理说明：索引按`user_id`分组，每组内`order_time`降序排列，查询时可快速定位`user_id=12345`的所有记录，并直接按`order_time`取前20条，无需回表和排序；`DESC`显式指定索引排序方向，与查询中的`ORDERBYorder_timeDESC`匹配，进一步提升效率。5.事务与并发控制问题：模拟银行转账场景，账户表"accounts"包含acc_id（主键）、balance（余额）。要求从acc_id='A001'向acc_id='A002'转账500元，需保证事务的原子性。写出事务控制的SQL语句，并说明如何处理可能的死锁。解答：事务控制语句（以InnoDB引擎为例）：```sqlSTARTTRANSACTION;-检查转出账户余额是否足够SELECTbalanceFROMaccountsWHEREacc_id='A001'FORUPDATE;-若余额≥500则扣款UPDATEaccountsSETbalance=balance-500WHEREacc_id='A001';-转入账户收款UPDATEaccountsSETbalance=balance+500WHEREacc_id='A002';COMMIT;```关键步骤说明：`STARTTRANSACTION`开启事务；`SELECT...FORUPDATE`对转出账户加行锁，防止其他事务同时修改，避免超支；若任一UPDATE失败（如余额不足），需执行`ROLLBACK`回滚所有操作，保证原子性；`COMMIT`提交事务，完成转账。死锁处理：缩短事务持有锁的时间，避免长时间查询或不必要的锁；对账户操作按固定顺序（如按acc_id升序）访问，避免循环等待；设置`innodb_lock_wait_timeout`（默认50秒）控制锁等待超时，超时后自动回滚事务；使用`SHOWENGINEINNODBSTATUS`查看死锁日志，分析原因并优化业务逻辑。6.视图与触发器问题：（1）创建视图"v_student_major"，显示学生学号、姓名、专业名称；（2）创建触发器"tri_check_score"，当向"scores"表（包含s_id、course_id、score）插入成绩时，若分数小于0或大于100，则自动将分数修正为0或100。解答：（1）视图创建：```sqlCREATEVIEWv_student_majorASSELECTs.s_id,s.s_name,m.m_nameFROMstudentssLEFTJOINmajorsmONs.major_id=m.m_id;````LEFTJOIN`确保无专业信息的学生也能显示（m_name为NULL），视图封装了多表连接逻辑，简化后续查询。（2）触发器创建（BEFOREINSERT）：```sqlDELIMITER$$CREATETRIGGERtri_check_scoreBEFOREINSERTONscoresFOREACHROWBEGINIFNEW.score<0THENSETNEW.score=0;ELSEIFNEW.score>100THENSETNEW.score=100;ENDIF;END$$DELIMITER;````BEFOREINSERT`在插入前触发，`NEW.score`表示待插入的分数值；通过条件判断修正分数范围，保证数据有效性。7.存储过程与函数问题：创建存储过程"proc_avg_score"，输入学生学号，输出该学生所有课程的平均成绩（保留2位小数）。若学生无成绩，返回'无成绩记录'。解答：```sqlDELIMITER$$CREATEPROCEDUREproc_avg_score(INp_s_idCHAR(10),OUTp_avgDECIMAL(5,2))BEGINDECLAREv_countINT;SELECTCOUNT()INTOv_countFROMscoresWHEREs_id=p_s_id;IFv_count=0THENSELECT'无成绩记录'ASresult;ELSESELECTROUND(AVG(score),2)INTOp_avgFROMscoresWHEREs_id=p_s_id;SELECTp_avgASavg_score;ENDIF;END$$DELIMITER;```调用示例：`CALLproc_avg_score('S20250001',@avg);``SELECT@avg;`说明：`INp_s_id`为输入参数（学生学号），`OUTp_avg`为输出参数（平均成绩）；先统计该学提供绩数量，若为0则返回提示信息；否则计算平均分并四舍五入到2位小数；使用`DECLARE`声明局部变量`v_count`存储成绩数量，控制流程走向。8.数据库备份与恢复问题：某系统需每天凌晨2点对"student_manage"数据库进行全量备份，每周日凌晨2点进行增量备份。写出使用`mysqldump`进行全量备份的命令，并说明增量备份的实现方法（假设已开启二进制日志）。解答：全量备份命令（Linux环境）：`mysqldump-uroot-p--databasesstudent_manage--single-transaction--flush-logs--master-data=2>/backup/full_backup_$(date+%Y%m%d).sql`参数说明：`-uroot-p`：指定用户并提示输入密码；`--databasesstudent_manage