1. 先搞清楚SQL到底在學什么1.1 你以為的SQL和實際要用的SQL可能不是一回事很多人一聽到“SQL基本操作”腦子里浮現(xiàn)的是大學數(shù)據(jù)庫課本里那一堆CREATE、DROP、GRANT、REVOKE。真到了工作中才發(fā)現(xiàn)日常打交道最多的其實是查詢、插入、更新、刪除這四類操作也就是常說的增刪改查。至于授權(quán)、備份、恢復、調(diào)參這些事大部分時候有DBA或運維同事頂著普通開發(fā)、數(shù)據(jù)分析、產(chǎn)品運營能把查詢寫好就已經(jīng)能解決工作中絕大多數(shù)問題了。我用一個比較直白的分類來幫大家建立整體認知。SQL語言按功能大致可以分成四類分類全稱代表命令日常工作占比DQL數(shù)據(jù)查詢語言SELECT最高超過一半DML數(shù)據(jù)操作語言INSERT、UPDATE、DELETE較高DDL數(shù)據(jù)定義語言CREATE、ALTER、DROP偶爾用到DCL數(shù)據(jù)控制語言GRANT、REVOKE很少用到這個表不是想讓你背概念而是想告訴你學習精力要花在刀刃上。先把SELECT練得滾瓜爛熟再掌握增刪改的基本姿勢建表改表了解常見用法權(quán)限控制交給專業(yè)的人。這跟我?guī)氯说乃悸芬恢隆視屝氯讼然ㄒ恢軙r間把各種查詢寫明白而不是上來就研究怎么建存儲過程、怎么調(diào)索引。1.2 數(shù)據(jù)庫選型MySQL、SQL Server、SQLite到底學哪個看熱搜詞就能發(fā)現(xiàn)大家的關(guān)注點分布在MySQL、SQL Server、SQLite這幾個方向上。這里我想先給一個結(jié)論SQL基本操作的語法在主流數(shù)據(jù)庫里七八成是通用的。標準SQL的SELECT、WHERE、GROUP BY、ORDER BY、JOIN在MySQL、PostgreSQL、SQL Server、SQLite里寫法幾乎一樣。真正不同的地方主要在函數(shù)名、分頁寫法、自增主鍵的定義方式這些細節(jié)上。比如分頁查詢MySQL用LIMITSQL Server用OFFSET FETCH或TOPSQLite也支持LIMIT比如字符串拼接MySQL用CONCATSQL Server用加號SQLite用||。這些差異確實煩人但如果你把標準SQL的部分打扎實換數(shù)據(jù)庫也就是查一下函數(shù)手冊的事。我平時用得最多的是MySQL線上生產(chǎn)環(huán)境也是MySQL為主偶爾用SQLite做本地數(shù)據(jù)處理。所以這篇文章的示例統(tǒng)一用MySQL語法來寫涉及SQL Server或SQLite有差異的地方我會單獨標注說明。這樣不管你在哪個數(shù)據(jù)庫上練習思路都能跟得上。2. 建庫建表所有SQL操作的地基2.1 建庫前先想清楚字符集和排序規(guī)則我第一次接手項目的時候上來就CREATE DATABASE連字符集都沒指定結(jié)果存中文一切正常后來做模糊查詢時發(fā)現(xiàn)明明條件是對的就是查不出來。排查了半天發(fā)現(xiàn)是建庫時用了默認的latin1字符集中文被存成了亂碼。從那以后我建庫一定顯式指定字符集。以MySQL為例一個穩(wěn)妥的建庫語句長這樣CREATE DATABASE IF NOT EXISTS school DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;utf8mb4是utf8的超集能存emoji也能存生僻字。雖然你可能覺得用不到但線上環(huán)境一旦定了字符集后期想改牽連很大所以一步到位更省心。COLLATE里的ci是case insensitive的縮寫意思是排序和比較時不區(qū)分大小寫這也是比較符合業(yè)務直覺的配置。如果你在SQL Server上對應的概念是實例排序規(guī)則比如Chinese_PRC_CI_AS思路是一樣的。2.2 用學生、課程、成績?nèi)龔埍碜咄ㄋ谢静僮鲗WSQL基本操作最忌諱的就是只看不練。為了讓你后面每個案例都有場景可以跑我設計一套特別經(jīng)典的三張表學生表、課程表、成績表。這套表我在不同公司帶新人時用過很多次知識點覆蓋很全面。學生表保存學生基礎信息CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 學生ID, stu_no VARCHAR(20) NOT NULL UNIQUE COMMENT 學號, stu_name VARCHAR(50) NOT NULL COMMENT 姓名, gender CHAR(1) DEFAULT 1 COMMENT 性別1男 2女, birth_date DATE NULL COMMENT 出生日期, phone VARCHAR(20) NULL COMMENT 手機號, create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 創(chuàng)建時間 ) COMMENT 學生表;課程表保存課程信息CREATE TABLE course ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 課程ID, course_no VARCHAR(20) NOT NULL UNIQUE COMMENT 課程編號, course_name VARCHAR(100) NOT NULL COMMENT 課程名稱, credit DECIMAL(3,1) DEFAULT 0 COMMENT 學分 ) COMMENT 課程表;成績表保存學生每門課的考試成績CREATE TABLE score ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 主鍵, stu_id INT NOT NULL COMMENT 學生ID, course_id INT NOT NULL COMMENT 課程ID, score DECIMAL(5,1) NOT NULL COMMENT 成績滿分100, exam_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 考試時間, UNIQUE KEY uk_stu_course (stu_id, course_id) ) COMMENT 成績表;可能有人問為什么要建三個表不是兩個甚至一個就夠了嗎。直接建一張大寬表把學生信息和成績放一起寫起來確實簡單。但這樣做會有兩個問題一是學生信息重復存儲一個學生選了五門課姓名性別就存了五遍浪費空間不說改一次要改多行二是數(shù)據(jù)更新容易不一致改了一行漏了一行后面對賬就頭疼。拆成三張表是關(guān)系型數(shù)據(jù)庫的基本設計套路也是你后面理解JOIN連接的重要鋪墊。2.3 主鍵、唯一鍵、索引的第一步認知建表語句里出現(xiàn)了PRIMARY KEY、UNIQUE KEY這里我講得淺一點但你要建立正確認知。主鍵是每行數(shù)據(jù)的唯一標識你可以把它理解為身份證號一表只能有一個主鍵且主鍵不允許為空。唯一鍵保證某一列或某幾列的組合值不重復比如成績表里的(stu_id, course_id)就是確保同一個學生同一門課只能錄一次成績。至于索引現(xiàn)在你只需要知道它是數(shù)據(jù)庫為了加速查詢而維護的一種數(shù)據(jù)結(jié)構(gòu)可以類比成書的目錄。沒有索引的查詢是全表掃描相當于從第一頁翻到最后一頁有索引的查詢相當于先查目錄再翻到對應頁。我建議剛?cè)腴T的人先不要把注意力放在索引原理上但要養(yǎng)成一個習慣作為查詢條件的字段尤其是經(jīng)常出現(xiàn)在WHERE和JOIN條件里的字段盡量加上索引。等后面遇到慢SQL了再深入研究執(zhí)行計劃那是進階的事。3. 增刪改數(shù)據(jù)別把線上庫當測試環(huán)境3.1 INSERT插入數(shù)據(jù)的三種姿勢插入數(shù)據(jù)是最容易理解的操作但寫法上有不少細節(jié)值得說。第一種是單條插入指定字段名和值INSERT INTO student (stu_no, stu_name, gender, birth_date, phone) VALUES (2024001, 張偉, 1, 2000-01-15, 13800001111);第二種是一次插入多條VALUES后面跟多個括號INSERT INTO student (stu_no, stu_name, gender, birth_date, phone) VALUES (2024002, 李娜, 2, 2001-03-22, 13800002222), (2024003, 王強, 1, 2000-07-08, 13800003333), (2024004, 趙敏, 2, 2001-11-30, 13800004444);第三種是從查詢結(jié)果直接插入常用于數(shù)據(jù)處理和表結(jié)構(gòu)遷移INSERT INTO student_archive (stu_no, stu_name, gender, birth_date, phone) SELECT stu_no, stu_name, gender, birth_date, phone FROM student WHERE create_time 2024-01-01;第三種用法我工作中用到過很多次。比如要臨時把線上某些數(shù)據(jù)導到分析庫或者做一張報表中間表直接從業(yè)務表里查出來灌進去比先導成文件再導進去要高效得多。要注意的是INSERT SELECT不會檢查目標表和源表的數(shù)據(jù)是否有約束沖突如果目標表有唯一鍵插入重復數(shù)據(jù)會直接報錯。3.2 UPDATE更新數(shù)據(jù)血淚教訓就是WHERE更新操作我見過太多翻車現(xiàn)場包括我自己剛?cè)胄袝r也干過。有一次要給一批用戶改等級寫UPDATE的時候忘了加WHERE條件執(zhí)行完后發(fā)現(xiàn)全表用戶的等級都被改了。當時真的冷汗直流還好是測試環(huán)境如果線上那后果不是寫個檢討就能了事的。正確的更新語句必須帶WHEREUPDATE student SET phone 13900001111 WHERE stu_no 2024001;這里有個很重要的習慣要培養(yǎng)UPDATE和DELETE語句里WHERE條件一定要先寫再回頭寫SET和表名。我寫更新語句的習慣是先寫UPDATE student SET phone 13900001111 WHERE stu_no 2024001;然后執(zhí)行之前再反復看一遍WHERE確認它圈定的范圍就是自己真正想改的范圍。還有一點如果一條UPDATE要改多個字段SET后面用逗號分隔就行UPDATE student SET phone 13900001111, gender 2 WHERE stu_no 2024001;有些人會問如果不小心忘帶了WHERE有沒有辦法回滾。答案取決于是否開啟了事務以及數(shù)據(jù)變更是否已經(jīng)提交。這就要引出事務的概念。3.3 DELETE、TRUNCATE和DROP的區(qū)別刪數(shù)據(jù)前先冷靜刪除操作有三兄弟長得像但脾氣完全不同。DELETE是逐行刪除可以帶WHERE條件只刪符合條件的行。DELETE執(zhí)行后不會重置自增ID也就是說你刪了最后一行再插入一條新數(shù)據(jù)ID會繼續(xù)往下跳。而且DELETE操作可以通過事務回滾在InnoDB引擎下如果還沒COMMIT有機會恢復。TRUNCATE是把整張表清空不能帶WHERE條件執(zhí)行效率比DELETE高很多原理是直接丟棄表數(shù)據(jù)頁再重新分配。TRUNCATE之后自增ID會歸零而且不能通過事務回滾在MySQL中隱式提交。DROP更狠直接刪除整個表的結(jié)構(gòu)和數(shù)據(jù)神仙難救。操作能否帶WHERE能否回滾自增IDDELETE能能事務內(nèi)不重置TRUNCATE不能基本不能重置DROP不能不能表都沒了我的實操建議是刪除數(shù)據(jù)之前先SELECT一遍同樣的WHERE條件確認刪的就是目標數(shù)據(jù)。如果可能先備份再刪比如CREATE TABLE student_bak_20250101 AS SELECT * FROM student WHERE 條件。這一步多花十秒能避免很多無法挽回的事故。3.4 事務與提交為什么推薦手動開啟事務的概念我用一個生活場景來解釋轉(zhuǎn)賬。A給B轉(zhuǎn)100塊數(shù)據(jù)庫要執(zhí)行兩步操作A的賬戶扣100B的賬戶加100。這兩步要么都成功要么都失敗不能出現(xiàn)只扣錢不加錢的情況。事務就是保證一系列操作作為一個整體來執(zhí)行。MySQL的InnoDB引擎下事務的ACID特性是數(shù)據(jù)庫可靠性的基礎。對于多行更新或刪除建議手動控制事務START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id 1; UPDATE account SET balance balance 100 WHERE user_id 2; COMMIT;如果執(zhí)行過程中發(fā)現(xiàn)某一步出錯了可以ROLLBACK回滾讓所有修改都不生效。我個人的習慣是涉及金額、庫存、狀態(tài)變更等多表聯(lián)動的數(shù)據(jù)操作一定會包上事務。查數(shù)據(jù)雖然也可以開事務但不那么必要默認的自動提交模式足夠了。4. 查詢基本功解決80%的日常取數(shù)需求4.1 SELECT執(zhí)行順序越早理解越少走彎路查詢絕對是SQL的重頭戲。很多人寫SQL能跑出結(jié)果但一旦報錯就懵了尤其是涉及多個子句組合的時候往往是因為沒搞懂SELECT語句的執(zhí)行順序。一條完整的查詢大概是這樣的SELECT stu_name, gender FROM student WHERE create_time 2024-01-01 GROUP BY stu_name, gender HAVING COUNT(*) 1 ORDER BY create_time DESC LIMIT 10;你可能以為SQL引擎是從SELECT開始讀的實際上執(zhí)行順序完全不是這樣。邏輯上的執(zhí)行順序大概是FROM確定數(shù)據(jù)來源先找到表WHERE過濾行級條件GROUP BY按字段分組HAVING過濾分組后的條件SELECT確定要輸出的列ORDER BY排序LIMIT分頁截取理解這個順序最大的好處是抓SQL報錯時能快速定位是哪一步出了問題。比如你寫了WHERE COUNT(*) 1數(shù)據(jù)庫會報錯因為WHERE在GROUP BY之前執(zhí)行此時聚合結(jié)果還不存在所以聚合條件必須放在HAVING里。這種錯誤新手經(jīng)常遇到理解了執(zhí)行順序就可以避免。4.2 WHERE條件與空值處理的三個大坑WHERE是查詢中最常用的子句除了常規(guī)的、、、、、比較運算符之外還有幾個高頻場景要注意。第一個坑是NULL的判斷。NULL在SQL里表示“未知”或“沒有值”它不等于空字符串也不等于0。判斷某列是否為NULL必須用IS NULL或IS NOT NULL不能用 NULL或! NULL。曾經(jīng)有新人寫了個查詢想找沒填手機號的用戶條件是WHERE phone ! 結(jié)果該用戶手機號是NULL這條記錄怎么都查不出來。要查出沒填手機號的用戶正確寫法是SELECT * FROM student WHERE phone IS NULL OR phone ;第二個坑是LIKE模糊查詢。%表示任意長度的任意字符_表示單個任意字符。比如查詢姓張的學生SELECT * FROM student WHERE stu_name LIKE 張%;注意如果數(shù)據(jù)量很大LIKE %關(guān)鍵詞%這種寫法即使有索引也未必能命中因為前導百分號會導致索引失效。這個細節(jié)在后面慢SQL排查部分我再展開。第三個坑是IN和NOT IN。IN后面跟一個值列表或子查詢結(jié)果比如查一班和二班的學生SELECT * FROM student WHERE class_id IN (1, 2);用NOT IN的時候要特別小心如果列表里包含NULL結(jié)果可能不符合直覺。因為NULL參與比較時結(jié)果是UNKNOWN不是TRUE所以帶NULL的NOT IN子查詢可能會返回空結(jié)果。建議用NOT EXISTS替代SELECT * FROM student s WHERE NOT EXISTS ( SELECT 1 FROM score sc WHERE sc.stu_id s.id );4.3 去重的兩種方式DISTINCT和GROUP BY別混用去重是個高頻需求。熱搜詞里“sql語句去重查詢”、“清洗---sql語句去重”都指向這個問題。最直接的是DISTINCTSELECT DISTINCT gender FROM student;這條語句返回學生表里所有不重復的性別。DISTINCT可以作用于多列意思是多列組合起來不重復SELECT DISTINCT class_id, gender FROM student;另一種去重方式是GROUP BY它本身的功能是分組聚合但也可以實現(xiàn)去重效果SELECT class_id, gender FROM student GROUP BY class_id, gender;兩條語句返回結(jié)果很相似但語義不同。DISTINCT強調(diào)的是“結(jié)果集去重”GROUP BY強調(diào)的是“按字段分組”如果你還需要統(tǒng)計每組的數(shù)量只能用GROUP BY。如果是單純?nèi)ブ谼ISTINCT就夠了別畫蛇添足。還要講一個實用場景刪除表里的重復數(shù)據(jù)。比如成績表因為重復導入產(chǎn)生了重復記錄思路是先找出每組保留的最小ID再刪除不在最小ID列表里的記錄DELETE FROM score WHERE id NOT IN ( SELECT MIN(id) FROM score GROUP BY stu_id, course_id );這里要提醒一下MySQL里DELETE的子查詢不能直接引用目標表會報“You cant specify target table for update in FROM clause”的錯誤。解決辦法是套一層臨時表DELETE FROM score WHERE id NOT IN ( SELECT * FROM ( SELECT MIN(id) FROM score GROUP BY stu_id, course_id ) tmp );4.4 排序與分頁LIMIT的偏移問題要注意ORDER BY用于排序默認為升序ASC降序要寫DESC。多字段排序時從左到右依次作為主次關(guān)鍵字比如先按班級排再按成績從高到低排SELECT stu_id, course_id, score FROM score ORDER BY course_id, score DESC;分頁常用LIMITMySQL和SQLite都支持。LIMIT的兩種寫法SELECT * FROM student ORDER BY id LIMIT 10; -- 返回前10行 SELECT * FROM student ORDER BY id LIMIT 10 OFFSET 20; -- 跳過20行返回10行也可以簡寫成LIMIT 20, 10表示從第21行開始取10行。這里有一個性能陷阱當OFFSET值很大的時候比如LIMIT 100000, 10數(shù)據(jù)庫仍然要先把前面10萬行找出來再丟棄效率很低。更優(yōu)的做法是記住上一頁最后一條記錄的ID或排序字段值用WHERE條件來定位SELECT * FROM student WHERE id 上一頁最后一條的ID ORDER BY id LIMIT 10;這種“鍵集分頁”方式不管翻到第幾頁查詢效率都穩(wěn)定。在做后臺列表分頁時我優(yōu)先用這種方式。5. 聚合分組從明細數(shù)據(jù)到統(tǒng)計報表5.1 五個聚合函數(shù)COUNT、SUM、AVG、MAX、MIN聚合函數(shù)是讓SQL從“查數(shù)據(jù)”升級為“算數(shù)據(jù)”的關(guān)鍵。常用的就五個COUNT統(tǒng)計行數(shù)SUM求和AVG求平均值MAX求最大值MIN求最小值比如統(tǒng)計學生總數(shù)、成績總分、平均分、最高分、最低分SELECT COUNT(*) AS total_students, COUNT(phone) AS has_phone_students FROM student;COUNT(*)統(tǒng)計的是所有行數(shù)COUNT(phone)統(tǒng)計的是phone不為NULL的行數(shù)這個差別非常實用。再比如統(tǒng)計某門課的情況SELECT COUNT(*) AS exam_count, SUM(score) AS total_score, AVG(score) AS avg_score, MAX(score) AS max_score, MIN(score) AS min_score FROM score WHERE course_id 1;5.2 GROUP BY分組統(tǒng)計新手最容易報錯的地方分組統(tǒng)計的原理就是按指定的列把數(shù)據(jù)切成不同的組然后對每組進行聚合計算。比如統(tǒng)計每個學生的選課數(shù)量SELECT stu_id, COUNT(*) AS course_count FROM score GROUP BY stu_id;這里有一個高頻報錯點也就是網(wǎng)上經(jīng)常說的“only_full_group_by mode”。在MySQL 5.7及以上版本默認開啟這個模式要求SELECT列表中的非聚合列必須出現(xiàn)在GROUP BY中。比如下面這條就是典型錯誤-- 錯誤的寫法 SELECT stu_name, COUNT(*) FROM score JOIN student ON score.stu_id student.id GROUP BY score.stu_id;報錯提示大概是“which isnt in GROUP BY”因為stu_name并沒有被GROUP BY。正確寫法是把stu_name加進GROUP BY-- 正確的寫法 SELECT student.stu_name, COUNT(*) FROM score JOIN student ON score.stu_id student.id GROUP BY student.stu_name;或者如果你想統(tǒng)計的是“每個學生且每門課”的組合那GROUP BY可以寫多個字段。理解這個報錯的關(guān)鍵還是回到SELECT執(zhí)行順序GROUP BY先執(zhí)行SELECT后執(zhí)行。分組之后每個組內(nèi)的stu_name并不是唯一的數(shù)據(jù)庫不知道選哪個值輸出干脆報錯讓你自己做決定。5.3 HAVING和WHERE的邊界一句話講明白WHERE在分組前過濾數(shù)據(jù)HAVING在分組后過濾分組。什么時候用哪個記住一句話過濾條件里如果涉及聚合結(jié)果必須用HAVING普通行級過濾用WHERE。比如找出平均分大于80分的學生SELECT stu_id, AVG(score) AS avg_score FROM score GROUP BY stu_id HAVING AVG(score) 80;如果想把不及格的成績先排除掉再統(tǒng)計平均分就用WHERE先過濾SELECT stu_id, AVG(score) AS avg_score FROM score WHERE score 60 GROUP BY stu_id HAVING AVG(score) 80;注意WHERE和HAVING同時出現(xiàn)時執(zhí)行的先后順序先WHERE過濾掉不及格的數(shù)據(jù)再GROUP BY分組最后HAVING過濾平均分。5.4 分組統(tǒng)計實戰(zhàn)課程成績分析把前面這些知識合起來做一個綜合的統(tǒng)計任務統(tǒng)計每門課的選課人數(shù)、平均分、最高分、最低分、及格率。SELECT c.course_name, COUNT(*) AS exam_count, ROUND(AVG(s.score), 2) AS avg_score, MAX(s.score) AS max_score, MIN(s.score) AS min_score, ROUND(SUM(CASE WHEN s.score 60 THEN 1 ELSE 0 END) / COUNT(*), 4) AS pass_rate FROM score s JOIN course c ON s.course_id c.id GROUP BY c.course_name;其中SUM(CASE WHEN ... THEN 1 ELSE 0 END)這個寫法很實用它能把滿足條件的行數(shù)統(tǒng)計出來。這里也可以用IF函數(shù)簡寫MySQL寫法是SUM(IF(s.score 60, 1, 0))效果一樣。這種條件聚合我?guī)缀趺恐芏紩玫健?. 多表連接從單表查詢走向關(guān)聯(lián)分析6.1 連接的本質(zhì)笛卡爾積與關(guān)聯(lián)條件單表查詢解決了大部分基礎需求但業(yè)務數(shù)據(jù)往往分散在多張表里想把學生姓名和成績放在一起看就必須用連接。連接的本質(zhì)可以理解為先把兩張表做笛卡爾積再根據(jù)關(guān)聯(lián)條件篩選出有效組合。所謂笛卡爾積就是第一張表的每一行都去匹配第二張表的每一行。比如學生表有4行成績表有8行笛卡爾積就是32行其中很多是沒有意義的組合。加上關(guān)聯(lián)條件ON之后就只保留兩邊匹配上的行。6.2 INNER JOIN、LEFT JOIN、RIGHT JOIN怎么選最常用的連接類型是INNER JOIN和LEFT JOIN。INNER JOIN只返回兩表都匹配上的行。比如查詢有成績記錄的學生及其成績SELECT s.stu_name, c.course_name, sc.score FROM score sc JOIN student s ON sc.stu_id s.id JOIN course c ON sc.course_id c.id;LEFT JOIN以左表為主返回左表的全部行右表沒有匹配的就補NULL。比如查詢所有學生的選課情況即使沒選過課的學生也要列出來SELECT s.stu_name, sc.score FROM student s LEFT JOIN score sc ON sc.stu_id s.id;沒選課的學生score字段會顯示NULL。RIGHT JOIN和LEFT JOIN是鏡像關(guān)系以右表為主。因為LEFT JOIN已經(jīng)能滿足絕大多數(shù)場景所以我個人基本不寫RIGHT JOIN完全可以用交換兩表順序的LEFT JOIN替代。有一個容易搞混的點ON條件里除了關(guān)聯(lián)條件還能寫過濾條件。比如LEFT JOIN時想把某些無效成績過濾掉可以寫在ON里但這時候要小心。ON條件在關(guān)聯(lián)階段用WHERE條件在關(guān)聯(lián)完成之后用兩者的結(jié)果可能不同。以LEFT JOIN student和score為例ON里加score.deleted 0左表未匹配的行依然會保留WHERE里加score.deleted 0未匹配的行會被過濾掉LEFT JOIN就變成了INNER JOIN的效果。6.3 JOIN和子查詢什么時候用哪個子查詢就是嵌套在查詢里的查詢常見的是WHERE后面的標量子查詢和FROM后面的派生表。比如查沒有考試成績的學生SELECT * FROM student WHERE id NOT IN ( SELECT DISTINCT stu_id FROM score );那什么時候用JOIN什么時候用子查詢呢。結(jié)合我自己的經(jīng)驗給出幾條實用建議。如果子查詢只是用來做條件過濾不涉及返回子查詢里的字段子查詢可讀性更好比如上面的NOT IN和NOT EXISTS場景。如果子查詢返回的字段也要輸出到結(jié)果集那直接JOIN更合適。比如查學生及其最高分用子查詢反而別扭JOIN分組更直接。MySQL的查詢優(yōu)化器在大多數(shù)情況下會把子查詢改寫成連接方式執(zhí)行性能差距不會特別大所以優(yōu)先考慮可讀性。這里還想多說一句子查詢也可以放在SELECT后面當標量用。比如查詢每個學生信息及其平均分SELECT s.stu_name, (SELECT AVG(sc.score) FROM score sc WHERE sc.stu_id s.id) AS avg_score FROM student s;這種方式在數(shù)據(jù)量大時可能性能不佳因為每一行都要執(zhí)行一次子查詢相當于循環(huán)查庫。能改寫成JOIN聚合的場景盡量改寫成JOIN。6.4 多表統(tǒng)計實戰(zhàn)每個學生的總成績與選課數(shù)來看一個實際需求統(tǒng)計每個學生的姓名、選課數(shù)、總成績并按總成績降序排列。SELECT s.stu_name, COUNT(sc.course_id) AS course_count, ROUND(SUM(sc.score), 2) AS total_score FROM student s LEFT JOIN score sc ON sc.stu_id s.id GROUP BY s.id, s.stu_name ORDER BY total_score DESC;這里有個細節(jié)如果學生沒有選課SUM函數(shù)的結(jié)果是NULL而不是0所以ORDER BY時NULL會被排在最前面還是最后面不同數(shù)據(jù)庫表現(xiàn)不一樣。如果想統(tǒng)一處理可以用IFNULL或COALESCE把NULL轉(zhuǎn)成0SELECT s.stu_name, COUNT(sc.course_id) AS course_count, COALESCE(ROUND(SUM(sc.score), 2), 0) AS total_score FROM student s LEFT JOIN score sc ON sc.stu_id s.id GROUP BY s.id, s.stu_name ORDER BY total_score DESC;COALESCE是標準SQL函數(shù)返回參數(shù)列表中第一個非NULL的值比IFNULL通用性更好。7. 進階操作窗口函數(shù)與慢SQL排查7.1 窗口函數(shù)入門ROW_NUMBER、RANK、SUM OVER窗口函數(shù)是SQL進階的一道分水嶺也是這輪熱搜里的高頻詞。它本質(zhì)上是在不改變結(jié)果集行數(shù)的前提下對每一行計算一個聚合值或排名值。和GROUP BY最大的區(qū)別是GROUP BY會把多行合并成一行窗口函數(shù)不會。最常用的場景是分組排名。比如給每個學生的成績排名SELECT stu_id, course_id, score, ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY score DESC) AS rn FROM score;這條SQL按照課程分組組內(nèi)按成績降序排名。ROW_NUMBER是連續(xù)排名哪怕成績相同排名也不同RANK是跳躍排名成績相同排名一樣但下一個排名會跳號比如1、2、2、4DENSE_RANK是連續(xù)排名成績相同并列為2下一個繼續(xù)是3。還有一個很實用的窗口函數(shù)場景是累計求和。比如統(tǒng)計每個學生的累計成績SELECT stu_id, course_id, score, SUM(score) OVER (PARTITION BY stu_id ORDER BY exam_time) AS cumulative_score FROM score;窗口函數(shù)剛開始上手會覺得語法別扭但只要記住固定套路聚合函數(shù)/排名函數(shù) OVER(PARTITION BY 分組字段 ORDER BY 排序字段)大部分需求都能套進去。它特別適合解決“分組內(nèi)TOP N”、“分組內(nèi)排名”、“累計值計算”這類問題用了窗口函數(shù)SQL會簡潔很多。7.2 常用字符串與日期處理函數(shù)寫SQL時字符串和日期的處理是日常高頻操作這里我整理幾個最常用的足以覆蓋大部分場景。字符串處理常用的有CONCAT拼接、SUBSTRING截取、UPPER/LOWER大小寫轉(zhuǎn)換、LENGTH/LEN長度計算、REPLACE替換、TRIM去空格。比如查詢學生的姓名和姓名長度SELECT stu_name, CHAR_LENGTH(stu_name) AS name_length FROM student;日期處理方面DATE_FORMAT格式化、DATE_ADD日期加、DATEDIFF日期差這三個最常用。比如查詢2024年注冊的學生按月統(tǒng)計數(shù)量SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) AS cnt FROM student WHERE create_time 2024-01-01 AND create_time 2025-01-01 GROUP BY DATE_FORMAT(create_time, %Y-%m) ORDER BY month;日期處理的注意事項是區(qū)間過濾。查某一天的數(shù)據(jù)更穩(wěn)妥的寫法是用 當天零點 AND 第二天零點而不是用BETWEEN。因為BETWEEN包含兩個端點如果字段是DATETIME類型且有時間部分BETWEEN 2024-01-01 AND 2024-01-31會把1月31日寫TIME的23:59:59之后的數(shù)據(jù)排除掉。寫成 2024-01-01 AND 2024-02-01就不會有這種問題。7.3 慢SQL排查的基本思路EXPLAIN和索引慢SQL優(yōu)化是熱搜詞里比較進階的話題我把新手能上手的排查思路講一下。遇到一條SQL跑得特別慢第一步不是猜而是用EXPLAIN看執(zhí)行計劃EXPLAIN SELECT * FROM score WHERE stu_id 1;不同數(shù)據(jù)庫的EXPLAIN輸出格式不同MySQL主要看幾個關(guān)鍵列type、key、rows。type列表示訪問類型從好到差大致是const、eq_ref、ref、range、index、ALL。ALL就是全表掃描通常意味著沒走索引是需要警惕的。key表示實際用到的索引。rows是預估掃描的行數(shù)越小越好。慢SQL的常見原因我整理成一張速查表慢SQL癥狀常見原因調(diào)整方向WHERE條件查得慢沒建索引給WHERE字段建索引JOIN查得慢關(guān)聯(lián)字段沒索引給關(guān)聯(lián)字段建索引LIKE %關(guān)鍵詞%掃全表前導百分號導致索引失效考慮全文索引或ESOR條件導致索引失效多個條件無法合并用UNION拆分分頁翻得越多越慢OFFSET過大鍵集分頁返回列太多SELECT *只查需要的列我實測過很多次簡單的WHERE字段加完索引查詢從幾百毫秒降到幾毫秒效果立竿見影。索引不是越多越好寫多寫少都有代價但一開始先把WHERE和JOIN的高頻字段索引建起來性價比最高。7.4 新手常見的幾個低級錯誤我都踩過最后分享幾個我自己和帶過的同學經(jīng)常犯的錯誤希望能幫你省下一些排查時間。第一個是字符串不用引號。寫WHERE stu_no 2024001如果stu_no是VARCHAR類型有些數(shù)據(jù)庫會做隱式轉(zhuǎn)換索引可能失效。規(guī)則很簡單字符串比較一定加單引號。第二個是UPDATE和DELETE忘寫WHERE。這類錯誤前面已經(jīng)反復強調(diào)我這里再換個角度它往往發(fā)生在你“趕時間”的時候。越急越要冷靜寫完后默讀一遍條件再回車。第三個是混淆“空字符串”和“NULL”。這兩種狀態(tài)在數(shù)據(jù)庫里不同前端傳空值到后端時尤其容易踩坑。統(tǒng)一規(guī)范比較好比如手機號沒填就存NULL不要既存在NULL又存在的情況不然查詢時寫條件很麻煩。第四個是SELECT *濫用。查幾條數(shù)據(jù)沒感覺但線上大表SELECT *會把不需要的大字段也讀出來拖慢網(wǎng)絡傳輸和內(nèi)存占用。我建議只寫需要的字段。8. 寫在最后8.1 我日常寫SQL的一些小習慣這篇文章基本把SQL基本操作的主干過了一遍。最后不講大道理分享幾個我實際寫SQL時的習慣。在確認環(huán)境的前提下我總是先想清楚要的數(shù)據(jù)長什么樣再動手寫。數(shù)據(jù)來自幾張表過濾條件有哪些是否需要分組輸出按什么排序。把這幾件事想明白SQL寫起來基本一氣呵成。拿到一個查數(shù)需求我不會急著敲鍵盤而是在草稿紙上畫出表關(guān)系標注清楚關(guān)聯(lián)字段這個小習慣救過我很多次。另外凡是修改操作我都會先查一遍目標數(shù)據(jù)再執(zhí)行變更改完后再查一遍確認結(jié)果符合預期。8.2 給新手最后的一個建議如果你現(xiàn)在還在大學階段或者剛接觸SQL我建議不要只停留在看懂這篇文章的程度而是把文中的三張表建出來把每個示例都親手跑一遍再多給自己出幾個變體題目。比如“查每個學生選課數(shù)超過2門的學生姓名和選課數(shù)”、“查每門課程排名前三的學生”、“查和某個學生同一天出生的同學”。把這些問題用SQL解決之后你的SQL基礎就算真的扎實了后面無論是數(shù)據(jù)分析、后端開發(fā)還是DBA方向都會有很順的開局。