Note: To follow along with the examples later in the column, the row deleted in this example must actually exist. The above is shown only as an example.

As you can see, deleting rows from a table is a fairly straightforward process. The general syntax for the DELETE statement is as follows:

DELETE FROM <table> WHERE <conditions>

As was the case with the UPDATE statement, you can omit the WHERE clause in the DELETE statement, causing the entire contents of the table to be deleted. In SQL, there is no (reasonable) way to recover data once it has been deleted from a table, so take extreme caution to ensure that any DELETE queries will behave as expected. It's often useful to issue a SELECT COUNT(*) query with the WHERE clause to verify that you're affecting only the rows you want to delete.

Also note that unlike the example provided, when removing the entire contents of a table, MySQL will not report how many rows were affected. This is due to the fact that a table could potentially contain thousands of rows and to count each individual row when they all are being removed is simply inefficient.

The final two statements of the day are used to modify tables themselves within a database. The ALTER statement changes the properties of tables themselves (for instance, adding an entirely new column to the table definition). If you wanted to add a column that kept track of the ISBN for each book in the books table, you could add that column to the table in the following fashion:

mysql> ALTER TABLE books ADD isbn VARCHAR(25) AFTER pub_date;

Query OK, 4 rows affected (0.10 sec)

Records: 4 Duplicates: 0 Warnings: 0

In this case, I have added a new column named isbn of type VARCHAR as the last item in the table by placing it after the pub_date column (itself previously the last column in the books table). To visualize this, use the DESC statement:

mysql> DESC books;

 ----------- -------------- ------------------- ------ ----- --------- ---------------- 
| Field     | Type         | Collation         | Null | Key | Default | Extra          |
 ----------- -------------- ------------------- ------ ----- --------- ---------------- 
| book_id   | int(11)      | binary            | YES  | PRI | NULL    | auto_increment |
| author_id | int(11)      | binary            | YES  |     | NULL    |                |
| title     | varchar(255) | latin1_swedish_ci | YES  |     | NULL    |                |
| pub_date  | date         | latin1_swedish_ci | YES  |     | NULL    |                |
| isbn      | varchar(25)  | latin1_swedish_ci | YES  |     | NULL    |                |
 ----------- -------------- ------------------- ------ ----- --------- ---------------- 

5 rows in set (0.00 sec)

To remove this column from the table, use the ALTER statement again. However, instead of using the ADD qualifier, use DROP:

mysql> ALTER TABLE books DROP isbn;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

To add a new column to a table as the first column, use the FIRST qualifier used instead of AFTER:

mysql> ALTER TABLE books ADD isbn VARCHAR(25) FIRST;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

Here's the general syntax of the ALTER statement:

ALTER TABLE <table> [ADD | DROP] <column definition> [FIRST | AFTER <column>]

DROP [TABLE | DATABASE] <table or database name>