2025年MySQL数据库基本操作试题及答案

2025年MySQL数据库基本操作试题及答案一、单项选择题（共20题，每题2分，共40分）1.MySQL8.4LTS版本默认的存储引擎是？A.MyISAMB.InnoDBC.MemoryD.Archive2.存储国内11位手机号，以下哪种数据类型综合存储效率和校验适配性最优？A.INTB.VARCHAR(11)C.CHAR(11)D.TEXT3.下列哪个关键字用于定义非空约束？A.UNIQUEB.PRIMARYKEYC.NOTNULLD.DEFAULT4.查询员工表emp中工资大于等于5000且小于等于10000的记录，下列SQL写法错误的是？A.SELECT*FROMempWHEREsalaryBETWEEN5000AND10000;B.SELECT*FROMempWHEREsalary>=5000ANDsalary<=10000;C.SELECT*FROMempWHERE5000<=salary<=10000;D.SELECT*FROMempWHEREsalaryIN(SELECTsalaryFROMempWHEREsalary>=5000ANDsalary<=10000);5.关于主键约束和唯一键约束的区别，下列说法错误的是？A.主键不允许为空，唯一键允许为空B.一张表只能有1个主键，可创建多个唯一键C.主键默认创建聚簇索引，唯一键默认创建非聚簇索引D.唯一键约束的列不允许出现重复值，包括NULL值6.下列函数中属于MySQL8.0版本新增的窗口函数是？A.COUNT()B.SUM()C.ROW_NUMBER()D.GROUP_CONCAT()7.事务ACID特性中，哪一项指事务一旦提交，对数据的修改就是永久的，即使系统故障也不会丢失？A.原子性B.一致性C.隔离性D.持久性8.MySQL默认的事务隔离级别是？A.读未提交B.读已提交C.可重复读D.串行化9.下列哪个命令可以查看指定表的字段结构、约束、数据类型等信息？A.SHOWTABLES;B.DESCtable_name;C.SHOWDATABASES;D.SELECT*FROMtable_name;10.清空表所有数据且执行后不可回滚的操作是？A.DELETEFROMtable_name;B.DROPTABLEtable_name;C.TRUNCATETABLEtable_name;D.REMOVETABLEtable_name;11.以下哪种索引类型最适合前缀匹配的模糊查询（如`nameLIKE'张%'`）？A.B+树索引B.哈希索引C.全文索引D.空间索引12.下列备份方式中，属于热备且支持增量备份的是？A.mysqldump逻辑备份B.xtrabackup物理备份C.直接cp表文件D.二进制日志备份13.创建用户`user1`，允许其从网段所有主机登录，密码为`Abc@2025`，下列SQL正确的是？A.CREATEUSER'user1'@'192.168.1.%'IDENTIFIEDBY'Abc@2025';B.CREATEUSER'user1'@'/24'IDENTIFIEDBY'Abc@2025';C.CREATEUSER'user1'@'%'IDENTIFIEDBY'Abc@2025';D.CREATEUSER'user1'@'192.168.1.*'IDENTIFIEDBY'Abc@2025';14.MySQL8.0及以上版本默认的字符集是？A.utf8B.utf8mb3C.utf8mb4D.gbk15.现有联合索引`idx_a_b_c(a,b,c)`，下列查询无法用到该索引的是？A.WHEREa=1ANDb=2;B.WHEREa=1ANDb>2ANDc=3;C.WHEREb=2ANDc=3;D.WHEREa=1ORDERBYb;16.为emp表新增INT类型的age字段，下列SQL正确的是？A.ALTERTABLEempADDCOLUMNageINT;B.MODIFYTABLEempADDageINT;C.UPDATETABLEempADDCOLUMNageINT;D.CHANGETABLEempADDageINT;17.下列聚合函数中会忽略NULL值的是？A.COUNT(*)B.COUNT(column_name)C.SUM(column_name)D.B和C都对18.InnoDB在可重复读隔离级别下，通过什么机制解决了幻读问题？A.MVCCB.临键锁（Next-KeyLock）C.MVCC+临键锁D.行级锁19.下列哪个参数用于开启慢查询日志功能？A.slow_query_log=ONB.long_query_time=1C.log_output=FILED.general_log=ON20.下列关于视图的说法错误的是？A.视图是虚拟表，本身不存储数据B.视图可以简化复杂查询的编写C.视图可以提升查询性能D.视图可以限制用户对敏感字段的访问单项选择题参考答案及解析1.答案：B。解析：MySQL5.5之后默认存储引擎为InnoDB，8.4LTS延续该默认配置，支持事务、行锁、外键等特性。2.答案：C。解析：国内手机号为固定11位长度，CHAR类型无长度前缀开销，存储和查询效率高于VARCHAR；INT类型最大存储值为2147483647，无法覆盖13开头的11位手机号。3.答案：C。解析：UNIQUE为唯一约束，PRIMARYKEY为主键约束，DEFAULT为默认值约束。4.答案：C。解析：MySQL不支持连续不等式写法，`5000<=salary<=10000`会先计算`5000<=salary`的布尔值（0或1），再和10000比较，结果恒为真，返回全表数据。5.答案：D。解析：唯一键约束允许存在多个NULL值，因为NULL和NULL不相等，不会触发唯一性校验。6.答案：C。解析：ROW_NUMBER()、RANK()、DENSE_RANK()等窗口函数为MySQL8.0新增特性，5.7及之前版本不支持。7.答案：D。解析：原子性指事务所有操作要么全部成功要么全部失败；一致性指事务执行前后数据完整性不被破坏；隔离性指多个事务执行时互不干扰。8.答案：C。解析：可重复读为MySQL默认隔离级别，多数商业数据库默认隔离级别为读已提交。9.答案：B。解析：SHOWTABLES查看当前库所有表，SHOWDATABASES查看所有库，SELECT*查询表数据。10.答案：C。解析：DELETE为DML操作，可回滚；DROP为删除表结构和数据；TRUNCATE为DDL操作，自动提交，不可回滚，执行速度远快于DELETE。11.答案：A。解析：哈希索引仅支持等值查询，不支持范围、模糊、排序；B+树的有序性适配前缀匹配模糊查询。12.答案：B。解析：mysqldump为温备，备份时锁表不支持写入；xtrabackup为Percona开源热备工具，备份过程不影响业务写入，支持全量、增量备份。13.答案：A。解析：MySQL用户主机匹配使用`%`作为通配符，`192.168.1.%`代表-255网段所有主机。14.答案：C。解析：MySQL8.0之前默认字符集为utf8mb3（仅支持最多3字节UTF-8字符，无法存储emoji等4字节字符），8.0之后默认改为utf8mb4，适配所有UTF-8字符。15.答案：C。解析：联合索引遵循最左前缀匹配原则，查询条件不包含最左列a时，无法触发索引匹配。16.答案：A。解析：修改表结构统一使用ALTERTABLE关键字，新增字段用ADDCOLUMN，修改字段属性用MODIFY，修改字段名用CHANGE。17.答案：D。解析：COUNT(*)统计行数，不忽略NULL值；COUNT(指定列)和SUM(指定列)统计时会跳过值为NULL的行。18.答案：C。解析：快照读（普通SELECT）通过MVCC避免幻读，当前读（SELECT...FORUPDATE、INSERT、UPDATE、DELETE）通过临键锁避免幻读，二者结合解决可重复读级别下的幻读问题。19.答案：A。解析：long_query_time设置慢查询阈值，log_output设置日志输出方式，general_log为通用查询日志，会记录所有SQL，性能损耗大不建议开启。20.答案：C。解析：视图本身不存储数据，查询视图本质是执行视图对应的SQL语句，不会直接提升查询性能，反而部分复杂视图可能降低查询效率。二、填空题（共15题，每题2分，共30分）1.命令行连接本地MySQL服务，用户名为root，密文输入密码的命令是____。2.查看当前使用的数据库名称的SQL语句是____。3.定义主键约束的关键字是____。4.事务的四个ACID特性分别是原子性、____、隔离性、持久性。5.MySQL模糊查询的通配符中，____匹配任意多个任意字符，____匹配单个任意字符。6.InnoDB存储引擎中，主键索引也被称为____索引，叶子节点存储整行数据。7.手动提交事务的命令是____，手动回滚事务的命令是____。8.MySQL8.0常用的三个排名窗口函数为ROW_NUMBER()、RANK()和____。9.查看emp表所有索引信息的SQL语句是____。10.删除emp表中id=10的记录的SQL语句是____。11.mysqldump备份整个MySQL实例所有库的专用参数是____。12.授予user1对test库所有表的查询权限的SQL语句是____。13.联合索引的核心匹配规则是____。14.查看当前MySQL版本的SQL语句是____。15.清空表所有数据且支持回滚的操作是____。填空题参考答案及解析1.答案：mysql-uroot-p。解析：-p后不跟密码，执行命令后会弹出密文输入提示，避免明文密码泄露风险。2.答案：SELECTDATABASE();3.答案：PRIMARYKEY。4.答案：一致性。5.答案：%、_。6.答案：聚簇（Clustered）。解析：非聚簇索引叶子节点存储主键值，查询时需要回表到聚簇索引获取整行数据。7.答案：COMMIT;、ROLLBACK;。8.答案：DENSE_RANK()。解析：ROW_NUMBER()排名不重复，RANK()排名跳号，DENSE_RANK()排名不跳号。9.答案：SHOWINDEXFROMemp;10.答案：DELETEFROMempWHEREid=10;。解析：DELETE语句必须加WHERE条件，否则会清空全表数据。11.答案：--all-databases。12.答案：GRANTSELECTONtest.*TO'user1'@'授权主机地址';。解析：授权后需执行FLUSHPRIVILEGES;刷新权限生效。13.答案：最左前缀匹配原则。14.答案：SELECTVERSION();15.答案：DELETEFROMemp;（未加WHERE条件）。解析：DELETE为DML操作，默认开启事务的情况下可回滚，TRUNCATE为DDL操作不可回滚。三、简答题（共5题，每题6分，共30分）1.请简述DELETE、TRUNCATE、DROP三种操作的核心区别。2.请简述InnoDB和MyISAM存储引擎的核心差异及适用场景。3.请简述MySQL的四个事务隔离级别，以及每个级别解决的问题和存在的风险。4.请简述索引的优缺点，以及创建索引的核心原则。5.请简述慢查询的完整优化思路。简答题参考答案1.核心区别如下：操作类型：DELETE是DML操作，TRUNCATE和DROP是DDL操作；可回滚性：DELETE可回滚，TRUNCATE和DROP执行后自动提交，不可回滚；执行效率：DROP>TRUNCATE>DELETE，DELETE逐行删除记录并记录二进制日志，TRUNCATE直接删除表并重建空表，DROP删除表结构和数据；影响内容：DELETE仅删除数据保留表结构，可加WHERE条件删除部分行；TRUNCATE清空全表数据保留表结构；DROP删除表结构、数据、索引、约束等所有对象；触发器：DELETE会触发DELETE触发器，TRUNCATE和DROP不会触发触发器。2.核心差异及适用场景：事务支持：InnoDB支持事务，MyISAM不支持；锁粒度：InnoDB支持行级锁，MyISAM仅支持表级锁，写入性能差；外键支持：InnoDB支持外键约束，MyISAM不支持；索引结构：InnoDB使用聚簇索引，MyISAM使用非聚簇索引；崩溃恢复：InnoDB有redolog和undolog，崩溃后可安全恢复，MyISAM崩溃后易出现数据损坏；计数查询：MyISAM存储表的总行数，`SELECTCOUNT(*)`无WHERE条件时速度极快，InnoDB需要逐行统计；适用场景：InnoDB适用于需要事务支持、高并发写入、数据一致性要求高的业务场景；MyISAM仅适用于只读或极少写入、对数据一致性要求低的场景，MySQL8.0已逐步淘汰MyISAM。3.四个事务隔离级别：读未提交：事务中的修改即使未提交，对其他事务也可见，存在脏读、不可重复读、幻读问题，几乎无生产使用场景；读已提交：事务只能看到其他事务已提交的修改，解决了脏读问题，存在不可重复读、幻读问题，适合多数对数据一致性要求不极高的OLTP场景，为Oracle、PostgreSQL等数据库默认隔离级别；可重复读：同一事务中多次读取同一条数据的结果一致，解决了脏读、不可重复读问题，InnoDB通过MVCC+临键锁解决了幻读问题，为MySQL默认隔离级别；串行化：所有事务按顺序串行执行，解决了所有并发问题，性能极差，仅适用于对数据一致性要求极高且并发量极低的场景。4.索引优缺点：优点：大幅提升查询、排序、分组的执行效率；唯一索引可保证列数据的唯一性；缺点：占用额外磁盘空间；增删改操作需要维护索引，降低写入性能；过多冗余索引会提升优化器选择索引的开销。创建核心原则：优先为高频查询的WHERE、JOIN、ORDERBY、GROUPBY字段创建索引；优先选择区分度高的字段（如手机号、用户ID，避免为状态类只有2-3个值的字段建索引）；联合索引按区分度从高到低、等值条件在前、范围条件在后的顺序排列；避免创建冗余索引（如已有idx(a,b)无需再单独建idx(a)）；小数据量的表无需建索引；优先使用覆盖索引，避免回表开销。5.慢查询优化思路：第一步：开启慢查询日志，设置合理阈值（通常1-2秒），定位慢SQL；第二步：使用EXPLAIN分析慢SQL的执行计划，查看是否走索引、扫描行数、是否有临时表、文件排序、关联类型等信息，定位性能瓶颈；第三步：SQL优化：避免SELECT*，只查询需要的字段；避免索引列上使用函数、类型转换、不等条件，导致索引失效；避免大表关联，拆分复杂SQL；避免大量数据的排序操作；第四步：索引优化：为查询条件添加合适的联合索引，优先使用覆盖索引；删除冗余、无效索引；第五步：结构优化：拆分大字段到扩展表，减少单表大小；数据量超过千万级时按业务字段分库分表；第六步：架构优化：热点数据缓存到Redis等内存数据库；配置读写分离，读请求分流到从库；调整MySQL配置参数（如sort_buffer_size、join_buffer_size、innodb_buffer_pool_size等）。四、实操题（共3题，每题20分，共60分）第1题基础库表操作需求：1.创建名为mall的数据库，字符集为utf8mb4，排序规则为utf8mb4_general_ci；2.在mall库中创建用户表user，字段要求：idINT主键自增，usernameVARCHAR(32)非空唯一，phoneCHAR(11)非空唯一，ageTINYINT要求大于等于18小于等于120，create_timeDATETIME默认当前时间，update_timeDATETIME默认当前时间且自动更新；3.修改user表，新增字段emailVARCHAR(64)允许为空；4.向user表插入3条测试数据：username分别为zhangsan、lisi、wangwu，phone分别13800000002age分别为22、25、30；5.查询age大于23的用户的username和phone；6.将username为lisi的用户age修改为26；7.删除username为wangwu的用户记录。参考答案```sql-1.创建mall库CREATEDATABASEmallDEFAULTCHARSETutf8mb4COLLATEutf8mb4_general_ci;USEmall;-2.创建user表CREATETABLEuser(idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'用户ID',usernameVARCHAR(32)NOTNULLUNIQUECOMMENT'用户名',phoneCHAR(11)NOTNULLUNIQUECOMMENT'手机号',ageTINYINTCHECK(age>=18ANDage<=120)COMMENT'年龄',create_timeDATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',update_timeDATETIMEDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT'用户表';-3.新增email字段ALTERTABLEuserADDCOLUMNemailVARCHAR(64)COMMENT'邮箱';-4.插入测试数据INSERTINTOuser(username,phone,age)VALUES('zhangsan',,22),('lisi',,25),('wangwu',,30);-5.查询age>23的用户SELECTusername,phoneFROMuserWHEREage>23;-6.修改lisi的年龄UPDATEuserSETage=26WHEREusername='lisi';-7.删除wangwu的记录DELETEFROMuserWHEREusername='wangwu';```注：CHECK约束为MySQL8.0.16版本之后原生支持，低版本需通过触发器实现。第2题进阶查询操作现有两张表：emp（员工表）：emp_idINT主键，emp_nameVARCHAR(32)，dept_idINT，salaryDECIMAL(10,2)，hire_dateDATEdept（部门表）：dept_idINT主键，dept_nameVARCHAR(32)，locationVARCHAR(64)需求：1.查询所有员工的姓名、部门名称、工资，没有部门的员工也要显示；2.查询每个部门的平均工资，只显示平均工资大于8000的部门，按平均工资降序排序；3.查询工资高于本部门平均工资的员工姓名、部门名称、工资；4.按工资对员工进行排名，要求工资相同排名相同、排名不跳号，显示排名、员工姓名、工资、部门名称；5.查询2020年1月1日之后入职的员工，按入职时间升序取前10条。参考答案```sql-1.左连接查询所有员工SELECTe.emp_name,d.dept_name,e.salaryFROMempeLEFTJOINdeptdONe.dept_id=d.dept_id;-2.分组过滤部门平均工资SELECTd.dept_name,AVG(e.salary)avg_salaryFROMempeJOINdeptdONe.dept_id=d.dept_idGROUPBYd.dept_idHAVINGavg_salary>8000ORDERBYavg_salaryDESC;-3.查询高于部门平均工资的员工SELECTe.emp_name,d.dept_name,e.salaryFROMempeJOINdeptdONe.dept_id=d.dept_idJOIN(SELECTdept_id,AVG(salary)dept_avgFROMempGROUPBYdept_id)tONe.dept_id=t.dept_idWHEREe.salary>t.dept_avg;-4.排名查询SELECTDENSE_RANK()OVER(ORDERBYsalaryDESC)rk,e.emp_name,e.salary,d.dept_nameFROMempeLEFTJOINdeptdONe.dept_id=d.dept_id;-5.入职时间筛选SELECT*FROMempWHEREhire_date>='2020-01-01'ORDERBYhire_dateASCLIMIT10;```第3题基础运维操作需求：1.备份mall数据库到/backup/mall_20250601.sql文件，要求包含触发器、存储过程、事件；2.将上述备份文件恢复到名为mall_test的新库中；3.创建用户test_user，允许本地登录，密码为Test@2025，授予其对mall_test库所有表的查询、插入、修改权限；4.临时开启慢查询日志，设置阈值为2秒，日志路径为/var/log/mysql/slow.log；5.统计慢查询日志中执行时间最长的5条SQL。参考答案```bashmysqldump-uroot-p--databasesmall--routines--triggers--events>/backup/mall_20250601.sqlmysql-uroot-p-e"CREATEDATABASEmall_testDEFAULTCHARSETutf8mb4;"mysql-uroot-pmall_test</backup/mall_20250601.sql``````sql-3.创建用户授权CREATEUSER'test_user'@'localhost'IDENTIFIEDBY'Test@2025';GRANTSELECT,INSERT,UPDATEONmall_test.*TO'test_user'@'localhost';FLUSHPRIVILEGES;-4.临时开启慢查询日志（重启后失效，永久生效需修改f配置）SETGLOBALslow_query_log=ON;SETGLOBALlong_query_time=2;SETGLOBALslow_query_log_file='/var/log/mysql/slow.log';``````bashmysqldumpslow-st-t5/var/log/mysql/slow.log```五、场景分析题（共1题，20分）场景某电商平台订单表orders结构如下：```sqlCREATETABLEorders(order_idBIGINTPRIMARYKEYAU