好的遵照您的要求我將僅依據(jù)提供的項(xiàng)目標(biāo)題“sql每日一題”及相關(guān)關(guān)鍵詞撰寫(xiě)一篇符合所有規(guī)范的、直接可發(fā)布的Markdown格式博文。內(nèi)容將完全圍繞SQL學(xué)習(xí)與實(shí)操展開(kāi)不含任何違禁及敏感信息。1. 為什么我堅(jiān)持做“SQL每日一題”干這行久了你會(huì)發(fā)現(xiàn)SQL這東西看十遍教程不如動(dòng)手寫(xiě)一遍。尤其是面試前突擊、換新數(shù)據(jù)庫(kù)、或者接手老項(xiàng)目的時(shí)候腦子里那點(diǎn)語(yǔ)法早就還給文檔了。我給自己定了個(gè)規(guī)矩每天至少解一道SQL題不求難但求穩(wěn)。這個(gè)習(xí)慣堅(jiān)持了快兩年收獲遠(yuǎn)超預(yù)期。所謂“SQL每日一題”不是什么高深的方法論就是每天拿出一道具體的SQL練習(xí)題可能是去重、可能是窗口函數(shù)、也可能是慢查詢優(yōu)化場(chǎng)景逼著自己用最快的速度寫(xiě)出最優(yōu)解然后復(fù)盤(pán)對(duì)比。它能解決的問(wèn)題很實(shí)在語(yǔ)法生疏、邏輯混亂、對(duì)數(shù)據(jù)庫(kù)特性不了解、以及面試時(shí)手寫(xiě)SQL發(fā)怵。這套內(nèi)容適合誰(shuí)剛?cè)腴T想打牢基礎(chǔ)的新人工作兩三年想提升查詢效率的開(kāi)發(fā)者以及準(zhǔn)備跳槽需要系統(tǒng)性復(fù)習(xí)SQL的面試者。說(shuō)白了只要你的日常工作離不開(kāi)數(shù)據(jù)庫(kù)這個(gè)習(xí)慣都值得養(yǎng)成。我接下來(lái)會(huì)把實(shí)操過(guò)程中總結(jié)的方法、踩過(guò)的坑、以及一些原理解析都掰開(kāi)揉碎講清楚。2. 內(nèi)容選題與思考路徑拆解2.1 每日一題怎么選從高頻場(chǎng)景反推選題是第一步也是最關(guān)鍵的一步。我的原則是“從高頻場(chǎng)景反推”。什么意思就是先去招聘網(wǎng)站、技術(shù)社區(qū)、以及自己平時(shí)的工作日志里收集那些反復(fù)出現(xiàn)的SQL問(wèn)題再按主題分類。我平時(shí)會(huì)維護(hù)一個(gè)題單大致分幾個(gè)方向基礎(chǔ)查詢WHERE、JOIN、GROUP BY、去重與排序DISTINCT、ROW_NUMBER、窗口函數(shù)LAG、LEAD、SUM OVER、子查詢與CTE、性能優(yōu)化慢SQL、索引命中、數(shù)據(jù)清洗空值處理、重復(fù)數(shù)據(jù)剔除。每天輪著來(lái)保證覆蓋面。比如“SQL語(yǔ)句去重”這個(gè)場(chǎng)景就特別值得單獨(dú)練。很多新手一提去重就只會(huì)SELECT DISTINCT但實(shí)際工作中按月去重、按用戶去重、按狀態(tài)去重邏輯完全不一樣。DISTINCT只能去完全重復(fù)的行而ROW_NUMBER()可以在分組內(nèi)部去重這才是高頻需求。我一般會(huì)出一道類似“每個(gè)用戶最近一筆訂單”的題強(qiáng)制自己用窗口函數(shù)而不是GROUP BY去寫(xiě)。2.2 為什么堅(jiān)持“小題大做”式復(fù)盤(pán)一道題寫(xiě)出來(lái)并不算完真正的價(jià)值在復(fù)盤(pán)。我每次做完題都會(huì)問(wèn)自己三個(gè)問(wèn)題這個(gè)SQL能不能去掉一層子查詢能不能用更語(yǔ)義化的函數(shù)替代如果數(shù)據(jù)量放大一千倍這個(gè)寫(xiě)法還扛得住嗎舉一個(gè)真實(shí)的例子。有次我寫(xiě)了這樣一條SQLselect a.id, a.name from users a where a.created_at (select max(created_at) from users where name a.name)功能沒(méi)錯(cuò)查每個(gè)名字下最近創(chuàng)建的用戶。但復(fù)盤(pán)時(shí)發(fā)現(xiàn)如果users表有幾百萬(wàn)行這個(gè)相關(guān)子查詢會(huì)逐行執(zhí)行性能極差。改成窗口函數(shù)一行搞定select id, name from ( select id, name, row_number() over (partition by name order by created_at desc) as rn from users ) t where rn 1這就是“小題大做”的意義。題目本身不難但通過(guò)對(duì)比不同寫(xiě)法把性能差異和表達(dá)方式的優(yōu)劣都暴露出來(lái)了。刷題不只是為了寫(xiě)對(duì)是為了知道“在什么場(chǎng)景下用什么方案是更優(yōu)的”。2.3 從熱搜詞里挖考點(diǎn)你踩過(guò)的坑別人也在踩我會(huì)定期掃一遍搜索熱詞看看大家最近都在查什么。熱搜詞往往能反映真實(shí)的痛點(diǎn)比如“sql語(yǔ)句去重查詢”“慢sql優(yōu)化”“sql server writelog”“navicat for sql server激活碼”這些詞背后都是具體的實(shí)操困境。拿“慢SQL優(yōu)化”來(lái)說(shuō)這是面試和工作中都繞不開(kāi)的硬骨頭。我復(fù)盤(pán)時(shí)專門總結(jié)過(guò)通用套路先看執(zhí)行計(jì)劃再看索引再看SQL寫(xiě)法。具體來(lái)說(shuō)EXPLAIN輸出里哪一行的type是ALL就意味著全表掃描key為NULL就意味著沒(méi)走索引。這些經(jīng)驗(yàn)不通過(guò)大量“做題—踩坑—總結(jié)”的循環(huán)很難內(nèi)化。熱搜詞里還有一類是“sql server writelog”這屬于數(shù)據(jù)庫(kù)日志膨脹問(wèn)題雖然不算標(biāo)準(zhǔn)SQL面試題但工作中遇到會(huì)非常頭疼。我把它也納入每日一題的延伸學(xué)習(xí)因?yàn)榭荚嚥豢疾淮韺?shí)戰(zhàn)不碰。3. 核心SQL場(chǎng)景拆解與實(shí)操要點(diǎn)3.1 去重場(chǎng)景別只會(huì)DISTINCT去重是“SQL每日一題”里出現(xiàn)頻率最高的主題之一也是新老手差距最明顯的地方。DISTINCT適合“完全重復(fù)行去重”。比如查所有不重復(fù)的部門名一行搞定select distinct department_id from employees;但如果是“按某字段分組取每組最新記錄”DISTINCT就無(wú)能為力了。需要靠窗口函數(shù)或自連接。我常用的黃金套路是這樣select * from ( select *, row_number() over (partition by user_id order by create_time desc) as rn from login_log ) t where rn 1;這段SQL的含義非常直觀先按user_id分組在組內(nèi)按create_time倒序編號(hào)最后只保留每組第1行。我用這個(gè)方法處理過(guò)千萬(wàn)級(jí)日志表的去重實(shí)測(cè)性能優(yōu)于NOT EXISTS寫(xiě)法和GROUP BY后取MAX再回表查的寫(xiě)法。再補(bǔ)充一個(gè)容易踩坑的點(diǎn)在MySQL里如果只查一個(gè)字段比如“只統(tǒng)計(jì)不重復(fù)的用戶數(shù)”那直接SELECT COUNT(DISTINCT user_id)最高效。但如果你還想同時(shí)查這個(gè)用戶的某條明細(xì)DISTINCT就幫不上忙了。很多新人在這里卡半天本質(zhì)上是對(duì)“去重粒度”理解不到位。3.2 空值處理NULL比你想象的更陰險(xiǎn)SQL里最容易被忽視的坑就是NULL。NULL不等于0不等于空字符串更不等于FALSE。寫(xiě)“WHERE name ! 張三”時(shí)那些name為NULL的行根本不會(huì)被查出來(lái)因?yàn)镹ULL參與比較的結(jié)果是UNKNOWN不是TRUE。我每日一題里專門安排過(guò)幾道空值處理題。最經(jīng)典的一道select id, coalesce(score, 0) as score from exam_result;COALESCE函數(shù)把NULL替換成0這樣后續(xù)做平均值、合計(jì)才不會(huì)把數(shù)據(jù)帶偏。另一個(gè)常用的是IS NULL判斷比如查出從未登錄過(guò)的用戶select id from users where last_login_time is null;注意這行SQL千萬(wàn)別寫(xiě)成“ NULL”這是新手最常見(jiàn)的語(yǔ)法錯(cuò)誤。實(shí)際工作中空值處理往往還涉及“凈化數(shù)據(jù)源”的場(chǎng)景。有陣子在清洗一份訂單表發(fā)現(xiàn)大量電話號(hào)碼字段是NULL后來(lái)定位是上游接口漏傳了字段。用SQL排查NULL分布范圍的寫(xiě)法select count(*), sum(case when phone is null then 1 else 0 end) as null_cnt from orders;通過(guò)這類題你練的不只是函數(shù)更是數(shù)據(jù)治理的邊緣意識(shí)。3.3 JOIN與子查詢誰(shuí)先誰(shuí)后有講究JOIN是SQL里概念最難講清楚、用起來(lái)最容易出錯(cuò)的部分。我見(jiàn)過(guò)很多同事寫(xiě)LEFT JOIN時(shí)因?yàn)檫^(guò)濾條件放錯(cuò)了位置導(dǎo)致結(jié)果少了數(shù)據(jù)。核心規(guī)則就一條LEFT JOIN右邊的表如果要過(guò)濾條件必須寫(xiě)在ON子句里而不是WHERE里。舉個(gè)例子select a.id, b.order_amount from users a left join orders b on a.id b.user_id and b.status paid;如果把“status paid”移到WHERE里那LEFT JOIN的結(jié)果會(huì)被過(guò)濾掉相當(dāng)于變成了INNER JOIN很多沒(méi)訂單的用戶就丟了。這個(gè)細(xì)節(jié)我至少在三個(gè)項(xiàng)目里幫別人排查過(guò)。另外能不用子查詢就不用子查詢。很多子查詢可以改寫(xiě)成JOIN性能會(huì)好一截。比如“查出每個(gè)分類銷量最高的商品”用窗口函數(shù)方案比用兩層嵌套子查詢簡(jiǎn)潔得多這個(gè)我在2.2節(jié)的例子里已經(jīng)復(fù)盤(pán)過(guò)。每日一題里反復(fù)練JOIN就是為了讓這些判斷變成肌肉記憶。4. 實(shí)操過(guò)程與核心環(huán)節(jié)實(shí)現(xiàn)4.1 本地環(huán)境搭建五分鐘跑起來(lái)搞SQL題本地先有個(gè)能跑的環(huán)境很重要。我現(xiàn)在的配置是MySQL 8.0 Navicat外加一臺(tái)裝著SQL Server 2019的虛擬機(jī)做兼容性驗(yàn)證。千萬(wàn)別只在在線刷題網(wǎng)站上寫(xiě)SQL因?yàn)楹芏囝}要跑真實(shí)執(zhí)行計(jì)劃本地環(huán)境更可控。安裝這塊我提幾個(gè)容易踩的坑MySQL 8.0安裝時(shí)如果選了“Use Strong Password Encryption”老版本Navicat會(huì)連不上建議換成“Use Legacy Password Encryption”。SQL Server 2019安裝失敗八成是權(quán)限或.NET環(huán)境問(wèn)題先裝好.NET Framework 4.8再跑安裝程序。Navicat連不上SQL Server時(shí)先去SQL Server配置管理器里啟用TCP/IP協(xié)議。一段最基礎(chǔ)的建表語(yǔ)句我每天練習(xí)都會(huì)用create table if not exists orders ( id int primary key auto_increment, user_id int not null, product_name varchar(50), amount decimal(10,2), status varchar(20), created_at datetime );然后造一批測(cè)試數(shù)據(jù)用存儲(chǔ)過(guò)程循環(huán)插入一千行左右夠練習(xí)大部分題目了。真實(shí)項(xiàng)目里數(shù)據(jù)量更大但刷題階段用幾百行數(shù)據(jù)驗(yàn)證邏輯對(duì)不對(duì)性價(jià)比最高。4.2 每日一題的完整SOP從讀題到復(fù)盤(pán)我總結(jié)了一套固定執(zhí)行流程每天照著走效率拉滿讀題先把需求拆成年份、單位、過(guò)濾條件三個(gè)要素。比如“查2024年每月的銷售總額”年份是2024單位是月過(guò)濾條件是銷售額。寫(xiě)出第一版想到什么寫(xiě)什么保證正確性優(yōu)先。優(yōu)化檢查能不能去掉子查詢、能不能用窗口函數(shù)、能不能加索引。跑EXPLAIN看執(zhí)行計(jì)劃里有沒(méi)有全表掃描。復(fù)盤(pán)把常用寫(xiě)法和“最優(yōu)寫(xiě)法”記錄到自己的題目庫(kù)。這套流程最大的好處是讓練習(xí)有節(jié)奏感。每天只看一道題知識(shí)點(diǎn)更聚焦但偶爾也會(huì)遇到“這道題有多種解法”的情況那我就把多種解法都跑一遍記錄各自的耗時(shí)匯總成一張對(duì)比表。4.3 索引調(diào)優(yōu)與慢SQL實(shí)戰(zhàn)一道題壓出性能差距“SQL每日一題”如果只練語(yǔ)法天花板很低。我每周會(huì)安排一到兩道性能題專門壓執(zhí)行計(jì)劃。比如這個(gè)案例select * from orders where status paid order by created_at desc limit 10;幾百行數(shù)據(jù)時(shí)毫無(wú)壓力但換到千萬(wàn)級(jí)表這條SQL有可能走全表掃描。原因很簡(jiǎn)單status區(qū)分度不高成本優(yōu)化器覺(jué)得走索引還不如掃全表。優(yōu)化辦法是建立一個(gè)復(fù)合索引alter table orders add index idx_status_created (status, created_at);有了這個(gè)索引WHERE status和ORDER BY created_at都能命中索引執(zhí)行計(jì)劃里的type會(huì)從ALL變成ref或range性能立竿見(jiàn)影。調(diào)優(yōu)過(guò)程中我強(qiáng)烈建議把執(zhí)行計(jì)劃讀透。MySQL里EXPLAIN輸出的關(guān)鍵字段就幾個(gè)type訪問(wèn)類型、key命中的索引、rows預(yù)估掃描行數(shù)、Extra額外信息?？吹健癠sing filesort”就要警覺(jué)說(shuō)明排序沒(méi)走索引看到“Using temporary”說(shuō)明有臨時(shí)表大查詢里很危險(xiǎn)。4.4 SQL Server專項(xiàng)從安裝到日志處理的完整備忘熱搜詞里不少是關(guān)于SQL Server的2022企業(yè)版密鑰、writelog日志、安裝教程這些都是實(shí)戰(zhàn)型問(wèn)題。作為每日一題的一部分我也會(huì)用SQL Server做兼容性驗(yàn)證因?yàn)門-SQL和MySQL語(yǔ)法存在差異比如TOP與LIMIT、GETDATE與NOW()。SQL Server 2019/2022安裝時(shí)比較容易在“SQL Server配置管理器”里卡住。如果安裝后服務(wù)起不來(lái)先去Windows事件查看器看錯(cuò)誤日志大概率是服務(wù)賬號(hào)權(quán)限或端口沖突。安裝完成后記得在“SQL Server網(wǎng)絡(luò)配置”里把TCP/IP協(xié)議啟用否則外網(wǎng)工具連不上。再提一個(gè)“writelog”問(wèn)題。SQL Server的日志文件如果不斷膨脹多半是因?yàn)閿?shù)據(jù)庫(kù)處于“完整恢復(fù)模式”且沒(méi)有定期備份日志。解決思路是alter database 你的庫(kù)名 set recovery simple;切到簡(jiǎn)單模式后日志不再無(wú)限增長(zhǎng)。但這會(huì)犧牲時(shí)間點(diǎn)恢復(fù)能力生產(chǎn)庫(kù)慎用。刷題階段無(wú)所謂但要知道這個(gè)操作的含義。我個(gè)人的建議是本地練習(xí)環(huán)境就裝SQL Server Express版免費(fèi)且夠用配合Navicat或SSMS都很順手。密鑰問(wèn)題在個(gè)人練習(xí)場(chǎng)景其實(shí)不需要糾結(jié)Express版游客登錄就好。5. 常見(jiàn)問(wèn)題與排查技巧實(shí)錄5.1 執(zhí)行計(jì)劃看不懂照著這幾個(gè)字段先掃一眼很多人拿到EXPLAIN輸出就發(fā)懵字段一個(gè)也看不明白。我提供一個(gè)極簡(jiǎn)排查順序字段重點(diǎn)看什么危險(xiǎn)信號(hào)typeconst/ref/range好ALL壞typeALL即全表掃描key命中的索引名key為NULL說(shuō)明沒(méi)走索引rows預(yù)估掃描行數(shù)rows遠(yuǎn)大于預(yù)期需要警惕ExtraUsing index好Using filesort/temporary壞出現(xiàn)filesort要優(yōu)化排序只要這幾項(xiàng)掃一遍大部分慢查詢的死因都能鎖定。再看熱搜詞里“ora-12518”這類Oracle監(jiān)聽(tīng)錯(cuò)誤其實(shí)也屬于排查問(wèn)題思路是查監(jiān)聽(tīng)狀態(tài)、看端口通不通、確認(rèn)服務(wù)是否注冊(cè)成功跟MySQL排查思路大同小異。5.2 遞歸查詢、函數(shù)報(bào)錯(cuò)與注入防范三道讓新手破防的題LAG、LEAD這類窗口函數(shù)考試?？嫉ぷ髦泻芏嗳瞬桓矣?。我前兩天剛復(fù)盤(pán)過(guò)一道求“同比環(huán)比”的題select month, amount, lag(amount, 1) over (order by month) as prev_amount from monthly_sales;這段SQL直接取出前一個(gè)月的銷售額比自連接簡(jiǎn)單太多。窗口函數(shù)最怕的是亂用PARTITION BY我在分析用戶行為數(shù)據(jù)時(shí)踩過(guò)坑PARTITION BY和ORDER BY的順序、組合一旦搞錯(cuò)結(jié)果直接對(duì)不上。還有一類題專門考察“函數(shù)副作用”。比如SQL Server里用MD5加密T-SQL寫(xiě)法是select HASHBYTES(MD5, 123456);這個(gè)函數(shù)返回的是VARBINARY直接輸出是一串不可讀的二進(jìn)制。很多人以為加密后應(yīng)該是一串十六進(jìn)制字符串拿到結(jié)果就先懵了。解決辦法是包一層CONVERT轉(zhuǎn)成VARCHAR或打印十六進(jìn)制select CONVERT(varchar(32), HASHBYTES(MD5,123456), 2);至于SQL注入防護(hù)刷題階段就要建立正確認(rèn)知永遠(yuǎn)不要用拼接字符串搭SQL永遠(yuǎn)走參數(shù)化查詢。面試時(shí)極大概率會(huì)問(wèn)到“萬(wàn)能密碼繞過(guò)”你只要回答“用參數(shù)化查詢避免拼接收窄數(shù)據(jù)庫(kù)賬號(hào)權(quán)限”就已經(jīng)答到點(diǎn)子上了。這個(gè)習(xí)慣不能光背得寫(xiě)進(jìn)每天的SQL練習(xí)里。5.3 常用工具與“激活碼”陷阱正版意識(shí)要從練習(xí)期養(yǎng)成熱搜詞里有一類我不推薦碰的“navicat for sql server激活碼”。這類工具的高級(jí)特性官方試用版基本都能滿足日常練習(xí)沒(méi)必要冒著安全風(fēng)險(xiǎn)去找破解資源。作為從業(yè)者版權(quán)意識(shí)也算基本功之一。工具選型上我推薦一套組合MySQL環(huán)境Navicat或DBeaverDBeaver社區(qū)版免費(fèi)。SQL Server環(huán)境官方SSMS體驗(yàn)可以而且完全免費(fèi)。在線刷題SQLZoo、LeetCode數(shù)據(jù)庫(kù)題庫(kù)隨時(shí)開(kāi)刷。工具只要能跑SQL、看執(zhí)行計(jì)劃、看表結(jié)構(gòu)就夠用了。糾結(jié)于“哪款工具的皮膚好看”純屬浪費(fèi)時(shí)間。我試過(guò)從Toad切到DBeaver又換回Navicat最后還是看功能需求來(lái)決定。每日一題的效率從來(lái)不在工具在思路。6. 把“SQL每日一題”沉淀成自己的題庫(kù)堅(jiān)持了這么長(zhǎng)時(shí)間最大的心得是刷題不是目的積累成自己的知識(shí)庫(kù)才是。我會(huì)給每道題打標(biāo)簽比如“窗口函數(shù)”“去重”“索引優(yōu)化”再用Markdown表格記錄題目描述、我的第一版答案、優(yōu)化后答案、踩坑點(diǎn)。隔一個(gè)月回頭翻比收藏一堆教程管用得多。比如我題庫(kù)里有一條記錄是這樣的標(biāo)簽題目初版寫(xiě)法優(yōu)化寫(xiě)法踩坑點(diǎn)窗口函數(shù)查每個(gè)用戶最近登錄時(shí)間GROUP BY MAX 回表ROW_NUMBER() OVER(PARTITION BY)GROUP BY后無(wú)法取整行回表代價(jià)高這種結(jié)構(gòu)化復(fù)盤(pán)能讓你刷一題精一題而不是刷一百題忘一百題。另外我會(huì)把同主題的題串成一條線比如先練DISTINCT去重再練GROUP BY去重再練ROW_NUMBER去重最后練性能對(duì)比。同一個(gè)業(yè)務(wù)需求用不同SQL實(shí)現(xiàn)理解深度完全不一樣。最后再分享一個(gè)小技巧每周挑一天不打開(kāi)編輯器純手寫(xiě)SQL。模擬面試場(chǎng)景限定五分鐘寫(xiě)出一條“查最近30天內(nèi)下單超過(guò)三次的用戶”的SQL。寫(xiě)完之后先不看資料再想兩個(gè)問(wèn)題這個(gè)SQL的索引命中情況如何如果改窗口函數(shù)會(huì)不會(huì)更好這個(gè)過(guò)程比無(wú)腦刷一百道題有用得多。我試過(guò)之后面試現(xiàn)場(chǎng)手寫(xiě)SQL時(shí)的肌肉記憶都是這么練出來(lái)的。