Mysql addition, deletion, modification and query update records

Update record

Update data We have already said. When you need to modify content, modify bank card balances, or modify equipment information, you need to use update and modify statements.

The basic syntax of the modification (also called update) statement is as follows:

Category	Detailed explanation
Basic syntax	update table name set field 1=value 1, field 2=value 2, field n=value n where conditions
Example	update money set balance=balance-500 where userid = 15;
Example description	Modify the money table and change The balance balance is reduced by 500. The required userid is 15

Suppose we have the following table, the table structure is as follows:

mysql> select * from emp where deptno=15;
+------+----------+----------+
| userid |username| balance |
+------+----------+----------+
| 15 | 马云 | 15000.00 |
+------+-------+-------------+
1 row in set (0.00 sec)

使用 update 语句进行记录更新

mysql> update money set balance=balance-500 where userid = 15;
Query OK, 1 row affected (0.35 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from emp where deptno=15;
+------+----------+----------+
| userid |username| balance |
+------+----------+----------+
| 15 | 马云 | 14500.00 |
+------+-------+-------------+
1 row in set (0.00 sec)

修改多个字段

mysql> update money set balance=balance-500,username='李文凯' where userid = 15;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from emp where deptno=15;
+------+----------+----------+
| userid |username| balance |
+------+----------+----------+
| 15 |王宝强 | 14500.00 |
+------+-------+-------------+
1 row in set (0.00 sec)

同时对两个表进行更新

##1 王宝强 50000.00 2 黄晓明 150000000.00 ##15 16

userid	username	balance


马云	15000.00
Chen He	1234131.00

类别	详细解示
基本语法	update 表1,表2 set 字段1=值1,字段2=值2,字段n=值n where 条件
示例	update money m,user u m.balance=m.balance*u.age where m.userid=u.id;
示例说明	修改money，将money表的别名设置为m；user表的别名设置为u；将m表的余额改为m表的balance*用户表的age。执行条件是：m.userid = u.id