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)

发布了28 篇原创文章 · 获赞 2 · 访问量 467
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览