简介
- 一个项目中,为了逻辑更清晰,我们通常会在一个数据库中创建多个表来保存数据
- 这些不同的表中往往都是有关联性的,所以当我们对其中一个表的内容进行更改的时候,其他的表对应的内容也需要同步修改
那么下面就来演示一下如何实现这个需求
场景模拟
表名称 | 作用 |
---|---|
type | 类型 |
user | 用户信息 |
当前数据:
MariaDB [test]> select * from user;
+----+------+---------------------+
| id | name | create_time |
+----+------+---------------------+
| 1 | liu | 2021-04-09 13:05:32 |
+----+------+---------------------+
1 row in set (0.000 sec)
MariaDB [test]> select * from type;
+----+------+--------+-----------------+
| id | name | permit | submission_date |
+----+------+--------+-----------------+
| 1 | liu | 1491 | 2021-03-28 |
+----+------+--------+-----------------+
1 row in set (0.000 sec)
需求:
需要修改user
表中某个用户的名字,同步修改type
表中该用户的名字
常用语法:
update <数据表1> a,<数据表2> b set b.<表1字段>=a.<表2字段> where a.id=b.id;
实践操作:
MariaDB [test]> update user set name='liuyi';
Query OK, 1 row affected (0.037 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [test]> select * from user;
+----+-------+---------------------+
| id | name | create_time |
+----+-------+---------------------+
| 1 | liuyi | 2021-04-09 13:13:02 |
+----+-------+---------------------+
1 row in set (0.000 sec)
MariaDB [test]> select * from type;
+----+------+--------+-----------------+
| id | name | permit | submission_date |
+----+------+--------+-----------------+
| 1 | liu | 1491 | 2021-03-28 |
+----+------+--------+-----------------+
1 row in set (0.001 sec)
MariaDB [test]> update user a,type b set b.name=a.name;
Query OK, 1 row affected (0.037 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [test]> select * from type;
+----+-------+--------+-----------------+
| id | name | permit | submission_date |
+----+-------+--------+-----------------+
| 1 | liuyi | 1491 | 2021-03-28 |
+----+-------+--------+-----------------+
1 row in set (0.000 sec)
MariaDB [test]>