1.修改表中指定条件固定列的数据
命令语法：update 表名 set 字段=新值….where 条件(一定要注意条件)
(1)首先查看要修改的表
mysql> select database();
+--------------+
| database() |
+--------------+
| linzhongniao |
+--------------+
1 row in set (0.00 sec)
mysql> select * from test;
+----+-------------+
| id | name|
+----+-------------+
| 1 | linzhogniao |
| 2 | wwn1314 |
| 3 | lisi|
| 4 | woshishei |
| 5 | xiaozhang |
+----+-------------+
5 rows in set (0.00 sec)
(2)修改id为3的行的名字为李四
mysql> update test set name='李四' where id='3';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test;
+----+-------------+
| id | name|
+----+-------------+
| 1 | linzhogniao |
| 2 | wwn1314 |
| 3 | 李四|
| 4 | woshishei |
| 5 | xiaozhang |
+----+-------------+
5 rows in set (0.00 sec)
2.修改表中所有行的数据
不指定条件会修改所有数据，严谨操作一定要小心
(1)不带条件更改所有表的数据
mysql> update test set name='张三';
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> select * from test;
+----+--------+
| id | name |
+----+--------+
| 1 | 张三 |
| 2 | 张三 |
| 3 | 张三 |
| 4 | 张三 |
| 5 | 张三 |
+----+--------+
5 rows in set (0.00 sec)
(2)修改了所有数据可以用备份恢复
之前已经备份过了，这里就不重新备份了，指定要恢复数据的库linzhongniao是库不是表
[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock linzhongniao
这时候再看数据就恢复了
mysql> select * from test;
+----+-------------+
| id | name|
+----+-------------+
| 1 | linzhogniao |
| 2 | wwn1314 |
| 3 | lisi|
| 4 | woshishei |
| 5 | xiaozhang |
+----+-------------+
5 rows in set (0.00 sec)
3.登录数据库指定-U参数防止误操作
怎么防止delete ,update误操作呢？
方法一：带参数-U登录mysql
(1)查看mysql帮助
在mysql命令上加上U，当发出没有where和limit关键字的update或delete时，mysql拒绝执行操作。
[root@mysql ~]# mysql --help|grep dummy
-U, --i-am-a-dummy Synonym for option --safe-updates, -U.
i-am-a-dummy FALSE
(2)指定-U登录mysql
mysql> use linzhongniao;
Database changed
mysql> select * from test;
+----+-------------+
| id | name|
+----+-------------+
| 1 | linzhogniao |
| 2 | wwn1314 |
| 3 | lisi|
| 4 | woshishei |
| 5 | xiaozhang |
+----+-------------+
5 rows in set (0.00 sec)
mysql> update test set name='张三';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
第二种方法:指定别名，如果经常忘记加-U参数可以mysql登录命令添加别名
(1)指定别名，完了正常登录就可以了，临时生效
[root@linzhongniao ~]# alias mysql='mysql -U'
[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -h localhost
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
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 linzhongniao;
Database changed
mysql> select * from test;
+----+-------------+
| id | name|
+----+-------------+
| 1 | linzhogniao |
| 2 | wwn1314 |
| 3 | lisi|
| 4 | woshishei |
| 5 | xiaozhang |
+----+-------------+
5 rows in set (0.00 sec)
mysql> update test set name='张三';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
(2)让别名永久生效，添加到全局变量配置文件
[root@linzhongniao ~]# echo "alias mysql='mysql -U'">>/etc/profile
[root@linzhongniao ~]# source /etc/profile
[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -h localhost
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> use linzhongniao;
Database changed
mysql> update test set name='张三';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
