Mysql DBA 進階營運學習筆記-刪除表中資料

標籤：truncate   9.1   內容   dba   實踐   delete   --   示範   區別   

9.11 刪除表中資料

1. 命令文法：delete from 表名 where 運算式

實踐：

（1）刪除表student中編號為3的記錄

mysql> use zbfDatabase changedmysql> 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 進階營運學習筆記-刪除表中資料