01 為什么要學SQL

首先，說下為什么要學SQL，目前職場中不少的求職崗位都需要會sql的，尤其是數據分析相關崗位，是必備的能力之一，也是做數據分析利器，也是入門必學。

只要在工作中需要數據反饋的，都離不開SQL的使用。一般想通過SQL尋求崗位需要掌握以下幾點就可以了

1）會利用SQL操作開關系數據庫mysql進行查詢
2）數據庫的分組、聚合、排序
3）存儲過程
4）對于增刪改、約束、索引、數據庫范式均大致了解即可，不需要深入

02學SQL的路徑

再來講講，學習SQL的路徑是怎么樣，回憶起來自己從剛開始不會SQL，到現在SQL 寫的非常熟練，結合自己學習工作的經驗，總結SQL學習路徑，如下：

1）首先，先了解SQL 使用場景，數據庫等相關概念。

只要跟數據相關的工作其實都可以用到SQL，特別是數據量較大的情況下，Excel 處理不了的情況下。

2）其次，學習SQL基本的語法，掌握一些簡單的SQL書寫。

包括select、where、group by、having、order by、delete、insert、join、update等，可以做日常的取數或簡單的分析（該水平運營、產品等非數據崗就夠了）;

3）再學習掌握并熟練使用SQL高階語法。

比如集合、分組聚合、子查詢、條件邏輯、字符串函數、算術函數、日期時間函數，并且知道MySQL、Oracle、SQL Server等數據庫的語法差異；

4）之后，進階學習如何優化SQL語句，以期達到最高查詢效率。

了解事務、鎖、索引、約束、視圖、元數據等概念，并且學會使用hive sql、spark sql、pymysql等工具，多表查詢，窗口函數等~

5）最終，需要根據業務需求，將需求轉化成SQL。

業務理解足夠深，即知道需要用什么樣的數據指標來分析、解決業務問題。比如說，統計一個班級的及格和不及格占比，統計考試中前20排名，

總之，SQL語言是數據分析工具之一，最終是為了服務于業務的。建?這個認知后以及基本的入門知識，之后你需要做的就是練習，練習，在練習。

《SQL必知必會》這本書的優點是可以快速入門,適合新手小白，語言通俗易懂，讀著不會犯困。全書200來頁,覆蓋的知識點也很全：

·了解SQL(SQL基礎概念)

·檢索數據(Select語句)

·過濾數據(where 子句)

·創建計算字段（concat函數、||的用法）

·匯總數據（常見的聚合函數，如COUNT、MAX、SUM、AVG等）

·分組數據（Groupby、having）

·使用子查詢（in & exits）

·表連接（left join 、right join 、full outer join）

·數據增刪改(DDL、DML)

·使用視圖

·使用存儲過程

·使用游標

·····

那么，接下來，讓我們來一起回顧一下：

什么是數據庫，數據庫就是存數據管理數據的一個東西系統，稱之為數據庫管理系統（Database Management System，DBMS），DBMS根據保存的格式分為5種，

• 層次數據庫（Hierarchical Database，HDB）

• 關系數據庫（Relational Database，RDB）

• 面向對象數據庫（Object Oriented Database，OODB）

• XML數據庫（XML Database，XMLDB）

• 鍵值存儲系統（Key-Value Store，KVS），舉例：MongoDB

我們最常用的就是關系型數據庫：

• Oracle Database：甲骨文公司的RDBMS

• SQL Server：微軟公司的RDBMS

• DB2：IBM公司的RDBMS

• PostgreSQL：開源的RDBMS

• MySQL：開源的RDBMS

數據庫中存儲的表結構類似于excel中的行和列，行稱為記錄，它相當于一條記錄，列稱為字段，主鍵：是這一列唯一標識表里的每一行，在這一列中沒有重復的。

SQL是什么呢？SQL是跟數據庫交流的語言，用SQL指揮數據庫去干這個干那個，想讓他干啥就干啥。

SQL的語言分三類，這個大概就了解一下，

第一類：DDL

DDL（Data Definition Language，數據定義語言） 用來創建或者刪除存儲數據用的數據庫以及數據庫中的表等對象。DDL 包含以下幾種指令。

• CREATE ：創建數據庫和表等對象

• DROP ：刪除數據庫和表等對象

• ALTER ：修改數據庫和表等對象的結構

第二類：DML

DML（Data Manipulation Language，數據操縱語言） 用來查詢或者變更表中的記錄。變更:DML 包含以下幾種指令。

• SELECT ：查詢表中的數據

• INSERT ：向表中插入新數據

• UPDATE ：更新表中的數據

• DELETE ：刪除表中的數據

第三類：DCL（了解）

DCL（Data Control Language，數據控制語言） 用來確認或者取消對數據庫中的數據進行的變更。除此之外，還可以對 RDBMS 的用戶是否有權限操作數據庫中的對象（數據庫表等）進行設定。DCL 包含以下幾種指令。

• COMMIT ：確認對數據庫中的數據進行的變更

• ROLLBACK ：取消對數據庫中的數據進行的變更

• GRANT ：賦予用戶操作權限

• REVOKE ：取消用戶的操作權限

實際使用的 SQL 語句當中有 90% 屬于 DML，已經認識數據庫的基本信息了，那我們可以創建一個數據庫。

#創建數據庫-- 語法：CREATE DATABASE < 數據庫名稱 > ;CREATE DATABASE guagua;

創建好數據庫之后還需要創建個表，數據是存儲在表里，那就創建一個學生的表

#創建表/*
語法：CREATE TABLE < 表名 >( < 列名 1> < 數據類型 > < 該列所需約束 > ,  < 列名 2> < 數據類型 > < 該列所需約束 > ,  < 列名 3> < 數據類型 > < 該列所需約束 > ,  < 列名 4> < 數據類型 > < 該列所需約束 > ,  .  .  .  < 該表的約束 1> , < 該表的約束 2> ,……);
*/
CREATE TABLE students(     sid int NOT NULL AUTO_INCREMENT,      sname VARCHAR(100) NOT NULL,      course VARCHAR(32) NOT NULL,      score decimal(18,1) NOT NULL,      sex varchar(10) ,      class varchar(10) NOT NULL,      Sage datetime,     PRIMARY KEY(sid) )  ;

這里可以稍微提一下命名規則：

• 只能使用半角英文字母、數字、下劃線（_）作為數據庫、表和列的名稱
• 名稱必須以半角英文字母開頭

書寫規則：

• SQL語句要以分號（ ; ）結尾
• SQL 不區分關鍵字的大小寫，但是插入到表中的數據是區分大小寫的
• win 系統默認不區分表名及字段名的大小寫
• linux / mac 默認嚴格區分表名及字段名的大小寫
• 本教程已統一調整表名及字段名的為小寫，以方便初學者學習使用。
• 常數的書寫方式是固定的

數據庫創建的表中都需要必須指定數據類型，如果創建錯誤會出現報錯的情況。

四種最基本的數據類型

• INTEGER 型：用來指定存儲整數的列的數據類型（數字型），不能存儲小數。

• CHAR型用來存儲定長字符串，當列中存儲的字符串長度達不到最大長度的時候，使用半角空格進行補足，由于會浪費存儲空間，所以一般不使用。

• VARCHAR 型用來存儲可變長度字符串，定長字符串在字符數未達到最大長度時會用半角空格補足，但可變長字符串不同，即使字符數未達到最大長度，也不會用半角空格補足。

• DATE 型用來指定存儲日期（年月日）的列的數據類型（日期型）。

指定數據類型之后后面還有一個not null，對存儲的數據進行限制了，我們把這種叫做約束，約束分兩種，一種是非空約束，就是必須輸入數據，另一種是主鍵約束，代表唯一值，就是剛剛提到的。

AUTO_INCREMENT自增約束：每當增加一行時自動增量。每次執行一個INSERT操作時，MySQL自動對該列增量

DEFAULT：默認值設定

表的其他操作可以熟悉一下

# 修改表-- 添加列 ALTER TABLE < 表名 > ADD COLUMN < 列名 >;ALTER TABLE students ADD COLUMN aaa VARCHAR(100);
-- 刪除列 ALTER TABLE product DROP COLUMN aaa;
ALTER TABLE students DROP COLUMN aaa ;
# 刪除表-- 整個表刪除 DROP TABLE < 表名 > ;-- 刪除的表是無法恢復的，只能重新插入，請執行刪除操作時無比要謹慎
-- 清空表TRUNCATE TABLE TABLE_NAME;-- 優點：相比drop``/``delete，truncate用來清除數據時，速度最快。

# 注意：ALTER TABLE 語句和 DROP TABLE 語句一樣，執行之后無法恢復

創建好數據庫了然后可以把數據添加到數據庫了，這樣要用到insert into￥

# 插入數據-- INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);  # 可以省略列名，會默認按照從左到右的順序賦給每一列，不建議用insert into students (sname , course, score , sex , class , Sage ) values('趙雷' , '語文', 98 , '男', '一班','1990-12-21');insert into students (sname , course, score , sex , class , Sage ) values('趙雷' , '數學', 34 , '男', '一班','1990-12-21');insert into students (sname , course, score , sex , class , Sage ) values('趙雷' , '英語', 69 , '男', '一班','1990-12-21');insert into students (sname , course, score , sex , class , Sage ) values('李四' , '語文', 76 , '女', '二班','1991-12-21');insert into students (sname , course, score , sex , class , Sage ) values('李四' , '數學', 45 , '女', '二班','1991-12-21');insert into students (sname , course, score , sex , class , Sage ) values('李四' , '英語', 56 , '女', '二班','1991-12-21');insert into students (sname , course, score , sex , class , Sage ) values('李五' , '語文', 33 , '女', '三班','1994-12-21');insert into students (sname , course, score , sex , class , Sage ) values('李五' , '數學', 56 , '女', '三班','1994-12-21');insert into students (sname , course, score , sex , class , Sage ) values('李五' , '英語', 88 , '女', '三班','1994-12-21');

#可以使用INSERT … SELECT 語句從其他表復制數據-- 將學生表的數據復制到學生表復制表中INSERT INTO students (sname , course, score , sex , class , Sage )SELECT sname , course, score , sex , class , SageFROM students;  

執行一次 INSERT 語句會插入一行數據。插入多行時，通常需要循環執行 INSERT 語句，數據添加好了，想修改數據咋辦，update...set...$

# 修改數據/*UPDATE <表名>SET <列名> = <表達式> [, <列名2>=<表達式2>...];  WHERE <條件>;  -- 可選，非常重要。*/-- 注意添加 where 條件，否則將會將所有的行按照語句修改，-- 把李五的性別改成女的，班級改成四班UPDATE students   SET sex = '女', -- 可以講null當做一個值來使用   class = '四班'  WHERE sname = '李五';  
-- UPDATE 語句的 SET 子句支持同時將多個列作為更新對象。

# 刪除數據
-- 刪除數據跟修改數據語法相似，delect from <表名> where 條件 （非常重要）

刪除數據跟修改數據相似就不在重復說了，重點就是不要忘記where。

1、簡單查詢

#從表中提取數據select * from students;select sname from students;select distinct sname  from students;

值得一提的是如果

從表中選取數據：SELECT <列名>,<列名>,<列名> FROM <表名>;

注意：DISTINCT關鍵字作用于所有的列，不僅僅是跟在其后的那一列

想從中篩選出符合條件的列用where字句指定查詢數的條件，where后面要跟條件表達式 

算數運算符：四則運算

注意：如果有括號優先處理

還有 is null/is not null

#運用# 查詢及格的信息select * from students where score>=60;#查詢出一班和二班里及格的學生select * from students where score>=60  and (class='一班' or class='二班' ) ;-- 其他的方法自行探索

2、聚合查詢

• COUNT：計算表中的記錄數（行數）
• SUM：計算表中數值列中數據的合計值
• AVG：計算表中數值列中數據的平均值
• MAX：求出表中任意列中數據的最大值
• MIN：求出表中任意列中數據的最小值

注意：聚合函數對null是排除在外的，COUNT(*)除外

COUNT(*)會得到包含NULL的數據行數，而COUNT(<列名>)會得到NULL之外的數據行數

# 計算最高分和最低分select max(score),min(score) from  students;# 計算里面多少個學生select count(distinct sname)  from students;

這些聚合函數運行的時候計算的整體的數，那我想分組匯總怎么辦，那就可以用group by。

3、分組

group by

SELECT <列名1>,<列名2>, <列名3>, ……  FROM <表名> GROUP BY <列名1>, <列名2>, <列名3>, ……;
# 計算每個考試科目里面的最高分和最低分select course,max(score),min(score) from  students group by course;

GROUP BY子句必須出現在WHERE子句之后，ORDER BY子句之前。

group by 能不能使用別名，可以，where 不能使用別名

with rollup是用來在分組統計數據的基礎上再進行統計匯總

having：過濾分組

分組統計之后想再添加條件過濾，那就用到having

# having 過濾分組# 語文和數學考試中最高分和最低分分別是多少select course,max(score),min(score) from  students group by course having course in ('數學','語文') ;

where 和having非常類似，唯一的差別是WHERE過濾行，而HAVING過濾分組。HAVING支持所有WHERE操作符。

4、排序

order by：排序

SELECT <列名1>, <列名2>, <列名3>, ……  FROM <表名> ORDER BY <排序基準列1>, <排序基準列2>, ……

SQL中的執行結果是隨機排列的，當需要按照特定順序排序時，可已使用ORDER BY子句。

order by中可以使用別名

ps：語句的執行順序

1、函數

函數大致分為如下幾類：

●算術函數 （用來進行數值計算的函數）
●字符串函數 （用來進行字符串操作的函數）
●日期函數 （用來進行日期操作的函數）
●轉換函數 （用來轉換數據類型和值的函數）
●聚合函數 （用來進行數據聚合的函數）

算術函數

●ABS – 絕對值
●MOD – 求余數，語法：MOD( 被除數，除數 )
●ROUND – 四舍五入，語法：ROUND( 對象數值，保留小數的位數 )

字符串函數

●CONCAT – 拼接，語法：CONCAT(str1, str2, str3)
●LENGTH – 字符串長度，語法：LENGTH( 字符串 )
●LOWER – 小寫轉換
●REPLACE – 字符串的替換，語法：REPLACE( 對象字符串，替換前的字符串，替換后的字符串 )
●LETF-從左開始截取字符串，語法：LEFT(被截取字段，截取長度)
●RIGHT-從右開始截取字符串，語法：right（被截取字段，截取長度）
●mid-自定義截取，語法：mid（被截取字段，從那位置開始，截取幾個字符數）
●SUBSTRING – 截取字符串，substring（被截取字段，從第幾位開始截?。?，substring（被截取字段，從第幾位開始截取，截取長度）
●SUBSTRING_INDEX –按關鍵字截取字符串，語法：substring_index（被截取字段，分隔符，關鍵字出現的次數）
●group_concat，字符串分組拼接

# 字符串截取select substring_index('趙雷-一班-一年級',"-",1) ; -- 從左邊關鍵字出現第1次，截取字符串之前所有select substring_index('趙雷-一班-一年級',"-",2) ; -- 從左邊關鍵字出現第2次，截取字符串之前所有select substring_index('趙雷-一班-一年級',"-",-1) ; -- 從右邊關鍵字出現第1次，截取字符串之前

日期函數

• DATE()，返回日期。格式：YYYY-MM-DD

• TIME()，返回日期。格式：HH-mm-ss

• TIMESTAMP()，返回日期時間。格式：YYYY-MM-DD HH-mm-ss

• NOW()、CURRENT_TIMESTAMP、CURRENT_TIMESTAMP()，返回語句開始執行的時間

• SYSDATE() 返回的是這個函數執行時候的時間

select SLEEP(1),NOW(),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP(),SYSDATE();

• DATE_FORMAT()，將日期根據指定的格式返回為對應的字符串

• date_sub，時間減

• date_add，時間加

select SLEEP(1),NOW(),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP(),SYSDATE();
SELECT DATE_FORMAT('2022-01-01 22:00:00','%Y-%m-%d');SELECT DATE_ADD('2022-01-01 22:00:00',interval 2 day);#往前加兩天SELECT DATE_ADD('2022-01-01 22:00:00',interval -2 day);#往后加兩天SELECT DATE_ADD('2022-01-01 22:00:00',interval 2 month);#往前加兩月

其他函數

• like函數，通配符

• between，注意：左右兩邊是閉區間

• is null，is not null

• in和not in 支持子查詢

• case 表達式

通配符：用來匹配值的一部分的特殊字符，使用通配符就必須使用like

# %通配符：%表示任何字符出現任意次數# 匹配李開頭的學員信息select * from students where sname like '李%'
# _ 通配符，只匹配單個字符# 匹配李某select * from students where sname like '李_'

case表達式

#case when then語法CASE WHEN <求值表達式> THEN <表達式>     WHEN <求值表達式> THEN <表達式>     WHEN <求值表達式> THEN <表達式>     .     .     .ELSE <表達式>END 
# 添加一列及格和不及格# case函數select *,  (case when score >= 60 then '及格' else '不及格' end) from students;
# 行轉列,科目放在表頭select sname,  (case when course ='語文' then score  else null  end) as '語文',  (case when course ='數學' then score  else null  end) as '數學',  (case when course ='英語' then score  else null  end) as '英語'    from students ;

  select sname,  sum((case when course ='語文' then score  else null  end)) as '語文',  sum((case when course ='數學' then score  else null  end)) as '數學',  sum((case when course ='英語' then score  else null  end)) as '英語'    from students  group by sname ;

2、視圖

什么是視圖？虛擬表，視圖是依據SELECT語句來創建的，所以說是select語句生產的虛擬表叫視圖。

# 創建視圖CREATE VIEW <視圖名稱>(<列名1>,<列名2>,...) AS 
CREATE view st (sname,score) asselect sname,score from students;

3、子查詢

子查詢指一個查詢語句嵌套在另一個查詢語句內部的查詢

子查詢的結果可以作為一個表，也可以作為一個過濾條件

# 作為一個表--嵌套子查詢select 列名 from (select 列名 from 表1 where 列 where 表達式) as 子查詢 -- 結果作為一個表
# 作為一個結果-- 標量子查詢select 列名 from 表1 where 列名 = (select max(列名) from 表1  )# 成績高于平均值select * from students where score>= (select avg(score) from students )

還有一種是關聯子查詢，有關聯兩個字就意味著查詢與子查詢之間存在著聯系，那么聯系是如何建立起來的

# 各個科目中高于該科目的成績select sname,course,score from students as a where score>=(select avg(score) as avg_score from students as bwhere a.course=b.course )

關鍵字：union ，union all，join，iinner join，outer join，left join，right join。

1、上下拼接

用UNION將多條SELECT語句組合成一個結果集

注意：UNION必須由兩條或兩條以上的SELECT語句組成，語句之間用關鍵字UNION分隔；

union 是去重，union all 是不去重的，只能使用一條ORDER BY子句，它必須出現在最后一條SELECT語句之后。列需要進行對齊操作。

# unionselect 123 as 數字 union select 456
# 排序select 123 as 數字 union select 456 order by 數字 desc

2、聯結

·內連接

-- 內連結FROM  INNER JOIN  ON 

INNER 關鍵詞表示使用了內連結

注意：

• 必須使用 ON 子句來指定連結條件，在進行內連結時 ON 子句是必不可少的，ON 子句是專門用來指定連結條件的。

• SELECT 子句中的列最好按照 表名.列名 的格式來使用。如果兩張表有其他名稱相同的列， 則必須使用上述格式來選擇列名,，否則查詢語句會報錯。

如果需要在使用內連結的時候同時使用 WHERE 子句對檢索結果進行篩選，則需要把 WHERE 子句寫在 ON 子句的后邊。

where的使用方法：

• 子查詢，上述查詢作為子查詢, 用括號封裝起來, 然后在外層查詢增加篩選條件.

• on后面加where，查詢的執行順序:FROM 子句->WHERE 子句->SELECT 子句，在做完 INNER JOIN … ON 得到一個新表后, 才會執行 WHERE 子句

select 列 from 表1 inner join 表2 on 列1=列2 where 列條件
select 列 from (select 列 from 表1 inner join 表2 on 列1 = 列2) as 表3 where 列條件

·自然聯結NATURAL JOIN

會按照兩個表中都包含的列名來進行等值內連結，此時無需使用 ON 來指定連接條件。使用自然連結還可以求出兩張表或子查詢的公共部分。

SELECT * FROM 表1 NATURAL JOIN 表2

·外連接

• LEFT JOIN（左連接）：獲取左表所有記錄，即使右表沒有對應匹配的記錄。

• RIGHT JOIN（右連接）： 與 LEFT JOIN 相反，用于獲取右表所有記錄，即使左表沒有對應匹配的記錄。

左右聯結最終要的一個點是選要把哪張表作為主表，使用 LEFT 時 FROM 子句中寫在左側的表是主表，使用 RIGHT 時右側的表是主表。

-- 左連結     FROM  LEFT   JOIN  ON -- 右連結     FROM  RIGHT  JOIN  ON -- 全外連結，mysql不支持全外聯結FROM  FULL   JOIN  ON -- 交叉連結，CROSS JOIN(笛卡爾積)FROM  CROSS JOIN  

什么是笛卡爾積： 就是使用集合 A 中的每一個元素與集合 B 中的每一個元素組成一個有序的組合。

沒有了ON子句的限制，會對左表和右表的每一行進行組合。

窗口函數稱之為OLAP，對數據進行實時分析處理，常規的SELECT語句都是對整張表進行查詢，而窗口函數可以讓我們有選擇的去某一部分數據進行匯總、計算和排序。

窗口函數的通用公式：

<窗口函數> OVER ([PARTITION BY <列名>] ORDER BY <排序用列名>) 

紅色的地方可以省略，最關鍵的是PARTITON BY，PARTITON英文是分割的意思，可以理解為PARTITON BY是用來分組，這個分組的意思是表中哪些行是一組。

order by是用來排序的，決定在窗口內，哪些規則是來排序的

1、排序類的窗口函數

• RANK函數：如果存在相同位次的記錄，則會跳過之后的位次，1134...

• DENSE_RANK函數：即使存在相同位次的記錄，也不會跳過之后的位次。11234

• ROW_NUMBER函數：連續位次，依次排序不會重復，1233

# 英語成績排名

select sname  ,course  ,score  ,rank() over (order by score desc) as ranking  ,dense_rank() over (order by score desc) as dense_ranking  ,row_number()  over (order by score desc) as row_numfrom students where course='英語';  # 根據科目進行排序
select sname  ,course  ,score  ,rank() over (PARTITION BY course order by score desc) as ranking  ,dense_rank() over (PARTITION BY course  order by score desc ) as dense_ranking  ,row_number()  over (PARTITION BY course  order by score desc) as row_numfrom students

2、聚合函數使用

出來的結果是一個累計的聚合函數值，根據分組情況進行累計計算，比如求和，平均，最大，最小，計數

• sum，累計求和

• avg，移動平均

• count

• max

• min

elect sname  ,course  ,score  ,sum(score) over (  order by score ) as current_sum  ,avg(score) over (  order by score  ) as current_avg  ,count(*)  over ( order by score desc ) as count_  ,max(score)  over ( order by score desc ) as max_score  ,min(score)  over ( order by score ) as min_scorefrom students 
# 分組排序select sname  ,course  ,score  ,sum(score) over ( PARTITION BY course order by score desc ) as current_sum  ,avg(score) over ( PARTITION BY course order by course  ) as current_avg  ,count(*)  over ( PARTITION BY course order by score desc) as count_  ,max(score)  over ( PARTITION BY course order by  score desc ) as max_score  ,min(score)  over (PARTITION BY course  order by course ) as min_scorefrom students

3、移動平均

<窗口函數> OVER (ORDER BY <排序用列名>                 ROWS n PRECEDING )                   <窗口函數> OVER (ORDER BY <排序用列名>                 ROWS BETWEEN n PRECEDING AND n FOLLOWING)

PRECEDING（“之前”）， 將框架指定為 “截止到之前 n 行”，加上自身行FOLLOWING（“之后”）， 將框架指定為 “截止到之后 n 行”，加上自身行BETWEEN 1 PRECEDING AND 1 FOLLOWING，將框架指定為 “之前1行” + “之后1行” + “自身”

# 移動平均select sname  ,course  ,score  ,AVG(score) OVER (ORDER BY course                               ROWS 2 PRECEDING) AS moving_avg -- 之前2行  ,AVG(score) OVER (ORDER BY course                               ROWS BETWEEN 1 PRECEDING                                         AND 1 FOLLOWING) AS moving_avg  -- 前一行，后一行from students