MySQL 如何實現資料更新

酸菜魚土豆大俠發表於2023-02-08
MySql

一般在更新時會遇到以下場景：1.所有欄位全部更新；2.根據條件更新欄位中的某部分內容；3.根據不同的條件更新不同的值，以下是幾種場景中常用的update方法。

一、方法分類

更新資料方法分類

二、具體用法

（1）根據條件更新值

  • 根據指定條件更新(多列)（全部更新）

把表中 [符合條件的行的] 列名1欄位中的值全部修改為值1 [，列名2欄位中的值修改為值2]。

update 表名 
set 列名1 = 值1 [,列名2=值2]
[where 條件];
  • 替換指定值(多列)（部分更新）

把表中 [符合條件的行的] 列名1欄位中的查詢內容全部修改為替換內容 [，列名2欄位中的查詢內容全部修改為替換內容]。

update 表名 
set 列名1 = replace(列名1, '查詢內容', '替換內容') [,列名2 = replace(列名2, '查詢內容', '替換內容')] 
[where 條件];

（2）按照不同條件(批次)更新不同值

  • 使用 if

把表中 [符合條件的行的] 列名1欄位中符合條件1的內容修改為值1，否則修改為值2 [,列名2欄位中符合條件2的內容修改為值3，否則修改為值4]。

update table 
set 
	列名1 = if(條件1,值1,值2),
    列名2 = if(條件2,值3,值4)
[where 條件];
  • 使用 case when

把表中 [符合條件的行的] 列名1欄位中符合條件1的內容修改為值1 [,符合條件2的修改為值2,...] [,列名2欄位中符合條件21的內容修改為值21,符合條件22的修改為值22,...] 。

update table 
set 列名1 =
    case
        when 條件1 then 值1
        when 條件2 then 值2
        when 條件3 then 值3
        ...
    end,
    列名2 =
    case
        when 條件21 then 值21
        when 條件22 then 值22
        when 條件23 then 值23
        ...
    end
[where 條件];

三、例項

students 表 (id表示主鍵，name是姓名，score是平均成績)

id name score
1 李明 99
2 張三 74
3 孫華 59

（1）根據條件更新值

  • 把 students 表中 name 為張三的 score 欄位的值全部修改為100。
#使用where
update students 
set score = 100
where name = '張三';
id name score
1 李明 99
2 張三 100
3 孫華 59
  • 把 students 表中 id 大於等於2的所有行中 score 中59的部分全部修改為0，name 中三的部分全部修改為四。
#使用replace
update students 
set score = replace(score,59,0),
	name = replace(name,'三','四')
where id >= 2;

注意：張三替換之後是張四，並不是只有欄位等於三時才能替換。

id name score
1 李明 99
2 張四 74
3 孫華 0

（2）按照不同條件更新不同值

  • 請把students表中score小於60的score欄位全部改為0，否則改為100，name欄位中的名字改為不及格，否則改為及格。
#批次更新多值 + if
update students 
set 
    score = if(score < 60,0,100),
    name = if(score < 60,'不及格','及格');
id name score
1 及格 100
2 及格 100
3 不及格 0

注意：更新的值要滿足建表時的欄位型別。比如score是int型別就不能更新為char型別。

  • 請把students表中score小於60的score欄位全部改為0，name欄位中的名字改為不及格；score大於等於90的score欄位全部改為2，name欄位中的名字改為優秀；score大於等於60小於90的score欄位全部改為1，name欄位中的名字改為良好。
#批次更新多值 + case when
update students 
set 
    name = case
        when score < 60 then '不及格'
		when score >= 90 then '優秀'
        else '良好'
    end,
		score = case
        when score < 60 then 0
		when score >= 90 then 2
        else 1
    end;

注意：更新的時候是按照程式碼語句的先後順序更新的。可以嘗試先更新score後更新name，結果是不一樣的。

id name score
1 優秀 2
2 良好 1
3 不及格 0

相關文章