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)

以上是mysql中關於刪除語句大全總結（上）的詳細內容。更多資訊請關注PHP中文網其他相關文章！