SQL 語法 刪除資料表,資料庫

測試環境為 CentOS 7 x86_64 (虛擬機)

刪除資料表

刪除資料表可以透過 DELETE , TRUNCATE TABLE 與 DROP TABLE ,來看一下他們的差異.
先建立測試用的資料.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 99
Server version: 5.5.60-MariaDB MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> USE testdb;
Database changed
MariaDB [testdb]> CREATE TABLE employee ( Name char(20),JobTitle char(20),Dept char(20));
Query OK, 0 rows affected (0.01 sec)

• DELETE
  DELETE 不加 WHERE 條件下就會刪除整個資料表 Table 的資料了,會保留資料表結構..

  MariaDB [testdb]> INSERT INTO employee (Name , JobTitle , Dept) VALUES ('Cars','Senior Engineer' ,'Testing') , ('Ben','Testing','Engineer') ,('Rocher','PM','Engineer') ;
  Query OK, 3 rows affected (0.00 sec)
  Records: 3  Duplicates: 0  Warnings: 0
  
  MariaDB [testdb]> SELECT * FROM employee;
  +--------+-----------------+----------+
  | Name   | JobTitle        | Dept     |
  +--------+-----------------+----------+
  | Cars   | Senior Engineer | Testing  |
  | Ben    | Testing         | Engineer |
  | Rocher | PM              | Engineer |
  +--------+-----------------+----------+
  3 rows in set (0.00 sec)
  
  MariaDB [testdb]> DELETE FROM employee WHERE Name like 'Cars';
  Query OK, 1 row affected (0.01 sec)
  
  MariaDB [testdb]> SELECT * FROM employee;
  +--------+----------+----------+
  | Name   | JobTitle | Dept     |
  +--------+----------+----------+
  | Ben    | Testing  | Engineer |
  | Rocher | PM       | Engineer |
  +--------+----------+----------+
  2 rows in set (0.00 sec)
  
  MariaDB [testdb]> DELETE FROM employee;
  Query OK, 2 rows affected (0.00 sec)
  
  MariaDB [testdb]> SELECT * FROM employee;
  Empty set (0.00 sec)
  

• TRUNCATE TABLE
  使用 TRUNCATE TABLE 會刪除資料表中的所有資料,會保留資料表結構.

  MariaDB [testdb]> INSERT INTO employee (Name , JobTitle , Dept) VALUES ('Cars','Senior Engineer' ,'Testing') , ('Ben','Testing','Engineer') ,('Rocher','PM','Engineer') ;
  Query OK, 3 rows affected (0.00 sec)
  Records: 3  Duplicates: 0  Warnings: 0
  
  MariaDB [testdb]> SELECT * FROM employee;
  +--------+-----------------+----------+
  | Name   | JobTitle        | Dept     |
  +--------+-----------------+----------+
  | Cars   | Senior Engineer | Testing  |
  | Ben    | Testing         | Engineer |
  | Rocher | PM              | Engineer |
  +--------+-----------------+----------+
  3 rows in set (0.00 sec)
  
  MariaDB [testdb]> TRUNCATE TABLE employee;
  Query OK, 0 rows affected (0.01 sec)
  
  MariaDB [testdb]> SELECT * FROM employee;
  Empty set (0.00 sec)
  

• DROP TABLE
  使用 DROP TABLE 會刪除資料表中的所有資料,包含資料表結構.

  MariaDB [testdb]> INSERT INTO employee (Name , JobTitle , Dept) VALUES ('Cars','Senior Engineer' ,'Testing') , ('Ben','Testing','Engineer') ,('Rocher','PM','Engineer') ;
  Query OK, 3 rows affected (0.00 sec)
  Records: 3  Duplicates: 0  Warnings: 0
  
  MariaDB [testdb]> SELECT * FROM employee;
  +--------+-----------------+----------+
  | Name   | JobTitle        | Dept     |
  +--------+-----------------+----------+
  | Cars   | Senior Engineer | Testing  |
  | Ben    | Testing         | Engineer |
  | Rocher | PM              | Engineer |
  +--------+-----------------+----------+
  3 rows in set (0.00 sec)
  
  MariaDB [testdb]> DROP TABLE employee;
  Query OK, 0 rows affected (0.01 sec)
  
  MariaDB [testdb]> SELECT * FROM employee;
  ERROR 1146 (42S02): Table 'testdb.employee' doesn't exist
  

刪除資料庫

直接使用 DROP DATABASE 刪除資料庫.

 
MariaDB [(none)]> DROP DATABASE testdb;
Query OK, 1 row affected (0.01 sec)