MySQL資料庫更新數據操作講解

在MySQL中,可以使用 UPDATE 語句更新表中的記錄,可以更新特定的行或者同時更新所有的行。

語法結構如下:

UPDATE table_name

SET column_name1 = value1 ,column_name2 = value2 , … ,column_namen = valuen

WHERE (condition);

column_name1,column_name2, … ,column_namen 為要更新的字段名稱;

value1,value2,… ,valuen 為相對應的指定字段的更新值;

condition 是更新的記錄需要滿足的條件

【例】在person 表中,更新 id=4 的記錄,將 id 字段改為 1 ,將 age 字段改為 21;

更改前:

mysql> SELECT  * FROM person;
+----+--------+-----+-------------+
| id | name   | age | info        |
+----+--------+-----+-------------+
|  2 | Suse   |  22 | dancer      |
|  3 | Mary   |  24 | Musicion    |
|  4 | Willam |  20 | sports  man |
+----+--------+-----+-------------+

更新SQL語句:

mysql> UPDATE PERSON
    -> SET  id=1,age=21
    -> WHERE id=4;
Query OK, 1 row affected (0.97 sec)

查看執行結果:

mysql> SELECT  * FROM person;
+----+--------+-----+-------------+
| id | name   | age | info        |
+----+--------+-----+-------------+
|  1 | Willam |  21 | sports  man |
|  2 | Suse   |  22 | dancer      |
|  3 | Mary   |  24 | Musicion    |
+----+--------+-----+-------------+

【註】如果不指定 WHERE 子句,將更新表中所有行

【例】在person 表中,更新 age 值在 10~22 的記錄,將 info 字段改為 student。SQL語句如下:

mysql> UPDATE  person
    -> SET  info='student'
    -> WHERE  age  BETWEEN  10  AND  22;

執行結果:

mysql> SELECT  * FROM person;
+----+--------+-----+----------+
| id | name   | age | info     |
+----+--------+-----+----------+
|  1 | Willam |  21 | student  |
|  2 | Suse   |  22 | student  |
|  3 | Mary   |  24 | Musicion |
+----+--------+-----+----------+

You May Also Like