2025年高频sql基础面试题及答案

2025年高频sql基础面试题及答案1.现有员工表（emp_id员工ID，dept_id部门ID，salary薪资，hire_date入职日期），要求查询每个部门薪资最高的员工信息（包括部门ID、员工ID、薪资）。需考虑部门内可能有多人并列最高薪资的情况。答案：使用窗口函数DENSE_RANK按部门分区并按薪资降序排序，筛选排名为1的记录。SQL如下：```sqlSELECTdept_id,emp_id,salaryFROM(SELECTdept_id,emp_id,salary,DENSE_RANK()OVER(PARTITIONBYdept_idORDERBYsalaryDESC)ASrkFROMemployee)tWHERErk=1;```关键点：DENSE_RANK处理并列情况（如两个员工同部门同最高薪时均保留），PARTITIONBY按部门分组，ORDERBY确保降序排序。2.订单表（order_id订单ID，user_id用户ID，order_date下单时间，amount金额），要求统计2024年每个用户的累计消费金额，且只保留累计金额超过1000元的用户。答案：使用GROUPBY按用户分组，SUM聚合金额，HAVING过滤累计条件。SQL如下：```sqlSELECTuser_id,SUM(amount)AStotal_amountFROMorderWHEREYEAR(order_date)=2024GROUPBYuser_idHAVINGSUM(amount)>1000;```注意点：YEAR函数提取年份需确保order_date为日期类型；HAVING子句在GROUPBY之后过滤分组结果，不能用WHERE代替（WHERE过滤行级数据，HAVING过滤分组后的数据）。3.解释INNERJOIN、LEFTJOIN、RIGHTJOIN、FULLOUTERJOIN的区别，并举例说明。答案：INNERJOIN（内连接）仅返回两表中满足连接条件的交集记录；LEFTJOIN（左连接）返回左表所有记录，右表无匹配时用NULL填充；RIGHTJOIN（右连接）与LEFTJOIN相反，返回右表所有记录；FULLOUTERJOIN（全外连接）返回左右表所有记录，无匹配时用NULL填充（部分数据库如MySQL不支持，需用UNIONALL模拟）。示例：用户表（user）和订单表（order），user_id为公共字段。INNERJOIN：`SELECTFROMuserINNERJOINorderONuser.user_id=order.user_id`（仅返回有订单的用户）；LEFTJOIN：`SELECTFROMuserLEFTJOINorderONuser.user_id=order.user_id`（返回所有用户，无订单用户的订单字段为NULL）。4.如何用SQL实现“查找所有至少购买过两次的用户”？涉及表：订单表（order_id,user_id,order_date）。答案：方法一用GROUPBY和HAVING统计用户订单数；方法二用窗口函数COUNT()OVER()计算每个用户的订单数。推荐方法一：```sqlSELECTuser_idFROMorderGROUPBYuser_idHAVINGCOUNT(order_id)>=2;```扩展：若需去重同一用户同一天的多次购买（假设同一天多次购买算一次），需先按user_id和order_date分组去重，再统计：```sqlWITHunique_ordersAS(SELECTDISTINCTuser_id,order_dateFROMorder)SELECTuser_idFROMunique_ordersGROUPBYuser_idHAVINGCOUNT(order_date)>=2;```5.解释索引的作用及适用场景，说明聚集索引（ClusteredIndex）和非聚集索引（Non-ClusteredIndex）的区别。答案：索引通过建立数据的快速查找结构（如B+树），减少全表扫描的IO消耗，提升查询效率。适用场景：频繁查询的列（如WHERE、JOIN条件）、排序或分组的列（如ORDERBY、GROUPBY）。聚集索引决定数据在磁盘上的物理存储顺序，一个表只能有一个聚集索引（通常为主键）；非聚集索引存储索引键和行指针（或聚集索引键），数据物理顺序与索引顺序无关，一个表可有多级非聚集索引。示例：员工表以emp_id为主键（聚集索引），数据按emp_id顺序存储；若在dept_id上建立非聚集索引，索引结构存储dept_id值和对应的emp_id（聚集索引键），通过emp_id回表查询完整数据。6.现有用户表（user_id,name,age），其中age字段存在NULL值，要求查询年龄大于25岁或年龄未知的用户，如何编写SQL？答案：NULL值不能用=或!=判断，需用ISNULL。正确SQL：```sqlSELECTFROMuserWHEREage>25ORageISNULL;```常见错误：`WHEREage>25ORage=NULL`（NULL是未知值，不能用等号比较）。7.订单表包含字段（order_id,user_id,create_time,status），其中status表示订单状态（1=未支付，2=已支付，3=已取消）。要求统计2024年每个月各状态订单的数量，结果按月份升序排列。答案：使用DATE_FORMAT提取月份，GROUPBY分组状态和月份，COUNT统计数量。SQL如下：```sqlSELECTDATE_FORMAT(create_time,'%Y-%m')ASmonth,status,COUNT(order_id)ASorder_countFROMorderWHEREcreate_time>='2024-01-01'ANDcreate_time<'2025-01-01'GROUPBYmonth,statusORDERBYmonthASC;```优化点：用范围查询（>=和<）代替YEAR()和MONTH()函数，避免函数导致索引失效（若create_time有索引）。8.解释事务的ACID特性，并说明MySQL中InnoDB引擎如何实现这些特性。答案：ACID指原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。原子性：事务中的操作要么全成功，要么全回滚。InnoDB通过undo日志记录事务修改前的数据，回滚时恢复。一致性：事务执行前后数据保持合法状态（如约束检查）。由数据库约束（主键、外键）、触发器及应用逻辑保证。隔离性：事务间互不干扰。InnoDB通过锁（行锁、间隙锁）和MVCC（多版本并发控制）实现不同隔离级别（读未提交、读已提交、可重复读、串行化）。持久性：事务提交后数据永久保存。InnoDB通过redo日志（预写日志WAL），提交时先写redo日志到磁盘，崩溃时通过redo日志恢复数据。9.如何优化慢查询？请结合具体场景说明步骤。答案：优化步骤：1.定位慢查询：通过MySQL的慢查询日志（slow_query_log）或EXPLAIN分析执行计划。2.分析执行计划：查看是否全表扫描（type=ALL）、索引是否被使用（key字段是否为NULL）、扫描行数（rows）是否过大。3.优化索引：为WHERE条件、JOIN条件、ORDERBY/GROUPBY的列添加索引（避免冗余索引）。例如，查询`SELECTFROMorderWHEREuser_id=123ANDstatus=2ORDERBYcreate_timeDESC`，可建立(user_id,status,create_time)的复合索引。4.优化查询语句：避免SELECT（只取需要的列）、减少子查询（用JOIN代替）、避免在WHERE条件对列使用函数（如YEAR(create_time)=2024改为create_timeBETWEEN'2024-01-01'AND'2024-12-31'）。5.分页优化：大数据量分页时，避免`LIMIT100000,20`（需扫描前100000行），改用`WHEREid>last_idLIMIT20`（需id连续）。10.用SQL实现“查询每个用户的最近一次下单时间”，涉及表：订单表（user_id,order_date）。答案：使用窗口函数ROW_NUMBER按用户分区并按下单时间降序排序，取排名第1的记录。SQL如下：```sqlSELECTuser_id,order_dateASlast_order_dateFROM(SELECTuser_id,order_date,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYorder_dateDESC)ASrnFROMorder)tWHERErn=1;```替代方案：GROUPBY+MAX()，但仅能获取时间，无法关联其他订单信息（如订单ID）。若需完整订单记录，窗口函数更合适。11.解释UNION和UNIONALL的区别，何时使用UNIONALL？答案：UNION会对结果集去重并排序，UNIONALL直接合并结果集（包含重复行）。UNIONALL效率更高（无需去重和排序），适用于确认两个结果集无重复或无需去重的场景（如合并日志表不同月份的数据）。示例：合并2024年1月和2月的订单：```sqlSELECTFROMorder_202401UNIONALLSELECTFROMorder_202402;```若用UNION，数据库会额外执行去重操作，影响性能。12.现有员工表（emp_id,name,manager_id），其中manager_id表示该员工的直属上级ID（NULL表示无上级）。要求查询所有员工及其直属上级的姓名（结果包含员工姓名、上级姓名）。答案：自连接（SELFJOIN），将员工表与自身连接，ON条件为员工的manager_id等于上级的emp_id。SQL如下：```sqlSELECTASemp_name,ASmanager_nameFROMemployeeeLEFTJOINemployeemONe.manager_id=m.emp_id;```关键点：LEFTJOIN确保无上级的员工（manager_id为NULL）也被保留，其manager_name显示为NULL。13.如何处理SQL注入攻击？开发中应遵循哪些最佳实践？答案：SQL注入通过拼接恶意SQL语句破坏查询逻辑，常见防护措施：使用预编译语句（PreparedStatement）：参数用占位符（如?），避免直接拼接用户输入（如Java的PreparedStatement，Python的cursor.execute("SELECTFROMuserWHEREid=?",(id,))）。输入校验：对用户输入的类型、长度、格式（如邮箱、手机号）进行严格校验，拒绝非法字符（如单引号、分号）。最小权限原则：数据库用户仅授予必要的查询/修改权限（如禁止使用DROP、DELETE等危险操作）。转义特殊字符：若无法使用预编译，对用户输入的单引号（'）、反斜杠（\）等字符进行转义（如MySQL的mysql_real_escape_string()函数）。14.订单表（order_id,user_id,amount,create_time），要求查询2024年每个季度的总销售额，结果按季度排序（Q1、Q2、Q3、Q4）。答案：用QUARTER()函数提取季度，或用CASEWHEN定义季度范围。推荐后者（避免函数对索引的影响）：```sqlSELECTCASEWHENcreate_time>='2024-01-01'ANDcreate_time<'2024-04-01'THEN'Q1'WHENcreate_time>='2024-04-01'ANDcreate_time<'2024-07-01'THEN'Q2'WHENcreate_time>='2024-07-01'ANDcreate_time<'2024-10-01'THEN'Q3'ELSE'Q4'ENDASquarter,SUM(amount)AStotal_salesFROMorderWHEREcreate_time>='2024-01-01'ANDcreate_time<'2025-01-01'GROUPBYquarterORDERBYCASEquarterWHEN'Q1'THEN1WHEN'Q2'THEN2WHEN'Q3'THEN3ELSE4END;```关键点：GROUPBY按自定义季度分组，ORDERBY通过CASE将季度字符串转为数字排序。15.解释视图（VIEW）和临时表（TEMPORARYTABLE）的区别及适用场景。答案：视图是虚拟表，不存储数据，基于SELECT语句定义，每次查询视图时动态执行底层SQL并返回结果；临时表是物理存储的表，数据在会话/事务结束时自动删除（或显式删除）。适用场景：视图：简化复杂查询（如多表JOIN）、提供数据安全（仅暴露部分列）、保持接口稳定（底层表结构变化时只需修改视图定义）。临时表：存储中间结果（如多次使用的聚合数据）、减少重复计算（避免多次执行相同子查询）、处理需要临时存储的大数据集。示例：频繁查询“各部门薪资前3的员工”，可创建视图：```sqlCREATEVIEWtop3_salary_deptASSELECTdept_id,emp_id,salaryFROM(SELECTdept_id,emp_id,salary,ROW_NUMBER()OVER(PARTITIONBYdept_idORDERBYsalaryDESC)ASrnFROMemployee)tWHERErn<=3;```后续查询直接`SELECTFROMtop3_salary_dept`即可。16.如何用SQL实现“查找比所有同事薪资都高的员工”？员工表（emp_id,dept_id,salary）。答案：需确保该员工的薪资大于同部门其他所有员工的薪资。方法一用NOTEXISTS子查询；方法二用窗口函数MAX()OVER()。推荐方法一：```sqlSELECTe1.emp_id,e1.dept_id,e1.salaryFROMemployeee1WHERENOTEXISTS(SELECT1FROMemployeee2WHEREe2.dept_id=e1.dept_idANDe2.emp_id!=e1.emp_idANDe2.salary>=e1.salary);```逻辑：对于员工e1，不存在同部门其他员工e2的薪资大于等于e1的薪资，即e1是部门内薪资最高的。17.解释EXISTS和IN的区别，哪种情况下性能更优？答案：EXISTS检查子查询是否返回至少一行（一旦找到匹配行立即停止搜索），IN检查值是否在子查询结果集中（需子查询返回所有结果）。当子查询结果集较大时，EXISTS通常更高效（无需存储所有结果）；当主查询结果集较小时，IN可能更高效（取决于索引和数据分布）。示例：查询有订单的用户：EXISTS：`SELECTFROMuseruWHEREEXISTS(SELECT1FROMorderoWHEREo.user_id=u.user_id)`；IN：`SELECTFROMuseruWHEREu.user_idIN(SELECTo.user_idFROMordero)`。若order表user_id有索引，EXISTS逐行检查，效率更高；若user表较小且order表user_id无索引，IN可能更快（先获取所有有订单的user_id再匹配）。18.现有学生表（student_id,name,class_id）和成绩表（score_id,student_id,subject,score），要求查询每个班级（class_id）数学（subject='数学'）成绩的平均分，结果按平均分降序排列。答案：先关联两表获取班级和数学成绩，再按班级分组计算平均。SQL如下：```sqlSELECTs.class_id,AVG(sc.score)ASmath_avgFROMstudentsJOINscorescONs.student_id=sc.student_idWHEREsc.subject='数学'GROUPBYs.class_idORDERBYmath_avgDESC;```注意点：JOIN会过滤无数学成绩的学生（若需包含无数学成绩的班级，用LEFTJOIN并处理NULL值：`AVG(IFNULL(sc.score,0))`）。19.解释索引的最左匹配原则，并举例说明。答案：复合索引（如(col1,col2,col3)）会按顺序使用列，查询条件中需包含索引的最左前缀才能生效。例如：查询`WHEREcol1=1`：使用索引；查询`WHEREcol1=1ANDcol2=2`：使用索引；查询`WHEREcol2=2`：不使用索引（缺少col1）；查询`WHEREcol1=1ANDcol3=3`：使用col1部分索引（col3无法利用）；查询`WHEREcol1=1ANDcol2=2ANDcol3=3`：完整使用索引。示例：为订单表建立(create_time,user_id,status)的复合索引，查询`WHEREcreate_time='2024-01-01'ANDuser_id=123`会使用索引；查询`WHEREuser_id=123`则无法使用该索引。20.如何用SQL实现“统计连续3天登录的用户”？用户登录表（user_id,login_date）。答案：使用窗口函数计算每个用户登录日期的排名差，若连续日期的排名差为2（如日期1、2、3，排名1、2、3，差为3-1=2），则存在连续3天登录。SQL如下：```sqlWITHranked_loginsAS(SELECTuser_id,login_date,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYlogin_date)ASrnFROM(SELECTDISTINCTuser_id,logi