How to Delete Data?

Use is very simple and imprtant command for working in database at manual time. It used to select database in sql.

Syntax

Delete Command

DELETE FROM table_name;

Example

mysql> SELECT * FROM personal;
+------+--------+
| id   | fname  |
+------+--------+
|    1 | Ram    |
|    2 | Sham   |
|    3 | Kajal  |
|    4 | Pooja  |
|    5 | Shruti |
+------+--------+
5 rows in set (0.00 sec)

mysql> DELETE FROM personal;
Query OK, 5 rows affected (0.02 sec)

mysql> SELECT * FROM personal;
Empty set (0.00 sec)

mysql>

Delete Specifice Row Data

If you want to delete specifice row data you need to use WHERE clause that help to conition with Operator with delete data. WHERE is optional part.

Syntax

DELETE FROM personal WHERE conditon;

Example

mysql> SELECT * FROM personal;
+------+--------+
| id   | fname  |
+------+--------+
|    1 | Ram    |
|    2 | Sham   |
|    3 | Kajal  |
|    4 | Pooja  |
|    5 | Shruti |
+------+--------+
5 rows in set (0.00 sec)

mysql> DELETE FROM personal WHERE id = 5;
Query OK, 1 rows affected (0.02 sec)

mysql> SELECT * FROM personal;
+------+--------+
| id   | fname  |
+------+--------+
|    1 | Ram    |
|    2 | Sham   |
|    3 | Kajal  |
|    4 | Pooja  |
+------+--------+
4 rows in set (0.00 sec)

mysql>

---

Truncate data

Truncate is DDL command. When you truncate a table SQL server doesn't delete the data, it drops the table and recreates it, there by deallocating the pages so there is a chance to recover the truncated data before the pages where overwritten.

Syntax

TRUNCATE table_name;

Example

mysql> SELECT * FROM personal;
+------+--------+
| id   | fname  |
+------+--------+
|    1 | Ram    |
|    2 | Sham   |
|    3 | Kajal  |
|    4 | Pooja  |
|    5 | Shruti |
+------+--------+
5 rows in set (0.00 sec)

mysql> TRUNCATE personal;
Query OK, 5 rows affected (0.02 sec)

mysql> SELECT * FROM personal;
Empty set (0.00 sec)

mysql>

---

How to Undo Query?

COMMIT

If AUTOCOMMIT set to false and the transaction not committed, the changes will be visible only for the current connection. After COMMIT statement commits the changes to the table, the result will be visible for all connections.

Syntax

COMMIT;

ROLLBACK

If anything went wrong in your query execution, ROLLBACK in used to revert the changes.

Syntax

ROLLBACK;

Example

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> TRUNCATE mytable;
Query OK, 0 rows affected (0.02 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM mytable;
+------+
| id   |
+------+
|    2 |
|    3 |
+------+
2 rows in set (0.00 sec)

mysql>