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

SQL数据库高频面试题及详细答案（实战版）一、SQL基础概念类（入门高频）1、简述SQL分为哪几类，分别代表什么？答案：SQL主要分为四大类，是日常开发和面试核心基础：1.DQL（数据查询语言）：核心用于查询数据库数据，不修改数据和结构。核心关键字：SELECT、FROM、WHERE、GROUPBY、HAVING、ORDERBY、LIMIT。2.DML（数据操作语言）：用于操作表中的数据，增删改数据。核心关键字：INSERT、UPDATE、DELETE。3.DDL（数据定义语言）：用于定义和修改数据库、数据表结构，不操作数据。核心关键字：CREATE、ALTER、DROP、TRUNCATE。4.DCL（数据控制语言）：用于权限管理和事务控制。核心关键字：GRANT、REVOKE、COMMIT、ROLLBACK。2、DELETE、TRUNCATE、DROP的区别？答案：三者核心差异在操作对象、日志记录、事务支持、自增列、效率五个维度：1.DELETE：DML语句，只删除表数据，保留表结构；支持WHERE条件精准删除；记录事务日志，可回滚；不会重置自增主键；效率最慢，适合批量精准删数据。2.TRUNCATE：DDL语句，清空整张表数据，保留表结构；无WHERE条件，无法指定删除数据；不记录详细日志，不可事务回滚；会重置自增主键；效率极高，适合清空全表无效数据。3.DROP：DDL语句，直接删除整张表（数据+表结构+索引+约束）；彻底释放存储空间；不可回滚；效率最高，适用于废弃表、废弃数据库的删除。3、CHAR和VARCHAR的区别？答案：1.存储方式：CHAR是固定长度，定义多少位就占用多少存储空间；VARCHAR是可变长度，仅占用数据实际长度的空间，额外少量字节记录长度。2.空格处理：CHAR会自动补空格填满固定长度，查询时自动截断尾部空格；VARCHAR不会补空格，数据原样存储。3.性能差异：CHAR存取速度更快，无需计算数据长度，但会浪费存储空间；VARCHAR节省空间，存取效率略低。4.使用场景：CHAR适合固定长度数据，如手机号、身份证号、性别；VARCHAR适合长度不固定数据，如用户名、地址、备注。二、SQL查询语法类（面试必考实操）1、WHERE和HAVING的区别？答案：1.执行时机不同：WHERE是分组之前过滤原始数据；HAVING是分组之后过滤分组后的统计结果。2.适用范围不同：WHERE不能使用聚合函数（SUM、COUNT、MAX等）；HAVING专门用于筛选聚合函数结果，必须配合GROUPBY使用。3.执行效率：优先用WHERE过滤原始数据，减少分组数据量，再用HAVING筛选结果，性能最优。示例：统计各部门人数，只展示人数大于3的部门SELECTdept_id,COUNT(*)ASnumFROMemployeeWHEREage>18GROUPBYdept_idHAVINGnum>3;2、GROUPBY分组原理及注意事项？答案：原理：按照指定字段对数据进行归类合并，相同字段值的数据合并为一组，通常搭配聚合函数完成统计。核心注意事项：1.SELECT后的非聚合字段，必须出现在GROUPBY后面，否则语法报错（MySQL宽松模式除外，但不规范）。2.GROUPBY可以多个字段分组，遵循先第一字段、后第二字段的优先级。3.分组后筛选条件只能用HAVING，不能用WHERE。3、UNION和UNIONALL的区别？答案：1.去重差异：UNION会自动去除合并后的重复数据；UNIONALL直接拼接所有数据，不去重。2.排序差异：UNION会默认排序；UNIONALL不排序，保留原有数据顺序。3.性能差异：UNION需要去重排序，消耗资源多，速度慢；UNIONALL无额外运算，效率远高于UNION。4.使用建议：业务中无重复数据时，优先使用UNIONALL提升查询性能。4、内连接、左连接、右连接、全连接的区别？答案：假设有A、B两张关联表：1.INNERJOIN（内连接）：只查询两张表中匹配关联条件的数据，两边不匹配的数据全部过滤。日常业务最常用。2.LEFTJOIN（左连接）：以左表A为基准，左表所有数据全部展示，右表匹配数据显示对应值，不匹配则显示NULL。3.RIGHTJOIN（右连接）：以右表B为基准，右表所有数据全部展示，左表不匹配数据显示NULL。4.FULLJOIN（全连接）：展示左右两张表所有数据，匹配数据正常显示，不匹配字段补NULL（MySQL不支持全连接，可通过UNIONALL拼接左右连接实现）。三、数据库索引类（面试核心难点）1、什么是索引？索引的优缺点？答案：索引是数据库为数据表字段建立的有序数据结构，相当于书籍的目录，用于快速检索数据。优点：1.极大提升数据查询速度，减少数据库IO消耗；2.唯一索引可以保证字段数据唯一性；3.联合索引、覆盖索引可以优化分组、排序、去重操作。缺点：1.占用磁盘存储空间，索引越多，占用空间越大；2.降低增删改效率，数据变更时，需要同步更新索引结构；3.无效索引会增加数据库负担，无收益。2、MySQL有哪些索引类型？各自作用？答案：1.主键索引（PRIMARY）：唯一且非空，一张表只能有一个主键索引，查询优先级最高。2.唯一索引（UNIQUE）：字段数据唯一，允许为NULL，一张表可以有多个唯一索引。3.普通索引（INDEX）：最基础索引，无唯一性限制，仅用于提升查询速度。4.联合索引（复合索引）：对多个字段建立一个索引，遵循最左前缀原则，大幅优化多条件查询。5.覆盖索引：查询的所有字段都包含在索引中，无需回表查询原始数据，性能最优。3、什么是最左前缀原则？联合索引失效场景？答案：最左前缀原则：联合索引(a,b,c)，查询条件必须匹配左侧连续字段，索引才会生效。有效查询：a、a+b、a+b+c；单独查b、c、b+c索引失效。常见索引失效场景：1.联合索引不满足最左前缀；2.索引字段使用函数、运算、类型转换；3.LIKE以%开头模糊查询（%xxx）；4.OR连接的条件中，有字段无索引；5.MySQL判断全表扫描比索引查询更快时，自动放弃索引。四、事务与锁机制（中高级面试必考）1、什么是数据库事务？事务四大特性（ACID）？答案：事务是一组SQL语句的执行单元，要么全部执行成功，要么全部执行失败回滚，保证数据操作的一致性。四大ACID特性：1.原子性（Atomicity）：事务不可分割，整体执行、整体回滚。2.一致性（Consistency）：事务执行前后，数据库数据状态合法、约束有效，数据不错乱。3.隔离性（Isolation）：多个事务并发执行时，相互独立、互不干扰。4.持久性（Durability）：事务提交成功后，数据永久保存，断电、宕机不会丢失。2、MySQL四大事务隔离级别及并发问题？答案：隔离级别从低到高，隔离性越强，并发性能越差：1.读未提交：能读取其他事务未提交数据，存在脏读、不可重复读、幻读，基本不用。2.读已提交（MySQL默认）：只能读取其他事务已提交数据，解决脏读，存在不可重复读、幻读。3.可重复读：同一事务内多次读取同一数据结果一致，解决脏读、不可重复读，存在幻读。4.串行化：事务串行执行，完全解决三类并发问题，并发效率极低，极少使用。三类并发问题释义：脏读：读取到其他事务未提交的无效数据；不可重复读：同一事务内，两次读取同一数据结果不一致；幻读：同一事务内，其他事务新增/删除数据，导致统计结果异常。3、行锁和表锁的区别？InnoDB和MyISAM锁机制差异？答案：1.表锁：操作数据时锁定整张表，其他事务无法操作表中任何数据，开销小、加锁快、并发低。2.行锁：仅锁定操作的单行数据，其他行数据可正常读写，开销大、加锁慢、并发性能高。引擎锁差异：MyISAM：只支持表锁，读锁共享、写锁独占，适合读多写少场景，不支持事务；InnoDB：默认行锁（索引生效时），无索引则降级为表锁，支持事务，支持行级并发，是主流存储引擎。五、SQL优化实战（高薪面试重点）1、日常工作中，SQL优化的核心思路有哪些？答案：1.索引优化：高频查询字段建索引，避免冗余索引、无效索引，遵循最左前缀原则，避免索引失效场景；2.查询语句优化：禁止SELECT*，只查询需要字段；优先WHERE过滤数据，减少分组、连接的数据量；避免子查询嵌套过深；能用UNIONALL不用UNION；3.分页优化：大分页场景避免LIMIT10000,10，采用主键偏移分页优化；4.避免函数操作索引字段：不在索引列做运算、函数转换，防止索引失效；5.表结构优化：合理设计字段类型，用更小的数据类型；大表分库分表、冷热数据分离；答案：1.type：查询类型，性能从优到劣：system>const>eq_ref>ref>range>index>all，日常需保证至少达到range、ref级别，避免all全表扫描；2.key：实际生效的索引，为NULL表示未使用索引；3.rows：数据库预估扫描的数据行数，数值越小性能越好；4.Extra：额外信息，出现Usingfilesort（文件排序）、Usingtemporary（临时表）表示SQL存在性能问题，需要优化。六、经典手写SQL真题（面试实操）基础表结构（通用面试表）：员工表employee(id,name,dept_id,salary,create_time)部门表dept(dept_id,dept_name)1、查询每个部门的员工人数、平均薪资，展示部门名称答案：SELECTd.dept_name,COUNT(e.id)ASuser_num,AVG(e.salary)ASavg_salary

FROMdeptd

LEFTJOINemployeeeONd.dept_id=e.dept_id

GROUPBYd.dept_id,d.dept_name;2、查询薪资高于公司平均薪资的所有员工答案：SELECTname,salaryF