Creating, Dropping, and Selecting Databases

MySQL provides three database-level statements:CREATE DATABASE for creating databases, DROP DATABASE for removing them, and USE for selecting a default database.

The CREATE DATABASE Statement

Creating a database is easy; just name it in a CREATE DATABASE statement:

CREATE DATABASE db_name
						

The constraints are that the name must be legal, the database must not already exist, and you must have sufficient privileges to create it.

The DROP DATABASE Statement

Dropping a database is just as easy as creating one, assuming you have sufficient privileges:

DROP DATABASE db_name
						

However, the DROP DATABASE statement is not something you should use with wild abandon. It removes the database and all tables within it. After you drop a database, it's gone forever. In other words, don't try out this statement just to see how it works. If your administrator has been performing database backups regularly, you may be able to get the database back. But I can guarantee that no administrator will be sympathetic if you say, "Uh, I was just playing around with DROP DATABASE to see what would happen, and, uh…can you restore my database for me?"

Note that a database is represented by a directory under the data directory. If you have put non-table files in that directory, they are not deleted by the DROP DATABASE statement. In that case, the database directory itself is not removed, either.

The USE Statement

The USE statement selects a database to make it the default (current) database for a given connection to the server:

USE db_name
						

You must have some access privilege for the database or you cannot select it. It's not actually necessary to select a database to use the tables in it because you can refer to its tables using db_name.tbl_name form. However, it's much more convenient to refer to tables without having to specify a database qualifier.

Selecting a default database doesn't mean it must be the default for the duration of the connection. You can issue any number of USE statements to switch back and forth among databases as often as you like, as long as you have access privileges to use them. Selecting a database also doesn't limit you to using tables only from that database. You can still refer to tables in other databases by qualifying table names with a database name.

When a connection to the server terminates, any notion by the server of what the default database was disappears. That is, if you connect to the server again, it doesn't remember what database you had selected previously. In fact, that's not even an idea that makes any sense, given that MySQL is multi-threaded and can handle multiple connections from a given user, which can connect and disconnect in any order. In this environment, it's not clear what the meaning of "the previously selected database" might be.