01 為什么要學(xué)SQL

首先，說下為什么要學(xué)SQL，目前職場(chǎng)中不少的求職崗位都需要會(huì)sql的，尤其是數(shù)據(jù)分析相關(guān)崗位，是必備的能力之一，也是做數(shù)據(jù)分析利器，也是入門必學(xué)。

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

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

02學(xué)SQL的路徑

再來講講，學(xué)習(xí)SQL的路徑是怎么樣，回憶起來自己從剛開始不會(huì)SQL，到現(xiàn)在SQL 寫的非常熟練，結(jié)合自己學(xué)習(xí)工作的經(jīng)驗(yàn)，總結(jié)SQL學(xué)習(xí)路徑，如下：

1）首先，先了解SQL 使用場(chǎng)景，數(shù)據(jù)庫等相關(guān)概念。

只要跟數(shù)據(jù)相關(guān)的工作其實(shí)都可以用到SQL，特別是數(shù)據(jù)量較大的情況下，Excel 處理不了的情況下。

2）其次，學(xué)習(xí)SQL基本的語法，掌握一些簡(jiǎn)單的SQL書寫。

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

3）再學(xué)習(xí)掌握并熟練使用SQL高階語法。

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

4）之后，進(jìn)階學(xué)習(xí)如何優(yōu)化SQL語句，以期達(dá)到最高查詢效率。

了解事務(wù)、鎖、索引、約束、視圖、元數(shù)據(jù)等概念，并且學(xué)會(huì)使用hive sql、spark sql、pymysql等工具，多表查詢，窗口函數(shù)等~

5）最終，需要根據(jù)業(yè)務(wù)需求，將需求轉(zhuǎn)化成SQL。

業(yè)務(wù)理解足夠深，即知道需要用什么樣的數(shù)據(jù)指標(biāo)來分析、解決業(yè)務(wù)問題。比如說，統(tǒng)計(jì)一個(gè)班級(jí)的及格和不及格占比，統(tǒng)計(jì)考試中前20排名，

總之，SQL語言是數(shù)據(jù)分析工具之一，最終是為了服務(wù)于業(yè)務(wù)的。建?這個(gè)認(rèn)知后以及基本的入門知識(shí)，之后你需要做的就是練習(xí)，練習(xí)，在練習(xí)。

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

·了解SQL(SQL基礎(chǔ)概念)

·檢索數(shù)據(jù)(Select語句)

·過濾數(shù)據(jù)(where 子句)

·創(chuàng)建計(jì)算字段（concat函數(shù)、||的用法）

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

·分組數(shù)據(jù)（Groupby、having）

·使用子查詢（in & exits）

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

·數(shù)據(jù)增刪改(DDL、DML)

·使用視圖

·使用存儲(chǔ)過程

·使用游標(biāo)

·····

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

什么是數(shù)據(jù)庫，數(shù)據(jù)庫就是存數(shù)據(jù)管理數(shù)據(jù)的一個(gè)東西系統(tǒng)，稱之為數(shù)據(jù)庫管理系統(tǒng)（Database Management System，DBMS），DBMS根據(jù)保存的格式分為5種，

• 層次數(shù)據(jù)庫（Hierarchical Database，HDB）

• 關(guān)系數(shù)據(jù)庫（Relational Database，RDB）

• 面向?qū)ο髷?shù)據(jù)庫（Object Oriented Database，OODB）

• XML數(shù)據(jù)庫（XML Database，XMLDB）

• 鍵值存儲(chǔ)系統(tǒng)（Key-Value Store，KVS），舉例：MongoDB

我們最常用的就是關(guān)系型數(shù)據(jù)庫：

• Oracle Database：甲骨文公司的RDBMS

• SQL Server：微軟公司的RDBMS

• DB2：IBM公司的RDBMS

• PostgreSQL：開源的RDBMS

• MySQL：開源的RDBMS

數(shù)據(jù)庫中存儲(chǔ)的表結(jié)構(gòu)類似于excel中的行和列，行稱為記錄，它相當(dāng)于一條記錄，列稱為字段，主鍵：是這一列唯一標(biāo)識(shí)表里的每一行，在這一列中沒有重復(fù)的。

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

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

第一類：DDL

DDL（Data Definition Language，數(shù)據(jù)定義語言） 用來創(chuàng)建或者刪除存儲(chǔ)數(shù)據(jù)用的數(shù)據(jù)庫以及數(shù)據(jù)庫中的表等對(duì)象。DDL 包含以下幾種指令。

• CREATE ：創(chuàng)建數(shù)據(jù)庫和表等對(duì)象

• DROP ：刪除數(shù)據(jù)庫和表等對(duì)象

• ALTER ：修改數(shù)據(jù)庫和表等對(duì)象的結(jié)構(gòu)

第二類：DML

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

• SELECT ：查詢表中的數(shù)據(jù)

• INSERT ：向表中插入新數(shù)據(jù)

• UPDATE ：更新表中的數(shù)據(jù)

• DELETE ：刪除表中的數(shù)據(jù)

第三類：DCL（了解）

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

• COMMIT ：確認(rèn)對(duì)數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行的變更

• ROLLBACK ：取消對(duì)數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行的變更

• GRANT ：賦予用戶操作權(quán)限

• REVOKE ：取消用戶的操作權(quán)限

實(shí)際使用的 SQL 語句當(dāng)中有 90% 屬于 DML，已經(jīng)認(rèn)識(shí)數(shù)據(jù)庫的基本信息了，那我們可以創(chuàng)建一個(gè)數(shù)據(jù)庫。

#創(chuàng)建數(shù)據(jù)庫-- 語法：CREATE DATABASE < 數(shù)據(jù)庫名稱 > ;CREATE DATABASE guagua;

創(chuàng)建好數(shù)據(jù)庫之后還需要?jiǎng)?chuàng)建個(gè)表，數(shù)據(jù)是存儲(chǔ)在表里，那就創(chuàng)建一個(gè)學(xué)生的表

#創(chuàng)建表/*
語法：CREATE TABLE < 表名 >( < 列名 1> < 數(shù)據(jù)類型 > < 該列所需約束 > ,  < 列名 2> < 數(shù)據(jù)類型 > < 該列所需約束 > ,  < 列名 3> < 數(shù)據(jù)類型 > < 該列所需約束 > ,  < 列名 4> < 數(shù)據(jù)類型 > < 該列所需約束 > ,  .  .  .  < 該表的約束 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) )  ;

這里可以稍微提一下命名規(guī)則：

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

書寫規(guī)則：

• SQL語句要以分號(hào)（ ; ）結(jié)尾
• SQL 不區(qū)分關(guān)鍵字的大小寫，但是插入到表中的數(shù)據(jù)是區(qū)分大小寫的
• win 系統(tǒng)默認(rèn)不區(qū)分表名及字段名的大小寫
• linux / mac 默認(rèn)嚴(yán)格區(qū)分表名及字段名的大小寫
• 本教程已統(tǒng)一調(diào)整表名及字段名的為小寫，以方便初學(xué)者學(xué)習(xí)使用。
• 常數(shù)的書寫方式是固定的

數(shù)據(jù)庫創(chuàng)建的表中都需要必須指定數(shù)據(jù)類型，如果創(chuàng)建錯(cuò)誤會(huì)出現(xiàn)報(bào)錯(cuò)的情況。

四種最基本的數(shù)據(jù)類型

• INTEGER 型：用來指定存儲(chǔ)整數(shù)的列的數(shù)據(jù)類型（數(shù)字型），不能存儲(chǔ)小數(shù)。

• CHAR型用來存儲(chǔ)定長字符串，當(dāng)列中存儲(chǔ)的字符串長度達(dá)不到最大長度的時(shí)候，使用半角空格進(jìn)行補(bǔ)足，由于會(huì)浪費(fèi)存儲(chǔ)空間，所以一般不使用。

• VARCHAR 型用來存儲(chǔ)可變長度字符串，定長字符串在字符數(shù)未達(dá)到最大長度時(shí)會(huì)用半角空格補(bǔ)足，但可變長字符串不同，即使字符數(shù)未達(dá)到最大長度，也不會(huì)用半角空格補(bǔ)足。

• DATE 型用來指定存儲(chǔ)日期（年月日）的列的數(shù)據(jù)類型（日期型）。

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

AUTO_INCREMENT自增約束：每當(dāng)增加一行時(shí)自動(dòng)增量。每次執(zhí)行一個(gè)INSERT操作時(shí)，MySQL自動(dòng)對(duì)該列增量

DEFAULT：默認(rèn)值設(shè)定

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

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

# 注意：ALTER TABLE 語句和 DROP TABLE 語句一樣，執(zhí)行之后無法恢復(fù)

創(chuàng)建好數(shù)據(jù)庫了然后可以把數(shù)據(jù)添加到數(shù)據(jù)庫了，這樣要用到insert into￥

# 插入數(shù)據(jù)-- INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);  # 可以省略列名，會(huì)默認(rèn)按照從左到右的順序賦給每一列，不建議用insert into students (sname , course, score , sex , class , Sage ) values('趙雷' , '語文', 98 , '男', '一班','1990-12-21');insert into students (sname , course, score , sex , class , Sage ) values('趙雷' , '數(shù)學(xué)', 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('李四' , '數(shù)學(xué)', 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('李五' , '數(shù)學(xué)', 56 , '女', '三班','1994-12-21');insert into students (sname , course, score , sex , class , Sage ) values('李五' , '英語', 88 , '女', '三班','1994-12-21');

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

執(zhí)行一次 INSERT 語句會(huì)插入一行數(shù)據(jù)。插入多行時(shí)，通常需要循環(huán)執(zhí)行 INSERT 語句，數(shù)據(jù)添加好了，想修改數(shù)據(jù)咋辦，update...set...$

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

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

刪除數(shù)據(jù)跟修改數(shù)據(jù)相似就不在重復(fù)說了，重點(diǎn)就是不要忘記where。

1、簡(jiǎn)單查詢

#從表中提取數(shù)據(jù)select * from students;select sname from students;select distinct sname  from students;

值得一提的是如果

從表中選取數(shù)據(jù)：SELECT <列名>,<列名>,<列名> FROM <表名>;

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

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

算數(shù)運(yùn)算符：四則運(yùn)算

注意：如果有括號(hào)優(yōu)先處理

還有 is null/is not null

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

2、聚合查詢

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

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

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

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

這些聚合函數(shù)運(yùn)行的時(shí)候計(jì)算的整體的數(shù)，那我想分組匯總怎么辦，那就可以用group by。

3、分組

group by

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

GROUP BY子句必須出現(xiàn)在WHERE子句之后，ORDER BY子句之前。

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

with rollup是用來在分組統(tǒng)計(jì)數(shù)據(jù)的基礎(chǔ)上再進(jìn)行統(tǒng)計(jì)匯總

having：過濾分組

分組統(tǒng)計(jì)之后想再添加條件過濾，那就用到having

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

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

4、排序

order by：排序

SELECT <列名1>, <列名2>, <列名3>, ……  FROM <表名> ORDER BY <排序基準(zhǔn)列1>, <排序基準(zhǔn)列2>, ……

SQL中的執(zhí)行結(jié)果是隨機(jī)排列的，當(dāng)需要按照特定順序排序時(shí)，可已使用ORDER BY子句。

order by中可以使用別名

ps：語句的執(zhí)行順序

1、函數(shù)

函數(shù)大致分為如下幾類：

●算術(shù)函數(shù) （用來進(jìn)行數(shù)值計(jì)算的函數(shù)）
●字符串函數(shù) （用來進(jìn)行字符串操作的函數(shù)）
●日期函數(shù) （用來進(jìn)行日期操作的函數(shù)）
●轉(zhuǎn)換函數(shù) （用來轉(zhuǎn)換數(shù)據(jù)類型和值的函數(shù)）
●聚合函數(shù) （用來進(jìn)行數(shù)據(jù)聚合的函數(shù)）

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

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

字符串函數(shù)

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

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

日期函數(shù)

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

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

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

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

• SYSDATE() 返回的是這個(gè)函數(shù)執(zhí)行時(shí)候的時(shí)間

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

• DATE_FORMAT()，將日期根據(jù)指定的格式返回為對(duì)應(yīng)的字符串

• date_sub，時(shí)間減

• date_add，時(shí)間加

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);#往前加兩月

其他函數(shù)

• like函數(shù)，通配符

• between，注意：左右兩邊是閉區(qū)間

• is null，is not null

• in和not in 支持子查詢

• case 表達(dá)式

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

# %通配符：%表示任何字符出現(xiàn)任意次數(shù)# 匹配李開頭的學(xué)員信息select * from students where sname like '李%'
# _ 通配符，只匹配單個(gè)字符# 匹配李某select * from students where sname like '李_'

case表達(dá)式

#case when then語法CASE WHEN <求值表達(dá)式> THEN <表達(dá)式>     WHEN <求值表達(dá)式> THEN <表達(dá)式>     WHEN <求值表達(dá)式> THEN <表達(dá)式>     .     .     .ELSE <表達(dá)式>END 
# 添加一列及格和不及格# case函數(shù)select *,  (case when score >= 60 then '及格' else '不及格' end) from students;
# 行轉(zhuǎn)列,科目放在表頭select sname,  (case when course ='語文' then score  else null  end) as '語文',  (case when course ='數(shù)學(xué)' then score  else null  end) as '數(shù)學(xué)',  (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 ='數(shù)學(xué)' then score  else null  end)) as '數(shù)學(xué)',  sum((case when course ='英語' then score  else null  end)) as '英語'    from students  group by sname ;

2、視圖

什么是視圖？虛擬表，視圖是依據(jù)SELECT語句來創(chuàng)建的，所以說是select語句生產(chǎn)的虛擬表叫視圖。

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

3、子查詢

子查詢指一個(gè)查詢語句嵌套在另一個(gè)查詢語句內(nèi)部的查詢

子查詢的結(jié)果可以作為一個(gè)表，也可以作為一個(gè)過濾條件

# 作為一個(gè)表--嵌套子查詢select 列名 from (select 列名 from 表1 where 列 where 表達(dá)式) as 子查詢 -- 結(jié)果作為一個(gè)表
# 作為一個(gè)結(jié)果-- 標(biāo)量子查詢select 列名 from 表1 where 列名 = (select max(列名) from 表1  )# 成績(jī)高于平均值select * from students where score>= (select avg(score) from students )

還有一種是關(guān)聯(lián)子查詢，有關(guān)聯(lián)兩個(gè)字就意味著查詢與子查詢之間存在著聯(lián)系，那么聯(lián)系是如何建立起來的

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

關(guān)鍵字：union ，union all，join，iinner join，outer join，left join，right join。

1、上下拼接

用UNION將多條SELECT語句組合成一個(gè)結(jié)果集

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

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

# unionselect 123 as 數(shù)字 union select 456
# 排序select 123 as 數(shù)字 union select 456 order by 數(shù)字 desc

2、聯(lián)結(jié)

·內(nèi)連接

-- 內(nèi)連結(jié)FROM  INNER JOIN  ON 

INNER 關(guān)鍵詞表示使用了內(nèi)連結(jié)

注意：

• 必須使用 ON 子句來指定連結(jié)條件，在進(jìn)行內(nèi)連結(jié)時(shí) ON 子句是必不可少的，ON 子句是專門用來指定連結(jié)條件的。

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

如果需要在使用內(nèi)連結(jié)的時(shí)候同時(shí)使用 WHERE 子句對(duì)檢索結(jié)果進(jìn)行篩選，則需要把 WHERE 子句寫在 ON 子句的后邊。

where的使用方法：

• 子查詢，上述查詢作為子查詢, 用括號(hào)封裝起來, 然后在外層查詢?cè)黾雍Y選條件.

• on后面加where，查詢的執(zhí)行順序:FROM 子句->WHERE 子句->SELECT 子句，在做完 INNER JOIN … ON 得到一個(gè)新表后, 才會(huì)執(zhí)行 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 列條件

·自然聯(lián)結(jié)NATURAL JOIN

會(huì)按照兩個(gè)表中都包含的列名來進(jìn)行等值內(nèi)連結(jié)，此時(shí)無需使用 ON 來指定連接條件。使用自然連結(jié)還可以求出兩張表或子查詢的公共部分。

SELECT * FROM 表1 NATURAL JOIN 表2

·外連接

• LEFT JOIN（左連接）：獲取左表所有記錄，即使右表沒有對(duì)應(yīng)匹配的記錄。

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

左右聯(lián)結(jié)最終要的一個(gè)點(diǎn)是選要把哪張表作為主表，使用 LEFT 時(shí) FROM 子句中寫在左側(cè)的表是主表，使用 RIGHT 時(shí)右側(cè)的表是主表。

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

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

沒有了ON子句的限制，會(huì)對(duì)左表和右表的每一行進(jìn)行組合。

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

窗口函數(shù)的通用公式：

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

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

order by是用來排序的，決定在窗口內(nèi)，哪些規(guī)則是來排序的

1、排序類的窗口函數(shù)

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

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

• ROW_NUMBER函數(shù)：連續(xù)位次，依次排序不會(huì)重復(fù)，1233

# 英語成績(jī)排名

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='英語';  # 根據(jù)科目進(jìn)行排序
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、聚合函數(shù)使用

出來的結(jié)果是一個(gè)累計(jì)的聚合函數(shù)值，根據(jù)分組情況進(jìn)行累計(jì)計(jì)算，比如求和，平均，最大，最小，計(jì)數(shù)

• sum，累計(jì)求和

• avg，移動(dòng)平均

• 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、移動(dòng)平均

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

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

# 移動(dòng)平均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