mysql中关于删除语句大全总结（上）

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)

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)

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)

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)

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)

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)