Mysql DBA Advanced Operations Learning Note-delete data from a table

9.11 deleting data from a table

1. Command syntax: Delete from table name where expression

Practice:

(1) Delete the record numbered 3 in table student

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) You can also delete a row with name equal to 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) You can also delete lines with IDs greater than 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)

Hint: No condition is all deleted, is also very dangerous operation, here does not show up. Delete from student.

2. command syntax TRUNCATE TABLE name

Truncate table student; Clear what you want in the table

mysql> truncate table student;Query OK, 0 rows affected (0.00 sec)mysql> select * from student;Empty set (0.00 sec)

Truncate from Srudent and delete from student differences

A.truncate table student; faster, empties physical files.

B.delete from student; logical purge, by row.

Mysql DBA Advanced Operations Learning Note-delete data from a table