4.更新数据

在mysql中，可以使用UPDATE语句更新表中的记录，可以更新特定的行或者同时更新所有行。语法结构如下：

UPDATE table_name
SET column_name1 = value1,column_name2 = value2,...,column_namen = valuen
WHERE (condition);

column_namen为要更新的字段的名称；valuen为相对应的指定字段的更新值；condition是更新的记录需要满足的条件。更新多个列时，每个“列——值”对之间用逗号隔开，最后一列之后不需要逗号。
例：在person表中，更新id值为11的记录，将age字段值改为15，将name字段值改为LiMing.SQL语句如下：

mysql> UPDATE person SET name = 'LiMing',age = 15 WHERE id = 11;
Query OK, 1 row affected (0.28 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM person WHERE id=11;
+----+--------+-----+---------+
| id | name   | age | info    |
+----+--------+-----+---------+
| 11 | LiMing |  15 | student |
+----+--------+-----+---------+
1 row in set (0.00 sec)

有结果可以看到，id=11的记录中name和age字段的值已经被成功修改。
注意：UPDATE语句以WHERE子句结束，通过WHERE子句指定被更新的记录所需要满足的条件，如果忽略WHERE子句，MySQL将更新表中所有的行。

例：在person表中，更新age值为19-22的记录，将info字段值都改为student：
更新前：

mysql> SELECT * FROM person WHERE age BETWEEN 19 AND 22;
+----+--------+-----+------------+
| id | name   | age | info       |
+----+--------+-----+------------+
|  1 | Green  |  21 | Lawyer     |
|  2 | Suse   |  22 | dancer     |
|  4 | Willam |  20 | sports man |
|  7 | Dale   |  22 | cook       |
+----+--------+-----+------------+
4 rows in set (0.05 sec)

更新：

mysql> UPDATE person SET info='student' WHERE age BETWEEN 19 AND 22;
Query OK, 4 rows affected (0.06 sec)
Rows matched: 4  Changed: 4  Warnings: 0

更新后：

mysql> SELECT * FROM person WHERE age BETWEEN 19 AND 22;
+----+--------+-----+---------+
| id | name   | age | info    |
+----+--------+-----+---------+
|  1 | Green  |  21 | student |
|  2 | Suse   |  22 | student |
|  4 | Willam |  20 | student |
|  7 | Dale   |  22 | student |
+----+--------+-----+---------+
4 rows in set (0.00 sec)