欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

mysql中关于删除语句大全总结(上)

程序员文章站 2022-03-13 12:20:48
...
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)

以上就是mysql中关于删除语句大全总结(上)的详细内容,更多请关注其它相关文章!