作為SQL Boy，基礎(chǔ)部分不會(huì)有人不會(huì)吧？面試也不怎么問(wèn)，基礎(chǔ)掌握不錯(cuò)的小伙伴可以跳過(guò)這一部分。當(dāng)然，可能會(huì)現(xiàn)場(chǎng)寫(xiě)一些SQL語(yǔ)句，SQ語(yǔ)句可以通過(guò)?？汀eetCode、LintCode之類(lèi)的網(wǎng)站來(lái)練習(xí)。

1. 什么是內(nèi)連接、外連接、交叉連接、笛卡爾積呢？

• 內(nèi)連接（inner join）：取得兩張表中滿(mǎn)足存在連接匹配關(guān)系的記錄。
• 外連接（outer join）：不只取得兩張表中滿(mǎn)足存在連接匹配關(guān)系的記錄，還包括某張表（或兩張表）中不滿(mǎn)足匹配關(guān)系的記錄。
• 交叉連接（cross join）：顯示兩張表所有記錄一一對(duì)應(yīng)，沒(méi)有匹配關(guān)系進(jìn)行篩選，它是笛卡爾積在SQL中的實(shí)現(xiàn)，如果A表有m行，B表有n行，那么A和B交叉連接的結(jié)果就有m*n行。
• 笛卡爾積：是數(shù)學(xué)中的一個(gè)概念，例如集合A={a,b}，集合B={1,2,3}，那么A✖️B={<a,o>,<a,1>,<a,2>,<b,0>,<b,1>,<b,2>,}。

2. 那MySQL 的內(nèi)連接、左連接、右連接有有什么區(qū)別？

MySQL的連接主要分為內(nèi)連接和外連接，外連接常用的有左連接、右連接。

• inner join 內(nèi)連接，在兩張表進(jìn)行連接查詢(xún)時(shí)，只保留兩張表中完全匹配的結(jié)果集
• left join 在兩張表進(jìn)行連接查詢(xún)時(shí)，會(huì)返回左表所有的行，即使在右表中沒(méi)有匹配的記錄。
• right join 在兩張表進(jìn)行連接查詢(xún)時(shí)，會(huì)返回右表所有的行，即使在左表中沒(méi)有匹配的記錄。

3.說(shuō)一下數(shù)據(jù)庫(kù)的三大范式？

• 第一范式：數(shù)據(jù)表中的每一列（每個(gè)字段）都不可以再拆分。 例如用戶(hù)表，用戶(hù)地址還可以拆分成國(guó)家、省份、市，這樣才是符合第一范式的。
• 第二范式：在第一范式的基礎(chǔ)上，非主鍵列完全依賴(lài)于主鍵，而不能是依賴(lài)于主鍵的一部分。 例如訂單表里，存儲(chǔ)了商品信息（商品價(jià)格、商品類(lèi)型），那就需要把商品ID和訂單ID作為聯(lián)合主鍵，才滿(mǎn)足第二范式。
• 第三范式：在滿(mǎn)足第二范式的基礎(chǔ)上，表中的非主鍵只依賴(lài)于主鍵，而不依賴(lài)于其他非主鍵。 例如訂單表，就不能存儲(chǔ)用戶(hù)信息（姓名、地址）。

三大范式的作用是為了控制數(shù)據(jù)庫(kù)的冗余，是對(duì)空間的節(jié)省，實(shí)際上，一般互聯(lián)網(wǎng)公司的設(shè)計(jì)都是反范式的，通過(guò)冗余一些數(shù)據(jù)，避免跨表跨庫(kù)，利用空間換時(shí)間，提高性能。

4.varchar與char的區(qū)別？

char：

• char表示定長(zhǎng)字符串，長(zhǎng)度是固定的；
• 如果插入數(shù)據(jù)的長(zhǎng)度小于char的固定長(zhǎng)度時(shí)，則用空格填充；
• 因?yàn)殚L(zhǎng)度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因?yàn)槠溟L(zhǎng)度固定，所以會(huì)占據(jù)多余的空間，是空間換時(shí)間的做法；
• 對(duì)于char來(lái)說(shuō)，最多能存放的字符個(gè)數(shù)為255，和編碼無(wú)關(guān)

varchar：

• varchar表示可變長(zhǎng)字符串，長(zhǎng)度是可變的；
• 插入的數(shù)據(jù)是多長(zhǎng)，就按照多長(zhǎng)來(lái)存儲(chǔ)；
• varchar在存取方面與char相反，它存取慢，因?yàn)殚L(zhǎng)度不固定，但正因如此，不占據(jù)多余的空間，是時(shí)間換空間的做法；
• 對(duì)于varchar來(lái)說(shuō)，最多能存放的字符個(gè)數(shù)為65532

日常的設(shè)計(jì)，對(duì)于長(zhǎng)度相對(duì)固定的字符串，可以使用char，對(duì)于長(zhǎng)度不確定的，使用varchar更合適一些。

5.blob和text有什么區(qū)別？

• blob用于存儲(chǔ)二進(jìn)制數(shù)據(jù)，而text用于存儲(chǔ)大字符串。
• blob沒(méi)有字符集，text有一個(gè)字符集，并且根據(jù)字符集的校對(duì)規(guī)則對(duì)值進(jìn)行排序和比較

6.DATETIME和TIMESTAMP的異同？

相同點(diǎn)：

0. 兩個(gè)數(shù)據(jù)類(lèi)型存儲(chǔ)時(shí)間的表現(xiàn)格式一致。均為 YYYY-MM-DD HH:MM:SS
1. 兩個(gè)數(shù)據(jù)類(lèi)型都包含「日期」和「時(shí)間」部分。
2. 兩個(gè)數(shù)據(jù)類(lèi)型都可以存儲(chǔ)微秒的小數(shù)秒（秒后6位小數(shù)秒）

區(qū)別：

• 日期范圍：DATETIME 的日期范圍是 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999；TIMESTAMP 的時(shí)間范圍是1970-01-01 00:00:01.000000 UTC到 ``2038-01-09 03:14:07.999999 UTC

• 存儲(chǔ)空間：DATETIME 的存儲(chǔ)空間為 8 字節(jié)；TIMESTAMP 的存儲(chǔ)空間為 4 字節(jié)

• 時(shí)區(qū)相關(guān)：DATETIME 存儲(chǔ)時(shí)間與時(shí)區(qū)無(wú)關(guān)；TIMESTAMP 存儲(chǔ)時(shí)間與時(shí)區(qū)有關(guān)，顯示的值也依賴(lài)于時(shí)區(qū)

• 默認(rèn)值：DATETIME 的默認(rèn)值為 null；TIMESTAMP 的字段默認(rèn)不為空(not null)，默認(rèn)值為當(dāng)前時(shí)間(CURRENT_TIMESTAMP)

7.MySQL中 in 和 exists 的區(qū)別？

MySQL中的in語(yǔ)句是把外表和內(nèi)表作hash 連接，而exists語(yǔ)句是對(duì)外表作loop循環(huán)，每次loop循環(huán)再對(duì)內(nèi)表進(jìn)行查詢(xún)。我們可能認(rèn)為exists比in語(yǔ)句的效率要高，這種說(shuō)法其實(shí)是不準(zhǔn)確的，要區(qū)分情景：

• 如果查詢(xún)的兩個(gè)表大小相當(dāng)，那么用in和exists差別不大。

• 如果兩個(gè)表中一個(gè)較小，一個(gè)是大表，則子查詢(xún)表大的用exists，子查詢(xún)表小的用in。

• not in 和not exists：如果查詢(xún)語(yǔ)句使用了not in，那么內(nèi)外表都進(jìn)行全表掃描，沒(méi)有用到索引；而not extsts的子查詢(xún)依然能用到表上的索引。所以無(wú)論那個(gè)表大，用not exists都比not in要快。

8.MySQL里記錄貨幣用什么字段類(lèi)型比較好？

貨幣在數(shù)據(jù)庫(kù)中MySQL常用Decimal和Numric類(lèi)型表示，這兩種類(lèi)型被MySQL實(shí)現(xiàn)為同樣的類(lèi)型。他們被用于保存與貨幣有關(guān)的數(shù)據(jù)。

例如salary DECIMAL(9,2)，9(precision)代表將被用于存儲(chǔ)值的總的小數(shù)位數(shù)，而2(scale)代表將被用于存儲(chǔ)小數(shù)點(diǎn)后的位數(shù)。存儲(chǔ)在salary列中的值的范圍是從-9999999.99到9999999.99。

DECIMAL和NUMERIC值作為字符串存儲(chǔ)，而不是作為二進(jìn)制浮點(diǎn)數(shù)，以便保存那些值的小數(shù)精度。

之所以不使用float或者double的原因：因?yàn)閒loat和double是以二進(jìn)制存儲(chǔ)的，所以有一定的誤差。

9.MySQL怎么存儲(chǔ)emoji?

MySQL可以直接使用字符串存儲(chǔ)emoji。

但是需要注意的，utf8 編碼是不行的，MySQL中的utf8是閹割版的 utf8，它最多只用 3 個(gè)字節(jié)存儲(chǔ)字符，所以存儲(chǔ)不了表情。那該怎么辦？

需要使用utf8mb4編碼。

alter table blogs modify content text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci not null;

10.drop、delete與truncate的區(qū)別？

三者都表示刪除，但是三者有一些差別：

因此，在不再需要一張表的時(shí)候，用drop；在想刪除部分?jǐn)?shù)據(jù)行時(shí)候，用delete；在保留表而刪除所有數(shù)據(jù)的時(shí)候用truncate。

11.UNION與UNION ALL的區(qū)別？

• 如果使用UNION ALL，不會(huì)合并重復(fù)的記錄行
• 效率 UNION 高于 UNION ALL

12.count(1)、count(*) 與 count(列名) 的區(qū)別？

執(zhí)行效果：

• count(*)包括了所有的列，相當(dāng)于行數(shù)，在統(tǒng)計(jì)結(jié)果的時(shí)候，不會(huì)忽略列值為NULL
• count(1)包括了忽略所有列，用1代表代碼行，在統(tǒng)計(jì)結(jié)果的時(shí)候，不會(huì)忽略列值為NULL
• count(列名)只包括列名那一列，在統(tǒng)計(jì)結(jié)果的時(shí)候，會(huì)忽略列值為空（這里的空不是只空字符串或者0，而是表示null）的計(jì)數(shù)，即某個(gè)字段值為NULL時(shí)，不統(tǒng)計(jì)。

執(zhí)行速度：

• 列名為主鍵，count(列名)會(huì)比count(1)快
• 列名不為主鍵，count(1)會(huì)比count(列名)快
• 如果表多個(gè)列并且沒(méi)有主鍵，則 count（1） 的執(zhí)行效率優(yōu)于 count（*）
• 如果有主鍵，則 select count（主鍵）的執(zhí)行效率是最優(yōu)的
• 如果表只有一個(gè)字段，則 select count（*）最優(yōu)。

13.一條SQL查詢(xún)語(yǔ)句的執(zhí)行順序？

• FROM：對(duì)FROM子句中的左表<left_table>和右表<right_table>執(zhí)行笛卡兒積（Cartesianproduct），產(chǎn)生虛擬表VT1

• ON：對(duì)虛擬表VT1應(yīng)用ON篩選，只有那些符合<join_condition>的行才被插入虛擬表VT2中

• JOIN：如果指定了OUTER JOIN（如LEFT OUTER JOIN、RIGHT OUTER JOIN），那么保留表中未匹配的行作為外部行添加到虛擬表VT2中，產(chǎn)生虛擬表VT3。如果FROM子句包含兩個(gè)以上表，則對(duì)上一個(gè)連接生成的結(jié)果表VT3和下一個(gè)表重復(fù)執(zhí)行步驟1）～步驟3），直到處理完所有的表為止

• WHERE：對(duì)虛擬表VT3應(yīng)用WHERE過(guò)濾條件，只有符合<where_condition>的記錄才被插入虛擬表VT4中

• GROUP BY：根據(jù)GROUP BY子句中的列，對(duì)VT4中的記錄進(jìn)行分組操作，產(chǎn)生VT5

• CUBE|ROLLUP：對(duì)表VT5進(jìn)行CUBE或ROLLUP操作，產(chǎn)生表VT6

• HAVING：對(duì)虛擬表VT6應(yīng)用HAVING過(guò)濾器，只有符合<having_condition>的記錄才被插入虛擬表VT7中。

• SELECT：第二次執(zhí)行SELECT操作，選擇指定的列，插入到虛擬表VT8中

• DISTINCT：去除重復(fù)數(shù)據(jù)，產(chǎn)生虛擬表VT9

• ORDER BY：將虛擬表VT9中的記錄按照<order_by_list>進(jìn)行排序操作，產(chǎn)生虛擬表VT10。11）

• LIMIT：取出指定行的記錄，產(chǎn)生虛擬表VT11，并返回給查詢(xún)用戶(hù)

數(shù)據(jù)庫(kù)架構(gòu)

14.說(shuō)說(shuō) MySQL 的基礎(chǔ)架構(gòu)?

MySQL邏輯架構(gòu)圖主要分三層：

• 客戶(hù)端：最上層的服務(wù)并不是MySQL所獨(dú)有的，大多數(shù)基于網(wǎng)絡(luò)的客戶(hù)端/服務(wù)器的工具或者服務(wù)都有類(lèi)似的架構(gòu)。比如連接處理、授權(quán)認(rèn)證、安全等等。
• Server層：大多數(shù)MySQL的核心服務(wù)功能都在這一層，包括查詢(xún)解析、分析、優(yōu)化、緩存以及所有的內(nèi)置函數(shù)（例如，日期、時(shí)間、數(shù)學(xué)和加密函數(shù)），所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)：存儲(chǔ)過(guò)程、觸發(fā)器、視圖等。
• 存儲(chǔ)引擎層：第三層包含了存儲(chǔ)引擎。存儲(chǔ)引擎負(fù)責(zé)MySQL中數(shù)據(jù)的存儲(chǔ)和提取。Server層通過(guò)API與存儲(chǔ)引擎進(jìn)行通信。這些接口屏蔽了不同存儲(chǔ)引擎之間的差異，使得這些差異對(duì)上層的查詢(xún)過(guò)程透明。

15.一條 SQL 查詢(xún)語(yǔ)句在 MySQL 中如何執(zhí)行的？

• 先檢查該語(yǔ)句是否有權(quán)限，如果沒(méi)有權(quán)限，直接返回錯(cuò)誤信息，如果有權(quán)限會(huì)先查詢(xún)緩存 (MySQL8.0 版本以前)。
• 如果沒(méi)有緩存，分析器進(jìn)行語(yǔ)法分析，提取 sql 語(yǔ)句中 select 等關(guān)鍵元素，然后判斷 sql 語(yǔ)句是否有語(yǔ)法錯(cuò)誤，比如關(guān)鍵詞是否正確等等。
• 語(yǔ)法解析之后，MySQL的服務(wù)器會(huì)對(duì)查詢(xún)的語(yǔ)句進(jìn)行優(yōu)化，確定執(zhí)行的方案。
• 完成查詢(xún)優(yōu)化后，按照生成的執(zhí)行計(jì)劃調(diào)用數(shù)據(jù)庫(kù)引擎接口，返回執(zhí)行結(jié)果。

存儲(chǔ)引擎

16.MySQL有哪些常見(jiàn)存儲(chǔ)引擎？

主要存儲(chǔ)引擎以及功能如下：

MySQL5.5之前，默認(rèn)存儲(chǔ)引擎是MylSAM，5.5之后變成了InnoDB。

InnoDB支持的哈希索引是自適應(yīng)的，InnoDB會(huì)根據(jù)表的使用情況自動(dòng)為表生成哈希索引，不能人為干預(yù)是否在一張表中生成哈希索引。

MySQL 5.6開(kāi)始InnoDB支持全文索引。

17.那存儲(chǔ)引擎應(yīng)該怎么選擇？

大致上可以這么選擇：

• 大多數(shù)情況下，使用默認(rèn)的InnoDB就夠了。如果要提供提交、回滾和恢復(fù)的事務(wù)安全（ACID 兼容）能力，并要求實(shí)現(xiàn)并發(fā)控制，InnoDB 就是比較靠前的選擇了。
• 如果數(shù)據(jù)表主要用來(lái)插入和查詢(xún)記錄，則 MyISAM 引擎提供較高的處理效率。
• 如果只是臨時(shí)存放數(shù)據(jù)，數(shù)據(jù)量不大，并且不需要較高的數(shù)據(jù)安全性，可以選擇將數(shù)據(jù)保存在內(nèi)存的 MEMORY 引擎中，MySQL 中使用該引擎作為臨時(shí)表，存放查詢(xún)的中間結(jié)果。

使用哪一種引擎可以根據(jù)需要靈活選擇，因?yàn)榇鎯?chǔ)引擎是基于表的，所以一個(gè)數(shù)據(jù)庫(kù)中多個(gè)表可以使用不同的引擎以滿(mǎn)足各種性能和實(shí)際需求。使用合適的存儲(chǔ)引擎將會(huì)提高整個(gè)數(shù)據(jù)庫(kù)的性能。

18.InnoDB和MylSAM主要有什么區(qū)別？

PS:MySQL8.0都開(kāi)始慢慢流行了，如果不是面試，MylSAM其實(shí)可以不用怎么了解。

1. 存儲(chǔ)結(jié)構(gòu)：每個(gè)MyISAM在磁盤(pán)上存儲(chǔ)成三個(gè)文件；InnoDB所有的表都保存在同一個(gè)數(shù)據(jù)文件中（也可能是多個(gè)文件，或者是獨(dú)立的表空間文件），InnoDB表的大小只受限于操作系統(tǒng)文件的大小，一般為2GB。

2. 事務(wù)支持：MyISAM不提供事務(wù)支持；InnoDB提供事務(wù)支持事務(wù)，具有事務(wù)(commit)、回滾(rollback)和崩潰修復(fù)能力(crash recovery capabilities)的事務(wù)安全特性。

3 最小鎖粒度：MyISAM只支持表級(jí)鎖，更新時(shí)會(huì)鎖住整張表，導(dǎo)致其它查詢(xún)和更新都會(huì)被阻塞InnoDB支持行級(jí)鎖。

4. 索引類(lèi)型：MyISAM的索引為聚簇索引，數(shù)據(jù)結(jié)構(gòu)是B樹(shù)；InnoDB的索引是非聚簇索引，數(shù)據(jù)結(jié)構(gòu)是B+樹(shù)。

5. 主鍵必需：MyISAM允許沒(méi)有任何索引和主鍵的表存在；InnoDB如果沒(méi)有設(shè)定主鍵或者非空唯一索引，就會(huì)自動(dòng)生成一個(gè)6字節(jié)的主鍵(用戶(hù)不可見(jiàn)) ，數(shù)據(jù)是主索引的一部分，附加索引保存的是主索引的值。

6. 表的具體行數(shù)：MyISAM保存了表的總行數(shù)，如果select count() from table;會(huì)直接取出出該值; InnoDB沒(méi)有保存表的總行數(shù)，如果使用select count() from table；就會(huì)遍歷整個(gè)表;但是在加了wehre條件后，MyISAM和InnoDB處理的方式都一樣。

7. 外鍵支持：MyISAM不支持外鍵；InnoDB支持外鍵。

日志

19.MySQL日志文件有哪些？分別介紹下作用？

MySQL日志文件有很多，包括 ：

• 錯(cuò)誤日志（error log）：錯(cuò)誤日志文件對(duì)MySQL的啟動(dòng)、運(yùn)行、關(guān)閉過(guò)程進(jìn)行了記錄，能幫助定位MySQL問(wèn)題。
• 慢查詢(xún)?nèi)罩?/strong>（slow query log）：慢查詢(xún)?nèi)罩臼怯脕?lái)記錄執(zhí)行時(shí)間超過(guò) long_query_time 這個(gè)變量定義的時(shí)長(zhǎng)的查詢(xún)語(yǔ)句。通過(guò)慢查詢(xún)?nèi)罩?，可以查找出哪些查?xún)語(yǔ)句的執(zhí)行效率很低，以便進(jìn)行優(yōu)化。
• 一般查詢(xún)?nèi)罩?/strong>（general log）：一般查詢(xún)?nèi)罩居涗浟怂袑?duì)MySQL數(shù)據(jù)庫(kù)請(qǐng)求的信息，無(wú)論請(qǐng)求是否正確執(zhí)行。
• 二進(jìn)制日志（bin log）：關(guān)于二進(jìn)制日志，它記錄了數(shù)據(jù)庫(kù)所有執(zhí)行的DDL和DML語(yǔ)句（除了數(shù)據(jù)查詢(xún)語(yǔ)句select、show等），以事件形式記錄并保存在二進(jìn)制文件中。

select * from table where type = 2 and level = 9 order by id asc limit 190289,10;

  select * from table where id >   (select * from table where type = 2 and level = 9 order by id asc limit 190

select name from test where city='上海'

alter table test add index idx_city_name (city, name);

alter table test add index index2(email(6));

select * from test where id + 1 = 50; select * from test where month(updateTime) = 7;

 select name from A left join B ;