文章目錄

資料新增
- 1、批量插入
- 2、蠕蟲復制
- 3、主鍵沖突
資料查詢
- 1、查詢選項 distinct
- 2、欄位選擇&別名 as
- 3、資料源 from
- 4、where子句
- 5、group by子句
- 6、having子句
- 7、order by子句
- 8、limit子句
資料更新
- 1、限制更新
資料洗掉
- 1、限制洗掉
- 2、清空資料

資料新增

1、批量插入

批量插入：是一種優化資料逐條插入的方式

批量插入資料的語法與簡單資料插入的語法差不多
批量插入分為兩種：
- 全欄位批量插入： insert into 表名 values(值串列1),(值串列2),...(值串列N);
- 部分欄位批量插入（注意欄位默認值）： insert into 表名 (欄位串列) values (值串列1),(值串列2),...(值串列N);
批量插入可以針對性解決資料的批量匯入之類的業務，可以一次性解決多條資料插入，能夠有效降低客戶端占用問題，提升資料操作效率
- MySQL8以后默認有事務安全，即批量要么都成功要么都失敗，不會出現部分問題

1、批量插入學生成績（t_1全欄位）

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

2、批量插入學生考試資訊（t_1不包含成績）

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

2、蠕蟲復制

蠕蟲復制：從已有表中復制資料直接插入到另外一張表（同一張表）

蠕蟲復制的目標是快速增加表中的資料
- 實作表中資料復制（用于資料備份或者遷移）
- 實作資料的指數級遞增（多用于測驗）
蠕蟲復制語法：insert into 表名 [(欄位串列)] select 欄位串列 from 表名;
注意事項：
- 欄位串列必須對應上
- 欄位型別必須匹配上
- 資料沖突需要事先考慮

1、創建一張新表，將t_1表中的資料遷移到新表t_2中

create table t_1(
	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_2 select * from t_1;

2、快速讓t_2表中的資料增長（重復執行）

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

3、主鍵沖突

主鍵沖突：在資料進行插入時包含主鍵指定，而主鍵在資料表已經存在

主鍵沖突的業務通常是發生在業務主鍵上（業務主鍵本身有業務意義）
主鍵沖突的解決方案:
- 忽略沖突：保留原始記錄: insert ignore into 表名 [(欄位串列)] values(值串列);
- 沖突更新：沖突后部分欄位變成更新: insert into 表名 [(欄位串列)] values(值串列) on duplicate key update 欄位 = 新值[,欄位=新值...];
- 沖突替換：先洗掉原有記錄，后新增記錄: replace into 表名 [(欄位串列)] values(值串列); # 效率沒有insert高（需要檢查是否沖突）
從效率上來講，insert into不考慮沖突的效率最高，三種解決沖突的方式都會有效率下降（需要檢索），其中三種本身的效率依次是：忽略新資料 > 更新部分資料 > 替換全部

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

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

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

2、用戶名作為主鍵的記錄用戶使用資訊（不存在新增、存在則更新時間）：username，logintime

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

insert into t_4 values('username',12345678);	# 當前時間戳

# 沖突更新（替換部分欄位資料）
insert into t_4 values('username',12345678) on duplicate key update logintime = unix_timestamp();	# 當前時間戳

如果主鍵不沖突：新增
如果主鍵沖突：更新指定欄位
上述方式適用于欄位較多，但是可能沖突時資料變化的欄位較少

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

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

insert into t_5 values('username',unix_timestamp(),'{PC:chrome}');

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

replace遇到主鍵重復就會先洗掉、后新增
如果有較多欄位需要更新：建議使用替換

資料查詢

1、查詢選項 distinct

查詢選項：用于對查詢結果進行簡單資料篩選

查詢選項是在select關鍵字之后，有兩個互斥值
- all：默認，表示保留所有記錄（關鍵字可以沒有）
- distinct：去重，重復的記錄（針對所選欄位構成的記錄，而不是某個欄位）

1、查看商品表中所有品類的商品資訊：重復的商品只保留一次（名字、價格、屬性都一致）

create table t_6(
	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_6 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_6;
select goods_name,goods_price,goods_color,goods_weight from t_6; # 保留所有

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

2、欄位選擇&別名 as

欄位選擇：根據實際需求選擇的要獲取資料的欄位資訊

根據實際需求，明確所需要的欄位名字，使用英文逗號,分隔
獲取所有欄位，使用星號*通配所有欄位
欄位資料可以不一定是來自資料源（select只要有結果即可）
- 資料常量：select 1
- 函式或者變數：select unix_timestamp(),@@version （@@是系統變數的前綴，后面跟變數名）
欄位的選擇只要在保證資料需求能實作的情況下，盡可能少使用*代替（MySQL優化）
- 減少服務器的資料讀取壓力
- 減少網路傳輸壓力
- 讓客戶端能夠精確決議資料（不用大海撈針）

欄位別名：給欄位取的臨時名字

欄位別名使用as語法實作：
- 欄位名 as 別名
- 欄位名別名
欄位別名的目的通常為了保護資料
- 欄位沖突：多張表同時操作有同名欄位（系統默認覆寫），想保留全部
- 資料安全：對外提供資料不使用真實欄位名字
欄位別名的靈活使用一方面可以保證原始資料的安全，也可以為資料使用者提供便利
- 同名欄位覆寫問題（連表操作學習時會遇到）
- 原始欄位保護
- 資料欄位的簡化
select是SQL中用于取出資料的一種指令，這種指令未必一定需要從資料表取出資料，只要是本身能夠有資料的運算式，都可以使用select獲取

1、查詢商品資訊

# 全部查詢
select * from t_6;

# 需求為商品名字和價格
select goods_name,goods_price from t_6;

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

2、不需要資料源的資料獲取：select的運算式本身能算出結果）

# 獲取當前時間戳和版本號
select unix_timestamp() as now,@@version as version,@@version;

3、資料源 from

資料源：from關鍵字之后，資料的來源，只要最終結果是一個二維表，都可以當做資料源

單表資料源：資料源就是一張表 from 表名
多表資料源：資料來源是多張表（逗號分隔） from 表名1,表名2,...表名N
子查詢資料源：資料來源是一個查詢結果 from (select 欄位串列 from 表名) as 別名
- 資料源要求必須是一個表
- 如果是查詢結果必須給起一個表別名
資料表也可以指定別名
- 表名 as 別名
- 表名別名
資料源是為查詢、檢索提供資料支持的，使用時需要明確指定
通常情況下資料源不會使用簡單的多表資料源（笛卡爾積）
資料表的別名在負責SQL查詢操作時非常有用，而且有些地方是必須使用（如子查詢資料源）

1、單表資料源：最簡單的資料源，直接從一個資料表獲取

select * from t_7;

2、多表資料源：利用一張表的一條資料匹配另外一張表的所有記錄，記錄結果為：

記錄數 = 表1記錄數 * 表2記錄數
欄位數 = 表1欄位數 + 表2欄位數（笛卡爾積）

select * from t_7,t_8;

3、子查詢資料源：資料來源是一個select對應的查詢結果

查詢陳述句需要使用括號包裹
查詢結果需要指定別名

select * from (select * from t_7,t_8) t; # 資料有沖突查不出來
select * from (select * from t_7) as t;

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

select * from t_1 as t;

select t1.*,t2.stu_name from t_1 as t1,t_2 t2;

一般情況下別名設定是為了后續條件中可以直接使用別名
如果多表操作下，可以使用表別名來明確提取表欄位

4、where子句

where子句：跟在from資料源之后，對資料進行條件匹配，篩選資料的

where是在磁盤讀取后，進入記憶體之前進行篩選
- 不符合條件的資料不會進入記憶體
where篩選的內容因為還沒進入記憶體，所以資料是沒有被加工過的
- 欄位別名不能在where中使用

1、查詢t_5表中學生為lily的成績資訊

select * from t_5 where stu_name = 'Lily';

2、因為where是在磁盤取資料時進行條件篩選，此時資料沒有進入記憶體，所以欄位別名是無效的

# 錯誤 
select stu_name name,score from t_5 where name = 'Lily';

運算子：用于進行運算的符號

運算子可以用來進行欄位資料運算，配合where進行條件篩選
比較運算子：
- >（大于）、<（小于）、=（等于）、>=（大于等于）、<=（小于等于）、<>（不等于）
- between A and B：A和B之間（A小于B），包括A和B本身（數值比較）
- in (資料1,資料2,...資料N)：在列舉的資料之中
- like 'pattern'：像上面樣的，用于字串比較
  - _：單下劃線，匹配對應位置的一個任意字符（ab_：ab開頭+一個字符，匹配abc，ab1，但不能匹配abcd）
  - %：匹配當前位置（往后）任意數量任意字符（ab%：ab開頭+任意數量任意字符，匹配abc，ab1，abcd）
邏輯運算子：
- and（邏輯與）、or（邏輯或）、not（邏輯非）
null運算子：
- is null（為空）、is not null（不為空）

1、查詢成績不及格的所有學生資訊

# 成績條件：成績是數值，又是比大小，可以直接使用比較運算子
select * from t_5 where score < 60;

2、查詢成績在60-90間的學生資訊

# 成績條件：區間60到90，可以有兩種解決方案
select * from t_5 where score between 60 and 90;
select * from t_5 where score >= 60 and score <= 90;

3、查詢還沒有成績的學生

# 成績條件：成績為null，所以不能用比較符號查，只能使用is null實作
select * from t_5 where score is null;

5、group by子句

group by子句：分組統計，根據某個欄位將所有的結果分類，并進行資料統計分析

分組的目的不是為了顯示資料，一定是為了統計資料
group by 子句一定是出現在where子句之后（如果同時存在）
分組統計可以進行統計細分：先分大組，然后大組分小組

聚合函式：

分組統計需要使用統計函式:
- group_concat()：將組里的某個欄位全部保留
- any_value()：不屬于分組欄位的任意一個組里的值
- count()：求對應分組的記錄數量
  - count(欄位名)：統計某個欄位值的數量（NULL不統計）
  - count(*)：統計整個記錄的數量（較多）
- sum()：求對應分組中某個欄位是和
- max()/min()：求對應分組中某個欄位的最大/最小值
- avg()：求對應分組中某個欄位的平均值
分組統計使用資料資料的查詢只能依賴統計函式和被分組欄位，而不能是其他欄位（MySQL7以前可以，不過資料沒意義：因為系統只保留組里的第一個）

1、創建一張表，存盤學生資訊

create table t_0(
	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 '班級名稱'
)charset utf8;

insert into t_0 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、統計每個班的人數

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

3、多分組：統計每個班的男女學生數量

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

4、統計每個班里的人數，并記錄班級學生的名字

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

分組原理:

以統計班級學生為例

回溯統計：在進行分組時（通常是多分組），每一次結果的回溯都進行一次匯總統計

回溯統計語法：在統計之后使用 with rollup
回溯統計一般用在多欄位分組中，用來統計各級分組的匯總資料
因為回溯統計會將對應的分組欄位置空（不置空無法合并），所以回溯的資料還需要經過其他程式語言加工處理才能取出資料來

統計每個班的男女同學數量，同時要知道班級人數總數

# 只統計每個班的男女同學數量，沒有班級匯總
select count(*),class_name,gender,group_concat(name) from t_40 group by class_name,gender;
# 匯總統計：回溯
select count(*),class_name,gender,group_concat(name) from t_40 group by class_name,gender with rollup;

回溯統計原理：

分組排序：在分組后統計結果時可以根據分組欄位進行升序或者降序顯示資料

默認的系統就會自動對分組結果根據分組欄位進行升序排序
可以設定分組結果的排序方式
- group by 欄位名 [ASC]：升序排序（默認）
- group by 欄位名 DESC：降序排序

1、對分組結果女性優先顯示：gender為列舉，男值為1，女值為2

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

6、having子句

having子句：類似于where子句，是用來進行條件篩選資料的

having 子句本身是針對分組統計結果進行條件篩選的，但是本質是針對分組統計，如果沒有分組統計，不要使用having進行資料篩選
having子句必須出現在group by子句之后（如果同時存在）
having針對的資料是在記憶體里已經加載的資料
having幾乎能做where能做的所有事，但是where卻不一定
- 欄位別名（where針對磁盤資料，那時還沒有）
- 統計結果（where在group by之前）
- 分組統計函式（having通常是針對group by存在的）
但是能用where解決問題的地方絕不使用having
- where針對磁盤讀取資料，源頭解決問題
- where能夠限制無效資料進入記憶體，記憶體利用率較高，而having是針對記憶體資料篩選

1、獲取班級人數小于3的班級

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

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

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

7、order by子句

order by子句：排序，根據某個指定的欄位進行升序或者降序排序

排序的參照物是校對集
排序是針對前面所得到的結果進行排序 （已經進入到記憶體的資料）
order by子句在having子句字后（如果同時存在）
排序分為升序和降序：默認是升序
- order by 欄位 [ASC]：升序
- order by 欄位 DESC：降序
多欄位排序：是在第一個欄位排好序的情況下，不改變原來排序的基調后，再小范圍排序（類似分組）
實際開發中排序的使用非常常見，尤其是在數值、時間上多見

1、單欄位排序：給所有學生按照年紀大小升序排序

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

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

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

8、limit子句

limit子句：限制資料的獲取數量（記錄數）

limit 子句必須在order by子句之后（如果同時存在）
limit限制數量的方式有兩種：
- limit 數量：限制獲取的數量（不保證一定能獲取到指定數量）
- limit 起始位置,數量：限制資料獲取的位置以及數量（分頁）
limit限制數量可以有效的減少服務器的壓力和傳輸壓力
常利用limit來實作分頁獲取資料

1、獲取t_0表中前3條資料

select * from t_0 limit 3;

2、獲取t_0表中第3條以后的3條資料

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

資料查詢總結:

查詢操作是所有操作里使用的最多也是最終的操作
查詢操作的完整語法：select select選項欄位串列[別名] /* from 資料源[別名] where子句 group by子句 having子句 order by子句 limit 子句;
- 各個位置的順序不能調換
- 五子句（where、group by、having、order by、limit）可以沒有，但是出現后一定要保證順序
- group by到最后都是針對已經加載帶記憶體中的資料進行加工處理
很多結構的組合其實可以達到同一效果，但是可能程序和效率會不同

資料更新

1、限制更新

限制更新：即更新時對更新的記錄數進行限制

限制更新通過 limit 來實作，來完成批量小范圍操作
限制更新其實是區域更新的一種手段，實際開發當中，極少出現這類操作，一般都愿意精準操作（利用where條件明確更新條件）
更新操作不可逆

1、對會員選3個發送10元紅包（添加到賬戶）

create table t_1(	
	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_1 values(null,'username1','password',default),
(null,'username2','password',default),
(null,'username3','password',default),
(null,'username4','password',default),
(null,'username5','password',default);

update t_1 set account = account + 10 limit 3;

資料洗掉

1、限制洗掉

限制洗掉：限制要洗掉的記錄數

使用 limit 限制洗掉數量
限制洗掉本質也是洗掉，操作不可逆，謹慎使用
一般很少使用限制洗掉，通常是通過where條件精確洗掉

1、洗掉沒有賬戶余額的一個用戶（當前用戶量少，一般數量會大些）

delete from t_1 where account = 0 limit 1;

2、清空資料

清空資料：將表中的所有資料清除，并且將表的所有狀態回到原始狀態

清空資料的本質是先洗掉表，后創建表
清空資料能夠讓表的一些變化狀態回到原始狀態
- 自增長重新回到初始值
清空語法： truncate 表名
清空資料表是一種比delete更徹底的資料洗掉方式，所以使用之前必須要慎重
一般只會在開發階段才會使用這種資料洗掉操作，如表資料發生錯亂，或者業務發生變化

1、清空用戶資料表

truncate t_1;

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/291559.html

標籤：其他

上一篇：Redis高可用架構設計以及常見問題分析

下一篇：你一定要知道的SQL優化技巧

MySQL 高級SQL操作（資料的增、刪、改、查）