MySQL DELETE From Table

Initial situation in our table: 

mysql> SELECT * FROM friends;
+----+-----------+----------+-----------+
| id | Name      | Lastname | Telephone |
+----+-----------+----------+-----------+
|  1 | John      | Smith    |    111111 |
|  2 | Anastasia | TheGreat |    111111 |
|  3 | Samantha  | Fox      |    111111 |
| 10 | Larry     | TheFirst |    111111 |
| 11 | Bill      | Gatezzz  |    111111 |
| 12 | Bill      | Gatezzz  |    111111 |
| 13 | John      | Smith    |    111111 |
| 14 | Anastasia | Some     |    111111 |
+----+-----------+----------+-----------+
8 rows in set (0.00 sec)

mysql>

Delete example targeting one column with specific id:

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

mysql>

The above MySQL query deletes the row from the friends table where the id is equal to 1.

The DELETE statement is used to delete one or more rows from a table.

In this case, the WHERE clause is used to specify the condition that the row with id=1 should be deleted. Once the query is executed, the row is removed from the table, and the query returns a message indicating the number of affected rows, which in this case is 1. 

mysql> DELETE FROM friends WHERE (Name='Anastasia');
Query OK, 2 rows affected (0.22 sec)

mysql>

This command will delete all rows from the table "friends" where the Name column has a value of "Anastasia". 

Situation after delete command:

mysql> SELECT * FROM friends;
+----+----------+----------+-----------+
| id | Name     | Lastname | Telephone |
+----+----------+----------+-----------+
|  3 | Samantha | Fox      |    111111 |
| 10 | Larry    | TheFirst |    111111 |
| 11 | Bill     | Gatezzz  |    111111 |
| 12 | Bill     | Gatezzz  |    111111 |
| 13 | John     | Smith    |    111111 |
+----+----------+----------+-----------+
5 rows in set (0.00 sec)

mysql>

We can use OR with DELETE:

mysql> DELETE FROM friends WHERE (id=3 OR id=13);
Query OK, 2 rows affected (0.12 sec)

mysql>

This command is deleting rows from the friends table where the id column equals either 3 or 13.

The syntax of the command is as follows:

• DELETE FROM friends: This specifies the table to delete rows from.
• WHERE (id=3 OR id=13): This specifies the condition that must be met in order for rows to be deleted. In this case, rows will only be deleted if their id value is either 3 or 13. 

mysql> SELECT * FROM friends;
+----+-------+----------+-----------+
| id | Name  | Lastname | Telephone |
+----+-------+----------+-----------+
| 10 | Larry | TheFirst |    111111 |
| 11 | Bill  | Gatezzz  |    111111 |
| 12 | Bill  | Gatezzz  |    111111 |
+----+-------+----------+-----------+
3 rows in set (0.00 sec)

mysql>

mysql> DELETE FROM friends WHERE Telephone=111111;
Query OK, 3 rows affected (0.28 sec)

mysql>

This command deletes all rows from the "friends" table where the "Telephone" column has a value of 111111. The output message shows that three rows have been affected, indicating that three rows have been deleted from the table. 

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

mysql>

The output shows that the SELECT query returned an empty set, indicating that there are no more rows in the friends table after deleting the rows where the Telephone value is 111111. 

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.05 sec)

mysql>