UPDATE

更新记录的几种方式 ：

1. 单表更新：UPDATE [LOW_PRIORITY] [IGNORE] table_referece SET col_name1={expr1|DEFAULT} [,col_name2={expr2| DEFAULT} ] … [WHERE where_condition]

UPDATE users SET age= age+5; //省略where条件 更新所有

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | Tom      | 123                              |  23 |    1 |
|  2 | Json     | 123                              |  23 |    1 |
|  3 | Json     | 123                              |  23 |    1 |
|  4 | Json     | 123                              |  10 |    1 |
|  5 | Json     | 123                              |  23 |    1 |
|  6 | Rose     | 202cb962ac59075b964b07152d234b70 |  10 |    0 |
|  7 | Ben      | 456                              |  10 | NULL |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)

mysql> UPDATE users SET age= age+5;
Query OK, 7 rows affected (0.04 sec)
Rows matched: 7  Changed: 7  Warnings: 0

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | Tom      | 123                              |  28 |    1 |
|  2 | Json     | 123                              |  28 |    1 |
|  3 | Json     | 123                              |  28 |    1 |
|  4 | Json     | 123                              |  15 |    1 |
|  5 | Json     | 123                              |  28 |    1 |
|  6 | Rose     | 202cb962ac59075b964b07152d234b70 |  15 |    0 |
|  7 | Ben      | 456                              |  15 | NULL |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)

mysql> UPDATE users SET age= age -id, sex=0;
Query OK, 7 rows affected (0.04 sec)
Rows matched: 7  Changed: 7  Warnings: 0

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | Tom      | 123                              |  27 |    0 |
|  2 | Json     | 123                              |  26 |    0 |
|  3 | Json     | 123                              |  25 |    0 |
|  4 | Json     | 123                              |  11 |    0 |
|  5 | Json     | 123                              |  23 |    0 |
|  6 | Rose     | 202cb962ac59075b964b07152d234b70 |   9 |    0 |
|  7 | Ben      | 456                              |   8 |    0 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)

加WHERE

mysql> UPDATE users SET age= age+10 WHERE id%2=0;
Query OK, 3 rows affected (0.06 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | Tom      | 123                              |  27 |    0 |
|  2 | Json     | 123                              |  36 |    0 |
|  3 | Json     | 123                              |  25 |    0 |
|  4 | Json     | 123                              |  21 |    0 |
|  5 | Json     | 123                              |  23 |    0 |
|  6 | Rose     | 202cb962ac59075b964b07152d234b70 |  19 |    0 |
|  7 | Ben      | 456                              |   8 |    0 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)

1. 多表更新：UPDATE table_referece SET col_name1={expr1|DEFAULT} [,col_name2={expr2| DEFAULT} ] … [WHERE where_condition]
   表的链接

mysql> UPDATE tdb_goods INNER JOIN tdb_goods_cate ON goods_cate = cate_name SET goods_cate = cate_id;