Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Does MySQL DROP TABLE completely remove the table or just the structure?
The DROP TABLE removes the table completely and also removes all data. If you want to remove all data completely and wants the table structure, then you can use TRUNCATE TABLE command. The TRUNCATE command will recreate the table.
Let us first check the DROP TABLE. For that, we will first create a table −
mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Name varchar(20) ); Query OK, 0 rows affected (0.20 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable(Name) values('Larry');
Query OK, 1 row affected (0.07 sec)
mysql> insert into DemoTable(Name) values('Chris');
Query OK, 1 row affected (0.05 sec)Display all records from the table using select statement −
mysql> select *from DemoTable;
This will produce the following output −
+----+-------+ | Id | Name | +----+-------+ | 1 | Larry | | 2 | Chris | +----+-------+ 2 rows in set (0.00 sec)
Now, use DROP TABLE command. After executing the DROP TABLE command, the data will be deleted as well as there won’t be any table structure.
mysql> DROP TABLE DemoTable; Query OK, 0 rows affected (0.18 sec)
If you try to display data, you will get the following error −
mysql> select *from DemoTable; ERROR 1146 (42S02): Table 'hb_student_tracker.DemoTable' doesn't exist
You won’t even get the table structure −
mysql> desc DemoTable; ERROR 1146 (42S02): Table 'hb_student_tracker.DemoTable' doesn't exist
Let us implement the TRUNCATE command.
mysql> create table DemoTable
(
Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
FirstName varchar(20)
);
Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command −
mysql> insert into DemoTable(FirstName) values('Chris');
Query OK, 1 row affected (0.08 sec)
mysql> insert into DemoTable(FirstName) values('Robert');
Query OK, 1 row affected (0.07 sec)Display all records from the table using select statement −
mysql> select *from DemoTable;
This will produce the following output −
+----+-----------+ | Id | FirstName | +----+-----------+ | 1 | Chris | | 2 | Robert | +----+-----------+ 2 rows in set (0.00 sec)
Now, implement TRUNCATE command. After executing the TRUNCATE command, data will be removed from the table and table structure will be recreated.
mysql> TRUNCATE TABLE DemoTable; Query OK, 0 rows affected (0.06 sec)
Now you won’t get any data from table but the table structure would be visible −
mysql> select *from DemoTable; Empty set (0.00 sec)
Following is the query to get table structure −
mysql> desc DemoTable;
This will produce the following output −
+-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | Id | int(11) | NO | PRI | NULL | auto_increment | | FirstName | varchar(20) | YES | | NULL | | +-----------+-------------+------+-----+---------+----------------+ 2 rows in set (0.01 sec)
64 Views
