9.11 删除表中数据
命令语法: delete from 表名 where 表达式
实践:
(1) 删除表 student 中编号为 3 的记录
mysql> use zbf
Database changed
mysql> select * from student;
+----+-----------+-----+--------+
| id | name | age | dept |
+----+-----------+-----+--------+
| 1 | zbf666| 29 | linux |
| 2 | lisi | 28 | mysql |
| 3 | zhangsan | 21 | python |
| 4 | woshishei | 24 | java |
+----+-----------+-----+--------+
4 rows in set (0.06 sec)
mysql> delete from student where id=3;
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+-----------+-----+-------+
| id | name | age | dept |
+----+-----------+-----+-------+
| 1 | zbf666| 29 | linux |
| 2 | lisi | 28 | mysql |
| 4 | woshishei | 24 | java |
+----+-----------+-----+-------+
3 rows in set (0.02 sec)
(2) 也可以删除 name 等于 lisi 的行
mysql> select * from student;
+----+-----------+-----+-------+
| id | name | age | dept |
+----+-----------+-----+-------+
| 1 | zbf666| 29 | linux |
| 2 | lisi | 28 | mysql |
| 4 | woshishei | 24 | java |
+----+-----------+-----+-------+
3 rows in set (0.02 sec)
mysql> delete from student where name='lisi';
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+-----------+-----+-------+
| id | name | age | dept |
+----+-----------+-----+-------+
| 1 | zbf666| 29 | linux |
| 4 | woshishei | 24 | java |
+----+-----------+-----+-------+
2 rows in set (0.00 sec)
(3) 也可以删除 id 大于 3 的行
mysql> select * from student;
+----+-----------+-----+-------+
| id | name | age | dept |
+----+-----------+-----+-------+
| 1 | zbf666| 29 | linux |
| 4 | woshishei | 24 | java |
+----+-----------+-----+-------+
2 rows in set (0.00 sec)
mysql> delete from student where id>3;
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+--------+-----+-------+
| id | name | age | dept |
+----+--------+-----+-------+
| 1 | zbf666 | 29 | linux |
+----+--------+-----+-------+
1 row in set (0.00 sec)
提示: 不加条件就是全部删除, 也是非常危险的操作, 这里接不演示了. delete from student .
2. 命令语法 Truncate table 表名
Truncate table student; 清空表中所欲内容
mysql > truncate table student;
Query OK,
0 rows affected(0.00 sec) mysql > select * from student;
Empty set(0.00 sec)
Truncate from srudent 和 delete from student 区别
a.Truncate table student; 更快, 清空物理文件.
b.delete from student; 逻辑清除, 按行删.
Mysql DBA 高级运维学习笔记 - 删除表中数据
来源: http://www.bubuko.com/infodetail-2478115.html