SQL常见面试题（含答案解析企业真实场景版）

SQL常见面试题（含答案解析，企业真实场景版）本次面试题覆盖SQL基础、查询优化、多表关联、函数使用、事务与索引、实战场景六大高频模块，所有题目均为互联网、传统行业数据库岗位高频面试原题，答案贴合实际工作开发规范，无冗余理论废话。一、SQL基础语法面试题1、简述SQL中DDL、DML、DQL、DCL的区别，并分别举例常用语句答案解析：四类语句是SQL的核心分类，核心区别为操作对象和使用场景不同：1.DDL（数据定义语言）：用于定义、修改数据库和表结构，操作不可逆，执行后自动提交事务。常用语句：CREATE（创建库/表）、ALTER（修改表结构）、DROP（删除库/表）、TRUNCATE（清空表数据、保留结构）。2.DML（数据操纵语言）：用于操作表中的数据，可增删改数据，需要手动提交事务。常用语句：INSERT（插入数据）、UPDATE（更新数据）、DELETE（删除数据）。3.DQL（数据查询语言）：用于查询数据库数据，不会修改任何数据和结构，是日常使用最多的语句。常用语句：SELECT。4.DCL（数据控制语言）：用于权限管理和事务控制。常用语句：GRANT（授权）、REVOKE（撤销权限）、COMMIT（提交事务）、ROLLBACK（回滚事务）。2、DELETE和TRUNCATE、DROP的区别？答案解析：1.作用对象不同：DELETE只删除表数据，保留表结构；TRUNCATE清空全表数据，保留表结构；DROP直接删除整张表（结构+数据），同时释放表占用的存储空间。2.事务支持不同：DELETE支持事务，可回滚；TRUNCATE、DROP不支持事务，执行后无法回滚。3.自增列特性不同：DELETE删除数据后，自增主键会延续之前数值；TRUNCATE清空表后，自增主键重置为初始值。4.执行效率：DROP>TRUNCATE>DELETE，前两者为批量操作，DELETE为逐行操作。5.权限要求：DROP权限级别最高，一般仅管理员拥有，业务开发极少使用。3、WHERE和HAVING的区别？答案解析：1.执行时机不同：WHERE是分组之前过滤数据；HAVING是分组之后过滤分组结果。2.筛选对象不同：WHERE针对单行原始数据筛选；HAVING针对分组后的聚合结果筛选。3.聚合函数使用：WHERE子句中不能使用聚合函数（SUM、COUNT、MAX等）；HAVING可以使用聚合函数。4.执行顺序：WHERE→GROUPBY→HAVING示例：统计各部门平均薪资，只展示平均薪资大于8000的部门SELECTdept_id,AVG(salary)ASavg_salFROMemployeeGROUPBYdept_idHAVINGAVG(salary)>8000;二、多表查询与关联查询面试题1、简述INNERJOIN、LEFTJOIN、RIGHTJOIN、FULLJOIN的区别答案解析：假设有A表（左表）、B表（右表），核心区别为匹配数据范围不同：1.INNERJOIN（内连接）：只查询两张表中匹配成功的数据，无匹配数据不展示，是最常用的连接方式。2.LEFTJOIN（左连接）：保留左表所有数据，匹配右表数据，无匹配的右表字段填充NULL。日常业务使用频率最高。3.RIGHTJOIN（右连接）：保留右表所有数据，匹配左表数据，无匹配的左表字段填充NULL，业务中极少使用，可通过左连接替代。4.FULLJOIN（全连接）：保留左右两张表的所有数据，无匹配的字段填充NULL。注意：MySQL不支持FULLJOIN，可通过UNION拼接左连接和右连接结果实现。2、UNION和UNIONALL的区别？各自使用场景答案解析：1.去重区别：UNION会自动去除合并后的重复数据；UNIONALL直接拼接所有结果，不做去重。2.效率区别：UNION需要排序去重，性能低；UNIONALL无排序、无去重，性能远高于UNION。3.排序规则：UNION会默认按首列排序；UNIONALL保持原有查询顺序。使用场景：业务数据无重复、追求查询速度时，优先用UNIONALL；需要自动去重的场景，使用UNION。实际开发中90%场景优先UNIONALL。3、多表关联查询出现重复数据，如何排查和解决？答案解析：1.重复原因：关联的其中一张表存在一对多数据，单条左表数据匹配多条右表数据，导致结果膨胀重复。2.解决方案：①对多数据的表提前去重（使用DISTINCT、GROUPBY预处理）；②使用子查询先筛选唯一数据，再进行表关联；③业务允许的情况下，使用LIMIT限制关联数据；④核对关联条件，避免关联字段匹配规则宽泛导致多余匹配。三、数据库索引面试题（高频核心）1、什么是索引？索引的优缺点答案解析：索引是数据库优化查询的磁盘数据结构，相当于表数据的目录，用于快速定位查询数据，避免全表扫描。优点：1.极大提升数据查询速度，减少IO读取次数；2.唯一索引可以保证字段数据唯一性；3.分组、排序查询时，可大幅提升GROUPBY、ORDERBY执行效率。缺点：1.占用额外磁盘空间，索引越多，存储空间占用越大；2.降低增删改（INSERT、UPDATE、DELETE）效率，数据变更时需要同步更新索引结构；3.索引并非越多越好，无效索引会增加数据库维护成本。2、MySQL中常见索引类型及适用场景答案解析：1.主键索引（PRIMARYKEY）：唯一且非空，一张表只能有一个，查询优先级最高，用于唯一标识数据行。2.唯一索引（UNIQUE）：字段数据唯一，允许为空，适用于手机号、身份证号、账号等唯一字段。3.普通索引（INDEX）：最基础索引，无唯一性限制，适用于高频查询、筛选、排序的普通字段。4.联合索引（复合索引）：多个字段组合的索引，遵循最左前缀原则，适用于多字段联合查询场景。5.全文索引（FULLTEXT）：用于文本字段模糊查询，替代低效的LIKE%xxx%查询。3、什么是最左前缀原则？联合索引如何失效？答案解析：最左前缀原则：联合索引(a,b,c)中，查询必须匹配最左侧连续字段，索引才会生效。索引生效场景：wherea=?、wherea=?andb=?、wherea=?andb=?andc=?索引失效场景：1.不使用最左字段查询，如whereb=?、wherec=?；2.索引字段参与运算、函数调用，如whereage+10=20、whereSUBSTR(name,1,2)='张'；3.字段类型隐式转换，如字符串字段不加引号、数字字段传字符串；4.使用!=、<>、NOTIN、OR（无全索引场景）；5.LIKE以通配符开头，如'%张三'、'_张三'；6.MySQL优化器判断全表扫描比索引查询更快时，会主动放弃索引。四、事务与数据库锁面试题1、数据库事务的四大特性（ACID）答案解析：1.原子性（Atomicity）：事务是最小执行单元，要么全部执行成功，要么全部回滚失败，不存在部分执行。2.一致性（Consistency）：事务执行前后，数据库数据完整性、约束规则保持一致，不会出现数据错乱。3.隔离性（Isolation）：多个事务并发执行时，相互独立、互不干扰。4.持久性（Durability）：事务提交成功后，数据永久写入数据库，即使服务器宕机也不会丢失。2、MySQL四大事务隔离级别及并发问题答案解析：隔离级别从低到高，安全性递增、性能递减：1.读未提交（ReadUncommitted）：可读取其他事务未提交数据，存在脏读、不可重复读、幻读，业务基本不用。2.读已提交（ReadCommitted）：只能读取其他事务已提交数据，解决脏读，存在不可重复读、幻读，Oracle默认级别。3.可重复读（RepeatableRead）：同一事务内多次读取同一数据结果一致，解决脏读、不可重复读，存在幻读，MySQLInnoDB默认级别。4.串行化（Serializable）：事务串行执行，完全解决所有并发问题，性能极差，极少使用。核心概念：脏读（读未提交数据）、不可重复读（同一事务多次查询结果不同）、幻读（其他事务新增/删除数据，导致当前事务查询数据总量变化）。3、InnoDB和MyISAM的核心区别答案解析：1.事务支持：InnoDB支持事务；MyISAM不支持事务。2.锁机制：InnoDB支持行锁、表锁；MyISAM只支持表锁，并发能力差。3.崩溃恢复：InnoDB支持事务日志，宕机可恢复数据；MyISAM崩溃易数据损坏。4.外键支持：InnoDB支持外键约束；MyISAM不支持。5.场景适配：InnoDB适用于增删改频繁、并发高、需要事务的业务表；MyISAM适用于静态、查询多、极少修改的日志表、统计表。五、SQL优化面试题（实操高频）1、日常开发中，常用的SQL优化手段有哪些？答案解析：1.索引优化：高频查询字段建立索引，避免过度建索引，遵循最左前缀原则，规避索引失效场景；2.查询语句优化：禁止使用SELECT*，只查询需要的字段；优先使用UNIONALL替代UNION；避免子查询嵌套过深；3.条件优化：WHERE条件避免索引字段运算、函数、隐式转换；慎用LIKE模糊前置匹配、OR、NOTIN；4.分页优化：大分页场景（limit10000,10）使用延迟分页、主键回溯优化，避免全量扫描；5.表结构优化：字段优先使用tinyint、varchar等轻量类型，避免大字段、冗余字段；大表进行分表、分区；答案解析：核心关注字段：1.type：查询类型，优先级system>const>eq_ref>ref>range>index>ALL，出现ALL代表全表扫描，需要优化；2.key：实际生效的索引，为NULL代表未使用索引；3.rows：扫描的数据行数，数值越大性能越差；4.Extra：额外信息，出现Usingfilesort（文件排序）、Usingtemporary（临时表）代表存在性能问题。六、实战场景SQL面试题（手写SQL）基础表结构（通用面试表）：员工表employee（id主键，name姓名，dept_id部门ID，salary薪资，create_time入职时间）部门表department（dept_id主键，dept_name部门名称）1、查询每个部门的员工人数、最高薪资、平均薪资参考答案：SELECT

dept_id,

COUNT(id)ASstaff_num,

MAX(salary)ASmax_sal,

ROUND(AVG(salary),2)ASavg_sal

FROMemployee

GROUPBYdept_id;2、查询薪资高于本部门平均薪资的所有员工信息参考答案：SELECTe.*

FROMemployeee

INNERJOIN(

SELECTdept_id,AVG(salary)ASavg_sal

FROMemployeeGROUPBYdept_id

)tONe.dept_id=t.dept_id