2025年mysql上机考试题及答案

2025年mysql上机考试题及答案考试环境说明：本次考试基于CentOS7.964位操作系统、MySQL8.0.36社区版，默认关闭sql_mode的ONLY_FULL_GROUP_BY规则，root用户初始密码为Test@2025，所有操作需在test数据库下完成，考试时长120分钟，总分100分。一、基础操作题（共20分，每题4分）1.题干：创建用户dev_user，允许其从/24网段登录，密码设置为Dev@2025，授予该用户对test库所有表的查询、插入、修改权限，且允许该用户将自身拥有的权限授予其他用户。参考答案：-创建用户（MySQL8.0需分开执行创建用户和授权操作，不可合并）CREATEUSER'dev_user'@'192.168.1.%'IDENTIFIEDBY'Dev@2025';-授权并开启权限转授GRANTSELECT,INSERT,UPDATEONtest.*TO'dev_user'@'192.168.1.%'WITHGRANTOPTION;-刷新权限（可选，MySQL8.0授权后自动生效，低版本需执行）FLUSHPRIVILEGES;-权限验证SHOWGRANTSFOR'dev_user'@'192.168.1.%';评分要点：用户网段配置正确得1分，密码符合安全规则得1分，权限配置正确得1分，WITHGRANTOPTION配置正确得1分。注意事项：禁止使用MySQL5.7版本的GRANT...IDENTIFIEDBY合并写法，8.0版本下该语法已废弃。2.题干：创建两张业务表，员工表emp字段要求：emp_idINT类型主键自增，emp_nameVARCHAR(32)非空，dept_idINT，hire_dateDATE非空，salaryDECIMAL(10,2)非空，phoneCHAR(11)添加唯一约束；部门表dept字段要求：dept_idINT类型主键自增，dept_nameVARCHAR(32)非空且添加唯一约束，locationVARCHAR(64)默认值为'北京总部'；两张表添加外键约束，emp的dept_id关联dept的dept_id，删除部门时若部门下存在员工则禁止删除。参考答案：-创建部门表CREATETABLEdept(dept_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'部门ID',dept_nameVARCHAR(32)NOTNULLUNIQUECOMMENT'部门名称',locationVARCHAR(64)DEFAULT'北京总部'COMMENT'办公地点')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT'部门表';-创建员工表CREATETABLEemp(emp_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'员工ID',emp_nameVARCHAR(32)NOTNULLCOMMENT'员工姓名',dept_idINTCOMMENT'所属部门ID',hire_dateDATENOTNULLCOMMENT'入职日期',salaryDECIMAL(10,2)NOTNULLCOMMENT'薪资',phoneCHAR(11)UNIQUECOMMENT'手机号',FOREIGNKEY(dept_id)REFERENCESdept(dept_id)ONDELETERESTRICTONUPDATECASCADE)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT'员工表';评分要点：字段类型、约束配置正确得2分，外键约束的ONDELETERESTRICT配置正确得1分，存储引擎、字符集配置符合规范得1分。注意事项：MySQL8.0原生支持CHECK约束，若需额外限制字段取值可直接添加CHECK规则，无需使用触发器模拟。3.题干：向dept表插入3条测试数据：dept_id=10研发部北京总部；dept_id=20市场部上海分公司；dept_id=30人事部广州分公司；向emp表插入5条测试数据，要求至少包含2名研发部员工、2名市场部员工、1名人事部员工，薪资范围4000-30000，入职日期覆盖2022-2024年。参考答案：-插入部门数据INSERTINTOdept(dept_id,dept_name,location)VALUES(10,'研发部','北京总部'),(20,'市场部','上海分公司'),(30,'人事部','广州分公司');-插入员工数据INSERTINTOemp(emp_name,dept_id,hire_date,salary,phone)VALUES('张三',10,'2022-03-15',22000.00,),('李四',10,'2023-05-20',18000.00,),('王五',20,'2022-09-10',12000.00,),('赵六',20,'2024-01-05',8500.00,),('孙七',30,'2023-11-30',7000.00,);评分要点：部门数据插入正确得1分，员工数据符合部门数量、薪资、入职日期要求得2分，手机号唯一约束无冲突得1分。4.题干：修改emp表结构，新增字段levelTINYINT默认值为1，添加约束限制level取值范围为1-5；为salary字段创建普通索引，为emp_name和hire_date创建联合索引。参考答案：-新增level字段并添加约束ALTERTABLEempADDCOLUMNlevelTINYINTDEFAULT1CHECK(levelBETWEEN1AND5);-创建普通索引CREATEINDEXidx_emp_salaryONemp(salary);-创建联合索引CREATEINDEXidx_emp_name_hiredateONemp(emp_name,hire_date);-验证索引SHOWINDEXFROMemp;评分要点：字段新增和约束配置正确得2分，索引创建规则符合最左前缀匹配原则得2分。5.题干：删除薪资低于6000的员工记录，将市场部所有员工的薪资上调10%。参考答案：-删除低薪资员工DELETEFROMempWHEREsalary<6000;-市场部薪资上调（两种写法）-子查询写法UPDATEempSETsalary=salary*1.1WHEREdept_id=(SELECTdept_idFROMdeptWHEREdept_name='市场部');-关联更新写法（效率更高，适合大数据量场景）UPDATEempeJOINdeptdONe.dept_id=d.dept_idSETe.salary=e.salary*1.1WHEREd.dept_name='市场部';评分要点：删除语句无逻辑错误得2分，更新语句条件正确且无全表更新风险得2分。二、SQL查询实操题（共35分，每题7分）1.题干：查询2023年1月1日之后入职的研发部员工姓名、入职日期、薪资，结果按薪资降序排序，薪资相同的按入职日期升序排序。参考答案：SELECTe.emp_name,e.hire_date,e.salaryFROMempeINNERJOINdeptdONe.dept_id=d.dept_idWHEREd.dept_name='研发部'ANDe.hire_date>='2023-01-01'ORDERBYe.salaryDESC,e.hire_dateASC;评分要点：表关联逻辑正确得2分，条件过滤无误得2分，排序规则符合要求得3分。2.题干：统计每个部门的部门名称、员工总人数、平均薪资、最高薪资、最低薪资，过滤掉员工人数少于2人的部门，结果按平均薪资降序排序。参考答案：SELECTd.dept_name,COUNT(e.emp_id)ASemp_count,ROUND(AVG(e.salary),2)ASavg_salary,MAX(e.salary)ASmax_salary,MIN(e.salary)ASmin_salaryFROMdeptdLEFTJOINempeONd.dept_id=e.dept_idGROUPBYd.dept_id,d.dept_nameHAVINGemp_count>=2ORDERBYavg_salaryDESC;评分要点：LEFTJOIN关联正确避免部门遗漏得2分，聚合函数使用无误得2分，HAVING过滤分组结果而非WHERE过滤得3分。注意事项：WHERE子句无法过滤聚合结果，必须使用HAVING对GROUPBY后的分组进行筛选。3.题干：查询薪资高于所在部门平均薪资的员工姓名、部门名称、薪资、部门平均薪资，部门平均薪资保留2位小数。参考答案：-写法1：子查询实现（兼容低版本MySQL）SELECTe.emp_name,d.dept_name,e.salary,t.dept_avg_salaryFROMempeINNERJOINdeptdONe.dept_id=d.dept_idINNERJOIN(SELECTdept_id,ROUND(AVG(salary),2)ASdept_avg_salaryFROMempGROUPBYdept_id)tONe.dept_id=t.dept_idWHEREe.salary>t.dept_avg_salary;-写法2：窗口函数实现（MySQL8.0+支持，效率更高）SELECTemp_name,dept_name,salary,dept_avg_salaryFROM(SELECTe.emp_name,d.dept_name,e.salary,ROUND(AVG(e.salary)OVER(PARTITIONBYe.dept_id),2)ASdept_avg_salaryFROMempeINNERJOINdeptdONe.dept_id=d.dept_id)tempWHEREsalary>dept_avg_salary;评分要点：部门平均薪资计算逻辑正确得3分，对比逻辑无误得2分，使用窗口函数实现可额外加2分（总分不超过7分）。4.题干：查询入职日期最早的3名员工的姓名、部门名称、入职日期，若出现入职日期相同则并列显示。参考答案：SELECTemp_name,dept_name,hire_dateFROM(SELECTe.emp_name,d.dept_name,e.hire_date,RANK()OVER(ORDERBYe.hire_dateASC)ASrank_numFROMempeINNERJOINdeptdONe.dept_id=d.dept_id)tempWHERErank_num<=3;评分要点：使用RANK()窗口函数实现并列排名得4分，排序逻辑正确得3分；若使用LIMIT实现无并列排名最多得3分。5.题干：现有销售记录表sales（sale_idINT主键，emp_idINT关联emp的emp_id，sale_amountDECIMAL(10,2)，sale_dateDATE），查询每个员工2024年的销售总额，同时显示员工的姓名、所属部门，没有销售记录的员工销售总额显示为0。参考答案：SELECTe.emp_id,e.emp_name,d.dept_name,IFNULL(SUM(s.sale_amount),0)AStotal_saleFROMempeLEFTJOINsalessONe.emp_id=s.emp_idANDs.sale_dateBETWEEN'2024-01-01'AND'2024-12-31'INNERJOINdeptdONe.dept_id=d.dept_idGROUPBYe.emp_id,e.emp_name,d.dept_name;评分要点：LEFTJOIN关联sales表得2分，销售日期过滤条件写在ON子句而非WHERE子句得3分，IFNULL处理空值得2分。注意事项：若将日期过滤条件写在WHERE子句中，LEFTJOIN会隐式转为INNERJOIN，过滤掉无销售记录的员工，属于逻辑错误。三、性能优化实操题（共25分）1.题干：现有慢查询语句：SELECTemp_name,salaryFROMempWHERELEFT(emp_name,2)='张'ANDsalary>10000;执行EXPLAIN分析显示type为ALL，未命中任何索引，要求优化该语句使其命中已创建的idx_emp_name_hiredate联合索引，写出优化后的SQL和优化依据。（8分）参考答案：-优化后SQLSELECTemp_name,salaryFROMempWHEREemp_nameLIKE'张%'ANDsalary>10000;优化依据：1.原语句对索引字段emp_name使用LEFT()函数，会触发索引失效规则，数据库无法使用索引定位数据，只能全表扫描；改用前缀匹配LIKE'张%'符合联合索引的最左前缀匹配原则，可命中idx_emp_name_hiredate的首个字段emp_name，扫描范围大幅缩小。2.若要进一步优化可创建覆盖索引idx_emp_name_salary(emp_name,salary)，查询字段全部包含在索引中，无需回表查询聚簇索引数据，查询效率可提升50%以上。验证方法：执行EXPLAIN分析优化后的语句，type字段显示为range，key字段显示为idx_emp_name_hiredate，rows字段扫描行数远低于原语句的全表行数，说明优化生效。评分要点：优化后SQL语法正确得3分，优化依据逻辑清晰得3分，覆盖索引优化建议得2分。2.题干：现有一张数据量为1200万的订单表orders（order_idBIGINT主键，user_idINT，order_statusTINYINT，create_timeDATETIME，amountDECIMAL(10,2)），业务查询2024年全年已完成（order_status=1）的订单总金额时耗时12秒，要求给出至少3种优化方案，说明实现逻辑和预期性能提升效果。（10分）参考答案：方案1：创建覆盖联合索引（成本最低，见效最快）创建索引idx_status_time_amount(order_status,create_time,amount)，三个字段顺序按照过滤优先级排序：order_status为等值过滤放在第一位，create_time为范围过滤放在第二位，amount为查询字段放在最后，该索引为覆盖索引，查询时无需回表访问聚簇索引，直接从二级索引读取数据，预期查询时间可降至1秒以内。方案2：时间范围分区（适合历史数据查询场景）对orders表按照create_time字段进行范围分区，分为2022、2023、2024、2025四个分区，查询时直接扫描2024年单分区数据，扫描数据量从1200万降至300万左右，配合覆盖索引使用，预期查询时间可降至300毫秒以内。方案3：新增汇总表（适合高并发只读统计场景）新增订单统计汇总表order_stat(stat_dateDATEPRIMARYKEY,finish_order_amountDECIMAL(12,2))，通过定时任务每日凌晨统计前一日已完成订单的总金额写入汇总表，业务查询时直接读取汇总表数据，无需扫描原始订单表，预期响应时间可降至10毫秒以内。方案4：读写分离（适合整体业务压力大的场景）配置一主多从集群，将统计类只读请求转发到从库执行，避免统计查询占用主库资源影响在线业务，查询性能可提升30%以上。评分要点：每给出1种合理方案且逻辑清晰得3分，3种及以上得10分。3.题干：实操开启MySQL慢查询日志，设置慢查询阈值为1秒，记录未使用索引的查询，给出配置步骤和验证方法。（7分）参考答案：临时生效配置（重启MySQL后失效，适合调试场景）：执行SQL语句：SETGLOBALslow_query_log='ON';SETGLOBALslow_query_log_file='/var/lib/mysql/localhost-slow.log';SETGLOBALlong_query_time=1;SETGLOBALlog_queries_not_using_indexes='ON';永久生效配置（适合生产环境）：编辑/etc/f配置文件，在[mysqld]节点下添加如下配置：slow_query_log=ONslow_query_log_file=/var/lib/mysql/localhost-slow.loglong_query_time=1log_queries_not_using_indexes=ON保存后执行systemctlrestartmysqld重启服务生效。验证方法：1.执行SHOWVARIABLESLIKE'%slow_query%';查看参数配置与设置一致；2.执行SELECTSLEEP(2);等待2秒后查看慢查询日志文件，确认该语句已被记录则配置生效。评分要点：临时配置正确得3分，永久配置正确得3分，验证方法合理得1分。四、事务与锁实操题（共10分）1.题干：开启两个MySQL会话窗口，会话A开启事务更新研发部所有员工薪资加500暂不提交，会话B开启事务更新同一名研发部员工的薪资加1000，描述会出现的现象、原因，给出3种优化方案。（5分）参考答案：现象：会话B会进入阻塞状态，直到会话A提交或回滚事务，若等待时间超过innodb_lock_wait_timeout默认值50秒，会返回锁等待超时错误。原因：InnoDB引擎的行锁通过索引加载，会话A更新研发部员工时，会对符合条件的行加排他锁，事务未提交前锁不会释放，会话B更新同一行数据时需要等待排他锁释放。优化方案：1.事务尽量短小，避免长事务占用锁资源，更新操作尽量放在事务末尾；2.更新条件尽量命中主键或唯一索引，减少锁扫描范围，避免升级为表锁；3.合理调整innodb_lock_wait_timeout参数适配业务场景，高并发场景下可适当缩短超时时间减少资源占用。评分要点：现象描述正确得1分，原因说明正确得1分，每给出1种合理方案得1分。2.题干：现有库存扣减场景，库存表stock（goods_idINT主键，stock_numINT，versionINT），要求避免超卖，分别写出悲观锁和乐观锁的实现SQL并说明原理。（5分）参考答案：悲观锁实现：BEGIN;-对查询行加排他锁，其他事务无法修改该行SELECTstock_numFROMstockWHEREgoods_id=1FORUPDATE;-扣减库存UPDATEstockSETstock_num=stock_num-1WHEREgoods_id=1ANDstock_num>=1;COMMIT;原理：通过FORUPDATE在事务中对目标行加排他锁，直到事务提交才释放锁，避免多个事务同时修改同一商品库存，适合并发量中等的场景。乐观锁实现：-第一步查询库存和版本号SELECTstock_num,versionFROMstockWHEREgoods_id=1;-假设查询到stock_num>=1，version=2，执行扣减UPDATEstockSETstock_num=stock_num-1,version=version+1WHEREgoods_id=1ANDversion=2ANDstock_num>=1;-判断影响行数，若为1则扣减成功，为0则说明库存已被其他事务修改，需重试。原理：通过版本号校验实现并发控制，无需加锁，性能更高，适合并发量高的电商场景。评分要点：悲观锁实现正确得2分，乐观锁实现正确得2分，原理说明清晰得1分。五、高可用配置实操题（共10分）题干：配置MySQL一主一从同步集群，主库IP为0，从库IP为1，要求使用基于GTID的半同步复制，写出主从库的完整配置步骤和验证方法。参考答案：主库配置步骤：1.编辑主库/etc/f配置文件，在[mysqld]节点添加如下配置：server-id=1log_bin=mysql-bingtid_mode=ONenforce_gtid_consistency=ONbinlog_do_db=testplugin_load_add=rpl_semi_sync_master.sorpl_semi_sync_master_enabled=1rpl_semi_sync_master_timeout=1000保存后执行systemctlrestartmysqld重启主库。2.