极客教程如何在MySQL中更新字段以添加现有值？
您可以使用UPDATE和SET命令将值添加到现有值以更新字段。语法如下所示−
UPDATE yourTableName SET yourColumnName = yourColumnName+integerValueToAdd WHERE yourCondition;
要理解上述语法，让我们创建一个表。创建表的查询如下所示−
mysql> create table addingValueToExisting
-> (
-> Id int NOT NULL AUTO_INCREMENT,
-> Name varchar(30),
-> GameScore int,
-> PRIMARY KEY(Id)
-> );
Query OK, 0 rows affected (0.58 sec)
使用insert命令向表中插入记录。查询如下所示−
mysql> insert into addingValueToExisting(Name,GameScore) values('John',89);
Query OK, 1 row affected (0.11 sec)
mysql> insert into addingValueToExisting(Name,GameScore) values('Mike',56);
Query OK, 1 row affected (0.28 sec)
mysql> insert into addingValueToExisting(Name,GameScore) values('Sam',99);
Query OK, 1 row affected (0.18 sec)
mysql> insert into addingValueToExisting(Name,GameScore) values('Carol',100);
Query OK, 1 row affected (0.17 sec)
mysql> insert into addingValueToExisting(Name,GameScore) values('David',67);
Query OK, 1 row affected (0.25 sec)
mysql> insert into addingValueToExisting(Name,GameScore) values('Bob',78);
Query OK, 1 row affected (0.14 sec)
使用SELECT语句显示表中的所有记录。查询如下所示−
mysql> select *from addingValueToExisting;
输出如下所示−
+----+-------+-----------+
| Id | Name | GameScore |
+----+-------+-----------+
| 1 | John | 89 |
| 2 | Mike | 56 |
| 3 | Sam | 99 |
| 4 | Carol | 100 |
| 5 | David | 67 |
| 6 | Bob | 78 |
+----+-------+-----------+
6 rows in set (0.00 sec)
更新字段以添加值到现有值。对于我们的示例，让我们通过添加10来将值100更新为110。查询如下所示−
mysql> update addingValueToExisting set GameScore = GameScore+10 where Id = 4;
Query OK, 1 row affected (0.23 sec)
Rows matched − 1 Changed − 1 Warnings − 0
检查特定记录是否已更新。查询如下所示−
mysql> select *from addingValueToExisting where Id = 4;
输出如下所示−
+----+-------+-----------+
| Id | Name | GameScore |
+----+-------+-----------+
| 4 | Carol | 110 |
+----+-------+-----------+
1 row in set (0.00 sec)
看上面的输出，值100增加了10，现在为110。
阅读更多：MySQL 教程