SQL高频面试题及详细答案（实战完整版）

SQL高频面试题及详细答案（实战完整版）本套题库整理互联网、软件行业面试高频SQL考题，涵盖基础语法、查询优化、索引、事务、联表查询、复杂统计、数据库原理等核心内容，答案结合实际工作场景，通俗易懂，适合初级、中级开发、测试、数据分析岗位面试备考。一、SQL基础语法面试题1、简述SQL的常用分类及核心作用SQL主要分为四大类，是日常数据库操作的核心规范：1、DDL（数据定义语言）：用于定义和管理数据库、数据表结构，核心语句包括CREATE、ALTER、DROP、TRUNCATE。不操作数据，只改结构。2、DML（数据操作语言）：用于操作表中具体数据，核心语句包括SELECT、INSERT、UPDATE、DELETE，是日常开发使用最多的语句。3、DCL（数据控制语言）：用于权限管理，核心语句包括GRANT（授权）、REVOKE（撤销权限）。4、TCL（事务控制语言）：用于管理数据库事务，核心语句包括COMMIT（提交）、ROLLBACK（回滚）、SAVEPOINT（保存点）。2、DELETE、TRUNCATE、DROP的区别（高频必考）三者核心差异体现在操作对象、日志记录、效率、可恢复性、自增字段五个维度：1、操作对象：DELETE删除表中数据行，保留表结构；TRUNCATE清空整张表数据，保留表结构；DROP直接删除整张表（结构+数据），同时删除索引、约束。2、日志与事务：DELETE属于DML，会记录事务日志，可事务回滚；TRUNCATE、DROP属于DDL，不记录行级日志，无法回滚。3、自增字段：DELETE删除数据后，自增主键会延续之前数值；TRUNCATE清空表后，自增主键重置从1开始。4、执行效率：DROP>TRUNCATE>DELETE，批量删除大量数据优先用TRUNCATE。5、权限要求：DROP权限等级最高，一般业务开发仅拥有DELETE权限。3、WHERE和HAVING的区别1、执行时机：WHERE是分组前过滤数据，先过滤再分组；HAVING是分组后过滤分组结果。2、适用条件：WHERE不能使用聚合函数（SUM、COUNT、MAX等）；HAVING专门用于过滤聚合函数的结果。3、执行顺序：WHERE>GROUPBY>HAVING。举例：统计班级平均分大于80分的班级，需要先通过WHERE筛选有效学生数据，再GROUPBY分组，最后HAVING筛选平均分。4、DISTINCT的作用及使用注意事项作用：去除查询结果中的重复数据，返回唯一值。注意事项：1、DISTINCT作用于所有查询字段组合，不是单个字段。例如SELECTDISTINCTid,name，只有id和name完全一致才会去重。2、大数据量下DISTINCT效率较低，会触发全表扫描，日常优化可通过GROUPBY替代。3、不支持单独用于聚合函数内部，写法有严格规范。二、联表查询与子查询高频题1、简述SQL四种常用联表查询及场景假设存在A表、B表两张关联表：1、INNERJOIN（内连接）：只返回两张表中匹配关联条件的数据，两边无匹配则不展示，是最常用的联表方式。2、LEFTJOIN（左连接）：以左表（A表）为基准，左表所有数据全部展示，右表匹配不到的数据显示NULL。3、RIGHTJOIN（右连接）：以右表（B表）为基准，右表所有数据全部展示，左表匹配不到的数据显示NULL。4、FULLJOIN（全连接）：返回左表、右表所有数据，无匹配字段填充NULL（MySQL不支持，可通过UNION实现）。2、LEFTJOIN和INNERJOIN使用场景区别1、INNERJOIN：适用于必须存在关联数据的场景，比如查询用户的订单记录，只展示有订单的用户。2、LEFTJOIN：适用于需要保留主表全部数据的场景，比如统计所有用户的订单数量，包括从未下单的用户（订单数为0）。核心避坑：LEFTJOIN查询后加WHERE过滤右表字段，会导致左连接失效，等同于内连接。需要过滤右表数据时，条件写在ON后面。3、UNION和UNIONALL的区别1、去重差异：UNION会自动去除两个查询结果的重复数据；UNIONALL直接拼接所有结果，不去重。2、执行效率：UNIONALL远高于UNION，因为UNION需要排序去重，消耗大量性能。3、排序规则：UNION会默认排序；UNIONALL不排序，按查询顺序拼接。工作建议：明确无重复数据时，优先使用UNIONALL。4、什么是子查询？子查询分类子查询指嵌套在SELECT、FROM、WHERE、HAVING语句中的查询语句，也叫嵌套查询。主要分为三类：1、标量子查询：返回单行单列数据，可直接用于条件判断。2、列子查询：返回多行单列数据，搭配IN、ANY、ALL使用。3、行子查询：返回单行多列数据，用于多字段匹配。4、表子查询：返回多行多列数据，常放在FROM后作为临时表使用。三、聚合函数与分组查询1、常用聚合函数及NULL处理规则常用聚合函数：COUNT（统计数量）、SUM（求和）、MAX（最大值）、MIN（最小值）、AVG（平均值）。NULL处理核心规则：1、SUM、AVG、MAX、MIN自动忽略NULL值，不参与计算。2、COUNT(*)：统计所有行数，包含NULL数据、重复数据。3、COUNT(字段)：统计该字段非NULL的数据行数。4、COUNT(1)：和COUNT(*)效果基本一致，统计表总行数，无字段判断，效率更高。2、GROUPBY分组查询的使用规范1、SELECT后的非聚合字段，必须出现在GROUPBY后面，否则会报错（严格模式下）。2、分组后过滤条件只能用HAVING，不能用WHERE。3、GROUPBY多个字段时，会按照字段组合唯一进行分组。四、MySQL索引核心面试题（重点）1、什么是索引？索引的优缺点索引是数据库为数据表字段建立的有序数据结构（MySQL默认B+树），用于快速检索数据。优点：1、大幅提升查询、排序、分组的效率，减少全表扫描。2、唯一索引可以保证字段数据唯一性。3、加速联表查询的匹配效率。缺点：1、占用磁盘存储空间，索引也是独立的数据文件。2、降低写入、更新、删除效率，数据变动时，索引需要同步维护更新。总结：索引适合读多写少的字段，频繁更新的字段不建议建索引。2、MySQL索引分类及区别1、主键索引：唯一且非空，一张表只能有一个主键索引，优先级最高。2、唯一索引：字段数据唯一，允许为NULL，一张表可以有多个唯一索引。3、普通索引：最基础的索引，无唯一性限制，仅用于加速查询。4、联合索引（复合索引）：多个字段组合建立的索引，遵循最左前缀原则。5、全文索引：用于长文本模糊查询，替代低效的LIKE%xxx%。3、什么是最左前缀原则？如何失效联合索引(a,b,c)的最左前缀原则：查询条件必须匹配最左侧的连续字段，索引才会生效。生效场景：wherea=?、wherea=?andb=?、wherea=?andb=?andc=?失效场景：1、跳过最左字段，直接查询b、c字段，索引完全失效。2、中间字段范围查询，后面字段索引失效，例如wherea=?andb>?andc=?，c字段索引失效。4、索引失效的常见场景（高频）1、联合索引不满足最左前缀原则。2、索引字段使用函数、运算、类型转换，例如whereDATE(create_time)=?、whereid+1=?3、LIKE查询以%开头，例如%张三、%张三%，索引失效；张三%索引生效。4、OR连接条件，只要其中一个字段无索引，整体索引失效。5、字段隐式类型转换，字符串字段传数字、数字字段传字符串。6、MySQL判断全表扫描比索引查询更快时，会放弃索引（数据量少、重复值多）。五、数据库事务与锁机制1、什么是事务？事务的四大特性（ACID）事务是一组SQL语句的执行单元，要么全部执行成功，要么全部执行失败回滚，保证数据一致性。四大特性：1、原子性(A)：事务不可分割，整体成功或整体失败。2、一致性(C)：事务执行前后，数据库数据状态合法、一致，无异常数据。3、隔离性(I)：多个事务并发执行时，相互独立、互不干扰。4、持久性(D)：事务提交后，数据永久写入数据库，宕机不丢失。2、MySQL四大事务隔离级别及问题隔离级别从低到高：1、读未提交：能读取其他事务未提交数据，存在脏读、不可重复读、幻读。2、读已提交（MySQL默认）：只能读取已提交数据，解决脏读，存在不可重复读、幻读。3、可重复读：同一事务多次查询结果一致，解决脏读、不可重复读，存在幻读。4、串行化：最高隔离级别，事务串行执行，解决所有并发问题，性能最差。3、脏读、不可重复读、幻读的区别1、脏读：一个事务读取到另一个事务未提交的脏数据，数据可能回滚，读取结果无效。2、不可重复读：同一事务内，两次查询同一数据，结果不一致（被其他事务修改更新）。3、幻读：同一事务内，多次查询数据行数不一致（被其他事务新增/删除数据）。六、SQL优化实战面试题1、日常SQL优化的核心思路1、避免全表扫描：高频查询字段建立合理索引，遵循最左前缀原则。2、优化查询语句：禁止SELECT*，只查询需要的字段；避免索引字段用函数、like左匹配、or失效场景。3、分页优化：大分页场景避免limit100000,10，用子查询、覆盖索引优化。4、联表优化：小表驱动大表，优先用INNERJOIN，合理使用LEFTJOIN，避免多余联表。5、减少临时表、排序：避免不必要的GROUPBY、ORDERBY，减少文件排序。2、SELECT*为什么不推荐使用？1、查询多余字段，增加网络传输、磁盘IO开销，降低查询效率。2、无法触发覆盖索引，会产生回表查询，消耗性能。3、后期表结构新增字段后，业务查询会被动接收多余数据，可能引发程序异常。3、大分页limitoffset,size卡顿如何优化？问题原因：offset数值越大，数据库需要遍历的前置数据越多，耗时越长。优化方案：1、覆盖索引+子查询：先通过索引查询出分页主键，再关联查询完整数据。2、主键分页：用whereid>上一页最后idlimitsize，替代偏移量分页，适合有序自增主键场景。七、实战手写SQL题（带答案）基础测试表：用户表user(id,username,age,create_time)、订单表order(id,user_id,order_price,order_time)1、查询所有有订单的用户信息PlainText

SELECTDISTINCTu.*FROMuseru

INNERJOIN`order`oONu.id=o.user_id;2、查询每个用户的订单总数、总消费金额（无订单显示0）PlainText

SELECTu.id,u.username,

COUNT(o.id)ASorder_num,

IFNULL(SUM(o.order_price),0)AStotal_price

FROMuseru

LEFTJOIN`order`oONu.id=o.user_id

GROUPBYu.id,u.username;3、查询近30天的订单数据PlainText

SELECT*FROM`order`

WHEREorder_time>=DATE_SUB(NOW(),INTERVAL30DAY);4、查询消费金额最高的前3名用户PlainText

SELECTu.id,u.username,SUM(o.order_price)AStotal_price

FROMuseru

LEFTJOIN`order`oONu.id=o.user_id

GROUPBYu.id,u.username

HAVINGtotal_priceISNOTNULL

ORDERBYtotal_priceDESC

LIMIT3;5、删除表中重复数据，保留最新一条PlainText

DELETEt1FROM`order`t1,`order`t2

WHEREt1.user_id=t2.user_id

ANDt1.order_time<t2.order_time;八、高频冷门补充题1、什么是覆盖索引？覆盖索引指查询的所有字段，全部包含在索引中，数据库无需回表查询原始数据，直接通过索引返回结果，查询效率极高。例：建立索引(a,b)，查询selecta,bfromtablewherea=?即为覆盖索引。2、InnoDB和MyISAM引擎的区别1、