二级MySQL数据库程序设计-样题及参考答案

二级MySQL数据库程序设计——样题及参考答案一、单项选择题（共20题，每题2分，共40分。每题只有1个正确选项）1.以下不属于MySQL8.0官方原生支持的存储引擎是A.MyISAMB.InnoDBC.MemoryD.MongoDB参考答案：D考点解析：MongoDB是独立的文档型数据库产品，不属于MySQL存储引擎范畴。MyISAM是MySQL早期默认存储引擎，支持表级锁、全文索引；InnoDB是当前版本默认存储引擎，支持事务、行级锁、外键；Memory是内存级存储引擎，数据存储在RAM中，适合做临时表、热点数据缓存。2.MySQL8.0版本中InnoDB存储引擎的默认事务隔离级别是A.读未提交（READUNCOMMITTED）B.读已提交（READCOMMITTED）C.可重复读（REPEATABLEREAD）D.串行化（SERIALIZABLE）参考答案：C考点解析：InnoDB默认的可重复读隔离级别通过MVCC（多版本并发控制）+间隙锁机制，在满足事务并发性能的前提下，有效解决了幻读问题，无需升级到串行化隔离级别即可保证数据一致性。3.以下关于主键约束和唯一约束的差异描述，错误的是A.一张表只能定义一个主键约束，但可以定义多个唯一约束B.主键约束对应的字段默认不允许为空，唯一约束对应的字段允许存在一个NULL值C.主键约束会自动生成聚簇索引，唯一约束默认生成非唯一二级索引D.主键约束和唯一约束都可以使用ALTERTABLE语句重复添加多次参考答案：D考点解析：主键约束具有全局唯一性，一张表重复添加主键约束会直接抛出语法错误，唯一约束也不能重复绑定到相同的字段组合上，否则会导致唯一键冲突。4.以下关于DELETE语句和TRUNCATE语句的差异描述，错误的是A.DELETE支持WHERE条件筛选删除指定行，TRUNCATE会清空整张表所有数据B.TRUNCATE会自动重置表的自增主键计数器，DELETE操作不会修改自增计数器数值C.DELETE属于DML数据操作语句，会触发表上绑定的DELETE类型触发器D.TRUNCATE属于DDL数据定义语句，执行过程不会对表加任何锁，不会阻塞业务访问参考答案：D考点解析：TRUNCATE属于全局DDL操作，执行过程会对整张表加元数据写锁，阻塞所有其他读写请求，因此生产环境大表清空操作不建议直接使用TRUNCATE，推荐按批次分条件执行DELETE删除再重置自增ID。5.对字段col定义普通B+树索引后，以下WHERE查询条件无法命中该索引的是A.WHEREcol='mysql'B.WHEREcollike'mysql%'C.WHEREcollike'%mysql%'D.WHEREcolin('mysql','oracle')参考答案：C考点解析：B+树索引的检索逻辑基于最左前缀匹配规则，当模糊查询的通配符%出现在字符串左侧时，无法利用索引有序性快速定位行，会触发全表扫描。如果需要支持全模糊匹配检索，建议使用MySQL8.0内置的全文索引或者引入Elasticsearch组件实现。6.以下不属于MySQL事务ACID四大核心特性的是A.原子性（Atomicity）B.并发性（Concurrency）C.一致性（Consistency）D.持久性（Durability）参考答案：B考点解析：事务四大核心特性为原子性、一致性、隔离性、持久性，高并发是数据库的性能指标，不属于事务的固有特性范畴。7.执行以下SQL语句的返回结果是SELECTROUND(12345.6789,-2)A.12346B.12300C.12345.68D.12400参考答案：B考点解析：ROUND函数的第二个参数表示保留的小数位数，当参数为负数时表示对整数部分指定位数做四舍五入，参数值为-2即对百位做四舍五入，12345.6789百位后数值为45，因此向下取整得到12300。8.要统计当前指定数据库下所有用户表的总数据量大小，需要查询的系统库是A.information_schemaB.mysqlC.performance_schemaD.sys参考答案：A考点解析：information_schema库存储了所有数据库的元数据信息，其中TABLES表的DATA_LENGTH、INDEX_LENGTH字段可以统计表的数据和索引占用空间大小。performance_schema用于采集数据库运行时性能指标，mysql库存储系统权限、账户信息，sys库是运维视图聚合库。9.以下SQL语句可以实现用户test授予对school库下所有表的SELECT权限的是A.GRANTSELECTONschool.*TO'test'@'%'B.GRANTSELECTON*.*TO'test'@'%'C.GRANTALLONschool.*TO'test'@'%'D.GRANTSELECTONschool.tableTO'test'@'%'参考答案：A考点解析：权限赋值语法中*.*表示全库所有表，school.*表示school库下所有表，符合题干要求的精准授权规则。10.InnoDB存储引擎的聚簇索引的存储结构特点是A.索引和数据完全分开存储，索引节点只存储主键值B.索引树的叶子节点直接存储整行数据，主键有序性决定了数据物理存储顺序C.索引树的叶子节点存储数据行的物理地址指针D.所有二级索引的检索路径不需要经过聚簇索引即可直接拿到全量数据参考答案：B考点解析：聚簇索引按照主键值的有序性排列整行数据存储，因此二级索引的叶子节点存储的是主键值，二级索引检索到主键后需要回表查询聚簇索引才能拿到全量字段。11.以下哪种操作场景最适合使用Memory存储引擎实现A.存储业务系统每日生成的订单流水数据B.存储用户登录态缓存数据，支持毫秒级读写，服务重启后数据可自动从下游数据源恢复C.存储大体积日志数据，长期归档留存D.存储需要支持事务、外键关联的核心业务数据参考答案：B考点解析：Memory存储引擎数据全部驻留内存，读写性能是磁盘InnoDB表的数十倍，服务重启后数据会清空，因此适合做缓存类临时数据存储，不适合持久化核心业务场景。12.执行EXPLAIN语句解析SELECT查询时，以下哪个type参数值代表查询触发了全表扫描，性能最差A.refB.rangeC.ALLD.index参考答案：C考点解析：type字段的检索性能从优到劣排序为system>const>eq_ref>ref>range>index>ALL，ALL代表全表逐行扫描，是生产环境慢SQL优化的核心靶点。13.MySQL8.0默认配置下，以下SQL语句无法正确创建名称为idx_name的普通索引的是A.CREATEINDEXidx_nameONstudent(s_name)B.ALTERTABLEstudentADDINDEXidx_name(s_name)C.CREATEUNIQUEINDEXidx_nameONstudent(s_id)D.CREATEINDEXidx_nameONstudent(s_name(100))WHEREs_nameisnotnull参考答案：D考点解析：MySQL普通索引不支持WHERE条件绑定的部分索引创建语法，该语法属于部分索引特性，仅在InnoDB特定新版本和第三方引擎中支持，默认配置会抛出语法错误。14.以下关于视图的描述，错误的是A.视图是基于SELECT查询结果生成的虚拟表，本身不存储物理数据B.单表生成的视图满足约束条件时支持UPDATE操作，修改数据会同步映射到原物理表C.视图可以实现敏感字段隐藏，简化复杂多表关联查询的调用逻辑D.视图创建完成后可以和物理表一样直接添加索引提升查询性能参考答案：D考点解析：视图属于虚拟表，没有对应的物理存储文件，因此不支持直接绑定实体索引，想要优化视图查询性能只能对视图关联的底层物理表添加索引实现。15.要批量生成100万条测试数据填充到测试表中，以下哪种方法性能最高A.循环执行100万次单条INSERT语句B.单条INSERT语句拼接1万条value值批次写入C.使用LOADDATAINFILE命令导入提前生成好的结构化文本数据D.逐条执行带事务的INSERT语句每条提交一次参考答案：C考点解析：LOADDATAINFILE是MySQL原生提供的大数据量导入命令，直接跳过SQL解析层批量写入数据，写入性能是普通INSERT语句的数十倍以上，是百万级测试数据生成的最优方案。16.以下不属于MySQL主从同步核心组件的是A.Binlog日志B.从库IO线程C.从库SQL线程D.Redo日志参考答案：D考点解析：Redo日志是InnoDB引擎层用于实现崩溃恢复的事务日志，不属于主从同步链路的核心组件，主从同步依赖Binlog日志实现主库数据变更的下游同步。17.执行以下语句的返回结果是SELECTSUBSTRING('MySQL数据库程序设计',2,3)A.'ySQ'B.'yS'C.'MySQL'D.'SQL'参考答案：A考点解析：MySQL内置SUBSTRING函数的起始下标从1开始计数，第2位对应字符y，截取3位后得到结果'ySQ'。18.以下哪个SQL关键字可以实现结果集去重，过滤重复行A.DISTINCTB.GROUPBYC.ORDERBYD.LIMIT参考答案：A考点解析：DISTINCT是专门用于结果集去重的关键字，GROUPBY也可以实现去重但本质是基于分组聚合逻辑实现，不属于专用去重语法。19.InnoDB引擎中用于保证事务持久性的核心日志是A.UndoLogB.RedoLogC.BinlogD.SlowLog参考答案：B考点解析：RedoLog采用预写日志机制，事务提交前会先把变更日志写入磁盘持久化，即使数据库宕机也可以通过RedoLog前滚恢复数据，实现事务的持久性特性。20.以下字段类型选择方案最合理的是A.用varchar类型存储用户身份证号B.用float类型存储订单实付金额，避免精度丢失C.用char(255)类型存储用户头像大体积图片数据D.用int类型存储IP地址，通过inet_aton和inet_ntoa函数转换参考答案：D考点解析：整数类型存储IP地址可以大幅节省存储空间，提升等值查询、范围检索性能。金额字段应该使用decimal类型避免浮点精度丢失，大体积二进制文件应该存储文件路径到数据库而非直接存储内容。二、多项选择题（共10题，每题3分，共30分。每题有2个及以上正确选项，错选漏选不得分）1.MySQL8.0官方标准日志体系中属于服务层全局日志的是A.ErrorLog错误日志B.SlowQueryLog慢查询日志C.Binlog二进制日志D.RedoLog重做日志参考答案：ABC考点解析：RedoLog属于InnoDB引擎层私有日志，不属于服务层全局日志范畴，其余三类日志均为服务层标准日志，所有存储引擎都可以生成对应日志内容。2.以下操作会触发MySQL事务隐式提交的是A.执行ALTERTABLE修改表结构语句B.执行DROPTABLE删除表语句C.执行CREATEINDEX创建索引语句D.执行普通SELECT查询语句参考答案：ABC考点解析：所有DDL数据定义语句执行前都会自动提交当前未完成的事务，属于隐式提交范畴，SELECT是只读语句不会修改事务状态，不会触发隐式提交。3.以下属于MySQL性能优化最佳实践的是A.避免在索引字段上使用函数运算，防止索引失效B.禁止使用SELECT*检索全量字段，按需取数减少回表开销C.大表JOIN关联时优先驱动小表，关联字段建立索引D.深度分页场景使用LIMIT100000,20语句直接检索数据，无需优化参考答案：ABC考点解析：深度分页场景下LIMIToffset,size会扫描前offset行无效数据，性能极差，应该使用上一页的最后一条数据作为锚点，通过WHEREid>#{last_id}LIMITsize的方式优化检索效率。4.以下关于存储过程和函数的差异描述，正确的是A.函数必须有返回值，存储过程可以没有返回值B.函数内部可以使用返回语句返回单个值，存储过程可以通过OUT参数返回多个结果集C.函数可以在SELECT语句中直接调用，存储过程需要通过CALL语句手动调用D.存储过程内部可以执行事务提交、回滚操作，用户自定义函数不支持事务操作参考答案：ABCD考点解析：以上描述完全符合MySQL8.0存储过程和自定义函数的语法特性，生产环境中复杂业务逻辑封装推荐使用存储过程，避免使用自定义函数导致主从同步不一致问题。5.以下可以实现MySQL数据备份的工具是A.mysqldumpB.xtrabackupC.mysqlpumpD.navicat图形工具导出参考答案：ABCD考点解析：mysqldump、mysqlpump是官方原生逻辑备份工具，xtrabackup是Percona团队开发的物理热备工具，Navicat等图形客户端也支持结构化数据导出备份。三、填空题（共10空，每空2分，共20分）1.要查询当前数据库所有正在运行的InnoDB事务详情，需要访问的系统视图是____。参考答案：information_schema.INNODB_TRX2.使用mysqldump对InnoDB引擎库做在线一致性热备，不阻塞业务DML写入的核心参数是____。参考答案：--single-transaction3.MySQL8.0中默认的字符集排序规则是____。参考答案：utf8mb4_0900_ai_ci4.可以实现查看SQL执行计划详情的关键字是____。参考答案：EXPLAINANALYZE5.为了避免主从同步数据不一致问题，生产环境主库写入操作禁止使用的时间函数是____。参考答案：NOW()四、SQL实操题（共2小题，每题5分，共10分）给定学生选课系统核心表结构：student(s_idchar(10)primarykey,s_namevarchar(20)notnull,s_genderchar(1),s_birthdate,s_deptvarchar(50),s_entrance_yearyear)course(c_idchar(8)primarykey,c_namevarchar(30)notnull,c_creditdecimal(3,1),t_idchar(10))sc(s_idchar(10),c_idchar(8),scoretinyint,select_timedatetimedefaultcurrent_timestamp,primarykey(s_id,c_id))1.统计2020级所有学生的总学分，要求成绩大于等于60分时累加对应课程的学分，低于60分不累加，显示学生姓名、总学分。参考答案SQL：selects.s_name,sum(casewhensc.score>=60thenc.c_creditelse0end)astotal_creditfromstudentsleftjoinscons.s_id=sc.s_idleftjoincourseconsc.c_id=c.c_idwheres.s_entrance_year=2020groupbys.s_id,s.s_name;2.查询每个学院成绩最高的学生信息，显示学院名称、学生姓名、最高成绩。参考答案SQL：selectt.s_dept,s.s_name,t.max_scorefrom(selects.s_dept,max(sc.score)asmax_scorefr