SQL UPDATE语句:修改数据
SQL UPDATE 语句用于修改数据表中现有的记录(数据行)。UPDATE 通常和 WHERE 子句一起使用,用以筛选满足条件的记录;如果不使用 WHERE 子句,那么表中所有的记录都将被修改,这往往不是我们期望的。
如果您要修改表中所有记录的 age 和 country 值,则只需要 UPTATE 命令,不需要使用 WHERE 子句,请看下面的代码:
语法
带有 WHERE 子句的 UPDATE 命令的基本语法如下:
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
示例
现在有一个包含如下记录的 web 表:+----+----------------+----------------------------+-----+-------+---------+---------+ | id | name | url | age | alexa | uv | country | +----+----------------+----------------------------+-----+-------+---------+---------+ | 1 | 百度 | https://www.baidu.com/ | 21 | 4 | 5010.5 | CN | | 2 | 淘宝 | https://www.taobao.com/ | 17 | 8 | 3996.75 | CN | | 3 | C语言中文网 | http://c.biancheng.net/ | 12 | 7923 | 11.62 | CN | | 4 | Google | https://www.google.com/ | 23 | 1 | 36474 | US | | 5 | GitHub | https://github.com/ | 13 | 95 | 216.3 | US | | 6 | Stack Overflow | https://stackoverflow.com/ | 16 | 48 | 592.2 | US | | 7 | Yandex | http://www.yandex.ru/ | 11 | 53 | 591.82 | RU | | 8 | VK | https://vk.com/ | 23 | 23 | 1206 | RU | +----+----------------+----------------------------+-----+-------+---------+---------+以下 SQL 语句将更新表中 id 为 6 的网站的名字(name):
UPDATE website SET name = 'stack-overflow' WHERE id = 6;执行完该语句,website 表的记录如下:
+----+----------------+----------------------------+-----+-------+---------+---------+ | id | name | url | age | alexa | uv | country | +----+----------------+----------------------------+-----+-------+---------+---------+ | 1 | 百度 | https://www.baidu.com/ | 21 | 4 | 5010.5 | CN | | 2 | 淘宝 | https://www.taobao.com/ | 17 | 8 | 3996.75 | CN | | 3 | C语言中文网 | http://c.biancheng.net/ | 12 | 7923 | 11.62 | CN | | 4 | Google | https://www.google.com/ | 23 | 1 | 36474 | US | | 5 | GitHub | https://github.com/ | 13 | 95 | 216.3 | US | | 6 | stack-overflow | https://stackoverflow.com/ | 16 | 48 | 592.2 | US | | 7 | Yandex | http://www.yandex.ru/ | 11 | 53 | 591.82 | RU | | 8 | VK | https://vk.com/ | 23 | 23 | 1206 | RU | +----+----------------+----------------------------+-----+-------+---------+---------+
如果您要修改表中所有记录的 age 和 country 值,则只需要 UPTATE 命令,不需要使用 WHERE 子句,请看下面的代码:
UPDATE website SET age = 20, country = 'CN';执行完该语句,CUSTOMERS 表的记录如下:
+----+----------------+----------------------------+-----+-------+---------+---------+ | id | name | url | age | alexa | uv | country | +----+----------------+----------------------------+-----+-------+---------+---------+ | 1 | 百度 | https://www.baidu.com/ | 20 | 4 | 5010.5 | CN | | 2 | 淘宝 | https://www.taobao.com/ | 20 | 8 | 3996.75 | CN | | 3 | C语言中文网 | http://c.biancheng.net/ | 20 | 7923 | 11.62 | CN | | 4 | Google | https://www.google.com/ | 20 | 1 | 36474 | CN | | 5 | GitHub | https://github.com/ | 20 | 95 | 216.3 | CN | | 6 | Stack Overflow | https://stackoverflow.com/ | 20 | 48 | 592.2 | CN | | 7 | Yandex | http://www.yandex.ru/ | 20 | 53 | 591.82 | CN | | 8 | VK | https://vk.com/ | 20 | 23 | 1206 | CN | +----+----------------+----------------------------+-----+-------+---------+---------+