文章目錄一、參考文獻二、基本格式三、基本操作3.1 插入3.2 查詢3.3 更新3.4 刪除3.4.1 delete3.4.2 drop3.4.3 truncate四、進階操作4.1 操作符like、通配符4.2 聯合表操作4.2.1 舉例4.3 嵌套操作4.4 SQL常用函數五、數據庫索引六、執行查詢語句期間發生了什么6.1 MySQL 的兩層架構6.1.1 Server 層6.1.2 存儲引擎層1 Memory2 MylSAM3 InnoDB6.2 詳解InnoDB存儲引擎6.2.1 Buffer Pool 緩沖池6.2.2 undo 日志文件6.2.3 redo 日志文件6.2.4 bin log文件6.2.5 后臺線程一、參考文獻參考菜鳥教程二、基本格式select * from表名left join表名xon條件1where條件2group by … having … order by …執行順序from _where __group by _ 對結果集進行分組having __主要和GROUP BY子句配合使用用于過濾聚合值select 查看結果集中的哪個列或列的計算結果DISTINCT去重order by __LIMIT舉例從多個班級中選出這些條件的班級——數學平均成績大于75分、平均成績按從高到低排名最前三的班級。SQLselect 班級, avg(數學成績) as 數學平均成績 where 數學成績 is not null group by 班級 having 數學平均成績 75 order by 數學平均成績 desc limit 0, 3。執行步驟執行 FROM 子句, 從學生成績表中組裝數據源的數據。執行 WHERE 子句, 篩選學生成績表中所有學生的數學成績不為 NULL 的數據 。執行 GROUP BY 子句, 把學生成績表按 “班級” 字段進行分組。計算 avg 聚合函數, 按group by的班級分組求出 數學平均成績。執行 HAVING 子句, 篩選出班級 數學平均成績大于 75 分的。執行SELECT語句選擇數據繼續執行后面幾個步驟。執行 ORDER BY 子句, 把最后的結果按 “數學平均成績” 進行排序。執行LIMIT 限制僅返回3條數據。結合ORDER BY 子句即返回所有班級中數學平均成績的前三的班級及其數學平均成績。三、基本操作3.1 插入INSERT INTO table_name (column_name1,column_name2,…) VALUES (value1,value2,…)3.2 查詢查詢某些字段SELECT column_name1,column_name2 FROM table_name;3.3 更新UPDATE table_name SET column1value1,column2value2,… WHERE some_columnsome_value;3.4 刪除3.4.1 deletedelete語句執行刪除的過程是從表中刪除行并且同時將行刪除操作作為事務記錄在日志中保存以便進行進行回滾操作。請注意添加where如果省略了 WHERE 子句所有的記錄都將被刪除例如DELETE FROM table_name WHERE some_columnsome_value;3.4.2 dropdrop會刪除內容和定義釋放空間。即把整個表去掉以后要新增數據是不可能的只能新增一個表。drop語句將刪除表的結構被依賴的約束constrain)、觸發器trigger)、索引index)依賴于該表的存儲過程/函數將被保留但其狀態會變為invalid。drop table 表名稱 eg: drop table dbo.Sys_Test3.4.3 truncatetruncate (清空表中數據)不刪除定義保留表的數據結構、刪除內容、釋放空間、重置主鍵/自動增長列計數器。與drop不同truncate 只是清空表數據。注意truncate只能清空表數據不能刪除指定行數據。truncate table 表名稱比如runcate table dbo.Sys_Test四、進階操作4.1 操作符like、通配符like1選取 name 以字母 “G” 開始的所有客戶SELECT * FROM Websites WHERE name LIKE ‘G%’;2選取 name 以字母 “k” 結尾的所有客戶SELECT * FROM Websites WHERE name LIKE ‘%k’;3選取 name 包含模式 “oo” 的所有客戶SELECT * FROM Websites WHERE name LIKE ‘%oo%’;4選取 name 不包含模式 “oo” 的所有客戶SELECT * FROM Websites WHERE name NOT LIKE ‘%oo%’;通配符通配符描述例子%替代 0 個或多個字符選取 url 以字母 “https” 開始的所有網站SELECT * FROM Websites WHERE url LIKE ‘https%’_替代一個字符選取 name 以 “G” 開始然后是一個任意字符然后是 “o”然后是一個任意字符然后是 “le” 的所有網站SELECT * FROM Websites WHERE name LIKE ‘G_o_le’[charlist]MySQL不支持 字符列中的任何單一字符1選取 name 以 “G”、“F” 或 “s” 開始的所有網站SELECT * FROM Websites WHERE name REGEXP ‘^ [GFs]’2選取 name 以 A 到 H 字母開頭的網站SELECT * FROM Websites WHERE name REGEXP ‘^ [A-H]’[^charlist] 或 [!charlist]MySQL不支持不在字符列中的任何單一字符選取 name 不以 A 到 H 字母開頭的網站SELECT * FROM Websites WHERE name REGEXP ‘^ [^A-H]’4.2 聯合表操作inner join返回兩張表的交集部分inner join joinleft join以左表為主表返回所有左表的數據left outer join left joinright join以右表為主表返回所有右表的數據right outer join right joinFULL JOIN完全連接可看作是兩張表的并集。如果匹配列的值在兩個表中匹配那么返回數據行否則返回空值。4.2.1 舉例參考知乎文章1person表2score表舉例select * from person t1 left join score t2 on t1.uid t2.uidselect * from person t1 join scorep t2 on t1.uid t2.uidselect * from person t1 full join scorep t2 on t1.uid t2.uid4.3 嵌套操作略代碼盡量避免嵌套原因難寫一旦寫錯就很難定位還可能把數據庫跑死。SQL調試難只能自己一步步執行子語句調試。長SQL后期想跟隨業務修改太難了。長SQL過段時間連自己都看不懂重新看懂跟又開發了一遍似的。復雜 SQL 還會影響數據庫移植在一個數據庫上使用的函數放到另一數據庫可能不支持。4.4 SQL常用函數求平均值avg()求和sum()求總行數count求最大值max()求最小值min()求第n1名到第nm名limit n,m五、數據庫索引參考前面寫的文章索引六、執行查詢語句期間發生了什么參考博客一條SQL查詢語句是如何執行的MySQL是典型的 C/S架構客戶端/服務器架構客戶端進程向服務端進程發送一段文本MySQL指令服務器進程進行語句處理然后執行并返回結果。6.1 MySQL 的兩層架構6.1.1 Server 層Server 層是MySQL的核心功能模塊負責建立連接、分析和執行 SQL主要包括連接器查詢緩存、解析器、預處理器、優化器、執行器等。另外所有的內置函數如日期、時間、數學和加密函數等和所有跨存儲引擎的功能如存儲過程、觸發器、視圖等都在 Server 層實現。執行一條 SQL 查詢語句期間發生了什么連接器建立連接管理連接。建立連接之后除非客戶端主動斷開連接否則服務器會等待客戶端發送請求。但是線程的創建和保持是需要消耗服務器資源的因此服務器會把長時間不活動的客戶端連接斷開。校驗用戶身份查詢緩存查詢語句如果命中查詢緩存則直接返回否則繼續往下執行。MySQL 8.0 已刪除該模塊。解析 SQL通過解析器對 SQL 查詢語句進行如下操作方便后續模塊讀取表名、字段、語句類型詞法分析。就是把一條完整的SQL語句打碎成一個個單詞比如MySQL會把SELECT識別成查詢語句把字符串t_user識別成“表名 t_user”把字符串user_name識別成“列 user_name。語法分析。語法分析器會根據語法規則生成解析樹從而判斷SQL 語句是否滿足語法比如單引號是否閉合關鍵詞拼寫是否正確等。構建語法樹。解析樹執行 SQL執行 SQL 共有三個階段預處理階段檢查表或字段是否存在將 select * 中的 * 符號擴展為表的所有列。優化階段基于查詢成本的考慮 查詢優化器會選擇成本最小的執行計劃MySQL作者擔心我們寫的SQL太垃圾所以有設計出查詢優化器輔助我們提高查詢效率。查詢優化器會根據解析樹生成不同的執行計劃Execution Plan然后選擇一種成本最小的執行計劃。這里的成本指【I/O成本 CPU成本】IO 成本: 即從磁盤把數據加載到內存的成本默認情況下讀取數據頁的 IO 成本是 1MySQL 是以頁的形式讀取數據的即當用到某個數據時并不會只讀取這個數據而會把這個數據相鄰的數據也一起讀到內存中這就是有名的程序局部性原理所以 MySQL 每次會讀取一整頁一頁的成本就是 1。所以 IO 的成本主要和頁的大小有關CPU 成本將數據讀入內存后還要檢測數據是否滿足條件和排序等 CPU 操作的成本顯然它與行數有關默認情況下檢測記錄的成本是 0.2。執行階段根據執行計劃執行 SQL 查詢語句從存儲引擎讀取記錄返回給客戶端。存儲引擎處理數據6.1.2 存儲引擎層補充知識:MySQL支持 InnoDB、MyISAM、Memory 等多個存儲引擎不同的存儲引擎共用一個 Server 層。從 MySQL 5.5 版本開始MySQL默認InnoDB為存儲引擎 。我們常說的索引數據結構就是由存儲引擎層實現的。不同的存儲引擎支持的索引類型也不相同比如 InnoDB 支持索引類型是 B樹且是默認使用。在數據表中創建的主鍵索引和二級索引默認使用的是 B 樹索引。存儲引擎層負責數據存儲和提取比如數據存儲在內存還是磁盤、怎么從表里讀取數據怎么把數據寫入表中。表是由一行一行的記錄組成的但這只是邏輯上的概念其實只是看上去是這樣而已。為什么需要多種存儲引擎不同存儲引擎特性不同存儲引擎只是讀寫MySQL數據的插件可以根據不同目隨意更換。如何選擇存儲引擎1對數據一致性要求比較高需要事務支持可以選擇InnoDB。2如果數據查詢多更新少對查詢性能要求比較高可以選擇MyISAM。3如果需要一個用于查詢的臨時表可以選擇Memory。1 MemoryMemory存儲引擎以前也稱堆引擎它將所有數據存儲在RAM內存中以便快速訪問。特點把數據放在內存里面讀寫的速度很快。但是數據庫重啟或者崩潰數據會全部消失只適合做臨時表。2 MylSAM應用范圍比較小表級鎖限制了讀/寫性能因此在Web和數據倉庫配置中通常用于只讀或以讀為主的工作。特點:支持表級別的鎖插入和更新會鎖表不支持事務擁有較高的插入insert和查詢select速度存儲了表的行數count速度更快。怎么快速向數據庫插入100萬條數據可以先用MylSAM插入數據然后修改存儲引擎為InnoDB。ALTER TABLE 表名 ENGINE 存儲引擎名稱;3 InnoDBMySQL 5.7及更新版中的默認存儲引擎。InnoDB是事務安全兼容ACID它具有提交、回滾和崩潰恢復功能來保護用戶數據。InnoDB行級鎖和Oracle風格的一致非鎖讀提高了多用戶并發性。InnoDB將用戶數據存儲在聚集索引中以減少基于主鍵的常見查詢的I/O。為了保持數據完整性InnoDB還支持外鍵引用完整性約束。特點支持事務支持外鍵因此數據的完整性、一致性更高支持行級別的鎖和表級別的鎖支持讀寫并發寫不阻塞讀MVCC特殊的索引存放方式可以減少IO提升査詢效率。番外為什么MySQL越來越像OracleInnoDB是InnobaseOy公司開發的它和MySQL AB公司合作開源了InnoDB的代碼。但是MySQL的競爭對手Oracle把InnobaseOy收購了。后來2008年Sun公司開發Java語言的Sun收購了MySQL AB2009年Sun公司又被Oracle收購了所以MySQL和 InnoDB又是一家了。6.2 詳解InnoDB存儲引擎事務在InnoDB中從提交到完成的整個流程準備更新一條 SQL 語句MySQLinnodb會先去緩沖池BufferPool中去查找這條數據沒找到就會去磁盤中查找如果查找到就會將這條數據加載到緩沖池BufferPool中。在加載到 Buffer Pool 的同時會將這條數據的原始記錄保存到 undo 日志文件中。innodb 會在 Buffer Pool 中執行更新操作。更新后的數據會記錄在 redo log buffer 中。提交事務時會將內存 redo log buffer 中的數據寫入到磁盤的 redo log 文件中。提交事務時MySQL還會1將本次修改的數據記錄到 bin log文件中2將本次修改的bin log文件名和修改的內容在bin log中的位置記錄到redo log中3在redo log中寫入 commit 標記標識本次事務被成功提交了。6.2.1 Buffer Pool 緩沖池緩沖池 Buffer Pool是InnoDB非常重要的組件。MySQL 的數據最終是存儲在磁盤中的有了 Buffer Pool第一次查詢時就會將查詢結果存到Buffer Pool之后再有請求時就會先從緩沖池中查詢沒查到再去磁盤中I/O查找然后在放到 Buffer Pool 中。6.2.2 undo 日志文件在準備更新一條語句的時候該條語句已經被加載到 Buffer pool 中了實際上這里還會同時在 undo 日志文件記錄下更新前的值。為什么要記錄更新前的值Innodb 存儲引擎的最大特點就是支持事務如果本次更新失敗也就是事務提交失敗那么該事務中的所有的操作都必須回滾到執行前的樣子也就是說當事務失敗的時候也不會對原始數據有影響6.2.3 redo 日志文件redo log buffer內存緩存記錄將要做的一些操作。redo log磁盤文件記錄數據被修改后的樣子。MySQL 為了提高效率會將更新操作先放在內存中去完成然后會在事務提交后 將其持久化到磁盤日志文件中。知識補充如果 redo log Buffer 刷入磁盤前MySQL宕機了緩存會丟失沒關系因為 MySQL 會認為本次事務是失敗的所以數據依舊是更新前的樣子沒有任何影響。如果 redo log Buffer 刷入磁盤后MySQL宕機了緩存會丟失也沒關系因為 redo log buffer 中的數據已經被寫入到磁盤redo log了下次重啟時 MySQL 會將 redo log 文件內容恢復到 Buffer Pool 中和 Redis 的持久化機制類似Redis 啟動時會檢查 RDB 或者 AOF 或者兩者都檢查根據持久化的文件將數據恢復到內存中。刷入磁盤參數設置通過 innodb_flush_log_at_trx_commit 參數設置刷入磁盤0 表示不刷入磁盤1 表示立即刷入磁盤2 表示先刷到 os cache6.2.4 bin log文件bin log 記錄對數據庫的整個修改操作對主從復制非常有用bin log刷盤策略可以通過sync_bin log修改策略。為0表示提交事務后先寫入os cache數據不會直接到磁盤中如果宕機bin log數據會丟失。建議將sync_bin log設置為 1 表示直接將數據寫入到磁盤文件中。bin log在redo log中被記錄提交事務時MySQL還會1將本次修改的數據記錄到 bin log文件中2將本次修改的bin log文件名和修改的內容在bin log中的位置記錄到redo log中3在redo log中寫入 commit 標記標識本次事務被成功提交了。如果數據剛被寫入到bin log文件數據庫宕機了數據會丟失嗎——不會丟失只要redo log最后沒有 commit 標記就說明本次的事務是失敗的但是數據已經被記錄到redo log的磁盤文件中了MySQL 重啟時會將 redo log 中的數據恢復加載到Buffer Pool。6.2.5 后臺線程疑問上面僅描述了在內存中的更新操作哪怕是宕機又恢復了也僅是將更新后的記錄加載到Buffer Pool中這時 MySQL 數據庫中的這條記錄依舊是舊值內存數據依舊是臟數據MySQL怎么保持內存和數據庫表數據統一的呢解答MySQL 有個后臺線程它會在某個時機將Buffer Pool 中的臟數據刷到磁盤表中保持內存和數據庫數據統一。