UPDATE

来源：互联网发布：道路工程预算软件编辑：程序博客网时间：2024/05/18 00:10

更新记录的几种方式：

单表更新：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: 0mysql> 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: 0mysql> 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: 0mysql> 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)

多表更新：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;

0 0

UPDATE

更新记录的几种方式 ：

更新记录的几种方式：