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)
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料