Krivalar Tutorials SQL TRUNCATE clause deletes all rows but not table structure
SQL TRUNCATE TABLE deletes all the rows from the table.
Syntax
TRUNCATE TABLE Table-Name;
Difference betwen DELETE from, DROP TABLE and TRUNCATE TABLE
| DELETE FROM statement without WHERE clause | DROP TABLE | TRUNCATE TABLE |
|---|---|---|
| Delete all the records from an existing table. Does not delete the table structure itself. | Drops the entire table structure, data records, and everything linked to the table including indexes | Delete all the records from a table. Does not delete the table structure itself. |
| Used for deleting rows from a small table. | Simply drops a table. If you want to store the same data in the table then you would have to re-create the table again. | Used for deleting data from a big table. |
| Not suitable for deleting all records from a big table. It is very slow and not efficient. | Suitable for deleting a table - small or big in one shot. | Suitable for deleting all the records from a table faster. |
| If you want to delete partial data from the table, then you would have to use this DELETE statement with the WHERE clause. | No way to delete only partial table | Deletes complete data from a table. |
Example
Below consider the demo table 'student' from 'Collegedb'database.
mysql>SELECT * FROM student +--------+---------+------+-----------+ | RollNo | Name | Age | City | +--------+---------+------+-----------+ | 1 | Aruna | 18 | Chennai | | 2 | Varun | 19 | Bangalore | | 3 | Ara | 19 | Kerala | | 4 | Markdin | 18 | Mumbai | | 5 | Kannan | 20 | Kerala | | 6 | Kanika | 18 | Chennai | | 7 | Jose | 19 | Kerala | +--------+---------+------+-----------+ 7 rows in set (0.00 sec)
Following is the TRUNCATE query statement and see the 'student' table is truncated.
mysql> TRUNCATE TABLE student; Query OK, 0 rows affected (0.07 sec) mysql> select * from student; Empty set (0.01 sec)
