一、修改表中数据
1、修改表中指定条件固定列的数据
1)命令语法:update 表名 set 字段=新值,…where 条件(一定要注意条件)
2)修改指定的行字段内容
a.查看要修改的表
mysql> select * from test; +----+---------+ | id | name | +----+---------+ | 3 | inca | | 5 | kaka | | 1 | oldboy | | 2 | oldgirl | | 4 | zuma | +----+---------+ 5 rows in set (0.00 sec)
b.修改id为3的行的名字为gongli。
mysql> update test set name='gongli' where id=3; Query OK, 1 row affected (0.38 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from test; +----+---------+ | id | name | +----+---------+ | 3 | gongli | | 5 | kaka | | 1 | oldboy | | 2 | oldgirl | | 4 | zuma | +----+---------+ 5 rows in set (0.00 sec)
2、修改表中所有行的数据
3)严重的案例(可能误操作导致数据对视)
a.不带条件更改所有表的记录
mysql> update test set name='gongli'; <==如果不加条件要十分小心。专业做法,一定要多问开发确定,如果你发给开发的语句要括号注明,防治DBA误会。 Query OK, 4 rows affected (0.00 sec) Rows matched: 5 Changed: 4 Warnings: 0
b.更改了所有的记录
mysql> select * from test; +----+--------+ | id | name | +----+--------+ | 1 | gongli | | 2 | gongli | | 3 | gongli | | 4 | gongli | | 5 | gongli | +----+--------+ 5 rows in set (0.00 sec)
c.数据恢复
[root@localhost ~]# mysql -uroot -pdubin oldboy </opt/oldboy_bak.sql [root@localhost ~]# [root@localhost ~]# [root@localhost ~]# mysql -uroot -pdubin Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 6 Server version: 5.5.32 Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> use oldboy Database changed mysql> select * from test; +----+---------+ | id | name | +----+---------+ | 1 | oldboy | | 2 | oldgirl | | 3 | inca | | 4 | zuma | | 5 | kaka | +----+---------+ 5 rows in set (0.00 sec) mysql>
3、防止误操作MySQL数据库一例
[root@localhost data]# mysql -uroot -pdubin -U Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 10 Server version: 5.5.32-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> select * from test; ERROR 1046 (3D000): No database selected mysql> use oldboy Database changed mysql> select * from test; +----+-----------+ | id | name | +----+-----------+ | 1 | oldboy | | 2 | oldgirl | | 3 | inca | | 4 | zuma | | 5 | kaka | | 6 | xiaozhang | +----+-----------+ 6 rows in set (0.00 sec) mysql> update test set name='gongli'; ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column mysql> quit Bye [root@localhost data]# alias mysql='mysql -U' [root@localhost data]# mysql -uroot -pdubin Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 11 Server version: 5.5.32-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> use oldboy Database changed mysql> select * from test; +----+-----------+ | id | name | +----+-----------+ | 1 | oldboy | | 2 | oldgirl | | 3 | inca | | 4 | zuma | | 5 | kaka | | 6 | xiaozhang | +----+-----------+ 6 rows in set (0.00 sec) mysql> update test set name='gongli'; ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column mysql>