(十五)MySQL命令大全：以後再也不用擔心忘記SQL該怎麼寫啦~
theme: channing-cyan
引言
本文為掘金社群首發簽約文章，14天內禁止轉載，14天后未獲授權禁止轉載，侵權必究！
相信大家在編寫SQL時一定有一個困擾，就是明明記得資料庫中有個命令/函式，可以實現自己需要的功能，但偏偏不記得哪個命令該怎麼寫了，這時只能靠盲目的去百度，以此來尋找自己需要的命令。
時間是最厲害的武器，少年定會白首，鮮花亦會凋零，滄海會演變桑田，高山也會化作平原。
而我們每一位開發者，作為人類也不例外，無法抵擋時間的流逝，其記憶力會隨著時間逐漸推移不斷下降，而MySQL中的命令/函式那麼多，咱們也並不能完全記住，所以對於前面的那種情況，在實際開發中也屬常事，所以本章則會將一些常用的SQL命令/函式全部羅列出來，以後當需要用到時只需回來搜尋即可。
其實在撰寫《JVM成神路》這個專欄的時候，也曾出過一篇類似於的文章，名為《JVM引數大全》，其中主要羅列了
JVM通用引數、記憶體各區域的調整引數、GC垃圾回收的相關引數、效能監控與調優等引數，本章則屬於它的姊妹篇，但區別在於：主角從JVM換成了MySQL。
當大家以後需要使用某條命令/函式時，可以很好的利用這篇命令大全來輔助您，方式有兩種：
- ①按下Ctrl+F搜尋快捷鍵，搜尋關鍵詞用於定位相應的命令。
- ②本文會以功能對所有命令進行分類，通過右側的文章目錄可按功能快捷調整命令位置。
當然，如若本文對你有些許幫助，那請不要忘了點贊支援一下噢~
一、基礎操作與庫命令
首先來介紹一些關於MySQL基礎操作的命令，以及操作資料庫相關的命令，MySQL中的所有命令預設是以;分好結尾的，因此在執行時一定要記得帶上分號，否則MySQL會認為你這條命令還未結束，會繼續等待你的命令輸入，如下：
1.1、MySQL基礎操作命令
net start mysql：Windows系統啟動MySQL服務。安裝目錄/mysql start：Linux系統啟動MySQL服務。shutdown：後面的start換成這個，表示關閉MySQL服務。restart：換成restart表示重啟MySQL服務。
ps -ef | grep mysql：Linux檢視MySQL後臺程序的命令。kill -9 MySQL程序ID：強殺MySQL服務的命令。mysql -h地址 -p埠 -u賬號 -p：客戶端連線MySQL服務（需要二次輸入密碼）。show status;：檢視MySQL執行狀態。SHOW VARIABLES like %xxx%;：檢視指定的系統變數。show processlist;：檢視當前庫中正在執行的所有客戶端連線/工作執行緒。show status like "Threads%";：檢視當前資料庫的工作執行緒系統。help data types;：檢視當前版本MySQL支援的所有資料型別。help xxx：檢視MySQL的幫助資訊。quit：退出當前資料庫連線。
1.2、MySQL庫相關的命令
show databases;：檢視目前MySQL中擁有的所有庫。show engines;：檢視當前資料庫支援的所有儲存引擎。use 庫名;：使用/進入指定的某個資料庫。show status;：檢視當前資料庫的狀態資訊。show grants;：檢視當前連線的許可權資訊。show errors;：檢視當前庫中記錄的錯誤資訊。show warnings：檢視當前庫丟擲的所有警告資訊。show create database 庫名;：檢視建立某個庫的SQL詳細資訊。show create table 表名;：檢視建立某張表的SQL詳細資訊。show tables;：檢視一個庫中的所有表。desc 表名;：檢視一張表的欄位結構。除開這種方式還有幾種方式：describe 表名;：檢視一張表的欄位結構。show columns from 表名;：檢視一張表的欄位結構。explain 表名;：檢視一張表的欄位結構。
create database 庫名;：新建一個數據庫，後面還可以指定編碼格式和排序規則。drop database 庫名;：刪除一個數據庫。ALTER DATABASE 庫名 DEFAULT CHARACTER SET 編碼格式 DEFAULT COLLATE 排序規則：修改資料庫的編碼格式、排序規則。
1.3、MySQL表相關的命令
對於MySQL表相關的命令，首先來聊一聊建立表的SQL命令，如下：
sql
CREATE TABLE `庫名`.`表名` (
欄位名稱1 資料型別(精度限制) [欄位選項],
欄位名稱2 資料型別(精度限制) [欄位選項]
) [表選項];
對於表中的每個欄位，都需要用,分割，但最後一個欄位後面無需跟,逗號，同時建立表時，對於每個欄位都有多個欄位選項，對於一張表而言也有多個表選項，下面一起來看看。
- 欄位選項（可以不寫，不選使用預設值）：
- NULL：表示該欄位可以為空。
- NOT NULL：表示改欄位不允許為空。
- DEFAULT 預設值：插入資料時若未對該欄位賦值，則使用這個預設值。
- AUTO_INCREMENT：是否將該欄位宣告為一個自增列。
- PRIMARY KEY：將當前欄位宣告為表的主鍵。
- UNIQUE KEY：為當前欄位設定唯一約束，表示不允許重複。
- CHARACTER SET 編碼格式：指定該欄位的編碼格式，如utf8。
- COLLATE 排序規則：指定該欄位的排序規則（非數值型別生效）。
- COMMENT 欄位描述：為當前欄位新增備註資訊，類似於程式碼中的註釋。
- 表選項（可以不寫，不選使用預設值）：
- ENGINE = 儲存引擎名稱：指定表的儲存引擎，如InnoDB、MyISAM等。
- CHARACTER SET = 編碼格式：指定表的編碼格式，未指定使用庫的編碼格式。
- COLLATE = 排序規則：指定表的排序規則，未指定則使用庫的排序規則。
- ROW_FORMAT = 格式：指定儲存行資料的格式，如Compact、Redundant、Dynamic....。
- AUTO_INCREMENT = n：設定自增列的步長，預設為1。
- DATA DIRECTORY = 目錄：指定表文件的儲存路徑。
- INDEX DIRECTORY = 目錄：指定索引檔案的儲存路徑。
- PARTITION BY ...：表分割槽選項，後續講《MySQL表分割槽》再細聊。
- COMMENT 表描述：表的註釋資訊，可以在這裡新增一張表的備註。
整體看下來會發現選項還蠻多，下面貼個例子感受一下：
sql
-- 在 db_zhuzi 庫下建立一張名為 zz_user 的使用者表
CREATE TABLE `db_zhuzi`.`zz_user` (
-- 使用者ID欄位：int型別、不允許為空、設為自增列、宣告為主鍵
`user_id` int(8) NOT NULL AUTO_INCREMENT PRIMARY "i_p_id" COMMENT '使用者ID',
-- 使用者名稱稱欄位：字串型別、執行為空、預設值為“新使用者”
`user_name` varchar(255) NULL DEFAULT "新使用者" COMMENT '使用者名稱'
)
-- 儲存引擎為InnoDB、編碼格式為utf-8、字元排序規則為utf8_general_ci、行格式為Compact
ENGINE = InnoDB
CHARACTER SET = utf8
COLLATE = utf8_general_ci
ROW_FORMAT = Compact;
上述程式碼塊中就貼出了一個建立表的例子，大家在建立表時可根據需求自行選擇需要的欄位選項、表選項。
接下來一起來看看其他關於表操作的
SQL命令，但對於增刪改查的命令會放在後面講。
show table status like 'zz_users'\G;：縱排輸出一張表的狀態資訊。alter table 表名 表選項;：修改一張表的結構，如alter table xxx engine=MyISAM。rename table 表名 to 新表名;：修改一張表的表名。alter table 表名 欄位操作;：修改一張表的欄位結構，操作如下：add column 欄位名 資料型別：向已有的表結構新增一個欄位。add primary key(欄位名)：將某個欄位宣告為主鍵。add foreing key 外來鍵欄位 表名.欄位名：將一個欄位設定為另一張表的外來鍵。add unique 索引名(欄位名)：為一個欄位建立唯一索引。add index 索引名(欄位名)：為一個欄位建立普通索引。drop column 欄位名：在已有的表結構中刪除一個欄位。modify column 欄位名 欄位選項：修改一個欄位的欄位選項。change column 欄位名 新欄位名：修改一個欄位的欄位名稱。drop primary key：移除表中的主鍵。drop index 索引名：刪除表中的一個索引。drop foreing key 外來鍵：刪除表中的一個外來鍵。
drop table if exists 表名：如果一張表存在，則刪除對應的表。truncate table 表名：清空一張表的所有資料。create table 表名 like 要複製的表名：複製一張表的結構，然後建立一張新表。create table 表名 as select * from 要複製的表名：同時複製表結構和資料建立新表。
1.4、表的分析、檢查、修復與優化操作
MySQL本身提供了一系列關於表的分析、檢查與優化命令：
- ①分析表：分析表中鍵的分佈，如主鍵、唯一鍵、外來鍵等是否合理。
- ②檢查表：檢查表以及表的資料檔案是否存在錯誤。
- ③修復表：當一個表的資料或結構檔案損壞時，可以修復表結構（僅支援MyISAM表）。
- ④優化表：消除delete、update語句執行時造成的空間浪費。
分析表
語法如下：
sql
analyze [local | no_write_to_binlog] table 表名1;
其中的可選引數local、no_write_to_binlog代表是否將本條SQL記錄進bin-log日誌，預設情況下是記錄的，加上這兩個引數中的其中一個後則不會記錄，執行效果如下：
如果Msg_text顯示的是OK，則代表這張表的鍵不存在問題，存在問題的情況我這邊就不模擬了，後面舉例聊。
檢查表
語法如下：
sql
check table 表名1,表名2... [檢查選項];
分析、檢查、優化、修復的命令都支援同時操作多張表，不同的表之間只需用,逗號隔開即可。檢查命令有多個可選項，如下：
- quick：不掃描行資料，不檢查連結錯誤，僅檢查表結構是否有問題。
- fast：只檢查表使用完成後，是否正確關閉了表文件的FD檔案描述符。
- changed：從上述檢查過的位置開始，只檢查被更改的表資料。
- medium：檢查行資料，收集每一行資料的鍵值（主鍵、外來鍵...），並計算校驗和，驗證資料是否正確。
- extended：對每行資料的所有欄位值進行檢查，檢查完成後可確保資料100%正確。
先來看看執行結果吧，如下：
這回的結果出現了些許不同，Msg_text中出現了一個Error資訊，提示咱們檢查的zz_u表不存在，而對於一張存在的zz_users表，則返回OK，表示沒有任何問題。
當然，這裡對於其他的檢查選項就不做測試了，大家可以自行實驗，比如把表的結構檔案或資料檔案，在本地開啟手動刪除前面的一點點資料，然後再執行檢查命令，其實你也可以觀察到，提示“資料不完整”的資訊（但需要先停止執行
MySQL，並且用本地表測試，不要用線上表瞎搞）。
修復表
語法如下：
sql
repair [local | no_write_to_binlog] table 表名 [quick] [extended] [use_frm];
值得一提的是，修復表的命令不支援InnoDB引擎，僅支援MyISAM、CSV、引擎，比如基於InnoDB引擎的表執行修復命令時，提示如下：
上述Msg_text資訊翻譯過來的意思是：選擇的表其引擎並不支援修復命令。
InnoDB引擎其實也有修復機制，可以在my.ini/my.conf檔案中加一行配置：[mysqld]innodb_force_recovery = 1，這樣在啟動時會強制恢復InnoDB的資料。
上述這個修復機制預設是不開啟的，因為InnoDB不需要這個恢復機制，畢竟之前在《引擎篇》中聊過：InnoDB有完善的事務和持久化機制，客戶端提交的事務都會持久化到磁碟，除非你人為損壞InnoDB的資料檔案，否則基本上不會出現InnoDB資料損壞的情況。
優化表
語法如下：
sql
optimize [local | no_write_to_binlog] table 表名;
這裡值得一提的是：此優化非彼優化，並不意味著你的表存在效能問題，執行後它會自動調優，而是指清除老資料，執行效果如下：
還記得之前在《MVCC機制》中聊過的隱藏欄位嘛？其實刪除一條資料本質上並不會立馬從磁碟移除，而是會先改掉隱藏的刪除標識位，執行這條優化命令後，MySQL會將一些已經delete過的資料徹底從磁碟刪除，從而釋放這些“廢棄資料”佔用的空間。
上面的執行結果顯示：“目前表的資料已經是最新的了”，這是啥原因呢？因為我這張表中壓根沒有資料，哈哈哈，沒有插入過資料，自然也不會有刪除資料的動作，因此就會出現這個提示。
OK~，到這裡對於分析表、檢查表、修復表以及優化表就已經介紹清楚啦！其實這幾個功能，在mysqlcheck工具中也有提供。
1.5、MySQL忘記密碼怎麼辦？
到這裡，對於一些MySQL基礎命令、庫表命令就打住了，最後再來講一個比較實用的知識點：MySQL忘記密碼怎麼辦？對於這種情況其實也十分常見，哪忘記時該如何處理呢？可以重置密碼！
①先停掉MySQL的後臺服務：
- Windows系統請執行：net stop mysql
- Linux系統請執行：安裝目錄/mysql shutdown（kill強殺程序也可以）
②進入到MySQL安裝目錄下的bin資料夾內，執行mysqld --skip-grant-tables去掉連線認證。
③因為上面關掉了連線認證，接著輸入mysql敲下回車，進入mysql終端命令列。
④輸入use mysql;，進入MySQL自身的系統資料庫，然後輸入show tables;檢視所有表。
⑤查詢MySQL中註冊的所有使用者：select user,host,password from user;。
⑥使用update語句，更改root超級管理員的賬號密碼，如下：
sql
update user set password=password('123') where user="root" and host="localhost";
因為
MySQL本身會用一張使用者表來儲存所有已建立的賬號資訊，連線時的效驗基準也是來自於該表中的資料，因此在這裡修改密碼後，再用新密碼登入即可！
如果不是root賬號的密碼忘記了，則可以直接登入root賬號修改其他使用者的密碼，如果是root賬號則按照上述流程操作。
完成之後可以用
mysql -uroot -p123連線一下，測試密碼是否被重置。
二、增刪改查語句
2.1、基本的增刪改查語句
插入資料
增刪改查俗稱為CRUD，這也是MySQL執行之後執行次數最多的一類SQL語句，同時也是每位開發者寫的最多的SQL語句，接下來則說說這塊的語句，首先登場的是咱們的幾位老夥伴，即insert、delete、update、select...這類普通SQL語句。
insert into 表名(欄位名...) values(欄位值...);：向指定的表中插入一條資料。insert into 表名(欄位名...) values(欄位值...),(...)...;：向表中插入多條資料。insert into 表名 set 欄位名=欄位值,...;：插入一條資料，但只插入某個欄位的值。
如果要插入一條完整的資料，欄位名可以用*代替所有欄位，除開上述兩種插入資料的基本方式外，還有幾種批量插入的方式，如下：
```sql
-- 使用insert語句批量插入另一張表中查詢的資料
insert into 表名(欄位名...) select 欄位名... from 表名...;
-- 使用replace語句來實現批量插入
replace into 表名(欄位名1,欄位名2...) values(欄位值....),(欄位值...),...;
``
上述批量插入資料的方式中，還可以通過replace關鍵字來實現插入，它與insert有啥區別呢？答案在於它可以實現批量更新，使用replace關鍵字來插入資料的表必須要有主鍵，MySQL`會根據主鍵值來決定新增或修改資料，當批量插入的資料中，主鍵欄位值在表中不存在時，則會向表中插入一條相應的資料，而當插入資料中的主鍵值存在時，則會使用新資料覆蓋原有的老資料。
刪除資料
delete from 表名;：刪除一張表的所有資料。delete from 表名 where 條件;：根據條件刪除一條或多條資料。truncate table 表名：清空一張表的所有資料。
修改資料
update 表名 set 欄位名=欄位值,...;：修改表中所有記錄的資料。update 表名 set 欄位名=欄位值,... where 條件;：根據條件修改一條或多條記錄的資料。replace 表名(欄位名1,...) values(欄位值...),...;：批量修改對應主鍵記錄的資料。
查詢資料
select * from 表名;：查詢一張表的所有資料。select * from 表名 where 條件;：根據條件查詢表中相應的資料。select 欄位1,欄位2... from 表名 where 條件;：根據條件查詢表中相應資料的指定欄位。select 函式(欄位) from 表名;：對查詢後的結果集，進行某個函式的特殊處理。
上述三種是最基本的查詢方式，接著來看一些高階查詢語法，如下： ```sql -- 為查詢出來的欄位取別名 select 欄位1 as 別名,... from 表名 where 條件; select 欄位1 別名,... from 表名;
-- 為查詢出的表取別名 select * from 表名 as 別名;
-- 以多條件查詢資料 select * from 表名 where 欄位1=值1 and 欄位2=值2 and ...; -- 所有條件都符合時才匹配 select * from 表名 where 欄位1=值1 or 欄位2=值2 or ...; -- 符合任意條件的資料都會返回 -- =符號，可以根據情況換為>、<、>=、<=、!=、between and、is null、not is null這些
-- 對查詢後的結果集使用函式處理 select 函式(欄位) from 表名 where 條件;
-- 對查詢條件使用函式處理 select * from 表名 where 函式(條件);
-- 模糊查詢 select * from 表名 where 欄位 like "%字元"; -- 查詢欄位值以指定字元結尾的所有記錄 select * from 表名 where 欄位 like "字元%"; -- 查詢欄位值以指定字元開頭的所有記錄 select * from 表名 where 欄位 like "%字元%"; -- 查詢欄位值包含指定字元的所有記錄
-- 按照多值查詢對應行記錄 select * from 表名 where 欄位 in (值1，值2,...); -- 按照多值查詢相反的行記錄 select * from 表名 where 欄位 not in (值1，值2,...); -- 基於多個欄位做多值查詢 select * from 表名 where (欄位1,欄位2...) in ((值1，值2,...),(...),...);
-- 只需要查詢結果中的前N條資料 select * from 表名 limit N; -- 返回查詢結果中 N~M 區間的資料 select * from 表名 limit N,M;
-- 聯合多條SQL語句查詢（union all表示不去重，union表示對查詢結果去重）
select * from 表名 where 條件
union all
select * from 表名 where 條件;
``
當然，對於MySQL`中支援的函式稍後再展開聊，下面再聊聊一些其他的高階查詢語法，如分組、過濾、子查詢、關聯查詢等。
分組過濾、資料排序
寫SQL語句時，有些需求往往無法通過最基本的查詢語句來實現，因此就需要用到一些高階的查詢語法，例如分組、過濾、排序等操作，接著先聊聊這個，語法如下：
```sql
-- 基於一個欄位進行排序查詢
select * from 表名 order by 欄位名 asc; -- 按欄位值正序返回結果集
select * from 表名 order by 欄位名 desc; -- 按欄位值倒序返回結果集
select * from 表名 order by 欄位1 asc,欄位2 desc; -- 按照多欄位進行排序查詢
-- 基於欄位進行分組 select * from 表名 group by 欄位1,欄位2....;
-- 基於分組查詢後的結果做條件過濾
select * from 表名 group by 欄位1 having 條件;
``
實際上group by、having這些語句，更多的要配合一些聚合函式使用，如min()、max()、count()、sum()、avg()....，這樣才能更符合業務需求，但對於聚合函式後面再介紹，先簡單說說where、having`的區別：
這兩個關鍵字都是用來做條件過濾的，但
where優先順序會比group by高，因此當分組後需要再做條件過濾時，就無法使用where來做篩選，而having就是用來對分組後的結果做條件過濾的。查詢語句中的各類關鍵字執行優先順序為：from → where → select → group by → having → order by。
子查詢
子查詢也可以理解成是查詢巢狀，是指一種由多條SQL語句組成的查詢語句，語法如下：
```sql
-- 基於一條SQL語句的查詢結果進一步做查詢
select * from (select * from 表名 where 條件) as 別名 where 條件;
-- 將一條SQL語句的查詢結果作為條件繼續查詢（只適用於子查詢返回單值的情況） select * from 表名 where 欄位名 = (select 欄位名 from 表名 where 條件);
-- 將一條SQL語句的查詢結果作為條件繼續查詢（適用於子查詢返回多值的情況） select * from 表名 where 欄位名 exists (select 欄位名 from 表名 where 條件); -- 上述的exists可以換為not exists，表示查詢不包含相應條件的資料
-- 將一條SQL語句的多個查詢結果，作為條件與多個欄位進行範圍查詢
select * from 表名 where (欄位1,欄位2...) in (select 欄位1,欄位2... from 表名);
``
在上述子查詢語法中，exists的作用和in大致相同，只不過not in時會觸發全表掃描，而not exists依舊可以走索引查詢，因此通常情況下儘量使用not exists代替not in`來查詢資料。
關聯查詢
關聯查詢也被稱之為連表查詢，也就是指利用主外來鍵連線多張表去查詢資料，這幾乎也是日常開發中寫的最多的一類查詢語句，MySQL中支援多種關聯型別，如：
- 交叉連線
- 內連線
- 外連線：
- 左連線
- 右連線
- 全連線
語法如下： ```sql -- 交叉連線：預設把前一張表的每一行資料與後一張表的所有資料做關聯查詢 select * from 表1,表2...; -- 這種方式預設採用交叉連線的方式 select * from 表1 cross join 表2; -- 顯式宣告採用交叉連線的方式
-- 內連線：只返回兩張表條件都匹配的資料 -- 隱式的內連線寫法 select * from 表1,表2... where 表1.欄位 = 表2.欄位 ...; -- 等值內連線 select * from 表1 別名1 inner join 表2 別名2 on 別名1.欄位 = 別名2.欄位; -- 不等式內連線 select * from 表1 別名1 inner join 表2 別名2 on 別名1.欄位 < 別名2.欄位;
-- 左外連線：左表為主，右表為次，無論左表在右表是否匹配，都返回左表資料，缺失的右表資料顯示NULL select * from 表1 left join 表2 on 表1.欄位 = 表2.欄位;
-- 右外連線：和左連線相反，右表為主，左表為次，永遠返回右表的所有資料 select * from 表1 right join 表2 on 表1.欄位 = 表2.欄位;
-- 全外連線：兩張表沒有主次之分，每次查詢都會返回兩張表的所有資料，不匹配的顯示NULL -- MySQL中不支援全連線語法，只能通過union all語句，將左、右連線查詢拼接起來實現 select * from 表1 left join 表2 on 表1.欄位 = 表2.欄位 union all select * from 表1 right join 表2 on 表1.欄位 = 表2.欄位;
-- 繼續拼接查詢兩張以上的表
select * from 表1 left join 表2 on 表1.欄位 = 表2.欄位 left join 表3 on 表2.欄位 = 表3.欄位;
-- 通過隱式連線的方式，查詢兩張以上的表
select * from 表1,表2,表3... where 表1.欄位 = 表2.欄位 and 表1.欄位 = 表3.欄位...;
-- 通過子查詢的方式，查詢兩張以上的表
select * from
(表1 as 別名1 left join 表2 as 別名2 on 別名1.欄位 = 別名2.欄位)
left join
表3 as 別名3 on 別名1.欄位 = 別名3.欄位;
``
對於連表查詢的語法相信大家都並不陌生，因此不做過多產生，重點講一下多表聯查時的笛卡爾積問題，所謂的笛卡爾積問題就是指兩張表的所有資料都做關聯查詢，一般連表查詢都需要指定連線的條件，但如果不指定時，MySQL預設會將左表每一條資料挨個和右表所有資料關聯一次，然後查詢一次資料。比如左表有3條資料，右表有4條資料，笛卡爾積情況出現時，一共就會查詢出3 x 4 = 12`條資料。
笛卡爾積現象出現時，會隨著表資料增長越來越大，因此在連表查詢時一定要消除笛卡爾積問題，咋消除呢？其實就是指定加上關聯條件即可。
至此，對於一些增刪改查的基本語句就已介紹清楚啦~，但實際業務開發過程中，往往會結合資料庫所提供的函式一起操作，因此接下來再聊聊MySQL支援的函式。
三、MySQL資料庫函式
MySQL中提供了豐富的函式支援，包括數學函式、字串函式、日期和時間函式、條件判斷函式、系統資訊函式、加密函式、格式化函式等，通過這些函式，一方面可以簡化業務的程式碼量，另一方面還能更好的實現各類特殊業務需求，下來一起來聊聊MySQL支援的函式。
3.1、數學函式
數學函式是MySQL中最常用的一類函式，主要用來處理所有數值型別的欄位值，一起來看看。
- abs(X)：返回X的絕對值，如傳進-1，則返回1。
- ln(X)：返回X的自然相對數。
- log(X,Y)：返回以X的以Y為底的對數。
- log10(X):返回以X基數為10的對數。
- bin(X)：返回X的二進位制值。
- oct(X)：返回X的八進位制值。
- hex(X)：返回X的十六進位制值。
- mod(X,Y)：返回X除以Y的餘數。
- ceil(X) | ceiling(X)：返回不小於X的最小整數，如傳入1.23，則返回2。
- round(X)：返回X四捨五入的整數。
- floop(X)：返回X向下取整後的值，如傳入2.34，會返回2。
- greatest(X1,X2....,Xn)：返回集合中的最大整數值。
- least(X1,X2....,Xn)：返回集合中的最小整數值。
- rand(N)：返回一個0~N``0~1之間的隨機小數（不傳參預設返回0~1之間的隨機小數）。
- sign(X)：傳入正數，返回1；傳入負數，返回-1；傳入0，返回0。
- pow(X,Y) | power(X,Y)：返回X的Y次方值。
- pi()：返回四捨五入後的圓周率，3.141593。
- sin(X)：返回X的正弦值。
- asin(X)：返回X的反正弦值。
- cos(X)：返回X的餘弦值。
- acos(X)：返回X的反餘弦值。
- tan(X)：返回X的正切值。
- atan(X)：返回X的反正切值。
- cot(X)：返回X的餘切值。
- radians(x)：返回x由角度轉化為弧度的值。
- degrees(x)：返回x由弧度轉化為角度的值。
- sqrt(X)：返回X的平方根。
- exp(e,X)：返回e的x乘方的值。
- truncate(X,N)：返回小數X保留N位精準度的小數。
- format(x,y)：將x格式化位以逗號隔開的數字列表，y是結果的小數位數。
- inet_aton(ip)：將IP地址以數字的形式展現。
- inet_ntoa(number)：顯示數字代表的IP地址。
- ......
3.2、字串函式
ascii(C)：返回字元C的ASCII碼。length(S)：返回字串的佔位空間，傳入“竹子愛熊貓”，返回15，一個漢字佔位3位元組。bit_length(S)：返回字串的位元長度。concat(S1,S2,...)：合併傳入的多個字串。concat_wa(sep,S1,S2...)：合併傳入的多個字串，每個字串之間用sep間隔。position(str,s) | locate(str,s)：返回s在str中第一次出現的位置，沒有則返回0。find_in_set(S,list)：返回字串S在list列表中的位置。insert(S1,start,end,S2)：使用S2字串替換掉S1字串中start~end的內容。lcase(S) | lower(S)：將傳入的字串中所有大寫字母轉換為小寫。ucase(S) | upper(S)：將傳入的字串中所有小寫字母轉換為大寫。left(S,index)：從左側開始擷取字串S的index個字元。right(S,index)：從右側開始擷取字串S的index個字元。trim(S)：刪除字元S左右兩側的空格。rtrim(S)：刪除字元S右側的空格。replace(S,old,new)：使用new新字元替換掉S字串中的old字元。repeat(str,count)：將str字串重複count次後返回。substring(S,index,N)：擷取S字串，從index位置開始，返回長度為N的字串。reverse(S)：將傳入的字串反轉，即傳入Java，返回avaJ。quote(str)：用反斜槓轉移str中的英文單引號。strcmp(S1,S2)：比較兩個字元是否相同。lpad(str,len,s)：對str字串左邊填充len個s字元。rpad(str,len,s)：對str字串右邊填充len個s字元。......
3.3、日期和時間函式
curdate() | current_date()：返回當前系統的日期，如2022-10-21。curtime() | current_time()：返回當前系統的時間，如17:30:52。now() | sysdate()：返回當前系統的日期時間，如2022-10-21 17:30:59。unix_timestamp()：獲取一個數值型別的unix時間戳，如1666348711。from_unixtime()：將unix_timestamp()獲取的數值時間戳，格式化成日期格式。month(date)：獲取date中的月份。year(date)：獲取date中的年份。hour(date)：獲取date中的小時。minute(date)：獲取date中的分鐘。second(date)：獲取date中的秒數。monthname(date)：返回date中月份的英文名稱。dayname(date)：獲取日期date是星期幾，如Friday。dayofweek(date)：獲取date位於一週的索引位置，週日是1、週一是2...週六是7。week(date)：獲取date是本年的第多少周。quarter(date)：獲取date位於一年中的哪個季度（1~4）。dayofyear(date)：獲取date是本年的第多少天。dayofmonth(date)：獲取date是本月的第多少天。time_to_sec(time)：將傳入的時間time轉換為秒數，比如"01:00:00" = 3600s。date_add(date,interval 時間 單位) | adddate(...)：將date與給定的時間按單位相加。date_sub(date,interval 時間 單位) | subdate(...)：將date與給定的時間按單位相減。addtime(date,time)：將date加上指定的時間，如addtime(now(),"01:01:01")。subtime(date,time)：將date減去指定的時間。datediff(date1,date2)：計算兩個日期之間的間隔天數。last_day(date)：獲取date日期這個月的最後一天。date_format(date,format)：將一個日期格式化成指定格式，format可選項如下：%a：工作日的英文縮寫（Sun~Sat）。%b：月份的英文縮寫（Jan~Dec）。%c：月份的數字格式（1~12）。%M：月份的英文全稱（January~December）。%D：帶有英文字尾的數字月份（1th、2st、3nd....）。%d：一個月內的天數，雙數形式（01、02、03....31）。%e：一個月內的天數，單數形式（1、2、3、4....31）。%f：微妙（000000~999999）。%H：一天內的小時，24小時的週期（00、01、02...23）。%h | %I：一天內的小時，12小時的週期（01、02、03...12）。%i：一小時內的分鐘（00~59）。%j：一年中的天數（001~366）。%k：以24小時制顯示時間（00~23）。%l：以12小時制顯示時間（01~12）。%m：月份的數字形式，雙數形式（01~12）。%p：一天內的時間段（上午AM、下午PM）。%r：12小時制的時間（12:01:09 AM）。%S | %s：秒數，雙數形式（00~59）。%T：24小時制的時間（23:18:22）。%U：一年內的周（00~53）。
time_format(time,format)：將一個時間格式化成指定格式。str_to_date(str,format)：將日期字串，格式化成指定格式。timestampdiff(unit,start,end)：計算兩個日期之間間隔的具體時間，unit是單位：year：年。quarter：季度。month：月。week：周。day：天。hour：小時。minute：分鐘。second：秒數。microsecond：微妙。
weekday(date)：返回date位於一週內的索引（0是週一...6是週日）。
3.4、聚合函式
聚合函式一般是會結合select、group by having篩選資料使用。
- max(欄位名)：查詢指定欄位值中的最大值。
- min(欄位名)：查詢指定欄位值中的最小值。
- count(欄位名)：統計查詢結果中的行數。
- sum(欄位名)：求和指定欄位的所有值。
- avg(欄位名)：對指定欄位的所有值，求出平均值。
- group_concat(欄位名)：返回指定欄位所有值組合成的結果，如下：
- distinct(欄位名)：對於查詢結果中的指定的欄位去重。
這裡稍微介紹一個日常業務中碰到次數較多的需求： ```sql select * from zz_users; +---------+-----------+----------+----------+---------------------+ | user_id | user_name | user_sex | password | register_time | +---------+-----------+----------+----------+---------------------+ | 1 | 熊貓 | 女 | 6666 | 2022-08-14 15:22:01 | | 2 | 竹子 | 男 | 1234 | 2022-09-14 16:17:44 | | 3 | 子竹 | 男 | 4321 | 2022-09-16 07:42:21 | | 4 | 黑熊 | 男 | 8888 | 2022-09-17 23:48:29 | | 8 | 貓熊 | 女 | 8888 | 2022-09-27 17:22:29 | | 9 | 棕熊 | 男 | 0369 | 2022-10-17 23:48:29 | +---------+-----------+----------+----------+---------------------+
-- 基於性別欄位分組，然後顯示各組中的所有ID
select
convert(
group_concat(user_id order by user_id asc separator ",")
using utf8) as "分組統計"
from zz_users group by user_sex;
+-------------+
| 分組統計 |
+-------------+
| 1,8 |
| 2,3,4,9 |
+-------------+
``
上述利用了group_concat()、group by實現了按照一個欄位分組後，顯示對應分組的所有ID`。
3.5、控制流程函式
if(expr,r1,r2)：expr是表示式，如果成立返回r1，否則返回r2。ifnull(v,r)：如果v不為null則返回v，否則返回r。nullif(v1,v2)：如果v1 == v2，則返回null，如果不相等則返回V1。
```sql -- if的用例 select if(user_id > 3,"√","×") from zz_users;
-- ifnull的用例 select ifnull(user_id,"×") from zz_users;
-- case語法1： case <表示式> when <值1> then <操作> when <值2> then <操作> ... else <操作> end; -- 用例：判斷當前時間是星期幾 select case weekday(now()) when 0 then '星期一' when 1 then '星期二' when 2 then '星期三' when 3 then '星期四' when 4 then '星期五' when 5 then '星期六' else '星期天' end as "今天是星期幾？";
-- case語法2：
case
when <條件1> then <命令>
when <條件2> then <命令>
...
else commands
end;
-- 用例：判斷今天是星期幾
select case
when weekday(now()) = 0 then '星期一'
when weekday(now()) = 1 then '星期二'
when weekday(now()) = 2 then '星期三'
when weekday(now()) = 3 then '星期四'
when weekday(now()) = 4 then '星期五'
when weekday(now()) = 5 then '星期六'
else '星期天'
end as "今天是星期幾？";
``
簡單聊一下CASE語法，第一種語法就類似於Java中的switch，而第二種語法就類似於多重if，通過CASE語法能夠讓SQL`更加靈活，完成類似於儲存過程的工作。
3.6、加密函式
password(str)：將str字串以資料庫密碼的形式加密，一般用在設定DB使用者密碼上。md5(str)：對str字串以MD5不可逆演算法模式加密。encode(str,key)：通過key金鑰對str字串進行加密（對稱加密演算法）。decode(str,key)：通過key金鑰對str字串進行解密。aes_encrypt(str,key)：通過key金鑰對str字串，以AES演算法進行加密。aes_decrypt(str,key)：通過key金鑰對str字串，以AES演算法進行解密。sha(str)：計算str字串的雜湊演算法校驗值。encrypt(str,salt)：使用salt鹽值對str字串進行加密。decrypt(str,salt)：使用salt鹽值對str字串進行解密。
3.7、系統函式
version()：查詢當前資料庫的版本。connection_id()：返回當前資料庫連線的ID。database() | schema()：返回當前連線位於哪個資料庫，即use進入的庫。user()：查詢當前的登入的所有使用者資訊。system_user()：返回當前登入的所有系統使用者資訊。session_user()：查詢所有連線的使用者資訊。current_user()：查詢當前連線的使用者資訊。charset(str)：返回當前資料庫的編碼格式。collation(str)：返回當前資料庫的字元排序規則。benchmark(count,expr)：將expr表示式重複執行count次。found_rows()：返回最後一個select查詢語句檢索的資料總行數。cast(v as 型別)：將v轉換為指定的資料型別。
3.8、......
除開上述這些函式之外，其實在
MySQL還有很多很多的函式，但目前幾乎已經將所有常用的函式全部列出來了，因此對於其他偏冷門一些的函式就不再介紹。當然，就算你需要的某個功能在MySQL中沒有提供函式支援，你也可以通過create function的方式自定義儲存函式，其邏輯與上篇講到的《MySQL儲存過程》大致相同。
四、MySQL支援的資料型別
這裡所謂的資料型別，也就是隻在建立表時可以選擇的列欄位型別，在MySQL中其實可以通過：
- help data types;：檢視當前版本支援的所有資料型別。如下(MySQL5.1版本)：
總體可分為數值型別、字元/串型別、時間/日期型別、其他型別四種，下面一起來聊聊吧。
4.1、數值型別
tinyint：小整數型別，佔位1Bytes，取值範圍-128~127。smallint：中整數型別，佔位2Bytes，取值範圍-32768~32767。mediumint：中大整數型別，佔位3Bytes，取值範圍-8388608~8388607。int | integer：常用整數型別，佔位4Bytes，取值範圍-2147483548~2147483647。bigint：超大整數型別，佔位8Bytes，取值範圍-9223372036854775808~9223372036854775807。float：單精度浮點數型別，佔位4Bytes，取值範圍-3.4E+38 ~ 3.4E+38。double：雙精度浮點數型別，佔位8Bytes，取值範圍-1.7E-308～1.7E+308。decimal(m,d)：小數型別，佔位和取值範圍都依賴m、d值決定，m是小數點後面的精度，d是小數點前面的標度。bit(m)：儲存位值，可儲存m個位元位，取值範圍是1~64。
4.2、字串型別
char：定長字串型別，儲存空間0~255Bytes。varchar：變長字串型別，儲存空間0~65535Bytes。tinyblob：二進位制短字串型別，儲存空間0~255Bytes。tinytext：短文字字串型別，儲存空間0~255Bytes。blob：二進位制長字串型別，儲存空間0~65535Bytes。text：長文字字串型別，儲存空間0~65535Bytes。mediumblob：二進位制大字串型別，儲存空間0~16777215Bytes。mediumtext：大文字字串型別，儲存空間0~16777215Bytes。longblob：二進位制超大字串型別，儲存空間0~4294967295Bytes。longtext：超大文字字串型別，儲存空間0~4294967295Bytes。binary(m)：定長字串型別，儲存空間為M個字元。varbinary(m)：定長字串型別，儲存空間為M個字元+1個位元組。
一般在為列指定資料型別時，都會varchar(255)這樣寫，其實中間的這個數字限制的並不是位元組長度，而是字元數量，比如varchar(255)，表示該列最大能儲存255個字元。
4.3、時間/日期型別
date：日期型別，佔位3Bytes，格式為YYYY-MM-DD。time：時間型別，佔位3Bytes，格式為hh:mm:ss。year：年份型別，佔位1Bytes，格式為YYYY。datetime：日期時間型別，佔位8Bytes，格式為YYYY-MM-DD hh:mm:ss。timestamp：時間戳型別，佔位4Bytes，格式為YYYYMMDDhhmmss，最大可精確到微妙。
4.4、其他型別
json：MySQL5.7版本引入的，在此之前只能用字串型別來儲存json資料，需要通過函式輔助使用：json_array(...)：儲存一個json陣列的資料。json_array_insert(欄位,'$[下標]',"值")：在指定的json陣列下標位置上插入資料。json_object(...)：儲存一個json物件。json_extract(欄位,'$.鍵')：查詢鍵為某個值的所有資料。json_search(....)：通過值查詢鍵。json_keys(欄位)：獲取某個欄位的所有json鍵。json_set(欄位,'$.鍵',"值")：更新某個鍵的json資料。json_replace(...)：替換某個json中的資料。json_remove(欄位,'$.鍵')：刪除某個json資料。.....：還有一些其他json型別的函式，這裡不再說明，一般json型別用的較少。
enum(選項1,選項2...選項n)：新增資料時只能從已有的選項中選擇一個並插入。set(選項1,選項2...選項n)：新增資料時可以從已有的選項中選擇多個並插入。eometry、point、linestring、polygon：空間型別（接觸不多）。
稍微解釋一下enum、set型別，這兩種型別就類似於平時的單選框和多選框，必須從已有的選項中選擇，兩者的區別在於：enum列舉型別只能選擇一個選項，而set集合型別可以選擇多個選項（其實用的比較少，多數情況下都是直接在客戶端中處理）。
OK~，簡單瞭解
MySQL支援的資料型別後，接著再來看看其他一些常用的SQL命令。
五、索引相關的命令
```sql
-- 建立一個普通索引（方式①）
create index 索引名 ON 表名 (列名(索引鍵長度) [ASC|DESC]);
-- 建立一個普通索引（方式②）
alter table 表名 add index 索引名(列名(索引鍵長度) [ASC|DESC]);
-- 建立一個普通索引（方式③）
CREATE TABLE tableName(
columnName1 INT(8) NOT NULL,
columnName2 ....,
.....,
index [索引名稱] (列名(長度))
);
-- 後續其他型別的索引都可以通過這三種方式建立
-- 建立一個唯一索引 create unique 索引名 ON 表名 (列名(索引鍵長度) [ASC|DESC]);
-- 建立一個主鍵索引 alter table 表名 add primary key 索引名(列名);
-- 建立一個全文索引 create fulltext index 索引名 ON 表名(列名);
-- 建立一個字首索引 create index 索引名 ON 表名 (列名(索引鍵長度));
-- 建立一個空間索引 alter table 表名 add spatial key 索引名(列名);
-- 建立一個聯合索引
create index 索引名 ON 表名 (列名1(索引鍵長度),列名2,...列名n);
上面將`MySQL`中建立各類索引的多種方式都列出來了，接著再聊聊索引檢視、使用與管理的命令。sql
-- 檢視一張表上的所有索引
show index from 表名;
-- 刪除一張表上的某個索引 drop index 索引名 on 表名;
-- 強制指定一條SQL走某個索引查詢資料 select * from 表名 force index(索引名) where .....;
-- 使用全文索引（自然搜尋模式） select * from 表名 where match(索引列) against('關鍵字'); -- 使用全文索引（布林搜尋模式） select * from 表名 where match(索引列) against('布林表示式' in boolean mode); -- 使用全文索引（拓展搜尋模式） select * from 表名 where match(索引列) against('關鍵字' with query expansion);
-- 分析一條SQL是否命中了索引 explain select * from 表名 where 條件....; ```
六、事務與鎖相關的命令
start transaction; | begin; | begin work;：開啟一個事務。commit;：提交一個事務。rollback;：回滾一個事務。savepoint 事務點名稱;：新增一個事務點。rollback to 事務點名稱;：回滾到指定名稱的事務點。release savepoint 事務點名稱;：刪除一個事務點。select @@tx_isolation;：查詢事務隔離級別（方式一）。show variables like '%tx_isolation%';：查詢事務隔離級別（方式二）。set transaction isolation level 級別：設定當前連線的事務隔離級別。set @@tx_isolation = "隔離級別";：設定當前會話的事務隔離級別。set global transaction isolation level 級別;：設定全域性的事務隔離級別，選項如下：read uncommitted：讀未提交級別。read committed：讀已提交級別。repeatable-read：可重複讀級別。serializable：序列化級別。
show variables like 'autocommit';：檢視自動提交事務機制是否開啟。set @@autocommit = 0|1|ON|OFF;：開啟或關閉事務的自動提交。select ... lock in share mode;：手動獲取共享鎖執行SQL語句。select ... for share;：MySQL8.0之後優化版的共享鎖寫法。select ... for update;：手動獲取排他鎖執行。lock tables 表名 read;：獲取表級別的共享鎖。lock tables 表名 write;：獲取表級別的排他鎖。show open tables where in_use > 0;：檢視目前資料庫中正在使用的表鎖。flush tables with read lock;：獲取全域性鎖。unlock tables;：釋放已獲取的表鎖/全域性鎖。update 表名 set version=version+1 ... where... and version=version;：樂觀鎖模式執行。
七、儲存過程、儲存函式與觸發器
```sql -- 建立一個儲存過程 DELIMITER $ CREATE PROCEDURE 儲存過程名稱(返回型別 引數名1 引數型別1, ....) [ ...... ] BEGIN -- 具體組成儲存過程的SQL語句.... END $ DELIMITER ;
-- 建立一個儲存函式 DELIMITER $ CREATE FUNCTION 儲存函式名稱(引數名1 引數型別1, ....) RETURNS 資料型別 [NOT] DETERMINISTIC statements BEGIN -- 具體組成儲存函式的SQL語句.... END $ DELIMITER ;
-- 建立一個觸發器 CREATE TRIGGER 觸發器名稱 {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON 表名 FOR EACH ROW -- 觸發器的邏輯（程式碼塊）;
-- ------------- 使用者變數與區域性變數 --------------- -- 定義、修改使用者變數 set @變數名稱 = 變數值; -- 查詢使用者變數 select @變數名稱;
-- 定義區域性變數 DECLARE 變數名稱 資料型別 default 預設值; -- 為區域性變數賦值（方式1） SET 變數名 = 變數值; -- 為區域性變數賦值（方式2） SET 變數名 := 變數值; -- 為區域性變數賦值（方式3） select 查詢結果欄位 into 變數名 from 表名;
-- ------------- 流程控制 --------------- -- if、elseif、else條件分支語法 IF 條件判斷 THEN -- 分支操作..... ELSEIF 條件判斷 THWN -- 分支操作..... ELSE -- 分支操作..... END IF
-- case分支判斷語句 -- 第一種語法 CASE 變數 WHEN 值1 THEN -- 分支操作1.... WHEN 值2 THEN -- 分支操作2.... ..... ELSE -- 分支操作n.... END CASE;
-- 第二種語法 CASE WHEN 條件判斷1 THEN -- 分支操作1.... WHEN 條件判斷2 THEN -- 分支操作2.... ..... ELSE -- 分支操作n.... END CASE;
-- 迴圈：LOOP、WHILE、REPEAT -- loop迴圈 迴圈名稱:LOOP -- 迴圈體.... END LOOP 迴圈名稱;
-- while迴圈 【迴圈名稱】:WHILE 迴圈條件 DO -- 迴圈體.... END WHILE 【迴圈名稱】;
-- repeat迴圈 【迴圈名稱】:REPEAT -- 迴圈體.... UNTIL 結束迴圈的條件判斷 END REPEAT 【迴圈名稱】;
-- 迴圈跳轉 LEAVE 【迴圈名稱】; -- 結束某個迴圈體 ITERATE 【迴圈名稱】; -- 跳出某個迴圈體，繼續下次迴圈
-- ------------- 儲存過程的遊標 --------------- -- ①宣告（建立）遊標 DECLARE 遊標名稱 CURSOR FOR select ...;
-- ②開啟遊標 OPEN 遊標名稱;
-- ③使用遊標 FETCH 遊標名稱 INTO 變數名稱;
-- ④關閉遊標
CLOSE 遊標名稱;
``
在上面列出了MySQL中儲存過程、儲存函式與觸發器的相關語法，接著再來聊聊管理的命令：
-SHOW PROCEDURE STATUS;：檢視當前資料庫中的所有儲存過程。
-SHOW PROCEDURE STATUS WHERE db = '庫名' AND NAME = '過程名';：檢視指定庫中的某個儲存過程。
-SHOW CREATE PROCEDURE 儲存過程名;：檢視某個儲存過程的原始碼。
-ALTER PROCEDURE 儲存過程名稱 ....：修改某個儲存過程的特性。
-DROP PROCEDURE 儲存過程名;：刪除某個儲存過程。
-SHOW FUNCTION STATUS;：檢視當前資料庫中的所有儲存函式。
-SHOW CREATE FUNCTION 儲存過程名;：檢視某個儲存函式的原始碼。
-ALTER FUNCTION 儲存過程名稱 ....：修改某個儲存函式的特性。
-DROP FUNCTION 儲存過程名;：刪除某個儲存函式。
-SHOW TRIGGERS;：檢視當前資料庫中定義的所有觸發器。
-SHOW CREATE TRIGGER 觸發器名稱;：檢視當前庫中指定名稱的觸發器。
-SELECT * FROM information_schema.TRIGGERS;：檢視MySQL所有已定義的觸發器。
-DROP TRIGGER IF EXISTS 觸發器名稱;`：刪除某個指定的觸發器。
當然，如若你對這塊感興趣，詳細的教程可參考上篇：《MySQL儲存過程與觸發器》。
八、MySQL使用者與許可權管理
create user 使用者名稱@'IP' identified by 密碼;：建立一個新使用者。drop user 使用者名稱@'IP';：刪除某個使用者。set password = password(新密碼);：為當前使用者設定新密碼。set password for 使用者名稱 = password(新密碼);：為指定使用者設定新密碼（需要許可權）。alter user 使用者名稱@'IP' identified by 新密碼;：使用root賬號修改密碼。mysqladmin -u使用者名稱 -p舊密碼 password 新密碼;：使用mysqladmin工具更改使用者密碼。rename user 原使用者名稱 to 新使用者名稱;：對某個使用者重新命名。show grants;：檢視當前使用者擁有的許可權。show grants for 使用者名稱;：檢視指定使用者擁有的許可權。grant 許可權1,許可權2... on 庫名.表名 to 使用者名稱;：為指定使用者授予許可權。- 許可權可選項：
insert：插入表資料的許可權。delete：刪除表資料的許可權。update：修改表資料的許可權。select：查詢表資料的許可權。alter：修改表結構的alter許可權。alter routine：修改子程式（儲存過程、函式、觸發器）的alter許可權。create：建立表的create許可權。create routine：建立儲存過程、儲存函式、觸發器的許可權。create temporary tables：建立臨時表的許可權。create user：建立/刪除/重新命名/授權使用者的許可權。create view：建立檢視的許可權。drop：刪除表的許可權。execute：執行儲存過程的許可權。file：匯出、匯入表資料的許可權。index：建立和刪除索引的許可權。lock tables：獲取表鎖的許可權。process：查詢工作執行緒的許可權。references：這個在MySQL中沒有。reload：請空表的許可權。replication clinet：獲取主節點、從節點地址的許可權。replication slave：複製主節點資料的許可權。show databases：檢視所有資料庫的許可權。show view：檢視所有檢視的許可權。shutdown：關閉資料庫服務的許可權。super：修改主節點資訊的許可權。all privileges：所有許可權。
usage：不授予這些許可權。其他許可權全部授予。grant option：授予這些許可權，其他許可權全部不授予。- 許可權範圍可選項：
*.*：全域性許可權，表示該使用者可對所有庫、所有表進行增刪改查操作。庫名.*：單庫許可權，表示該使用者可對指定庫下的所有表進行增刪改查操作。庫名.表名：單表許可權，表示該使用者可對指定表進行增刪改查操作。
- 許可權可選項：
revoke 許可權1,許可權2... on 庫名.表名 from 使用者名稱;：撤銷指定使用者的指定許可權。revoke all privileges from 使用者名稱 with grant option;：撤銷一個使用者的所有許可權。flush privileges;：重新整理許可權。select user,password,host from mysql.user;：查詢當前庫中的所有使用者資訊。MySQL8.0版本後推出的密碼管理機制：set persist default_password_lifetime=90;：設定所有使用者的密碼在90天后失效。create user 使用者@IP password expire interval 90 day;：建立使用者時設定失效時間。alter user 使用者名稱@IP password expire interval 90 day;：設定指定使用者密碼失效。alter user 使用者名稱@IP password expire never;：設定指定使用者的密碼永不失效。alter user 使用者名稱@IP password expire default;：使用預設的密碼失效策略。 上述給出了一系列的使用者管理和許可權管理的命令，最後稍微提一下建立使用者時的注意事項：sql -- 建立一個名為 zhuzi 的使用者 create user 'zhuzi'@'196.xxx.xxx.xxx' identified by "123456";在建立使用者時需要在使用者名稱稱後面跟一個IP地址，這個IP的作用是用來限制登入使用者的機器，如果指定為具體IP，則表示只能由該IP的機器登入該使用者，如果寫%表示任意裝置都能使用該使用者名稱登入連線。同時也最後提一嘴，
MySQL對於所有的使用者資訊，都會放在自帶的mysql庫的user表中儲存，因此也可以對錶執行insert、delete、update、select操作，來實現管理使用者的功能。
九、MySQL檢視與臨時表
create view 檢視名 as select ...;：對查詢出的結果集建立一個指定名稱的檢視。select * from 檢視名;：基於某個已經建立的檢視查詢資料。show create view 檢視名;：檢視某個已存在的檢視其詳細資訊。desc 檢視名;：檢視某個檢視的欄位結構。alter view 檢視名(欄位1,...) as select 欄位1...;：修改某個檢視的欄位為查詢欄位。drop view 檢視名;：刪除某個檢視。create temporary table 表名(....);：建立一張臨時表（方式1）。create temporary view 表名 as select ...;：建立一張臨時表（方式2）。truncate table 臨時表名;：清空某張臨時表的資料。
MySQL的臨時表本質上是一種特殊的檢視，被稱為不可更新的檢視，也就是臨時表只支援查詢資料，不支援增刪改操作，因此也可以通過建立檢視的方式建立臨時表，在建立語句中加入temporary關鍵字即可，不指定預設為undedined，意思是自動選擇檢視結構，一般為merge結構，表示建立一個支援增刪改查的檢視。
十、資料的匯出、匯入與備份、還原
資料庫的備份其實本質上就是指通過匯出資料的形式，或者拷貝表文件的方式來製作資料的副本，資料恢復/還原即是指在資料庫故障、異常、錯誤的情況下，通過匯入原本的資料副本，將資料恢復到正常狀態，下面來介紹MySQL中提供的相關命令。
```sql
-- --------使用 mysqldump 工具做資料的邏輯備份（匯出的是sql語句）-----------
-- 匯出MySQL中全部的庫資料（使用--all-databases 或者 -A 引數）
mysqldump -uroot -p密碼 --all-databases > 備份檔名.sql
-- 匯出MySQL中一部分的庫資料（使用--databases 或者 -B 引數） mysqldump -uroot -p密碼 --databases > 備份檔名.sql
-- 匯出MySQL單庫中的一部分表資料 mysqldump –u 使用者名稱 –h主機名 –p密碼 庫名[表名1,表名2...]> 備份檔名.sql
-- 匯出MySQL單表的部分資料（使用 --where 引數） mysqldump -u使用者名稱 -p 庫名 表名 --where="條件" > 備份檔名.sql
-- 排除某些表，匯出庫中其他的所有資料（使用 --ignore-table 引數） mysqldump -u使用者名稱 -p 庫名 --ignore-table=表名1,表名2... > 備份檔名.sql
-- 只匯出表的結構（使用 --no-data 或者 -d 選項） mysqldump -u使用者名稱 -p 庫名 --no-data > 備份檔名.sql
-- 只匯出表的資料（使用 --no-create-info 或者 -t 選項） mysqldump -u使用者名稱 -p 庫名 --no-create-info > 備份檔名.sql
-- 匯出包含儲存過程、函式的庫資料（使用--routines 或者 -R選項） mysqldump -u使用者名稱 -p -R --databases 庫名 > 備份檔名.sql
-- 匯出包含事件（觸發器）的庫資料（使用 --events 或者 -E選項） mysqldump -u使用者名稱 -p -E --databases 庫名 > 備份檔名.sql
-- --------使用 mysql 工具來恢復備份的資料（匯入xx.sql檔案執行）----------- -- 恢復庫級別的資料（包含了建庫語句的情況下使用） mysql -u使用者名稱 -p < xxx.sql
-- 恢復庫中表級別的資料 mysql -u使用者名稱 -p 庫名 < xxx.sql
-- ----------以物理形式備份資料（匯出的是表資料） ------------
-- 檢視資料庫匯出資料的路徑（如果沒有則需在my.ini/my.conf中配置）
show variables like '%secure_file_priv%';
-- 匯出一張表的資料為txt檔案（使用 select ... into outfile 語句） select * from 表名 into outfile "備份檔名.txt";
-- 匯出一張表的資料為txt檔案（使用 mysql 工具） mysql -u使用者名稱 -p --execute="select ...;" 庫名 > "資料存放目錄/xxx.txt"
-- 匯出一張表的結構和資料為sql、txt檔案（使用 mysqldump -T 的方式） mysqldump -u使用者名稱 -p -T "資料存放目錄" 庫名 檔名
-- 匯出一張表的資料為txt檔案，以豎排形式儲存（使用 mysql –veritcal 的方式） mysql -u使用者名稱 -p -veritcal --execute="select ...;" 庫名 > "資料存放目錄/xxx.txt"
-- 匯出一張表的資料為xml檔案（使用 mysql -xml 的方式） mysql -u使用者名稱 -p -xml --execute="select ...;" 庫名 > "資料存放目錄/xxx.xml"
-- -----------通過物理資料檔案恢復資料---------------- -- 使用load data infile 的方式匯入.txt 物理資料 load data infile "資料目錄/xxx.txt" into table 庫名.表名;
-- 使用 mysqlimport 工具匯入xxx.txt物理資料 mysqlimport -u使用者名稱 -p 庫名 '資料存放目錄/xxx.txt' --fields-terminatedby=',' --fields-optionally-enclosed-by='\"'
-- 使用 mysqldump 工具遷移資料
mysqldump –h 地址1 –u使用者名稱 –p密碼 –-all-databases | mysql –h地址2 –u使用者名稱 –p密碼
``
上述列出了一系列資料匯出匯入、備份恢復、遷移等命令，這些都是MySQL自身就支援的方式，但這些自帶的命令或工具，在一些情況下往往沒有那麼靈活、方便，因此在實際情況下，可以適當結合第三方工具來完成，比如：
- 較大的資料需要做物理備份時，可以通過xtrabackup備份工具來完成。
-MySQL5.5版本之前的MyISAM表，可以通過MySQLhotcopy工具做邏輯備份（速度最快）。
- 不同版本的MySQL可以使用XtraBackup備份工具來做資料遷移。
-MySQL、Oracle之間可以通過MySQL Migration Toolkit工具來做資料遷移。
-MySQL、SQL Server之間可以通過MyODBC`工具來做資料遷移。
當然，無論是
MySQL自身提供的工具也好，亦或是第三方提供的工具也罷，因為本身就寫死了邏輯，因此在有些場景下依舊存在侷限性，因此有時咱們也需要寫自動化指令碼，以此來完成一些特殊的需求。
十一、表分割槽相關的命令
``sql
-- 建立範圍分割槽
create table表名(xxx` xxx not null,
....
)
partition by range(xxx)(
partition 分割槽名1 values less than (範圍) data directory = "/xxx/xxx/xxx",
partition 分割槽名2 values less than (範圍) data directory = "/xxx/xxx/xxx",
......
);
-- 建立列舉分割槽
create table 表名(
xxx xxx not null,
....
)
partition by list(xxx)(
partition 分割槽名1 values in (列舉值1,列舉值2...),
partition 分割槽名2 values in (列舉值),
......
);
-- 建立常規雜湊分割槽
create table 表名(
xxx xxx not null,
....
)
partition by hash(xxx)
partitions 分割槽數量;
-- 建立線性雜湊分割槽
create table 表名(
xxx xxx not null,
....
)
partition by linear hash(xxx)
partitions 分割槽數量;
-- 建立Key鍵分割槽
create table 表名(
xxx xxx not null,
....
)
partition by key(xxx)
partitions 分割槽數量;
-- 建立Sub子分割槽
create table 表名(
xxx xxx not null,
....
)
partition by range(父分割槽鍵)
subpartition by hash(子分割槽鍵)(
partition 分割槽名1 values less than (範圍1)(
subpartition 子分割槽名1,
subpartition 子分割槽名2,
......
),
partition 分割槽名2 values less than (範圍2)(
subpartition 子分割槽名1,
subpartition 子分割槽名2,
......
),
......
);
-- 查詢一張表各個分割槽的資料量 select partition_name as "分割槽名稱",table_rows as "資料行數" from information_schema.partitions where table_name = '表名';
-- 查詢一張表父子分割槽的資料量 select partition_name as "父分割槽名稱", subpartition_name as "子分割槽名稱", table_rows as "子分割槽行數" from information_schema.partitions where table_name = '表名';
-- 查詢MySQL中所有表分割槽的資訊 select * from information_schema.partitions;
-- 查詢一張表某個分割槽中的所有資料 select * from 表名 partition (分割槽名);
-- 對於一張已存在的表新增分割槽 alter table 表名 reorganize partition 分割槽名 into ( partition 分割槽名1 values less than (範圍) data directory = "/xxx/xxx/xxx", partition 分割槽名2 values less than (範圍) data directory = "/xxx/xxx/xxx", ...... );
-- 將多個分割槽合併成一個分割槽 alter table 表明 reorganize partition 分割槽名1,分割槽名2... into ( partition 新分割槽名 values less than (範圍) );
-- 清空一個分割槽中的所有資料 alter table 表名 truncate partition 分割槽名;
-- 刪除一個表的指定分割槽 alter table 表名 drop partition 分割槽名;
-- 重建一張表的分割槽 alter table 表名 rebuild partition 分割槽名;
-- 分析一個表分割槽 alter table 表名 analyze partition 分割槽名; -- 優化一個表分割槽 alter table 表名 optimize partition 分割槽名; -- 檢查一個表分割槽 alter table 表名 check partition 分割槽名; -- 修復一個表分割槽 alter table 表名 repair partition 分割槽名;
-- 減少hash、key分割槽方式的 n 個分割槽 alter table 表名 coalesce partition n;
-- 將一張表的分割槽切換到另一張表 alter table 表名1 exchange partition 分割槽名 with table 表名2;
-- 移除一張表的所有分割槽 alter table 表名 remove partitioning; ```
十二、MySQL、InnoDB、MyISAM的引數
`引數，也被稱之為MySQL的系統變數，這些變數是影響MySQL執行的關鍵，對每個引數做出不同調整，都有可能直接影響到線上資料庫的效能，具體的完整系統引數可參考[《MySQL官網文件-系統變數》](http://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html)，官方提供的系統引數數量大致如下：
通過xpath的方式提取資料，大概能夠得知MySQL`系統變數大概一千個上下，因此這裡就不做過多的詳細介紹，簡單介紹一些較為常用的即可，其他具體的可參考前面給出的官網連結。
但是要注意，雖說
MySQL中有一千多個對外暴露的系統引數，但並不是所有的引數都可以讓使用者調整，MySQL的系統引數分為了三類：
一類是由MySQL自己維護的引數，這類引數使用者無法調整。
第二類是以配置檔案的形式載入的引數，這類引數必須在MySQL停機的情況下才能更改。
第三類是執行時的系統引數，這類是可以由使用者去做動態調整的。
咱們需要關心的重點就是第三類引數，那如何觀察這類引數呢？方式如下：
- show global variables;：檢視全域性所有使用者級別可以看到的系統變數。
- show session variables; | show variables;：檢視當前會話的所有系統變數。
- show variables like '%關鍵字%';：使用模糊查詢搜尋某個系統變數。
MySQL5.1與MySQL8.0版本的執行結果如下：
可以很明顯的從結果中得知：MySQL5.1版本中存在278個系統變數，MySQL8.0版本中存在557個系統變數，這僅僅只是社群版，而在商業版的MySQL中，其系統引數會更多，下面調出一些重點來聊一聊。
max_connections：MySQL的最大連線數，超出後新到來的連線會阻塞或被拒絕。version：當前資料庫的版本。ft_min_word_len：使用MyISAM引擎的表中，全文索引最小搜尋長度。ft_max_word_len：使用MyISAM引擎的表中，全文索引最大搜索長度。ft_query_expansion_limit：MyISAM中使用with query expansion搜尋的最大匹配數。innodb_ft_min_token_size：InnoDB引擎的表中，全文索引最小搜尋長度。innodb_ft_max_token_size：InnoDB引擎的表中，全文索引最大搜索長度。optimizer_switch：MySQL隱藏引數的開關。skip_scan：是否開啟索引跳躍掃描機制。innodb_page_size：InnoDB引擎資料頁的大小。tx_isolation：事務的隔離級別。autocommit：事務自動提交機制。innodb_autoinc_lock_mode：插入意向鎖的工作模式。innodb_lock_wait_timeout：InnoDB鎖衝突時，阻塞的超時時間。innodb_deadlock_detect：是否開啟InnoDB死鎖檢測機制。innodb_max_undo_log_size：本地磁碟檔案中，Undo-log的最大值，預設1GB。innodb_rollback_segments：指定回滾段的數量，預設為1個。innodb_undo_directory：指定Undo-log的存放目錄，預設放在.ibdata檔案中。innodb_undo_logs：指定回滾段的數量，預設為128個，也就是之前的innodb_rollback_segments。innodb_undo_tablespaces：指定Undo-log分成幾個檔案來儲存，必須開啟innodb_undo_directory引數。back_log：回滾日誌的最大回撤長度（一條資料的最長版本鏈長度）。innodb_undo_log_truncate：是否開啟Undo-log的壓縮功能，即日誌檔案超過一半時自動壓縮，預設關閉。innodb_flush_log_at_trx_commit：設定redo_log_buffer的刷盤策略，預設每次提交事務都刷盤。innodb_log_group_home_dir：指定redo-log日誌檔案的儲存路徑，預設為./。innodb_log_buffer_size：指定redo_log_buffer緩衝區的大小，預設為16MB。innodb_log_files_in_group：指定redo日誌的磁碟檔案個數，預設為2個。innodb_log_file_size：指定redo日誌的每個磁碟檔案的大小限制，預設為48MB。innodb_log_write_ahead_size：設定checkpoint刷盤機制每次落盤動作的大小。innodb_log_compressed_pages：是否對Redo日誌開啟頁壓縮機制，預設ON。innodb_log_checksums：Redo日誌完整性效驗機制，預設開啟。log_bin：是否開啟bin-log日誌，預設ON開啟，表示會記錄變更DB的操作。log_bin_basename：設定bin-log日誌的儲存目錄和檔名字首，預設為./bin.0000x。log_bin_index：設定bin-log索引檔案的儲存位置，因為本地有多個日誌檔案，需要用索引來確定目前該操作的日誌檔案。binlog_format：指定bin-log日誌記錄的儲存方式，可選Statment、Row、Mixed。max_binlog_size：設定bin-log本地單個檔案的最大限制，最多隻能調整到1GB。binlog_cache_size：設定為每條執行緒的工作記憶體，分配多大的bin-log緩衝區。sync_binlog：控制bin-log日誌的刷盤頻率。binlog_do_db：設定後，只會收集指定庫的bin-log日誌，預設所有庫都會記錄。log-error：error-log錯誤日誌的儲存路徑和名字。slow_query_log：設定是否開啟慢查詢日誌，預設OFF關閉。slow_query_log_file：指定慢查詢日誌的儲存目錄及檔名。general_log：是否開啟查詢日誌，預設OFF關閉。general_log_file：指定查詢日誌的儲存路徑和檔名。innodb_buffer_pool_size：InnoDB緩衝區的大小。innodb_adaptive_hash_index：是否開啟InnoDB的自適應雜湊索引機制。innodb_compression_level：調整壓縮的級別，可控範圍在1~9，越高壓縮效果越好，但壓縮速度也越慢。innodb_compression_failure_threshold_pct：當壓縮失敗的資料頁超出該比例時，會加入資料填充來減小失敗率，為0表示禁止填充。innodb_compression_pad_pct_max：一個數據頁中最大允許填充多少比例的空白資料。innodb_log_compressed_pages：控制是否對redo-log日誌的資料也開啟壓縮機制。innodb_cmp_per_index_enabled：是否對索引檔案開啟壓縮機制。character_set_client：客戶端的字元編碼格式。character_set_connection：資料庫連線的字元編碼格式。character_set_database：資料庫的字元編碼格式。character_set_results：返回的結果集的編碼格式。character_set_server：MySQL-Server的字元編碼格式。character_set_system：系統的字元編碼格式。collation_database：資料庫的字元排序規則。......：剩下的就不再列出來了，大家可根據查詢出的變數名，去官網文件查詢釋義即可。
十三、MySQL常見的錯誤碼
程式Bug、錯誤、異常.....，這些詞彙天生與每位程式設計師掛鉤，當一個程式出現問題時，使用者第一時間想到的是這個平臺的程式設計師不行，而身為開發者逃不開一點是：又得背鍋和加班解決問題！
而MySQL作為整個系統的後方大本營，自然也無法避免會出現錯誤與異常，在MySQL內部其實定義了一系列錯誤碼，當執行過程中發生錯誤，或執行語句時出現異常時，一般情況下都會向客戶端返回錯誤碼以及錯誤資訊。
但往往很多時候有些錯誤咱們沒見過，所以面對問題時難免有些束手無策，也正因為如此，本節將羅列
MySQL中常見的錯誤碼，當你碰到問題時可以直接通過錯誤碼在此搜尋，以此來定位問題出現的原因，以此進一步推匯出問題的解決方案。
MySQL的錯誤資訊由ErrorCode、SQLState、ErrorInfo三部分組成，即錯誤碼、SQL狀態、錯誤資訊三部分組成，如下：
ERROR 1045 (28000): Access denied for user 'zhuzi'@'localhost' (using password: YES)
其中1045屬於錯誤狀態碼，28000屬於SQL狀態，後面跟著的則是具體的錯誤資訊，不過MySQL內部大致定義了兩三千個錯誤碼，其錯誤碼的定義位於include/mysqld_error.h、include/mysqld_ername.h檔案中，而SQLState的定義則位於include/sql_state.h檔案中，所有的錯誤資訊列表則位於share/errmsg.txt檔案中，因此大家感興趣的可自行編譯MySQL原始碼檢視。
OK~，由於本章篇幅過長，就不再羅列細緻的錯誤碼詳情了，畢竟當出現錯誤時，百度、谷歌才是最好的選擇，畢竟從上面不僅僅能找到錯誤碼的含義，同時還能找到錯誤碼的解決方案，但為了文章的完整性，再貼一位大佬的→《錯誤碼大全》←的整理：
通篇下來共計16.07W字，裡面對於MySQL中90%+的錯誤碼都羅列出來了，但我大致看了一些，有些錯誤碼的描述並不恰當，應該是直接根據share/errmsg.txt檔案中的錯誤資訊直接翻譯過來的，大家有興趣可以看看~
十四、MySQL命令大全總結
到這裡為止，對於MySQL中大部分常用的命令基本上都已經羅列出來啦！以後如若忘記某個函式名稱、某條語句的語法等等，都可以直接在本章中快捷搜尋，但本篇僅僅只寫出了基本的語法，實際資料庫開發中往往還需要結合業務，來編寫更為複雜的SQL語句~。本章的主要目的是當成《MySQL命令手冊》，本質上沒有太多可以學習的知識點，但它卻非常實用，能夠協助咱們的日常開發。
- 全解MySQL終章：這份爆肝30W字的資料庫寶典贈與有緣的你！
- 追憶四年前：一段關於我被外企CTO用登入註冊吊打的不堪往事
- (十一)Netty實戰篇：基於Netty框架打造一款高效能的IM即時通訊程式
- (四)MySQL之索引初識篇：索引機制、索引分類、索引使用與管理綜述
- (九)MySQL之MVCC機制：為什麼你改了的資料我還看不見？
- (十)全解MySQL之死鎖問題分析、事務隔離與鎖機制的底層原理剖析
- (二十八)MySQL面試通關祕籍：這次你也可以在簡歷寫上精通MySQL！
- (一)全解MySQL之架構篇：自頂向下深入剖析MySQL整體架構！
- (九)Java網路程式設計無冕之王-這回把大名鼎鼎的Netty框架一網打盡！
- (八)MySQL鎖機制：高併發場景下該如何保證資料讀寫的安全性？
- (十五)MySQL命令大全：以後再也不用擔心忘記SQL該怎麼寫啦~
- (七)MySQL事務篇：ACID原則、事務隔離級別及事務機制原理剖析
- 深入理解SpringMVC工作原理，像大牛一樣手寫SpringMVC框架
- (三)MySQL之庫表設計篇：一、二、三、四、五正規化、BC正規化與反正規化詳解！
- (五)MySQL索引應用篇：建立索引的正確姿勢與使用索引的最佳指南！
- (六)MySQL索引原理篇：深入資料庫底層揭開索引機制的神祕面紗！
- (五)網路程式設計之流量接入層設計：基於效能怪獸從零構建日均億級吞吐量的閘道器架構！
- (四)網路程式設計之請求分發篇：負載均衡靜態排程演算法、平滑輪詢加權、一致性雜湊、最小活躍數演算法實踐！
- (三)Nginx一網打盡：動靜分離、壓縮、快取、黑白名單、跨域、高可用、效能優化...想要的這都有！
- Redis綜述篇：與面試官徹夜長談Redis快取、持久化、淘汰機制、哨兵、叢集底層原理！