update mysql_MySQL更新命令_UPDATE

创建测试表

mysql>CREATE TABLE `product` (-> `proID` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品表主键',-> `price` decimal(10,2) NOT NULL COMMENT '商品价格',-> `type` int(11) NOT NULL COMMENT '商品类别(0生鲜,1食品,2生活)',-> `dtime` datetime NOT NULL COMMENT '创建时间',->PRIMARY KEY (`proID`)-> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='商品表';

Query OK,0 rows affected (0.11sec)

mysql>CREATE TABLE `producttype` (-> `ID` int(11) NOT NULL COMMENT '商品类别(0生鲜,1食品,2生活)',-> `amount` int(11) COMMENT '每种类别商品总金额',->UNIQUE KEY (`ID`)-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品类别资金汇总表'

->;

Query OK,0 rows affected (0.12 sec)

mysql> INSERT INTO product(price,type,dtime) VALUES(10.00,0,now()),(10.00,1,now()),(10.00,1,now()),(20.00,2,now()),(30.00,3,now());

Query OK, 5 rows affected (0.06 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> INSERT INTO producttype(ID) VALUES(1),(2),(3);

Query OK, 3 rows affected (0.04 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * fromproduct;+-------+-------+------+---------------------+

| proID | price | type | dtime |

+-------+-------+------+---------------------+

| 1 | 10.00 | 0 | 2018-01-31 03:06:05 |

| 2 | 10.00 | 1 | 2018-01-31 03:06:05 |

| 3 | 10.00 | 1 | 2018-01-31 03:06:05 |

| 4 | 20.00 | 2 | 2018-01-31 03:06:05 |

| 5 | 30.00 | 3 | 2018-01-31 03:06:05 |

+-------+-------+------+---------------------+

5 rows in set (0.00sec)

mysql> select * fromproducttype;+----+--------+

| ID | amount |

+----+--------+

| 1 | NULL |

| 2 | NULL |

| 3 | NULL |

+----+--------+

3 rows in set (0.00 sec)

1. 单表更新

UPDATE用法：update 表名 set  属性1=value1,属性2=value2 where 限定条件

示例：

mysql> UPDATE product

-> SET price='20.00',type=0

-> WHERE proID=2;

Query OK, 1 row affected (0.04 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from product;

+-------+-------+------+---------------------+

| proID | price | type | dtime |

+-------+-------+------+---------------------+

| 1 | 10.00 | 0 | 2018-01-31 03:06:05 |

| 2 | 20.00 | 0 | 2018-01-31 03:06:05 |

| 3 | 10.00 | 1 | 2018-01-31 03:06:05 |

| 4 | 20.00 | 2 | 2018-01-31 03:06:05 |

| 5 | 30.00 | 3 | 2018-01-31 03:06:05 |

+-------+-------+------+---------------------+

5 rows in set (0.00 sec)

2. 关联更新

UPDATE用法：update 表名1,表名2 set  表1.属性=表2.属性值 where 限定条件

示例：

mysql>UPDATE producttype,product

-> SET producttype.amount=product.price

-> where product.TYPE = producttype.ID AND product.TYPE=1;

Query OK, 1 row affected (0.09 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from producttype;

+----+--------+

| ID | amount |

+----+--------+

| 1 | 10 |

| 2 | NULL |

| 3 | NULL |

+----+--------+

3 rows in set (0.00 sec)

3. 限制更新

UPDATE用法：update 表名1,表名2 set  表1.属性=表2.属性值 where 限定条件 limit  m

说明：只支持更新前多少行，而不支持非TOP的指定范围更新，也就是说，limit只接收一个参数。

意义：更新前m个符合where条件的记录。

mysql> UPDATE tb_name SET column_name='test' LIMIT 30;

4. 排序更新

UPDATE用法：update 表名1,表名2 set  表1.属性=表2.属性值 where 限定条件 order by ... [limit m]

说明：同限制更新一样，首先判断出符合where条件的记录，然后对符合条件的记录进行排序，最后对排序后的前m条记录做更新

mysql> UPDATE tb_name SET column_name='test' ORDER BY id ASC LIMIT 30;

5. 联合更新

UPDATE用法：update 表A set  A.属性=表B.属性值 FROM a as A [inner join | right join | left join] b as B on [连接条件] where 限定条件

mysql>UPDATE Table_A-> SET Table_A.col1 =Table_B.col1,-> Table_A.col2 =Table_B.col2->FROM Some_Table AS Table_A-> INNER JOIN Other_Table AS Table_B ON Table_A.id =Table_B.id-> WHERE Table_A.col3 = 'cool';

mysql> update a join b on a.id=b.id set a.name=b.name where a.year=2016;