SQL 知识量:22 - 44 - 129
更新(修改)数据表中的数据,可以使用update语句,有两种使用方式:
更新表中的特定行。
更新表中的所有行。
update语句由三部分组成,分别是:
要更新的表。
列名和它们的新值。
确定要更新哪些行的过滤条件。
例如有一个person表如下:
+----+----------+---------+--------+ | id | realName | realAge | sex | +----+----------+---------+--------+ | 1 | Susan | 12 | female | | 2 | Jame | 12 | male | | 3 | Bob | 12 | male | | 4 | Robot | 12 | male | | 5 | Jen | 12 | female | | 6 | Toney | 12 | male | | 8 | Alice | 12 | female | | 9 | Bonnie | 12 | male | | 10 | Ella | 12 | female | | 11 | Bill | 15 | male | | 12 | Limon | 14 | male | | 13 | Soen | 16 | male | +----+----------+---------+--------+
现在更新这个表,将男性的年龄都设为18。
update person set realAge=18 where sex='male';
更新后的person表为:
+----+----------+---------+--------+ | id | realName | realAge | sex | +----+----------+---------+--------+ | 1 | Susan | 12 | female | | 2 | Jame | 18 | male | | 3 | Bob | 18 | male | | 4 | Robot | 18 | male | | 5 | Jen | 12 | female | | 6 | Toney | 18 | male | | 8 | Alice | 12 | female | | 9 | Bonnie | 18 | male | | 10 | Ella | 12 | female | | 11 | Bill | 18 | male | | 12 | Limon | 18 | male | | 13 | Soen | 18 | male | +----+----------+---------+--------+
除了更新一列数据外,还可以同时更新多列或所有列的数据。更新多列时,每个“列=值”对使用逗号分隔。例如:修改Soen的年龄为17,性别为female。
update person set realAge=17,sex='female' where realName='Soen';
更新后的person表为:
+----+----------+---------+--------+ | id | realName | realAge | sex | +----+----------+---------+--------+ | 1 | Susan | 12 | female | | 2 | Jame | 18 | male | | 3 | Bob | 18 | male | | 4 | Robot | 18 | male | | 5 | Jen | 12 | female | | 6 | Toney | 18 | male | | 8 | Alice | 12 | female | | 9 | Bonnie | 18 | male | | 10 | Ella | 12 | female | | 11 | Bill | 18 | male | | 12 | Limon | 18 | male | | 13 | Soen | 17 | female | +----+----------+---------+--------+
应当特别注意,不要忘记where子句,否则更新将应用于所有行。
如果要删除某行某个列的值,可以在更新时将其设为NULL。但是,前提是该列定义为可以为NULL。
例如:删除person表中,Soen的性别值。
update person set sex=null where realName='Soen';
删除后的person表为:
+----+----------+---------+--------+ | id | realName | realAge | sex | +----+----------+---------+--------+ | 1 | Susan | 12 | female | | 2 | Jame | 12 | male | | 3 | Bob | 12 | male | | 4 | Robot | 12 | male | | 5 | Jen | 12 | female | | 6 | Toney | 12 | male | | 8 | Alice | 12 | female | | 9 | Bonnie | 12 | male | | 10 | Ella | 12 | female | | 11 | Bill | 15 | male | | 12 | Limon | 14 | male | | 13 | Soen | 16 | NULL | +----+----------+---------+--------+
Copyright © 2017-Now pnotes.cn. All Rights Reserved.
编程学习笔记 保留所有权利
MARK:3.0.0.20240214.P35
From 2017.2.6