整理一份 SQL 面试常考知识点 + 典型案例，适合快速复习

SQL 面试知识点与案例这份文档整理 SQL 面试中最常见的知识点、典型查询案例和容易被追问的细节适合用于面试前快速复习。1. SQL 执行顺序SQL 的逻辑执行顺序通常是FROMJOINWHEREGROUPBYHAVINGSELECTORDERBYLIMIT示例SELECTdepartment_id,COUNT(*)AScntFROMemployeesWHEREsalary10000GROUPBYdepartment_idHAVINGCOUNT(*)3ORDERBYcntDESC;含义先从employees表取数据再筛选salary 10000的员工按department_id分组只保留人数不少于 3 的部门最后按人数倒序排序常见追问WHERE和HAVING的区别是什么为什么SELECT中的别名通常不能在WHERE中使用2. JOIN 连接假设有两张表employees(id,name,department_id,salary)departments(id,dept_name)查询员工及其部门名称SELECTe.name,d.dept_nameFROMemployees eJOINdepartments dONe.department_idd.id;常见连接类型JOIN 类型含义INNER JOIN只保留两边都匹配的数据LEFT JOIN保留左表全部数据右表无匹配则为NULLRIGHT JOIN保留右表全部数据左表无匹配则为NULLFULL JOIN保留两边所有数据MySQL 不直接支持案例查询没有部门的员工。SELECTe.*FROMemployees eLEFTJOINdepartments dONe.department_idd.idWHEREd.idISNULL;案例查询没有下过单的用户。SELECTu.*FROMusers uLEFTJOINorders oONu.ido.user_idWHEREo.idISNULL;常见追问LEFT JOIN后面的条件放在ON和WHERE中有什么区别如何避免 JOIN 后数据量被放大3. GROUP BY 聚合查询每个部门的平均工资SELECTdepartment_id,AVG(salary)ASavg_salaryFROMemployeesGROUPBYdepartment_id;查询平均工资大于 15000 的部门SELECTdepartment_id,AVG(salary)ASavg_salaryFROMemployeesGROUPBYdepartment_idHAVINGAVG(salary)15000;WHERE和HAVING的区别关键字执行阶段用途WHERE分组前过滤原始行HAVING分组后过滤聚合结果案例查询订单数大于等于 3 的用户。SELECTuser_id,COUNT(*)ASorder_countFROMordersGROUPBYuser_idHAVINGCOUNT(*)3;4. DISTINCT 与去重统计查询有订单的用户数SELECTCOUNT(DISTINCTuser_id)ASuser_countFROMorders;查询每个部门有多少员工SELECTdepartment_id,COUNT(*)ASemployee_countFROMemployeesGROUPBYdepartment_id;注意COUNT(*)统计所有行COUNT(column)不统计NULLCOUNT(DISTINCT column)统计去重后的非空值5. 子查询查询工资高于公司平均工资的员工SELECT*FROMemployeesWHEREsalary(SELECTAVG(salary)FROMemployees);查询工资高于所在部门平均工资的员工SELECT*FROMemployees eWHEREsalary(SELECTAVG(salary)FROMemployeesWHEREdepartment_ide.department_id);也可以用 JOIN 改写SELECTe.*FROMemployees eJOIN(SELECTdepartment_id,AVG(salary)ASavg_salaryFROMemployeesGROUPBYdepartment_id)tONe.department_idt.department_idWHEREe.salaryt.avg_salary;常见追问相关子查询和非相关子查询有什么区别子查询和 JOIN 哪个性能更好6. 窗口函数窗口函数是 SQL 面试高频重点常用于排名、Top N、累计值、环比等场景。查询每个部门员工工资排名SELECTname,department_id,salary,RANK()OVER(PARTITIONBYdepartment_idORDERBYsalaryDESC)ASsalary_rankFROMemployees;常见排名函数区别函数特点示例排名ROW_NUMBER()不允许并列1, 2, 3RANK()允许并列跳号1, 1, 3DENSE_RANK()允许并列不跳号1, 1, 2案例查询每个部门工资最高的员工。SELECT*FROM(SELECTe.*,ROW_NUMBER()OVER(PARTITIONBYdepartment_idORDERBYsalaryDESC)ASrnFROMemployees e)tWHERErn1;如果要保留并列第一SELECT*FROM(SELECTe.*,RANK()OVER(PARTITIONBYdepartment_idORDERBYsalaryDESC)ASrkFROMemployees e)tWHERErk1;7. Top N 问题查询工资前三名员工SELECT*FROMemployeesORDERBYsalaryDESCLIMIT3;查询每个部门工资前三名员工SELECT*FROM(SELECTe.*,ROW_NUMBER()OVER(PARTITIONBYdepartment_idORDERBYsalaryDESC)ASrnFROMemployees e)tWHERErn3;查询销售额排名前 3 的商品SELECT*FROM(SELECTproduct_id,SUM(order_amount)AStotal_amount,RANK()OVER(ORDERBYSUM(order_amount)DESC)ASsales_rankFROMordersGROUPBYproduct_id)tWHEREsales_rank3;8. 连续登录问题表结构login_log(user_id,login_date)查询连续登录 3 天的用户SELECTDISTINCTuser_idFROM(SELECTuser_id,login_date,DATE_SUB(login_date,INTERVALrnDAY)ASgrpFROM(SELECTuser_id,login_date,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYlogin_date)ASrnFROM(SELECTDISTINCTuser_id,login_dateFROMlogin_log)d)t1)t2GROUPBYuser_id,grpHAVINGCOUNT(*)3;核心思想先对每个用户的登录日期排序用登录日期减去连续序号连续日期会得到相同的分组值对分组计数数量大于等于 3 即表示连续登录 3 天注意如果同一用户一天有多条登录记录需要先去重。9. 订单类经典题表结构orders(id,user_id,product_id,order_amount,order_date)查询每个用户的首单时间SELECTuser_id,MIN(order_date)ASfirst_order_dateFROMordersGROUPBYuser_id;查询每个用户首单金额SELECTuser_id,order_amount,order_dateFROM(SELECTo.*,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYorder_date)ASrnFROMorders o)tWHERErn1;查询每个月销售额SELECTDATE_FORMAT(order_date,%Y-%m)ASmonth,SUM(order_amount)AStotal_amountFROMordersGROUPBYDATE_FORMAT(order_date,%Y-%m)ORDERBYmonth;查询复购用户SELECTuser_idFROMordersGROUPBYuser_idHAVINGCOUNT(*)2;查询每月新增用户数SELECTDATE_FORMAT(first_order_date,%Y-%m)ASmonth,COUNT(*)ASnew_user_countFROM(SELECTuser_id,MIN(order_date)ASfirst_order_dateFROMordersGROUPBYuser_id)tGROUPBYDATE_FORMAT(first_order_date,%Y-%m)ORDERBYmonth;10. 留存率案例用户注册表users(user_id,register_date)登录表login_log(user_id,login_date)查询次日留存用户数SELECTu.register_date,COUNT(DISTINCTu.user_id)ASregister_count,COUNT(DISTINCTl.user_id)ASnext_day_retained_countFROMusers uLEFTJOINlogin_log lONu.user_idl.user_idANDl.login_dateDATE_ADD(u.register_date,INTERVAL1DAY)GROUPBYu.register_date;查询次日留存率SELECTu.register_date,COUNT(DISTINCTl.user_id)/COUNT(DISTINCTu.user_id)ASnext_day_retention_rateFROMusers uLEFTJOINlogin_log lONu.user_idl.user_idANDl.login_dateDATE_ADD(u.register_date,INTERVAL1DAY)GROUPBYu.register_date;注意留存类问题通常使用LEFT JOIN登录表可能有重复记录所以常用COUNT(DISTINCT user_id)条件放在ON中避免把未留存用户过滤掉11. 索引与优化常见索引原则索引可以加快查询但会降低写入速度索引适合建在WHERE、JOIN、ORDER BY、GROUP BY高频字段上联合索引遵循最左前缀原则不要在索引字段上使用函数否则可能导致索引失效LIKE %abc通常无法有效使用普通索引小表、低区分度字段不一定适合建索引创建联合索引CREATEINDEXidx_user_order_dateONorders(user_id,order_date);适合以下查询SELECT*FROMordersWHEREuser_id1001ORDERBYorder_dateDESC;可能导致索引失效的写法SELECT*FROMordersWHEREDATE(order_date)2026-07-01;更好的写法SELECT*FROMordersWHEREorder_date2026-07-01ANDorder_date2026-07-02;常见追问什么是最左前缀原则什么情况下索引会失效如何使用EXPLAIN分析慢 SQL12. EXPLAIN 常看字段示例EXPLAINSELECT*FROMordersWHEREuser_id1001ORDERBYorder_dateDESC;常看字段字段含义type访问类型常见有ALL、index、range、ref、constpossible_keys可能使用的索引key实际使用的索引rows预估扫描行数Extra额外信息如Using filesort、Using temporary一般来说type从差到好大致是ALL index range ref const13. 事务事务四大特性 ACID特性含义Atomicity原子性要么全部成功要么全部失败Consistency一致性事务前后数据满足约束Isolation隔离性事务之间互不干扰Durability持久性提交后数据持久保存转账案例STARTTRANSACTION;UPDATEaccountsSETbalancebalance-100WHEREuser_id1;UPDATEaccountsSETbalancebalance100WHEREuser_id2;COMMIT;如果中途失败ROLLBACK;常见隔离级别隔离级别可能问题READ UNCOMMITTED可能脏读READ COMMITTED避免脏读REPEATABLE READ避免不可重复读MySQL 默认SERIALIZABLE隔离最高性能最低常见并发问题脏读读到了其他事务未提交的数据不可重复读同一事务中两次读取同一行结果不同幻读同一事务中两次范围查询结果行数不同14. NULL 相关问题NULL表示未知不等于任何值也不等于另一个NULL。错误写法SELECT*FROMemployeesWHEREdepartment_idNULL;正确写法SELECT*FROMemployeesWHEREdepartment_idISNULL;判断非空SELECT*FROMemployeesWHEREdepartment_idISNOTNULL;注意COUNT(*)会统计NULL行COUNT(column)不统计该列为NULL的行NOT IN遇到子查询中有NULL时容易出现意外结果更推荐使用NOT EXISTSSELECT*FROMusers uWHERENOTEXISTS(SELECT1FROMorders oWHEREo.user_idu.id);15. 面试高频题清单建议重点练习查询每个部门工资最高的员工查询每个用户最近一次登录记录查询每个用户的第一笔订单查询连续登录 N 天的用户查询销售额排名前 3 的商品查询每个月新增用户数查询复购用户查询没有下单的用户查询订单金额超过平均值的订单查询次日留存率查询每个商品的累计销售额查询环比增长率解释WHERE和HAVING解释LEFT JOIN条件放在ON和WHERE的区别分析一条慢 SQL 并给出优化方案16. 面试回答模板回答 SQL 题时可以按这个顺序表达先说明要用哪些表以及表之间如何关联再说明先过滤什么条件如果需要聚合说明按什么字段分组如果是 Top N优先考虑窗口函数如果涉及性能说明是否需要索引以及索引字段顺序示例回答这题可以先按用户统计订单数再用 HAVING 筛选订单数大于等于 2 的用户。 如果订单表数据量很大可以在 user_id 上建索引方便分组和关联。17. 复习优先级如果时间有限优先掌握JOINGROUP BY和HAVING子查询窗口函数Top N 问题连续登录问题留存率问题索引优化事务与隔离级别NULL处理