Selecting, Creating, Dropping, and Altering Databases

MySQL provides several database-level statements: USE for selecting a default database, CREATE DATABASE for creating databases, DROP DATABASE for removing them, and ALTER DATABASE for modifying global database characteristics.

Selecting Databases

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. If you do have access to a database, you can use its tables even without selecting the database explicitly by qualifying table names with the database name. For example, to retrieve the contents of the president table in the sampdb database without selecting the database first, write the query like this:

 SELECT * FROM sampdb.president;  

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 want, as long as you have access privileges to use them. Nor does selecting a database limit you to using tables only from that database. While one database is the default, you can refer to tables in other databases by qualifying their names with the appropriate 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 begin and end asynchronously. In this environment, it's not clear what the meaning of "the previously selected database" might be.

Creating Databases

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

 CREATE DATABASE db_name;  

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

Dropping Databases

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, and the directory is intended for storage of table data. If you drop a database but its name continues to show up when you issue a SHOW DATABASES statement, the reason is most likely that the database directory contains non-table files. DROP DATABASE will not delete such files and, as a result, will not delete the directory either. This means that the database directory will continue to exist, albeit empty of any tables. To really drop the database if this occurs, manually remove any remaining files in the database directory and the directory itself.

Altering Databases

The ALTER DATABASE statement, available as of MySQL 4.1, makes changes to a database's global characteristics or attributes. Currently, the only such characteristic is the default character set:

 ALTER DATABASE db_name DEFAULT CHARACTER SET charset;  

charset should be the name of a character set supported by the server, such as latin1_de or sjis. (To find out which sets your server supports, issue a SHOW CHARACTER SET statement.) charset can also be DEFAULT to indicate that the database uses the server-level character set by default. See Chapter 2, "Working with Data in MySQL," for further discussion of character sets and character set levels.

Database attributes are stored in the db.opt file in the database directory.