mysql查询删除的数据 mysql删除语句

1.drop语句。可以用来删除数据库和表。

A用drop语句来删除数据库：mysql> drop database hello;Query OK, 0 rows affected (0.19 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| trains |
+--------------------+

6 rows in set (0.00 sec)

B用drop语句来删除表：mysql> drop table q1;Query OK, 0 rows affected (0.39 sec)

mysql> show tables;
+------------------+
| Tables_in_trains |
+------------------+
| 4inall |
| ava |
| book |
| c1score |
| c2score |
| course |
| joke |
| sc |
| sc1 |
| student |
| student1 |
| teacher |
+------------------+

12 rows in set (0.00 sec)

2.delete语句。用来删除表中的字段：

A通过where子句来指定删除表中的某个记录：mysql> delete from joke where gid=1;

mysql> select * from joke;
+------+-----------+-------+------+
| gid | name | sex | age |
+------+-----------+-------+------+
| 3 | xiaowan2 | male | 22 |
| 3 | xiaowan2 | male | 22 |
| 3 | xiaowan2 | male | 22 |
| 3 | xiaowan22 | 1male | 22 |
| 0 | joker | NULL | NULL |
| 0 | joker | NULL | NULL |
+------+-----------+-------+------+

6 rows in set (0.00 sec)

注意：如果delete语句中没有加入where就会把表中的所有记录全部删除：mysql> select * from student1;

+------+--------+------+------+

| s | sname | sage | ssex |

+------+--------+------+------+

| 1 | 刘一 | 18 | 男 |

| 2 | 钱二 | 19 | 女 |

| 3 | 张三 | 17 | 男 |

| 4 | 李四 | 18 | 女 |

| 5 | 王五 | 17 | 男 |

| 6 | 赵六 | 19 | 女 |

+------+--------+------+------+

6 rows in set (0.00 sec)

mysql> delete from student1;Query OK, 6 rows affected (0.19 sec)
mysql> select * from student1;Empty set (0.00 sec)
B通过select子句来删除表中的某个记录：mysql> delete from student1 where s in (select s from student where sage=18 and ssex="男");Query OK, 1 row affected (0.13 sec)
mysql> select * from student1;
+------+--------+------+------+
| s | sname | sage | ssex |
+------+--------+------+------+
| 2 | 钱二 | 19 | 女 |
| 3 | 张三 | 17 | 男 |
| 4 | 李四 | 18 | 女 |
| 5 | 王五 | 17 | 男 |
| 6 | 赵六 | 19 | 女 |
+------+--------+------+------+

5 rows in set (0.00 sec)

3.用truncate来删除表中的所以字段：mysql> select * from student1;

+------+--------+------+------+
| s | sname | sage | ssex |
+------+--------+------+------+
| 1 | 刘一 | 18 | 男 |
| 2 | 钱二 | 19 | 女 |
| 3 | 张三 | 17 | 男 |
| 4 | 李四 | 18 | 女 |
| 5 | 王五 | 17 | 男 |
| 6 | 赵六 | 19 | 女 |
+------+--------+------+------+
6 rows in set (0.00 sec)
mysql> truncate table student1;Query OK, 0 rows affected (0.28 sec)
mysql> select * from student1;Empty set (0.00 sec)