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中文網其他相關文章!