本文主要梳理了 SQL 的基礎(chǔ)用法，會涉及到以下方面內(nèi)容：

SQL大小寫的規(guī)范數(shù)據(jù)庫的類型以及適用場景SELECT 的執(zhí)行過程WHERE 使用規(guī)范MySQL 中常見函數(shù)子查詢分類如何選擇合適的 EXISTS 和 IN子查詢

了解 SQL

SQL 是我們用來和數(shù)據(jù)打交道的方式之一，如果按照功能劃分可分為如下 4 個部分：

DDL，數(shù)據(jù)定義語言。定義數(shù)據(jù)庫對象，數(shù)據(jù)表，數(shù)據(jù)列。也就是，對數(shù)據(jù)庫和表結(jié)構(gòu)進(jìn)行增刪改操作。DML，數(shù)據(jù)操作語言。對數(shù)據(jù)表的增刪改。DCL，數(shù)據(jù)控制語言。定義訪問權(quán)限和安全級別。DQL，數(shù)據(jù)查詢語言。用來查詢數(shù)據(jù)。

平時在編寫 SQL 時，可能發(fā)現(xiàn)許多 SQL 大小寫不統(tǒng)一，雖然不會影響 SQL 的執(zhí)行結(jié)果，但保持統(tǒng)一的書寫規(guī)范，是提高效率的關(guān)鍵，通常遵循如下的原則：

表名，表別名，字段名，字段別名等用小寫。SQL 保留字，函數(shù)名，綁定變量等用大寫。數(shù)據(jù)表，字段名采用下劃線命名。

目前排名較前的 DBMS：

關(guān)系型數(shù)據(jù)庫：建立在關(guān)系模型上的數(shù)據(jù)庫，在建表時，通常先設(shè)計 ER 圖表示之間的關(guān)系。鍵值型數(shù)據(jù)庫：以 key-value 的形式存儲數(shù)據(jù)，優(yōu)點是查找速度快，缺點是無法像關(guān)系型數(shù)據(jù)庫一樣使用如 WHERE 等的過濾條件。常見場景是作為內(nèi)容緩存。文檔型數(shù)據(jù)庫，在保存時以文檔作為處理信息的基本單位。搜索引擎：針對全文檢索而設(shè)計。核心原理是 “倒排索引”。列式數(shù)據(jù)庫：相對于如 MySQL 等行式存儲的數(shù)據(jù)庫，是以列將數(shù)據(jù)存在數(shù)據(jù)庫中，由于列具有相同的數(shù)據(jù)類型，所以可以更好的壓縮，從而減低系統(tǒng)的 I/O，適用于分布式文件系統(tǒng)，但功能相對有限。圖形數(shù)據(jù)庫，利用圖的數(shù)據(jù)結(jié)構(gòu)存儲實體之間的關(guān)系。比如社交網(wǎng)絡(luò)中人與人的關(guān)系，數(shù)據(jù)模型為節(jié)點和邊來實現(xiàn)。認(rèn)識SELECTSELECT一般是在學(xué)習(xí)SQL接觸的第一個關(guān)鍵字，基礎(chǔ)的內(nèi)容就不提了，這里整理常用的規(guī)范：起別名SELECTnameAS n FROM student查詢常數(shù), 增加一列固定的常數(shù)列：SELECT'學(xué)生信息'as student_info, nameFROM student去重重復(fù)行SELECTDISTINCT age FROM student需要注意的是DISTINCT是對后面的所有列進(jìn)行去重，下面這種情況就會對age和name的組合進(jìn)行去重。SELECTDISTINCT age,nameFROM student排序數(shù)據(jù)，ASC代表升序，DESC代表降序如先按照name排序，name相等的情況下按照age排序。SELECTDISTINCT age FROM student ORDERY BYname,age DESC限制返回的數(shù)量SELECTDISTINCT age FROM student ORDERY BYnameDESCLIMIT5SELECT 的執(zhí)行順序了解了SELECT的執(zhí)行順序，才能更好地寫出更有效率的 SQL。對于SELECT順序有兩個原則：關(guān)鍵字的順序不能顛倒：SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...SELECT會按照如下順序執(zhí)行：FROM > WHERE > GROUP BY > HAVING > SELECT的字段 > DISTINCT > ORDER BY > LIMIT

SELECTDISTINCT student_id, name, count(*) asnum#順序5FROM student JOIN class ON student.class_id = class.class_id #順序1WHERE age > 18 #順序2GROUP BY student.class_id #順序3HAVING num > 2 #順序4ORDER BY num DESC #順序6LIMIT 2 #順序7

在逐一分析下這個過程前，我們需要知道在上面的每一個步驟中都會產(chǎn)生一個虛擬表，然后將這個虛擬表作為下一個步驟中作為輸入，但這一過程對我們來說是不可見的：

從FROM語句開始，對student和class表進(jìn)行CROSS JOIN 笛卡爾積運(yùn)算，得到虛擬表 vt 1-1；通過 ON 篩選，在 vt1-1 的基礎(chǔ)上進(jìn)行過濾然后得到表 vt 1-2；添加外部行。如使用左連接，右連接和全連接時，就會涉及到外部行，會在 vt1-2 的基礎(chǔ)上增加外部行，得到 vt1-3。如果超過兩張表，就會重復(fù)上面的步驟。在拿到最終的 vt1 的表數(shù)據(jù)后，會執(zhí)行 WHERE 后面的過濾階段，得到表 vt2.接著到 GROUP 階段，進(jìn)行分組得到 vt3.接著到 HAVING 階段，對分組的數(shù)據(jù)進(jìn)行過濾，得到 vt4.后面進(jìn)入 SELECT 階段，提取需要的字段，得到 vt5-1，接著通過 DISTINCT 階段，過濾到重復(fù)的行，得到 vt5-2.然后對指定的字段進(jìn)行排序，進(jìn)入 ORDER BY 階段，得到 vt6.最后在 LIMIT 階段，取出指定的行，對應(yīng) vt7，也就是最后的結(jié)果。如果涉及到函數(shù)的計算比如 sum() 等，會在 GROUP BY分組后，HAVING 分組前，進(jìn)行聚集函數(shù)的計算。涉及到表達(dá)式計算，如 age * 10 等，會在 HAVING 階段后，SELECT 階段前進(jìn)行計算。

通過這里，就可以總結(jié)出提高 SQL 效率的第一個方法：

使用 SELECT 時指定明確的列來代替 SELECT * . 從而減少網(wǎng)絡(luò)的傳輸量。使用 WHERE 進(jìn)行過濾

使用 WHERE 篩選時，常有通過比較運(yùn)算符，邏輯運(yùn)算符，通配符三種方式。

對于比較運(yùn)算符，常用的運(yùn)算符如下表。

對于邏輯運(yùn)算符來說，可以將多個比較運(yùn)行符連接起來，進(jìn)行多條件的篩選，常用的運(yùn)算符如下：

需要注意的是，當(dāng)AND和OR同時出現(xiàn)時，AND的優(yōu)先級更高會先被執(zhí)行。當(dāng)如果存在 () 的話，則括號的優(yōu)先級最高。使用通配符過濾：like：(%)代表零個或多個字符，(_)只代表一個字符函數(shù)和編程語言中的定義的函數(shù)一樣，SQL同樣定義了一些函數(shù)方便使用，比如求和、平均值、長度等。常見的函數(shù)主要分為如下四類，分類的原則是根據(jù)定義列時的數(shù)據(jù)類型：算術(shù)函數(shù)：

字符串函數(shù)

需要注意的是，在使用字符串比較日期時，要使用DATE函數(shù)比較。日期函數(shù)

轉(zhuǎn)換函數(shù)：

CAST函數(shù)在轉(zhuǎn)換數(shù)據(jù)類型時，不會四舍五入，如果原數(shù)值是小數(shù)，在轉(zhuǎn)換到整數(shù)時會報錯。在轉(zhuǎn)換時可以使用DECIMAL(a,b) 函數(shù)來規(guī)定小數(shù)的精度，比如DECIMAL(8,2) 表示精度為8 位 - 小數(shù)加整數(shù)最多 8 位。小數(shù)后面最多為 2 位。然后通過 SELECT CAST(123.123 AS DECIMAL(8,2)) 來轉(zhuǎn)換。聚集函數(shù)通常情況下，我們會使用聚集函數(shù)來匯總表的數(shù)據(jù)，輸入為一組數(shù)據(jù)，輸出為單個值。常用的聚集函數(shù)有 5 個：

如何進(jìn)行分組在統(tǒng)計結(jié)果時，往往需要對數(shù)據(jù)按照一定條件進(jìn)行分組，對應(yīng)就是GROUP BY語句。比如統(tǒng)計每個班級的學(xué)生人數(shù)：SELECT class_id, COUNT(*) as student_count FROM student \GROUPBY class_id;GROUP BY 后也可接多個列名，進(jìn)行分組，比如按照班級和性別分組：SELECT class_id, sex, COUNT(*) as student_count FROM \student GROUPBY class_id, sex;HAVING 過濾和 WHERE 的區(qū)別和 WHERE 一樣，可以對分組后的數(shù)據(jù)進(jìn)行篩選。區(qū)別在于 WHERE 適用于數(shù)據(jù)行，HAVING 用于分組。而且 WHERE 支持的操作，HAVING 也同樣支持。比如可以篩選大于2人的班級：SELECT class_id, COUNT(*) as student_count FROM student \ GROUPBY class_id \ HAVING student_count > 20;子查詢在一些更為復(fù)雜的情況中，往往會進(jìn)行嵌套的查詢，比如在獲取結(jié)果后，該結(jié)果作為輸入，去獲取另外一組結(jié)果。在 SQL 中，查詢可以分為關(guān)聯(lián)子查詢和非關(guān)聯(lián)子查詢。假設(shè)有如下的表結(jié)構(gòu)：-- ----------------------------DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (`id`int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) NOT NULL DEFAULT '',`age`int(3) NOT NULL,`sex` varchar(10) NOT NULL DEFAULT '',`class_id`int(11) NOT NULL COMMENT '班級ID', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;-- ------------------------------ Records of Student-- ----------------------------INSERT INTO `student` VALUES ('1', '胡一', 13, '男', '1');INSERT INTO `student` VALUES ('3', '王阿', 11, '女', '1');INSERT INTO `student` VALUES ('5', '王琦', 12, '男', '1');INSERT INTO `student` VALUES ('7', '劉偉', 11, '女', '1');INSERT INTO `student` VALUES ('7', '王意識', 11, '女', '2');-- ----------------------------DROP TABLE IF EXISTS `student_activities`;CREATE TABLE `student_activities` (`id`int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) NOT NULL DEFAULT '',`stu_id`int(11) NOT NULL COMMENT '班級ID', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;INSERT INTO `student_activities` VALUES ('1', '博物館', 1);INSERT INTO `student_activities` VALUES ('3, '春游', 3);非關(guān)聯(lián)子查詢子查詢從數(shù)據(jù)表中查詢了數(shù)據(jù)結(jié)果，如果這個數(shù)據(jù)結(jié)果只執(zhí)行一次，然后這個數(shù)據(jù)結(jié)果作為主查詢的條件接著執(zhí)行。這里想要查詢和胡一相同班級的同學(xué)名稱：SELECTnameFROM student WHERE class_id = \(SELECT class_id FROM student WHEREname='胡一')這里先查到胡一的班級，只有一次查詢，再根據(jù)該班級查找學(xué)生就是非關(guān)聯(lián)子查詢。關(guān)聯(lián)子查詢?nèi)绻硬樵冃枰獔?zhí)行多次，即采用循環(huán)的方式，先從外部查詢開始，每次都傳入子查詢進(jìn)行查詢，然后再將結(jié)果反饋給外部再舉個例子, 比如查詢比每個班級中比平均年齡大的學(xué)生姓名信息：SELECTnameFROM student as s1 WHERE age > (SELECTAVG(age) FROM student as s2 where s1.class_id = s2.class_id)這里根據(jù)每名同學(xué)的班級信息，查找出對應(yīng)班級的平均年齡，然后做判斷。子查詢每次執(zhí)行時，都需要根據(jù)外部的查詢?nèi)缓筮M(jìn)行計算。這樣的子查詢就是關(guān)聯(lián)子查詢。EXISTS子查詢在關(guān)聯(lián)子查詢中，常會和EXISTS 一起使用。用來判斷條件是否滿足，滿足的話為True，不滿足為 False。比如查詢參加過學(xué)校活動的學(xué)生名稱：SELECTNAMEFROM student as s where \EXISTS(SELECT stu_id FROM student_activities as sa where sa.stu_id=s.id)同樣NOT EXISTS就是不存在的意思，滿足為FALSE , 不滿足為True.比如查詢沒有參加過學(xué)校活動的學(xué)生名稱：SELECTNAMEFROM student as s where \NOTEXISTS(SELECT stu_id FROM student_activities as sa where sa.stu_id=s.id)集合比較子查詢可以在子查詢中，使用集合操作符，來比較結(jié)果。

還是上面查詢參加學(xué)校活動的學(xué)生名字的子查詢, 同樣可以使用 IN：SELECTnameFROM student WHEREidIN (SELECT stu_id FROM student_activities)EXISTS 和 IN 的區(qū)別既然 EXISTS 和 IN 都能實現(xiàn)相同的功能，那么他們之間的區(qū)別是什么？現(xiàn)在假設(shè)我們有表A和表B，其中 A、B 都有字段 cc，并對 cc 建立了 b+ 索引，其中 A 表 n 條記錄，B 表 m 條索引。將其模式抽象為：SELECT * FROM A WHERE cc IN (SELECT cc FROM B)SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)對于 EXISTS 來說，會先對外表進(jìn)行逐條循環(huán)，每次拿到外表的結(jié)果后，帶入子查詢的內(nèi)表中，去判斷該值是否存在。偽代碼類似于下面：for i in Afor j in Bif j.cc == i.cc:return result首先先看外表 A，每一條都需要遍歷到，所以需要 n 次。內(nèi)表 B，在查詢時由于使用索引進(jìn)而查詢效率變成 log(m) B+ 的樹高，而不是 m。進(jìn)而總效率：n * log(m)所以對于 A 表的數(shù)量明顯小于 B 時，推薦使用 EXISTS 查詢。再看 IN ，會先對內(nèi)表 B 進(jìn)行查詢，然后用外表 A 進(jìn)行判斷，偽代碼如下：for i in Bfor j in Aif j.cc == i.cc:return result由于需要首先將內(nèi)表所有數(shù)據(jù)查出，所以需要的次數(shù)就是 m. 再看外表 A ，由于使用了 cc 索引，可將 n 簡化至 log(n), 也就是 m * log(n).所以對于 A 表的數(shù)據(jù)明顯大于 B 表時，推薦使用 IN 查詢。總結(jié)一下對于 IN 和 EXISTS時，采用小表驅(qū)動大表的原則。這里再擴(kuò)展下 NOT EXISTS 和 NOT IN 的區(qū)別：SELECT * FROM A WHERE cc NOTIN (SELECT cc FROM B) SELECT * FROM A WHERENOT EXIST (SELECT cc FROM B WHERE B.cc=A.cc)對于 NOT EXITS 來說，和 EXISTS 一樣，對于內(nèi)表可以使用 cc 的索引。適用于 A 表小于 B 表的情況。但對于 NOT IN 來說，和 IN 就有區(qū)別了，由于 cc 設(shè)置了索引 cc IN (1, 2, 3) 可以轉(zhuǎn)換成 WHERE cc=1 OR cc=2 OR cc=3 , 是可以正常走 cc 索引的。但對于 NOT IN 也就是轉(zhuǎn)化為 cc!=1 OR cc!=2 OR cc!=3 這時由于是不等號查詢，是無法走索引的，進(jìn)而全表掃描。也就是說，在設(shè)置索引的情況下 NOT EXISTS 比 NOT IN 的效率高。但對于沒有索引的情況，IN 和 OR 是不同的：一、操作不同1、in：in是把父查詢表和子查詢表作hash連接。2、or：or是對父查詢表作loop循環(huán)，每次loop循環(huán)再對子查詢表進(jìn)行查詢。二、適用場景不同1、in：in適合用于子查詢表數(shù)據(jù)比父查詢表數(shù)據(jù)多的情況。2、or：or適合用于子查詢表數(shù)據(jù)比父查詢表數(shù)據(jù)少的情況。三、效率不同1、in：在沒有索引的情況下，隨著in后面的數(shù)據(jù)量越多，in的執(zhí)行效率不會有太大的下降。2、or：在沒有索引的情況下，隨著or后面的數(shù)據(jù)量越多，or的執(zhí)行效率會有明顯的下降。總結(jié)這篇文章中主要?dú)w納了一些 SQL 的基礎(chǔ)知識：在使用 SELECT 查詢時，通過顯式指定列名，來減少 IO 的傳輸，從而提高效率。并且需要注意 SELECT 的查詢過程會從 FROM 后開始到 LIMIT 結(jié)束，理解了整體的流程，可以讓我們更好的組織 SQL.之后詳細(xì)介紹了 WHERE 進(jìn)行過濾的操作符和常用的函數(shù)，這里要注意在比較時間時要使用 DATE 函數(shù)，以及如何對數(shù)據(jù)進(jìn)行分組和過濾。最后著重介紹了子查詢，IN 和 EXISTS 的適用場景。

總結(jié)

以上是生活随笔為你收集整理的mysql gt resource_实用干货，MYSQL这么用就对啦的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。