Mysql DBA 高级运维学习之路-删除表中数据

1.删除表中数据

1.1 方法一：

命令语法：delete from 表名 where 表达式

实践：

（1）删除表test表中编号为5的记录

mysql> use linzhongniao;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from student;
ERROR 1146 (42S02): Table 'linzhongniao.student' doesn't exist
mysql> select * from test;   
+----+-----------+
| id | name  |
+----+-----------+
|  1 | nishishei |
|  2 | wwn1314   |
|  3 | lisi  |
|  4 | woshishei |
|  5 | xiaozhang |
+----+-----------+
5 rows in set (0.00 sec)

mysql> delete from test where id='5';
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;   
+----+-----------+
| id | name  |
+----+-----------+
|  1 | nishishei |
|  2 | wwn1314   |
|  3 | lisi  |
|  4 | woshishei |
+----+-----------+
4 rows in set (0.00 sec)

（2）也可以删除name等于lisi的行

mysql> select * from test;   
+----+-----------+
| id | name  |
+----+-----------+
|  1 | nishishei |
|  2 | wwn1314   |
|  3 | lisi  |
|  4 | woshishei |
+----+-----------+
4 rows in set (0.00 sec)

mysql> delete from test where name='lisi';
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> delete from test where name='lisi' and id='3';
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+-----------+
| id | name  |
+----+-----------+
|  1 | nishishei |
|  2 | wwn1314   |
|  4 | woshishei |
+----+-----------+
3 rows in set (0.00 sec)

（3）也可以删除id大于3的行

mysql> delete from test where id>'3'; 
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;   
+----+-----------+
| id | name  |
+----+-----------+
|  1 | nishishei |
|  2 | wwn1314   |
+----+-----------+
2 rows in set (0.00 sec)

提示：不加条件就是全部删除，也是非常危险的操作，这里接不演示了。delete from student 。

1.2 方法二：

命令语法:truncate table 表名(清空表中的所有数据)

mysql> truncate table test;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

truncate from srudent和delete from student区别

(1)truncate table student; 更快，清空物理文件。

(2)delete from student; 逻辑清除，按行删。

转载于:https://blog.51cto.com/10642812/2065957