Databases & SQL · Class 12 · Getting Started with MySQL

MySQLDDL⏱️ 6 min read

Dropping a Table or Database

The last of the structure commands, and the shortest — which is unfortunate, because it is also the most destructive. drop does not empty a table. It removes the table itself, and everything that was in it, immediately.

1Dropping a table

MySQL command line client

mysql> drop table temp1;

Query OK, 0 rows affected (0.11 sec)

mysql> show tables;

+---------------------+

| Tables_in_lambdalab |

+---------------------+

| admissions |

| students |

| teachers |

+---------------------+

3 rows in set (0.00 sec)

temp1 is gone from the list. Not emptied — gone. Its columns, its data types, its primary key and every row it held no longer exist.

2Dropping a database

The same idea one level up. This removes the container and every table inside it:

MySQL command line client

mysql> create database scratchdb;

Query OK, 1 row affected (0.02 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| lambdalab |

| mysql |

| performance_schema |

| practice |

| scratchdb |

| sys |

+--------------------+

7 rows in set (0.00 sec)

mysql> drop database scratchdb;

Query OK, 0 rows affected (0.10 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| lambdalab |

| mysql |

| performance_schema |

| practice |

| sys |

+--------------------+

6 rows in set (0.00 sec)

Six databases, then five. There was no “are you sure?”, no warning, and nothing in a recycle bin. Had scratchdb held twenty tables and a year of records, the reply would have been the same.

There is no undo

drop cannot be reversed. The only protection is a backup taken beforehand. Read the name twice before pressing Enter — especially with drop database, where one wrong word costs every table at once.

3drop is not delete

These two are confused constantly, and the difference is the whole point of this lesson: one removes rows, the other removes the table.

drop table vs delete

	delete from students;	drop table students;
What goes	Every row	The rows AND the table itself
Does the table still exist?	Yes, empty	No — it is not in show tables any more
Can you insert into it afterwards?	Yes, straight away	No — you would have to create it again first
Which sub-language	DML — it changes data	DDL — it changes structure

After delete, select * from students; gives you Empty set — the table is there and has nothing in it. After drop, the same query gives you ERROR 1146: Table 'lambdalab.students' doesn't exist.

An exam favourite

“Rahul wants to remove all the rows but keep the table for next year's data. Which command?” The answer is delete, not drop. Watch for the words structure and rows in the question — they are what decides it.

4Two errors you will meet

Creating something that is already there:

MySQL command line client

mysql> create database lambdalab;

ERROR 1007 (HY000): Can't create database 'lambdalab'; database exists

And opening something that is not:

MySQL command line client

mysql> use lambdlab;

ERROR 1049 (42000): Unknown database 'lambdlab'

Look closely at that second one — lambdlab. The n and the g are the wrong way round. The error printed the name exactly as typed, which is how you spot it.