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支持事务，可以回滚；TRUNCATE和DROP不支持事务，执行后无法回滚。3.日志记录：DELETE会逐行记录操作日志，效率低；TRUNCATE和DROP只记录结构操作日志，几乎不记录数据日志，效率极高。4.自增主键：DELETE删除数据后，自增主键会延续之前数值；TRUNCATE清空表后，自增主键重置为初始值。5.使用场景：批量少量删除用DELETE；清空全表数据用TRUNCATE；彻底删除数据表用DROP。3、什么是数据库事务？事务的四大特性（ACID）事务是一组不可分割的SQL操作单元，要么全部执行成功，要么全部执行失败回滚，用于保证业务数据的一致性，比如转账、订单扣款场景。四大特性ACID：1.原子性（Atomicity）：事务是最小执行单元，不可拆分，全部成功或全部失败。2.一致性（Consistency）：事务执行前后，数据库数据的完整性、约束规则不变，不会出现数据错乱。3.隔离性（Isolation）：多个事务并发执行时，相互独立、互不干扰。4.持久性（Durability）：事务提交成功后，数据永久写入数据库，即使服务器宕机也不会丢失。4、数据库四大事务隔离级别及各自问题MySQL默认隔离级别为可重复读，四种级别由低到高排序，隔离性越强，并发性能越差：1.读未提交（ReadUncommitted）：能读取其他事务未提交的数据。存在问题：脏读、不可重复读、幻读。几乎不使用。2.读已提交（ReadCommitted）：只能读取其他事务已提交的数据。解决脏读，存在不可重复读、幻读。Oracle默认级别。3.可重复读（RepeatableRead）：同一个事务内，多次读取同一数据，结果始终一致。解决脏读、不可重复读，存在幻读。MySQL默认级别。4.串行化（Serializable）：最高隔离级别，事务串行执行，禁止并发。解决所有并发问题，性能极低，极少使用。5、脏读、不可重复读、幻读的区别1.脏读：一个事务读取到了另一个事务未提交的临时数据，后续对方回滚，读取的数据无效，属于错误数据。2.不可重复读：同一个事务内，两次查询同一行数据，结果不一致。原因是其他事务中途修改并提交了该行数据。侧重「数据修改更新」。3.幻读：同一个事务内，两次查询数据行数不一致。原因是其他事务新增或删除了数据并提交。侧重「数据新增/删除」。二、查询语法高频题1、WHERE和HAVING的区别1.执行顺序：WHERE在分组（GROUPBY）之前执行，过滤原始数据；HAVING在分组之后执行，过滤分组后的统计结果。2.适用范围：WHERE不能使用聚合函数（SUM、COUNT、MAX等）；HAVING专门用于筛选聚合函数结果，支持聚合条件。3.优先级：WHERE优先过滤数据，减少分组计算的数据量，效率更高；能使用WHERE过滤的条件，绝不使用HAVING。示例：统计各班人数大于30的班级

SELECTclass,COUNT(*)ASnumFROMstudentWHEREage>18GROUPBYclassHAVINGnum>30;2、LIMIT的用法及分页优化LIMIT用于限制查询结果行数，基础语法：LIMIT起始下标,条数。下标从0开始。常规分页：第n页，每页m条→LIMIT(n-1)*m,m。分页问题：数据量越大，偏移量越高，查询越慢，因为数据库会先查询前所有数据，再丢弃偏移部分。优化方案：避免大偏移量分页，采用主键索引分页。

低效写法：SELECT*FROMuserLIMIT10000,10;

高效写法：SELECT*FROMuserWHEREid>10000LIMIT10;3、DISTINCT的作用及注意事项DISTINCT用于去除查询结果中的重复数据，返回唯一数据行。注意要点：1.DISTINCT作用于后面所有字段，不是单个字段，多字段组合重复才会去重。2.去重会触发全表扫描或排序操作，性能较差，大数据量下尽量通过唯一索引、业务逻辑规避重复。3.不能和聚合函数随意混用，语法顺序固定，需放在字段前。4、ORDERBY排序空值问题MySQL中，NULL值排序优先级最高：1.ORDERBY字段ASC（升序）：NULL值排在最前面；2.ORDERBY字段DESC（降序）：NULL值排在最后面。自定义空值排序：可通过IFNULL函数调整，例如将NULL排最后：ORDERBYIFNULL(score,999)ASC;三、联表查询与子查询1、七种联表查询的区别（INNER/LEFT/RIGHT/FULLJOIN）假设有A表、B表，核心差异如下：1.INNERJOIN（内连接）：只查询两张表中匹配关联条件的数据，两边无匹配则不展示。是最常用的连接方式。2.LEFTJOIN（左连接）：以左表为基准，展示左表所有数据，右表匹配不到的字段补NULL。3.RIGHTJOIN（右连接）：以右表为基准，展示右表所有数据，左表匹配不到的字段补NULL。4.FULLJOIN（全连接）：展示两张表所有数据，无匹配字段补NULL。MySQL不支持FULLJOIN，可通过LEFTJOINUNIONRIGHTJOIN实现。其余三种为衍生用法：LEFTJOIN取左表独有、RIGHTJOIN取右表独有、全表独有数据。2、LEFTJOIN后加WHERE和AND的区别面试超级高频易错点，核心差异：执行时机不同、筛选对象不同1.ON+AND：是连接条件，只筛选关联匹配的数据，不会过滤左表数据。左表数据会全部保留，右表不满足条件则补NULL。2.后续WHERE：是过滤结果条件，会对连接后的整张结果集筛选，会过滤掉包含NULL的行，很多场景下会让LEFTJOIN失效，等同于INNERJOIN。举例：查询所有用户，关联订单，只展示订单状态为已完成的记录

正确：userLEFTJOINorderONuser.id=order.uidANDorder.status=1

错误：userLEFTJOINorderONuser.id=order.uidWHEREorder.status=13、子查询的分类及使用注意事项子查询即嵌套查询，分为：1.标量子查询：返回单行单列数据，可直接用于条件判断。2.行子查询：返回单行多列数据。3.列子查询：返回多行单列数据，常搭配IN、ANY、ALL使用。4.表子查询：返回多行多列数据，常搭配FROM使用（派生表）。注意事项：1.子查询必须用括号包裹；2.派生表必须起别名，否则语法报错；3.大数据量下，子查询效率低于联表查询，优先使用JOIN替代子查询。4、UNION和UNIONALL的区别1.去重差异：UNION会自动去除合并后的重复数据；UNIONALL直接拼接所有结果，不去重。2.性能差异：UNION需要排序去重，性能低；UNIONALL无排序、无去重，性能远高于UNION。3.排序规则：UNION会统一排序结果；UNIONALL按原有查询顺序拼接。实战建议：业务确定无重复数据时，一律用UNIONALL，提升查询效率。四、聚合函数与分组1、常用聚合函数及NULL处理规则常用聚合函数：COUNT、SUM、AVG、MAX、MIN。核心规则：所有聚合函数都会忽略NULL值。重点区分COUNT用法：1.COUNT(*)：统计所有行数，包含NULL、重复数据、空行，不会忽略任何数据。2.COUNT(字段)：统计该字段非NULL的行数，忽略字段为NULL的数据。3.COUNT(1)：和COUNT(*)效果一致，无性能差异，MySQL中两者效率基本相同。2、GROUPBY分组的核心规则1.SELECT后的非聚合字段，必须全部出现在GROUPBY后（MySQL严格模式下强制要求）。2.分组后筛选条件必须用HAVING，不能用WHERE。3.分组字段可以多个，按照字段顺序依次分组。五、索引核心面试题1、什么是索引？索引的优缺点索引是数据库为数据表字段建立的有序数据结构，用于快速检索数据，类似书籍目录。优点：大幅提升查询、排序、分组、联表查询的效率。缺点：1.占用磁盘存储空间；2.降低写入、更新、删除效率，因为数据变更时，索引需要同步更新维护；3.索引过多会加重数据库负担。2、MySQL索引分类及区别1.主键索引（PRIMARY）：唯一且非空，一张表只能有一个主键索引，查询优先级最高。2.唯一索引（UNIQUE）：字段值唯一，允许一个NULL值，可建立多个。3.普通索引（INDEX）：最基础索引，无唯一、非空限制，仅用于加速查询。4.联合索引（复合索引）：多个字段组合建立的索引，遵循最左前缀原则。5.全文索引：用于长文本模糊查询，替代低效的LIKE%xxx%。3、联合索引最左前缀原则联合索引(a,b,c)，生效规则：从最左侧字段开始匹配，连续生效，间断则失效。生效场景：wherea=?、wherea=?andb=?、wherea=?andb=?andc=?失效场景：whereb=?、wherec=?、whereb=?andc=?（跳过最左a字段）补充：查询中a无序但连续，依然生效，MySQL会自动优化字段顺序。4、哪些场景会导致索引失效1.违反联合索引最左前缀原则；2.索引字段使用函数、运算、类型转换，如WHEREDATE(create_time)=?、WHEREid+1=?；3.LIKE以%开头模糊查询，如LIKE'%张三'，后置%可生效；4.OR连接非索引字段，OR两侧必须都为索引字段才生效；5.MySQL判断全表扫描比索引查询更快时，会自动放弃索引（数据量过少、数据分布极端）；6.字段为空值过多，离散度极低，索引失效。5、聚簇索引和非聚簇索引的区别MySQLInnoDB引擎专属考点：1.聚簇索引：主键索引就是聚簇索引，索引和数据存储在一起，叶子节点直接存储整行数据。查询速度最快，一张表仅有一个聚簇索引。2.非聚簇索引（二级索引）：普通索引、唯一索引都属于此类，索引和数据分离，叶子节点仅存储索引字段值+主键ID。回表查询：通过二级索引查询时，先找到主键ID，再通过主键索引查询整行数据，这个过程叫回表。覆盖索引：查询字段全部为索引字段，无需回表，性能最优。六、SQL优化面试题1、日常SQL优化的核心思路1.优先优化WHERE条件，精准过滤数据，减少参与计算的数据量；2.合理建立索引，高频查询字段、联表字段、排序分组字段建索引，避免索引失效；3.禁止SELECT*，只查询需要的字段，减少网络传输和内存占用，触发覆盖索引；4.大分页采用主键分页优化，避免大偏移量LIMIT；5.优先用JOIN替代子查询、临时表，减少嵌套查询；6.避免在索引字段使用函数、运算、模糊前置%；7.大批量数据操作尽量批量执行，减少数据库交互次数。核心关键字段：1.type：查询类型，优先级从优到差：system>const>eq_ref>ref>range>index>all，尽量避免all（全表扫描）；2.key：实际生效的索引，为NULL表示未使用索引；3.rows：预估扫描行数，数值越小效率越高；4.Extra：额外信息，出现Usingfilesort（文件排序）、Usingtemporary（临时表）代表存在性能问题，需要优化。3、如何避免Usingfilesort和Usingtemporary1.Usingtemporary：分组、联表查询无索引导致，优化方式：为GROUPBY、JOIN字段建立索引；2.Usingfilesort：ORDERBY排序无索引导致，优化方式：为排序字段建立联合索引，让排序走索引，避免文件排序。七、经典实操面试题（含答案）1、查询数据表中重复的数据需求：查询name字段重复的所有数据答案：

SELECTname,COUNT(*)ASnumFROMuserGROUPBYnameHAVINGnum>1;查询重复的完整行数据：

SELECT*FROMuserWHEREnameIN(SELECTnameFROMuserGROUPBYnameHAVINGCOUNT(*)>1);2、删除数据表中重复数据，保留最新一条假设：user表，name重复，id自增，保留最大id的数据答案：

DELETEu1FROMuseru1,useru2WHERE=ANDu1.id<u2.id;3、实现行转列（经典面试场景）原始数据：学生表（姓名、科目、分数），需要展示为（姓名、语文、数学、英语）格式答案：

SELECT