2026年3月4日上午MySQL 學(xué)習(xí)筆記第 34 節(jié)創(chuàng)建數(shù)據(jù)庫與運(yùn)行各類 SQL。這句話如果放在以前我可能會(huì)覺得只是例行記錄但今天把它當(dāng)標(biāo)題認(rèn)真寫下來其實(shí)是在提醒自己數(shù)據(jù)庫入門階段最容易忽略的恰恰是這些每天都要用的基礎(chǔ)操作。很多人裝完 MySQL 之后第一反應(yīng)是趕緊去查“SELECT 怎么寫”“JOIN 怎么連”結(jié)果連數(shù)據(jù)庫都還沒建出來。今天上午我給自己定的任務(wù)很簡(jiǎn)單也很扎實(shí)先創(chuàng)建一個(gè) bookstore 數(shù)據(jù)庫再建一張 books 表插入幾條數(shù)據(jù)最后把增刪改查完整跑一遍。這條路徑走完SQL 的基本盤就穩(wěn)了。1. 動(dòng)筆之前先把 SQL 的類型理清楚1.1 SQL 四大分類每一類到底在管什么剛開始學(xué) SQL 的人最容易犯的毛病是“見什么敲什么”完全不看這句話屬于哪一類。實(shí)際開發(fā)中這條語句屬于哪一類決定了你的操作范圍、影響對(duì)象以及能不能回滾。DDLData Definition Language數(shù)據(jù)定義語言CREATE、ALTER、DROP、TRUNCATE。管的是表結(jié)構(gòu)、數(shù)據(jù)庫結(jié)構(gòu)這類元數(shù)據(jù)執(zhí)行后一般立即生效很多操作沒法簡(jiǎn)單回滾。DMLData Manipulation Language數(shù)據(jù)操作語言INSERT、UPDATE、DELETE。管的是表里的數(shù)據(jù)是日常開發(fā)寫最多的語句。DQLData Query Language數(shù)據(jù)查詢語言SELECT。雖然也有資料把它歸到 DML 里但單獨(dú)拎出來更好理解它不改變數(shù)據(jù)。DCLData Control Language數(shù)據(jù)控制語言GRANT、REVOKE。管的是用戶權(quán)限。至于COMMIT、ROLLBACK這類事務(wù)控制有的資料單獨(dú)列為 TCL。我建議你在練習(xí)時(shí)先問自己一句這會(huì)改結(jié)構(gòu)、改數(shù)據(jù)還是只是看數(shù)據(jù)答案不同對(duì)待方式也不同。改結(jié)構(gòu)的語句要格外謹(jǐn)慎改數(shù)據(jù)的語句先確認(rèn) WHERE查數(shù)據(jù)的語句隨便練出錯(cuò)最多浪費(fèi)一點(diǎn)時(shí)間。1.2 學(xué)習(xí)路徑為什么要按“庫→表→數(shù)據(jù)→查詢”走數(shù)據(jù)庫里的一切操作都離不開一個(gè)前提你得先有庫和表。沒有庫你的 SQL 不知道該往哪執(zhí)行沒有表數(shù)據(jù)就沒有存放的容器。今天上午我安排的順序是建庫 → 建表 → 插數(shù) → 查詢 → 更新與刪除。每一步都是下一步的基礎(chǔ)。比如只有先建好表你才能談插入數(shù)據(jù)時(shí)字段類型是否匹配只有插入了幾行數(shù)據(jù)你才能驗(yàn)證查詢條件到底寫沒寫對(duì)。這種遞進(jìn)關(guān)系比單純背語法要牢固得多。提示學(xué)習(xí)階段可以大膽建庫刪庫但進(jìn)入公司項(xiàng)目后不管是 DDL 還是 DML都要先想清楚影響范圍。尤其 DDL很多都沒有“撤銷”按鈕。2. 創(chuàng)建數(shù)據(jù)庫一條 CREATE DATABASE 背后的門道2.1 標(biāo)準(zhǔn)語法與每個(gè)參數(shù)的含義創(chuàng)建數(shù)據(jù)庫的語法很簡(jiǎn)單不過簡(jiǎn)單不代表可以隨便寫CREATE DATABASE [IF NOT EXISTS] bookstore CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;拆開看中括號(hào)里的IF NOT EXISTS表示如果同名庫已經(jīng)存在就不報(bào)錯(cuò)。第一次練習(xí)時(shí)我嫌它多余后來批量腳本跑多了才發(fā)現(xiàn)這個(gè)保護(hù)很有用腳本重復(fù)執(zhí)行不會(huì)中斷。bookstore是庫名。庫名最好用小寫字母、數(shù)字和下劃線別用減號(hào)、空格也不要跟 SQL 保留字撞車。CHARACTER SET utf8mb4指定字符集COLLATE utf8mb4_general_ci指定排序規(guī)則。CHARACTER SET和COLLATE其實(shí)可以不寫MySQL 會(huì)采用配置文件里的默認(rèn)值。但我們剛學(xué)習(xí)我建議每次都寫出來。理由有兩個(gè)一是不依賴服務(wù)器環(huán)境換臺(tái)機(jī)器跑結(jié)果一樣二是以后從備份文件恢復(fù)數(shù)據(jù)庫時(shí)你靠SHOW CREATE DATABASE能一眼看出當(dāng)初的設(shè)計(jì)意圖。2.2 為什么建議選 utf8mb4而不是直接寫 utf8這是今天上午我自己最容易踩的坑。很多新手看到utf8就以為萬事大吉結(jié)果 MySQL 里的utf8是utf8mb3每個(gè)字符最多 3 個(gè)字節(jié)存不了四字節(jié)的 emoji就連某些特殊漢字和生僻字也容易出問題。utf8mb4才是完整的 UTF-8 編碼每個(gè)字符最多 4 個(gè)字節(jié)能覆蓋 emoji、生僻字等更廣的字符集。所以從 5.5.3 開始引入后新項(xiàng)目基本都默認(rèn)utf8mb4。MySQL 8.0 的默認(rèn)值也已經(jīng)是utf8mb4但這不代表你可以忽略它。排序規(guī)則可以簡(jiǎn)單理解為字符比較和排序的方式。utf8mb4_general_ci里的ci是 case insensitive 的縮寫也就是大小寫不敏感適合中英文混合場(chǎng)景。如果你對(duì)排序規(guī)則有更高要求可以了解utf8mb4_unicode_ci和utf8mb4_0900_ai_ci但初學(xué)階段記住utf8mb4_general_ci已經(jīng)足夠穩(wěn)妥。再提醒一個(gè)連帶影響因?yàn)閡tf8mb4每個(gè)字符最多占 4 字節(jié)所以一個(gè)被索引的VARCHAR(255)字段在舊版 InnoDB 里容易觸發(fā)“索引鍵太長(zhǎng)”的錯(cuò)誤Specified key was too long。設(shè)計(jì)字段長(zhǎng)度時(shí)得把字符集因素算進(jìn)去。2.3 查看、切換與修改數(shù)據(jù)庫的基本命令創(chuàng)建完不是結(jié)束至少要會(huì)三件事看、切、改。-- 查看所有數(shù)據(jù)庫 SHOW DATABASES; -- 查看庫的創(chuàng)建語句看字符集和排序規(guī)則 SHOW CREATE DATABASE bookstore; -- 切換到目標(biāo)庫 USE bookstore; -- 修改庫的字符集 ALTER DATABASE bookstore CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;SHOW DATABASES輸出的結(jié)果里除了剛建的bookstore還有 MySQL 自帶的幾個(gè)系統(tǒng)庫像information_schema、mysql、performance_schema這些不建議亂動(dòng)。USE這條命令很關(guān)鍵。很多人寫完CREATE DATABASE后直接在下一行敲建表語句結(jié)果報(bào)No database selected原因就是沒切庫。切庫之后你后續(xù)的建表、插數(shù)、查詢才都能落在bookstore里。至于ALTER DATABASE它修改的是庫級(jí)別的字符集和排序規(guī)則不影響已經(jīng)建好的表。想要讓已存在的表也改過來需要單獨(dú)對(duì)每張表執(zhí)行ALTER TABLE。這個(gè)細(xì)節(jié)我后面還會(huì)提到先記上一筆。3. 建表實(shí)戰(zhàn)把字段類型和約束一次弄明白3.1 常用數(shù)據(jù)類型怎么選才不容易翻車建表之前先看表結(jié)構(gòu)。今天我用了一張極簡(jiǎn)的圖書表字段設(shè)計(jì)如下。字段名類型說明idINT UNSIGNED主鍵自增book_nameVARCHAR(100)書名不可為空authorVARCHAR(50)作者默認(rèn)“佚名”priceDECIMAL(10,2)定價(jià)精確到分publish_dateDATE出版日期可為空stockINT UNSIGNED庫存數(shù)量默認(rèn) 0created_atDATETIME入庫時(shí)間默認(rèn)當(dāng)前時(shí)間選型要點(diǎn)整數(shù)優(yōu)先看范圍。TINYINT占 1 字節(jié)INT占 4 字節(jié)BIGINT占 8 字節(jié)。用戶數(shù)、商品數(shù)這類數(shù)據(jù)用INT UNSIGNED通常就夠無符號(hào)范圍能到 42 億左右不要一上來就BIGINT沒必要地浪費(fèi)存儲(chǔ)空間。價(jià)格不用FLOAT或DOUBLE因?yàn)楦↑c(diǎn)數(shù)在二進(jìn)制存儲(chǔ)里有精度誤差0.1 加 0.2 不一定等于 0.3。涉及錢用DECIMAL(10,2)總位數(shù) 10小數(shù)位 2最大能表示 99999999.99覆蓋絕大多數(shù)定價(jià)場(chǎng)景。日期要區(qū)分DATE、DATETIME和TIMESTAMP。DATE只存年月日DATETIME存年月日時(shí)分秒范圍更大TIMESTAMP受數(shù)據(jù)庫時(shí)區(qū)影響。業(yè)務(wù)表里記錄創(chuàng)建時(shí)間用DATETIME比較多。3.2 一條完整的建表語句拆解基于上面的字段設(shè)計(jì)完整建表語句如下CREATE TABLE IF NOT EXISTS books ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主鍵ID, book_name VARCHAR(100) NOT NULL COMMENT 書名, author VARCHAR(50) NOT NULL DEFAULT 佚名 COMMENT 作者, price DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 定價(jià), publish_date DATE DEFAULT NULL COMMENT 出版日期, stock INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 庫存數(shù)量, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 入庫時(shí)間, PRIMARY KEY (id), UNIQUE KEY uk_book_name_author (book_name, author) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT圖書信息表;幾個(gè)容易忽略的細(xì)節(jié)NOT NULL說的是字段值不能為空。為什么book_name要非空因?yàn)橐槐緯绻B名字都沒有這條數(shù)據(jù)基本沒有意義。DEFAULT說的是插入時(shí)不顯式給值用什么默認(rèn)值。這里給author設(shè)了默認(rèn)“佚名”給price設(shè)了默認(rèn) 0給stock設(shè)了默認(rèn) 0這樣后期插入時(shí)可以只填關(guān)鍵字段。AUTO_INCREMENT必須配合索引使用通常就是主鍵。每次插入時(shí)不給 id它會(huì)自動(dòng)加 1。不過要留意事務(wù)回滾之后自增值可能不連續(xù)這不影響業(yè)務(wù)判斷。COMMENT是注釋。給字段加注釋剛開始覺得啰嗦三個(gè)月后回頭看能幫你省下大量回憶時(shí)間。唯一聯(lián)合索引uk_book_name_author約束“書名 作者”不能重復(fù)防止不小心插兩條一模一樣的記錄。3.3 約束與存儲(chǔ)引擎建表時(shí)就要想清楚建表語句末尾寫了ENGINEInnoDB。InnoDB 是 MySQL 5.5 之后的默認(rèn)存儲(chǔ)引擎支持事務(wù)、行級(jí)鎖和外鍵。早期教材里常出現(xiàn)的 MyISAM 不支持事務(wù)也不支持外鍵現(xiàn)在做業(yè)務(wù)系統(tǒng)基本可以不考慮。約束里的主鍵、唯一鍵、非空、默認(rèn)值我建議在建表時(shí)就一起寫好不要等數(shù)據(jù)多了再回頭補(bǔ)。原因很簡(jiǎn)單約束是數(shù)據(jù)庫層面的護(hù)欄。沒有唯一約束靠程序去判斷重復(fù)總會(huì)有漏網(wǎng)之魚沒有非空約束寫入的臟數(shù)據(jù)就很難從源頭攔住。建表成功后可以用下面三條命令確認(rèn)表結(jié)構(gòu)和創(chuàng)建信息SHOW TABLES; DESC books; SHOW CREATE TABLE books;DESC是DESCRIBE的簡(jiǎn)寫查看字段名、類型、是否允許為空、鍵信息。做練習(xí)時(shí)我每建完一張表都會(huì)先DESC看一眼再去拼 SQL 語句免得類型想錯(cuò)。4. 增刪改查實(shí)戰(zhàn)INSERT / SELECT / UPDATE / DELETE 的完整跑法4.1 插入數(shù)據(jù)單行插入與多行批量插入表建好之后先插幾條數(shù)據(jù)。插入語法不難但要小心字段列表和值的順序一一對(duì)應(yīng)。INSERT INTO books (book_name, author, price, publish_date, stock) VALUES (三體, 劉慈欣, 39.50, 2008-01-01, 100); INSERT INTO books (book_name, author, price, publish_date, stock) VALUES (圍城, 錢鐘書, 29.00, 1991-02-01, 50), (活著, 余華, 25.00, 2012-08-01, 80);第一條是單行插入第二條是多行插入。多行插入的好處很明顯一次網(wǎng)絡(luò)往返效率比一行一行插高很多。數(shù)據(jù)量上來以后比如初始化一批測(cè)試數(shù)據(jù)用多行VALUES是最省事的方式。注意我特意沒有在主鍵id和created_at上給值。id由自增生成created_at由CURRENT_TIMESTAMP默認(rèn)值填充。這樣以后寫程序時(shí)這兩個(gè)字段基本不用管。如果插入時(shí)漏了某個(gè)非空字段且該字段沒有默認(rèn)值MySQL 會(huì)報(bào)Field xxx doesnt have a default value。遇到這個(gè)錯(cuò)誤先別急著改表多數(shù)原因是你沒把插入的字段列表寫全。4.2 從 SELECT 到 WHERE、ORDER BY、LIMIT 的查詢套路查詢是 SQL 使用頻率最高的一類也是你驗(yàn)證前面建表是否正確的手段。-- 查全部 SELECT * FROM books; -- 指定字段按價(jià)格從高到低 SELECT id, book_name, price FROM books ORDER BY price DESC; -- 帶條件查詢 SELECT id, book_name, price FROM books WHERE price 30 ORDER BY price DESC; -- 分頁查詢 SELECT id, book_name, price FROM books WHERE price 20 ORDER BY price DESC LIMIT 10;WHERE后面寫篩選條件ORDER BY控制排序LIMIT控制返回行數(shù)。三者加起來已經(jīng)能覆蓋大多數(shù)簡(jiǎn)單查詢場(chǎng)景。我說一下執(zhí)行順序并不是先 SELECT 再 WHERE。大概的順序是先找表FROM再過濾WHERE再分組GROUP BY再過濾分組結(jié)果HAVING再排序ORDER BY最后限制行數(shù)LIMIT。把這個(gè)順序搞明白你就知道為什么 WHERE 里不能直接用聚合函數(shù)別名而 ORDER BY 可以。還有一個(gè)容易忽略的組合LIMIT的偏移量。LIMIT 10表示最多返回 10 行LIMIT 10, 20表示跳過前 10 行取 20 行。這個(gè)語法在不同數(shù)據(jù)庫里寫法不一樣但 MySQL 里就是這么記的。如果想順手算點(diǎn)數(shù)據(jù)可以加計(jì)算列SELECT book_name, price, stock, price * stock AS inventory_value FROM books;inventory_value是計(jì)算出來的庫存價(jià)值不影響表結(jié)構(gòu)。這種“查詢時(shí)臨時(shí)計(jì)算”的寫法很常用。4.3 聚合查詢COUNT / SUM / AVG / GROUP BY 的基本用法建表后數(shù)據(jù)不多但聚合查詢的習(xí)慣要早養(yǎng)成。SELECT author, COUNT(*) AS book_count, AVG(price) AS avg_price FROM books GROUP BY author ORDER BY book_count DESC;這句的意思是按作者分組統(tǒng)計(jì)每個(gè)作者的圖書數(shù)量和平均價(jià)格。AS是給查詢結(jié)果起別名方便閱讀。GROUP BY之后SELECT 里能出現(xiàn)的非聚合列一般只能是分組列不然語義會(huì)亂。這個(gè)規(guī)則一開始我不適應(yīng)踩了幾次語法報(bào)錯(cuò)后才理解。如果要對(duì)分組后的結(jié)果再篩選用HAVING比如只保留有 2 本及以上圖書的作者SELECT author, COUNT(*) AS book_count FROM books GROUP BY author HAVING COUNT(*) 2;WHERE篩選的是原始行HAVING篩選的是分組后的結(jié)果兩者不要用混。想統(tǒng)計(jì)總數(shù)、最大值、最小值對(duì)應(yīng)SUM、MAX、MIN思路和上面一樣。4.4 更新與刪除動(dòng)手之前先把 WHERE 看清楚更新數(shù)據(jù)的寫法核心是 SET 后面寫要改的字段WHERE 后面寫范圍。UPDATE books SET stock stock - 1 WHERE id 1;這句模擬賣出一本《三體》庫存減 1。寫SET stock stock - 1而不是SET stock 99更符合真實(shí)業(yè)務(wù)。你也應(yīng)該特別注意如果 UPDATE 忘寫 WHERE整張表的對(duì)應(yīng)字段都會(huì)被更新。這大概是我見過最貴的一行命令。刪除數(shù)據(jù)也一樣DELETE FROM books WHERE id 2;刪除前我的習(xí)慣是先用同條件 SELECT 看一下SELECT * FROM books WHERE id 2;確認(rèn)這條確實(shí)是你要?jiǎng)h的再把 SELECT 替換成 DELETE。多花一秒鐘能避免刪錯(cuò)行。DELETE只是刪數(shù)據(jù)自增計(jì)數(shù)器不會(huì)重置。想清空整表并重置自增用TRUNCATE TABLE books;但它是 DDL執(zhí)行前更要想清楚而且不能按條件刪只能整表清空。5. 高頻報(bào)錯(cuò)與實(shí)戰(zhàn)排查這些坑我基本都踩過5.1 常見的語法與結(jié)構(gòu)報(bào)錯(cuò)學(xué)習(xí)階段最容易遇到的錯(cuò)誤整理成一張速查表。報(bào)錯(cuò)信息常見原因處理方法ERROR 1049數(shù)據(jù)庫不存在先用 SHOW DATABASES 確認(rèn)庫名ERROR 1050表已存在加 IF NOT EXISTS或 DROP 后重建ERROR 1062唯一索引沖突檢查重復(fù)字段改為更新或換數(shù)據(jù)ERROR 1064SQL 語法錯(cuò)誤逐字檢查關(guān)鍵字、逗號(hào)、引號(hào)ERROR 1366字符集或編碼不匹配確認(rèn)連接字符集和表字符集一致No database selected沒切庫執(zhí)行 USE 庫名最常犯的還是ERROR 1064。比如把保留字order當(dāng)字段名又不加反引號(hào)MySQL 就會(huì)報(bào)語法錯(cuò)。遇到這種我第一個(gè)動(dòng)作是看報(bào)錯(cuò)位置前后 5 個(gè)字符八成是拼寫或符號(hào)問題。另外一個(gè)是環(huán)境差異問題Windows 和 Linux 下表名大小寫是否敏感不同。MySQL 在 Windows 上默認(rèn)不區(qū)分表名大小寫Linux 上默認(rèn)區(qū)分所以換環(huán)境部署時(shí)經(jīng)常莫名其妙找不到表。項(xiàng)目里約定統(tǒng)一小寫表名能少很多麻煩。5.2 中文亂碼十有八九是字符集鏈路沒打通創(chuàng)建數(shù)據(jù)庫時(shí)特意指定了utf8mb4但如果客戶端連接字符集不對(duì)查詢中文依然會(huì)亂碼。排查思路是這樣表字符集、連接字符集、客戶端顯示字符集三段都要一致。命令行客戶端可以先執(zhí)行SET NAMES utf8mb4;這條命令相當(dāng)于同時(shí)設(shè)置了character_set_client、character_set_connection和character_set_results。執(zhí)行完再跑 SELECT中文通常就正常。圖形化工具里一般都有連接字符集選項(xiàng)默認(rèn)選中utf8mb4即可。如果已經(jīng)建好表但數(shù)據(jù)亂碼先備份數(shù)據(jù)再改表字符集最后重新導(dǎo)入。注意單純的ALTER TABLE books CONVERT TO CHARACTER SET utf8mb4;會(huì)修改列字符集但不一定能自動(dòng)修復(fù)已經(jīng)亂碼的數(shù)據(jù)。亂碼問題最好的對(duì)策是創(chuàng)建庫表時(shí)堅(jiān)決寫清楚字符集而不是事后補(bǔ)救。5.3 安全底線SQL 注入與最小權(quán)限寫 SQL 的時(shí)候腦子里要一直有一根安全弦。SQL 注入攻擊依然是 Web 應(yīng)用最大的風(fēng)險(xiǎn)之一網(wǎng)上相關(guān)的搜索熱度一直很高。作為開發(fā)人員正確的做法是和數(shù)據(jù)庫交互時(shí)不拼接 SQL 字符串使用參數(shù)化查詢或預(yù)編譯語句賬號(hào)權(quán)限最小化只給業(yè)務(wù)所需的增刪改查權(quán)限敏感數(shù)據(jù)不要在日志里明文記錄。至于“萬能密碼繞過登錄”這類思路屬于危害系統(tǒng)安全的行為不應(yīng)該去研究更不應(yīng)該用在任何項(xiàng)目里。把自己的程序做安全才是對(duì)用戶負(fù)責(zé)。排查問題的順序我建議是“先結(jié)構(gòu)、再字符集、再權(quán)限”不要一上來就懷疑數(shù)據(jù)庫壞了。多數(shù)時(shí)候是低級(jí)錯(cuò)誤比如庫名寫錯(cuò)、少了個(gè)括號(hào)、忘記切庫。把每一步執(zhí)行結(jié)果看清楚比瞎猜快得多。說回到今天上午這 34 節(jié)筆記。我以前圖快建表時(shí)連字符集都懶得指定后面吃了不少苦頭?，F(xiàn)在養(yǎng)成兩個(gè)習(xí)慣任何庫、任何表創(chuàng)建語句一定完整寫清楚字符集、排序規(guī)則和注釋任何 UPDATE 和 DELETE 之前先跑一條同條件的 SELECT 確認(rèn)目標(biāo)。這兩個(gè)習(xí)慣讓我少走了很多彎路。希望這節(jié)筆記對(duì)正在學(xué) MySQL 的你也有同樣的作用。