DML语句--更新(update)

更新记录update

UPDATE tablename SET field1=value1, field2=value2, ..., fieldn=valuen [WHERE CONDITION]

• 显示表的结构

mysql> select *from student;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | karin    |   3 |
|  2 | 兔兔      |   5 |
|  3 | xiaoming |  17 |
|  4 | lihua    |  18 |
|  5 | lili     |  12 |
|  7 | xiaohong |  19 |
|  8 | xiaoqing |  89 |
+----+----------+-----+
7 rows in set (0.00 sec)

• 将表student中name为’karin’的年龄(age)修改为22

mysql> update student set age=22 where name='karin';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

• 显示表的结构

mysql> select *from student;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | karin    |  22 |
|  2 | 兔兔      |   5 |
|  3 | xiaoming |  17 |
|  4 | lihua    |  18 |
|  5 | lili     |  12 |
|  7 | xiaohong |  19 |
|  8 | xiaoqing |  89 |
+----+----------+-----+
7 rows in set (0.00 sec)

update 命令可以同时更新多个表中的数据

UPDATE t1, t2, ..., tn set t1.field1=expr1, t2.field2=expr2, ..., tn.fieldn=exprn [WHERE CONDITION]

• 显示表student和studys的结构

mysql> select *from student;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | karin    |  22 |
|  2 | 兔兔      |   5 |
|  3 | xiaoming |  17 |
|  4 | lihua    |  18 |
|  5 | lili     |  12 |
|  7 | xiaohong |  19 |
|  8 | xiaoqing |  89 |
+----+----------+-----+
7 rows in set (0.00 sec)
 
mysql> select *from studys;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1  | haha |  17 |
| 2  | dada |  67 |
+----+------+-----+
2 rows in set (0.00 sec)

mysql> update student a,studys b set a.age=a.age*b.id,b.name=a.name where a.id =b.id;
Query OK, 3 rows affected (0.03 sec)
Rows matched: 4  Changed: 3  Warnings: 0

mysql> select *from student;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | karin    |  22 |
|  2 | 兔兔      |  10 |
|  3 | xiaoming |  17 |
|  4 | lihua    |  18 |
|  5 | lili     |  12 |
|  7 | xiaohong |  19 |
|  8 | xiaoqing |  89 |
+----+----------+-----+
7 rows in set (0.00 sec)
 
mysql> select *from studys;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
| 1  | karin  |  17 |
| 2  | 兔兔 |  67 |
+----+--------+-----+
2 rows in set (0.00 sec)