在MariaDB中,UPDATE
语句用于通过更改表中的值来修改现有字段。
语法:
UPDATE table_name SET field=new_value, field2=new_value2,... [WHERE ...]
或者,UPDATE
语句可以与WHERE
,ORDER BY
和LIMIT
子句一起使用。
UPDATE table SET column1 = expression1, column2 = expression2, ... [WHERE conditions] [ORDER BY expression [ ASC | DESC ]] [LIMIT number_rows];
假设我们有一个表 - students
,并具有以下数据记录:
MariaDB [testdb]> select * from students; +------------+--------------+-----------------+----------------+ | student_id | student_name | student_address | admission_date | +------------+--------------+-----------------+----------------+ | 1 | Maxsu | Haikou | 2017-01-07 | | 3 | JMaster | Beijing | 2016-05-07 | | 4 | Mahesh | Guangzhou | 2016-06-07 | | 5 | Kobe | Shanghai | 2016-02-07 | | 6 | Blaba | Shengzheng | 2016-08-07 | +------------+--------------+-----------------+----------------+ rows in set (0.00 sec)
现在,更改student_id
列的值为 6
的行记录,把student_address
列的值更新为:Shenzhen
。
UPDATE Students SET student_address = 'Shenzhen' WHERE student_id = '6';
执行上面更新语句后,查询更新的结果 -
MariaDB [testdb]> UPDATE Students -> SET student_address = 'Shenzhen' -> WHERE student_id = '6'; Query OK, 1 row affected (0.21 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [testdb]> select * from students where student_id=6; +------------+--------------+-----------------+----------------+ | student_id | student_name | student_address | admission_date | +------------+--------------+-----------------+----------------+ | 6 | Blaba | Shenzhen | 2016-08-07 | +------------+--------------+-----------------+----------------+ row in set (0.08 sec)
还可以使用MariaDB数据库中的UPDATE
语句来更新多个列。 在以下示例中,将更新表students
中student_name
为Kobe
的两列 - student_name
和student_address
的值。参考以下更新语句 -
UPDATE Students SET student_name = '科比', student_address = 'Haikou' WHERE student_name = 'Kobe';
执行上面语句,得到以下结果 -
MariaDB [testdb]> UPDATE Students -> SET student_name = '科比', student_address = 'Haikou' -> WHERE student_name = 'Kobe'; Query OK, 1 row affected (0.09 sec) Rows matched: 1 Changed: 1 Warnings: 0 -- 查询更新的结果 MariaDB [testdb]> select * from students; +------------+--------------+-----------------+----------------+ | student_id | student_name | student_address | admission_date | +------------+--------------+-----------------+----------------+ | 1 | Maxsu | Haikou | 2017-01-07 | | 3 | JMaster | Beijing | 2016-05-07 | | 4 | Mahesh | Guangzhou | 2016-06-07 | | 5 | 科比 | Haikou | 2016-02-07 | | 6 | Blaba | Shenzhen | 2016-08-07 | +------------+--------------+-----------------+----------------+ rows in set (0.02 sec)
注意事项
在执行语句时,经常要指定更新的条件,如果忘记了指定了WHERE
子句中的条件,那么将会更新所有行记录。想象一下,以下两个语句执行的效果 -
-- 语句1 UPDATE Students SET student_name = '科比', student_address = 'Haikou' WHERE student_name = 'Kobe'; -- 语句2 UPDATE Students SET student_name = '科比', student_address = 'Haikou';