SQL数据库面试题及详细答案（实战完整版）

SQL数据库面试题及详细答案（实战完整版）一、SQL基础面试题（入门必问）1、简述SQL的常用分类及核心语句答案：SQL主要分为四大类，是日常开发和运维的核心基础，具体如下：1、DDL（数据定义语言）：用于定义和管理数据库、数据表结构，不操作数据内容。核心语句：CREATE（创建）、ALTER（修改）、DROP（删除）、TRUNCATE（清空表结构保留、删除数据）。2、DML（数据操作语言）：用于操作表中的数据记录，是开发最常用语句。核心语句：INSERT（新增数据）、UPDATE（修改数据）、DELETE（删除数据）、SELECT（查询数据）。3、DCL（数据控制语言）：用于权限管理和事务控制。核心语句：GRANT（授权）、REVOKE（撤销权限）、COMMIT（提交事务）、ROLLBACK（回滚事务）。4、TCL（事务控制语言）：专门管理数据库事务，保证数据一致性。核心语句：COMMIT、ROLLBACK、SAVEPOINT（设置保存点）。2、DELETE、TRUNCATE、DROP的区别答案：三者核心差异体现在操作对象、日志记录、事务支持、自增列、执行效率五个方面：1、操作对象：DELETE删除表中部分或全部数据，保留表结构；TRUNCATE清空表全部数据，保留表结构；DROP直接删除整张表（结构+数据），释放表占用的所有资源。2、事务支持：DELETE支持事务，可回滚；TRUNCATE、DROP不支持事务，执行后无法回滚。3、日志记录：DELETE逐行记录日志，开销大；TRUNCATE、DROP只记录少量结构日志，开销极小。4、自增列：DELETE删除数据后，自增ID会延续之前数值；TRUNCATE清空后，自增ID重置从1开始。5、执行效率：DROP>TRUNCATE>DELETE。补充：日常删除数据优先用DELETE，清空测试表数据用TRUNCATE，彻底废弃数据表用DROP。3、WHERE和HAVING的区别答案：1、执行时机不同：WHERE是分组前过滤，先筛选数据，再执行GROUPBY分组；HAVING是分组后过滤，先分组聚合，再筛选分组后的结果。2、筛选内容不同：WHERE可以过滤普通字段条件，不支持聚合函数（SUM、COUNT、MAX等）；HAVING专门用于过滤聚合函数结果，也可支持普通字段。3、优先级：WHERE优先级高于GROUPBY，HAVING优先级低于GROUPBY。示例：查询班级人数大于30人的班级。先通过WHERE筛选有效学生，再GROUPBY分组，最后HAVING筛选人数条件。4、DISTINCT的作用及使用注意事项答案：DISTINCT用于去除查询结果中的重复数据，返回唯一不重复的记录。注意事项：1、DISTINCT作用于所有查询字段组合，而非单个字段。若查询多个字段，只有所有字段数据完全一致才会去重。2、该关键字会增加查询开销，数据库会对结果集排序去重，大数据量下尽量少用，可通过索引、条件筛选替代。3、不能和ORDERBY随意乱序搭配，排序字段必须包含在查询字段中。二、数据库核心概念面试题（高频）1、什么是数据库事务？事务的四大特性（ACID）答案：事务是数据库中一组不可分割的SQL操作单元，要么全部执行成功，要么全部执行失败回滚，用于保证数据操作的一致性。四大特性ACID：1、原子性（Atomicity）：事务是最小执行单元，不可拆分，全部成功或全部失败。例如转账操作，扣款和入账必须同时完成，任意一步失败则全部撤销。2、一致性（Consistency）：事务执行前后，数据库数据完整性规则不变。转账前后，两人账户总金额保持不变。3、隔离性（Isolation）：多个事务并发执行时，相互独立、互不干扰，不会互相篡改数据。4、持久性（Durability）：事务提交成功后，数据永久写入数据库，即使服务器宕机、重启，数据也不会丢失。2、数据库四大事务隔离级别及区别（MySQL）答案：MySQL默认隔离级别为可重复读，四种级别从低到高，隔离性越强，并发性能越差：1、读未提交（ReadUncommitted）：最低级别，可读取其他事务未提交的数据。会出现脏读、不可重复读、幻读问题，基本不使用。2、读已提交（ReadCommitted）：只能读取其他事务已提交的数据。解决脏读，存在不可重复读、幻读问题，Oracle默认级别。3、可重复读（RepeatableRead）：同一事务内，多次读取同一数据，结果始终一致。解决脏读、不可重复读，存在幻读问题，MySQL默认级别。4、串行化（Serializable）：最高级别，事务串行执行，完全隔离。解决所有并发问题，无脏读、不可重复读、幻读，但并发效率极低，仅用于金融、支付等极高安全场景。补充：脏读：读取到未提交的回滚数据；不可重复读：同一事务两次读取同一数据，结果不同；幻读：同一事务两次查询，数据条数不一致。3、索引的作用、分类及优缺点答案：索引是数据库用于快速检索数据的磁盘数据结构，类似书籍目录，大幅提升查询效率。常用索引分类（MySQL）：1、主键索引：唯一且非空，一张表只能有一个，主键自动创建主键索引。2、唯一索引：字段数据唯一，允许为空，可创建多个。3、普通索引：最基础索引，无唯一性限制，仅用于加速查询。4、联合索引：多个字段组合创建的索引，遵循最左前缀原则。5、全文索引：用于长文本模糊查询，适配文章、内容检索场景。优点：极大提升数据查询速度；减少数据库磁盘IO消耗。缺点：占用磁盘存储空间；新增、修改、删除数据时，需要同步更新索引，降低写入效率；索引过多会加重数据库维护负担。4、什么是最左前缀原则？联合索引如何生效答案：最左前缀原则是联合索引的核心规则：联合索引生效必须匹配索引的最左侧起始字段。例如创建联合索引（a,b,c）：生效场景：wherea=1、wherea=1andb=2、wherea=1andb=2andc=3；失效场景：whereb=2、wherec=3、whereb=2andc=3（跳过最左字段a）。补充：索引字段出现范围查询（>、<、between、like左模糊），范围查询后的索引字段全部失效。日常设计联合索引，将等值查询字段放前面，范围查询字段放最后。三、多表查询高频面试题1、SQL常用联表查询的区别（内连接、左连接、右连接、全连接）答案：假设存在A表、B表两张关联表：1、内连接（INNERJOIN）：只查询两张表中匹配关联条件的数据，无匹配数据不展示。2、左连接（LEFTJOIN）：以左表（A表）为基准，展示左表所有数据，右表匹配数据展示，不匹配则字段为NULL。3、右连接（RIGHTJOIN）：以右表（B表）为基准，展示右表所有数据，左表匹配数据展示，不匹配则字段为NULL。4、全连接（FULLJOIN）：展示两张表所有数据，匹配数据合并展示，不匹配字段补NULL。MySQL不支持直接FULLJOIN，可通过UNION拼接左连接和右连接结果实现。2、UNION和UNIONALL的区别答案：1、去重差异：UNION会自动去除查询结果中的重复数据；UNIONALL不做去重，直接拼接所有结果。2、效率差异：UNION需要排序去重，性能较低；UNIONALL无排序、无去重，性能远高于UNION。3、排序规则：UNION会默认排序；UNIONALL保持原有查询顺序。实战建议：业务中确认数据无重复时，优先使用UNIONALL，提升查询效率。四、数据库优化面试题（进阶高频）1、SQL查询慢的常见原因及优化方案答案：常见慢查询原因：1、无索引、索引失效；2、查询字段使用*，返回冗余字段；3、大量模糊查询（%前置模糊）；4、联表查询过多、子查询嵌套过深；5、数据库数据量过大，无分页；6、索引设计不合理（冗余索引、无效索引）。核心优化方案：1、避免SELECT*，按需查询所需字段；2、合理创建索引，遵循最左前缀原则，避免索引失效场景；3、模糊查询尽量使用后缀匹配（like'xxx%'），禁止前置匹配（like'%xxx'）；4、大表查询必须分页，避免一次性查询全量数据；5、减少子查询嵌套，优先使用联表查询；6、定期清理无效索引、碎片化数据。2、常见索引失效场景答案：1、联合索引不遵循最左前缀原则，跳过前置字段查询；2、索引字段使用函数、运算操作（如wheresubstring(name,1,2)='xx'、age+1=20）；3、索引字段使用!=、<>、NOTIN、NOTEXISTS；4、字符串字段未加引号，隐式类型转换导致索引失效；5、OR连接的条件中，一侧字段无索引，整体索引失效；6、like前置模糊查询（%xxx）。3、Limit分页深偏移量优化方案答案：问题：当执行limit100000,10时，数据库会先查询前100010条数据，丢弃前100000条，返回后10条，偏移量越大，查询越慢。优化方案：1、主键索引覆盖查询：先通过主键分页，再关联查询完整数据，示例：select*fromtablewhereid>100000limit10；2、避免大页码分页，业务层限制分页最大页数；3、使用延迟查询，利用索引快速定位偏移位置，再关联查询。五、实战手写SQL面试题（带答案）基础数据表：学生表student（id,name,age,class_id）、班级表class（class_id,class_name）、成绩表score（id,student_id,subject,score）1、查询所有学生的姓名、班级名称答案：sql

SELECT,c.class_name

FROMstudents

LEFTJOINclasscONs.class_id=c.class_id;2、查询每个班级的学生人数，展示班级名、人数答案：sql

SELECTc.class_name,COUNT(s.id)ASstudent_num

FROMclassc

LEFTJOINstudentsONc.class_id=s.class_id

GROUPBYc.class_name;3、查询平均分大于80分的学生姓名、平均分答案：sql

SELECT,AVG(sc.score)ASavg_score

FROMstudents

LEFTJOINscorescONs.id=sc.student_id

GROUPBY

HAVINGAVG(sc.score)>80;4、删除成绩表中重复的学生成绩（保留最新一条）答案：sql

DELETEs1FROMscores1,scores2

WHEREs1.student_id=s2.student_id

ANDs1.subject=s2.subject

ANDs1.id<s2.id;六、数据库高频冷门面试题1、什么是死锁？如何避免和解决死锁答案：死锁是两个或多个事务互相持有对方需要的锁，同时等待对方释放锁，导致所有事务无限阻塞，无法执行。产生条件：互斥条件、请求保持条件、不可剥夺条件、环路等待条件。解决和避免方案：1、统一数据表操作顺序