mysql高级删除_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; 逻辑清除，按行删。