Learning MySQL

Creating and Using Databases

When you’ve finished designing a database, the first practical step to take with MySQL is to create it. You do this with the CREATE DATABASE statement. Suppose you want to create a database with the name lucy. Here’s the statement you’d type in the monitor:

mysql> CREATE DATABASE lucy;
Query OK, 1 row affected (0.10 sec)

We assume here that you know how to connect to and use the monitor, as described in Chapter 3. We also assume that you’re able to connect as the root user or as another user who can create, delete, and modify structures (you’ll find a detailed discussion on user privileges in Chapter 9). Note that when you create the database, MySQL says that one row was affected. This isn’t in fact a normal row in any specific database—but a new entry added to the list that you see with SHOW DATABASES.

Behind the scenes, MySQL creates a new directory under the data directory for the new database and stores the text file db.opt that lists the database options; for example, the file might contain:

default-character-set=latin1
default-collation=latin1_swedish_ci

These particular two lines specify the default character set and collation of the new database. We’ll look at what these mean later, but you generally won’t need to know much about the db.opt file or access it directly.

Once you’ve created the database, the next step is to use it—that is, choose it as the database you’re working with. You do this with the MySQL command:

mysql> USE lucy; Database ...