MySQL TRUNCATE TABLE

The TRUNCATE command is a MySQL statement used to remove all data from a specified table in a database. 

The TRUNCATE command works by deleting all the rows in the table, but it does not delete the table structure itself, including the columns, indexes, and constraints. 

The syntax for the TRUNCATE command is as follows:

TRUNCATE TABLE table_name;

In this syntax, table_name is the name of the table that you want to truncate. 

Once the TRUNCATE command is executed, all data in the table will be permanently removed, and the table will have zero rows. 

mysql> SELECT * FROM friends;
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 15 | Ana  | Smith    |    111222 |
| 16 | Ana  | Gatez    |    555666 |
| 17 | John | Snow     |    666777 |
| 18 | John | Smith    |    999888 |
+----+------+----------+-----------+
4 rows in set (0.00 sec)

mysql>

Deleting specific id:

mysql> DELETE FROM friends WHERE id=18;
Query OK, 1 row affected (0.10 sec)

mysql>

The statement DELETE FROM friends specifies the table where the data will be deleted from. The WHERE clause is used to specify the condition that must be met for a row to be deleted. In this case, the condition is that the id value must be 18.

After the execution of this code, the row with id=18 will no longer be present in the friends table.

mysql> TRUNCATE TABLE friends;
Query OK, 0 rows affected (1.47 sec)

mysql>

The statement TRUNCATE TABLE friends specifies the table where the data will be deleted from. 

Unlike the DELETE statement, the TRUNCATE statement does not require a WHERE clause to specify the condition for deleting data. Instead, it deletes all rows in the specified table.

mysql> SELECT * FROM friends;
Empty set (0.03 sec)

mysql>

Structure is there:

mysql> DESCRIBE friends;

+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| id        | int      | NO   | PRI | NULL    | auto_increment |
| Name      | char(30) | YES  |     | NULL    |                |
| Lastname  | char(30) | YES  |     | NULL    |                |
| Telephone | int      | YES  |     | NULL    |                |
+-----------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql>