數據分析必要技能——踏進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學習之旅到此結束,非常感謝您的學習,祝福你,加油~
推薦閱讀:
※給elasticsearch 5.X 安裝sql插件 並修復
※將excel裡面的數據直接生成sql語句
※SQL學生選課場景練習
※當計算欄位邂逅子查詢
※文檔模型(JSON)使用介紹