1.删除表中数据

1.1 方法一：

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

实践：

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

mysql> use linzhongniao;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from student;ERROR 1146 (42S02): Table 'linzhongniao.student' doesn't existmysql> 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 columnmysql> 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; 逻辑清除，按行删。