Creating, Dropping, and Selecting a Database

Now that you have seen how to use MySQL's basic client tools, it's time to start putting them to use. You will first use them as you learn how to create a database and then go on to do other kinds of database manipulation.

Creating and dropping a database is something that you may seldom do, except during the design phase of a project, yet the ways of doing these things are important for you to grasp. As you will see, there are often two or more ways to do the same thing, so you will learn both the SQL and the client-driven ways to manipulate databases.

Creating a Database

To create a database from a Unix console, use the mysqladmin command with the following format:

mysqladmin create database_name -u username -p

As you can see, the preceding line runs mysqladmin with the keyword create to create a database, followed by name of the database, database_name. The user authentication options may also be required, as explained previously for the mysql client.

In the following example, we create the database store and authenticate ourselves as root:

$ mysqladmin create store -u root -p
Enter password:

Whatever username you use, you will have to make sure that that user has sufficient privileges to create a database. (This is unlikely to be a limitation for root, but you may find yourself using other usernames that have fewer privileges.) The creation privilege is defined by the Create_priv setting for that user in the mysql database's user grant table. You'll learn more about privileges in Day 14.

Another way to create a database is using the mysql program.

Start the mysql console without specifying a database name (because you're about to create it). Your session should look like this:

$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 13 to server version: 3.23.46-log

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql>

The SQL statement for creating a database takes the following form:

CREATE DATABASE [IF NOT EXISTS] database_name
						

To run this in the mysql monitor, you could type your SQL like this:

mysql> CREATE DATABASE store;
Query OK, 1 row affected (0.06 sec)

The Query OK feedback means that your query worked without error, and 1 row affected means that the database was created.

If you run the preceding SQL and a database with that name exists already, an error results. Alternatively, you can add IF NOT EXISTS in your SQL to suppress this error if it should occur. (IF NOT EXISTS was added in MySQL 3.23.)

On a Unix system, database names are case sensitive. So in the preceding example, you will be able to refer to the database only as store, never as STORE or Store.

Although the database has been created, it contains no tables yet. The effect of this has been to create a new, empty directory in MySQL's data directory. This directory will eventually hold all the files corresponding to the tables of the database.

There's no technical reason why an application shouldn't create a database. You could have a user-invoked program that runs SQL to do the same as we just did here. However, creating and dropping databases are serious matters; think carefully about whether you want a program, potentially run by someone other than you, to perform such powerful queries on your system.

Listing Databases

To get a list of databases, you can use the mysqlshow program; it simply shows what databases exist on the server. When run from the command line, it takes this format:

mysqlshow [options] [database_name [table_name [column_name]]]

You can use it to show just the list of databases, or information about databases, tables, and columns within them. For now we'll just use it in its simplest mode, passing the minimum options to it. (We'll return to mysqlshow in Day 15.)

The options may include -u and -p for specifying username and password (though there are other options), which you can offer in the same way as with mysql. So a simple example of running mysqlshow could be like this:

$ mysqlshow -u root -p
Enter password:
+-----------+
| Databases |
+-----------+
| store     |
| mysql     |
| test      |
+-----------+

You can do the same thing by running a query from mysql. From inside the mysql console, you can use the SQL SHOW DATABASES, like this:

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| store    |
| mysql    |
| test     |
+----------+
3 rows in set (0.00 sec)
						

Dropping a Database

Dropping a database means deleting it, together with all tables and data within it. Using mysqladmin, you can drop a database like this:

$ mysqladmin drop store -u root -p
Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'store' database [y/N] y
Database "store" dropped

As you can see, mysqladmin gives a stern warning and asks for confirmation before proceeding.

You can also drop a database using the SQL DROP statement, seen from within the mysql monitor:

mysql> DROP DATABASE store;
Query OK, 0 rows affected (0.00 sec)

This time the effect is instant, and the database disappears! Needless to say, take care when using the DROP statement.

When dropping a database, the corresponding directory under MySQL's data directory will be deleted.