mysql 更新数据表的记录
对于表里的记录值,可以通过update 命令进行更改,语法如下:
UPDATE tablename SET field1=value1,field2.=value2,……fieldn=valuen [WHERE CONDITION]
例如,将表emp 中ename 为“lisa”的薪水(sal)从3000 更改为4000:
mysql> update emp set sal=4000 where ename='lisa'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
在MySQL 中,update 命令可以同时更新多个表中数据,语法如下:
UPDATE t1,t2…tn set t1.field1=expr1,tn.fieldn=exprn [WHERE CONDITION]
在下例中,同时更新表emp 中的字段sal 和表dept 中的字段deptname:
mysql> select * from emp; +--------+------------+---------+--------+ | ename | hiredate | sal | deptno | +--------+------------+---------+--------+ | zzx | 2000-01-01 | 100.00 | 1 | | lisa | 2003-02-01 | 200.00 | 2 | | bjguan | 2004-04-02 | 100.00 | 1 | | dony | 2005-02-05 | 2000.00 | 4 | +--------+------------+---------+--------+ 4 rows in set (0.00 sec) mysql> select * from dept; +--------+----------+ | deptno | deptname | +--------+----------+ | 1 | tech | | 2 | sale | | 5 | fin | +--------+----------+ 3 rows in set (0.00 sec) mysql> update emp a,dept b set a.sal=a.sal*b.deptno,b.deptname=a.ename where a.deptno=b.deptno; Query OK, 3 rows affected (0.04 sec) Rows matched: 5 Changed: 3 Warnings: 0 mysql> select * from emp; +--------+------------+---------+--------+ | ename | hiredate | sal | deptno | +--------+------------+---------+--------+ | zzx | 2000-01-01 | 100.00 | 1 | | lisa | 2003-02-01 | 400.00 | 2 | | bjguan | 2004-04-02 | 100.00 | 1 | | dony | 2005-02-05 | 2000.00 | 4 | +--------+------------+---------+--------+ 4 rows in set (0.01 sec) mysql> select * from dept; +--------+----------+ | deptno | deptname | +--------+----------+ | 1 | zzx | | 2 | lisa | | 5 | fin | +--------+----------+ 3 rows in set (0.00 sec) 自此,两个表的数据同时进行了更新。
注意:多表更新的语法更多地用在了根据一个表的字段,来动态的更新另外一个表的字段
如果觉得对您有帮助,麻烦您点一下推荐,谢谢!
好记忆不如烂笔头
好记忆不如烂笔头