MySQL高級(jí)SQL操作

• 數(shù)據(jù)新增
  • 多數(shù)據(jù)插入
  • 蠕蟲復(fù)制
  • 主鍵沖突
• 查詢數(shù)據(jù)
  • 查詢選項(xiàng)
  • 別名
  • 數(shù)據(jù)源
  • where子句
  • group by子句
  • having子句
  • order by子句
  • limit子句
• 更新數(shù)據(jù)
  • 限制更新
• 刪除數(shù)據(jù)
  • 限制刪除
  • 數(shù)據(jù)清除

1、統(tǒng)計(jì)不同班級(jí)的人數(shù)信息

• 按照現(xiàn)有SQL操作，即便數(shù)據(jù)表已經(jīng)存在數(shù)據(jù)，但是我們也無法通過SQL實(shí)現(xiàn)，只能取出來后通過其他代碼對(duì)數(shù)據(jù)進(jìn)行加工實(shí)現(xiàn)

• 通過高級(jí)SQL學(xué)習(xí)，我們就可以通過一條SQL指令完成操作

select count(*),班級(jí)ID from 學(xué)生表 group by 班級(jí)ID;

一、數(shù)據(jù)新增

• 批量插入
• 蠕蟲復(fù)制
• 主鍵沖突
  • 沖突更新
  • 沖突替換

1、批量插入

批量插入：是一種優(yōu)化數(shù)據(jù)逐條插入的方式

• 批量插入數(shù)據(jù)的語法與簡單數(shù)據(jù)插入的語法差不多

• 批量插入分為兩種

  • 全字段批量插入

  insert into 表名 values(值列表1),(值列表2),...(值列表N);
  

  • 部分字段批量插入（注意字段默認(rèn)值）

  insert into 表名 (字段列表) values (值列表1),(值列表2),...(值列表N);
  

步驟

1、用戶的操作涉及到多記錄同時(shí)插入（通常數(shù)據(jù)批量導(dǎo)入）

2、組裝成批量插入SQL指令

• 字段為全字段（邏輯主鍵不存在沒問題）：全字段批量插入SQL
• 部分字段：組織字段列表并檢查其他字段是否允許默認(rèn)值

3、執(zhí)行批量插入

示例

1、批量插入學(xué)生成績（t_30全字段）

insert into t_30 values(null,'Tom','Computer',90),
(null,'Lily','Computer',92);

2、批量插入學(xué)生考試信息（t_30不包含成績）

insert into t_30 (stu_name,course) values('Tony','English'),('Ray','Math');

小結(jié)

1、批量插入可以針對(duì)性解決數(shù)據(jù)的批量導(dǎo)入之類的業(yè)務(wù)

2、批量插入可以一次性解決多條數(shù)據(jù)插入，能夠有效降低客戶端占用問題，提升數(shù)據(jù)操作效率

• MySQL8以后默認(rèn)有事務(wù)安全，即批量要么都成功要么都失敗，不會(huì)出現(xiàn)部分問題

2、蠕蟲復(fù)制

蠕蟲復(fù)制：從已有表中復(fù)制數(shù)據(jù)直接插入到另外一張表（同一張表）

• 蠕蟲復(fù)制的目標(biāo)是快速增加表中的數(shù)據(jù)
  • 實(shí)現(xiàn)表中數(shù)據(jù)復(fù)制（用于數(shù)據(jù)備份或者遷移）
  • 實(shí)現(xiàn)數(shù)據(jù)的指數(shù)級(jí)遞增（多用于測試）
• 蠕蟲復(fù)制語法

insert into 表名 [(字段列表)] select 字段列表 from 表名;

• 注意事項(xiàng)
  • 字段列表必須對(duì)應(yīng)上
  • 字段類型必須匹配上
  • 數(shù)據(jù)沖突需要事先考慮

步驟

1、確定一張需要插入數(shù)據(jù)的表（一般與要蠕蟲復(fù)制數(shù)據(jù)的表結(jié)構(gòu)一致）

2、確定數(shù)據(jù)的來源表

• 字段數(shù)量匹配
• 字段類型匹配
• 所選字段不存在沖突（數(shù)據(jù)可能是重復(fù)數(shù)據(jù)）

3、使用蠕蟲復(fù)制

示例

1、創(chuàng)建一張新表，將t_30表中的數(shù)據(jù)遷移到新表中

create table t_35(
    id int primary key auto_increment,
    stu_name varchar(20) not null,
    course varchar(20) not null,
    score decimal(5,2)
)charset utf8;

insert into t_35 select * from t_30;

2、快速讓t_35表中的數(shù)據(jù)達(dá)到超過100條（重復(fù)執(zhí)行）

insert into t_35 (stu_name,course,score) select stu_name,course,score from t_35;

小結(jié)

1、蠕蟲復(fù)制的目標(biāo)就是批量利用已有數(shù)據(jù)來豐富表數(shù)據(jù)

• 數(shù)據(jù)遷移：一次性復(fù)制表數(shù)據(jù)
• 數(shù)據(jù)增長：重復(fù)執(zhí)行自我復(fù)制增長（數(shù)據(jù)測試使用）

3、主鍵沖突

主鍵沖突：在數(shù)據(jù)進(jìn)行插入時(shí)包含主鍵指定，而主鍵在數(shù)據(jù)表已經(jīng)存在

• 主鍵沖突的業(yè)務(wù)通常是發(fā)生在業(yè)務(wù)主鍵上（業(yè)務(wù)主鍵本身有業(yè)務(wù)意義）

• 主鍵沖突的解決方案

  • 忽略沖突：保留原始記錄

  insert ignore into 表名 [(字段列表)] values(值列表);
  

  • 沖突更新：沖突后部分字段變成更新

  insert into 表名 [(字段列表)] values(值列表) on duplicate key update 字段 = 新值[,字段=新值...];
  # 1、嘗試新增
  # 2、更新
  

  • 沖突替換：先刪除原有記錄，后新增記錄

  replace into 表名 [(字段列表)] values(值列表); # 效率沒有insert高（需要檢查是否沖突）
  

步驟

1、確定當(dāng)前業(yè)務(wù)可能存在主鍵沖突

2、選擇主鍵沖突的解決方案

示例

1、用戶名作為主鍵的用戶注冊（沖突不能覆蓋）：username，password，regtime

create table t_36(
    username varchar(50) primary key,
    password char(32) not null,
    regtime int unsigned not null
)charset utf8;

insert into t_36 values('username','password',12345678);
# 沖突忽略
insert ignore into t_36 values('username','12345678',12345678);

2、用戶名作為主鍵的記錄用戶使用信息（不存在新增、存在則更新時(shí)間）：username，logintime

create table t_37(
    username varchar(50) primary key,
    logintime int unsigned
)charset utf8;

insert into t_37 values('username',12345678);   # 當(dāng)前時(shí)間戳

# 沖突更新（替換部分字段數(shù)據(jù)）
insert into t_37 values('username',12345678) on duplicate key update logintime = unix_timestamp();  # 當(dāng)前時(shí)間戳

• 如果主鍵不沖突：新增
• 如果主鍵沖突：更新指定字段
• 上述方式適用于字段較多，但是可能沖突時(shí)數(shù)據(jù)變化的字段較少

3、用戶名作為主鍵的記錄用戶使用信息（不存在新增、存在則更新全部）：username，logintime、clientinfo

create table t_38(
    username varchar(50) primary key,
    logintime int unsigned,
    clientinfo varchar(255) not null
)charset utf8;

insert into t_38 values('username',unix_timestamp(),'{PC:chrome}');
# 替換插入
replace into t_38 values('username',unix_timestamp(),'{phone:uc}');

• replace遇到主鍵重復(fù)就會(huì)先刪除、后新增
• 如果有較多字段需要更新：建議使用替換

小結(jié)

1、主鍵沖突的解決方案有三種，但是需要根據(jù)具體的業(yè)務(wù)來選擇合適的方式

• 忽略新數(shù)據(jù)：insert ignore
• 更新部分?jǐn)?shù)據(jù)：insert ... on duplicate key update
• 全部替換：replace into

2、從效率上來講，insert into不考慮沖突的效率最高，三種解決沖突的方式都會(huì)有效率下降（需要檢索），其中三種本身的效率依次是：忽略新數(shù)據(jù) > 更新部分?jǐn)?shù)據(jù) > 替換全部

二、數(shù)據(jù)查詢

• 查詢選項(xiàng)
• 別名應(yīng)用
  • 字段別名
  • 表別名
• 數(shù)據(jù)源
  • 單表數(shù)據(jù)源
  • 多表數(shù)據(jù)源
  • 子查詢數(shù)據(jù)源
• where子句
  • 比較運(yùn)算
  • 邏輯運(yùn)算
  • 空運(yùn)算
• group by子句
  • 聚合函數(shù)
  • 回溯統(tǒng)計(jì)
• having子句
• order by子句
• limit子句
  • 分頁制作

1、查詢選項(xiàng)

概念

查詢選項(xiàng)：用于對(duì)查詢結(jié)果進(jìn)行簡單數(shù)據(jù)篩選

• 查詢選項(xiàng)是在select關(guān)鍵字之后，有兩個(gè)互斥值
  • all：默認(rèn)，表示保留所有記錄
  • distinct：去重，重復(fù)的記錄（所有字段都重復(fù)）

步驟

1、查詢的結(jié)果需要去除重復(fù)記錄

2、使用distinct查詢選項(xiàng)去重（默認(rèn)就是all保留全部）

示例

查看商品表中所有品類的商品信息：重復(fù)的商品只保留一次（名字、價(jià)格、屬性都一致）

create table t_39(
    id int primary key auto_increment,
    goods_name varchar(50) not null,
    goods_price decimal(10,2) default 0.00,
    goods_color varchar(20),
    goods_weight int unsigned comment '重量，單位克'
)charset utf8;

insert into t_39 values(null,'mate10',5499.00,'blue',320),
(null,'mate10',5499.00,'gray',320),
(null,'nokia3301',1299,'black',420);

# 考慮所有字段的去重（不含邏輯主鍵）
select distinct goods_name,goods_price,goods_color,goods_weight from t_39;
select goods_name,goods_price,goods_color,goods_weight from t_39; # 保留所有

# 不考慮顏色去重
select distinct goods_name,goods_price,goods_weight from t_39;
select all goods_name,goods_price,goods_weight from t_39;

小結(jié)

1、select選項(xiàng)主要是用來進(jìn)行數(shù)據(jù)全保留或者去重選擇的

• all：默認(rèn)，保留全部（關(guān)鍵字可以沒有）
• distinct：手動(dòng)選擇，去重（針對(duì)所選字段構(gòu)成的記錄，而不是某個(gè)字段）

2、distinct選項(xiàng)一般用于制作數(shù)據(jù)報(bào)表時(shí)使用

2、字段選擇&別名

概念

字段選擇：根據(jù)實(shí)際需求選擇的要獲取數(shù)據(jù)的字段信息

• 根據(jù)實(shí)際需求，明確所需要的字段名字，使用英文逗號(hào),分隔
• 獲取所有字段，使用星號(hào)*通配所有字段
• 字段數(shù)據(jù)可以不一定是來自數(shù)據(jù)源（select只要有結(jié)果即可）
  • 數(shù)據(jù)常量：select 1
  • 函數(shù)或者變量：select unix_timestamp(),@@version （@@是系統(tǒng)變量的前綴，后面跟變量名）

字段別名：給字段取的臨時(shí)名字

• 字段別名使用as語法實(shí)現(xiàn)
  • 字段名 as 別名
  • 字段名 別名
• 字段別名的目的通常為了保護(hù)數(shù)據(jù)
  • 字段沖突：多張表同時(shí)操作有同名字段（系統(tǒng)默認(rèn)覆蓋），想保留全部
  • 數(shù)據(jù)安全：對(duì)外提供數(shù)據(jù)不使用真實(shí)字段名字

步驟

1、明確需要查詢的字段信息

• 全部：*
• 部分：確定字段列表

2、確定存在數(shù)據(jù)沖突或者需要數(shù)據(jù)保護(hù)（通常可以理解為對(duì)外提供給別的系統(tǒng)訪問）

• 使用別名

示例

1、查詢商品信息

# 全部查詢
select * from t_39;

# 需求為商品名字和價(jià)格
select goods_name,goods_price from t_39;

# 別名使用
select goods_name as gn,goods_price gp from t_39;

2、不需要數(shù)據(jù)源的數(shù)據(jù)獲?。簊elect的表達(dá)式本身能算出結(jié)果）

# 獲取當(dāng)前時(shí)間戳和版本號(hào)
select unix_timestamp() as now,@@version as version,@@version;

小結(jié)

1、字段的選擇只要在保證數(shù)據(jù)需求能實(shí)現(xiàn)的情況下，盡可能少使用*代替（MySQL優(yōu)化）

• 減少服務(wù)器的數(shù)據(jù)讀取壓力
• 減少網(wǎng)絡(luò)傳輸壓力
• 讓客戶端能夠精確解析數(shù)據(jù)（不用大海撈針）

2、字段別名的靈活使用一方面可以保證原始數(shù)據(jù)的安全，也可以為數(shù)據(jù)使用者提供便利

• 同名字段覆蓋問題（連表操作學(xué)習(xí)時(shí)會(huì)遇到）
• 原始字段保護(hù)
• 數(shù)據(jù)字段的簡化

3、select是SQL中用于取出數(shù)據(jù)的一種指令，這種指令未必一定需要從數(shù)據(jù)表取出數(shù)據(jù)，只要是本身能夠有數(shù)據(jù)的表達(dá)式，都可以使用select獲取

3、數(shù)據(jù)源

概念

數(shù)據(jù)源：from關(guān)鍵字之后，數(shù)據(jù)的來源。只要最終結(jié)果是一個(gè)二維表，都可以當(dāng)做數(shù)據(jù)源

• 單表數(shù)據(jù)源：數(shù)據(jù)源就是一張表 from 表名
• 多表數(shù)據(jù)源：數(shù)據(jù)來源是多張表（逗號(hào)分隔） from 表名1,表名2,...表名N
• 子查詢數(shù)據(jù)源：數(shù)據(jù)來源是一個(gè)查詢結(jié)果 from (select 字段列表 from 表名) as 別名
  • 數(shù)據(jù)源要求必須是一個(gè)表
  • 如果是查詢結(jié)果必須給起一個(gè)表別名
• 數(shù)據(jù)表也可以指定別名
  • 表名 as 別名
  • 表名 別名

示例

1、單表數(shù)據(jù)源：最簡單的數(shù)據(jù)源，直接從一個(gè)數(shù)據(jù)表獲取

select * from t_27;

2、多表數(shù)據(jù)源：利用一張表的一條數(shù)據(jù)匹配另外一張表的所有記錄，記錄結(jié)果為：記錄數(shù) = 表1記錄數(shù) * 表2記錄數(shù)；字段數(shù) = 表1字段數(shù) + 表2字段數(shù)（笛卡爾積）

select * from t_27,t_30;

3、子查詢數(shù)據(jù)源：數(shù)據(jù)來源是一個(gè)select對(duì)應(yīng)的查詢結(jié)果

• 查詢語句需要使用括號(hào)包裹
• 查詢結(jié)果需要指定別名

select * from (select * from t_27,t_30) t; # 數(shù)據(jù)有沖突查不出來
select * from (select * from t_27) as t;

4、如果有時(shí)候名字較長或者使用不方便，可以利用表別名

select * from t_30 as t;

select t1.*,t2.stu_name from t_27 as t1,t_30 t2;

• 一般情況下別名設(shè)置是為了后續(xù)條件中可以直接使用別名
• 如果多表操作下，可以使用表別名來明確提取表字段

小結(jié)

1、數(shù)據(jù)源是為查詢、檢索提供數(shù)據(jù)支持的，使用時(shí)需要明確指定

2、通常情況下數(shù)據(jù)源不會(huì)使用簡單的多表數(shù)據(jù)源（笛卡爾積）

3、數(shù)據(jù)表的別名在負(fù)責(zé)SQL查詢操作時(shí)非常有用，而且有些地方是必須使用（如子查詢數(shù)據(jù)源）

4、where子句

where子句：跟在from數(shù)據(jù)源之后，對(duì)數(shù)據(jù)進(jìn)行條件匹配

• where是在磁盤讀取后，進(jìn)入內(nèi)存之前進(jìn)行篩選
  • 不符合條件的數(shù)據(jù)不會(huì)進(jìn)入內(nèi)存
• where篩選的內(nèi)容因?yàn)檫€沒進(jìn)入內(nèi)存，所以數(shù)據(jù)是沒有被加工過的
  • 字段別名不能在where中使用

步驟

1、確定要查詢的數(shù)據(jù)需要進(jìn)行條件篩選

2、使用where進(jìn)行數(shù)據(jù)篩選

示例

1、查詢t_35表中學(xué)生為lily的成績信息

select * from t_35 where stu_name = 'Lily';

2、因?yàn)閣here是在磁盤取數(shù)據(jù)時(shí)進(jìn)行條件篩選，此時(shí)數(shù)據(jù)沒有進(jìn)入內(nèi)存，所以字段別名是無效的

# 錯(cuò)誤
select stu_name name,score from t_35 where name = 'Lily';

小結(jié)

1、where是用來匹配條件篩選數(shù)據(jù)的

2、where是在數(shù)據(jù)從磁盤取出，還沒進(jìn)入內(nèi)存前進(jìn)行篩選：篩選過后合適的才會(huì)進(jìn)入到內(nèi)存（后續(xù)才能顯示）

3、成熟的項(xiàng)目中幾乎每次執(zhí)行查詢都是會(huì)使用條件查詢的

5、運(yùn)算符

概念

運(yùn)算符：用于進(jìn)行運(yùn)算的符號(hào)

• 比較運(yùn)算符
  • >（大于）、<（小于）、=（等于）、>=（大于等于）、<=（小于等于）、<>（不等于）
  • between A and B：A和B之間（A小于B），包括A和B本身（數(shù)值比較）
  • in (數(shù)據(jù)1,數(shù)據(jù)2,...數(shù)據(jù)N)：在列舉的數(shù)據(jù)之中
  • like 'pattern'：像上面樣的，用于字符串比較
    • _：單下劃線，匹配對(duì)應(yīng)位置的一個(gè)任意字符（ab_：ab開頭+一個(gè)字符，匹配abc，ab1，但不能匹配abcd）
    • %：匹配當(dāng)前位置（往后）任意數(shù)量任意字符（ab%：ab開頭+任意數(shù)量任意字符，匹配abc，ab1，abcd）
• 邏輯運(yùn)算符
  • and（邏輯與）、or（邏輯或）、not（邏輯非）
• null運(yùn)算符
  • is null（為空）、is not null（不為空）

步驟

1、確定需要使用運(yùn)算符進(jìn)行運(yùn)算

2、根據(jù)數(shù)據(jù)要求使用準(zhǔn)確的運(yùn)算符

示例

1、查詢成績不及格的所有學(xué)生信息

# 成績條件：成績是數(shù)值，又是比大小，可以直接使用比較運(yùn)算符
select * from t_35 where score < 60;

2、查詢成績在60-90間的學(xué)生信息

# 成績條件：區(qū)間60到90，可以有兩種解決方案

select * from t_35 where score between 60 and 90;
select * from t_35 where score >= 60 and score <= 90;

3、查詢還沒有成績的學(xué)生

# 成績條件：成績?yōu)閚ull，所以不能用比較符號(hào)查，只能使用is null實(shí)現(xiàn)
select * from t_35 where score is null;

小結(jié)

1、運(yùn)算符可以用來進(jìn)行字段數(shù)據(jù)運(yùn)算，配合where進(jìn)行條件篩選

2、基本運(yùn)算符與其他編程語言都差不多，SQL中有幾個(gè)特殊的要了解一下

• between and：一種>= and <=的便捷寫法
• in：用來做具體選擇，類似于switch里的case
• is null/is not null：字段值為Null的判定方式

3、熟練應(yīng)用的前提是不斷熟練的使用，掌握每個(gè)運(yùn)算符帶來的結(jié)果和效果

6、group by子句

概念

group by子句：分組統(tǒng)計(jì)，根據(jù)某個(gè)字段將所有的結(jié)果分類，并進(jìn)行數(shù)據(jù)統(tǒng)計(jì)分析

• 分組的目的不是為了顯示數(shù)據(jù)，一定是為了統(tǒng)計(jì)數(shù)據(jù)
• group by子句一定是出現(xiàn)在where子句之后（如果同時(shí)存在）
• 分組統(tǒng)計(jì)可以進(jìn)行統(tǒng)計(jì)細(xì)分：先分大組，然后大組分小組
• 分組統(tǒng)計(jì)需要使用統(tǒng)計(jì)函數(shù)
  • group_concat()：將組里的某個(gè)字段全部保留
  • any_value()：不屬于分組字段的任意一個(gè)組里的值
  • count()：求對(duì)應(yīng)分組的記錄數(shù)量
    • count(字段名)：統(tǒng)計(jì)某個(gè)字段值的數(shù)量（NULL不統(tǒng)計(jì)）
    • count(*)：統(tǒng)計(jì)整個(gè)記錄的數(shù)量（較多）
  • sum()：求對(duì)應(yīng)分組中某個(gè)字段是和
  • max()/min()：求對(duì)應(yīng)分組中某個(gè)字段的最大/最小值
  • avg()：求對(duì)應(yīng)分組中某個(gè)字段的平均值

步驟

1、確定要進(jìn)行數(shù)據(jù)統(tǒng)計(jì)

2、確定統(tǒng)計(jì)對(duì)象：分組字段（可以多個(gè)）

3、確定要統(tǒng)計(jì)的數(shù)據(jù)形式：選擇對(duì)應(yīng)統(tǒng)計(jì)函數(shù)

4、分組統(tǒng)計(jì)

示例

1、創(chuàng)建一張表，存儲(chǔ)學(xué)生信息

create table t_40(
id int primary key auto_increment,
name varchar(10) not null,
gender enum('男','女','保密'),
age tinyint unsigned not null,
class_name varchar(10) not null comment '班級(jí)名稱'
)charset utf8;

insert into t_40 values(null,'鳴人','男',18,'木葉1班'),
(null,'佐助','男',18,'木葉1班'),
(null,'佐井','男',19,'木葉2班'),
(null,'大蛇丸','男',28,'木葉0班'),
(null,'卡卡西','男',29,'木葉0班'),
(null,'小櫻','女',18,'木葉1班'),
(null,'雛田','女',18,'木葉1班'),
(null,'我愛羅','男',19,'木葉1班'),
(null,'向日葵','女',6,'木葉10班'),
(null,'博人','男',8,'木葉10班'),
(null,'鼬','男',28,'木葉0班');

2、統(tǒng)計(jì)每個(gè)班的人數(shù)

select count(*),class_name from t_40 group by class_name;

3、多分組：統(tǒng)計(jì)每個(gè)班的男女學(xué)生數(shù)量

select count(*),class_name,gender from t_40 group by class_name,gender;

4、統(tǒng)計(jì)每個(gè)班里的人數(shù)，并記錄班級(jí)學(xué)生的名字

select count(*),group_concat(name),class_name from t_40 group by class_name;
select count(*),any_value(name),class_name from t_40 group by class_name;

分組原理

以統(tǒng)計(jì)班級(jí)學(xué)生為例

graph TB
A(獲取數(shù)據(jù)后分組開始)-->B[匹配班級(jí)名字分組]
B-->|木葉1班|C[木葉1班組<br>鳴人<br>佐助<br>小櫻<br>雛田<br>我愛羅]
B-->|木葉2班|D[木葉2班組<br>佐井]
B-->|木葉0班|E[木葉0班組<br>大蛇丸<br>卡卡西<br>鼬]
B-->|木葉10班|F[木葉10班組<br>博人<br>向日葵]
C-->G[統(tǒng)計(jì)結(jié)果<br>只對(duì)結(jié)果負(fù)責(zé)<br>結(jié)果就是函數(shù),而函數(shù)只對(duì)小組工作<br>木葉1班組5人 木葉2班組1人 木葉0班組3人 木葉10班組2人]
D-->G
E-->G
F-->G
G-->H[返回結(jié)果]
H-->I((分組結(jié)束))

小結(jié)

1、分組與統(tǒng)計(jì)是不分離的，分組必然要用到統(tǒng)計(jì)，而統(tǒng)計(jì)一旦使用實(shí)際上就進(jìn)行了分組

2、分組統(tǒng)計(jì)使用數(shù)據(jù)數(shù)據(jù)的查詢只能依賴統(tǒng)計(jì)函數(shù)和被分組字段，而不能是其他字段（MySQL7以前可以，不過數(shù)據(jù)沒意義：因?yàn)橄到y(tǒng)只保留組里的第一個(gè)）

3、group by子句有自己明確的位置：在where之后（where可以沒有）

7、回溯統(tǒng)計(jì)

概念

回溯統(tǒng)計(jì)：在進(jìn)行分組時(shí)（通常是多分組），每一次結(jié)果的回溯都進(jìn)行一次匯總統(tǒng)計(jì)

• 回溯統(tǒng)計(jì)語法：在統(tǒng)計(jì)之后使用 with rollup

步驟

1、確定要進(jìn)行分組統(tǒng)計(jì)

2、確定是多分組統(tǒng)計(jì)

3、需要對(duì)每次分組結(jié)果進(jìn)行匯總

4、使用回溯統(tǒng)計(jì)

示例

統(tǒng)計(jì)每個(gè)班的男女同學(xué)數(shù)量，同時(shí)要知道班級(jí)人數(shù)總數(shù)

# 只統(tǒng)計(jì)每個(gè)班的男女同學(xué)數(shù)量，沒有班級(jí)匯總
select count(*),class_name,gender,group_concat(name) from t_40 group by class_name,gender;

# 匯總統(tǒng)計(jì)：回溯
select count(*),class_name,gender,group_concat(name) from t_40 group by class_name,gender with rollup;

回溯統(tǒng)計(jì)原理

graph TB
A(統(tǒng)計(jì)開始)-->B[大分組:班級(jí)名字分組]
B-->|木葉1班|C[木葉1班組<br>鳴人<br>佐助<br>小櫻<br>雛田<br>我愛羅]
B-->|木葉2班|D[木葉2班組<br>佐井]
B-->|木葉0班|E[木葉0班組<br>大蛇丸<br>卡卡西<br>鼬]
B-->|木葉10班|F[木葉10班組<br>博人<br>向日葵]
C-->G[小分組:性別分組]
G-->|男|H[木葉1班組:男<br>鳴人<br>佐助<br>我愛羅]
G-->|女|I[木葉1班組:女<br>小櫻<br>雛田]
D-->J[小分組:性別分組]
J-->|男|K[木葉2班組:男<br>佐井]
E-->L[小分組:性別分組]
L-->|男|M[木葉0班組:男<br>大蛇丸<br>卡卡西<br>鼬]
F-->N[小分組:性別分組]
N-->|男|O[木葉10班組:男<br>博人]
N-->|女|P[木葉10班組:女<br>向日葵]
H-->Q[性別回溯<br>木葉1班組:NULL<br>鳴人<br>佐助<br>小櫻<br>雛田<br>我愛羅]
I-->Q
K-->R[性別回溯<br>木葉2班組:NULL<br>佐井]
M-->S[性別回溯<br>木葉0班組:NULL<br>大蛇丸<br>卡卡西<br>鼬]
O-->T[性別回溯<br>木葉10班組:NULL<br>博人<br>向日葵]
P-->T
Q-->U[班級(jí)名字回溯<br>NULL:NULL<br>全部人]
R-->U
S-->U
T-->U
U-->V((回溯統(tǒng)計(jì)結(jié)束))

小結(jié)+

1、回溯統(tǒng)計(jì)一般用在多字段分組中，用來統(tǒng)計(jì)各級(jí)分組的匯總數(shù)據(jù)

2、因?yàn)榛厮萁y(tǒng)計(jì)會(huì)將對(duì)應(yīng)的分組字段置空（不置空無法合并），所以回溯的數(shù)據(jù)還需要經(jīng)過其他程序語言加工處理才能取出數(shù)據(jù)來

8、分組排序

概念

分組排序：在分組后統(tǒng)計(jì)結(jié)果時(shí)可以根據(jù)分組字段進(jìn)行升序或者降序顯示數(shù)據(jù)

• 默認(rèn)的系統(tǒng)就會(huì)自動(dòng)對(duì)分組結(jié)果根據(jù)分組字段進(jìn)行升序排序
• 可以設(shè)定分組結(jié)果的排序方式
  • group by 字段名 [ASC]：升序排序（默認(rèn)）
  • group by 字段名 DESC：降序排序

步驟

1、確定使用分組統(tǒng)計(jì)

2、需要對(duì)結(jié)果進(jìn)行降序排序（升序不用管）

3、使用分組降序排序

示例

對(duì)分組結(jié)果女性優(yōu)先顯示：gender為枚舉，男值為1，女值為2

select count(*),class_name,gender,group_concat(name),any_value(name) from t_40 group by class_name,gender desc;

小結(jié)

1、分組排序是針對(duì)分組的結(jié)果，通過分組字段進(jìn)行排序

2、一般情況下較少使用分組排序

9、having子句

概念

having子句：類似于where子句，是用來進(jìn)行條件篩選數(shù)據(jù)的

• having子句本身是針對(duì)分組統(tǒng)計(jì)結(jié)果進(jìn)行條件篩選的

• having子句必須出現(xiàn)在group by子句之后（如果同時(shí)存在）

• having針對(duì)的數(shù)據(jù)是在內(nèi)存里已經(jīng)加載的數(shù)據(jù)

• having幾乎能做where能做的所有事，但是where卻不一定

  • 字段別名（where針對(duì)磁盤數(shù)據(jù)，那時(shí)還沒有）
  • 統(tǒng)計(jì)結(jié)果（where在group by之前）
  • 分組統(tǒng)計(jì)函數(shù)（having通常是針對(duì)group by存在的）

步驟

1、前面有分組統(tǒng)計(jì)

2、需要針對(duì)分組統(tǒng)計(jì)后的結(jié)果進(jìn)行數(shù)據(jù)篩選

3、使用having組織條件進(jìn)行篩選

示例

1、獲取班級(jí)人數(shù)小于3的班級(jí)

select count(*) as `count`,class_name,group_concat(name) from t_40 group by class_name having `count` < 3;

select count(*) as `count`,class_name,group_concat(name) from t_40 group by class_name having count(*) < 3; # 多用了一次函數(shù)（效率降低）

select class_name,group_concat(name) from t_40 group by class_name having count(*) < 3; # 沒辦法，前面沒統(tǒng)計(jì)，只能自己統(tǒng)計(jì)

小結(jié)

1、having也是用于數(shù)據(jù)篩選的，但是本質(zhì)是針對(duì)分組統(tǒng)計(jì)，如果沒有分組統(tǒng)計(jì)，不要使用having進(jìn)行數(shù)據(jù)篩選

2、能用where解決問題的地方絕不使用having

• where針對(duì)磁盤讀取數(shù)據(jù)，源頭解決問題
• where能夠限制無效數(shù)據(jù)進(jìn)入內(nèi)存，內(nèi)存利用率較高，而having是針對(duì)內(nèi)存數(shù)據(jù)篩選

10、order by子句

概念

order by子句：排序，根據(jù)某個(gè)指定的字段進(jìn)行升序或者降序排序

• 排序的參照物是校對(duì)集
• order by子句在having子句字后（如果同時(shí)存在）
• 排序分為升序和降序：默認(rèn)是升序
  • order by 字段 [ASC]：升序
  • order by 字段 DESC：降序
• 多字段排序：在根據(jù)某個(gè)字段排序好后，可以再細(xì)分排序

步驟

1、確定需要排序的字段

2、確定排序的方式：升序 or 降序

3、使用排序

示例

1、單字段排序：給所有學(xué)生按照年紀(jì)大小升序排序

select * from t_40 order by age;
select * from t_40 order by age asc;

2、多字段排序：先性別降序排序，然后按年齡升序排序

select * from t_40 order by gender desc,age;
select * from t_40 order by gender desc,age asc;

小結(jié)

1、排序是針對(duì)前面所得到的結(jié)果進(jìn)行排序（已經(jīng)進(jìn)入到內(nèi)存的數(shù)據(jù)）

2、多字段排序是在第一個(gè)字段排好序的情況下，不改變原來排序的基調(diào)后，再小范圍排序（類似分組）

3、實(shí)際開發(fā)中排序的使用非常常見，尤其是在數(shù)值、時(shí)間上多見

11、limit子句

概念

limit子句：限制數(shù)據(jù)的獲取數(shù)量（記錄數(shù)）

• limit子句必須在order by子句之后（如果同時(shí)存在）
• limit限制數(shù)量的方式有兩種
  • limit 數(shù)量：限制獲取的數(shù)量（不保證一定能獲取到指定數(shù)量）
  • limit 起始位置,數(shù)量：限制數(shù)據(jù)獲取的位置以及數(shù)量（分頁）

步驟

1、確定要對(duì)記錄數(shù)進(jìn)行限制

2、確定限制的方式：限定數(shù)量 or 限定位置+限定數(shù)量

示例

1、獲取t_40表中前3條數(shù)據(jù)

select * from t_40 limit 3;

2、獲取t_40表中第3條以后的3條數(shù)據(jù)

select * from t_40 limit 3,3;
select * from t_40 limit 6,3;

小結(jié)

1、limit限制數(shù)量可以有效的減少服務(wù)器的壓力和傳輸壓力

2、常利用limit來實(shí)現(xiàn)分頁獲取數(shù)據(jù)

12、總結(jié)

1、查詢操作是所有操作里使用的最多也是最終的操作

2、查詢操作的完整語法

select select選項(xiàng) 字段列表[別名]|* from 數(shù)據(jù)源[別名] where子句 group by子句 having子句 order by子句 limit 子句;

• 各個(gè)位置的順序不能調(diào)換
• 五子句（where、group by、having、order by、limit）可以沒有，但是出現(xiàn)后一定要保證順序
• group by到最后都是針對(duì)已經(jīng)加載帶內(nèi)存中的數(shù)據(jù)進(jìn)行加工處理

3、很多結(jié)構(gòu)的組合其實(shí)可以達(dá)到同一效果，但是可能過程和效率會(huì)不同

三、數(shù)據(jù)更新

概念

限制更新：即更新時(shí)對(duì)更新的記錄數(shù)進(jìn)行限制

• 限制更新通過limit來實(shí)現(xiàn)
• 限制更新其實(shí)是局部更新的一種手段，一般更多情況下是依據(jù)條件精確更新

步驟

1、確定要進(jìn)行批量更新：符合條件的部分

2、確定更新的數(shù)量

3、使用limit限制更新數(shù)量

示例

對(duì)會(huì)員選3個(gè)發(fā)送10元紅包（添加到賬戶）

create table t_41(
    id int primary key auto_increment,
    username varchar(50) not null unique,
    password char(32) not null,
    account decimal(10,2) default 0.00
)charset utf8;

insert into t_41 values(null,'username1','password',default),
(null,'username2','password',default),
(null,'username3','password',default),(null,'username4','password',default),(null,'username5','password',default);


update t_41 set account = account + 10 limit 3;

小結(jié)

1、限制更新可以實(shí)現(xiàn)批量小范圍操作

2、實(shí)際開發(fā)當(dāng)中，極少出現(xiàn)這類操作，一般都愿意精準(zhǔn)操作（利用where條件明確更新條件）

3、更新操作不可逆

四、數(shù)據(jù)刪除

• 限制刪除
• 清空數(shù)據(jù)

1、限制刪除

概念

限制刪除：限制要?jiǎng)h除的記錄數(shù)

• 使用limit限制刪除數(shù)量
• 一般很少使用限制刪除，通常是通過where條件精確刪除

步驟

1、確定要進(jìn)行數(shù)據(jù)批量刪除

2、確定通過where條件限定后無法完全滿足刪除條件

3、使用limit限制刪除數(shù)量完成目標(biāo)

示例

刪除沒有賬戶余額的一個(gè)用戶（當(dāng)前用戶量少，一般數(shù)量會(huì)大些）

delete from t_41 where account = 0 limit 1;

小結(jié)

1、限制刪除本質(zhì)也是刪除，操作不可逆，謹(jǐn)慎使用

2、更愿意使用精確刪除

2、清空數(shù)據(jù)

概念

清空數(shù)據(jù)：將表中的所有數(shù)據(jù)清除，并且將表的所有狀態(tài)回到原始狀態(tài)

• 清空數(shù)據(jù)的本質(zhì)是先刪除表，后創(chuàng)建表
• 清空數(shù)據(jù)能夠讓表的一些變化狀態(tài)回到原始狀態(tài)
  • 自增長重新回到初始值
• 清空語法： truncate 表名

步驟

1、確定要?jiǎng)h除表中所有數(shù)據(jù)

2、確定需要讓表狀態(tài)回到原始

3、truncate清空數(shù)據(jù)

示例

清空用戶數(shù)據(jù)表

truncate t_41;

小結(jié)

1、清空數(shù)據(jù)表是一種比delete更徹底的數(shù)據(jù)刪除方式，所以使用之前必須要慎重

2、一般只會(huì)在開發(fā)階段才會(huì)使用這種數(shù)據(jù)刪除操作，如表數(shù)據(jù)發(fā)生錯(cuò)亂，或者業(yè)務(wù)發(fā)生變化