之前兩篇文章帶你了解了 MySQL 的基礎(chǔ)語(yǔ)法和 MySQL 的進(jìn)階內(nèi)容，那么這篇文章我們來了解一下 MySQL 中的高級(jí)內(nèi)容。
其他文章：
本文思維導(dǎo)圖如下。
事務(wù)控制和鎖定語(yǔ)句
我們知道，MyISAM 和 MEMORY 存儲(chǔ)引擎支持表級(jí)鎖定(table-level locking)，InnoDB 存儲(chǔ)引擎支持行級(jí)鎖定(row-level locking)，BDB 存儲(chǔ)引擎支持頁(yè)級(jí)鎖定(page-level locking)。各個(gè)鎖定級(jí)別的特點(diǎn)如下
頁(yè)級(jí)鎖：銷和加鎖時(shí)間界于表鎖和行鎖之間；會(huì)出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般
表級(jí)鎖：表級(jí)鎖是對(duì)整張表進(jìn)行加鎖，MyISAM 和 MEMORY 主要支持表級(jí)鎖，表級(jí)鎖加鎖快，不會(huì)出現(xiàn)死鎖，鎖的粒度比較粗，并發(fā)度最低
行級(jí)鎖：行級(jí)鎖可以說是 MySQL 中粒度最細(xì)的一種鎖了，InnoDB 支持行級(jí)鎖，行級(jí)鎖容易發(fā)生死鎖，并發(fā)度比較好，同時(shí)鎖的開銷也比較大。
MySQL 默認(rèn)情況下支持表級(jí)鎖定和行級(jí)鎖定。但是在某些情況下需要手動(dòng)控制事務(wù)以確保整個(gè)事務(wù)的完整性，下面我們就來探討一下事務(wù)控制。但是在探討事務(wù)控制之前我們先來認(rèn)識(shí)一下兩個(gè)鎖定語(yǔ)句
鎖定語(yǔ)句
MySQL 的鎖定語(yǔ)句主要有兩個(gè)Lock和unLock，Lock Tables 可用于鎖定當(dāng)前線程的表，就跟 Java 語(yǔ)法中的 Lock 鎖的用法是一樣的，如果表鎖定，意味著其他線程不能再操作表，直到鎖定被釋放為止。如下圖所示
lock table cxuan005 read;復(fù)制代碼
我們鎖定了 cxuan005 的 read 鎖，然后這時(shí)我們?cè)龠M(jìn)行一次查詢，看看是否能夠執(zhí)行這條語(yǔ)句
select * from cxuan005 where id = 111;復(fù)制代碼
可以看到，在進(jìn)行 read 鎖定了，我們?nèi)耘f能夠執(zhí)行查詢語(yǔ)句。
現(xiàn)在我們另外起一個(gè)窗口，相當(dāng)于另起了一個(gè)線程來進(jìn)行查詢操作。
select * from cxuan005;復(fù)制代碼
這是第二個(gè)窗口執(zhí)行查詢的結(jié)果，可以看到，在一個(gè)線程執(zhí)行 read 鎖定后，其他線程仍然可以進(jìn)行表的查詢操作。
那么第二個(gè)線程能否執(zhí)行更新操作呢？我們來看一下
update cxuan005 set info='cxuan' where id = 111;復(fù)制代碼
發(fā)生了什么？怎么沒有提示結(jié)果呢？其實(shí)這個(gè)情況下表示 cxuan005 已經(jīng)被加上了 read 鎖，由于當(dāng)前線程不是持有鎖的線程，所以當(dāng)前線程無法執(zhí)行更新。
解鎖語(yǔ)句
現(xiàn)在我們把窗口切換成持有 read 鎖的線程，來進(jìn)行 read 鎖的解鎖
unlock tables;復(fù)制代碼
>need-to-insert-img
在解鎖完成前，進(jìn)行更新的線程會(huì)一直等待，直到解鎖完成后，才會(huì)進(jìn)行更新。我們可以看一下更新線程的結(jié)果。
>need-to-insert-img
可以看到，線程已經(jīng)更新完畢，我們看一下更新的結(jié)果
select * from cxuan005 where id = 111;復(fù)制代碼
>need-to-insert-img
如上圖所示，id = 111 的值已經(jīng)被更新成了 cxuan。
事務(wù)控制
事務(wù)(Transaction)是訪問和更新數(shù)據(jù)庫(kù)的基本執(zhí)行單元，一個(gè)事務(wù)中可能會(huì)包含多個(gè) SQL 語(yǔ)句，事務(wù)中的這些 SQL 語(yǔ)句要么都執(zhí)行，要么都不執(zhí)行，而 MySQL 它是一個(gè)關(guān)系型數(shù)據(jù)庫(kù)，它自然也是支持事務(wù)的。事務(wù)同時(shí)也是區(qū)分關(guān)系型數(shù)據(jù)庫(kù)和非關(guān)系型數(shù)據(jù)庫(kù)的一個(gè)重要的方面。
在 MySQL 事務(wù)中，主要涉及的語(yǔ)法包含SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK等。
自動(dòng)提交
在 MySQL 中，事務(wù)默認(rèn)是自動(dòng)提交(Autocommit)的，如下所示
show variables like 'autocommit';復(fù)制代碼
>need-to-insert-img
在自動(dòng)提交的模式下，每個(gè) SQL 語(yǔ)句都會(huì)當(dāng)作一個(gè)事務(wù)執(zhí)行提交操作，例如我們上面使用的更新語(yǔ)句
update cxuan005 set info='cxuan' where id = 111;復(fù)制代碼
如果想要關(guān)閉數(shù)據(jù)庫(kù)的自動(dòng)提交應(yīng)該怎么做呢？
其實(shí)，MySQL 是可以關(guān)閉自動(dòng)提交的，你可以執(zhí)行
set autocommit = 0;復(fù)制代碼
>need-to-insert-img
然后我們?cè)倏匆幌伦詣?dòng)提交是否關(guān)閉了，再次執(zhí)行一下 show variables like 'autocommit' 語(yǔ)句
>need-to-insert-img
可以看到，自動(dòng)提交已經(jīng)關(guān)閉了，再次執(zhí)行
set autocommit = 1;復(fù)制代碼
會(huì)再次開啟自動(dòng)提交。
這里注意一下特殊操作。
在 MySQL 中，存在一些特殊的命令，如果在事務(wù)中執(zhí)行了這些命令，會(huì)馬上強(qiáng)制執(zhí)行 commit 提交事務(wù)；比如 DDL 語(yǔ)句(create table/drop table/alter/table)、lock tables 語(yǔ)句等等。
不過，常用的 select、insert、update 和 delete命令，都不會(huì)強(qiáng)制提交事務(wù)。
手動(dòng)提交
如果需要手動(dòng) commit 和 rollback 的話，就需要明確的事務(wù)控制語(yǔ)句了。
典型的 MySQL 事務(wù)操作如下
start transaction;... # 一條或者多條語(yǔ)句commit;復(fù)制代碼
上面代碼中的 start transaction 就是事務(wù)的開始語(yǔ)句，編寫 SQL 后會(huì)調(diào)用 commit 提交事務(wù)，然后將事務(wù)統(tǒng)一執(zhí)行，如果 SQL 語(yǔ)句出現(xiàn)錯(cuò)誤會(huì)自動(dòng)調(diào)用 Rollback 進(jìn)行回滾。
下面我們就通過示例來演示一下 MySQL 的事務(wù)，同樣的，我們需要啟動(dòng)兩個(gè)窗口來演示，為了便于區(qū)分，我們使用 mysql01 和 mysql02 來命名。
>need-to-insert-img
我們用start transaction命令啟動(dòng)一個(gè)事務(wù)，然后再 cxuan005 表中插入一條數(shù)據(jù)，此時(shí) mysql02 不做任何操作。涉及的 SQL 語(yǔ)句如下。
start transaction;復(fù)制代碼
>need-to-insert-img
然后執(zhí)行
select * from cxuan005;復(fù)制代碼
查詢一下 cxuan005 中的數(shù)據(jù)
>need-to-insert-img
嗯。。。很多長(zhǎng)度太長(zhǎng)了，現(xiàn)在我們把所有的 info 數(shù)據(jù)都更新為 cxuan 。
update cxuan005 set info='cxuan';復(fù)制代碼
>need-to-insert-img
更新完畢后，我們先不提交事務(wù)，分別在 mysql01 和 mysql02 中進(jìn)行查詢，發(fā)現(xiàn)只有 mysql01 窗口中的查詢已經(jīng)生效，而 mysql02 中還是更新前的數(shù)據(jù)
>need-to-insert-img
現(xiàn)在我們?cè)?mysql01 中 commit 當(dāng)前事務(wù)，然后在 mysql02 中查詢，發(fā)現(xiàn)數(shù)據(jù)已經(jīng)被修改了。
除了 commit 之外，MySQL 中還有commit and chain命令，這個(gè)命令會(huì)提交當(dāng)前事務(wù)并且重新開啟一個(gè)新的事務(wù)。如下代碼所示
start transaction; # 開啟一個(gè)新的事務(wù)insert into cxuan005(id,info) values (555,'cxuan005'); # 插入一條數(shù)據(jù)commit and chain; # 提交當(dāng)前事務(wù)并重新開啟一個(gè)事務(wù)復(fù)制代碼
上面是一個(gè)事務(wù)操作，在 commit and chain 鍵入后，我們可以再次執(zhí)行 SQL 語(yǔ)句
update cxuan005 set info = 'cxuan' where id = 555;commit;復(fù)制代碼
然后再次查詢
select * from cxuan005;復(fù)制代碼
>need-to-insert-img
執(zhí)行后，可以發(fā)現(xiàn)，我們僅僅使用了一個(gè) start transaction 命令就執(zhí)行了兩次事務(wù)操作。
如果在手動(dòng)提交的事務(wù)中，你發(fā)現(xiàn)有一條 SQL 語(yǔ)句寫的不正確或者有其他原因需要回滾，那么此時(shí)你就會(huì)用到rollback語(yǔ)句，它會(huì)回滾當(dāng)前事務(wù)，相當(dāng)于什么也沒發(fā)生。如下代碼所示。
start transaction;delete from cxuan005 where id = 555;rollback;復(fù)制代碼
這里切忌一點(diǎn)：delete 刪除語(yǔ)句一定要加 where ，不加 where 語(yǔ)句的刪除就是耍流氓。
在同一個(gè)事務(wù)操作中，最好使用相同存儲(chǔ)引擎的表，如果使用不同存儲(chǔ)引擎的表后，rollback 語(yǔ)句會(huì)對(duì)非事務(wù)類型的表進(jìn)行特別處理，因此 commit 、rollback 只能對(duì)事務(wù)類型的表進(jìn)行提交和回滾。
我們提交的事務(wù)一般都會(huì)被記錄到二進(jìn)制的日志中，但是如果一個(gè)事務(wù)中包含非事務(wù)類型的表，那么回滾操作也會(huì)被記錄到二進(jìn)制日志中，以確保非事務(wù)類型的表可以被復(fù)制到從數(shù)據(jù)庫(kù)中。
這里解釋一下什么是事務(wù)表和非事務(wù)表
事務(wù)表和非事務(wù)表
事務(wù)表故名思義就是支持事務(wù)的表，支不支持事務(wù)和 MySQL 的存儲(chǔ)類型有關(guān)，一般情況下，InnoDB存儲(chǔ)引擎的表是支持事務(wù)的，關(guān)于 InnoDB 的知識(shí)，我們會(huì)在后面詳細(xì)介紹。
非事務(wù)表相應(yīng)的就是不支持事務(wù)的表，在 MySQL 中，存儲(chǔ)引擎MyISAM是不支持事務(wù)的，非事務(wù)表的特點(diǎn)是不支持回滾。
對(duì)于回滾的話，還要講一點(diǎn)就是SAVEPOINT，它能指定事務(wù)回滾的一部分，但是不能指定事務(wù)提交的一部分。 SAVEPOINT 可以指定多個(gè)，在滿足不同條件的同時(shí)，回滾不同的 SAVEPOINT。需要注意的是，如果定義了兩個(gè)相同名稱的 SAVEPOINT，則后面定義的 SAVEPOINT 會(huì)覆蓋之前的定義。如果 SAVEPOINT 不再需要的話，可以通過RELEASE SAVEPOINT來進(jìn)行刪除。刪除后的 SAVEPOINT 不能再執(zhí)行 ROLLBACK TO SAVEPOINT 命令。
我們通過一個(gè)示例來進(jìn)行模擬不同的 SAVEPOINT
首先先啟動(dòng)一個(gè)事務(wù) ，向 cxuan005 中插入一條數(shù)據(jù)，然后進(jìn)行查詢，那么是可以查詢到這條記錄的
start transaction;insert into cxuan005(id,info) values(666,'cxuan666');select * from cxuan005 where id = 666;復(fù)制代碼
查詢之后的記錄如下
>need-to-insert-img
然后我們定義一個(gè) SAVEPOINT，如下所示
savepoint test;復(fù)制代碼
然后繼續(xù)插入一條記錄
insert into cxuan005(id,info) values(777,'cxuan777');復(fù)制代碼
此時(shí)就可以查詢到兩條新增記錄了，id 是 666 和 777 的記錄。
select * from cxuan005 where id = 777;復(fù)制代碼
>need-to-insert-img
那么我們可以回滾到剛剛定義的 SAVEPOINT
rollback to savepoint test;復(fù)制代碼
再次查詢 cxuan005 這個(gè)表，可以看到，只有 id=666 的這條記錄插入進(jìn)來了，說明 id=777 這條記錄已經(jīng)被回滾了。
>need-to-insert-img
此時(shí)我們看到的都是 mysql01 中事務(wù)還沒有提交前的狀態(tài)，所以這時(shí)候 mysql02 中執(zhí)行查詢操作是看不到 666 這條記錄的。
然后我們?cè)?mysql01 中執(zhí)行 commit 操作，那么此時(shí)在 mysql02 中就可以查詢到這條記錄了。
SQL 安全問題
SQL 安全問題應(yīng)該是我們程序員比較忽視的一個(gè)地方了。日常開發(fā)中，我們一般只會(huì)關(guān)心 SQL 能不能解決我們的業(yè)務(wù)問題，能不能把數(shù)據(jù)查出來，而對(duì)于 SQL 問題，我們一般都認(rèn)為這是 DBA 的活，其實(shí)我們 CRUD 程序員也應(yīng)該了解一下 SQL 的安全問題。
SQL 注入簡(jiǎn)介
SQL 注入就是利用某些數(shù)據(jù)庫(kù)的外部接口將用戶數(shù)據(jù)插入到實(shí)際的 SQL 中，從而達(dá)到入侵?jǐn)?shù)據(jù)庫(kù)的目的。SQL 注入是一種常見的網(wǎng)絡(luò)攻擊的方式，它不是利用操作系統(tǒng)的 BUG 來實(shí)現(xiàn)攻擊的。SQL 主要是針對(duì)程序員編寫時(shí)的疏忽來入侵的。
SQL 注入攻擊有很大的危害，攻擊者可以利用它讀取、修改或者刪除數(shù)據(jù)庫(kù)內(nèi)的數(shù)據(jù)，獲取數(shù)據(jù)庫(kù)中的用戶名和密碼，甚至獲得數(shù)據(jù)庫(kù)管理員的權(quán)限。并且 SQL 注入一般比較難以防范。
SQL Mode
MySQL 可以運(yùn)行在不同的 SQL Mode 模式下，不同的 SQL Mode 定義了不同的 SQL 語(yǔ)法，數(shù)據(jù)校驗(yàn)規(guī)則，這樣就能夠在不同的環(huán)境中使用 MySQL ，下面我們就來介紹一下 SQL Mode。
SQL Mode 解決問題
SQL Mode 可以解決下面這幾種問題
通過設(shè)置 SQL Mode，可以完成不同嚴(yán)格程度的數(shù)據(jù)校驗(yàn)，有效保障數(shù)據(jù)的準(zhǔn)確性。
設(shè)置 SQL Mode 為ANSI模式，來保證大多數(shù) SQL 符合標(biāo)準(zhǔn)的 SQL 語(yǔ)法，這樣應(yīng)用在不同數(shù)據(jù)庫(kù)的遷移中，不需要對(duì) SQL 進(jìn)行較大的改變
數(shù)據(jù)在不同數(shù)據(jù)庫(kù)的遷移中，通過改變 SQL Mode 能夠更方便的進(jìn)行遷移。
下面我們就通過示例來演示一下 SQL Mode 用法
我們可以通過
select @@sql_mode;復(fù)制代碼
來查看默認(rèn)的 SQL Mode，如下是我的數(shù)據(jù)庫(kù)所支持的 SQL Mode
>need-to-insert-img
涉及到很多 SQL Mode，下面是這些 SQL Mode 的解釋
ONLY_FULL_GROUP_BY：這個(gè)模式會(huì)對(duì) GROUP BY 進(jìn)行合法性檢查，對(duì)于 GROUP BY 操作，如果在SELECT 中的列，沒有在 GROUP BY 中出現(xiàn)，那么將認(rèn)為這個(gè) SQL 是不合法的，因?yàn)榱胁辉?GROUP BY 從句中
同樣舉個(gè)例子，我們現(xiàn)在查詢一下 cxuan005 的 id 和 info 字段。
select id,info from cxuan005;復(fù)制代碼
這樣是可以運(yùn)行的
>need-to-insert-img
然后我們使用 GROUP BY 字句進(jìn)行分組，這里只對(duì) info 進(jìn)行分組，我們看一下會(huì)出現(xiàn)什么情況
select id,info from cxuan005 group by info;復(fù)制代碼
>need-to-insert-img
我們可以從錯(cuò)誤原因中看到，這條 SQL 語(yǔ)句是不符合 ONLY_FULL_GROUP_BY 的這條 SQL Mode 的。因?yàn)槲覀冎粚?duì) info 進(jìn)行分組了，沒有對(duì) id 進(jìn)行分組，我們把 SQL 語(yǔ)句改成如下形式
select id,info from cxuan005 group by id,info;復(fù)制代碼
>need-to-insert-img
這樣 SQL 就能正確執(zhí)行了。
當(dāng)然，我們也可以刪除 sql_mode = ONLY_FULL_GROUP_BY 的這條 Mode，可以使用
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));復(fù)制代碼
來進(jìn)行刪除，刪除后我們使用分組語(yǔ)句就可以放飛自我了。
select id,info from cxuan005 group by info;復(fù)制代碼
>need-to-insert-img
但是這種做法只是暫時(shí)的修改，我們可以修改配置文件 my.ini 中的 sql_mode= STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
STRICT_TRANS_TABLES：這就是嚴(yán)格模式，在這個(gè)模式下會(huì)對(duì)數(shù)據(jù)進(jìn)行嚴(yán)格的校驗(yàn)，錯(cuò)誤數(shù)據(jù)不能插入，報(bào)error 錯(cuò)誤。如果不能將給定的值插入到事務(wù)表中，則放棄該語(yǔ)句。對(duì)于非事務(wù)表，如果值出現(xiàn)在單行語(yǔ)句或多行語(yǔ)句的第1行，則放棄該語(yǔ)句。
當(dāng)使用 innodb 存儲(chǔ)引擎表時(shí)，考慮使用 innodb_strict_mode 模式的 sql_mode，它能增量額外的錯(cuò)誤檢測(cè)功能。
NO_ZERO_IN_DATE：這個(gè)模式影響著日期中的月份和天數(shù)是否可以為 0（注意年份是非 0 的），這個(gè)模式也取決于嚴(yán)格模式是否被啟用。如果這個(gè)模式未啟用，那么日期中的零部分被允許并且插入沒有警告。如果這個(gè)模式啟用，那么日期中的零部分插入被作為0000-00-00并且產(chǎn)生一個(gè)警告。
這個(gè)模式需要注意下，如果啟用的話，需要STRICT_TRANS_TABLES和NO_ZERO_IN_DATE同時(shí)啟用，否則不起作用，也就是
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE';復(fù)制代碼
然后我們換表了，使用 cxuan003 這張表，表結(jié)構(gòu)如下
>need-to-insert-img
我們主要測(cè)試日期的使用，在 cxuan003 中插入一條日期為0000-00-00的數(shù)據(jù)
insert into cxuan003 values(111,'study','0000-00-00');復(fù)制代碼
發(fā)現(xiàn)能夠執(zhí)行成功，但是把年月日各自變?yōu)?0 之后再進(jìn)行插入，則會(huì)插入失敗。
insert into cxuan003 values(111,'study','2021-00-00');復(fù)制代碼
>need-to-insert-img
insert into cxuan003 values(111,'study','2021-01-00');復(fù)制代碼
>need-to-insert-img
這些組合有很多，我這里就不再細(xì)致演示了，讀者可以自行測(cè)試。
如果要插入0000-00-00這樣的數(shù)據(jù)，必須設(shè)置NO_ZERO_IN_DATE和NO_ZERO_DATE。
ERROR_FOR_DIVISION_BY_ZERO：如果這個(gè)模式未啟用，那么零除操作將會(huì)插入空值并且不會(huì)產(chǎn)生警告；如果這個(gè)模式啟用，零除操作插入空值并產(chǎn)生警告；如果這個(gè)模式和嚴(yán)格模式都啟用，零除從操作將會(huì)產(chǎn)生一個(gè)錯(cuò)誤。
NO_AUTO_CREATE_USER：禁止使用 grant 語(yǔ)句自動(dòng)創(chuàng)建用戶，除非認(rèn)證信息被指定。
NO_ENGINE_SUBSTITUTION：此模式指定當(dāng)執(zhí)行 create 語(yǔ)句或者 alter 語(yǔ)句指定的存儲(chǔ)引擎沒有啟用或者沒有編譯時(shí)，控制默認(rèn)存儲(chǔ)引擎的自動(dòng)切換。默認(rèn)是啟用狀態(tài)的。
SQL Mode 三種作用域
SQL Mode 按作用區(qū)域和時(shí)間可分為 3。個(gè)級(jí)別，分別是會(huì)話級(jí)別，全局級(jí)別，配置（永久生效）級(jí)別。
我們上面使用的 SQL Mode 都是會(huì)話級(jí)別，會(huì)話級(jí)別就是當(dāng)前窗口域有效。它的設(shè)置方式是
set @@session.sql_mode='xx_mode'set session sql_mode='xx_mode'復(fù)制代碼
全局域就是當(dāng)前會(huì)話關(guān)閉不失效，但是在 MySQL 重啟后失效。它的設(shè)置方式是
set global sql_mode='xx_mode';set @@global.sql_mode='xx_mode';復(fù)制代碼
配置域就是在vi /etc/my.cnf里面添加
[mysqld]sql-mode = "xx_mode"復(fù)制代碼
配置域在保存退出后，重啟服務(wù)器，即可永久生效。
SQL 正則表達(dá)式
正則表達(dá)式相信大家應(yīng)該都用過，不過你在 MySQL 中用過正則表達(dá)式嗎？下面我們就來聊一聊 SQL 中的正則表達(dá)式。
正則表達(dá)式(Regular Expression)是指一個(gè)用來描述或者匹配字符串的句法規(guī)則。正則表達(dá)式通常用來檢索和替換某個(gè)文本中的文本內(nèi)容。很多語(yǔ)言都支持正則表達(dá)式，MySQL 同樣也不例外，MySQL 利用REGEXP命令提供給用戶擴(kuò)展的正則表達(dá)式功能。下面是 MySQL 中正則表達(dá)式的一些規(guī)則。
>need-to-insert-img
下面來演示一下正則表達(dá)式的用法
^在字符串的開始進(jìn)行匹配，根據(jù)返回的結(jié)果來判斷是否匹配，1 = 匹配，0 = 不匹配。下面嘗試匹配字符串a(chǎn)aaabbbccc是否以字符串a(chǎn)為開始
select 'aaaabbbccc' regexp '^a';復(fù)制代碼
>need-to-insert-img
同樣的，$會(huì)在末尾處進(jìn)行匹配，如下所示
select 'aaaabbbccc' regexp 'c$';復(fù)制代碼
>need-to-insert-img
.匹配單個(gè)任意字符
select 'berska' regexp '.s', 'zara' regexp '.a';復(fù)制代碼
>need-to-insert-img
[...]表示匹配括號(hào)內(nèi)的任意字符，示例如下
select 'whosyourdaddy' regexp '[abc]';復(fù)制代碼
>need-to-insert-img
[^...]匹配括號(hào)內(nèi)不包含的任意字符，和[...]是相反的，如果有任何匹配不上，返回 0 ，全部匹配上返回 1。
select 'x' regexp '[^xyz]';復(fù)制代碼
>need-to-insert-img
n*表示匹配零個(gè)或者多個(gè) n 字符串，如下
select 'aabbcc' regexp 'd*';復(fù)制代碼
>need-to-insert-img
沒有 d 出現(xiàn)也可以返回 1 ，因?yàn)?* 表示 0 或者多個(gè)。
n+表示匹配 1 個(gè)或者 n 個(gè)字符串
select 'aabbcc' regexp 'd+';復(fù)制代碼
>need-to-insert-img
n?的用法和 n+ 類似，只不過 n? 可以匹配空串
常見 SQL 技巧
RAND() 函數(shù)
大多數(shù)數(shù)據(jù)庫(kù)都會(huì)提供產(chǎn)生隨機(jī)數(shù)的函數(shù)，通過這些函數(shù)可以產(chǎn)生隨機(jī)數(shù)，也可以使用從數(shù)據(jù)庫(kù)表中抽取隨機(jī)產(chǎn)生的記錄，這對(duì)統(tǒng)計(jì)分析來說很有用。
在 MySQL 中，通常使用RAND()函數(shù)來產(chǎn)生隨機(jī)數(shù)。RAND() 和 ORDER BY 組合完成數(shù)據(jù)抽取功能，如下所示。
我們新建一張表用于數(shù)據(jù)檢索。
CREATE TABLE `clerk_Info` (? `id` int(11) NOT NULL,? `name` varchar(255) DEFAULT NULL,? `salary` decimal(10,2) DEFAULT NULL,? `companyId` int(10) DEFAULT NULL,? PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1復(fù)制代碼
然后插入一些數(shù)據(jù)，插入完成后的數(shù)據(jù)如下。
>need-to-insert-img
然后我們可以使用 RAND() 函數(shù)進(jìn)行隨機(jī)檢索數(shù)據(jù)行
select * from clerk_info order by rand();復(fù)制代碼
檢索完成后的數(shù)據(jù)如下
>need-to-insert-img
多次查詢后發(fā)現(xiàn)每次檢索的數(shù)據(jù)順序都是隨機(jī)的。
這個(gè)函數(shù)多用于隨機(jī)抽樣，比如選取一定數(shù)量的樣本在進(jìn)行隨機(jī)排序，需要用到limit關(guān)鍵字。
GROUP BY + WITH ROLLUP
我們經(jīng)常使用 GROUP BY 語(yǔ)句，但是你用過GROUP BY和WITH ROLLUP一起使用的嗎？使用 GROUP BY 和 WITH ROLLUP 字句可以檢索出更多的分組集合信息。
我們?nèi)耘f對(duì) clerk_info 表進(jìn)行操作，我們對(duì) name 和 salary 進(jìn)行分組統(tǒng)計(jì)工資總數(shù)。
select name,sum(salary) from clerk_info group by name with rollup;復(fù)制代碼
>need-to-insert-img
可以看到上面的表按照 name 進(jìn)行分組，然后再對(duì) money 進(jìn)行統(tǒng)計(jì)。
也就是說 GROUP BY 語(yǔ)句執(zhí)行完成后可以滿足用戶想要的任何一個(gè)分組以及分組組合的聚合信息值。
這里需要注意一點(diǎn)，不能同時(shí)使用 ORDER BY 字句對(duì)結(jié)果進(jìn)行排序，ROLLUP 和 ORDER BY 是互斥的。
數(shù)據(jù)庫(kù)名、表名大小寫問題
在 MySQL 中，數(shù)據(jù)庫(kù)中的每個(gè)表至少對(duì)應(yīng)數(shù)據(jù)庫(kù)目錄中的一個(gè)文件，當(dāng)然這取決于存儲(chǔ)引擎的實(shí)現(xiàn)了。不同的操作系統(tǒng)對(duì)大小寫的敏感性決定了數(shù)據(jù)庫(kù)和表名的大小寫的敏感性。在 UNIX 操作系統(tǒng)中是對(duì)大小寫敏感的，因此數(shù)據(jù)庫(kù)名和表名也是具有敏感性的，而到了 Windows 則不存在敏感性問題，因?yàn)?Windows 操作系統(tǒng)本身對(duì)大小寫不敏感。列、索引、觸發(fā)器在任何平臺(tái)上都對(duì)大小寫不敏感。
在 MySQL 中，數(shù)據(jù)庫(kù)名和表名是由lower_case_tables_name系統(tǒng)變量決定的?？梢栽趩?dòng)mysqld時(shí)設(shè)置這個(gè)系統(tǒng)變量。下面是lower_case_tables_name的值。
>need-to-insert-img
如果只在一個(gè)平臺(tái)上使用 MySQL 的話，通常不需要修改lower_case_tables_name變量。如果想要在不同系統(tǒng)系統(tǒng)之間遷移表就會(huì)涉及到大小寫問題，因?yàn)?UNIX 中 clerk_info 和 CLERK_INFO 被認(rèn)為是兩個(gè)不同的表，而 Windows 中則認(rèn)為是一個(gè)。在 UNIX 中使用 lower_case_tables_name=0， 而在 Windows 中使用lower_case_tables_name=2，這樣可以保留數(shù)據(jù)庫(kù)名和表名的大小寫，但是不能保證所有的 SQL 查詢中使用的表名和數(shù)據(jù)庫(kù)名的大小寫相同。如果 SQL 語(yǔ)句中沒有正確引用數(shù)據(jù)庫(kù)名和表名的大小寫，那么雖然在 Windows 中能正確執(zhí)行，但是如果將查詢轉(zhuǎn)移到 UNIX 中，大小寫不正確，將會(huì)導(dǎo)致查詢失敗。
外鍵問題
這里需要注意一個(gè)問題，InnoDB存儲(chǔ)引擎是支持外鍵的，而MyISAM存儲(chǔ)引擎是不支持外鍵的，因此在 MyISAM 中設(shè)置外鍵會(huì)不起作用。
MySQL 常用函數(shù)
下面我們來了解一下 MySQL 函數(shù)，MySQL 函數(shù)也是我們?nèi)粘ｉ_發(fā)過程中經(jīng)常使用的，選用合適的函數(shù)能夠提高我們的開發(fā)效率，下面我們就來一起認(rèn)識(shí)一下這些函數(shù)
字符串函數(shù)
字符串函數(shù)是最常用的一種函數(shù)了，MySQL 也是支持很多種字符串函數(shù)，下面是 MySQL 支持的字符串函數(shù)表
函數(shù)功能
LOWER將字符串所有字符變?yōu)樾?/p>
UPPER將字符串所有字符變?yōu)榇髮?/p>
CONCAT進(jìn)行字符串拼接
LEFT返回字符串最左邊的字符
RIGHT返回字符串最右邊的字符
INSERT字符串替換
LTRIM去掉字符串左邊的空格
RTRIM去掉字符串右邊的空格
REPEAT返回重復(fù)的結(jié)果
TRIM去掉字符串行尾和行頭的空格
SUBSTRING返回指定的字符串
LPAD用字符串對(duì)最左邊進(jìn)行填充
RPAD用字符串對(duì)最右邊進(jìn)行填充
STRCMP比較字符串 s1 和 s2
REPLACE進(jìn)行字符串替換
下面通過具體的示例演示一下每個(gè)函數(shù)的用法
LOWER(str) 和 UPPER(str) 函數(shù)：用于轉(zhuǎn)換大小寫
>need-to-insert-img
CONCAT(s1,s2 ... sn) ：把傳入的參數(shù)拼接成一個(gè)字符串
>need-to-insert-img
上面把c xu an拼接成為了一個(gè)字符串，另外需要注意一點(diǎn)，任何和 NULL 進(jìn)行字符串拼接的結(jié)果都是 NULL。
>need-to-insert-img
LEFT(str,x) 和 RIGHT(str,x) 函數(shù)：分別返回字符串最左邊的 x 個(gè)字符和最右邊的 x 個(gè)字符。如果第二個(gè)參數(shù)是 NULL，那么將不會(huì)返回任何字符串
>need-to-insert-img
INSERT(str,x,y,instr) ： 將字符串 str 從指定 x 的位置開始， 取 y 個(gè)長(zhǎng)度的字串替換為 instr。
>need-to-insert-img
LTRIM(str) 和 RTRIM(str) 分別表示去掉字符串 str 左側(cè)和右側(cè)的空格
>need-to-insert-img
REPEAT(str,x) 函數(shù)：返回 str 重復(fù) x 次的結(jié)果
>need-to-insert-img
TRIM(str) 函數(shù)：用于去掉目標(biāo)字符串的空格
>need-to-insert-img
SUBSTRING(str,x,y) 函數(shù)：返回從字符串 str 中第 x 位置起 y 個(gè)字符長(zhǎng)度的字符串
>need-to-insert-img
LPAD(str,n,pad) 和 RPAD(str,n,pad) 函數(shù)：用字符串 pad 對(duì) str 左邊和右邊進(jìn)行填充，直到長(zhǎng)度為 n 個(gè)字符長(zhǎng)度
>need-to-insert-img
STRCMP(s1,s2) 用于比較字符串 s1 和 s2 的 ASCII 值大小。如果 s1 < s2，則返回 -1；如果 s1 = s2 ，返回 0 ；如果 s1 > s2 ，返回 1。
>need-to-insert-img
REPLACE(str,a,b) : 用字符串 b 替換字符串 str 種所有出現(xiàn)的字符串 a
>need-to-insert-img
數(shù)值函數(shù)
MySQL 支持?jǐn)?shù)值函數(shù)，這些函數(shù)能夠處理很多數(shù)值運(yùn)算。下面我們一起來學(xué)習(xí)一下 MySQL 中的數(shù)值函數(shù)，下面是所有的數(shù)值函數(shù)
函數(shù)功能
ABS返回絕對(duì)值
CEIL返回大于某個(gè)值的最大整數(shù)值
MOD返回模
ROUND四舍五入
FLOOR返回小于某個(gè)值的最大整數(shù)值
TRUNCATE返回?cái)?shù)字截?cái)嘈?shù)的結(jié)果
RAND返回 0 - 1 的隨機(jī)值
下面我們還是以實(shí)踐為主來聊一聊這些用法
ABS(x) 函數(shù)：返回 x 的絕對(duì)值
>need-to-insert-img
CEIL(x) 函數(shù)： 返回大于 x 的整數(shù)
>need-to-insert-img
MOD(x,y)，對(duì) x 和 y 進(jìn)行取模操作
>need-to-insert-img
ROUND(x,y) 返回 x 四舍五入后保留 y 位小數(shù)的值；如果是整數(shù)，那么 y 位就是 0 ；如果不指定 y ，那么 y 默認(rèn)也是 0 。
>need-to-insert-img
FLOOR(x) : 返回小于 x 的最大整數(shù)，用法與 CEIL 相反
>need-to-insert-img
TRUNCATE(x,y): 返回?cái)?shù)字 x 截?cái)酁?y 位小數(shù)的結(jié)果， TRUNCATE 知識(shí)截?cái)?，并不是四舍五入?/p>
>need-to-insert-img
RAND() ：返回 0 到 1 的隨機(jī)值
>need-to-insert-img
日期和時(shí)間函數(shù)
日期和時(shí)間函數(shù)也是 MySQL 中非常重要的一部分，下面我們就來一起認(rèn)識(shí)一下這些函數(shù)
函數(shù)功能
NOW返回當(dāng)前的日期和時(shí)間
WEEK返回一年中的第幾周
YEAR返回日期的年份
HOUR返回小時(shí)值
MINUTE返回分鐘值
MONTHNAME返回月份名
CURDATE返回當(dāng)前日期
CURTIME返回當(dāng)前時(shí)間
UNIX_TIMESTAMP返回日期 UNIX 時(shí)間戳
DATE_FORMAT返回按照字符串格式化的日期
FROM_UNIXTIME返回 UNIX 時(shí)間戳的日期值
DATE_ADD返回日期時(shí)間 + 上一個(gè)時(shí)間間隔
DATEDIFF返回起始時(shí)間和結(jié)束時(shí)間之間的天數(shù)
下面結(jié)合示例來講解一下每個(gè)函數(shù)的使用
NOW(): 返回當(dāng)前的日期和時(shí)間
>need-to-insert-img
WEEK(DATE) 和 YEAR(DATE) ：前者返回的是一年中的第幾周，后者返回的是給定日期的哪一年
>need-to-insert-img
HOUR(time) 和 MINUTE(time) : 返回給定時(shí)間的小時(shí)，后者返回給定時(shí)間的分鐘
>need-to-insert-img
MONTHNAME(date) 函數(shù)：返回 date 的英文月份
>need-to-insert-img
CURDATE() 函數(shù)：返回當(dāng)前日期，只包含年月日
CURTIME() 函數(shù)：返回當(dāng)前時(shí)間，只包含時(shí)分秒
UNIX_TIMESTAMP(date) : 返回 UNIX 的時(shí)間戳
FROM_UNIXTIME(date) : 返回 UNIXTIME 時(shí)間戳的日期值，和 UNIX_TIMESTAMP 相反
>need-to-insert-img
DATE_FORMAT(date,fmt) 函數(shù)：按照字符串 fmt 對(duì) date 進(jìn)行格式化，格式化后按照指定日期格式顯示
具體的日期格式可以參考這篇文章blog.csdn.net/weixin_3870…
我們演示一下將當(dāng)前日期顯示為年月日的這種形式，使用的日期格式是%M %D %Y。
>need-to-insert-img
DATE_ADD(date, interval, expr type) 函數(shù)：返回與所給日期 date 相差 interval 時(shí)間段的日期
interval 表示間隔類型的關(guān)鍵字，expr 是表達(dá)式，這個(gè)表達(dá)式對(duì)應(yīng)后面的類型，type 是間隔類型，MySQL 提供了 13 種時(shí)間間隔類型
表達(dá)式類型描述格式
YEAR年YY
MONTH月MM
DAY日DD
HOUR小時(shí)hh
MINUTE分mm
SECOND秒ss
YEAR_MONTH年和月YY-MM
DAY_HOUR日和小時(shí)DD hh
DAY_MINUTE日和分鐘DD hh : mm
DAY_SECOND日和秒DD hh ：mm ：ss
HOUR_MINUTE小時(shí)和分hh:mm
HOUR_SECOND小時(shí)和秒hh:ss
MINUTE_SECOND分鐘和秒mm:ss
DATE_DIFF(date1, date2) 用來計(jì)算兩個(gè)日期之間相差的天數(shù)
>need-to-insert-img
查看離 2021 - 01 - 01 還有多少天
流程函數(shù)
流程函數(shù)也是很常用的一類函數(shù)，用戶可以使用這類函數(shù)在 SQL 中實(shí)現(xiàn)條件選擇。這樣做能夠提高查詢效率。下表列出了這些流程函數(shù)
函數(shù)功能
IF(value,t f)如果 value 是真，返回 t；否則返回 f
IFNULL(value1,value2)如果 value1 不為 NULL，返回 value1，否則返回 value2。
CASE WHEN[value1] THEN[result1] ...ELSE[default] END如果 value1 是真，返回 result1，否則返回 default
CASE[expr] WHEN[value1] THEN [result1]... ELSE[default] END如果 expr 等于 value1， 返回 result1， 否則返回 default
其他函數(shù)
除了我們介紹過的字符串函數(shù)、日期和時(shí)間函數(shù)、流程函數(shù)，還有一些函數(shù)并不屬于上面三類函數(shù)，它們是
函數(shù)功能
VERSION返回當(dāng)前數(shù)據(jù)庫(kù)的版本
DATABASE返回當(dāng)前數(shù)據(jù)庫(kù)名
USER返回當(dāng)前登陸用戶名
PASSWORD返回字符串的加密版本
MD5返回 MD5 值
INET_ATON(IP)返回 IP 地址的數(shù)字表示
INET_NTOA(num)返回?cái)?shù)字代表的 IP 地址
下面來看一下具體的使用
VERSION: 返回當(dāng)前數(shù)據(jù)庫(kù)版本
DATABASE: 返回當(dāng)前的數(shù)據(jù)庫(kù)名
USER : 返回當(dāng)前登錄用戶名
PASSWORD(str) : 返回字符串的加密版本，例如
MD5(str) 函數(shù)：返回字符串 str 的 MD5 值
>need-to-insert-img
INET_ATON(IP): 返回 IP 的網(wǎng)絡(luò)字節(jié)序列
INET_NTOA(num)函數(shù)：返回網(wǎng)絡(luò)字節(jié)序列代表的 IP 地址，與 INET_ATON 相對(duì)
總結(jié)
這篇文章我?guī)闶职咽謹(jǐn)]了一波 MySQL 的高級(jí)內(nèi)容，其實(shí)說高級(jí)也不一定真的高級(jí)或者說難，其實(shí)就是區(qū)分不同梯度的東西。
