快速學(xué)習(xí)MySQL基礎(chǔ)知識(shí)

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

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

了解 SQL

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

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

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

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

目前排名較前的 DBMS：

• 關(guān)系型數(shù)據(jù)庫：建立在關(guān)系模型上的數(shù)據(jù)庫，在建表時(shí)，通常先設(shè)計(jì) ER 圖表示之間的關(guān)系。
• 鍵值型數(shù)據(jù)庫：以 key-value 的形式存儲(chǔ)數(shù)據(jù)，優(yōu)點(diǎn)是查找速度快，缺點(diǎn)是無法向關(guān)系型數(shù)據(jù)庫一樣使用如 WHERE 等的過濾條件。常見場(chǎng)景是作為內(nèi)容緩存。
• 文檔型數(shù)據(jù)庫，在保存時(shí)以文檔作為處理信息的基本單位。
• 搜索引擎：針對(duì)全文檢索而設(shè)計(jì)。核心原理是 “倒排索引”。
• 列式數(shù)據(jù)庫：相對(duì)于如 MySQL 等行式存儲(chǔ)的數(shù)據(jù)庫，是以列將數(shù)據(jù)存在數(shù)據(jù)庫中，由于列具有相同的數(shù)據(jù)類型，所以可以更好的壓縮，從而減低系統(tǒng)的 I/O，適用于分布式文件系統(tǒng)，但功能相對(duì)有限。
• 圖形數(shù)據(jù)庫，利用圖的數(shù)據(jù)結(jié)構(gòu)存儲(chǔ)實(shí)體之間的關(guān)系。比如社交網(wǎng)絡(luò)中人與人的關(guān)系，數(shù)據(jù)模型為節(jié)點(diǎn)和邊來實(shí)現(xiàn)。
  

認(rèn)識(shí) SELECT

SELECT 一般是在學(xué)習(xí) SQL 接觸的第一個(gè)關(guān)鍵字，基礎(chǔ)的內(nèi)容就是不提了，這里整理常用的規(guī)范：

起別名

SELECT name AS n FROM student

查詢常數(shù), 增加一列固定的常數(shù)列：

SELECT '學(xué)生信息' as student_info, name FROM student

去重重復(fù)行

SELECT DISTINCT age FROM student

需要注意的是 DISTINCT 是對(duì)后面的所有列進(jìn)行去重, 下面這種情況就會(huì)對(duì) age 和 name 的組合進(jìn)行去重。

SELECT DISTINCT age,name FROM student

排序數(shù)據(jù),ASC 代表升序，DESC 代表降序

如先按照 name 排序，name 相等的情況下按照 age 排序。

SELECT DISTINCT age FROM student ORDERY BY name,age DESC

限制返回的數(shù)量

SELECT DISTINCT age FROM student ORDERY BY name DESC LIMIT 5

SELECT 的執(zhí)行順序

了解了 SELECT 的執(zhí)行順序，才能更好地寫出更有效率的 SQL。

對(duì)于 SELECT 順序有兩個(gè)原則：

• 關(guān)鍵字的順序不能顛倒：

  SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...

• SELECT 會(huì)按照如下順序執(zhí)行：

  FROM > WHERE > GROUP BY > HAVING > SELECT的字段 > DISTINCT > ORDER BY > LIMIT

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

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

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

如果涉及到函數(shù)的計(jì)算比如 sum() 等，會(huì)在 GROUP BY分組后，HAVING 分組前，進(jìn)行聚集函數(shù)的計(jì)算。

涉及到表達(dá)式計(jì)算，如 age * 10 等，會(huì)在 HAVING 階段后，SELECT 階段前進(jìn)行計(jì)算。

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

使用 SELECT 時(shí)指定明確的列來代替 SELECT * . 從而減少網(wǎng)絡(luò)的傳輸量。

使用 WHERE 進(jìn)行過濾

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

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

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

需要注意的是，當(dāng) AND 和 OR 同時(shí)出現(xiàn)時(shí)，AND 的優(yōu)先級(jí)更高會(huì)先被執(zhí)行。當(dāng)如果存在 () 的話，則括號(hào)的優(yōu)先級(jí)最高。

使用通配符過濾：

like：（%）代表零個(gè)或多個(gè)字符，（_）只代表一個(gè)字符

函數(shù)

和編程語言中的定義的函數(shù)一樣，SQL 同樣定義了一些函數(shù)方便使用，比如求和，平均值，長(zhǎng)度等。

常見的函數(shù)主要分為如下四類，分類的原則是根據(jù)定義列時(shí)的數(shù)據(jù)類型：

• 算術(shù)函數(shù)：

• 字符串函數(shù)

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

• 日期函數(shù)

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

CAST 函數(shù)在轉(zhuǎn)換數(shù)據(jù)類型時(shí)，不會(huì)四舍五入，如果原數(shù)值是小數(shù)，在轉(zhuǎn)換到整數(shù)時(shí)會(huì)報(bào)錯(cuò)。

在轉(zhuǎn)換時(shí)可以使用 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ù)

通常情況下，我們會(huì)使用聚集函數(shù)來匯總表的數(shù)據(jù)，輸入為一組數(shù)據(jù)，輸出為單個(gè)值。

常用的聚集函數(shù)有 5 個(gè)：

其中 COUNT 函數(shù)需要額外注意，具體的內(nèi)容可以參考這篇。

如何進(jìn)行分組

在統(tǒng)計(jì)結(jié)果時(shí)，往往需要對(duì)數(shù)據(jù)按照一定條件進(jìn)行分組，對(duì)應(yīng)就是 GROUP BY 語句。

比如統(tǒng)計(jì)每個(gè)班級(jí)的學(xué)生人數(shù)：

SELECT class_id, COUNT(*) as student_count FROM student \

GROUP BY class_id; 

GROUP BY 后也可接多個(gè)列名，進(jìn)行分組，比如按照班級(jí)和性別分組：

SELECT class_id, sex, COUNT(*) as student_count FROM \

student GROUP BY class_id, sex; 

HAVING 過濾和 WHERE 的區(qū)別

和 WHERE 一樣，可以對(duì)分組后的數(shù)據(jù)進(jìn)行篩選。區(qū)別在于 WHERE 適用于數(shù)據(jù)行，HAVING 用于分組。

而且 WHERE 支持的操作，HAVING 也同樣支持。

比如可以篩選大于2人的班級(jí)：

SELECT class_id, COUNT(*) as student_count FROM student \ 
GROUP BY class_id \ 
HAVING student_count > 20; 

子查詢

在一些更為復(fù)雜的情況中，往往會(huì)進(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 '班級(jí)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', '王意識(shí)', 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 '班級(jí)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é)果，如果這個(gè)數(shù)據(jù)結(jié)果只執(zhí)行一次，然后這個(gè)數(shù)據(jù)結(jié)果作為主查詢的條件接著執(zhí)行。

這里想要查詢和胡一相同班級(jí)的同學(xué)名稱：

SELECT name FROM student WHERE class_id = \

(SELECT class_id FROM student WHERE name='胡一')

這里先查到胡一的班級(jí)，只有一次查詢，再根據(jù)該班級(jí)查找學(xué)生就是非關(guān)聯(lián)子查詢。

關(guān)聯(lián)子查詢

如果子查詢需要執(zhí)行多次，即采用循環(huán)的方式，先從外部查詢開始，每次都傳入子查詢進(jìn)行查詢，然后再將結(jié)果反饋給外部

再舉個(gè)例子, 比如查詢比每個(gè)班級(jí)中比平均年齡大的學(xué)生姓名信息：

SELECT name FROM student as s1 WHERE age > 
	(SELECT AVG(age) FROM student as s2 where s1.class_id = s2.class_id) 

這里根據(jù)每名同學(xué)的班級(jí)信息，查找出對(duì)應(yīng)班級(jí)的平均年齡，然后做判斷。子查詢每次執(zhí)行時(shí)，都需要根據(jù)外部的查詢?nèi)缓筮M(jìn)行計(jì)算。這樣的子查詢就是關(guān)聯(lián)子查詢。

EXISTS 子查詢

在關(guān)聯(lián)子查詢中，常會(huì)和 EXISTS 一起使用。用來判斷條件是否滿足，滿足的話為 True，不滿足為 False。

比如查詢參加過學(xué)校活動(dòng)的學(xué)生名稱：

SELECT NAME FROM student as s where \

	EXISTS(SELECT stu_id FROM student_activities as sa where sa.stu_id=s.id)

同樣 NOT EXISTS 就是不存在的意思，滿足為 FALSE , 不滿足為 True.

比如查詢沒有參加過學(xué)校活動(dòng)的學(xué)生名稱：

SELECT NAME FROM student as s where \

	NOT EXISTS(SELECT stu_id FROM student_activities as sa where sa.stu_id=s.id)

集合比較子查詢

可以在子查詢中，使用集合操作符，來比較結(jié)果。

還是上面查詢參加學(xué)?；顒?dòng)的學(xué)生名字的子查詢, 同樣可以使用 IN：

SELECT name FROM student WHERE id IN (SELECT stu_id FROM student_activities)

EXISTS 和 IN 的區(qū)別

既然 EXISTS 和 IN 都能實(shí)現(xiàn)相同的功能，那么他們之間的區(qū)別是什么？

現(xiàn)在假設(shè)我們有表 A 和 表 B，其中 A，B 都有字段 cc，并對(duì) 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)

對(duì)于 EXISTS 來說，會(huì)先對(duì)外表進(jìn)行逐條循環(huán)，每次拿到外表的結(jié)果后，帶入子查詢的內(nèi)表中，去判斷該值是否存在。

偽代碼類似于下面：

for i in A
    for j in B
        if j.cc == i.cc:
         return result

首先先看外表 A，每一條都需要遍歷到，所以需要 n 次。內(nèi)表 B，在查詢時(shí)由于使用索引進(jìn)而查詢效率變成 log(m) B+ 的樹高，而不是 m。

進(jìn)而總效率：n * log(m)

所以對(duì)于 A 表的數(shù)量明顯小于 B 時(shí)，推薦使用 EXISTS 查詢。

再看 IN ，會(huì)先對(duì)內(nèi)表 B 進(jìn)行查詢，然后用外表 A 進(jìn)行判斷，偽代碼如下：

for i in B
    for j in A
        if j.cc == i.cc:
         return result

由于需要首先將內(nèi)表所有數(shù)據(jù)查出，所以需要的次數(shù)就是 m. 再看外表 A ，由于使用了 cc 索引，可將 n 簡(jiǎn)化至 log(n), 也就是 m * log(n).

所以對(duì)于 A 表的數(shù)據(jù)明顯大于 B 表時(shí)，推薦使用 IN 查詢。

總結(jié)一下對(duì)于 IN 和 EXISTS時(shí)，采用小表驅(qū)動(dòng)大表的原則。

這里再擴(kuò)展下 NOT EXISTS 和 NOT IN 的區(qū)別：

SELECT * FROM A WHERE cc NOT IN (SELECT cc FROM B) 

SELECT * FROM A WHERE NOT EXIST (SELECT cc FROM B WHERE B.cc=A.cc)

對(duì)于 NOT EXITS 來說，和 EXISTS 一樣，對(duì)于內(nèi)表可以使用 cc 的索引。適用于 A 表小于 B 表的情況。

但對(duì)于 NOT IN 來說，和 IN 就有區(qū)別了，由于 cc 設(shè)置了索引 cc IN (1, 2, 3) 可以轉(zhuǎn)換成 WHERE cc=1 OR cc=2 OR cc=3 , 是可以正常走 cc 索引的。但對(duì)于 NOT IN 也就是轉(zhuǎn)化為 cc!=1 OR cc!=2 OR cc!=3 這時(shí)由于是不等號(hào)查詢，是無法走索引的，進(jìn)而全表掃描。

也就是說，在設(shè)置索引的情況下 NOT EXISTS 比 NOT IN 的效率高。

但對(duì)于沒有索引的情況，IN 和 OR 是不同的：

一、操作不同
1、in：in是把父查詢表和子查詢表作hash連接。
2、or：or是對(duì)父查詢表作loop循環(huán)，每次loop循環(huán)再對(duì)子查詢表進(jìn)行查詢。

二、適用場(chǎng)景不同
1、in：in適合用于子查詢表數(shù)據(jù)比父查詢表數(shù)據(jù)多的情況。
2、or：or適合用于子查詢表數(shù)據(jù)比父查詢表數(shù)據(jù)少的情況。

三、效率不同
1、in：在沒有索引的情況下，隨著in后面的數(shù)據(jù)量越多，in的執(zhí)行效率不會(huì)有太大的下降。
2、or：在沒有索引的情況下，隨著or后面的數(shù)據(jù)量越多，or的執(zhí)行效率會(huì)有明顯的下降。

總結(jié)

這篇文章中主要?dú)w納了一些 SQL 的基礎(chǔ)知識(shí)：

在使用 SELECT 查詢時(shí)，通過顯式指定列名，來減少 IO 的傳輸，從而提高效率。

并且需要注意 SELECT 的查詢過程會(huì)從 FROM 后開始到 LIMIT 結(jié)束，理解了整體的流程，可以讓我們更好的組織 SQL.

之后詳細(xì)介紹了 WHERE 進(jìn)行過濾的操作符和常用的函數(shù)，這里要注意在比較時(shí)間時(shí)要使用 DATE 函數(shù)，以及如何對(duì)數(shù)據(jù)進(jìn)行分組和過濾。

最后著重介紹了子查詢，IN 和 EXISTS 的適用場(chǎng)景。

以上就是快速學(xué)習(xí)MySQL基礎(chǔ)知識(shí)的詳細(xì)內(nèi)容，更多關(guān)于MySQL基礎(chǔ)知識(shí)的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！