23篇大數(shù)據(jù)系列（三）sql基礎知識（史上最全，建議收藏）（sql數(shù)據(jù)庫基礎知識筆記）

我的使命與愿景：持續(xù)穩(wěn)定輸出，賦能中國技術(shù)社區(qū)蓬勃發(fā)展！

大數(shù)據(jù)系列文章，從技術(shù)能力、業(yè)務基礎、分析思維三大板塊來呈現(xiàn)，你將收獲：

??提升自信心，自如應對面試，順利拿到實習崗位或offer；

??掌握大數(shù)據(jù)的基礎知識，與其他同事溝通無障礙；

??具備一定的項目實戰(zhàn)能力，對于大數(shù)據(jù)工作直接上手；

【評論、、】是對我最大的支持！

大數(shù)據(jù)工程師知識體系：

大數(shù)據(jù)工程師的工作內(nèi)容是什么？

而大數(shù)據(jù)時代，有一個關(guān)鍵性的崗位不得不提，那就是大數(shù)據(jù)工程師。想必大家也會好奇，大數(shù)據(jù)工程師，日常是做什么的呢？

基于用戶的各方面數(shù)據(jù)，建立對用戶的全方位理解，構(gòu)建每個特定用戶的畫像，以便針對每個個體完成精細化運營。

大數(shù)據(jù)工程師必備技能

那么，問題來了，如果想成為一名大數(shù)據(jù)工程師，勝任上述工作內(nèi)容，需要具備什么樣的條件？擁有什么樣的知識呢？

分類

子分類

技能

描述

技

術(shù)

能

力

編程基礎

Java基礎

大數(shù)據(jù)生態(tài)必備的java基礎

Scala基礎

Spark相關(guān)生態(tài)的必備技能

SQL基礎

數(shù)據(jù)分析師的通用語言

SQL進階

完成復雜分析的必備技能

大數(shù)據(jù)框架

HDFS&YARN

大數(shù)據(jù)生態(tài)的底層基石

Hive基礎

大數(shù)據(jù)分析的常用工具

Hive進階

大數(shù)據(jù)分析師的高級裝備

Spark基礎

排查問題必備的底層運行原理

Spark SQL

應對復雜任務的利刃

工具

Hue&Zeppelin

通用的探索分析工具

Azkaban

作業(yè)管理調(diào)度平臺

Tableau

數(shù)據(jù)可視化平臺

業(yè)務基礎

數(shù)據(jù)收集

數(shù)據(jù)是如何收集到的？

ETL工程

怎么清洗、處理和轉(zhuǎn)化數(shù)據(jù)？

數(shù)據(jù)倉庫基礎

如何完成面向分析的數(shù)據(jù)建模？

元數(shù)據(jù)中心

如何做好數(shù)據(jù)治理？

分析思維

數(shù)據(jù)分析思維方法論

怎么去分析一個具體問題？

排查問題思維

如何高效排查數(shù)據(jù)問題？

指標體系

怎么讓數(shù)據(jù)成體系化？

由于介紹的是基礎知識，因此本文的主要內(nèi)容是講解面向數(shù)據(jù)分析的SQL基礎知識。本文會從4個方面來講解：

第1部分，數(shù)據(jù)庫體系的一些基本概念。我們會提到數(shù)據(jù)庫相關(guān)的一些重要概念，方便大家理解SQL的寫法，更重要的是理解這些概念是與他人溝通SQL相關(guān)內(nèi)容的前提。

第2部分，SQL查詢的基本語法。我們會來講解下SELECT子句的使用方法，如何完成一些針對單表的簡單的統(tǒng)計分析。

第3部分，數(shù)據(jù)庫函數(shù)、謂詞和CASE表達式。我們會來介紹下常用的一些函數(shù)、用于判斷真假的謂詞和用于多條件判斷的CASE表達式。

第4部分，關(guān)聯(lián)查詢和子查詢。我們將會帶大家學習下，如何把多張表連接起來，通過表交叉來獲取更多的信息，以及使用子查詢實現(xiàn)在查詢的結(jié)果上繼續(xù)分析。

由于不同數(shù)據(jù)庫廠商，引擎實現(xiàn)各有不同，SQL的語法、關(guān)鍵字、函數(shù)等都略有差異，因此本文只拿在互聯(lián)網(wǎng)公司使用最廣泛的MySQL為例進行講解，文中涉及的SQL和例子都是在MySQL中運行的。下面我們就進入正式的知識講解。

1? 數(shù)據(jù)庫體系的一些基本概念

本部分的核心目標是讓大家理解一些核心的基本概念，這些概念在日常工作中經(jīng)常會提到和用到，因此理解了這些概念才能和團隊其他小伙伴順暢地溝通，愉快地協(xié)作。由于本文的重點是講SQL，因此我們只講解關(guān)系型數(shù)據(jù)庫相關(guān)的概念。

1.1? 數(shù)據(jù)庫與數(shù)據(jù)庫管理系統(tǒng)

我們通常口中所說的數(shù)據(jù)庫，有兩種含義，一是指實際存儲數(shù)據(jù)的倉庫，二是指抽象層面上容納一組表的那個database。其中，表達后一種含義更常見。數(shù)據(jù)庫管理系統(tǒng)，通常是指管理數(shù)據(jù)庫的一套系統(tǒng)，通過它可以實現(xiàn)對數(shù)據(jù)的定義、插入、更新、刪除、查詢等操作，它提供了數(shù)據(jù)分析師與數(shù)據(jù)交互的窗口。

1.2? 表

關(guān)系型數(shù)據(jù)庫中的表，通常是指由行和列組成的用于存儲數(shù)據(jù)的二維表。表是數(shù)據(jù)存儲的直接載體，我們的數(shù)據(jù)通常都需要存儲在表中。數(shù)據(jù)庫基本上都是通過表來組織數(shù)據(jù)的。所以，表也是我們查詢并獲取數(shù)據(jù)最直接的對象。

對于表而言，有以下幾個特性：

a. 表是由存在關(guān)聯(lián)性的多列組成的，可以存儲N多行數(shù)據(jù)，每行數(shù)據(jù)稱為一條記錄，行和列的交叉點唯一確定一個單元格

b. 表中的列名不重復，即列名需唯一

c. 表中的任意一列都只能存儲一種數(shù)據(jù)類型的數(shù)據(jù)

1.3? 數(shù)據(jù)類型

在不同的數(shù)據(jù)庫管理系統(tǒng)中，支持的數(shù)據(jù)類型會略有差異，本文就以MySQL為例，介紹幾種最常用的數(shù)據(jù)類型，分別如下所示：

數(shù)值類型

類型（有符號）

大小

范圍

描述

TINYINT

1字節(jié)

(-128 ~ 127)

小整型，通常用于存儲一些整型枚舉值

INT

4字節(jié)

(-2,147,483,648 ~ 2,147,483,647)

整型，使用頻率較高

BIGINT

8字節(jié)

(-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807)

長整型，通常存儲比較大的數(shù)值

FLOAT(n,d)

4字節(jié)

與n和d的值有關(guān)

單精度浮點數(shù)（不精準表達）

DOUBLE(n,d)

8字節(jié)

與n和d的值有關(guān)

雙精度浮點數(shù)（不精準表達）

DECIMAL(p,d)

與p和d

的值有關(guān)

與p和d的值有關(guān)

定點數(shù)（精準表達）

日期類型

類型

示例

描述

DATE

2019-05-01

日期

TIME

12:23:34

時間

DATETIME

2019-05-01 12:23:34

日期時間

字符串類型

類型

范圍

描述

CHAR(n)

n <= 255

定長字符串

VARCHAR(n)

n <= 65535

變長字符串

TEXT

0 - 65535字節(jié)

長文本數(shù)據(jù)

MEDIUMTEXT

0 - 16,777,215字節(jié)

中等長度文本數(shù)據(jù)

上面只是羅列出了幾種最常用的數(shù)據(jù)類型，如果大家接觸到了其他不常用的類型，可以自己在網(wǎng)上搜索一下相關(guān)的信息。

1.4? 主鍵

主鍵是一列或多列的組合，用于標識表中唯一的一條記錄。所以，它天然的一個屬性就是不重復性，也不允許為NULL值。通常我們會使用自增的整型值來作為主鍵，由數(shù)據(jù)庫管理系統(tǒng)來維護，既能保證唯一性，又使用起來很方便。一個表的主鍵，通常也會作為其他表引用的對象，即后面要講到的外鍵。

1.5? 外鍵

外鍵通常用來建立兩張表之間的關(guān)聯(lián)關(guān)系，一個表的外鍵通常是與之關(guān)聯(lián)的另一個表的主鍵。這樣在進行關(guān)聯(lián)查詢時，就可以通過兩個表外鍵和主鍵之間的關(guān)系，將兩張表連接起來，形成一張中間表，將兩張表的信息融合，產(chǎn)生更大的價值。

1.6? 索引

如果你想快速找到一本書中，你感興趣的部分，你就會去查找目錄，目錄可以幫你快速定位到你想看的內(nèi)容在哪一頁。對于數(shù)據(jù)庫中的表來說，索引就相當于是表的目錄。其存在的主要目的就是為了加快查詢速度。當然，索引也還有一些其他用途，其設計原理也是非常巧妙，我們會在下一篇SQL進階文章中，詳細講解這塊內(nèi)容。

1.7? 表關(guān)系

在關(guān)系型數(shù)據(jù)庫中，表和表之間的關(guān)系通常有三種，1對1、1對多、多對多。為方便描述，我們假定有兩張表，分別為表A和表B。

1對1，是指表A和表B通過某字段關(guān)聯(lián)后，表A中的一條記錄最多對應表B中的一條記錄，表B中的一條記錄也最多對應表A中的一條記錄。

1對多，是指表A和表B通過某字段關(guān)聯(lián)后，表A中的一條記錄可能對應表B中的多條記錄，而表B中的一條記錄最多對應表A中的一條記錄。

多對多，是指表A和表B通過某字段關(guān)聯(lián)后，表A中的一條記錄可能對應表B中的多條記錄，而表B中的一條記錄可能對應表A中的多條記錄。

1對1 和 1對多關(guān)系，通常使用外鍵引用對應表的主鍵就可以表達。而多對多關(guān)系，通常需要使用中間表來表達，中間表中記錄了兩張表的主鍵的對應關(guān)系。

1.8? 視圖

如果一條SQL的結(jié)果在日常查詢中經(jīng)常被用到，我們通常就會考慮使用視圖將其存儲起來，下次再使用時直接讀取視圖，就會執(zhí)行視圖對應的SQL語句，非常地簡潔方便。所以，視圖就是一張?zhí)摂M的表。不過，值得注意的是，視圖存儲的是SQL語句，而不是SQL執(zhí)行后的結(jié)果，其結(jié)果是每次執(zhí)行時動態(tài)生成的，可能每次讀取都會有變化。

1.9? 集合

集合在數(shù)學領域表示“(各種各樣的)事物的總和”，在數(shù)據(jù)庫領域表示記錄的集合。具體來說，表、視圖和查詢的執(zhí)行結(jié)果都是記錄的集合。是集合，就可以進行集合運算，如求并集、交集、差集等。另外，查詢的執(zhí)行結(jié)果也是集合，那么就可以把查詢的結(jié)果再當做一個表，繼續(xù)基于這個表做分析。這個便是子查詢的理論基礎。在第4部分，我們會詳細講到子查詢。

2? SQL查詢的基本語法

SQL是結(jié)構(gòu)化查詢語言(Structural Query Language)的簡稱，是開發(fā)者與數(shù)據(jù)庫管理系統(tǒng)對話的語言。SQL用關(guān)鍵字、表名、列名、操作符等組合而成的一條語句，用來描述操作的內(nèi)容。SQL是有國際標準的，因此其通用性不言而喻。

2.1? 關(guān)鍵字

SQL有很多關(guān)鍵字，每個關(guān)鍵字的含義和用法都不相同。本文只羅列出在數(shù)據(jù)分析工作中與查詢分析相關(guān)的常用的基礎關(guān)鍵字及其含義，其中有一些會在下面的段落中詳細講解，如下表格所示：

關(guān)鍵字

描述

SELECT

后面跟用戶想獲取的列或計算公式

FROM

后面跟要讀取數(shù)據(jù)的表

LEFT/RIGHT/INNER JOIN

后面跟要進行關(guān)聯(lián)的表

ON

后面跟關(guān)聯(lián)條件

WHERE

后面跟過濾條件，只有滿足條件的行才會保留下來

GROUP BY

后面跟用來分組的列或計算公式

HAVING

后面跟分組后的過濾條件

ORDER BY

后面跟用于排序的列或計算公式

LIMIT

從結(jié)果中選取前N行，后面跟具體行數(shù)

DISTINCT

對后面跟的列進行去重

COUNT

對指定的一列或多列計數(shù)，會忽略掉NULL值

SUM

對指定的列求和，會忽略掉NULL值

AVG

對指定的列求平均值，會忽略掉NULL值

MIN

求指定列的最小值

MAX

求指定列的最大值

ASC/DESC

ASC表示升序排列，DESC表示降序排列，與ORDER BY配合使用

[NOT] IN

多條件搜索

[NOT] LIKE

模糊匹配

REGEXP

正則匹配

AND/OR/NOT

邏輯判斷符

[NOT] BETWEEN AND

區(qū)間限定

[NOT] EXISTS

判斷集合是否為空

IS [NOT] NULL

判斷是否為NULL值

UNION/UNION ALL

求兩個集合的并集，UNION會剔除結(jié)果集中的重復記錄，UNION ALL則會保留重復記錄

AS

取別名或用于使用查詢結(jié)果集創(chuàng)建表

*

單獨出現(xiàn)或出現(xiàn)在"."后面表示表中的所有列，出現(xiàn)在兩個字段間表示乘法

2.2? 書寫規(guī)則

SQL的書寫規(guī)則非常簡單靈活，但是如果不注意，也是很容易犯錯的，工作中常用的規(guī)則如下：

a. 關(guān)鍵字、表名和列名等大小寫不敏感；

b. 使用全英文半角（關(guān)鍵字、空格、符號）來書寫；

c. SQL語句以分號結(jié)尾；

d. SQL語句的單詞及運算符之間需使用半角空格或換行符來進行分隔；

e. 函數(shù)名和括號是一個整體，中間不能有空格，空參數(shù)函數(shù)括號不能省略;

f. 數(shù)字常量直接書寫，如 20 ;

g. 日期和字符串常量需要使用英文單引號包裹起來，如 '2002-10-01 12:23:21'，'Lily'；

h. 注釋的三種寫法：單行注釋（#，--）和多行注釋（/* */）。單行注釋推薦使用"--"。

2.3? 簡單查詢

最簡單的查詢語句莫過于"SELECT * FROM A"，其中A表示數(shù)據(jù)表名A，這條

SQL的含義是從表A中查詢出所有列的所有數(shù)據(jù)。"*"代表表A中的所有列，是一種簡寫形式。我們就從這條最簡單的SQL開始，逐漸添加關(guān)鍵字，最后變成一條復雜的SQL。

接下來我們要講解的簡單查詢，都是針對單個表的查詢。針對單表的查詢雖然比較簡單，但是卻是復雜查詢的基礎。為了方便演示，我們先定義一個數(shù)據(jù)表student，用于存儲學生的信息，表里的數(shù)據(jù)如下所示：

從左到右列的含義依次為學號、姓名、年齡、英語成績、數(shù)學成績、總成績。

2.4? 過濾

如果要對表中的數(shù)據(jù)進行過濾，只保留滿足我們需求的數(shù)據(jù)，那就要用到WHERE關(guān)鍵字了。WHERE關(guān)鍵字后跟的是由邏輯運算符連接的一個或多個表達式，每個表達式的最終結(jié)果為TRUE或FALSE，只保留表達式結(jié)果為TRUE的行。

例如，我們要獲取英文成績不合格的學生姓名和學號，則對應的SQL為

SELECT sno, name FROM student WHERE eng_score < 60運行結(jié)果如下：

sno

name

22270202

Lily

22270203

Tom

2.5? 運算符

運算符，顧名思義就是用于做運算的符號。常見的運算符有三種，比較運算符、算術(shù)運算符和邏輯運算符。

比較運算符

含義

=

等值比較

>

大于

<

小于

>=

大于或等于

<=

小于或等于

<>

不等于

不等于的判斷，目前絕大部分的數(shù)據(jù)庫管理系統(tǒng)廠商也都支持了"!="運算符，與"<>"表達的含義相同。

算術(shù)運算符

含義

+

加法運算

-

減法運算

*

乘法運算

/

除法運算

算術(shù)運算符在書寫時可以緊挨著字段名寫，如eng_score-math_score，所以字段名和表名的命名中不能使用中劃線("-")，否則它會被誤判為是在做減法運算的。

邏輯運算符

含義

AND

與，并且

OR

或，或者

NOT

非，取反

當存在多種邏輯運算符時，為了避免歧義，需要使用括號來界定執(zhí)行的先后順序，使用括號組織的表達式，可讀性也會更強。建議大家不要去記憶邏輯運算符的優(yōu)先級，容易記混，而且寫出的SQL可讀性比較差，最好是使用括號，來厘清多個邏輯條件的關(guān)系，清晰易懂，可讀性強，不容易出錯。

了解了上面這些運算符，我們便可以通過組合各種運算符，書寫出WHERE后面復雜的表達式，來滿足我們的過濾需求了。

2.6? 分組聚合

分組聚合是指，我們可以將表中的數(shù)據(jù)，根據(jù)某一列或多列進行分組，然后將其他列的值進行聚合計算，如計數(shù)、求和和求平均值等。用到的關(guān)鍵字是GROUP BY，對于分組后的計算結(jié)果，我們還可以使用HAVING進行過濾。

例如，從student表中，求出不同年齡的人數(shù)、英語總成績和數(shù)學成績的平均值，且過濾掉。對應的SQL為

SELECT age, COUNT(sno) AS student_num, SUM(eng_score) AS sum_eng_score, AVG(math_score) AS avg_math_scoreFROM studentGROUP BY ageHAVING avg_math_score >= 60

運行后結(jié)果如下所示：

age

student_num

sum_eng_score

avg_math_score

10

2

138

70.5

12

1

89

82

這里需要注意的是，出現(xiàn)在group by后面的字段或計算公式，必須出現(xiàn)在對應的select的后面，并且除了這些字段或計算公式外，select后面不能有其他字段，只能使用聚合函數(shù)。

2.7? 去重

DISTINCT關(guān)鍵字用于對一列或多列去重，返回剔除了重復行的結(jié)果。DISTINCT對多列去重時，必須滿足每一列都相同時，才認為是重復的行進行剔除。DISTINCT不會過濾掉NULL值，但去重后的結(jié)果只會保留一個NULL值。

例如，從student表中，找出有幾種年齡的學生，即求出去重后的年齡。對應的SQL為

SELECT DISTINCT age FROM student

運行后的結(jié)果如下所示：

age

10

11

12

2.8? 排序

日常生活場景里，我們經(jīng)常對各種各樣的排名比較感興趣，比較關(guān)注排在前面的內(nèi)容。在數(shù)據(jù)庫中，求出排名，就需要用到ORDER BY子句。ORDER BY通常配合ASC和DESC使用，可以根據(jù)一列或多列，進行升序或降序排列，之后使用LIMIT取出滿足條件的前N行。

例如，從student表中，求出數(shù)學成績最好的前3名學生的姓名、年齡和其數(shù)學成績。對應的SQL如下：

SELECT name, age, math_scoreFROM studentORDER BY math_score DESCLIMIT 3

運行后的結(jié)果為：

name

age

math_score

Jack

12

82

Alice

10

76

Tom

10

65

2.9? 增加常量列

增加常量列，即把某一固定的常量值做為一列添加到我們的結(jié)果數(shù)據(jù)中。這種做法的應用場景，通常是結(jié)果集中所有的行在某個屬性上值是相同的，這時便可以通過增加常量列的方式，來增加這一列。我們通過下面的例子來演示其語法形式。

例如，從student表中，查詢英語成績大于80分的學生的姓名和學號，并把他們都分入A班。對應的SQL如下：

SELECT sno, name, 'A' AS class FROM student WHERE eng_score > 80

運行后的結(jié)果為：

sno

name

class

22270201

Alice

A

22270204

Jack

A

從示例中可以看出，直接通過"常量 AS 新列名"的方式就可以增加常量列，非常地方便。

3? 數(shù)據(jù)庫函數(shù)、謂詞和CASE表達式

SQL之所以具有強大的分析表達能力，其中一個重要原因，就是它具備豐富的函數(shù)，通過這些函數(shù)的組合可以實現(xiàn)對數(shù)據(jù)的復雜處理，最終得到我們想要的數(shù)據(jù)。另外一方面，SQL也有豐富的謂詞來對數(shù)據(jù)進行判斷，匹配出符合我們需求的數(shù)據(jù)。CASE表達式是一種多條件判斷表達式，可以根據(jù)不同條件返回不同的值，類似于編程語言中的IF ELSE。

3.1? 聚合函數(shù)

聚合函數(shù)，又稱分析函數(shù)，是將一組值通過聚合分析后得到一個值，因此得名聚合函數(shù)。使用頻率最高的聚合函數(shù)有5個，如下表所示

函數(shù)名

含義

COUNT

計數(shù)

SUM

求和

AVG

求平均值

MIN

求最小值

MAX

求最大值

聚合函數(shù)有一個共同的特點，即在計算過程中都會忽略掉NULL值，因為對NULL的聚合是沒有任何意義的。COUNT、SUM和AVG三個函數(shù)還可以和DISTINCT配合使用，其含義為先對目標列進行去重，之后再對去重后的結(jié)果聚合。SUM和AVG只能應用于一列，且列的數(shù)據(jù)類型為數(shù)值型。MIN和MAX也是只能應用于一列，不過除了支持數(shù)值型外，還支持字符串類型和日期類型。COUNT可以應用于一列或多列，而且不限制列的類型。

3.2? 算術(shù)函數(shù)

算術(shù)函數(shù)，主要用于對數(shù)值類型進行各種數(shù)學運算。SQL中除了加減乘除（+-*/）四個運算符外，還提供了一系列的算術(shù)函數(shù)，如下表所示：

函數(shù)名

含義

CEIL(x)

向上取整

FLOOR(x)

向下取整

ABS(x)

求絕對值

ROUND(x, d)

四舍五入，對x保留d位小數(shù)

POWER(x, y)

冪運算，求x的y次方

MOD(x, y)

取余數(shù)，求x被y整除后的余數(shù)

RAND([n])

返回0-1.0的隨機數(shù)，n為隨機種子，可以省略不寫

這里只羅列了常用的一些函數(shù)，通過他們之間的組合，可以實現(xiàn)復雜的運算，如果上述表格不滿足你的分析需求，可以自行Google或查看官方文檔，尋找匹配的算術(shù)函數(shù)。

3.3? 日期函數(shù)

日常分析工作中，經(jīng)常需要對日期進行加減、格式化等處理，這就離不開強大的日期處理函數(shù)，常用的日期函數(shù)如下：

函數(shù)名

含義

CURDATE()

返回當前日期

CURRENT_DATE()

返回當前日期，和上面的函數(shù)作用相同

CURRENT_TIME()

返回當前時間

NOW()

返回當前的日期和時間

DATE_ADD(d, interval n unit)

返回日期d加上n個單位后的時間，unit為具體單位，如day，表示天

DATE_SUB(d, interval n unit)

返回日期d減去n個單位后的時間，unit為具體單位，如day，表示天

DATE_DIFF(d1, d2)

返回日期d1和日期d2的天數(shù)差

DATE_FORMAT(d, 'format_exp')

返回使用日期格式表達式format_exp格式化日期d后得到的字符串

YEAR(d)

返回日期d的年份

MONTH(d）

返回日期d的月份

DATE(d)

返回日期時間d的日期部分，舍棄時間部分

日期函數(shù)用于獲取當前日期時間的函數(shù)多數(shù)是空參數(shù)函數(shù)，雖然參數(shù)為空，但是函數(shù)名后的括號不能省略不寫。數(shù)據(jù)庫廠商雖然也提供了部分與函數(shù)名相同的屬性值，不帶括號也能調(diào)用，不過筆者建議最好還是使用函數(shù)帶上空括號，這樣識別度更高，可讀性更好。

3.4? 字符串函數(shù)

字符串是信息的一個重要載體，其中包含著大量的重要信息，因此對字符串的處理非常重要，相應地字符串處理函數(shù)也是非常豐富，以下我們羅列出最常用的一些函數(shù)：

函數(shù)

含義

使用示例

返回值

LENGTH(str)

求字符串str的長度

LENGTH('bigdata')

7

INSTR(str, substr)

返回substr在str第一次出現(xiàn)的位置(str不包含substr時返回0)

INSTR('bigdata', 'data')

4

LEFT(str, len)

返回str的左端len個字符

LEFT('bigdata',3)

'big'

RIGHT(str, len)

返回str的右端len個字符

RIGHT('bigdata',4)

'data'

SUBSTRING(str, pos, len)

返回str的從位置pos起len個字符

SUBSTRING('bigdata',4,4)

'data'

SUBSTRING_INDEX(str, delim, count)

當count為正數(shù)時，從左找到第count個分隔符delim所在位置，并返回其左側(cè)的字符；否則從右開始找，并返回對應位置右側(cè)的字符

SUBSTRING_INDEX('180.97.33.108', '.', 3)

'180.97.33'

REPLACE(str, from_str, to_str)

返回用to_str替換str中的from_str后的字符串

REPLACE('bigdata', 'big', 'Big')

'Bigdata'

LOWER(str)

返回str轉(zhuǎn)小寫后的字符串

LOWER('Bigdata')

'bigdata'

UPPER(str)

返回str轉(zhuǎn)大寫后的字符串

UPPER('Bigdata')

'BIGDATA'

CONCAT(str1, str2,...)

將參數(shù)連接起來并返回

CONCAT('big', 'data')

'bigdata'

CONCAT_WS(delim, str1, str2,...)

將參數(shù)使用分隔符delim連接起來并返回

CONCAT_WS('_', 'big', 'data')

'big_data'

3.5? 轉(zhuǎn)換函數(shù)

當某些數(shù)據(jù)的類型與我們需要的類型不符時，可以使用類型轉(zhuǎn)換函數(shù)，將其類型轉(zhuǎn)換為我們需要的類型。常用的類型轉(zhuǎn)換函數(shù)有兩個，分別為CAST和CONVERT，兩個函數(shù)的作用是相同的，只是語法略有不同。CAST函數(shù)的用法為CAST(字段 AS 數(shù)據(jù)類型)，而CONVERT的用法為CONVERT(字段, 數(shù)據(jù)類型)。

不過，并不是所有的類型都是可以互相轉(zhuǎn)換的，而且有些轉(zhuǎn)換會導致精度的損失，因此請謹慎使用。

3.6? 其他函數(shù)

還有一些函數(shù)是使用在特定用途上的，本文也羅列出幾個數(shù)據(jù)分析工作中常用的。

MD5函數(shù)，其作用是生成等長的信息摘要。在數(shù)據(jù)分析工作中，經(jīng)常用于對敏感信息的脫敏，因為很難通過md5值反向推斷加密前的內(nèi)容，因此是非常安全的。其使用方法為，MD5(str)，返回對str進行md5算法計算得到的校驗和字符串。

IFNULL(expr1, expr2)：如果expr1不為NULL，則返回expr1，否則返回expr2。通常用于對某個字段的NULL值填補，也叫缺失值填補。

IF(expr1, expr2, expr3)：如果expr1不等于0或者不為NULL，則返回expr2的值，否則返回expr3的值。相當于編程語言中的IF ELSE條件判斷語句

3.7? 謂詞

簡單來說，謂詞就是用于真假判斷的關(guān)鍵字，用來判定兩個對象間關(guān)系論斷的真假，返回值只有真或假。這么說可能還是有點抽象。我們來舉一些謂詞的例子大家就明白了。

例如，我們前面講到的比較運算符，就都屬于謂詞的范疇。還有一些其他謂詞如下表所示：

謂詞

含義

[NOT] LIKE

模糊匹配，通常配合%和_使用

[NOT] IN

多值包含關(guān)系判斷

[NOT] BETWEEN ... AND ...

區(qū)間判斷

IS [NOT] NULL

是否為NULL值判斷

[NOT] EXISTS

是否為空集合判斷

[NOT] REGEXP

是否滿足正則表達式判斷

3.8? CASE表達式

SQL語句中的CASE表達式，對應著編程語言中的條件分支，起到多條件判斷返回多種值的作用。其語法形式為：

CASE

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

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

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

......

ELSE <表達式> END

其執(zhí)行過程為，按照書寫順序，依次判斷WHEN后面求值表達式返回的值為真或假，如果返回值為假，則繼續(xù)向下搜索；如果返回值為真時，執(zhí)行THEN后面對應的表達式，將執(zhí)行后的值返回，CASE表達式退出；如果所有WHEN子句都不滿足時，則執(zhí)行ELSE后面的表達式，返回執(zhí)行后得到的值，CASE表達式退出。

了解了執(zhí)行過程，那么在書寫CASE表達式時，就一定要注意順序問題。這里需要注意一點的是，如果執(zhí)行到第二個THEN的時候，實際生效的條件為<求值表達式1>的值為假，與此同時<求值表達式2>的值為真；如果執(zhí)行到第三個THEN的時候，實際生效的條件為<求值表達式1>和<求值表達式2>的值都為假，與此同時<求值表達式3>的值為真，往后以此類推。

3.9? NULL值判斷

NULL值的判斷必須使用謂詞IS，因為NULL和其他任何值（包括NULL值）比較結(jié)果都為NULL，也就對應著假。這一點很好理解，你可以把NULL值理解為未知。未知和任何值比較結(jié)果還是未知，未知和未知比較，結(jié)果也只能是未知。

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

擁有了前面3部分的知識基礎，那么我們就可以開始學習SQL的復雜查詢。本文要講的復雜查詢有兩個，一個是關(guān)聯(lián)查詢，一個是子查詢。首先，我們先來看下他們的理論基礎，集合運算。

4.1? 集合運算

在第1部分，我們提到過，在數(shù)據(jù)庫領域，集合是指一組記錄的總和，它可以指代表，也可以指代視圖、查詢執(zhí)行的結(jié)果。所以，表和查詢執(zhí)行的結(jié)果都是集合，那么就都可以參與集合運算。也就是說，可以把查詢執(zhí)行的結(jié)果看做是一張中間表或臨時表，繼續(xù)參與運算，這就是子查詢的理論基礎。

集合運算主要包含四種，并集、交集、差集和笛卡爾積。

并集，是求兩個集合合并后的集合。在MySQL中使用關(guān)鍵字UNION或UNION ALL實現(xiàn)，兩者的區(qū)別是，UNION會剔除掉合并后集合中的多余重復值，只保留一份；而UNION ALL，不會剔除重復值。因此，UNION操作，運行結(jié)束后，可能會導致記錄數(shù)的減少。

交集，是求兩個集合都共同擁有的元素的集合。在MySQL中沒有提供專門的關(guān)鍵字，而是通過內(nèi)關(guān)聯(lián)實現(xiàn)的，下一小結(jié)會詳細介紹。

差集，是求在一個集合中存在而在另一個集合中不存在的元素的集合。差集計算具有方向性，同樣的，MySQL也沒有提供差集計算的關(guān)鍵字，而是需要通過左/右關(guān)聯(lián)然后再過濾出未關(guān)聯(lián)成功的記錄而得到。

笛卡爾積，是將兩個集合中記錄兩兩組合，相當于集合的乘法。它是關(guān)聯(lián)查詢的數(shù)學理論基礎。你可以簡單理解為，關(guān)聯(lián)查詢的過程就是，先做笛卡爾積，然后再通過on條件過濾出符合條件的記錄。當然，實際的執(zhí)行過程，不會這么簡單，但是是在這個流程基礎上去做優(yōu)化，減少計算量的。

在進行集合的并集、交集和差集運算時，需要注意的是：

參與運算的兩個集合記錄的列數(shù)必須相同

參與運算的兩個集合對應位置的列的類型必須一致

如果使用ORDER BY子句，必須寫在最后

4.2? 表關(guān)聯(lián)類型

常見的表關(guān)聯(lián)類型有四種，內(nèi)連接(INNER JOIN)、左外連接(LEFT OUTER JOIN)、右外連接(RIGHT OUTER JOIN)、全外連接(FULL OUTER JOIN)。

關(guān)聯(lián)的語法比較簡單，拿內(nèi)連接舉例，書寫為，A INNER JOIN B ON expr。其中，A和B表示兩個表的名稱，也可以是子查詢。ON后面跟的expr表示關(guān)聯(lián)條件，通常是由表A和表B關(guān)聯(lián)字段組成的表達式。

內(nèi)連接(INNER JOIN)，通常可以省略掉INNER不寫，它的含義是左右兩個集合相乘后，只保留滿足ON后面關(guān)聯(lián)條件的記錄。所以，可以利用內(nèi)連接計算兩個集合的交集，只需要把集合元素的字段都寫在ON后面的關(guān)聯(lián)條件里即可。

左外連接(LEFT OUTER JOIN)，OUTER通常可以省略不寫，它的含義是，左右兩個集合相乘后，保留滿足ON后面關(guān)聯(lián)條件的記錄加上左表中原有的但未關(guān)聯(lián)成功的記錄。因此，左外連接，可以用來計算集合的差集，只需要過濾掉關(guān)聯(lián)成功的記錄，留下左表中原有的但未關(guān)聯(lián)成功的記錄，就是我們要的差集。

右外連接(RIGHT OUTER JOIN)，與左外連接含義相同，只是方向不同而已，通常也是省略OUTER不寫。

全外連接(FULL OUTER JOIN)，含義是，左右兩個集合相乘后，保留滿足ON后面關(guān)聯(lián)條件的記錄加上左表和右表中原有的但未關(guān)聯(lián)成功的記錄。

4種JOIN方式的示意圖

4.3? 多表關(guān)聯(lián)

多表關(guān)聯(lián)的本質(zhì)，還是兩兩關(guān)聯(lián)。例如，表A內(nèi)關(guān)聯(lián)表B再內(nèi)關(guān)聯(lián)表C，實際上就可以等價于表A內(nèi)關(guān)聯(lián)表B，運行后的結(jié)果作為一張中間表，然后再與表C內(nèi)關(guān)聯(lián)。所以，執(zhí)行過程仍然是兩兩關(guān)聯(lián)。

4.4? 表關(guān)聯(lián)注意事項

表關(guān)聯(lián)是比較復雜的查詢方式，在書寫時，大家要在腦海中構(gòu)建關(guān)聯(lián)后的集合的樣子，對應去選擇需要使用的連接方法。下面是根據(jù)實際工作經(jīng)驗總結(jié)的容易出錯的點，希望大家注意。

a.?使用UNION可能會導致記錄數(shù)的減少，在使用聚合函數(shù)時，可能會導致計算出現(xiàn)偏差

b.?在使用1對多或多對多關(guān)系的表進行關(guān)聯(lián)時，記錄數(shù)可能會增多，也可能會導致計算出現(xiàn)偏差

c.?左外連接和右外連接都有連接方向的問題，表放的位置對結(jié)果是有影響的，尤其是多表關(guān)聯(lián)時，一定要關(guān)注書寫的順序，盡可能先做內(nèi)連接再做左/右外連接。

d.?盡量避免使用交叉連接

4.5? 子查詢

子查詢，就是指被括號嵌套起來的查詢SQL語句，通常是一條完整的SELECT語句。

子查詢放在不同的位置，起到的作用也是不同的。它經(jīng)常出現(xiàn)在3個位置上，分別是SELECT后面、FROM/JOIN后面，還有WHERE/HAVING后面。

當子查詢出現(xiàn)在SELECT后面時，其作用通常是要為結(jié)果添加一列。不過，這里要注意的是，在SELECT后使用的子查詢語句只能返回單個列，且要保證滿足條件時子查詢語句只會返回單行結(jié)果。企圖檢索多個列或返回多行結(jié)果將引發(fā)錯誤。

子查詢出現(xiàn)在FROM/JOIN后面，是我們最常用的方式，就是將子查詢的結(jié)果作為中間表，繼續(xù)基于這個表做分析。

當子查詢出現(xiàn)在WHERE/HAVING后面時，則表示要使用子查詢返回的結(jié)果做過濾。這里根據(jù)子查詢返回的結(jié)果數(shù)量，分三種情況，即1行1列、N行1列、N行N列。

當返回結(jié)果為1行1列時，實際上就是返回了一個具體值，這種子查詢又叫標量子查詢。標量子查詢的結(jié)果，可以直接用比較運算符來進行計算。

當返回結(jié)果是N行1列時，實際上就是返回了一個相同類型數(shù)值的集合。因此可以使用IN謂詞判斷，同時也可以配合ANY、SOME、ALL等關(guān)鍵字使用。

當返回結(jié)果是N行N列時，實際上就是返回一個臨時表，這時就不能進行值的比較了，而是使用EXISTS謂詞判斷返回的集合是否為空。

【評論、、】?是對我最大的支持！！

Hadoop SQL 大數(shù)據(jù)

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。