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
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
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.
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)
For more information on the LIKE operator, visit http://mysql.com/LIKE.
Setting the Default Database
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:
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;
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
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
The DROP DATABASE command removes a database, including all tables within the database.
Use DROP DATABASE very carefullythere is no way to easily undo the command after it has been executed.