Other than CREATE statement in SQL to create the database and the CREATE TABLE statement to create the table, DELETE statement in SQL Used to delete rows from a table.
Syntax of DELETE statement in SQL
DELETE FROM table_name [WHERE condition];
Inside: table_name is the table name must be updated.
Note: The WHERE clause in the DELETE statement in SQL is not required, this clause identifies the rows in the deleted column. Without adding the WHERE clause, all rows in the table will be deleted. So be careful while writing a DELETE query without the WHERE clause.
Example DELETE statement in SQL
To delete an employee with ID 100 from the employees list, the query DELETE in SQL looks like this:
DELETE FROM staff member WHERE id = 100;
To delete all rows from the employee list, use the following query:
DELETE FROM employee;
TRUNCATE statement in SQL
The TRUNCATE statement in SQL is used to delete all rows from the table and free up table space.
Syntax of the TRUNCATE command in SQL:
TRUNCATE TABLE table_name;
Example of a TRUNCATE statement in SQL:
To delete all rows from the employee list, the query is of the form:
TRUNCATE TABLE employee;
Differences between DELETE and TRUNCATE statements
– DELETE command: This command only deletes rows from the table based on the condition provided in the WHERE clause or deletes all rows from the table if no condition is specified. But this command does not free up table space.
– TRUNCATE command: This command is used to delete all rows from the table and free up table space.
DROP command in SQL
The DROP command in SQL is used to remove an object from the database. If you delete a table, all rows in the table are deleted and the table structure is deleted from the database. Once the table has been deleted, you cannot restore it. So be careful when using the DROP command.
When a table has all references removed, the table has no value.
DROP command syntax in SQL
DROP TABLE table_name;
Example of DROP command in SQL:
To delete the employee table, use the following query:
DROP TABLE employee;
Difference between DROP and TRUNCATE statements in SQL
If using the DROP command, all references, relationships with other tables will no longer be valid, integrity constraints, and table access permissions will be removed. If you want to use the table again, you must recreate the table, reset its integrity constraints, access privileges, and relationships with other tables.
If you use the TRUNCATE command, the table structure remains the same, so there won’t be any problems.
Basically the DELETE statement in SQL is used to delete rows from a table in SQL. Do not forget to share your opinion about the DELETE, TRUNCATE and DROP commands in SQL for Taimienphi.vn, besides, please review your knowledge about CREATE statement in SQL here
In SQL Server, you will often have to backup and restore SQL Server to safeguard your data. If you do not know how to do it, Taimienphi.vn also has complete and detailed instructions for you.