5.删除数据

DELETE FROM table_name [WHERE <condition>];

table_name表示要执行删除操作的表;[WHERE ]为可选参数,用于指定删除条件,如果没有WHERE子句,DELETE语句将删除表中所有记录。
例:删除person表中,id=11的记录:
删除前:

mysql> SELECT * FROM person;
+----+---------+-----+-----------+
| id | name    | age | info      |
+----+---------+-----+-----------+
|  1 | Green   |  21 | student   |
|  2 | Suse    |  22 | student   |
|  3 | Mary    |  24 | Musician  |
|  4 | Willam  |  20 | student   |
|  5 | Laura   |  25 | NULL      |
|  6 | Evans   |  27 | secretary |
|  7 | Dale    |  22 | student   |
|  8 | Edison  |  28 | singer    |
| 11 | LiMing  |  15 | student   |
| 12 | Beckham |  31 | police    |
+----+---------+-----+-----------+
10 rows in set (0.00 sec)

删除:

mysql> DELETE FROM person WHERE id=11;
Query OK, 1 row affected (0.19 sec)

删除后:

mysql> SELECT * FROM person;
+----+---------+-----+-----------+
| id | name    | age | info      |
+----+---------+-----+-----------+
|  1 | Green   |  21 | student   |
|  2 | Suse    |  22 | student   |
|  3 | Mary    |  24 | Musician  |
|  4 | Willam  |  20 | student   |
|  5 | Laura   |  25 | NULL      |
|  6 | Evans   |  27 | secretary |
|  7 | Dale    |  22 | student   |
|  8 | Edison  |  28 | singer    |
| 12 | Beckham |  31 | police    |
+----+---------+-----+-----------+
9 rows in set (0.00 sec)

例:
在person表中使用DELETE语句同时删除多条记录:

mysql> SELECT * FROM person WHERE age BETWEEN 19 AND 22;
+----+--------+-----+---------+
| id | name   | age | info    |
+----+--------+-----+---------+
|  1 | Green  |  21 | student |
|  2 | Suse   |  22 | student |
|  4 | Willam |  20 | student |
|  7 | Dale   |  22 | student |
+----+--------+-----+---------+
4 rows in set (0.00 sec)

删除:

mysql> DELETE FROM person WHERE age BETWEEN 19 AND 22;
Query OK, 4 rows affected (0.07 sec)

删除后:

mysql> SELECT * FROM person WHERE age BETWEEN 19 AND 22;
Empty set (0.00 sec)

例:删除person表中所有记录:
删除前:

mysql> SELECT * FROM person;
+----+---------+-----+-----------+
| id | name    | age | info      |
+----+---------+-----+-----------+
|  3 | Mary    |  24 | Musician  |
|  5 | Laura   |  25 | NULL      |
|  6 | Evans   |  27 | secretary |
|  8 | Edison  |  28 | singer    |
| 12 | Beckham |  31 | police    |
+----+---------+-----+-----------+
5 rows in set (0.00 sec)

删除:

mysql> DELETE FROM person;
Query OK, 5 rows affected (0.02 sec)

删除后:

mysql> SELECT * FROM person;
Empty set (0.00 sec)
发布了28 篇原创文章 · 获赞 2 · 访问量 418
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览