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