数据库教程-mariadb教程-MariaDB更新数据

跨零代码为大家提供高品质的解决方案,请大家多多来访,跨零不胜感激,在此谢过。

在MariaDB中,UPDATE语句用于通过更改表中的值来修改现有字段。

语法:

UPDATE table_name SET field=new_value, field2=new_value2,...   [WHERE ...] 

或者UPDATE语句可以与WHEREORDER BYLIMIT子句一起使用。

UPDATE table   SET column1 = expression1,       column2 = expression2,       ...   [WHERE conditions]   [ORDER BY expression [ ASC | DESC ]]   [LIMIT number_rows]; 

1. 更新单个列

假设我们有一个表 – 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     | +------------+--------------+-----------------+----------------+ 5 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     | +------------+--------------+-----------------+----------------+ 1 row in set (0.08 sec) 

2. 更新多列

还可以使用MariaDB数据库中的UPDATE语句来更新多个列。 在以下示例中,将更新表studentsstudent_nameKobe的两列 – student_namestudent_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     | +------------+--------------+-----------------+----------------+ 5 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'; 

  

从零到一,创造未来!跨零综合IT问题解决服务站,欢迎你的到来。Oracle数据库教程 mariadb教程 只为你绽放。

本文固定链接: http://kua0.com/2019/02/01/数据库教程-mariadb教程-mariadb更新数据/

为您推荐

发表评论

电子邮件地址不会被公开。 必填项已用*标注