數據分析必要技能——踏進SQL的大門

來自專欄數據分析（初級）5 人贊了文章

前言：作為一名合格的數據分析師，不懂SQL語句，那隻能是業務上的分析師，不瞞你說，工資可不高，大佬們都是既會業務，也會技術，工資妥妥的！！！

這篇文章有點長，可能需要花個幾天時間，但是建議大夥堅持下，利用碎片化時間，上下班地鐵上打開文章抽空瞄兩眼，差距就是一點點拉開的！！！Keep Moving!!

一、安裝

工具善其事必先利其器，沒有傢伙我們怎麼能搞事

安裝方面我就不多說，安利一個良心的網址，有安裝過程的教程：軟體管家

二、學習之旅

1、資料庫是什麼

● 資料庫是將大量數據保存起來，通過計算機加工而成的可以進行高效訪問的數據集合。

● 用來管理資料庫的計算機系統稱為資料庫管理系統（DBMS）。

● 通過使用 DBMS，多個用戶便可安全、簡單地操作大量數據。

● 資料庫有很多種類，本書將介紹如何使用專門的 SQL語言來操作關係資料庫。

● 關係資料庫通過關係資料庫管理系統（RDBMS）進行管理。

2、創建資料庫與表

2-1、資料庫的創建

CREATE DATABASE <資料庫名稱>;

3-1、表的創建

CREATE TABLE <表名>

（<列名1> <數據類型> <該列所需約束>，

<列名2> <數據類型> <該列所需約束>，

<列名3> <數據類型> <該列所需約束>，

<列名4> <數據類型> <該列所需約束>，

.

.

.

<該表的約束1>， <該表的約束2>，……）；

舉例子:

CREATE TABLE Product

(product_id CHAR(4) NOT NULL,

product_name VARCHAR(100) NOT NULL,

product_type VARCHAR(32) NOT NULL,

sale_price INTEGER ,

purchase_price INTEGER ,

regist_date DATE ,

PRIMARY KEY (product_id));

命名規則：我們只能使用半形英文字母、數字、下劃線（_）作為資料庫、表和

列的名稱??。

3、表的刪除與更新

3-1、表的刪除

DROP TABLE <表名>；

3-2、表定義的更新

添加特定的列：ALTER TABLE <表名> ADD COLUMN <列的定義>；

刪除特定的列：ALTER TABLE <表名> DROP COLUMN <列名>；

3-3、表的更名

4、查詢基礎

4-1、SELECT語句基礎

1）基本查詢語句

SELECT <列名>，…… FROM <表名>

查詢所有的列：SELECT * FROM <表名>

2）從結果中刪除重複行

SELECT DISTINCT <列名>，…… FROM <表名>

注意：

DISTINCT 關鍵字只能用在第一個列名之前

3）根據WHERE語句來選擇記錄

SELECT <列名>, ……

FROM <表名>

WHERE <條件表達式>

注意：

SQL 中子句的書寫順序是固定的，不能隨意更改。

4）注釋

● 1行注釋

書寫在「--」之後，只能寫在同一行。

● 多行注釋

書寫在「/*」和「*/」之間，可以跨多行

4-2、算術運算符和比較運算符

1）算術運算符

注意：

SELECT子句中可以使用常數或者表達式。

所有包含 NULL 的計算，結果肯定是 NULL

2）比較運算符

注意：

在使用大於等於（>=）或者小於等於（<=）作為查詢條件時， 一定要注意不等號（<、>）和等號（=）的位置不能顛倒。一定要讓不等 號在左，等號在右。

3）不能對NULL使用比較運算符

判斷是否為空：IS NULL

判斷是否不為空：IS NOT NULL

4-3、邏輯運算符

1）NOT運算符

NOT運算符用來否定某一條件，但是不能濫用。放在該條件之前

2）AND運算符和OR運算符

● 在 WHERE 子句中使用 AND 運算符或者 OR 運算符，可以對多個查詢條件進行組合。

● AND 運算符在其兩側的查詢條件都成立時整個查詢條件才成立，其意思相當於「並且」。

● OR 運算符在其兩側的查詢條件有一個成立時整個查詢條件都成立，其意思相當於「或者」。

3）通過括弧強化處理

在書寫比較複雜的查詢語句的時候，可以使用括弧，有點類似於我們的數學，括弧外面的不影響括弧裡面的語句

4）邏輯運算符和真值

NOT、AND 和 OR 稱為邏輯運算符。這裡所 說的邏輯就是對真值進行操作的意思。真值就是值為真（TRUE）或假 （FALSE）其中之一的值

真值表

5、聚合與排序

5-1、對錶進行聚合查詢

1）聚合函數

用於匯總的函數稱為聚合函數或者聚集函數

2）計算表中數據的行數

COUNT（*） 計算全部數據的行數

3）計算NULL之外的數據的行數

COUNT（列名） 計算全部數據（不包括NULL值）的行數

4）計算合計值

SUM（列名） 計算該列的合計值

5）計算平均值

AVG（列名） 計算該列的平均值

6）計算最大值和最小值

MAX（列名） 計算該列得到最大值

MIN（列名） 計算該列的最小值

注意：

MAX/MIN函數幾乎適用於所有數據類型的列。SUM/AVG函數只適用於數值類型的列。

7）使用聚合函數刪除重複值（關鍵字DISTINCT）

SELECT COUNT(DISTINCT product_type) FROM Product;

注意：

想要計算值的種類時，可以在COUNT函數的參數中使用DISTINCT。

5-2、對錶進行分組

1）GROUP BY子句

SELECT <列名1>, <列名2>, <列名3>, ……

FROM <表名>

GROUP BY <列名1>, <列名2>, <列名3>, ……

按照商品種類進行分組

2）聚合鍵中包含NULL的情況

注意：

聚合鍵中包含NULL時，在結果中會以「不確定」行（空行）的形式表現出來。

3）使用WHERE子句時GROUP BY的執行結果

使用 WHERE 子句進行匯總處理時，會先根據 WHERE 子句指 定的條件進行過濾，然後再進行匯總處理。

4）與聚合函數和GROUP BY子句有關的常見錯誤

常見錯誤① ——在SELECT子句中書寫了多餘的列

常見錯誤② ——在GROUP BY子句中寫了列的別名

SELECT 子句中 的項目可以通過 AS 關鍵字來指定別名。但是，在 GROUP BY 子句中是 不能使用別名的。

常見錯誤③——GROUP BY子句的結果能排序嗎

完全是隨機的

常見錯誤④——在WHERE子句中使用聚合函數

只有 SELECT 子句和 HAVING 子句（以及之後將要學到的 ORDER BY 子句）中能夠使用 COUNT 等聚合函數。

5-3、為聚合結果指定條件

1) HAVING子句

語法：

SELECT <列名1>, <列名2>, <列名3>, ……

FROM <表名>

GROUP BY <列名1>, <列名2>, <列名3>, ……

HAVING <分組結果對應的條件>

2）HAVING子句的構成要素

HAVING 子句中 能夠使用的 3 種要素如下所示。

● 常數

● 聚合函數

● GROUP BY子句中指定的列名（即聚合鍵）

3）相對於HAVING子句， 更適合寫在WHERE子句中的條件

需要分清什麼時候寫WHERE條件或者HAVING條件

WHERE 子句 = 指定行所對應的條件

HAVING 子句 = 指定組所對應的條件

注意：

聚合鍵所對應的條件不應該書寫在HAVING子句當中，而應該書寫在WHERE子句 當中。

5-4、對查詢結果進行排序

1）ORDER BY子句

語法：

SELECT <列名1>, <列名2>, <列名3>, ……

FROM <表名>

ORDER BY <排序基準列1>, <排序基準列2>, …… DESC/ASC

▲子句的書寫順序

1. SELECT 子句 → 2. FROM 子句 → 3. WHERE 子句 → 4. GROUP BY 子句 → 5. HAVING 子句 → 6. ORDER BY 子句

注意：

ORDER BY子句通常寫在SELECT語句的末尾。

降序——DESC 升序——ASC

注意：

未指定ORDER BY子句中排列順序時會默認使用升序進行排列。

2）NULL的順序

排序鍵中包含NULL時，會在開頭或末尾進行匯總。

3）在排序鍵中使用顯示用的別名

▲使用 HAVING 子句時 SELECT 語句的順序

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

所以是允許在排序鍵中使用別名，在SELECT前的都不行

4）ORDER BY子句中可以使用的列

注意：

在ORDER BY子句中可以使用SELECT子句中未使用的列和聚合函數。

5）不要使用列編號

列編號是指 SELECT 子句中的列按照從左到 右的順序進行排列時所對應的編號（1, 2, 3, …）。

兩個語句執行結果是一樣的，但是我們一般不鼓勵使用列編號

6、數據更新

6-1、數據的插入（INSERT語句的使用方法）

1）什麼是INSERT

通過 CREATE TABLE 語句創建出來的表，可以被認為是一個空空如也的箱子。 只有把數據裝入到這個箱子後，它才能稱為資料庫。用來裝入數據的 SQL 就是 INSERT（插入）

2）INSERT語句的基本語法

INSERT INTO <表名> (列1, 列2, 列3, ……)

VALUES (值1, 值2, 值3, ……);

注意：

原則上，執行一次INSERT語句會插入一行數據。

-- 通常的INSERT

INSERT INTO ProductIns VALUES (0002, 打孔器,  辦公用品, 500, 320, 2009-09-11);

INSERT INTO ProductIns VALUES (0003, 運動T恤,  衣服, 4000, 2800, NULL);

INSERT INTO ProductIns VALUES (0004, 菜刀,  廚房用具, 3000, 2800, 2009-09-20);

-- 多行INSERT

INSERT INTO ProductIns VALUES (0002, 打孔器,  辦公用品, 500, 320, 2009-09-11), (0003, 運動T恤,  衣服, 4000, 2800, NULL), (0004, 菜刀,  廚房用具, 3000, 2800, 2009-09-20);

3）列清單的省略

對錶進行全列 INSERT 時，可以省略表名後的列清單。這時 VALUES 子句的值會默認按照從左到右的順序賦給每一列。

4）插入NULL

INSERT 語句中想給某一列賦予 NULL 值時，可以直接在 VALUES 子句的值清單中寫入 NULL。

注意：

想要插入 NULL 的列一定不能設置 NOT NULL 約束。

5）插入默認值

我們還可以向表中插入默認值（初始值）。可以通過在創建表的 CREATE TABLE 語句中設置DEFAULT 約束來設定默認值。

注意：

省略INSERT語句中的列名，就會自動設定為該列的默認值（沒有默認值時會設定 NULL）

6）從其他表中複製數據語法

INSERT INTO <表名> (列1, 列2, 列3, ……)

SELECT(列1, 列2, 列3, ……) FROM <表名>;

注意：

兩個表的列名數量以及類型必須一一對應

INSERT語句的SELECT語句中，可以使用WHERE子句或者GROUP BY子句等任 何 SQL語法（但使用ORDER BY子句並不會產生任何效果）。

6-2、數據的刪除（DELETE語句的使用方法）

1）DROP TABLE語句和DELETE語句

刪除數據的方法大體可以分為以下兩種。

① ?DROP TABLE 語句可以將表完全刪除

② ?DELETE 語句會留下表（容器），而刪除表中的全部數據

2）DELETE語句的基本語法

DELETE FROM <表名>

注意：DELETE語句的刪除對象並不是表或者列，而是記錄（行）。

3）指定刪除對象的DELETE語句（搜索型DELETE）

語法：

DELETE FROM <表名>

WHERE <條件>

6-3、數據的更新（UPDATE語句的使用方法）

1）UPDATE語句的基本語法

UPDATE <表名>

SET <列名> = <表達式>

2）指定條件的UPDATE語句（搜索型UPDATE）

UPDATE <表名>

SET <列名> = <表達式>

WHERE <條件>

注意：SET 子句中賦值表達式的右 邊不僅可以是單純的值，還可以是包含列的表達式

3）使用NULL進行更新

使用 UPDATE 也可以將列更新為 NULL（該更新俗稱為 NULL 清 空）。此時只需要將賦值表達式右邊的值直接寫為 NULL 即可。

注意：使用UPDATE語句可以將值清空為NULL（但只限於未設置NOT NULL約束的列）。

4）多列更新語法

UPDATE <表名>

SET <列名1，列名2...> = <表達式1，表達式2...>

WHERE <條件>

6-4、事務

1）什麼是事務

事務就是需要在同一個處理單元中執行的一系列更 新處理的集合。

2）創建事務

事務開始語句;

DML語句①;

DML語句②;

DML語句③; . . .

事務結束語句（COMMIT或者ROLLBACK）;

COMMIT——提交處理

COMMIT 是提交事務包含的全部更新處理的結束指令，相當 於文件處理中的覆蓋保存。一旦提交，就無法恢復到事務開始前的狀態了。 因此，在提交之前一定要確認是否真的需要進行這些更新。

　COMMIT的流程=直線進行

ROLLBACK——取消處理

ROLLBACK 是取消事務包含的全部更新處理的結束指令， 相當於文件處理中的放棄保存。一旦回滾，資料庫就會恢復到事務開始之 前的狀態。通常回滾並不會像提交那樣造成大規模的數 據損失。

　ROLLBACK的流程=掉頭回到起點

7、複雜查詢

7-1、視圖

1）視圖和表

視圖：視圖保存的是 SELECT 語句。我們從視圖中讀取數據時，視圖會在內部執行該 SELECT 語句並創建出一張臨時表。但是使用視圖時並不會將數據保存到存儲設備之中，而且也不會將數 據保存到其他任何地方。

注意：表中存儲的是實際數據，而視圖中保存的是從表中取出數據所使用的SELECT語句。

2）創建視圖的方法

語法：CREATE VIEW 視圖名稱(<視圖列名1>, <視圖列名2>, ……) AS SELECT語句

使用視圖的查詢 在 FROM 子句中使用視圖的查詢，通常有如下兩個步驟：

① 首先執行定義視圖的 SELECT 語句

② 根據得到的結果，再執行在 FROM 子句中使用視圖的 SELECT 語句

注意：應該避免在視圖的基礎上創建視圖。

3）視圖的限制① ——定義視圖時不能使用ORDER BY子句

因為視圖和表一樣，數據 行都是沒有順序的。

4）視圖的限制② ——對視圖進行更新

視圖歸根結底還是從表派生出來的，因此，如果原表可以更新，那麼 視圖中的數據也可以更新。反之亦然，如果視圖發生了改變，而原表沒有 進行相應更新的話，就無法保證數據的一致性了。

7-2、子查詢

5）刪除視圖

DROP VIEW 視圖名稱(<視圖列名1>, <視圖列名2>, ……)

7-2、子查詢

1）子查詢和視圖

子查 詢就是將用來定義視圖的SELECT語句直接用於FROM子句當中。

注意：子查詢作為內層查詢會首先執行。

2）標量子查詢

標量子查詢就是返回單一值的子查詢。

能夠使用常數或者列名的 地方，無論是 SELECT 子句、GROUP BY 子句、HAVING 子句，還是 ORDER BY 子句，幾乎所有的地方都可以使用。

使用標量子查詢時的注意事項——該子查詢 絕對不能返回多行結果

8、函數、謂詞、CASE表達式

8-1、各種各樣的函數

1）函數的種類

2）算術函數

ABS——絕對值

ABS(數值)

MOD——求余

MOD(被除數，除數)

ROUND——四捨五入

ROUND(對象數值，保留小數的位數)

3）字元串函數

||——拼接

字元串1||字元串2

LENGTH——字元串長度

LENGTH(字元串)

LOWER——小寫轉換

LOWER(字元串)

REPLACE——字元串的替換

REPLACE(對象字元串，替換前的字元串，替換後的字元串)

SUBSTRING——字元串的截取

SUBSTRING（對象字元串 FROM 截取的起始位置 FOR 截取的字元數）

UPPER——大寫轉換

UPPER(字元串)

3）日期函數

CURRENT_DATE——當前日期

CURRENT_DATE

CURRENT_TIME——當前時間

CURRENT_TIME

CURRENT_TIMESTAMP——當前日期和時間

CURRENT_TIMESTAMP

EXTRACT——截取日期元素

EXTRACT(日期元素 FROM 日期)

4）轉換函數

CAST——類型轉換

CAST（轉換前的值 AS 想要轉換的數據類型）

COALESCE——將NULL轉換為其他值

COALESCE(數據1，數據2，數據3……)

8-2、謂詞

1）什麼是謂詞

是需要滿足特定條 件的函數，該條件就是返回值是真值。對通常的函數來說，返回值有可能 是數字、字元串或者日期等，但是謂詞的返回值全都是真值（TRUE/ FALSE/UNKNOWN）。這也是謂詞和函數的最大區別。

2）LIKE謂詞——字元串的部分一致查詢

當需要進行字元串的部分一致查詢時需要使用該LIKE 謂詞

注意：其中的 % 是代表「0 字元以上的任意字元串」的特殊符號

3）BETWEEN謂詞——範圍查詢

語法： BETWEEN A and B

查詢A與B範圍之間的數據（包含臨界值）

4）IS NULL、IS NOT NULL——判斷是否為NULL

為了選取出某些值為 NULL 的列的數據，不能使用 =，而只能使用特 定的謂詞 IS NULL

5）IN謂詞——OR的簡便用法

等同於多個OR語句串聯

6）EXIST謂詞

作用就是「判斷是否存在滿足某種條件的記錄」。 如果存在這樣的記錄就返回真（TRUE），如果不存在就返回假（FALSE）。 EXIST（存在）謂詞的主語是「記錄」。

注意：

通常指定關聯子查詢作為EXIST的參數。

作為EXIST參數的子查詢中經常會使用SELECT *。

8-3、CASE表達式

1）什麼是CASE表達式

CASE 表達式是在區分情況時使用的，這種情況的區分在編程中通常 稱為（條件）分支 3

2）CASE表達式的語法

CASE WHEN <求值表達式> THEN <表達式>

WHEN <求值表達式> THEN <表達式>

WHEN <求值表達式> THEN <表達式> . . .

ELSE <表達式>

END

WHEN 子句中的「< 求值表達式 >」就是類似「列 = 值」這樣，返回 值為真值（TRUE/FALSE/UNKNOWN）的表達式。我們也可以將其看作 使用 =、!= 或者 LIKE、BETWEEN 等謂詞編寫出來的表達式。

注意：CASE表達式中的END不能省略。

9、集合運算

9-1、表的加減法

1)什麼是集合運算

通過集 合運算，可以得到兩張表中記錄的集合或者公共記錄的集合，又或者其中 某張表中的記錄的集合。像這樣用來進行集合運算的運算符稱為集合運 算符。

2)表的加法——UNION

使用UNION對錶進行加法運算

注意：集合運算符會除去重複的記錄。

2）集合運算的注意事項

注意事項①——作為運算對象的記錄的列數必須相同

注意事項②——作為運算對象的記錄中列的類型必須一致

注意事項③——可以使用任何SELECT語句，但ORDER BY子句只 能在最後使用一次

3）包含重複行的集合運算——ALL選項

注意：在集合運算符中使用ALL選項，可以保留重複行。

4）選取表中公共部分——INTERSECT

5）記錄的減法——EXCEPT

9-2、聯結（以列為單位對錶進行聯結）

1）內聯結——INNER JOIN

所謂聯結運算，一言以蔽之，就是「以A中的列作為橋樑，將 B中滿 足同樣條件的列彙集到同一結果之中」

●內聯結要點①——FROM子句

FROM 表名1 INNER JOIN 表名2

●內聯結要點②——ON子句

ON 表名1.列名1 = 表名2.列名2

●內聯結要點③ ——SELECT子句

使用聯結時SELECT子句中的列需要按照「<表的別名>.<列名>」的格式進行書寫。

內聯結和WHERE子句結合使用

根據WHERE語句篩選條件之後再選擇內聯結

注意：進行內聯結時必須使用ON子句，並且要書寫在FROM和WHERE之間。

2）外聯結——OUTER JOIN

●外聯結要點①——選取出單張表中全部的信息

外聯結名稱的由來也跟 NULL 有關，即「結果中包含原表 中不存在（在原表之外）的信息」。

●外聯結要點②——每張表都是主表嗎？

指定主表的關鍵字是 LEFT（左表為主表） 和 RIGHT（右表為主表）。

3）3張以上的表的聯結

10、SQL高級處理

10-1、窗口函數

1）什麼是窗口函數

是對資料庫數據 進行實時分析處理。例如，市場分析、創建財務報表、創建計劃等日常性商務工作。

窗口函數就是將表以窗口為單位進行分割，並在其中進行排序的函數。

2）窗口函數的語法

<窗口函數> OVER ([PARTITION BY <列清單>]

ORDER BY <排序用列清單>)

其中重要的關鍵字是 PARTITION BY 和 ORDER BY,

PARTITION BY 能夠設定排序的對象範圍

ORDER BY 能夠指定按照哪一列、何種順序進行排序。

能夠作為窗口函數使用的函數

窗口函數大體可以分為以下兩種。

① 能夠作為窗口函數的聚合函數（SUM、AVG、COUNT、MAX、MIN）

② RANK、DENSE_RANK、ROW_NUMBER 等專用窗口函數

注意：窗口函數兼具之前我們學過的 GROUP BY 子句的分組功能以及 ORDER BY 子句的排序功能。但是，PARTITION BY 子句並不具備 GROUP BY 子句的匯總功能。因此，使用 RANK 函數並不會減少原表中 記錄的行數

3)語法的基本使用方法——使用RANK函數

RANK 是用來計算記錄排序的函數。

對product_type這一列進行分組，在每個組裡面分別排序

注意：通過PARTITION BY分組後的記錄集合稱為「窗口」。

4）無需指定PARTITION BY

不使用PARTITION BY相當於不用分組，是對整個查詢的數據進行排序

5）專用窗口函數的種類

6）窗口函數的適用範圍

注意：原則上窗口函數只能在SELECT子句中使用

7）作為窗口函數使用的聚合函數

從上往下依次疊加

8）計算移動平均

●指定框架（匯總範圍）

我們使用了 ROWS（「行」）和 PRECEDING（「之前」）兩個關鍵 字，將框架指定為「截止到之前 ~ 行」截止到之前 2 行

使用關鍵字 FOLLOWING（「之後」）替換 PRECEDING，就可以指 定「截止到之後 ~ 行」作為框架了

●將當前記錄的前後行作為匯總對象

同時使用 PRECEDING（「之前」）和 FOLLOWING（「之後」）關 鍵字

9）兩個ORDER BY

注意：將聚合函數作為窗口函數使用時，會以當前記錄為基準來決定匯總對象的記錄。

10-2、GROUPING運算符

1）ROLLUP——同時得出合計和小計

GROUPING 運算符包含以下 3 種 。

●ROLLUP ●CUBE ●GROUPING SETS

ROLLUP的使用方法

計算 出了如下兩種組合的匯總結果。

① GROUP BY () ——超級分組，類型為空的，總價格

② GROUP BY (product_type) ——根據類型進行各個匯總

①中的 GROUP BY () 表示沒有聚合鍵，也就相當於沒有 GROUP BY 子句（這時會得到全部數據的合計行的記錄），該合計行記錄稱為超級分組 記錄

2）將「登記日期」添加到聚合鍵當中

計算 出了如下三種組合的匯總結果

① GROUP BY ()

② GROUP BY (product_type)

③ GROUP BY (product_type, regist_date)

3）GROUPING函數——讓NULL更加容易分辨

SQL 提供了一個用來判斷超級分組記錄的 NULL 的 特定函數 —— GROUPING 函數。該函數在其參數列的值為超級分組記錄 所產生的 NULL 時返回 1，其他情況返回 0

4）CUBE——用數據來搭積木

匯總的結果

① GROUP BY ()

② GROUP BY (product_type)

③ GROUP BY (regist_date) ←添加的組合

④ GROUP BY (product_type, regist_date)

5）GROUPING SETS——取得期望的積木

該運算符可以用於從 ROLLUP 或者 CUBE 的結果中取出部分記錄

SQL學習之旅到此結束，非常感謝您的學習，祝福你，加油~