2025年高頻sql基礎(chǔ)面試題及答案1.現(xiàn)有員工表（emp_id員工ID，dept_id部門ID，salary薪資，hire_date入職日期），要求查詢每個(gè)部門薪資最高的員工信息（包括部門ID、員工ID、薪資）。需考慮部門內(nèi)可能有多人并列最高薪資的情況。答案：使用窗口函數(shù)DENSE_RANK按部門分區(qū)并按薪資降序排序，篩選排名為1的記錄。SQL如下：```sqlSELECTdept_id,emp_id,salaryFROM(SELECTdept_id,emp_id,salary,DENSE_RANK()OVER(PARTITIONBYdept_idORDERBYsalaryDESC)ASrkFROMemployee)tWHERErk=1;```關(guān)鍵點(diǎn)：DENSE_RANK處理并列情況（如兩個(gè)員工同部門同最高薪時(shí)均保留），PARTITIONBY按部門分組，ORDERBY確保降序排序。2.訂單表（order_id訂單ID，user_id用戶ID，order_date下單時(shí)間，amount金額），要求統(tǒng)計(jì)2024年每個(gè)用戶的累計(jì)消費(fèi)金額，且只保留累計(jì)金額超過1000元的用戶。答案：使用GROUPBY按用戶分組，SUM聚合金額，HAVING過濾累計(jì)條件。SQL如下：```sqlSELECTuser_id,SUM(amount)AStotal_amountFROMorderWHEREYEAR(order_date)=2024GROUPBYuser_idHAVINGSUM(amount)>1000;```注意點(diǎn)：YEAR函數(shù)提取年份需確保order_date為日期類型；HAVING子句在GROUPBY之后過濾分組結(jié)果，不能用WHERE代替（WHERE過濾行級數(shù)據(jù)，HAVING過濾分組后的數(shù)據(jù)）。3.解釋INNERJOIN、LEFTJOIN、RIGHTJOIN、FULLOUTERJOIN的區(qū)別，并舉例說明。答案：INNERJOIN（內(nèi)連接）僅返回兩表中滿足連接條件的交集記錄；LEFTJOIN（左連接）返回左表所有記錄，右表無匹配時(shí)用NULL填充；RIGHTJOIN（右連接）與LEFTJOIN相反，返回右表所有記錄；FULLOUTERJOIN（全外連接）返回左右表所有記錄，無匹配時(shí)用NULL填充（部分?jǐn)?shù)據(jù)庫如MySQL不支持，需用UNIONALL模擬）。示例：用戶表（user）和訂單表（order），user_id為公共字段。INNERJOIN：`SELECTFROMuserINNERJOINorderONuser.user_id=order.user_id`（僅返回有訂單的用戶）；LEFTJOIN：`SELECTFROMuserLEFTJOINorderONuser.user_id=order.user_id`（返回所有用戶，無訂單用戶的訂單字段為NULL）。4.如何用SQL實(shí)現(xiàn)“查找所有至少購買過兩次的用戶”？涉及表：訂單表（order_id,user_id,order_date）。答案：方法一用GROUPBY和HAVING統(tǒng)計(jì)用戶訂單數(shù)；方法二用窗口函數(shù)COUNT()OVER()計(jì)算每個(gè)用戶的訂單數(shù)。推薦方法一：```sqlSELECTuser_idFROMorderGROUPBYuser_idHAVINGCOUNT(order_id)>=2;```擴(kuò)展：若需去重同一用戶同一天的多次購買（假設(shè)同一天多次購買算一次），需先按user_id和order_date分組去重，再統(tǒng)計(jì)：```sqlWITHunique_ordersAS(SELECTDISTINCTuser_id,order_dateFROMorder)SELECTuser_idFROMunique_ordersGROUPBYuser_idHAVINGCOUNT(order_date)>=2;```5.解釋索引的作用及適用場景，說明聚集索引（ClusteredIndex）和非聚集索引（Non-ClusteredIndex）的區(qū)別。答案：索引通過建立數(shù)據(jù)的快速查找結(jié)構(gòu)（如B+樹），減少全表掃描的IO消耗，提升查詢效率。適用場景：頻繁查詢的列（如WHERE、JOIN條件）、排序或分組的列（如ORDERBY、GROUPBY）。聚集索引決定數(shù)據(jù)在磁盤上的物理存儲順序，一個(gè)表只能有一個(gè)聚集索引（通常為主鍵）；非聚集索引存儲索引鍵和行指針（或聚集索引鍵），數(shù)據(jù)物理順序與索引順序無關(guān)，一個(gè)表可有多級非聚集索引。示例：員工表以emp_id為主鍵（聚集索引），數(shù)據(jù)按emp_id順序存儲；若在dept_id上建立非聚集索引，索引結(jié)構(gòu)存儲dept_id值和對應(yīng)的emp_id（聚集索引鍵），通過emp_id回表查詢完整數(shù)據(jù)。6.現(xiàn)有用戶表（user_id,name,age），其中age字段存在NULL值，要求查詢年齡大于25歲或年齡未知的用戶，如何編寫SQL？答案：NULL值不能用=或!=判斷，需用ISNULL。正確SQL：```sqlSELECTFROMuserWHEREage>25ORageISNULL;```常見錯(cuò)誤：`WHEREage>25ORage=NULL`（NULL是未知值，不能用等號比較）。7.訂單表包含字段（order_id,user_id,create_time,status），其中status表示訂單狀態(tài)（1=未支付，2=已支付，3=已取消）。要求統(tǒng)計(jì)2024年每個(gè)月各狀態(tài)訂單的數(shù)量，結(jié)果按月份升序排列。答案：使用DATE_FORMAT提取月份，GROUPBY分組狀態(tài)和月份，COUNT統(tǒng)計(jì)數(shù)量。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;```優(yōu)化點(diǎn)：用范圍查詢（>=和<）代替YEAR()和MONTH()函數(shù)，避免函數(shù)導(dǎo)致索引失效（若create_time有索引）。8.解釋事務(wù)的ACID特性，并說明MySQL中InnoDB引擎如何實(shí)現(xiàn)這些特性。答案：ACID指原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、持久性（Durability）。原子性：事務(wù)中的操作要么全成功，要么全回滾。InnoDB通過undo日志記錄事務(wù)修改前的數(shù)據(jù)，回滾時(shí)恢復(fù)。一致性：事務(wù)執(zhí)行前后數(shù)據(jù)保持合法狀態(tài)（如約束檢查）。由數(shù)據(jù)庫約束（主鍵、外鍵）、觸發(fā)器及應(yīng)用邏輯保證。隔離性：事務(wù)間互不干擾。InnoDB通過鎖（行鎖、間隙鎖）和MVCC（多版本并發(fā)控制）實(shí)現(xiàn)不同隔離級別（讀未提交、讀已提交、可重復(fù)讀、串行化）。持久性：事務(wù)提交后數(shù)據(jù)永久保存。InnoDB通過redo日志（預(yù)寫日志W(wǎng)AL），提交時(shí)先寫redo日志到磁盤，崩潰時(shí)通過redo日志恢復(fù)數(shù)據(jù)。9.如何優(yōu)化慢查詢？請結(jié)合具體場景說明步驟。答案：優(yōu)化步驟：1.定位慢查詢：通過MySQL的慢查詢?nèi)罩荆╯low_query_log）或EXPLAIN分析執(zhí)行計(jì)劃。2.分析執(zhí)行計(jì)劃：查看是否全表掃描（type=ALL）、索引是否被使用（key字段是否為NULL）、掃描行數(shù)（rows）是否過大。3.優(yōu)化索引：為WHERE條件、JOIN條件、ORDERBY/GROUPBY的列添加索引（避免冗余索引）。例如，查詢`SELECTFROMorderWHEREuser_id=123ANDstatus=2ORDERBYcreate_timeDESC`，可建立(user_id,status,create_time)的復(fù)合索引。4.優(yōu)化查詢語句：避免SELECT（只取需要的列）、減少子查詢（用JOIN代替）、避免在WHERE條件對列使用函數(shù)（如YEAR(create_time)=2024改為create_timeBETWEEN'2024-01-01'AND'2024-12-31'）。5.分頁優(yōu)化：大數(shù)據(jù)量分頁時(shí)，避免`LIMIT100000,20`（需掃描前100000行），改用`WHEREid>last_idLIMIT20`（需id連續(xù)）。10.用SQL實(shí)現(xiàn)“查詢每個(gè)用戶的最近一次下單時(shí)間”，涉及表：訂單表（user_id,order_date）。答案：使用窗口函數(shù)ROW_NUMBER按用戶分區(qū)并按下單時(shí)間降序排序，取排名第1的記錄。SQL如下：```sqlSELECTuser_id,order_dateASlast_order_dateFROM(SELECTuser_id,order_date,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYorder_dateDESC)ASrnFROMorder)tWHERErn=1;```替代方案：GROUPBY+MAX()，但僅能獲取時(shí)間，無法關(guān)聯(lián)其他訂單信息（如訂單ID）。若需完整訂單記錄，窗口函數(shù)更合適。11.解釋UNION和UNIONALL的區(qū)別，何時(shí)使用UNIONALL？答案：UNION會對結(jié)果集去重并排序，UNIONALL直接合并結(jié)果集（包含重復(fù)行）。UNIONALL效率更高（無需去重和排序），適用于確認(rèn)兩個(gè)結(jié)果集無重復(fù)或無需去重的場景（如合并日志表不同月份的數(shù)據(jù)）。示例：合并2024年1月和2月的訂單：```sqlSELECTFROMorder_202401UNIONALLSELECTFROMorder_202402;```若用UNION，數(shù)據(jù)庫會額外執(zhí)行去重操作，影響性能。12.現(xiàn)有員工表（emp_id,name,manager_id），其中manager_id表示該員工的直屬上級ID（NULL表示無上級）。要求查詢所有員工及其直屬上級的姓名（結(jié)果包含員工姓名、上級姓名）。答案：自連接（SELFJOIN），將員工表與自身連接，ON條件為員工的manager_id等于上級的emp_id。SQL如下：```sqlSELECTASemp_name,ASmanager_nameFROMemployeeeLEFTJOINemployeemONe.manager_id=m.emp_id;```關(guān)鍵點(diǎn)：LEFTJOIN確保無上級的員工（manager_id為NULL）也被保留，其manager_name顯示為NULL。13.如何處理SQL注入攻擊？開發(fā)中應(yīng)遵循哪些最佳實(shí)踐？答案：SQL注入通過拼接惡意SQL語句破壞查詢邏輯，常見防護(hù)措施：使用預(yù)編譯語句（PreparedStatement）：參數(shù)用占位符（如?），避免直接拼接用戶輸入（如Java的PreparedStatement，Python的cursor.execute("SELECTFROMuserWHEREid=?",(id,))）。輸入校驗(yàn)：對用戶輸入的類型、長度、格式（如郵箱、手機(jī)號）進(jìn)行嚴(yán)格校驗(yàn)，拒絕非法字符（如單引號、分號）。最小權(quán)限原則：數(shù)據(jù)庫用戶僅授予必要的查詢/修改權(quán)限（如禁止使用DROP、DELETE等危險(xiǎn)操作）。轉(zhuǎn)義特殊字符：若無法使用預(yù)編譯，對用戶輸入的單引號（'）、反斜杠（\）等字符進(jìn)行轉(zhuǎn)義（如MySQL的mysql_real_escape_string()函數(shù)）。14.訂單表（order_id,user_id,amount,create_time），要求查詢2024年每個(gè)季度的總銷售額，結(jié)果按季度排序（Q1、Q2、Q3、Q4）。答案：用QUARTER()函數(shù)提取季度，或用CASEWHEN定義季度范圍。推薦后者（避免函數(shù)對索引的影響）：```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;```關(guān)鍵點(diǎn)：GROUPBY按自定義季度分組，ORDERBY通過CASE將季度字符串轉(zhuǎn)為數(shù)字排序。15.解釋視圖（VIEW）和臨時(shí)表（TEMPORARYTABLE）的區(qū)別及適用場景。答案：視圖是虛擬表，不存儲數(shù)據(jù)，基于SELECT語句定義，每次查詢視圖時(shí)動(dòng)態(tài)執(zhí)行底層SQL并返回結(jié)果；臨時(shí)表是物理存儲的表，數(shù)據(jù)在會話/事務(wù)結(jié)束時(shí)自動(dòng)刪除（或顯式刪除）。適用場景：視圖：簡化復(fù)雜查詢（如多表JOIN）、提供數(shù)據(jù)安全（僅暴露部分列）、保持接口穩(wěn)定（底層表結(jié)構(gòu)變化時(shí)只需修改視圖定義）。臨時(shí)表：存儲中間結(jié)果（如多次使用的聚合數(shù)據(jù)）、減少重復(fù)計(jì)算（避免多次執(zhí)行相同子查詢）、處理需要臨時(shí)存儲的大數(shù)據(jù)集。示例：頻繁查詢“各部門薪資前3的員工”，可創(chuàng)建視圖：```sqlCREATEVIEWtop3_salary_deptASSELECTdept_id,emp_id,salaryFROM(SELECTdept_id,emp_id,salary,ROW_NUMBER()OVER(PARTITIONBYdept_idORDERBYsalaryDESC)ASrnFROMemployee)tWHERErn<=3;```后續(xù)查詢直接`SELECTFROMtop3_salary_dept`即可。16.如何用SQL實(shí)現(xiàn)“查找比所有同事薪資都高的員工”？員工表（emp_id,dept_id,salary）。答案：需確保該員工的薪資大于同部門其他所有員工的薪資。方法一用NOTEXISTS子查詢；方法二用窗口函數(shù)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是部門內(nèi)薪資最高的。17.解釋EXISTS和IN的區(qū)別，哪種情況下性能更優(yōu)？答案：EXISTS檢查子查詢是否返回至少一行（一旦找到匹配行立即停止搜索），IN檢查值是否在子查詢結(jié)果集中（需子查詢返回所有結(jié)果）。當(dāng)子查詢結(jié)果集較大時(shí)，EXISTS通常更高效（無需存儲所有結(jié)果）；當(dāng)主查詢結(jié)果集較小時(shí)，IN可能更高效（取決于索引和數(shù)據(jù)分布）。示例：查詢有訂單的用戶：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.現(xiàn)有學(xué)生表（student_id,name,class_id）和成績表（score_id,student_id,subject,score），要求查詢每個(gè)班級（class_id）數(shù)學(xué)（subject='數(shù)學(xué)'）成績的平均分，結(jié)果按平均分降序排列。答案：先關(guān)聯(lián)兩表獲取班級和數(shù)學(xué)成績，再按班級分組計(jì)算平均。SQL如下：```sqlSELECTs.class_id,AVG(sc.score)ASmath_avgFROMstudentsJOINscorescONs.student_id=sc.student_idWHEREsc.subject='數(shù)學(xué)'GROUPBYs.class_idORDERBYmath_avgDESC;```注意點(diǎn)：JOIN會過濾無數(shù)學(xué)成績的學(xué)生（若需包含無數(shù)學(xué)成績的班級，用LEFTJOIN并處理NULL值：`AVG(IFNULL(sc.score,0))`）。19.解釋索引的最左匹配原則，并舉例說明。答案：復(fù)合索引（如(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)的復(fù)合索引，查詢`WHEREcreate_time='2024-01-01'ANDuser_id=123`會使用索引；查詢`WHEREuser_id=123`則無法使用該索引。20.如何用SQL實(shí)現(xiàn)“統(tǒng)計(jì)連續(xù)3天登錄的用戶”？用戶登錄表（user_id,login_date）。答案：使用窗口函數(shù)計(jì)算每個(gè)用戶登錄日期的排名差，若連續(xù)日期的排名差為2（如日期1、2、3，排名1、2、3，差為3-1=2），則存在連續(xù)3天登錄。SQL如下：```sqlWITHranked_loginsAS(SELECTuser_id,login_date,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYlogin_date)ASrnFROM(SELECTDISTINCTuser_id,logi