MariaDB更新資料

在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];

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語句來更新多個列。 在以下範例中，將更新表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     |
+------------+--------------+-----------------+----------------+
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';