Creating and Managing Databases

Databases are, for the most part, easy to create and maintainthey are simple containers with few properties of their own. The following phrases cover most of what you will need to know for creating, maintaining, and deleting them.

List All Databases

SHOW DATABASES; 

Use the SHOW DATABASES command to fetch a list of the names of the databases on the MySQL server to which you are connected.

On a new installation of MySQL, the SHOW DATABASES command might return a result like the following:

+----------+ | Database | +----------+ | mysql    | | test     | +----------+ 2 rows in set (0.00 sec) 

Get a Filtered List of Databases

SHOW DATABASES LIKE 'my%';

If you only want to list database names that match all or part of a word, use the LIKE operator.

The word used with the LIKE operator is just a normal word, except that percent (%) and underscore (_) characters have a special meaning within the word. In the context of LIKE, percent (%) can represent any sequence of zero or more characters, while the underscore (_) wildcard can match any single character.

In this example, only databases whose names are my or start with my will be returned by SHOW DATABASES.

Tip

The LIKE operator can be used with many other SQL commands, including other SHOW commands and the WHERE clause of SELECT and DELETE commands.

If you are working with a newly installed copy of MySQL and run the sample code at the start of this phrase, you should see a result like the following:

+----------+ | Database | +----------+ | mysql    | +----------+ 1 rows in set (0.00 sec) 

Note

For more information on the LIKE operator, visit http://mysql.com/LIKE.

Setting the Default Database

USE test;

A single installation of MySQL can have multiple databases. Any query that operates on a database or table must have some way of knowing on which database or table to operate. You can explicitly specify the database to use within a query. For example, this query will be made on the book table within the library database:

SELECT title FROM library.book; 

Alternately, you can specify a default database to operate on with the USE command:

USE library; 

Over time this can save quite a bit of typing because after setting a default database, you no longer need to explicitly specify the database in each query. The database specified in the USE command will be used by default.

Compare the following queries. While the first and third queries generate the same output, the first uses an explicit database and the third uses the default database specified by the second query.

SELECT title FROM library.book; USE library; SELECT title FROM book; 

Tip

Even if a default database has been selected with the USE command, you can still explicitly specify a database to operate on within a given query (as in the first query above).

Creating a Database

CREATE DATABASE library;

This command creates a new database named library. On its own, the database isn't very useful. See the "Creating and Managing Tables" section to learn how to add tables to a database.

Deleting a Database

DROP DATABASE library;

The DROP DATABASE command removes a database, including all tables within the database.

Caution

Use DROP DATABASE very carefullythere is no way to easily undo the command after it has been executed.

Renaming a Database

Databases are not commonly renamed. In part, this is because performing the operation correctly is a little tricky. Additionally, there is no single SQL command within MySQL to rename databases.

In very old versions of MySQL, renaming a database was as simple a process as

1.	Stopping the MySQL server
2.	Renaming the directory that represented the database
3.	Restarting the server

In modern versions of MySQL, this might cause the server to no longer recognize certain kinds of tables that were stored within the database. To safely rename a database, follow this process:

1.	Ensure that none of the tables in the database are being accessed. A simple way to do this is to revoke all permissions to use the database. Use the MySQL Administrator tool to do so. See "Tailoring User Permissions" and "Removing User Access" in Chapter 6, "User Management and Security," for details.
2.	Create a new database. Give it the name you want the old database to have.
3.	For every table in the old database, use the SHOW TABLES and RENAME TABLE commands to move the table to the new database.
4.	Give all users of the old database similar permissions on the new database.
5.	Test to ensure that everything still works.
6.	After you are sure everything works, delete the old database.

The code for migrating tables book, borrower, and loan from database books to database library might look like this:

-- Temporarily disable permissions CREATE DATABASE library; RENAME TABLE books.book TO library.book; RENAME TABLE books.borrower TO library.borrower; RENAME TABLE books.loan TO library.loan; -- Migrate permissions -- Re-enable permissions